How to get Parent's child table through SQL

To get the parent’s child table we can use connect by prior clause.
What does connect by Prior clause will do?
To display hierarchical data. We can use this. A condition that identifies the relationship between parent rows and child rows of the hierarchy.

Syntax:

SELECT...
   [START WITH initial_condition]
   CONNECT BY [nocycle] PRIOR recurse_condition
   [ORDER SIBLINGS BY order_by_clause]

Start with: Specify the value for Start with so it will consider that as a Root Row. If start with clause is not specified all rows are considered root rows.

Connect By: Condition that identifies the relationship between parent and child rows of the hierarchy.

Prior: This keyword will evaluate parent row of the current row.

For example take some records like R_RECORD, R_RECORD2, R_RECORD3 and R_FLAG

R_FLAG record's child record is R_RECORD.
R_RECORD record's child record is R_RECORD2.
R_RECORD2 record's child record is R_RECORD3.

Select rownum, recname from psrecdefn
CONNECT BY RECNAME=PARENTRECNAME  
START WITH PARENTRECNAME = 'R_FLAG';

Output:

ROWNUM          RECNAME         PARENTRECN

----------               ---------------         --------------

1                         R_RECORD       R_FLAG

Only one row will be returned since you searching for the record R_FLAG without prior keyword.
So if you need parents Childs and that Childs child, till the end then use the prior keyword which is used to return all Childs child.

Select rownum, recname, parentrecname from psrecdefn
CONNECT BY PRIOR RECNAME=PARENTRECNAME  
START WITH PARENTRECNAME = 'R_FLAG';



Output:

ROWNUM     RECNAME         PARENTRECNAM

----------          ---------------        ---------------
              1                     R_RECORD      R_FLAG
              2                     R_RECORD2    R_RECORD
              3                     R_RECORD3    R_RECORD2

So first R_FLAG table’s child R_RECORD will be returned then R_RECORD table’s child R_RECORD2 will be returned then R_RECORD2 table’s child R_RECORD3 will be retuned.

Prompt Table showing No matching values were found

When user tried to click prompt table,it showing No matching values were found,However even if we given proper prompt table.For example if you give DEPT_TBL as  prompt table for department field  its showing No matching values were found.Seems to be prompt table issue.But actually its related to component buffer.

Solution : Actually for the delivered prompts the particular page has field called SETID which gets loaded into component buffer when user selected it.Based on the SETID the prompt table values get populated.This is because SETID is a higher level key field in tables like DEPT_TBL, LOCTATION_TBL etc.Since we trying to access second level key field which is department or location we have to specify higher level key.Once it got loaded into component buffer,it will display the values for  prompt table based on given SETID.So keeping the SETID field in the page will help to display the prompt values.

(SQR 3506) Array specified not defined with CREATE-ARRAY

Issue seems to be related to an array.Interestingly my SQR program is not related to any array.But i don't know how this is happening.Checked the line numbers specified in error log.LINE NUMBER OUT OF RANGE this what i got while searching in notepad.So there is no such lines but still this error remains.Then i came to know that I'm using effective date in SQR.So i included #Include 'datetime.sqc' (Routines for date and time formatting) SQC in program.Now its running fine.If we using any date in our program its mandatory to include this SQC.

File Index is not in range 1 to 1

Last day i got one task.Need to insert employees form CSV file to CI.We know its was a common thing.First thing need to create CI for that.Drag drop CI in application engine.Then we will read our CSV file and insert to the CI.This is the code which i have written to load CSV file data (commented non required variables).

/* ===>

This is a dynamically generated PeopleCode template to be used only as a helper
to the application developer.
You need to replace all references to '[*]' OR default values with  references to
PeopleCode variables and/or a Rec.Fields. */

