Peoplesoft tables

http://www.pscustomizer.com/peoplesoft-examples/peopletools-metadata/

IB Configation

http://docs.oracle.com/cd/E28394_01/pt852pbh1/eng/psbooks/tibr/chapter.htm?File=tibr/htm/tibr08.htm

AWE

http://peoplesoftlearnings.blogspot.in/2010/02/peoplesoft-awe-workflow-configuration.html

SQLExec : Return: 8015 - Bind value is too long

SQLExec : Return: 8015 - Bind value is too long

You get this error in an online page or while running a Application engine program. This error happens when you try to insert more than 254 characters in a long field using sqlexec and do not use %TextIn meta sql.
Resolution
Use %TextIn meta-sql for the bind variable that is used for inserting into a long field. For e.g. %TextIn(:1)
%TextIn is documented in peoplebooks and is mandatory for all insertions/update of LongChar fields using sqlexec for all database platforms.
Here are some resolutions that discusses this issue in Metalink – Oracle support site.
E-AE Application Engine PeopleCode Step with SQLExec Receives Error; return code 8015 "Bind value is too long" [ID 889806.1]
E-PC:"Bind value is too long" Error When Using SQLExec to Insert into Long Char Field [ID 620874.1]

 

 

i got above info from http://peoplesoftexperts.blogspot.in/

Display Prompt on a Search dialog box

Display Prompt on a Search dialog box

If you have a prompt defined on a search record, it does not get displayed if you have component properties –> Internet tab set to use Basic Mode. (Default). Only Advanced mode displays the prompt on the search dialog box.

got ablove info from http://peoplesoftexperts.blogspot.in/

comments fields to outbound as xl file:

While comments fields to outbound as xl file, we will face some problems because of tabs ,new line chars and special chars so I searched in internet and I fund some logic and I added some logic to that code Below is code this will help adding commets to xl file

Function F2(&String As string) Returns string

Local string &sCurrChar, &sTmpStr;

Local number &nPos, &nLen;

Local string &VALID = "~`!#$%^&*()_|\{}[]:'<>?\/"; /* Special chars can be allowed */

&sStr = &String; &sTmpStr = RTrim(LTrim(&sStr));

 /* Trim blank spaces before and after string */

 If IsAlphaNumeric(&sTmpStr) Or IsDigits(&sTmpStr) Then

/* Return String if already IsAlphanNumeric */

       Return &sTmpStr;

Else

     &sTmpStr = Substitute(&sTmpStr, "-", "_");

     &nLen = Len(&sTmpStr);

    /* Get Length of string */

      &nPos = 1;

  /* Set initial position */

 While &nPos <= &nLen   

/* Loop through string one character at a time and strip out non alpha character. */

 &sCurrChar = Substring(&sTmpStr, &nPos, 1);

 /* Get one character from string at the current position */

 If IsAlphaNumeric(&sCurrChar) Then

/* Check if alpha character or space */

&nPos = &nPos + 1; /* Increment current position */

Else &V = Find(&sCurrChar, &VALID);

/* Leave spaces and some Special chars inside of string */

If &sCurrChar = " " Or &V > 0 Then

&nPos = &nPos + 1;

 /* Increment current position */

Else

    If &nPos < &nLen Then

       &sTmpStr = Substring(&sTmpStr, 1, &nPos - 1) | Substring(&sTmpStr, &nPos + 1, &nLen - &nPos);

/* Remove non-alpha character */

 &nLen = Len(&sTmpStr);

/* Get new length of string */

             Else /* Last character */
                     Return Substring(&sTmpStr, 1, &nPos - 1);
/* Return string without last character */
End-If;
 End-If;
End-If;
End-While;
End-If;
Return &sTmpStr;
End-Function;

Adding and updating data into record in peoplecode

&variable_name= CreateRecord(Record.Record_name);

&variable_name.field_name.value= data

&variable_name.field_name2.value= data

/*to insert new row*/

&variable_name.Insert()

/*to update existing data*/

&variable_name.update()

Note: when doing update key field’s data required

          If with same key fields data is already exist in table insert will not work

