11 Replies Latest reply on Jan 21, 2013 7:33 AM by user10569054

    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!
            • 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.
                      1 person found this helpful
                      • 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