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;