Monthly Archives: September 2017

How to use peoplesoft sendmail function with attachment by peoplecode

Posted on by

How to use peoplesoft sendmail function with attachment by peoplecode

You can use send html email and file attachment by peoplecode use sendmail function

&MAIL_FLAGS = 0;
&MAIL_CC = “”;
&MAIL_BCC = “”;
&MAIL_SUBJECT = “”;
&MAIL_TEXT = “”;
&MAIL_FILES = “”;
&MAIL_TITLES = “”;
&MAIL_SENDER = “Test@mail.com”;
&MAIL_SUBJECT = &Subject_mail;
&MAIL_TEXT = &Content_Mail;

&CONTTYPE = “Content-type: text/html; charset=utf8”;
&MAIL_SEP = “;”;

/*Path Report of Report Server or path websever*/

&MAIL_FILES = “C:\temp\Filename.doc”;
&MAIL_TITLES = “Filemame.doc”;

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

How to Get Column Names of a Table in Oracle Query

Posted on by

How to Get Column Names of a Table in Oracle Query

You can select column names from table oracle database

** table name is upper **

select column_name from user_tab_cols where table_name =’PS_JOB’;

SELECT COLUMN_NAME FROM ALL_TAB_COLS WHERE TABLE_NAME =’PS_JOB’;

SELECT COLUMN_NAME FROM ALL_COL_COMMENTS WHERE TABLE_NAME = ‘PS_JOB’;

/* Sort Field by order in table*/

SELECT * FROM DBA_TAB_COLS WHERE TABLE_NAME =’PS_JOB’ ORDER by COLUMN_ID

SELECT COLUMN_NAME FROM DBA_TAB_COLS WHERE TABLE_NAME =’PS_JOB’ ORDER by COLUMN_ID

/* Sort Field by order in table and concate with ,*/

