Home Home » Application Engine (AE)

Record Change PeopleCode

Posted on July 19, 2018September 3, 2018 by JointLantic

Record Change PeopleCode

How to check record change PeopleCode in peoplesoft. I have the example about checking record in saveprechange event, field change event or save edit of PeopleCode. Define page has scroll area in level 1 and the user can edit the value of each field. When the user changes data and saves data then system update date time and operator id.

Example 1

Local Rowset &rsLevel0, &rsLevel1;
Local Row &rowLevel0, &rowLevel1; 

&rsLevel0 = GetLevel0(); 
&index = CurrentRowNumber(); 
rem WinMessage(&index, 0); 
&rowLevel0 = &rsLevel0.GetRow(1);
&rsLevel1 = &rowLevel0.GetRowset(Scroll.ScrollName); 
&rowLevel1 = &rsLevel1.GetRow(&index); 
If &rowLevel1.IsChanged Then 
   TABLE.Date.Value = %Datetime; 
   TABLE.EMPLID.Value = %OperatorId; 
End-If;

Local Rowset &rsLevel0, &rsLevel1;

Local Row &rowLevel0, &rowLevel1;

&rsLevel0 = GetLevel0();

&index = CurrentRowNumber();

rem WinMessage(&index, 0);

&rowLevel0 = &rsLevel0.GetRow(1);

&rsLevel1 = &rowLevel0.GetRowset(Scroll.ScrollName);

&rowLevel1 = &rsLevel1.GetRow(&index);

If &rowLevel1.IsChanged Then

TABLE.Date.Value = %Datetime;

TABLE.EMPLID.Value = %OperatorId;

End-If;

Example 2: Check record change from some field in page in scroll area level 1. This code using for loop all record in level one. If record changed then update filed last_emplid and LAST_UPDATE_DTTM1. I replace code at SavePreChange action of record contracts (saveedit, savepostchange,etc).

&Level0_ROWSET = GetLevel0();
For &I = 1 To &Level0_ROWSET.ActiveRowCount

If RecordChanged(Record.CONTRACTS, CurrentRowNumber(1)) Then
    rem WinMessage("Changed row message from level one.", 64);
    CONTRACTS.LAST_EMPLID.Value = %OperatorId;
    CONTRACTS.LAST_UPDATE_DTTM1.Value = %Datetime;
End-If;
End-For;

&Level0_ROWSET = GetLevel0();

For &I = 1 To &Level0_ROWSET.ActiveRowCount

If RecordChanged(Record.CONTRACTS, CurrentRowNumber(1)) Then

rem WinMessage("Changed row message from level one.", 64);

CONTRACTS.LAST_EMPLID.Value = %OperatorId;

CONTRACTS.LAST_UPDATE_DTTM1.Value = %Datetime;

End-If;

End-For;

Exmaple 3: The code would execute on level one, checks rows on level two to determine which have been changed

For &I = 1 To ActiveRowCount(RECORD.BUS_EXPENSE_PER, CurrentRowNumber(1), RECORD.BUS_EXPENSE_DTL); 
If RecordChanged(RECORD.BUS_EXPENSE_PER, CurrentRowNumber(1), RECORD.BUS_EXPENSE_DTL, &I) Then 
   WinMessage("Changed row message from level one.", 64); 
End-If; 
End-For;

For &I = 1 To ActiveRowCount(RECORD.BUS_EXPENSE_PER, CurrentRowNumber(1), RECORD.BUS_EXPENSE_DTL);

If RecordChanged(RECORD.BUS_EXPENSE_PER, CurrentRowNumber(1), RECORD.BUS_EXPENSE_DTL, &I) Then

WinMessage("Changed row message from level one.", 64);

End-If;

End-For;

Reference: https://docs.oracle.com/cd/E13292_01/pt849pbr0/eng/psbooks/tpcl/chapter.htm?File=tpcl/htm/tpcl02.htm

Keeping Leading Zeros in excel/CSV by peoplecode