To get field name of that record

For &i = 1 To &variable_name.FieldCount
   &fn = &variable_nameGetField(&i).Name;

Creating structure (columns) from other table:   

To get only structure not data use below SQL

CREATE TABLE new_table
  AS (SELECT * FROM old_table WHERE 1=2)

 If you want data also

CREATE TABLE new_table
  AS (SELECT * FROM old_table)

Clicking of push button XL to CI :

Some times we will get requirement like this in a page click of push button is mandatory, so if you create XL to CI to the you have to include that push button field in that CI, then in  XL to CI give input as ‘Y’ so it’ll work

Warning: Application Engine Request is not active -- processing suspended (108,517):

 Some times when we try to schedule an application engine through AE it gives above error. & the process goes into success. It actually does not execute the application engine
  
 you have to  set the process frequency to "Always" in below page and then schedule process

Home > PeopleTools > Application Engine > Process > Request

 

Enable and Disable add and delete Buttons in Scroll:

 To enable and disable add/delete button using people code

Example With bit logic

 Local Rowset &PCS_CMP_HDR;

 & CMP_HDR = GetLevel0()(1).GetRowset(Scroll.PER_ROL_REC);

If &CMP_HDR(CurrentRowNumber()).PER_ROL_REC.WF_STATUS.Value = "A" Then

   &CMP_HDR.InsertEnabled = True;

Else

   &CMP_HDR.InsertEnabled = True;

End-If;

In front end  also you can do

1.

2.

Note:

 

In Scroll if all field all fields display only automatically add/deletions will become display only

to avoid this you have to make put at least one filed in editable and make visible property false

Writing Inline Queries using PS Query Tool

To achive this we can use SQL objects
appdesigner create SQL object with in line select statement

use this SQL as exprestion in PSQuery

click on Add Expresstion

In Expresstion test place give SQL(Name of SQL Object) like ( %SQL(V_TEST))

click on save and then click on use it as field

Finding Duplicates in a Record

  select p.emplid, p.expiratn_dt from sysadm.ps_CITIZEN_PSSPRT P where
p.expiratn_dt=(select max(expiratn_dt) from sysadm.ps_CITIZEN_PSSPRT  where emplid=p.emplid) GROUP by p.emplid,p.expiratn_dt
HAVING COUNT(emplid)>1

  Change above SQL based on your requirement

Data Buffer Classes
In PeopleCode there are four data buffer classes

1. Rowset Class
2. Row Class
3. Record Class
4. Field Class

Order of these classes will be
Rowset > Row > Record > Field 

Ex:

 Standard form

Local  RowSet  &Rs0,&Rs1,&Rs2;
Local Row        &Row0,&Row1,&Row2;
Local Record   &Rec0,&Rec1,&Rec2;
Local Filed       &Field,&Field2;

&Rs0 = GetLevel0();
&Row0 = &Rs0.GetRow(1);
&Rs1 = &Row0.GetRowset(Scroll.Z_CLASS);
&Row1 = &Rs1.GetRow(2);
&Rs2 = &Row1.GetRowset(Scroll.Z_SECTION_REC);
&Row2 = &Rs2.GetRow(3);
&Rs3 = &Row2.GetRowset(Scroll.Z_STUDENT_REC);
&Row3 = &Rs3.GetRow(3);
&Rec2 = &Row3.GetRecord(Record.Z_STUDENT_REC);
rem &Row2=&Rec1.GetRow(3);
&Field2 = &Rec2.GetField(Field.NAME);
rem &Row3=&Field1.GetRow(3);
WinMessage(&Field2.Value);

Short form

&RQ_Lvl0 = GetLevel0();
   &RQ_Lvl1 = &RQ_Lvl0(1).GetRowset(Scroll.HRS_JO_RQMT);
   &RQ_Lvl1(1).HRS_JO_RQMT.SAL_ADMIN_PLAN.Value = &SAL_ADMIN_PLAN; 

To Check Required Fields,Field Length of a record

