SendMail PeopleCode function

You can use the SendMail PeopleCode function to send emails from within PeopleCode. You can also call this function from an Application Engine.

Note: Make sure your SMTP server is configured properly or the SendMail function will fail.

--------------------------------------------------------------------------
Local string &MAIL_CC, &MAIL_TO, &MAIL_BCC, &MAIL_SUBJECT, &MAIL_TITLES, &MAIL_TEXT, &MAIL_FILES, &MAIL_FROM, &REPLYTO, &SENDER;
Local number &MAIL_FLAGS;

&MAIL_FLAGS = 0;
&MAIL_TO = "email-address-message-going-to";
&MAIL_CC = "";
&MAIL_BCC = "";
&MAIL_SUBJECT = "Test email";
&MAIL_TEXT = "Sending an email from PeopleCode.";
&MAIL_FILES = "";
&MAIL_TITLES = "";
&MAIL_FROM = "email-address-message-is-from";
&MAIL_SEP = ";";
&CONTTYPE = "";
&REPLYTO = "";
&SENDER = "";

&RET = SendMail(&MAIL_FLAGS, &MAIL_TO, &MAIL_CC, &MAIL_BCC, &MAIL_SUBJECT, &MAIL_TEXT, &MAIL_FILES, &MAIL_TITLES, &MAIL_FROM, &MAIL_SEP, &CONTTYPE, &REPLYTO, &SENDER);

If &RET <> 0 Then
MessageBox(0, "", 0, 0, "Return code from SendMail= " | &RET);
/*Do error processing here*/
End-If;
--------------------------------------------------------------------------



Your PeopleCode will have an SQLExec function to get the OPERATOR's email address.

--------------------------------------------------------------------------
SELECT emailid
FROM psoprdefn
WHERE oprid = (SELECT supervisor_id
FROM ps_employees
WHERE emplid = 'user-id-submitting-request');
--------------------------------------------------------------------------

Scheduled PSJob distribution list

When you want to detect the distribution list within a scheduled job, the two tables will be fancy to see:

select * from PS_SCHDLITEM -- All scheduled items
select * from PS_SCHDLRPTDIST -- All distribution list

PeopleSoft Batch Server