Posted on May 30, 2018May 30, 2018 by JointLantic

Keeping Leading Zeros in excel/CSV by peoplecode

Keeping Leading Zeros in excel/CSV by peoplecode in app engine or AE. You can use below sql code before write file in peoplecode.

This syntax for convert number to text for solve issue leading zeros

‘=””‘|| REPORTS_TO ||'””‘

e.g. select jb.emplid ,’=”‘||jb.reports_to||'”‘ from ps_job jb

Ref: https://it.toolbox.com/question/writing-data-into-csv-file-through-app-engine-peoplecode-100112
Ref:http://www.compshack.com/peoplecode/leading-zero-issue-csvexcel-formatterd-report

How to use Update/ DisplayAll, Correction mode in Component Action

Posted on May 24, 2018May 24, 2018 by JointLantic

How to use Update/ DisplayAll, Correction mode in Component Action

How to use Update/ DisplayAll, Correction mode in Component Action.

Component Action : Update
– Shows Current and Future
– Allows changes to Current
– Allows changes to Future

Component Action: Update/ Display All
– Shows History, Current, and Future
– Allows changes to Current
– Allows changes to Future

Component Action: Correction
– Shows History, Current, and Future
– Allows changes to History
– Allows changes to Current
– Allows changes to Future

Actions Detail

Add	Select to add a new high-level key, such as a new employee ID or customer. Except in the case of effective dating, use Add to insert a new current row or to update future rows.
Update/Display	Select to update existing rows only.
Update/Display All	Select to update current and future rows in an effective-dated record. Use only with effective-dated records. Do not use these actions unless the main record that is associated with the page definitions is effective dated. This information is translated to include history at runtime.
Correction	Select to update any rows (history, current, and future) in an effective-dated record. Use only with effective-dated records. This information is translated to correct history at runtime.

Ref: https://docs.oracle.com/cd/E24150_01/pt851h2/eng/psbooks/tapd/chapter.htm?File=tapd/htm/tapd15.htm
Ref:https://it.toolbox.com/question/what-is-the-use-update-displayall-correction-mode-in-component-action-092214

Differences Between Transfer and TransferExact in PeopleSoft

Posted on May 24, 2018November 28, 2018 by JointLantic

Differences Between Transfer and TransferExact in PeopleSoft

What are differences Between Transfer and TransferExact in PeopleSoft.

When you do a transfer, the first thing the system checks is whether all the key field values for the target component are provided.

If all the keys aren’t provided, the search page is displayed. In this scenario, TransferExact and Transfer are the same.

If all the keys are provided, a Select is done against the search record for that component using those keys.

If you use the Transfer function, a LIKE operator is used in the Where clause of that Select for each key.
If you use the TransferExact function, the equals operator is used in the Where clause for each key. Using equals allows the database to take full advantage of key indexes for maximum performance.

Example Code

If %Page = Page.TEST_TRANSFER Then

   Transfer( False, @("MENUNAME.ROLE_EMPLOYEE"), BarName.HOME, ItemName.TEST_TRANSFER, Panel.TEST_TRANSFER, "U", &YEAR,&QUATER);

End-If;

If %Page = Page.TEST_TRANSFER Then

Transfer( False, @("MENUNAME.ROLE_EMPLOYEE"), BarName.HOME, ItemName.TEST_TRANSFER, Panel.TEST_TRANSFER, "U", &YEAR,&QUATER);

End-If;

Ref:https://docs.oracle.com/cd/E80738_01/pt854pbh2/eng/pt/tpcl/langref_PeopleCodeBuilt-inFunctionsAndLanguageConstructs_T.html#topofpage

how to bypass search page using peoplecode

Posted on May 23, 2018May 23, 2018 by JointLantic

how to bypass search page using PeopleCode

how to bypass search page using peoplecode

1. You can use function peoplecode setsearchdialogbehavior(0) at searchinit keyfield for bypass search record.

2. Use search record is INSTALLATIION