SELECT A.RECNAME
 , A.FIELDNAME
 , CASE WHEN B.FIELDTYPE = 0 THEN 'CHAR' WHEN B.FIELDTYPE = 1 THEN 'LONG CHAR' WHEN B.FIELDTYPE = 2 THEN 'NUMBER' WHEN B.FIELDTYPE = 3 THEN 'SIGNED NBR' WHEN B.FIELDTYPE = 4 THEN 'DATE' WHEN B.FIELDTYPE = 5 THEN 'TIME' WHEN B.FIELDTYPE = 6 THEN 'DATETIME' WHEN B.FIELDTYPE = 7
    OR B.FIELDTYPE = 8 THEN 'IMAGE' ELSE NULL END AS FIELDTYPE
    , CASE WHEN B.FIELDTYPE = 2
    OR B.FIELDTYPE = 3 THEN TRIM(TO_CHAR(B.LENGTH)) || '.' || TO_CHAR(B.DECIMALPOS) ELSE TO_CHAR(B.LENGTH) END AS FLDLEN
    , CASE WHEN bitand(A.USEEDIT
    , 256) > 0 THEN 'YES' ELSE 'NO' END AS REQ
    , CASE WHEN bitand(A.USEEDIT
    , 1) > 0 THEN 'KEY' WHEN bitand(A.USEEDIT
    , 2) > 0 THEN 'DUP' WHEN bitand(A.USEEDIT
    , 16) > 0 THEN 'ALT' ELSE NULL END AS KEY_TYPE
    , CASE WHEN bitand(A.USEEDIT
    , 64) > 0 THEN 'DESC' WHEN ( bitand(A.USEEDIT
    , 1) > 0
    OR bitand(A.USEEDIT
    , 2) > 0
    OR bitand(A.USEEDIT
    , 16) > 0 )
   AND bitand(A.USEEDIT
   , 64) = 0 THEN 'ASC' ELSE NULL END AS DIR
   , CASE WHEN bitand(A.USEEDIT
   , 2048) > 0 THEN 'YES' ELSE 'NO' END AS SRCH
   , CASE WHEN bitand(A.USEEDIT
   , 32) > 0 THEN 'YES' ELSE 'NO' END AS LIST
   , CASE WHEN bitand(A.USEEDIT
   , 4) > 0 THEN 'YES' ELSE 'NO' END AS SYS
   , CASE WHEN TRIM(A.DEFRECNAME) = '' THEN A.DEFFIELDNAME ELSE TRIM(A.DEFRECNAME) || '.' || A.DEFFIELDNAME END AS DEFAULT_VALUE
   , CASE WHEN bitand(A.USEEDIT
   , 8) > 0
   AND bitand(A.USEEDIT
   , 128) = 0
   AND bitand(A.USEEDIT
   , 1024) = 0 THEN 'A' WHEN bitand(A.USEEDIT
   , 8) > 0
   AND bitand(A.USEEDIT
   , 128) > 0
   AND bitand(A.USEEDIT
   , 1024) = 0 THEN 'AC' WHEN bitand(A.USEEDIT
   , 8) > 0
   AND bitand(A.USEEDIT
   , 128) > 0
   AND bitand(A.USEEDIT
   , 1024) > 0 THEN 'ACD' WHEN bitand(A.USEEDIT
   , 8) = 0
   AND bitand(A.USEEDIT
   , 128) > 0
   AND bitand(A.USEEDIT
   , 1024) = 0 THEN 'C' WHEN bitand(A.USEEDIT
   , 8) = 0
   AND bitand(A.USEEDIT
   , 128) > 0
   AND bitand(A.USEEDIT
   , 1024) > 0 THEN 'CD' WHEN bitand(A.USEEDIT
   , 8) = 0
   AND bitand(A.USEEDIT
   , 128) = 0
   AND bitand(A.USEEDIT
   , 1024) > 0 THEN 'D' ELSE NULL END AS AUDT
   , CASE WHEN bitand(A.USEEDIT
   , 16384) > 0 THEN 'PROMPT' WHEN bitand(A.USEEDIT
   , 512) > 0 THEN 'XLAT' WHEN bitand(A.USEEDIT
   , 8192) > 0 THEN 'Y/N' ELSE NULL END AS EDIT
   , A.EDITTABLE AS PROMPT_TABLE
   , A.SETCNTRLFLD AS SET_CONTROL_FLD
   , CASE WHEN bitand(A.USEEDIT
   , 4096) > 0 THEN 'YES' ELSE 'NO' END AS REASONABLE_DT
   , CASE WHEN bitand(A.USEEDIT
   , 32768) > 0 THEN 'YES' ELSE 'NO' END AS AUTO_UPDT
   , CASE WHEN bitand(A.USEEDIT
   , 262144) > 0 THEN 'FROM' WHEN bitand(A.USEEDIT
   , 524288) > 0 THEN 'THROUGH' ELSE NULL END AS SEARCH_FIELD
   , CASE WHEN A.SUBRECORD = 'Y' THEN 'YES' ELSE 'NO' END AS SUBRECORD
   , A.LASTUPDDTTM
   , A.LASTUPDOPRID
  FROM PSRECFIELD A
  , PSDBFIELD B
 WHERE A.RECNAME = 'RECNAME'
   AND A.FIELDNAME = B.FIELDNAME
  ORDER BY FIELDNUM,REQ

