Useful Apps Technical SQL Queries

By | January 20, 2015

Summary

Following is the list of SQL Queries that i have mentioned on this post. These are requirement that we often come across while we are on some support or implementation project. Therefore i decided to compile them in one place in ready to use format.

Flashback Query to get old state of Data
Get Profile Values set for a responsibility
Get Full List of Responsibilities
Get Menu List against responsibilities
Get Request Group against Responsibilities

Query to Find Profile Options Values Against a Responsibility

SELECT  FPOV.USER_PROFILE_OPTION_NAME ,
FPO.PROFILE_OPTION_NAME ,
NVL(R.RESPONSIBILITY_NAME,FPO_VAL.LEVEL_VALUE) RESPONSIBILITY_LEVEL,
FPO_VAL.PROFILE_OPTION_VALUE VALUE,
FPOV.SQL_VALIDATION
FROM   APPS.FND_PROFILE_OPTIONS FPO ,
APPS.FND_PROFILE_OPTIONS_VL FPOV ,
APPS.FND_PROFILE_OPTION_VALUES FPO_VAL ,
APPS.FND_RESPONSIBILITY_VL R ,
APPS.FND_APPLICATION APP,
APPS.FND_USER FU ,
APPS.FND_USER FU1
WHERE FPO.APPLICATION_ID = FPO_VAL.APPLICATION_ID
AND FPO.PROFILE_OPTION_NAME = FPOV.PROFILE_OPTION_NAME
AND FPO.PROFILE_OPTION_ID = FPO_VAL.PROFILE_OPTION_ID
AND FPO_VAL.LAST_UPDATED_BY = FU.USER_ID (+)
AND FPO_VAL.LEVEL_VALUE = FU1.USER_ID (+)
AND FPO_VAL.LEVEL_VALUE = R.RESPONSIBILITY_ID (+)
AND FPO_VAL.LEVEL_VALUE = APP.APPLICATION_ID (+)
AND R.RESPONSIBILITY_NAME = ‘LSG TRAINING AND DEVELOPMENT ADMINISTRATOR’
AND FPO_VAL.LEVEL_ID = 10003
ORDER BY FPOV.USER_PROFILE_OPTION_NAME, FPO_VAL.LEVEL_ID;

Back to top

Select the List of Responsibilities

SELECT    RESPONSIBILITY_ID,RESPONSIBILITY_NAME
FROM       FND_RESPONSIBILITY_TL
WHERE  LANGUAGE = ‘US’
AND         (              (UPPER(RESPONSIBILITY_NAME) LIKE ‘%LEARNER%’ )    )
AND       CREATED_BY <> 1

Back to top

Select Distinct Menus Used in one or List of Responsibilities

SELECT DISTINCT R.MENU_ID, MT.USER_MENU_NAME
FROM FND_RESPONSIBILITY_TL RTL, FND_RESPONSIBILITY R,FND_MENUS M, FND_MENUS_TL MT
WHERE RTL.LANGUAGE = ‘US’
AND (      (UPPER(RESPONSIBILITY_NAME) LIKE ‘%LEARNER%’ )
           OR (UPPER(RESPONSIBILITY_NAME) LIKE ‘%MANAGER%’ )
            )
AND RTL.CREATED_BY <> 1
AND R.MENU_ID = M.MENU_ID
AND M.MENU_ID = MT.MENU_ID
AND MT.LANGUAGE = ‘US’
AND R.RESPONSIBILITY_ID = RTL.RESPONSIBILITY_ID

Back to top

Select Reqeust Groups in One or List of Responsibilties

SELECT DISTINCT REQUEST_GROUP_NAME

FROM FND_REQUEST_GROUPS
WHERE REQUEST_GROUP_ID IN