PeopleSoft Batch Server
From [http://visualsuccess.com/mediawiki/index.php/PeopleSoft_Batch_Server]


The Peoplesoft Batch Server, generally known as the PeopleSoft Process Scheduler, polls the database (PSPRCSRQST table) at regular intervals (intervals defined as Heartbeat and Sleep Intervals) performs maintenance (like determining if it is supposed to shutdown) and polls for actionable process requests (example: run Application Engine Programs, Cobol Programs, SQR’s, or other batch proccesses). The PeopleSoft Process Scheduler Server (also known as Process Scheduler Agent) can be managed using the PSADMIN command located in the PS_HOME/APPSERV directory. The Process scheduler agent is responsible for initiating most batch and report processes in PeopleTools. Process Scheduler tables can grow quickly – affecting system performance and space, it is important to purge them on a regular basis; use delivered PRCSPURG.sqr or the new PSTools 8.4+ app engine program PRCSYSPURGE. The process scheduler is configured (including trace file location) using the psprcss.cfg and psprcsssrv.* files (typically located %PS_HOME%\\appserv\prcs\. Peoplesoft 8.4+ added a master process scheduler which acts as a load balancer for other process schedulers.
Key Tables

* PSPRCSRQST Process Request table; Process Scheduler executes and updates with status information. Key field RUNSTATUS 1=cancel; 2=delete; 3=error; 4=hold; 5=queued; 6=initiated; 7=processing; 8=cancelled; 9=success; 10=notSuccess; 11=posted; 12=unableToPost; 13=resend; 14=posting; 15=content generated; 16=pending; 17=successWarning; 18=blocked; 19=restart
* PS_PRCSSEQUENCE Process Sequence Number
* PS_PRCSRUNCNTLDTL Destination Options
* PS_BATRUNCNTL Process Specific Run Control
* PS_PRCSRQSTDIST Distribution Requests
* PSPRCSPARMS Process Request Parameters
** PS_PMN_PRCSLIST Process Monitor list (five stars!)

Troubleshooting Peoplesoft Batch Server

* Poor Performance - (1) Navigate to PeopleTools/ Process Scheduler Manager/ Servers/ Server Definition; consider increasing Sleep Time and Heartbeat Time – this will increase request start time but decrease database load. (2) Consider changing process definition to restrict number of processes allowed to run concurrently; or add to Server Definition a new Process Type that limits max concurrent runs, or add a maximum processing time to the process definition. (3) Consider (on operating system) lowering or raising process scheduler priority

After being a deadlock victim, rerun VAT1001 SQR report

User just suffered from a pain:

"Transaction (Process ID 93) was deadlocked on lock resources with another process and has been chosen as the deadlock victim".

Simply re-issuing the report will cause another problem - in VAT1001 SQR Report (even Process Monitor showed "Success" and "Posted"), it just shows:

PROCESS_INSTANCE = 123456789
No rows found to round in PS_VAT_TXN_TMP_TBL

----------------------------------------------------------------------------------------------------------

Oracle Metalink has an article explains how to run the VAT report, which is:
EGL: How to Set Up And Run a VAT Report ? [ID 620419.1]

However, from VAT1001.SQR, we can see more:

begin-procedure Main-Processing
    #debugp do Debug-Msg('Main-Processing')

    do Mark-Txn-Lines
    do Insert-vat-txn-tmp-tbl
    #ifdef MICROSOFT
       do Update-Statistics
    #else
       #ifdef INFORMIX
         do Update-Statistics
       #else
          #ifdef ORACLE
             do Update-Statistics
          #end-if
       #end-if
    #end-if
    do delete-frm-vat-txn-tbl
    do Fill-Line-Info
    do Get-Report-Defn-Header
    do Get-Home-Country
    do Create-VAT-Rpt-Inst1
    do Process-Report-Lines
    do Insert-vat-rpt-err1
    do Check-for-Unselected
    do Update-In-Process-Flag
    do insert-vat-txn-tbl
    do delete-vat-txn-tmp-tbl
    do Process-Scheduler-Closing

    #debugp display 'Leaving Main-Processing'
end-procedure ! Main-Processing

In the process Mark-Txn-Lines, we can see, the SQR marks the lines by some criteria, but here I just want to point out 3 conditions for the lines being reported:

• In table PS_VAT_TXN_TBL, field IN_PROCESS_FLG is set as 'N';

• No result should be found by the SQL, if found, delete the lines having PROCESS_INSTANCE = [## the failed PI##] the from table PS_VAT_RPT_INST2; or, change their PROCESS_INSTANCE to a easy number to trace back (say, -1122334455?) :

SELECT * FROM PS_VAT_TXN_TBL, PS_VAT_RPT_INST2 B, PS_VAT_RPT_INST1 C
  WHERE  PS_VAT_TXN_TBL.PROCESS_INSTANCE = [## the failed PI##]
  AND B.PROCESS_INSTANCE = C.PROCESS_INSTANCE
  AND B.VAT_RPT_ID = C.VAT_RPT_ID
  AND PS_VAT_TXN_TBL.VAT_ENTITY = B.VAT_ENTITY
  AND PS_VAT_TXN_TBL.COUNTRY_VAT_RPTG = B.COUNTRY_VAT_RPTG
  AND PS_VAT_TXN_TBL.VAT_DCMNT_ID = B.VAT_DCMNT_ID
  AND PS_VAT_TXN_TBL.LINE_NBR = B.LINE_NBR
  --AND C2.VAT_RPT_TYPE = $rqst_vat_rpt_type;

• Cross your finger to wish NO result be found by the SQL below (otherwise, you need to check what caused a previous reporting error - check PeopleBooks???):

SELECT * FROM PS_VAT_TXN_TBL, PS_VAT_RPT_ERR2 B2, PS_VAT_RPT_INST1 C2
  WHERE B2.PROCESS_INSTANCE = C2.PROCESS_INSTANCE
  AND PS_VAT_TXN_TBL.VAT_ENTITY = B2.VAT_ENTITY
  AND PS_VAT_TXN_TBL.COUNTRY_VAT_RPTG = B2.COUNTRY_VAT_RPTG
  AND PS_VAT_TXN_TBL.VAT_DCMNT_ID = B2.VAT_DCMNT_ID
  AND PS_VAT_TXN_TBL.LINE_NBR = B2.LINE_NBR
  --AND C2.VAT_RPT_TYPE = $rqst_vat_rpt_type
  AND B2.VAT_RPT_ASSOC_FLG IN ('T', 'A')   

Then re-run VAT1001.