XML FILE CREATION and uploading to FTP Using Peoplecode

   Local XmlDoc &inXMLDoc;

   Local XmlNode &rootNode, &childNode1, &childNode2, &textNode2;

   Local string &xmlStr;

   Local File &xmlFile;

   Local number &i;

 

   &XML_FILE_PATH = GetEnv("PS_SERVDIR") | "\LOGS\";

   SQLExec("SELECT B.LM_CS_LONG_NM FROM PS_LM_ACT A, PS_LM_CI_TBL B WHERE B.LM_CI_ID = A.LM_CI_ID AND A.LM_ACT_ID=:1 AND B.EFFDT =(SELECT MAX(B_ED.EFFDT) FROM PS_LM_CI_TBL B_ED WHERE B.LM_CI_ID = B_ED.LM_CI_ID AND B_ED.EFFDT <= GETDATE())", &Acivity_ID, &LM_CS_LONG_NM);

   &FILE_NAME = &LM_CS_LONG_NM | "_" | %Date | ".XML";

   &xmlFile = GetFile(&XML_FILE_PATH | &FILE_NAME, "W", "", %FilePath_Absolute);

  

   &inXMLDoc = CreateXmlDoc("");

   &rootNode = &inXMLDoc.CreateDocumentElement("Start");

  

  

   rem   SQLExec("SELECT B.LM_CS_LONG_NM FROM PS_LM_ACT A, PS_LM_CI_TBL B WHERE B.LM_CI_ID = A.LM_CI_ID AND A.LM_ACT_ID=:1 AND B.EFFDT =(SELECT MAX(B_ED.EFFDT) FROM PS_LM_CI_TBL B_ED WHERE B.LM_CI_ID = B_ED.LM_CI_ID AND B_ED.EFFDT <= GETDATE())", &Acivity_ID, &LM_CS_LONG_NM);

   Local Record &rec = CreateRecord(Record.PCS_GE_GDC_VW);

   Local SQL &sql = CreateSQL("SELECT  * FROM PS_TEST_REC  where LM_CS_LONG_NM=:1", &LM_CS_LONG_NM);

  

   While &sql.Fetch(&rec)

      &childNode1 = &rootNode.AddElement(&rec.Name);

      For &i = 1 To &rec.FieldCount

         &childNode2 = &childNode1.AddElement(&rec.GetField(&i).Name);

         &textNode2 = &childNode2.AddText(&rec.GetField(&i).Value);

      End-For;

   End-While;

  

   &xmlStr = &inXMLDoc.GenFormattedXmlString();

  

   &xmlFile.WriteLine(&xmlStr);

  

   /* Pushing xml file into FTP path from the local server path */

  

   &xmlFile.Close();

   &FTP_URL = "ftp Path”

   &FILE_PATH_NAME = &FTP_URL;

   &FILE_NAME_FTP = &LM_CS_LONG_NM | "_" | %Date | ".XML";

   &PutAttachment = PutAttachment(&FILE_PATH_NAME, &FILE_NAME_FTP, &XML_FILE_PATH | &FILE_NAME);

 

