This discussion is archived
11 Replies Latest reply: Jan 20, 2013 11:33 PM by user10569054 RSS

XML Publicher Report Output without Prompt Value

user10569054 Newbie
Currently Being Moderated
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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    and what is the error?
  • 4. Re: XML Publicher Report Output without Prompt Value
    user10569054 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    >
    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 Newbie
    Currently Being Moderated
    Thanks!
    I'll give a try and let you know.
  • 7. Re: XML Publicher Report Output without Prompt Value
    user10569054 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points