Home Home » Archives for September 2018

oracle sql case

Posted on September 28, 2018September 28, 2018 by JointLantic

oracle sql case

oracle sql case or case statement in sql

SELECT a.emplid,
  case when a.ts_percentage >= 85 then  'G'
  else case when  a.ts_ranking1 >= '3' and a.ts_ranking2 >= '3'  then  'G'
       else case when  (a.ts_ranking1 >= '3' and a.ts_percentage2 >= 85)  then  'G'
       else case when  (a.ts_ranking2 >= '3' and a.ts_percentage1 >= 85)  then  'G'
       else
       'T' 
  end end end end end end type
from ps_ts_score a

SELECT a.emplid,

case when a.ts_percentage >= 85 then 'G'

else case when a.ts_ranking1 >= '3' and a.ts_ranking2 >= '3' then 'G'

else case when (a.ts_ranking1 >= '3' and a.ts_percentage2 >= 85) then 'G'

else case when (a.ts_ranking2 >= '3' and a.ts_percentage1 >= 85) then 'G'

else

'T'

end end end end end end type

from ps_ts_score a

Refresh Page PeopleCode

Posted on September 28, 2018September 28, 2018 by JointLantic

Refresh Page PeopleCode

How to Refresh Page PeopleCode after save or equal to F5.

1. You can use this PeopleCode after saving at last statement. This code refreshes the whole your page.

GetLevel0().Refresh();

2. Using Java Script

<SCRIPT type="text/javascript">
function ReloadPage() 
{
window.top.location.reload();
}
</SCRIPT>

function ReloadPage()

{

window.top.location.reload();

}

</SCRIPT>

2.1 You Create HTML object for replace java script code is name = PAGE_LOAD

2.2 Define code to record filed HTML

DERIVED_TBL.HTMLAREA.Value = GetHTMLText(HTML.PAGE_LOAD)

2.3 Using Meta HTML TEXT at below

How to use Meta HTML PeopleSoft

How to Check Number/ Letter Peoplecode

Posted on September 28, 2018September 28, 2018 by JointLantic

How to Check Number/ Letter Peoplecode

How to Check Number/ Letter Peoplecode function from input value. You can use this peoplecode function at below.

Function Check_Number(&STR) Returns boolean;
   &LEN = Len(&STR);
   If &LEN = 0 Then
      Return False;
   End-If;
   For &I = 1 To &LEN;
      &CODE = Code(Substring(&STR, &I, 1));
      If (&CODE < 48 Or
            &CODE > 57) Then
         Return False;
      End-If;
   End-For;
   Return True;
End-Function;

Function Check_Letter(&STR) Returns boolean;
   &LEN = Len(&STR);
   If &LEN = 0 Then
      Return False;
   End-If;
   For &I = 1 To &LEN;
      &CODE = Code(Upper(Substring(&STR, &I, 1)));
      If (&CODE < 65 Or
            &CODE > 90) Then
         Return False;
      End-If;
   End-For;
   Return True;
End-Function;

Function Check_Number(&STR) Returns boolean;

&LEN = Len(&STR);

If &LEN = 0 Then

Return False;

End-If;

For &I = 1 To &LEN;

&CODE = Code(Substring(&STR, &I, 1));

If (&CODE < 48 Or

&CODE > 57) Then

Return False;

End-If;

End-For;

Return True;

End-Function;

Function Check_Letter(&STR) Returns boolean;

&LEN = Len(&STR);

If &LEN = 0 Then

Return False;

End-If;

For &I = 1 To &LEN;

&CODE = Code(Upper(Substring(&STR, &I, 1)));

If (&CODE < 65 Or

&CODE > 90) Then

Return False;

End-If;

End-For;

Return True;

End-Function;

Reference :http://www.compshack.com/peoplecode/peoplecode-check-number-and-check-letter-functions#comment-4979

How to access related field on grid by peoplecode

Posted on September 27, 2018September 27, 2018 by JointLantic

How to access related field on grid by peoplecode

How to access related field on grid by peoplecode of peoplesoft.

My requirement:

How to access related field DESCR100 of table CNTRC_TRAN and field EMPLID3 in all row of grid.

Local Rowset &rsLvl1 = GetLevel0()(1).GetRowset(Scroll.CNTRC_TRAN);
Local Field &fldCtrl, &fldRelated;rem loop through each row in the grid;

rem WinMessage(&rsLvl1.ActiveRowCount, 0);
For &j = 1 To &rsLvl1.ActiveRowCount;

rem get the control field;
    &fldCtrl = &rsLvl1(&j).GetRecord(Record.CNTRC_TRAN).GetField(Field.EMPLID3);

    rem now get the related display field;
    &fldRelated = &fldCtrl.GetRelated(EMP_NAME.DESCR100);
    WinMessage(&fldRelated.Value, 0);

