Monthly Archives: May 2017

How to Clear Drop-Down List Peoplecode

Posted on by

How to Clear Drop-Down List PeopleCode

How to Clear Drop-Down List PeopleCode: Dynamic DropDown Lists PeopleCode for sort or clear some dropdown list by PeopleCode for each requirement.  You can clear drop-down list of translate values in PeopleCode at rowinit event or field change event.

This peoplecode for clear all translate values of field “WO_WO_TYPE” and select some data for show in drop-down list box by use code where expression and order by  this code

“Where fieldname = ‘WO_WO_TYPE’ AND FIELDVALUE NOT IN ( ‘0’,’1′,’2′,’3′,’4′,’5′,’6′,’7′,’8′,’9′,’10’,’12′” ORDER BY decode(XLATLONGNAME,’Income’,0,’Value Creation’,’2′ ,’Benchmarking’,’3′ ,’Risk Management’,3 ,’KPI Main’,3,1)

Peoplecode Example

For Example

This PeopleCode for replacing in Rowint in Record field

Example code of dropdown list in row init event of record

 

 

Select Insert Long Character PL SQL

Posted on by

Select Insert Long Character PL SQL

 

DECLARE

CURSOR c IS

SELECT GRP_LVL, DATE, SCORE_FLAG, DESCRLONG
FROM grp_lvl_c1;

rc c%ROWTYPE;
BEGIN

OPEN c;
LOOP

FETCH c INTO rc;
EXIT WHEN c%NOTFOUND;
INSERT INTO grp_lvl_c1
( GRP_LVL, DATE, SCORE_FLAG, DESCRLONG   )

VALUES

( rc.GRP_LVL, rc.DATE, rc.SCORE_FLAG, rc.DESCRLONG);
END LOOP;
COMMIT;
END;

SQL Select Age

Posted on by

SQL Select Age

SELECT trunc(months_between(sysdate,A.EFFDT)/12) “NumOfYear”
, decode( sign(trunc(sysdate) – add_months(A.EFFDT, trunc(months_between(sysdate,A.EFFDT)))), -1, trunc(mod(months_between(sysdate,A.EFFDT),12))-1
, trunc(mod(months_between(sysdate,A.EFFDT),12))) “NumOfMonth”

, decode( sign(trunc(sysdate) – add_months(A.EFFDT, trunc(months_between(sysdate,A.EFFDT)))) , -1, trunc(sysdate) – add_months(A.EFFDT, trunc(months_between(sysdate,A.EFFDT))-1)
, trunc(sysdate) – add_months(A.EFFDT, trunc(months_between(sysdate,A.EFFDT)))
) as “NumOfDay”
FROM PS_JOB A

 

Analyze Table/ Index SQL

Posted on by

Analyze Table/Index SQL

Syntax SQL Analyze Table

analyze table <<table_name>> compute statistics;

Example :
analyze table course compute statistics;

 

Syntax SQL Analyze Index

analyze index <<table_name>> compute statistics;

Example :
analyze index course compute statistics;

Update Select SQL

Posted on by

Update Select SQL

Syntax SQL : Update Table a

set(a.column1, a.column2,a…) = (select column1, column2,… from table b where b.key = a.key)

where exists (select ‘X’ from table b where b.key = a.key)

 

Example1 : Update Select

Update course a set (a.course,a.start_date) = (select b.course , b.start_date from course  b where b.course=a.course and b.course like ‘A%’)

where exists  (select ‘X’ where b.course=a.course and b.course like ‘A%’)

Example 2 : Update Select

UPDATE PS_TEST_TABLE a SET (a.FLAG1 ,a.DT_TM1, a.DT_TM2) =

( SELECT ‘P’,SYSDATE,SYSDATE FROM PS_TEST_TABLE b
WHERE b.FLAG2 =’Y’ AND b.SUBMIT_FLAG <>’S’
AND b.EMPLID = a.emplid AND b.seqnum = a.seqnum AND b.YEAR =a.YEAR AND b.YEAR = ‘2017’
)

WHERE exists

( SELECT ‘X’ FROM PS_TEST_TABLE b
WHERE b.FLAG2 =’Y’ AND b.SUBMIT_FLAG <>’S’
AND b.EMPLID = a.emplid AND b.seqnum = a.seqnum AND b.YEAR =a.YEAR AND b.YEAR = ‘2017’
)

How to Create Report BI Publisher

Posted on by

How to Create Report BI Publisher

1. Create Data Source (PS Query/ XML)

  • Query 

Menu: Reporting Tools > Query > Query Manager

Select Record / View Table for Query

View SQL and Run Test Query and Save.

  • XML Publisher

Use tool for Create File.xml

2. Create BI Publisher Data Source

Menu:  Reporting Tools >  BI Publisher > Data Source

– Click Add a New Value

– Input Data

   Data Source Type: Query, XML Publisher

– Data Source ID: Datasource

– Click Add Button and Generate or Upload XML file

Upload XML if Data Source Type is XML Publisher

Generate XML if Data Source Type is PSQuery

XML of XDS file for use data in a template of the report definition.

3. Create BI Publisher Report Definition

Menu:  Reporting Tools >  BI Publisher > Report Definition

3.1 Add Report Definition

Click Add Button

3.2 Upload Template

3.3 Run Report Definition

     3.3.1 Run at Menu: Reporting > Tools BI Publisher > Query Report Viewer

     3.3.2 Run By Report Definition in Peoplecode

How to Hide/Unhide Row Table in RTF Template

Posted on by

How to Hide/Unhide Row Table in RTF Template

You can Hide and Unhide Row Table in RTF Template of PeopleSoft by Use Conditional Region.

Example 1:

Check code at Tab Advance

 

Example 2: You want to hide text field when page id <> 1  by using a code of RTF template at properties.

<?xdofx:if PAGE_ID = 1 then ‘ ‘ else TEXT_FIELD end if?>

FTP a File Using PeopleCode

Posted on by

FTP a File Using PeopleCode

There is a function to FTP a file on a server using PeopleCode.
Local string &FtpUserID;
Local string &FtpPassword;
Local string &FtpURL;
Function ftp_file;
&FtpUserID = “user_id”;
&FtpPassword = “password”;
&FtpURL = “directory_name/subdirectory_name”;
&targetFile = “File_Name”;
/* Set ftp string */
&Ftp_my_file = “ftp://” | &FtpUserID | “:” | &FtpPassword | “@” | &FtpURL;
/*** It is always a good practice to check to see if local file exists before trying to transfer it ***/
If FileExists(&FILEPATHNAME, %FilePath_Absolute) Then
/*** Sending file… FILEFATHNAME is the path and the name of the file, while targetFile can be used to change the name of the file if need be ***/
&RetCode = PutAttachment(&Ftp_my_file, &targetFile, &FILEPATHNAME);
/* make sure to check the retcode by the built-in PeopleCode function PutAttachment to get the result of your ftp attempt */
Evaluate &RetCode
When %Attachment_Success
MessageBox(0, “File Attachment Status”, 0, 0, “File transfer succeeded”);
Break;
When %Attachment_Failed
MessageBox(0, “File Attachment Status”, 0, 0, “File transfer failed”);
Break;
When-Other
MessageBox(0, “File Attachment Status”, 0, 0, “File transfer failed, Return Code is ” | &RetCode | “. Please refer to PeopleCode Reference for Return Code description. Search for AddAttachment function.”);
End-Evaluate;
Else
MessageBox(0, “File Status”, 0, 0, “File ” | &FILEPATHNAME | ” doesn’t exist. FTP transfer Failed.”);
End-If;
End-Function;

 

credit : (borrowed from Lepa @ CompShack).

How to use Peoplecode in Application Engineto copy one File to FTP or Database Server

Posted on by

How to use PeopleCode in Application Engine to copy one File to FTP or Database Server

[Purpose: if the application server is on Unix and the files are on NT/LAN – write an app engine program, start it from a page button, etc. on app server (Unix), but force it to run on process scheduler on NT or Windows that has access to LAN]

Local File &MYFILE;
Local string &ATTACHUSERFILE, &ATTACHSYSFILENAME, &fname, &fname_provided, &Date_Time;
/*Local array of string &FNAMES;*/
/*&FNAMES = FindFiles(RUN_CNTL_HR.WHERE_CLAUSE, %FilePath_Absolute); /* Use for Multiple Files – Store paths of all files on a folder to an Array */
/*While &FNAMES.Len > 0*/

&URL_ID = “RECORD://HRS_ATTACHMENTS”;
/*&URL_ID = “ftp://user:password@ftpserver.peoplesoft.com:6000/”;*/
/*&URL_ID = “URL.HRS_INT_ATCH”;*/
/*&URL_ID = “ftp://userid:password@mycompany.com”;*/

/*fname_provided = RUN_CNTL_HR.WHERE_CLAUSE;*/
/*name_provided = “/sw/app/psoft/psofthr/OSGN5.pdf”;*/
/*&fname_provided = “/sw/app/psoft/psofthr/OSGN.pdf”; — if the app server is on Unix – file on Unix also*/
&fname_provided = “\\sy-hr9upg\hrdev\ps\test1\OSGN5.docx”;

/* if the app server is on NT – file on NT also – follow UNC naming convention*/
/* if the app server is on unix and the files are on NT – write an app engine program, start it from a page button, but force it to run on NT */

&MYFILE = GetFile(&fname_provided, “R”, %FilePath_Absolute); /* get the file */
&Date_Time = DateTimeToLocalizedString(%Datetime, “yyyyMMdd_HHmmss”);
&ATTACHUSERFILE = &MYFILE.Name; /* Actual File Name with path */
&fname = Substitute(&MYFILE.Name, “:\”, “_”);
&fname = Substitute(&fname, “\”, “_”);
&fname = Substitute(&fname, “/”, “_”);
&fname = Substitute(&fname, ” “, “_”);
&ATTACHSYSFILENAME = Replace(&fname, Len(&fname) – 3, 0, “_” | &Date_Time | “_” | &file_no);

/* Unique name – same name may overwrite old file */
If Exact(Left(&URL_ID, 4), “URL.”) Then
&return_code = PutAttachment(@(&URL_ID), &ATTACHSYSFILENAME, &ATTACHUSERFILE); /* Upload file */
Else
&return_code = PutAttachment(&URL_ID, &ATTACHSYSFILENAME, &ATTACHUSERFILE); /* Upload file */
End-If;

/* The following deletes file from the FTP or Database Server.
&ATTACHSYSFILENAME = “Test123.pdf”;
If Exact(Left(&URL_ID, 4), “URL.”) Then
   &return_code = DeleteAttachment(@(&URL_ID), &ATTACHSYSFILENAME);
Else
   &return_code = DeleteAttachment(&URL_ID, &ATTACHSYSFILENAME);
End-If;
*/
/* Use the following code in the main PeopleCode to schedule this app engine directly
&processRqst = CreateProcessRequest();
&processRqst.RunControlID = %Datetime;
&processRqst.ProcessType = “Application Engine”;
&processRqst.ProcessName = “YourAEName”;
&processRqst.RunDateTime = %Datetime;
&processRqst.RunLocation = “PSNT”;
&processRqst.Schedule(); 

 If &processRqst.Status = 0 Then
   WinMessage(“Success”, 0);
Else
   WinMessage(“Error”, 0);
End-If;

*/

How to use Peoplecode for Save File Attachment from any Server to FTP or Database Server

Posted on by

How to use PeopleCode to Save File Attachment from any Server to FTP or Database Server

(You does not Browse for File on the PC – just takes it from a variable name – use this if the files are already known or created by the program where no user input is required)

/* This code saves file from any server (NT/Unix) to FTP or Database Server using PutAttachment command*/

/*Unlike AddAttachment, PutAttachment does not open the file browse window for user to select one file to load */

Local File &MYFILE;
Local string &ATTACHUSERFILE, &ATTACHSYSFILENAME, &fname, &fname_provided, &Date_Time;
/*Local array of string &FNAMES;*/
/*&FNAMES = FindFiles(RUN_CNTL_HR.WHERE_CLAUSE, %FilePath_Absolute); /* Use for Multiple Files – Store paths of all files on a folder to an Array */
/*While &FNAMES.Len > 0*/

&URL_ID = “RECORD://HRS_ATTACHMENTS”;
/*&URL_ID = “ftp://user:password@ftpserver.peoplesoft.com:6000/”;*/
/*&URL_ID = “URL.HRS_INT_ATCH”;*/
/*&URL_ID = “ftp://user:password@mycompany.com”;*/

&fname_provided = RUN_CNTL_HR.WHERE_CLAUSE;
/*&fname_provided = “/sw/app/psoft/psofthr/OSGN.pdf”; — if the app server is on Unix – file on Unix also*/
/*&fname_provided = “\\sy-hr9upg\hrdev\ps\OSGN.pdf”; — if the app server is on NT – file on NT also – follow UNC naming convention*/
/* if the app server is on unix and the files are on NT – write an app engine program, start it from a page button, but force it to run on NT */

&MYFILE = GetFile(&fname_provided, “R”, %FilePath_Absolute);

/* get the file */
&Date_Time = DateTimeToLocalizedString(%Datetime, “yyyyMMdd_HHmmss”);
&ATTACHUSERFILE = &MYFILE.Name; /* Actual File Name with path */
&fname = Substitute(&MYFILE.Name, “:\”, “_”);
&fname = Substitute(&fname, “\”, “_”);
&fname = Substitute(&fname, “/”, “_”);
&fname = Substitute(&fname, ” “, “_”);
&ATTACHSYSFILENAME = Replace(&fname, Len(&fname) – 3, 0, “_” | &Date_Time | “_” | &file_no);

/* Unique name – same name may overwrite old file */
If Exact(Left(&URL_ID, 4), “URL.”) Then
&return_code = PutAttachment(@(&URL_ID), &ATTACHSYSFILENAME, &ATTACHUSERFILE); /* Upload file */
Else
&return_code = PutAttachment(&URL_ID, &ATTACHSYSFILENAME, &ATTACHUSERFILE); /* Upload file */
End-If;

/* The following deletes file from the FTP or Database Server.
&ATTACHSYSFILENAME = “Test123.pdf”;
If Exact(Left(&URL_ID, 4), “URL.”) Then
&return_code = DeleteAttachment(@(&URL_ID), &ATTACHSYSFILENAME);
Else
&return_code = DeleteAttachment(&URL_ID, &ATTACHSYSFILENAME);
End-If;
*/