--- List All applications along with its current Patch set level
select patch_level, application_name
from fnd_product_installations fpi
, fnd_application_tl fat
where patch_level is not null
and fpi.application_id = fat.application_id
order by application_name;

---Display object versions
select US.NAME NAME,
       US.TYPE TYPE,
       substr(US.TEXT, instr(US.TEXT, '$Header')+9, 
	      instr(substr(US.TEXT, instr(US.TEXT, '$Header')+9), 
		    ' ', 1, 3)-1) VERSION
from   USER_SOURCE US
where  US.NAME like 'WF%'
and    US.TYPE in ('PACKAGE', 'PACKAGE BODY')
and    US.TEXT like '%$Header%'
order by 1, 2

select * from dba_source where name like 'PJI%' and line=2;

---Display file versions
select  a.FILENAME FILENAME, b.VERSION VERSION, b.CREATION_DATE
from ad_files a,
	 ad_file_versions b
 where a.file_id = b.file_id 
 and a.filename = 'OA.jsp'
 and b.creation_date = (select max(creation_date) 
 	 				    from ad_file_versions c
						where c.FILE_ID = b.FILE_ID)
--Display WF version
select TEXT Version
from   WF_RESOURCES
where  TYPE = 'WFTKN' and NAME = 'WF_VERSION'
and    LANGUAGE = 'US'


---Display only those programs that are in Pending-Standby status
select fcr.request_id, fusr.user_name, PVL.USER_concurrent_program_NAME
  from apps.fnd_concurrent_requests    fcr,
       apps.fnd_user                   fusr,
       APPS.FND_CONCURRENT_PROGRAMS_VL PVL
 where fcr.phase_code = 'P' AND fcr.STATUS_CODE = 'Q' --Include Only those which are on Pending-Standby status
   and fusr.user_id = fcr.requested_by
   AND FCR.HOLD_fLAG = 'N'
   AND CRM_TSTMP IS NOT NULL  --Exclude Scheduled programs
   AND PVL.CONCURRENT_PROGRAM_ID = FCR.CONCURRENT_PROGRAM_ID
 ORDER BY FUSR.USER_NAME;

---Display only Programs that are Scheduled to be run in near future
select fcr.request_id, fusr.user_name, PVL.USER_concurrent_program_NAME,fcr.requested_start_date
  from apps.fnd_concurrent_requests    fcr,
       apps.fnd_user                   fusr,
       APPS.FND_CONCURRENT_PROGRAMS_VL PVL
 where fcr.phase_code = 'P' AND fcr.STATUS_CODE = 'Q' 
   and fusr.user_id = fcr.requested_by
   AND FCR.HOLD_fLAG = 'N'
   AND CRM_TSTMP IS NULL  
   AND PVL.CONCURRENT_PROGRAM_ID = FCR.CONCURRENT_PROGRAM_ID
 ORDER BY FUSR.USER_NAME;


 -----------To find the the List of Employees who has a Responsibility -----------
SELECT distinct fu.user_name,FR.RESPONSIBILITY_NAME,papf.employee_number,papf.full_name
  FROM FND_USER FU,FND_USER_RESP_GROUPS FURG,FND_RESPONSIBILITY_TL FR,PER_ALL_PEOPLE_F PAPF
 WHERE FU.USER_ID = FURG.USER_ID AND
       FURG.RESPONSIBILITY_ID = FR.RESPONSIBILITY_ID AND
       FU.employee_id = PAPF.person_id AND
       FR.RESPONSIBILITY_NAME like '<Responsibility name>' and papf.employee_number like '<Employee number>';

 -----------To find the Responsibilities a Employee has(Emp No)-----------
 
 SELECT FU.USER_NAME,FRT.RESPONSIBILITY_NAME,FURG.END_DATE
   FROM FND_USER FU,FND_USER_RESP_GROUPS  FURG,FND_RESPONSIBILITY_TL FRT
  WHERE FU.USER_NAME = '<Login user name>' AND FU.USER_ID = FURG.USER_ID AND
        FURG.RESPONSIBILITY_ID = FRT.RESPONSIBILITY_ID;
        
----- To find which requests are tagged with which responisibility
Select Fcr.Responsibility_Id,
       Frt1.Responsibility_Name,Fcr.Description,Max(Fcr.Request_Date) Last_Run_Date
  From Fnd_Concurrent_Requests Fcr,Fnd_Responsibility_Tl   Frt1
 Where Fcr.Description In ('<Request name>') And
       Fcr.Responsibility_Id = Frt1.Responsibility_Id 
 Group By Fcr.Description,Fcr.Responsibility_Id,Frt1.Responsibility_Name;
          

-- List of menus tagged to a responsibility
select * from fnd_form_functions_vl where function_id in(
select function_id from fnd_menu_entries where menu_id =(
select menu_id	from Fnd_Responsibility
 where responsibility_id = (select Responsibility_id
					from Fnd_Responsibility_Tl where responsibility_name = '<Responsibility name>')));


---- Steps to fix Responsibility assignment bug
select ura.user_name, ura.role_name 
from wf_local_user_roles ur, wf_user_role_assignments ura 
where ur.user_name = ura.user_name
and ura.user_name in 'VANREN_H'
and ur.role_name = ura.role_name 
and ura.relationship_id = -1 
and ((ur.effective_start_date is null or ur.effective_start_date  <> ura.effective_start_date) 
or (ur.effective_end_date is null or ur.effective_end_date <> ura.effective_end_date));


UPDATE WF_USER_ROLE_ASSIGNMENTS set effective_end_date = to_date(null) 
where rowid in (select ura.rowid 
from wf_local_user_roles ur, wf_user_role_assignments ura 
where ur.user_name = ura.user_name
and ura.user_name in 'VANREN_H'  
and ur.role_name = ura.role_name 
and ura.relationship_id = -1 
and ((ur.effective_start_date is null or ur.effective_start_date <> ura.effective_start_date) 
or (ur.effective_end_date is null or ur.effective_end_date <> ura.effective_end_date)));

Commit

--Run Workflow Directory Services UserRole Validation from Oracle front end.

--set the parameters to yes