Local File &fileLog;
Local ApiObject &oSession, &oRPersonalData;
Local ApiObject &oNameTypeVwCollection, &oNameTypeVw;
Local ApiObject &oNamesCollection, &oNames;
Local ApiObject &oPersDataEffdtCollection, &oPersDataEffdt;
Local ApiObject &oPersNidCollection, &oPersNid;
Local ApiObject &oAddressTypeVwCollection, &oAddressTypeVw;
Local ApiObject &oAddressesCollection, &oAddresses;
Local ApiObject &oPersonalPhoneCollection, &oPersonalPhone;
Local ApiObject &oEmailAddressesCollection, &oEmailAddresses;
Local ApiObject &oDiversEthnicCollection, &oDiversEthnic;
Local ApiObject &oDiversReligionCollection, &oDiversReligion;
Local ApiObject &oPersonBraCollection, &oPersonBra;
Local ApiObject &oPersDataBraCollection, &oPersDataBra;
Local ApiObject &oPersDataCanCollection, &oPersDataCan;
Local ApiObject &oPersSmokerCollection, &oPersSmoker;
Local ApiObject &oPersDataCheCollection, &oPersDataChe;
Local ApiObject &oPlaceOrigCheCollection, &oPlaceOrigChe;
Local ApiObject &oPersHukouChnCollection, &oPersHukouChn;
Local ApiObject &oPersWrklifChnCollection, &oPersWrklifChn;
Local ApiObject &oPersWrklvChnCollection, &oPersWrklvChn;
Local ApiObject &oPersDataDeuCollection, &oPersDataDeu;
Local ApiObject &oNationalityGerCollection, &oNationalityGer;
Local ApiObject &oPersDataEspCollection, &oPersDataEsp;
Local ApiObject &oPersonFraCollection, &oPersonFra;
Local ApiObject &oPersDataFraCollection, &oPersDataFra;
Local ApiObject &oPersDataIndCollection, &oPersDataInd;
Local ApiObject &oPersDataItaCollection, &oPersDataIta;
Local ApiObject &oPersDataJpnCollection, &oPersDataJpn;
Local ApiObject &oPersDataMexCollection, &oPersDataMex;

Local ApiObject &oPersDataUsaCollection, &oPersDataUsa;

Local ApiObject &oPersDataFpsCollection, &oPersDataFps;

Local ApiObject &oFpmactdocTblCollection, &oFpmactdocTbl;
Local ApiObject &oPerChecklistCollection, &oPerChecklist;
Local ApiObject &oPerChklstItmCollection, &oPerChklstItm;
Local ApiObject &oCurJobPygrpCollection, &oCurJobPygrp;
Function errorHandler()

   Local ApiObject &oPSMessageCollection, &oPSMessage;
   Local number &i;
   Local string &sErrMsgSetNum, &sErrMsgNum, &sErrMsgText, &sErrType;
   &oPSMessageCollection = &oSession.PSMessages;

   For &i = 1 To &oPSMessageCollection.Count
      &oPSMessage = &oPSMessageCollection.Item(&i);
      &sErrMsgSetNum = &oPSMessage.MessageSetNumber;
      &sErrMsgNum = &oPSMessage.MessageNumber;
      &sErrMsgText = &oPSMessage.Text;
      &fileLog.WriteLine(&sErrType | " (" | &sErrMsgSetNum | "," | &sErrMsgNum | ") - " | &sErrMsgText);
   End-For;

   rem ***** Delete the Messages from the collection *****;
   &oPSMessageCollection.DeleteAll();
End-Function;
   try
      rem ***** Set the Log File *****;
      &fileLog = GetFile("D:\DOCUME~1\dzrkq7\LOCALS~1\Temp\R_PERSONAL_DATA.log", "w", "a", %FilePath_Absolute);
      &fileLog.WriteLine("Begin");

            rem ***** Get current PeopleSoft Session *****;
      &oSession = %Session;
      rem ***** Set the PeopleSoft Session Error Message Mode *****;
      rem ***** 0 - None *****;
      rem ***** 1 - PSMessage Collection only (default) *****;
      rem ***** 2 - Message Box only *****;
      rem ***** 3 - Both collection and message box *****;
      &oSession.PSMessagesMode = 1;

      rem ***** Get the Component Interface *****;

      &oRPersonalData = &oSession.GetCompIntfc(CompIntfc.R_PERSONAL_DATA);
      If &oRPersonalData = Null Then
         errorHandler();
         throw CreateException(0, 0, "GetCompIntfc failed");
      End-If;

      rem ***** Set the Component Interface Mode *****;
      &oRPersonalData.InteractiveMode = False;
      &oRPersonalData.GetHistoryItems = True;
      &oRPersonalData.EditHistoryItems = False;  

      Local array of string &Array_IP_string;
&MYFILE = GetFile("D:\Documents and Settings\dzrkq7\Desktop\ci.txt", "R", %FilePath_Absolute);