(

SELECT R.REQUEST_GROUP_ID–DISTINCT R.MENU_ID, MT.USER_MENU_NAME
FROM FND_RESPONSIBILITY_TL RTL, FND_RESPONSIBILITY R,FND_MENUS M, FND_MENUS_TL MT
WHERE RTL.LANGUAGE = ‘US’
AND (
UPPER(RESPONSIBILITY_NAME) LIKE ‘UA%’
OR (UPPER(RESPONSIBILITY_NAME) LIKE ‘%MANAGER%’ )

)
AND RTL.CREATED_BY <> 1
AND R.MENU_ID = M.MENU_ID
AND M.MENU_ID = MT.MENU_ID
AND MT.LANGUAGE = ‘US’
AND R.RESPONSIBILITY_ID = RTL.RESPONSIBILITY_ID
AND R.REQUEST_GROUP_ID IS NOT NULL

)

Flash Back Query to Find old State of the Data

SELECT   *
FROM LSG_EMPLOYEE_STG1
AS OF TIMESTAMP TO_TIMESTAMP(‘2015-08-04 09:11:00′,’YYYY-MM-DD HH24:MI:SS’)
WHERE EMPLOYEE_NUMBER = ‘70106’

Find the List Users that have a particular Responsibility

select rg.user_id,fu.user_name,ppf.employee_number,ppf.full_name,rt.responsibility_name
from fnd_responsibility_tl rt,FND_USER_RESP_GROUPS_DIRECT rg,fnd_user fu, per_all_people_f ppf
where upper(RESPONSIBILITY_NAME) like ‘%APPROVALS MANAGEMENT BUSINESS ANALYST’
and rt.RESPONSIBILITY_ID = rg.RESPONSIBILITY_ID
and rg.user_id = fu.user_id
and fu.employee_id = ppf.person_id
and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date

Find the Values defined against an independent Value Set

select vs.FLEX_VALUE_SET_NAME,vsv.FLEX_VALUE_MEANING,vsv.description
from FND_FLEX_VALUE_SETS vs, FND_FLEX_VALUES_VL vsv
where (     vs.FLEX_VALUE_SET_NAME like ‘LSG_%_DEPARTMENT’  or
vs.FLEX_VALUE_SET_NAME like ‘LSG_%_COST_CENTER’
)
and vs.FLEX_VALUE_SET_NAME not like ‘LSG_%_PAYROLL_COST_CENTER’
and vs.FLEX_VALUE_SET_ID = vsv.FLEX_VALUE_SET_ID
and vsv.ENABLED_FLAG = ‘Y’

Get the Running Total

create table runtotal(dt date,sale_amt number)

insert into runtotal values(’01-DEC-2016′,12)
insert into runtotal values(’02-DEC-2016′,13)
insert into runtotal values(’03-DEC-2016′,14)
insert into runtotal values(’04-DEC-2016′,15)
insert into runtotal values(’05-DEC-2016′,16)
insert into runtotal values(’06-DEC-2016′,17)

select o.dt,sum(o.sale_amt),sum(o.sale_amt)+
nvl((select sum(i.sale_amt) from runtotal i where i.dt < o.dt),0) running_sum
from runtotal o
group by dt
order by dt asc

Template for Writing a Package and Procedure for being used in Concurrent Program

Package Specification

CREATE OR REPLACE PACKAGE APPS.LSG_PKG_SAMPLE
AS

PROCEDURE LSG_PROC_CONC_PROG_SAMPLE ( p_from_date IN DATE, p_to_date IN DATE,p_Cnt_Records OUT NUMBER,
p_Status OUT VARCHAR2);

FUNCTION LSG_FUNC ( p_from_date IN DATE,p_to_date IN DATE) RETURN VARCHAR2;

END LSG_PKG_SAMPLE;
/

Package Body

CREATE OR REPLACE PACKAGE BODY APPS.LSG_PKG_SAMPLE
AS

PROCEDURE LSG_PROC_CONC_PROG_SAMPLE (p_from_date IN DATE,p_to_date IN DATE,p_Cnt_Records OUT NUMBER,
p_Status OUT VARCHAR2
)
IS
BEGIN

fnd_file.put_line(fnd_file.output,’Sample Output Line’);
exception
when others then
dbms_output.put_line(‘Exception’);

end;
FUNCTION LSG_FUNC (P_FROM_DATE IN DATE,P_TO_DATE IN DATE) RETURN VARCHAR2

IS

BEGIN
NULL;
END;

END LSG_PKG_SAMPLE;
/

Back to top