Getting Navigation Using SQL 

Page name known

SELECT P4.PNLNAME,P4.PNLGRPNAME, 'Home > '+P3.PORTAL_LABEL+'->'+P2.PORTAL_LABEL+'->'+P1.PORTAL_LABEL+'->'+P.PORTAL_LABEL  Navigation
,P3.PORTAL_LABEL L3
, P2.PORTAL_LABEL L2
, P1.PORTAL_LABEL L1
, P.PORTAL_LABEL L0
, P.*
FROM PSPRSMDEFN P
, PSPRSMDEFN P1
, PSPRSMDEFN P2
, PSPRSMDEFN P3
, (SELECT PNLGRPNAME,PNLNAME FROM PSPNLGROUP) P4
WHERE P.PORTAL_URI_SEG2 =P4.PNLGRPNAME
AND P.PORTAL_PRNTOBJNAME = P1.PORTAL_OBJNAME
AND P1.PORTAL_PRNTOBJNAME = P2.PORTAL_OBJNAME
AND P2.PORTAL_PRNTOBJNAME = P3.PORTAL_OBJNAME
AND P.PORTAL_NAME = P1.PORTAL_NAME
AND P1.PORTAL_NAME = P2.PORTAL_NAME
AND P2.PORTAL_NAME = P3.PORTAL_NAME
AND P4.PNLNAME IN("Page Name")
and P3.PORTAL_LABEL<>'Navigation Collections'
order by P4.PNLNAME 

 When a page name is known:

SELECT DISTINCT
       COMP.PNLNAME as Page
     , 'Home > '
           || RTRIM(MENU.MENUGROUP)  || ' > '
           || RTRIM(MENU.MENULABEL)  || ' > '
           || RTRIM(ITEM.BARLABEL)   || ' > '
           || ITEM.ITEMLABEL as MenuPath
FROM   PSMENUDEFN     MENU
     , PSMENUITEM     ITEM
     , PSPNLGROUP     COMP
WHERE  MENU.MENUNAME    = ITEM.MENUNAME
AND    ITEM.PNLGRPNAME  = COMP.PNLGRPNAME
AND    COMP.PNLNAME     = 'page-name'

Above Information I Got from   http://www.sparkpath.com/

When a record name is known:

SELECT DISTINCT 
       PFLD.RECNAME 
     , PFLD.PNLNAME as Page
     , 'Home > ' 
           || RTRIM(MENU.MENUGROUP)  || ' > ' 
           || RTRIM(MENU.MENULABEL)  || ' > ' 
           || RTRIM(ITEM.BARLABEL)   || ' > ' 
           || ITEM.ITEMLABEL as MenuPath
FROM   PSMENUDEFN     MENU
     , PSMENUITEM     ITEM
     , PSPNLGROUP     COMP
     , PSPNLFIELD     PFLD
WHERE  MENU.MENUNAME    = ITEM.MENUNAME
AND    ITEM.PNLGRPNAME  = COMP.PNLGRPNAME
AND    COMP.PNLNAME     = PFLD.PNLNAME
AND    PFLD.RECNAME     = 'record-name'

Above Information I Got from   http://www.sparkpath.com/

 

Delivered Page to Search Component Navigation.

There was several solutions to find the navigation path for component and page. There were several SQL statements for different databases. Finally Oracle as developed page this purpose.

In the latest Ptools 8.50 and Application 9.1 application search is possible from two navigations.

1) Main Menu > Enterprise Components > Find Object Navigation
2) Setup HRMS > System Administration > Utilites > Portal Navigation Path

Search is possible with name of Component,Page,Secondary Page and Content Reference.

Search works fine with hidden components as well.

 

Above Information I Got from   peoplesoftlearnings.blogspot