While &MYFILE.readline(&text)
   &Array_IP_string = Split(&text, ",");
   rem Warning (&Array_IP_string.Len);
   &array1 = &Array_IP_string [1];
   &array2 = &Array_IP_string [2];
   &array3 = &Array_IP_string [3];
   &array4 = &Array_IP_string [4];
   &array5 = &Array_IP_string [5];
   &array6 = &Array_IP_string [6];        

      rem ***** Set Component Interface Get/Create Keys *****;
      Rem &oRPersonalData.EMPLID = "New"       
      &oRPersonalData.EMPLID = &array1; 

      rem ***** Execute Get *****;
      rem If Not &oRPersonalData.Get() Then
      rem ***** No rows exist for the specified keys.*****;

      rem errorHandler();
      rem throw CreateException(0, 0, "Get failed");
      rem End-If;

      rem ***** Execute Create ******;
      If Not &oRPersonalData.Create() Then;
         rem ***** Unable to Create Component Interface for the Add keys provided. *****;
         errorHandler();

         throw CreateException(0, 0, "Create failed");
      End-If;   

      rem ***** Begin: Get/Set Component Interface Properties *****;
      rem ***** Get/Set Level 0 Field Properties *****;
      rem &fileLog.WriteLine("&oRPersonalData.BIRTHDATE = " | &oRPersonalData.BIRTHDATE);
      rem &oRPersonalData.BIRTHDATE = [*];

      rem &fileLog.WriteLine("&oRPersonalData.BIRTHPLACE = " | &oRPersonalData.BIRTHPLACE);
      rem &oRPersonalData.BIRTHPLACE = [*];
      rem &fileLog.WriteLine("&oRPersonalData.BIRTHCOUNTRY = " | &oRPersonalData.BIRTHCOUNTRY);
      rem &oRPersonalData.BIRTHCOUNTRY = [*];
      rem &fileLog.WriteLine("&oRPersonalData.BIRTHSTATE = " | &oRPersonalData.BIRTHSTATE);
      rem &oRPersonalData.BIRTHSTATE = [*];
      rem &fileLog.WriteLine("&oRPersonalData.DT_OF_DEATH = " | &oRPersonalData.DT_OF_DEATH);
      rem &oRPersonalData.DT_OF_DEATH = [*];
      rem &fileLog.WriteLine("&oRPersonalData.CHECKLIST_CD = " | &oRPersonalData.CHECKLIST_CD);
      rem &oRPersonalData.CHECKLIST_CD = [*];
      &fileLog.WriteLine("&oRPersonalData.DERIVED_EMP = " | &oRPersonalData.DERIVED_EMP);
      &oRPersonalData.DERIVED_EMP = &array2 ;
      rem &fileLog.WriteLine("&oRPersonalData.DERIVED_CWR = " | &oRPersonalData.DERIVED_CWR);
      rem &oRPersonalData.DERIVED_CWR = [*];
      rem &fileLog.WriteLine("&oRPersonalData.DERIVED_POI = " | &oRPersonalData.DERIVED_POI);
      rem &oRPersonalData.DERIVED_POI = [*];
      rem &fileLog.WriteLine("&oRPersonalData.EMPL_RCD = " | &oRPersonalData.EMPL_RCD);
      rem &oRPersonalData.EMPL_RCD = [*];
      rem &fileLog.WriteLine("&oRPersonalData.POI_TYPE = " | &oRPersonalData.POI_TYPE);
      rem &oRPersonalData.POI_TYPE = [*];
      rem &fileLog.WriteLine("&oRPersonalData.ORG_EMP_TYPE = " | &oRPersonalData.ORG_EMP_TYPE);
      rem &oRPersonalData.ORG_EMP_TYPE = [*];
      rem &fileLog.WriteLine("&oRPersonalData.WAIVE_DATA_PROTECT = " | &oRPersonalData.WAIVE_DATA_PROTECT);
      rem &oRPersonalData.WAIVE_DATA_PROTECT = [*];
      rem &fileLog.WriteLine("&oRPersonalData.ETHNIC_GRP_CD_UK = " | &oRPersonalData.ETHNIC_GRP_CD_UK);
      rem &oRPersonalData.ETHNIC_GRP_CD_UK = [*];
      rem &fileLog.WriteLine("&oRPersonalData.NI_COMMUNITY_BG = " | &oRPersonalData.NI_COMMUNITY_BG);
      rem &oRPersonalData.NI_COMMUNITY_BG = [*];
      rem &fileLog.WriteLine("&oRPersonalData.NI_CB_DET_DATE = " | &oRPersonalData.NI_CB_DET_DATE);
      rem &oRPersonalData.NI_CB_DET_DATE = [*];
      rem &fileLog.WriteLine("&oRPersonalData.EXP_RETIRE_DT = " | &oRPersonalData.EXP_RETIRE_DT);
      rem &oRPersonalData.EXP_RETIRE_DT = [*];
      rem &fileLog.WriteLine("&oRPersonalData.NIFE_DIRECT_QUEST = " | &oRPersonalData.NIFE_DIRECT_QUEST);
      rem &oRPersonalData.NIFE_DIRECT_QUEST = [*];
      rem &fileLog.WriteLine("&oRPersonalData.DEPTID = " | &oRPersonalData.DEPTID);

      rem &oRPersonalData.DEPTID = [*];

      rem &fileLog.WriteLine("&oRPersonalData.JOBCODE = " | &oRPersonalData.JOBCODE);
      rem &oRPersonalData.JOBCODE = [*];
      rem &fileLog.WriteLine("&oRPersonalData.BUSINESS_UNIT = " | &oRPersonalData.BUSINESS_UNIT);

      rem &oRPersonalData.BUSINESS_UNIT = [*];

      rem ***** Set/Get NAME_TYPE_VW Collection Field Properties -- Parent: PS_ROOT Collection *****;
      &oNameTypeVwCollection = &oRPersonalData.NAME_TYPE_VW;
      Local integer &i148;
      For &i148 = 1 To &oNameTypeVwCollection.Count;
         &oNameTypeVw = &oNameTypeVwCollection.Item(&i148);
         rem ***** Set/Get NAMES Collection Field Properties -- Parent: NAME_TYPE_VW Collection *****;
         rem &oNamesCollection = &oRPersonalData.NAMES;
         &oNamesCollection = &oNameTypeVw.NAMES;
         Local integer &i249;

         For &i249 = 1 To &oNamesCollection.Count;
            &oNames = &oNamesCollection.Item(&i249);
            &fileLog.WriteLine("&oNames.EFFDT = " | &oNames.EFFDT);
            &oNames.EFFDT = &array3;
            rem &fileLog.WriteLine("&oNames.COUNTRY_NM_FORMAT = " | &oNames.COUNTRY_NM_FORMAT);
            &oNames.COUNTRY_NM_FORMAT = &array4;
            rem &fileLog.WriteLine("&oNames.NAME_INITIALS = " | &oNames.NAME_INITIALS);
            rem &oNames.NAME_INITIALS = [*];
            rem &fileLog.WriteLine("&oNames.NAME_PREFIX = " | &oNames.NAME_PREFIX);
            rem &oNames.NAME_PREFIX = [*];
            rem &fileLog.WriteLine("&oNames.NAME_SUFFIX = " | &oNames.NAME_SUFFIX);
            rem &oNames.NAME_SUFFIX = [*];
            rem &fileLog.WriteLine("&oNames.NAME_ROYAL_PREFIX = " | &oNames.NAME_ROYAL_PREFIX);
            rem &oNames.NAME_ROYAL_PREFIX = [*];
            rem &fileLog.WriteLine("&oNames.NAME_ROYAL_SUFFIX = " | &oNames.NAME_ROYAL_SUFFIX);
            rem &oNames.NAME_ROYAL_SUFFIX = [*];
            rem &fileLog.WriteLine("&oNames.NAME_TITLE = " | &oNames.NAME_TITLE);
            rem &oNames.NAME_TITLE = [*];
            &fileLog.WriteLine("&oNames.LAST_NAME = " | &oNames.LAST_NAME);
            &oNames.LAST_NAME = &array5;
            &fileLog.WriteLine("&oNames.FIRST_NAME = " | &oNames.FIRST_NAME);
            &oNames.FIRST_NAME = &array6;
            rem &fileLog.WriteLine("&oNames.MIDDLE_NAME = " | &oNames.MIDDLE_NAME);
            rem &oNames.MIDDLE_NAME = [*];
            rem &fileLog.WriteLine("&oNames.SECOND_LAST_NAME = " | &oNames.SECOND_LAST_NAME);
            rem &oNames.SECOND_LAST_NAME = [*];
            rem &fileLog.WriteLine("&oNames.NAME_AC = " | &oNames.NAME_AC);
            rem &oNames.NAME_AC = [*];
            rem &fileLog.WriteLine("&oNames.PREF_FIRST_NAME = " | &oNames.PREF_FIRST_NAME);
            rem &oNames.PREF_FIRST_NAME = [*];
            rem &fileLog.WriteLine("&oNames.PARTNER_LAST_NAME = " | &oNames.PARTNER_LAST_NAME);
            rem &oNames.PARTNER_LAST_NAME = [*];
            rem &fileLog.WriteLine("&oNames.PARTNER_ROY_PREFIX = " | &oNames.PARTNER_ROY_PREFIX);
            rem &oNames.PARTNER_ROY_PREFIX = [*];
            rem &fileLog.WriteLine("&oNames.LAST_NAME_PREF_NLD = " | &oNames.LAST_NAME_PREF_NLD);
            rem &oNames.LAST_NAME_PREF_NLD = [*];
         End-For;
      End-For;
      rem ***** End: Get/Set Component Interface Properties *****;  

      rem***** Execute Save *****;
      If Not &oRPersonalData.Save() Then;
         errorHandler();
         throw CreateException(0, 0, "Save failed");
      End-If;  

      rem ***** Execute Cancel *****;
      rem If Not &oRPersonalData.Cancel() Then;
      rem errorHandler();

      rem throw CreateException(0, 0, "Cancel failed");
      rem End-If;

   catch Exception &ex
      rem Handle the exception;
      &fileLog.WriteLine(&ex.ToString());
