11 Replies Latest reply: Jan 21, 2013 1:33 AM by user10569054 RSS

    XML Publicher Report Output without Prompt Value

    user10569054
      Hello,
      I've one report(XML Publisher) which prints various data from PS_JOB,PS_PERSONAL_DATA and some other Performance tables.

      The Prompt Values are from a dynamic view which shows the Subordinate employees(emplids and names) of the login employee.
      The base query view i created has all the information of all active employees from all Business Unit. Now when i select when employee from prompt table the output values for that particular employee and is ok. But when i don't select anything(no table edit), it is not showing the output of my subordinates but for all the employee. That is Right because i've base report view is for all employees.
      Is there any way that if i dont select anything from prompt values i'll get only output of my subordinates only?

      Regards!
        • 1. Re: XML Publicher Report Output without Prompt Value
          HakanBiroglu
          Try extending you dynamic view with %OPRCLAUS

          See PeopleBooks on detailed information
          PeopleBooks > PeopleTools 8.52: PeopleCode Language Reference > Meta-SQL Elements > %OPRCLAUS

          Regards,

          Hakan
          • 2. Re: XML Publicher Report Output without Prompt Value
            user10569054
            Thanks for the Reply.

            Below is my query
            SELECT A.emplid,
                   A.Name_Display,
                   K.descr,
                   T4.effdt join_date,
                   D.grade,
                   G.descr Dept,
                   D.effdt Last_prom_dt,
                   period_begin_dt,
                   AA.EMPLID MGR_ID_VW,
                   AA.NAME_DISPLAY NAME_DISPLAY_MGR,
                 
                   ab.supervisor_id EMPLID_FM_VW,
                   FMGN.name_display NAME_DISPLAY_FM,
                  FROM PS_PERSONAL_DATA A,
                   (SELECT EMPLID, NAME_DISPLAY FROM PS_PERSONAL_DATA) AA,
                   (SELECT ad.emplid,
                           DECODE(ad.supervisor_id, ' ', 'XXXX', ad.supervisor_id) supervisor_id
                      FROM ps_job ad
                     WHERE ad.effdt = (SELECT MAX(t3.effdt)
                                         FROM ps_job t3
                                        WHERE ad.emplid = t3.emplid)) AB,
                   (SELECT EMPLID, NAME_DISPLAY FROM PS_PERSONAL_DATA) FMGN,
                   (SELECT emplid, effdt
                      FROM ps_job T3
                     WHERE effdt =
                           (SELECT MIN(effdt) FROM PS_JOB T5 WHERE T5.emplid = T3.emplid)) T4,
                   PS_EP_APPR B,
                   PS_EP_APPR_ITEM C,
                   PS_JOB D,
                   PS_DEPT_TBL G,
                   PS_BUSUNIT_HR_VW K
             WHERE A.EMPLID = B.EMPLID
               AND B.EP_APPRAISAL_ID = C.EP_APPRAISAL_ID
               AND A.EMPLID = D.EMPLID
               AND AA.EMPLID = D.SUPERVISOR_ID
               AND D.supervisor_id = AB.emplid
               AND FMGN.Emplid = AB.supervisor_id
               AND T4.emplid = D.emplid
               AND D.deptid = G.Deptid
               AND D.business_unit = K.Business_unit
               AND c.EP_ROLE = 'FA'
               AND C.EP_SECTION_TYPE = 'NPART08'
               AND EP_ITEM_ID != 'HALFYEAR'
               AND D.effdt =
                   (SELECT MAX(t1.effdt) FROM PS_JOB t1 WHERE t1.emplid = D.EMPLID)
                   
              ORDER BY A.emplid
            Now if i don't pass anything it returns all data. In my Prompt view i can return subordinates of the person who is logged in.
            What i want is if i doesn't pass any value only his subordinate data should return but query is returning all data.
            I tried the subordinate query in above main query using another clause like A.emplid in (SUBORDINATE_QUERY).
            The Prompt has Dynamic view assigned, in dynamic view I'm taking OPRID=%OPeratorID and for prompt it works well.
            I replace %OPeratorID with hard coded employee id in sub query of main query and it worked well but when i change it to %OPeratorid output is blank.

            I tried your suggestion,i'm on PeopleTool 8.49.27. There is %OPRCLAUSE but when i use it in subquery in main query it gives me error.

            Regards!
            • 3. Re: XML Publicher Report Output without Prompt Value
              HakanBiroglu
              and what is the error?
              • 4. Re: XML Publicher Report Output without Prompt Value
                user10569054
                Error

                Error: IRS_RA_LOC_REP - SQL Error. Error Position: 2878 Return: 907 - ORA-00907: missing right parenthesis

                my subquery is
                SELECT EMPLID 
                  FROM( 
                 SELECT A.EMPLID 
                 , A.NAME_DISPLAY 
                 ,A.FIRST_NAME 
                 , A.LAST_NAME 
                  FROM PS_NAMES A INNER JOIN PS_JOB B ON B.EMPLID = A.EMPLID 
                   AND B.EMPLID = A.EMPLID 
                   AND B.EFFDT = ( 
                 SELECT MAX(CA.EFFDT) 
                  FROM PS_JOB CA 
                 WHERE CA.EMPLID = B.EMPLID 
                   AND CA.EFFDT <= %CurrentDateIn ) 
                 WHERE A.NAME_TYPE = 'PRI' 
                   AND A.EFFDT = ( 
                 SELECT MAX(B.EFFDT) 
                  FROM PS_NAMES B 
                 WHERE B.EMPLID = A.EMPLID 
                   AND B.NAME_TYPE = A.NAME_TYPE 
                   AND B.EFFDT <= %CurrentDateIn ) 
                   AND B.SUPERVISOR_ID = ( 
                 SELECT T1.EMPLID 
                  FROM PSOPRDEFN T1 
                 WHERE T1.OPRID = %OperatorId) 
                    OR A.EMPLID = ( 
                 SELECT T2.EMPLID 
                  FROM PSOPRDEFN T2 
                 WHERE T2.OPRID = %OperatorId) 
                    OR B.SUPERVISOR_ID IN ( 
                 SELECT EMPLID 
                  FROM PS_JOB 
                 WHERE SUPERVISOR_ID = ( 
                 SELECT T3.EMPLID 
                  FROM PSOPRDEFN T3 
                 WHERE T3.OPRID = %OperatorId) ) )
                Here i replace %OperatorId with %OPRCLAUSE and the error appeared.

                This is the SQL i'm using in Main query in Where clause like A.emplid in (ABOVE_SUB_QUERY)

                Regards!
                • 5. Re: XML Publicher Report Output without Prompt Value
                  HakanBiroglu
                  >
                  Here i replace %OperatorId with %OPRCLAUSE and the error appeared.
                  >
                  You need to replace the entire equation instead of just %OperatorID

                  Notice the extra parenthesis this creates in below example.

                  See PeopleBooks
                  Here is an example:

                  SELECT EMPLID, ABSENCE_TYPE, OPRID FROM PS_ABSENCE_HIST WHERE %OPRCLAUSE AND
                  (EMPLID='8001' AND ABSENCE_TYPE='CNF')

                  This code expands to:
                  SELECT EMPLID, ABSENCE_TYPE, OPRID FROM PS_ABSENCE_HIST WHERE ( OPRID =
                  'PTDMO') AND (EMPLID='8001' AND ABSENCE_TYPE='CNF') ORDER BY EMPLID, ABSENCE_TYPE
                  • 6. Re: XML Publicher Report Output without Prompt Value
                    user10569054
                    Thanks!
                    I'll give a try and let you know.
                    • 7. Re: XML Publicher Report Output without Prompt Value
                      user10569054
                      Below Query gave no error while building. I changed OPRID instead Emplid in Last 3 sub-queries.
                      But the Report Excel Output is Blank
                       SELECT EMPLID 
                        FROM ( 
                       SELECT A.EMPLID 
                       , A.NAME_DISPLAY 
                       , A.FIRST_NAME 
                       , A.LAST_NAME 
                        FROM PS_NAMES A INNER JOIN PS_JOB B ON B.EMPLID = A.EMPLID 
                         AND B.EMPLID = A.EMPLID 
                         AND B.EFFDT = ( 
                       SELECT MAX(CA.EFFDT) 
                        FROM PS_JOB CA 
                       WHERE CA.EMPLID = B.EMPLID 
                         AND CA.EFFDT <= %CurrentDateIn ) 
                       WHERE A.NAME_TYPE = 'PRI' 
                         AND A.EFFDT = ( 
                       SELECT MAX(B.EFFDT) 
                        FROM PS_NAMES B 
                       WHERE B.EMPLID = A.EMPLID 
                         AND B.NAME_TYPE = A.NAME_TYPE 
                         AND B.EFFDT <= %CurrentDateIn ) 
                         AND B.SUPERVISOR_ID = ( 
                       SELECT T1.OPRID 
                        FROM PSOPRDEFN T1 
                       WHERE %oprclause 
                         AND (T1.OPRID = %OperatorId) ) 
                          OR A.EMPLID = ( 
                       SELECT T2.OPRID 
                        FROM PSOPRDEFN T2 
                       WHERE %oprclause 
                         AND (T2.OPRID = %OperatorId) ) 
                          OR B.SUPERVISOR_ID IN ( 
                       SELECT EMPLID 
                        FROM PS_JOB 
                       WHERE SUPERVISOR_ID = ( 
                       SELECT T3.OPRID 
                        FROM PSOPRDEFN T3 
                       WHERE %oprclause 
                         AND (T3.OPRID = %OperatorId) ) ) )
                      It also resolves Meta Sql to PS in PeopleTool but in Query viewer when i run the report, the o/p is blank.

                      Regards!
                      • 8. Re: XML Publicher Report Output without Prompt Value
                        HakanBiroglu
                        A few things:

                        As stated in my previous post %OPERCLAUSE expands to (OPRID ='CurrentUserRunningThisQuery')
                        so your where clauses seam a bit redundant
                        FROM PSOPRDEFN T3
                        WHERE %oprclause
                        AND (T3.OPRID = %OperatorId)

                        this will expend to

                        FROM PSOPRDEFN T3
                        WHERE (T3.OPRID ='CurrentUserRunningThisQuery')
                        AND (T3.OPRID = 'CurrentUserRunningThisQuery')

                        I would say remove the line
                        AND (T3.OPRID = %OperatorId)

                        Are you running the query logged in as a user that would return data for this query? Do not run it with a superuser/admin user, because these users should not have transactional data.
                        • 9. Re: XML Publicher Report Output without Prompt Value
                          user10569054
                          Thanks. I did try that before but the view didn't build but i don't how today the view built successfully.
                          But when i tried to run the query with a normal user who's having subordinate the excel output is still blank.
                          One more thing, when i checked Resolve Meta SQL in Application designer, the %oprclause doesn't resolve to PS.

                          below is my complete query..with subqeury for finding current user..
                          SELECT A.emplid,
                                 A.Name_Display,
                                 K.descr,
                                 T4.effdt join_date,
                                 D.grade,
                                 G.descr Dept,
                                 D.effdt Last_prom_dt,
                                 period_begin_dt,
                                 AA.EMPLID MGR_ID_VW,
                                 AA.NAME_DISPLAY NAME_DISPLAY_MGR,
                                 ab.supervisor_id EMPLID_FM_VW,
                                 FMGN.name_display NAME_DISPLAY_FM,
                            FROM PS_PERSONAL_DATA A,
                                 (SELECT EMPLID, NAME_DISPLAY FROM PS_PERSONAL_DATA) AA,
                                 (SELECT ad.emplid,
                                         DECODE(ad.supervisor_id, ' ', 'XXXX', ad.supervisor_id) supervisor_id
                                    FROM ps_job ad
                                   WHERE ad.effdt = (SELECT MAX(t3.effdt)
                                                       FROM ps_job t3
                                                      WHERE ad.emplid = t3.emplid)) AB,
                                 (SELECT EMPLID, NAME_DISPLAY FROM PS_PERSONAL_DATA) FMGN,
                                 (SELECT emplid, effdt
                                    FROM ps_job T3
                                   WHERE effdt =
                                         (SELECT MIN(effdt) FROM PS_JOB T5 WHERE T5.emplid = T3.emplid)) T4,
                                 PS_EP_APPR B,
                                 PS_EP_APPR_ITEM C,
                                 PS_JOB D,
                                 PS_DEPT_TBL G,
                                 PS_BUSUNIT_HR_VW K
                           WHERE A.EMPLID = B.EMPLID
                             AND B.EP_APPRAISAL_ID = C.EP_APPRAISAL_ID
                             AND A.EMPLID = D.EMPLID
                             AND AA.EMPLID = D.SUPERVISOR_ID
                             AND D.supervisor_id = AB.emplid
                             AND FMGN.Emplid = AB.supervisor_id
                             AND c.ep_appraisal_id = pnt.EP_APPRAISAL_ID
                             AND T4.emplid = D.emplid
                             AND D.deptid = G.Deptid
                             AND D.business_unit = K.Business_unit
                             AND D.effdt =
                                 (SELECT MAX(t1.effdt) FROM PS_JOB t1 WHERE t1.emplid = D.EMPLID)
                             AND A.EMPLID IN
                               (SELECT EMPLID                                                            ----------here comes subquery for finding logged in user
                                    FROM (SELECT B.EMPLID
                                            FROM PS_NAMES A
                                           INNER JOIN PS_JOB B ON B.EMPLID = A.EMPLID
                                                              AND B.EMPLID = A.EMPLID
                                                              AND B.EFFDT =
                                                                  (SELECT MAX(CA.EFFDT)
                                                                     FROM PS_JOB CA
                                                                    WHERE CA.EMPLID = B.EMPLID
                                                                      AND CA.EFFDT <= %CurrentDateIn)
                                           WHERE A.NAME_TYPE = 'PRI'
                                             AND A.EFFDT = (SELECT MAX(B.EFFDT)
                                                              FROM PS_NAMES B
                                                             WHERE B.EMPLID = A.EMPLID
                                                               AND B.NAME_TYPE = A.NAME_TYPE
                                                               AND B.EFFDT <= %CurrentDateIn)
                                             AND B.SUPERVISOR_ID =
                                                 (SELECT T1.OPRID FROM PSOPRDEFN T1 WHERE %oprclause)
                                              OR A.EMPLID =
                                                 (SELECT T2.OPRID FROM PSOPRDEFN T2 WHERE %oprclause)
                                              OR B.SUPERVISOR_ID IN
                                                 (SELECT EMPLID
                                                    FROM PS_JOB
                                                   WHERE SUPERVISOR_ID =
                                                         (SELECT T3.OPRID
                                                            FROM PSOPRDEFN T3
                                                           WHERE %oprclause))))
                           ORDER BY A.emplid
                          Regards!
                          • 10. Re: XML Publicher Report Output without Prompt Value
                            user10569054
                            When i check log(Application designer) after building view i found this...

                            B.SUPERVISOR_ID = ( SELECT T1.OPRID FROM PSOPRDEFN T1 WHERE
                            OPRID='PS' ) OR A.EMPLID = ( SELECT T2.OPRID FROM PSOPRDEFN T2 WHERE
                            OPRID='PS' ) OR B.SUPERVISOR_ID IN ( SELECT EMPLID FROM PS_JOB WHERE
                            SUPERVISOR_ID = ( SELECT T3.OPRID FROM PSOPRDEFN T3 WHERE OPRID='PS'
                            ) ) ) ) ORDER BY A.EMPLID

                            Regards!
                            • 11. Re: XML Publicher Report Output without Prompt Value
                              user10569054
                              Success at Last.
                              This is what i needed.
                              SELECT SUBSTR(sys_context('USERENV' 
                               , 'CLIENT_INFO') 
                               , 1 
                               , INSTR(sys_context('USERENV' 
                               , 'CLIENT_INFO') 
                               , ',' 
                               , 1 
                               , 1) - 1) 
                                FROM DUAL
                              Thanks Jim Marion.
                              http://jjmpsj.blogspot.com/2006/09/peoplesoft-on-oracle-whoami.html

                              Edited by: user10569054 on Jan 21, 2013 12:06 PM

                              Edited by: user10569054 on Jan 21, 2013 12:07 PM

                              Edited by: user10569054 on Jan 21, 2013 2:33 PM