Using CONNECT BY in PSQuery

 

 

 

 

1.     In Application Designer, create a SQL object with following SQL. Let us name it as TEST_SQL

a.POSITION_NBR<>' '    AND a.REPORTS_TO<>' '    AND LEVEL>1) CONNECT BY nocycle PRIOR a.POSITION_NBR=a.REPORTS_TO start WITH (a.EMPLID

 

2.     Add a record in Query

 

3.     Add an Expression

 

 

4.     Add a prompt to the Query

 

5.     Add the Expression as the Criteria to the Query

 

6.     Ultimately the Query would look like

 

PeopleSoft Direct and Indirect reportees

In PeopleSoft, following can be the queries to fetch the Direct & Indirect reportees for a Manager.




   

With passing of Manager’s OPRID

SELECT A.EMPLID , A.EMPL_RCD , A.POSITION_NBR FROM PS_JOB_CURR_VW A, PS_PERSON_NAME B WHERE A.EMPLID=B.EMPLID and a.POSITION_NBR<>' ' and a.EMPLID in ( select EMPLID from ( select a.EMPLID , POSITION_NBR , REPORTS_TO , EMPL_STATUS from PS_JOB a WHERE a.effdt = ( SELECT MAX(EFFDT) FROM PS_JOB WHERE EMPLID=A.EMPLID AND EMPL_RCD=A.EMPL_RCD AND EFFDT<=sysdate) AND A.EFFSEQ = ( SELECT MAX(EFFSEQ) FROM PS_JOB WHERE EMPLID = A.EMPLID AND EMPL_RCD = A.EMPL_RCD AND EFFDT = A.EFFDT) AND position_nbr <> reports_to AND reports_to <> ' ' and EMPL_STATUS = 'A' ) start with REPORTS_TO = ( select POSITION_NBR from PS_JOB A1 , PSOPRDEFN B1 where A1.EMPLID = B1.EMPLID and A1.EFFDT = ( select max(EFFDT) from PS_JOB where EMPLID = A1.EMPLID and EMPL_RCD = A1.EMPL_RCD and EFFDT <= sysdate) and A1.EFFSEQ = ( select max(EFFSEQ) from PS_JOB where EMPLID = A1.EMPLID and EMPL_RCD = A1.EMPL_RCD and EFFDT = A1.EFFDT and A1.HR_STATUS='A') and OPRID = 'USER_OPRID' ) connect by nocycle prior POSITION_NBR=REPORTS_TO and POSITION_NBR <> REPORTS_TO and REPORTS_TO <> ' ' and EMPL_STATUS = 'A');

With passing of Manager’s EMPLID

select a.* from PS_DIR_REPORTS_VW3 a where a.POSITION_NBR<>' ' and a.REPORTS_TO<>' ' and level>1 start with a.EMPLID='12345' connect by nocycle prior a.POSITION_NBR=a.REPORTS_TO ORDER BY LEVEL;

PeopleCode to generate BI Publsiher report with Connected Query

Last week there was a requirement, at one of our clients, to generate BI Publisher Report. The data source for the report was to be PeopleSoft Connected Query.


Initially to save time, I Googled for the PeopleCode to generate the report using Connected Query as the data-source.
I found some links, but all those were using XML Doc as the data-source. That is, a XML document was created with the PeopleCode, which was saved at Server and the report was generated out of it; Which is a normal practice to do so, and I know that this impacts the Performance, while generating Report with huge data.


After my unsuccessful search at Google, I dig into PeopleBooks & made some understanding of AppPackage PT_CONQRS. And end of the day I was able to run the BI Publisher Report, with Connected Query as the data-source.


Below is the code which generated the desired Report.


I hope it would be of some help to others.

import PSXP_RPTDEFNMANAGER:*; import PT_CONQRS:*; 　 Local integer &nJobOpeningID; &nJobOpeningID = HRS_JOB_OPENING.HRS_JOB_OPENING_ID.Value; Local array of PT_CONQRS:QUERYITEMPROMPT &CQPromptsArray; Local PSXP_RPTDEFNMANAGER:DataSourceDefn &oDataSrcDefn; try /* Refer to the Data Source of XMLP report */ &oDataSrcDefn = create PSXP_RPTDEFNMANAGER:DataSourceDefn("CQR", "JOBOPENING_CONN_QRY", True); &ConQryInst = &oDataSrcDefn.ConQryObj; If &ConQryInst = Null Then     Return; End-If; If Not &ConQryInst.Validate() Then     &str = &ConQryInst.ErrString;     If &str <> "" Then        Error &str;     End-If; End-If; &ConQryInst.MaxRowsPerQuery = 0;    If &RunControlId = "" Then    &RunControlId = &ConQryInst.Name; End-If; Local PT_CONQRS:SCHED_INFO &schedInfo; &schedInfo = &ConQryInst.SchedInfo; &schedInfo.OUTDESTTYPE = 2; &schedInfo.RUN_CNTL_ID = &RunControlId; &schedInfo.PROCESS_INSTANCE = &sProcessId; &schedInfo.OPRID = %OperatorId; &schedInfo.PRCSFILENAME = &ConQryInst.XMLDataFullName; &schedInfo.DIRLOCATION = &ConQryInst.GetOutDestinationDir(); &schedInfo.AE_ID = &ConQryInst.Const.SchedRequest_XMLP; /* Array for PSQuery prompts */ &CQPromptsArray = &ConQryInst.QueriesPromptsArray;    /*Loop through the Connected Query "Queries" and fill in each query's prompts as needed*/ For &ap = 1 To &CQPromptsArray.Len     &CQPromptRec = &CQPromptsArray.Get(&ap).QueryPromptRecord;     &CQPromptRec.GetField(1).Value = &nJobOpeningID;     &CQPromptRec.GetField(2).Value = %OperatorId; End-For; If &ConQryInst.Run(&CQPromptsArray, &ConQryInst.SchedInfo) Then       &sOutFile = &ConQryInst.OutProcessFileName; End-If; Local PSXP_RPTDEFNMANAGER:ReportDefn &oRptDefn; &ReportName = "JOB_XPRPT"; &TemplateId = "JOB_XPRPT"; &LanguageCd = %Language_User;    &oRptDefn = create PSXP_RPTDEFNMANAGER:ReportDefn(&ReportName); &oRptDefn.Get();    &oRptDefn.SetRuntimeDataXMLFile(&sOutFile); &oRptDefn.ProcessReport(&TemplateId, &LanguageCd, %Date, "");    CommitWork();    &oRptDefn.DisplayOutput(); catch Exception &Err Local string &sSub1, &sSub2, &sSub3, &sSub4, &sSub5; Evaluate &Err.SubstitutionCount When > 4 &sSub5 = &Err.GetSubstitution(5); When > 3 &sSub4 = &Err.GetSubstitution(4); When > 2 &sSub3 = &Err.GetSubstitution(3); When > 1 &sSub2 = &Err.GetSubstitution(2); When > 0 &sSub1 = &Err.GetSubstitution(1); End-Evaluate; Error MsgGet(&Err.MessageSetNumber, &Err.MessageNumber, &Err.ToString(), &sSub1, &sSub2, &sSub3, &sSub4, &sSub5); end-try;

PeopleCode to Generate BI Publisher report using PeopleSoft Connected Queries

import PSXP_RPTDEFNMANAGER:*;
import EP_FUNCTIONS:EP_Utilities2;
import PT_CONQRS:*;
Declare Function DeleteLocalFile PeopleCode PSXPFUNCLIB.FUNCLIB FieldFormula;
Declare Function GetDirSeparator PeopleCode PSXPFUNCLIB.FUNCLIB FieldFormula;
Function ShowReport(&sEmplID As string, &nAppraisalID As number)
  
   try
     
      Local PT_CONQRS:CONQRSMGR &oConQrsInst;
      Local array of PT_CONQRS:QUERYITEMPROMPT &CQPromptsArray;
      Local string &sRep$ortName, &Template, &sDirSep, &ServerPrefix, &OutStr, &LanguageCd;
      &sReportName = "XYZ_SP_XMLP";
      &Template = "XYZ_SP_XMLP_1";
      &LanguageCd = %Language_User;
     
      /* detect system directory separator */
      &sDirSep = GetDirSeparator();
      /* create process directory */
      CreateDirectory("XMLP", %FilePath_Relative);
     
      /* create report defn object */
      &oRptDefn = create PSXP_RPTDEFNMANAGER:ReportDefn(&sReportName);
      &oRptDefn.Get();
     
      /* output directory */
      &RptOutputDir = GetEnv("PS_SERVDIR") | &sDirSep | "files" | &sDirSep | "XMLP" | &sDirSep | UuidGen();
      &sOutputDir = &RptOutputDir | &sDirSep | "RptInst";
      &sDataDir = &RptOutputDir | &sDirSep | "Data";
      CreateDirectory(&sOutputDir, %FilePath_Absolute);
      CreateDirectory(&sDataDir, %FilePath_Absolute);
     
      &oRptDefn.OutDestination = &RptOutputDir;
     
      /*Create a location to place the connected query output file*/
      &CQXmlFile = GetFile(&RptOutputDir | &sDirSep | "XYZ_CQData.xml", "N", "UTF8", %FilePath_Absolute);
     
      /* get a reference to the Connected Query defn object */
      &oConQrsInst = create PT_CONQRS:CONQRSMGR("", "XYZ_SP_XMLP_CONQRY"); /*The blank "" parameter is filled in with OPRID if this is a private CQ. */
      &result = &oConQrsInst.Open(&oConQrsInst.Const.InitExisting);
      &CQPromptsArray = &oConQrsInst.QueriesPromptsArray;
     
      /*Loop through the Connected Query "Queries" and fill in each query's prompts as needed*/
      For &ap = 1 To &CQPromptsArray.Len
         &CQPromptRec = &CQPromptsArray.Get(&ap).QueryPromptRecord;
         &CQPromptRec.GetField(1).Value = &sEmplID;
         /* Start: 01.04.2014: Narvinder Singh */
         &CQPromptRec.GetField(2).Value = &nAppraisalID;
         /* end: 01.04.2014: Narvinder Singh */
      End-For;
     
      &OutStr = &oConQrsInst.RunToXMLFormattedString(&CQPromptsArray);
     
      /*The following 2 lines of code for formatting issues caused by extra carriage returns.  */
      &OutStr = Substitute(&OutStr, ">" | Char(13) | "~", ">");
      &OutStr = Substitute(&OutStr, ">" | Char(10) | "~", ">");
     
     
      /*Write the output to the xml file for use in the XML Publisher report*/
      &CQXmlFile.WriteLine(&OutStr);
      &CQXmlFile.Close();
      &oRptDefn.SetRuntimeDataXMLFile(&RptOutputDir | &sDirSep | "XYZ_CQData.xml"); /*Set XMLP report to use this file*/
     
      &oRptDefn.ProcessReport(&Template, &LanguageCd, %Date, "");
      CommitWork();
     
         
      /* cleanup your temp files to keep the application server clean*/
      RemoveDirectory(&RptOutputDir, %FilePath_Absolute + %Remove_Subtree);
     
     
   catch Exception &Err
      Local string &sSub1, &sSub2, &sSub3, &sSub4, &sSub5;
      Evaluate &Err.SubstitutionCount
      When > 4
         &sSub5 = &Err.GetSubstitution(5);
      When > 3
         &sSub4 = &Err.GetSubstitution(4);
      When > 2
         &sSub3 = &Err.GetSubstitution(3);
      When > 1
         &sSub2 = &Err.GetSubstitution(2);
      When > 0
         &sSub1 = &Err.GetSubstitution(1);
      End-Evaluate;
      Error MsgGet(&Err.MessageSetNumber, &Err.MessageNumber, &Err.ToString(), &sSub1, &sSub2, &sSub3, &sSub4, &sSub5);
   end-try;
  
  
End-Function;

CASE statement in WHERE clause of SQL

I'd situation where I had to search with either Item_ID or Item_Title. That is, if Item_ID is blank then only search with Item_Title, otherwise search with Item_ID only.

So to handle the situation CASE statement was used in WHERE clause of the SQL.

SELECT ITEM_DESCR
FROM ITEM_TBL
WHERE ITEM_TYPE=:1
    AND ( CASE WHEN (ITEM_ID=' ' OR ITEM_ID='' )
                 AND ITEM_TITLE =:2 THEN 1 WHEN (ITEM_ID<>' ' OR ITEM_ID<>'')
                 AND ITEM_ID = :3 THEN 1 ELSE 0 END=1);