End-While;
&MYFILE.Close();
   end-try;

&fileLog.WriteLine("End");
&fileLog.Close();

While running this App engine,fews issues came
1)Instance already exists
2)Fetching array element 2: index is not in range 1 to 1. (180,252)

Instance already exists: We know google is the solution.Yes i found.For first this Instance already exists issue i came to know i'm calling while loop after the session object.so its trying to insert same value again.so i made while loop before session object.

Fetching array element 2: index is not in range 1 to 1. (180,252) : Every one can say that array not initiated properly.May be.But issue is related to CSV file.After entering our data in CSV file we should not hit the space button or enter button.If so delete those and keep the cursor next to last data.On removing these blank lines in the csv file(opened in a notepad) the app engine ran successfully without any errors.
Source for this issue : Shyam's Peoplesoft Blog and Google

XML Publisher Using PeopleCode

How To Run XML Reports Using PeopleCode?

Here is the sample Peoplecode for XML Report.
You can write this peoplecode in field change event in the field where you want to trigger .


import PSXP_RPTDEFNMANAGER:*;
import PSXP_XMLGEN:*;
&QueryName = "";    /*your query name*/
&sRptDefn  = "";       /*your Report Defn*/
&sTmpltID  = "";       /*your Templete ID*/
Local integer &prcsInstId = Staterecord.PROCESS_INSTANCE.Value;
try
/* get report definition object */
&oRptDefn = create PSXP_RPTDEFNMANAGER:ReportDefn(&sRptDefn);
&oRptDefn.Get();
&rcdQryPrompts = &oRptDefn.GetPSQueryPromptRecord();
If Not &rcdQryPrompts = Null Then
      &rcdQryPrompts.oprid.value = %OperatorId;
      &rcdQryPrompts.Run_CNTL_ID.value =     Staterecord.RUN_CNTL_ID.Value;
/*You can set more query prompts depends on your Datasource;
/* Set report definition object */
      &rcdQryPrompts =  &oRptDefn.setPSQueryPromptRecord(&rcdQryPrompts);
End-If;
/*generate report*/
/* Syntax : (TemplateID,Languagecode,Date,Format)*/
&oRptDefn.ProcessReport(&sTmpltID, "3", %Date, "PDF");    /*publish report */
/* Syntax :Publish(ServerName,ReportPath,olderName,ProcessInstanceId)*/
&oRptDefn.Publish("", "", "", &prcsInstId); 
CommitWork();
/*Displays Output When push button is clicked*/ 
&oRptDefn.DisplayOutput();   
catch Exception &Err
If Not &oRptDefn = Null Then
      &oRptDefn.Close();
End-If;
WriteToLog(%ApplicationLogFence_Error, &Err.ToString());
 &Err.Output();
end-try;
To View Report,the user should have PTPT2600(XMLP Report Developer) permission list.