SELECT RTRIM (xmlagg (xmlelement (e, COLUMN_NAME || ‘,’)).extract (‘//text()’),’,’) COLUMN_NAMES FROM DBA_TAB_COLS WHERE TABLE_NAME =’PS_DEPT_TBL’ ORDER by COLUMN_ID

/* Select text all field form owner table */

SELECT ‘SELECT ‘||RTRIM (xmlagg (xmlelement (e, COLUMN_NAME || ‘,’)).extract (‘//text()’),’,’)||’ FROM ‘||OWNER||’.’||TABLE_NAME FROM ALL_TAB_COLS WHERE TABLE_NAME=’PS_DEPT_TBL’ GROUP BY OWNER,TABLE_NAME;

How to Remove Special Characters from String in Peoplecode

Posted on by

How to Remove Special Characters from String in Peoplecode

You can remove special characters from string by use Substitute function by peoplecode

For example 1  is create function remove special

Function RemoveSpecial(&Textdescr)

&Textdescr = Substitute(&Textdescr, “*”, “”);
&Textdescr = Substitute(&Textdescr, “^”, “”);
&Textdescr = Substitute(&Textdescr, “~”, “”);
&Textdescr = Substitute(&Textdescr, “|”, “”);
&Textdescr = Substitute(&Textdescr, “&”, “”);
&Textdescr = Substitute(&Textdescr, “>”, “”);
&Textdescr = Substitute(&Textdescr, “<“, “”);

&Textdescr = Substitute(&Textdescr, “.”, “”);
&Textdescr = Substitute(&Textdescr, “-“, “”);

/*– more statment –*/

End-Function;

For example 2: You create peoplecode at action saveprechang or savepostchange

SQLExec("UPDATE PS_TESTS SET KTB_DESCR1 = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(TEST_DESCR1, CHR(10), ''), CHR(13), '') , CHR(09), ''),'""',''),' ','') WHERE TEST_SIC_YEAR= :1 AND EMPLID = :2 AND TEST_SIC_GRP = :3 and TEST_INT_RC= :4", TEST_EMP_RANK.TEST_SIC_YEAR, TEST.EMPLID, TEST.TEST_SIC_GRP, TEST.TEST_INT_RC);

How to change xml publisher default Output Template

Posted on by

How to change xml publisher default Output Template

You can change output file of xml publisher in Peoplesoft by go to menu: Report Definition and search your report and click Output tab and choose defualt format and save

defualt format has pdf, html, rtf (doc), xls

Example use defualt format is PFD

How to use MsgGet, MsgGetText and MsgGetExplainText by Peoplecode Function of Peoplesoft

Posted on by

How to use MsgGet, MsgGetText and MsgGetExplainText by Peoplecode Function of Peoplesoft

MsgGet syntax

MsgGet(message_set, message_num, default_msg_txt[, paramlist])

Example: &MsgText = MsgGet(30000, 2, “Message not found”);

MsgGetText syntax

MsgGetText(message_set, message_num, default_msg_txt[, paramlist])
Example: &MsgText = MsgGetText(30000, 2, “Message not found”);

 

MsgGetExplainText(message_set, message_num, default_msg_txt[, paramlist])

Example1

If Message Catalog (100,1)= This is my name is %1 and i like pet is %2.

&paramlist1= “Joy”;

&paramlist2= “cat”;

MsgGetExplainText(100, 1, “Message not found”, paramlist1,&paramlist2)

Result= This is my name is Joy and i like pet is cat.

 

Example2

Define Message catalog : 

message_set =20005 

message_num= 4

Severity = Message

Message Text = Test Mail

Description = Email Tracking Coaching & Performance

 

Example of use each function

&MsgGet = MsgGet(20005, 4, “default_msg_txt”);

&MsgGet=Test Mail (20005,4)

&MsgGetText = MsgGetText(20005, 4, “default_msg_txt”);

&MsgGetText=Test Mail 

&MsgGetExplainText = MsgGetExplainText(20005, 4, “default_msg_txt”);

&MsgGetExplainText=Email Tracking Coaching & Performance

WinMessage(“&MsgGet=” | &MsgGet);
WinMessage(“&MsgGetText=” | &MsgGetText);
WinMessage(“&MsgGetExplainText=” | &MsgGetExplainText);

 

 

 

How to use Winmessage in Peoplecode

Posted on by

How to use Winmessage in Peoplecode

&RESULT = WinMessage(“Please Confirm ” | Char(13) | Char(10) | ” Yes = Confirm ” | Char(13) | Char(10) | “No = Exit”, 4);

If &RESULT = %MsgResult_Yes Then
/* Yes button was pressed — do Yes button stuff */

else
/* No button was pressed — do No button stuff */

end-if;

Syntax : WinMessage(message [, ​style] [, ​title])

Message : Text displayed in message box. Normally you want to use the MsgGet or MsgGetText function to retrieve the message from the Message Catalog.

Title : Title of message box.

Style : Either a numerical value or a constant specifying the contents and behavior of the dialog box. This parameter is calculated by cumulatively adding either a value or a constant from each of the following categories

Category Value Constant Meaning
Buttons 0 %MsgStyle_OK The message box contains one pushbutton: OK.
1 %MsgStyle_OKCancel The message box contains two pushbuttons: OK and Cancel.
2 %MsgStyle_AbortRetryIgnore The message box contains three pushbuttons: Abort, Retry, and Ignore.
3 %MsgStyle_YesNoCancel The message box contains three pushbuttons: Yes, No, and Cancel.
4 %MsgStyle_YesNo The message box contains two push buttons: Yes and No.
5 %MsgStyle_RetryCancel The message box contains two push buttons: Retry and Cancel.
Value Constant Meaning
-1 %MsgResult_Warning Warning was generated.
1 %MsgResult_OK OK button was selected.
2 %MsgResult_Cancel Cancel button was selected.
3 %MsgResult_Abort Abort button was selected.
4 %MsgResult_Retry Retry button was selected.
5 %MsgResult_Ignore Ignore button was selected.
6 %MsgResult_Yes Yes button was selected.
7 %MsgResult_No No button was selected.

How to use Dynamic Variable Names in PeopleCode

Posted on by

How to use Dynamic Variable Names in PeopleCode

  • Record name dynamically by peoplecode

&RECNAME = “JOB”;
&RECNAME = “RECORD.” | Upper(&RECNAME);

Local Record &rec = CreateRecord(@&RECNAME);

 

  • Field name dynamically by peoplecode

&MesssgevalueName = “&ValueFieldName”| “1”;
WinMessage(@(&MesssgevalueName),0);

 

  • Record and Field name dynamically by peoplecode

Local Field &FieldName;
Local Record &REC;

&REC = CreateRecord(Record.TEST_TBL);

For &RW= 1 To &rec.activerowcount

&REC.GetField(@(“Field.” | “STATUS” | String(&RW)).Value = &RW;

end-for;

How to Generate xml file using Peoplecode

Posted on by

How to Generate xml file using Peoplecode

You can create xml file by peoplecode

How to Generate xml file using Peoplecode

1. Create xml file by peoplecode then print data in message and copay data and save as xml file

 import PSXP_XMLGEN:*;
/* You can also use XMLDoc class to create xml files from peoplecode. Here is a simple example. This creates an xml file with the contents of a table.
*/

Local XmlDoc &inXMLDoc;
Local XmlNode &rootNode, &childNode1, &childNode2, &textNode2;
Local string &xmlStr;
Local File &xmlFile;
Local number &i;

&file_name = “/Your Path/appserv/UAT/TESTXML.xml”;
&xmlFile = GetFile(&file_name, “W”, %FilePath_Absolute); /* Code to initialize the file object */

&inXMLDoc = CreateXmlDoc(“”);
&rootNode = &inXMLDoc.CreateDocumentElement(“root”);

Local Record &rec = CreateRecord(Record.TEST);
Local SQL &sql = CreateSQL(“SELECT * FROM PS_TEST “);

While &sql.Fetch(&rec)
&childNode1 = &rootNode.AddElement(Lower(&rec.Name));
For &i = 1 To &rec.FieldCount
&childNode2 = &childNode1.AddElement(Lower(&rec.GetField(&i).Name));
&textNode2 = &childNode2.AddText(&rec.GetField(&i).Value);
End-For;
End-While;

&xmlStr = &inXMLDoc.GenFormattedXmlString();

&xmlFile.WriteLine(&xmlStr);

&xmlFile.Close();
WinMessage(&xmlStr, 0);

2. Create xml file by peoplecode then write file xml to app server

You can use field change event or other event.

import PSXP_XMLGEN:*;
/* You can also use XMLDoc class to create xml files from peoplecode. Here is a simple example. This creates an xml file with the contents of a table. */

Local XmlDoc &inXMLDoc;
Local XmlNode &rootNode, &childNode1, &childNode2, &textNode2;
Local string &xmlStr;
Local File &xmlFile;
Local number &i;

&inXMLDoc = CreateXmlDoc(“”);
&rootNode = &inXMLDoc.CreateDocumentElement(“root”);

Local Record &rec = CreateRecord(Record.TEST);
Local SQL &sql = CreateSQL(“SELECT * FROM PS_TEST “);

While &sql.Fetch(&rec)
&childNode1 = &rootNode.AddElement(Lower(&rec.Name));
For &i = 1 To &rec.FieldCount
&childNode2 = &childNode1.AddElement(Lower(&rec.GetField(&i).Name));
&textNode2 = &childNode2.AddText(&rec.GetField(&i).Value);
End-For;
End-While;

&xmlStr = &inXMLDoc.GenFormattedXmlString();
WinMessage(&xmlStr, 0);