How to find Filed data length in PeopleCode

Posted on May 22, 2018May 22, 2018 by JointLantic

How to find Filed data length in PeopleCode

How to find Filed data length in PeopleCode function of PeopleSoft. You can Len(record.field) function of PeopleCode.

Example: Len(Record.Descr254)

You can use this code for requirement such as

if Len(Record.Descr254) > 254 then

winmessage(‘Please input text data less than 254 or equal 254’,0);

end-if;

peoplesoft application engine trace

Posted on May 7, 2018May 14, 2018 by JointLantic

peoplesoft application engine trace

How to use peoplesoft application engine trace.

1. Go to the process definition of that AE(Application Engine) in menu PeopleTools > Process Scheduler > Processes.
2. Go to Override options of Precess AE.
3. Choose parameter list –> Append –> Add “”-TRACE 3″”
4. Save the process definition.
5. Run the process and go to the view log/trace and you will find the trace.

-TRACE output goes into Application Engine Trace (.AET)
-TOOLSTRACEPC output goes into the PeopleTools trace file (.trc)

or use -TRACE 7 -TOOLSTRACEPC 3596 -TOOLSTRACESQL 31

or use -TRACE 7 -TOOLSTRACEPC 4044

Ref: it.toolbox.com/question/debugging-an-application-engine-061405

Ref:docs.oracle.com/cd/E41633_01/pt853pbh1/eng/pt/tape/task_EnablingApplicationEngineTracing-077149.html

How to Using Multiple SQL Statements in an SQL Action of Application Engine

Posted on January 12, 2018May 14, 2018 by JointLantic

How to Using Multiple SQL Statements in an SQL Action of Application Engine

/*– Use ; –*/
SQL Statment 1

%Execute()
Update TABLE1 Set Status=’A’;
Update TABLE1 Set Status=’B’;
Update TABLE2 Set Status=’C’;

SQL Statment 2

%Execute()
Update TABLE1 Set Status=’A’;
Delete TABLE1 where Status=’B’;
insert into TABLE2 values(‘1′,’C’);

/*– Use / –*/
SQL Statment 3

%Execute(/)
Update TABLE1 Set Status=’A’/
Update TABLE1 Set Status=’B’/
Update TABLE2 Set Status=’C’/

SQL Statment 4

%Execute(/)
Update TABLE1 Set Status=’A’/
Delete TABLE1 where Status=’B’/
insert into TABLE2 values(‘1′,’C’)/

How to Run PS Query Report in Application Engine PeopleCode

Posted on July 27, 2017May 14, 2018 by JointLantic

How to Run PS Query Report in Application Engine PeopleCode

Local ApiObject &aRunQry;

&PSQry = %Session.GetQuery();

&qryName = “PS Query Name”;

/* State Record is Prompt field in PS Query*/
&process_instance = PT_TEST_AET.PROCESS_INSTANCE;

If (&PSQry.Open(&qryName, True, True) <> 0) Then

&fileLogLM.WriteLine(“Cannot Open the Query”);

Else
&aQryPromptRec = &PSQry.PromptRecord;
&aQryPromptRec.PROCESS_INSTANCE.Value = &process_instance;
&Date_Format = DateTimeToLocalizedString(%Date, “dd-MMM-yyyy”);

/* Output File */
&strFile = “FILE_” | &process_instance;

/* Use the RunToFile method to execute the Query and return the result to the file specified with Destination.*/
&outStrFile = %FilePath | &strFile;
MessageBox(0, “”, 0, 0, “Query Out File : ” | &outStrFile);

If (&PSQry.RunToFile(&aQryPromptRec, &outStrFile, %Query_XLS, 0) = 0) Then

MessageBox(0, “”, 0, 0, “ResultSet saved into file successfully.”);
Else
MessageBox(0, “”, 0, 0, “Failed to save Resultset into file.”);
End-If;
End-If;

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

Posted on May 2, 2017May 14, 2018 by JointLantic

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;