&fldRelated.Value = ""test_related_feild"";

&fldRelated.Visible = True;

End-For;

Local Rowset &rsLvl1 = GetLevel0()(1).GetRowset(Scroll.CNTRC_TRAN);

Local Field &fldCtrl, &fldRelated;rem loop through each row in the grid;

rem WinMessage(&rsLvl1.ActiveRowCount, 0);

For &j = 1 To &rsLvl1.ActiveRowCount;

rem get the control field;

&fldCtrl = &rsLvl1(&j).GetRecord(Record.CNTRC_TRAN).GetField(Field.EMPLID3);

rem now get the related display field;

&fldRelated = &fldCtrl.GetRelated(EMP_NAME.DESCR100);

WinMessage(&fldRelated.Value, 0);

&fldRelated.Value = ""test_related_feild"";

&fldRelated.Visible = True;

End-For;

How to Disable Grid Column using Page Field Name Peoplecode

Posted on September 27, 2018October 27, 2021 by JointLantic

How to Disable Grid Column using Page Field Name Peoplecode

How to Disable Grid Column using Page Field Name Peoplecode. You can use disable of grid column using page Field Name for define value and access page field name by Peoplecode of Peoplesoft.

Assuming the grid’s Page Field Name value is “GRID_NAME” and the column’s Page Field Name value is “LINK” , you can use the following code

&Grid = GetGrid(Page.MY_PAGE, "GRID_NAME");
&GridColumnName = &Grid.GetColumn("LINK");
&GridColumnName.enabled = False;

&Grid = GetGrid(Page.MY_PAGE, "GRID_NAME");

&GridColumnName = &Grid.GetColumn("LINK");

&GridColumnName.enabled = False;

GetGrid(Page.STUD_AD_SP, “ADM_VW”).LINK.Visible = False;

If many requirement about grid such as user want to change column label name by condition automatically. You can coding from below link.

How to Change Grid Column Name of Grid/Scroll level 1 in Peoplecode Dynamically

How to access related field on grid by peoplecode

How to Change Grid Column Name of Grid/Scroll level 1 in Peoplecode

how to convert string to date peoplesoft

Posted on September 14, 2018September 14, 2018 by JointLantic

How to convert string to date peoplesoft

How to convert string to date peoplesoft using function peoplecode.

You can convert string to date by peoplecode using Datevalue().

DateValue(date_str)

Using DateValue function to convert a date string and returns the result as a Date type. date_str must be a string in the active date format user’s current personalization date format.

Example

&Currentdate = DateValue(’14/09/2018′);

&Currentdate= Date value.

Reference:

https://docs.oracle.com/cd/E41633_01/pt853pbh1/eng/pt/tpcl/langref_PeopleCodeBuilt-inFunctionsandLanguageConstructs-073e6a.html#DateValue-073de2

peoplecode last day of month

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

peoplecode last day of month

peoplecode last day of month using function last_day of sql and using sqlexec of peoplecode for select last day of month

Using SQL

select last_day(add_months((sysdate),0)) from dual;

Using Peoplecode

Sqlexec(“select last_day(add_months((sysdate),0)) from dual”,&last_day_of_month);

add months to date in peoplecode

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

add months to date in peoplecode

add months to date in peoplecode using function is AddToDate of PeopleSoft. This function to add the specified number of years, months, and days to the date provided.

Syntax: AddToDate(date, num_years, num_months, num_days)

Local date &amp;result_dt,&amp;current_dt;
&amp;current_dt= %Date;

/* Get date forward 2 year, 0 months, 3 days */
&amp;result_dt= AddToDate(&amp;current_dt, 2, 0, 3);

/* Get date forward 0 years, 1 months, 30 days */
&amp;result_dt= AddToDate(&amp;current_dt, 0, 1, 30);

Local date &result_dt,&current_dt;

&current_dt= %Date;

/* Get date forward 2 year, 0 months, 3 days */

&result_dt= AddToDate(&current_dt, 2, 0, 3);

/* Get date forward 0 years, 1 months, 30 days */

&result_dt= AddToDate(&current_dt, 0, 1, 30);

Example add date forward 2 year, 6 months.

&current_dt='03/09/2018';
&result_dt = AddToDate(&current_dt,2,6,0);

1 2	&current_dt='03/09/2018'; &result_dt = AddToDate(&current_dt,2,6,0);

&result_dt = 03/03/2021

References:

http://peoplesoft.wikidot.com/addtodate
https://docs.oracle.com/cd/E13292_01/pt849pbr0/eng/psbooks/tpcl/book.htm?File=tpcl/htm/tpcl02.htm#H2001