PeopleSoft: May 2012

Monday, 28 May 2012

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

Tuesday, 15 May 2012

Data Buffer Classes
In PeopleCode there are four data buffer classes

Rowset Class
Row Class
Record Class
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;

Monday, 7 May 2012

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);

Friday, 4 May 2012

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