title: PeopleSoft prompt table adding eff_status criteria
body: <p>I am using a custom prompt table (PS_AVZ_JD_JBCD_SRC) based on the JOBCODE_TBL record:
      SELECT a.jobcode 
      , a.descr 
      , a.eff_status 
      , a.flsa_status 
      ,a.job_family 
      FROM ps_jobcode_tbl a 
      WHERE 1=1 
       AND A.setid='GLOBL' 
       AND a.effdt = ( 
      SELECT MAX(a_ed.effdt) 
        FROM ps_jobcode_tbl a_ed 
      WHERE a_ed.jobcode = a.jobcode 
       AND a_ed.effdt &lt;= sysdate)</p>
  
  <p>This ought to return both active and inactive jobcodes (I am not specifying this in the view).  However, when I click the prompt button, only active jobcodes are returned.  A SQL Trace shows the following SQL being fired:
      SELECT  /*+ FIRST_ROWS */ JOBCODE, DESCR, EFF_STATUS FROM PS_AVZ_JD_JBCD_SRC     WHERE       EFF_STATUS&lt;>'I' ORDER BY JOBCODE</p>
  
  <p>Where is the eff_status criteria coming from?  The prompt is on a non-key field if that matters...</p>