REM RMINC_RESPONSIBILITIES.sql
REM
REM Copyright Reed-Matthews, Inc.
REM

REM Responsibilities Report (RMINC

REM Lets you run the responsibilities report but on a
REM subset of responsibilities instead of all of them
REM
REM Parameters &1-&5 - you fill in responsibility names
REM or subsets of names
REM Parameters &6 and &7 - look for changes that were
REM made between these dates
set heading on
ttitle CENTER 'Recently Updated Responsibilities Assigned to Users' -
RIGHT ' Page:' FORMAT 999 sql.pno SKIP 2 

column RESPONSIBILITY format a30 heading 'RESPONSIBILITY'
column APP format a20 heading 'APPLICATION'
column USER format a25 heading 'USER'
column CHANGED format a25 heading 'LAST CHANGED BY'
column UPDATED format date heading 'UPDATED'
column ENDDATE format date heading 'END DATE'

break on RESPONSIBILITY skip 1

SELECT
  substr(f.application_short_name,1,20) APP,
  substr(R.RESPONSIBILITY_NAME,1,30) RESPONSIBILITY,
  substr(U.USER_NAME,1,30) "USER",
  substr(G.USER_NAME,1,30) CHANGED,
  UR.LAST_UPDATE_DATE UPDATED,
  UR.END_DATE ENDDATE
FROM
  FND_APPLICATION f,
  FND_RESPONSIBILITY_VL R,
  FND_USER U,
  FND_USER G,
  FND_USER_RESPONSIBILITY UR
WHERE
    (UPPER(R.RESPONSIBILITY_NAME) LIKE '%&1%'
  OR UPPER(R.RESPONSIBILITY_NAME) LIKE '%&2%' 
  OR UPPER(R.RESPONSIBILITY_NAME) LIKE '%&3%'
  OR UPPER(R.RESPONSIBILITY_NAME) LIKE '%&4%'
  OR UPPER(R.RESPONSIBILITY_NAME) LIKE '%&5%')
  AND UR.USER_ID=U.USER_ID AND
  UR.LAST_UPDATED_BY=G.USER_ID AND 
  UR.RESPONSIBILITY_ID=R.RESPONSIBILITY_ID
  AND UR.LAST_UPDATE_DATE BETWEEN '&6' and '&7'
  AND (U.END_DATE > SYSDATE OR U.END_DATE IS NULL)
  AND (R.END_DATE > SYSDATE OR R.END_DATE IS NULL)
  and r.application_id=f.application_id
  order by RESPONSIBILITY,USER;