[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select 'x' into dummy
from pa_implementations;
SELECT parent_organization_id,parent_org_name
INTO x_org_id,x_org_name
FROM ( SELECT DISTINCT parent_organization_id,parent_org_name
FROM pa_rep_org_util_v
ORDER BY parent_org_name )
WHERE ROWNUM = 1;
SELECT rsd.resource_name,rsd.person_id
INTO x_login_person_name,x_login_person_id
FROM pa_resources_denorm rsd, fnd_user usr
WHERE rsd.person_id = usr.employee_id
AND usr.user_id = fnd_global.user_id
AND rsd.resource_effective_end_date >= sysdate
AND rownum < 2
;
SELECT USER_PROFILE_OPTION_NAME INTO l_user_profile_option_name1
FROM fnd_profile_options_tl
WHERE profile_option_name='PA_ORG_UTIL_DEF_CALC_METHOD'
AND language=userenv('LANG');
SELECT USER_PROFILE_OPTION_NAME INTO l_user_profile_option_name2
FROM fnd_profile_options_tl
WHERE profile_option_name='PA_ORG_UTIL_DEF_PERIOD_TYPE'
AND language=userenv('LANG');
SELECT USER_PROFILE_OPTION_NAME INTO l_user_profile_option_name3
FROM fnd_profile_options_tl
WHERE profile_option_name='PA_GLOBAL_WEEK_START_DAY'
AND language=userenv('LANG');
select forecast_thru_date
into l_forecast_thru_date
from pa_utilization_options ut ;
SELECT meaning
INTO x_def_period_typ_desc
FROM pa_lookups
WHERE lookup_type = 'PA_REP_PERIOD_TYPES'
AND lookup_code = l_period_type;
SELECT paperiod.period_name,
paperiod.period_year,
paperiod.status_meaning
INTO l_prd_name,l_prd_yr,l_meaning
FROM pa_periods_v paperiod,
pa_implementations imp,
gl_period_statuses glpersts
WHERE glpersts.period_type = imp.pa_period_type
AND imp.set_of_books_id = paperiod.set_of_books_id
AND glpersts.set_of_books_id = imp.set_of_books_id
AND glpersts.period_name = paperiod.period_name
AND glpersts.period_year = paperiod.period_year
AND glpersts.application_id = 275
AND l_default_date BETWEEN paperiod.pa_start_date AND paperiod.pa_end_date
AND ROWNUM=1
ORDER BY paperiod.period_year; */
SELECT paperiod.period_name,
paperiod.period_year,
paperiod.status_meaning
INTO l_prd_name,l_prd_yr,l_meaning
FROM pa_periods_v paperiod
WHERE l_default_date BETWEEN paperiod.pa_start_date AND paperiod.pa_end_date
AND ROWNUM=1
ORDER BY paperiod.period_year;
SELECT glper.period_name,glper.period_year,gllkups.meaning
INTO l_prd_name,l_prd_yr,l_meaning
FROM pa_implementations imp,
gl_sets_of_books gl,
gl_periods glper,
gl_period_statuses glst,
gl_lookups gllkups
WHERE imp.set_of_books_id = gl.set_of_books_id
AND gl.period_set_name = glper.period_set_name
AND gl.accounted_period_type = glper.period_type
AND glper.period_name = glst.period_name
AND glper.period_type = glst.period_type
AND glst.set_of_books_id=imp.set_of_books_id
AND glst.application_id = PA_Period_Process_PKG.Application_ID
AND gllkups.lookup_code = glst.closing_status
AND gllkups.lookup_type = 'CLOSING_STATUS'
/* Bug 2288460 - Start */
/* Added the following clause */
AND glper.adjustment_period_flag = 'N'
/* Bug 2288460 - End */
AND l_default_date BETWEEN glper.start_date AND glper.end_date
AND ROWNUM=1
ORDER BY glper.period_year;
SELECT
TO_CHAR(NEXT_DAY(l_default_date, NVL(TO_NUMBER(FND_PROFILE.VALUE('PA_GLOBAL_WEEK_START_DAY')),0)) -1,l_date_format)
INTO x_def_period_name
FROM sys.dual;
SELECT
TO_CHAR( NEXT_DAY(l_default_date, NVL( TO_NUMBER(FND_PROFILE.VALUE('PA_GLOBAL_WEEK_START_DAY')),0)) -1, 'DD-MON-YYYY')
INTO x_def_period_name
FROM sys.dual;
SELECT glper.quarter_num
INTO l_quarter_num
FROM pa_implementations imp,
gl_sets_of_books gl,
gl_periods glper,
gl_period_statuses glst
WHERE imp.set_of_books_id = gl.set_of_books_id
AND gl.period_set_name = glper.period_set_name
AND gl.accounted_period_type = glper.period_type
AND glper.period_name = glst.period_name
AND glper.period_type = glst.period_type
AND glst.set_of_books_id=imp.set_of_books_id
AND glst.application_id = PA_Period_Process_PKG.Application_ID
/* Bug 2288460 - Start */
/* Added the following clause */
AND glper.adjustment_period_flag = 'N'
/* Bug 2288460 - End */
AND l_default_date BETWEEN glper.start_date AND glper.end_date
AND ROWNUM=1
ORDER BY glper.period_year;
p_select_yr IN NUMBER,
p_period_name IN VARCHAR2,
p_calling_mode IN VARCHAR2,
p_showprctgby IN VARCHAR2,
x_org_id OUT NOCOPY NUMBER,
x_period_type OUT NOCOPY VARCHAR2,
x_period_name OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
l_invalid_value EXCEPTION;
PA_REP_UTIL_GLOB.update_util_cache; -- Bug 2447797 added this call
SELECT distinct parent_organization_id
INTO x_org_id
FROM pa_rep_org_util_v
WHERE parent_org_name = p_org_name;
SELECT period_type
INTO l_period_type
FROM pa_rep_period_types_v
WHERE period_type_desc = p_period_type_desc;
SELECT period_year
INTO l_period_year
FROM pa_rep_period_years_v
WHERE period_type = l_period_type
AND period_year = p_select_yr
AND ROWNUM = 1;
SELECT period_year
INTO l_period_year
FROM pa_rep_periods_v
WHERE period_type = l_period_type
AND period_year = p_select_yr
AND ROWNUM = 1;
SELECT period_name
INTO x_period_name
FROM pa_rep_periods_v
WHERE period_type = l_period_type
AND ( period_name = p_period_name
OR period_name||' '||period_status = p_period_name)
AND period_year = p_select_yr;
SELECT TO_CHAR(mon_or_qtr)
INTO x_period_name
FROM pa_rep_periods_v
WHERE period_type = l_period_type
AND ( period_name = p_period_name
OR period_name||' '||period_status = p_period_name)
AND period_year = p_select_yr;
PA_REP_UTIL_GLOB.update_util_cache; -- Bug 2447797 added this call
SELECT organization_id
INTO l_org_id
FROM pa_rep_util_res_orgs_v
WHERE organization_name = p_org_name;
SELECT manager_id
INTO l_mgr_id
FROM pa_rep_util_mgr_org_v
WHERE manager_name = p_mgr_name;
SELECT PERIOD_TYPE
INTO l_period_type
FROM pa_rep_period_types_v
WHERE period_type_desc = p_period_type_desc;
SELECT period_year
INTO l_period_year
FROM pa_rep_period_years_v
WHERE period_type = l_period_type
AND period_year = p_period_year
AND ROWNUM =1;
SELECT period_year
INTO l_period_year
FROM pa_rep_periods_v
WHERE period_type = l_period_type
AND period_year = p_period_year
AND ROWNUM =1;
SELECT period_name
INTO l_period_name
FROM pa_rep_periods_v
WHERE period_type = l_period_type
AND ( period_name = p_period_name
OR period_name||' '||period_status = p_period_name)
AND period_year = p_period_year;
SELECT MON_OR_QTR
INTO l_prd_quarter
FROM pa_rep_periods_v
WHERE period_type = l_period_type
AND ( period_name = p_period_name
OR period_name||' '||period_status = p_period_name)
AND period_year = p_period_year;
SELECT ge_week_dt
INTO l_glb_wek_dt
FROM pa_rep_periods_v
WHERE period_type = l_period_type
AND ( period_name = p_period_name
OR period_name||' '||period_status = p_period_name)
AND period_year = p_period_year;
/* CURSOR C1 IS SELECT glper.period_name,glper.period_year,glst.closing_status,glper.quarter_num,glst.effective_period_num
FROM pa_implementations imp,
gl_sets_of_books gl,
gl_periods glper,
gl_period_statuses glst
WHERE imp.set_of_books_id = gl.set_of_books_id
AND gl.period_set_name = glper.period_set_name
AND imp.pa_period_type = glper.period_type
AND glper.period_name = glst.period_name
AND glper.period_type = glst.period_type
AND glst.set_of_books_id=imp.set_of_books_id
AND glst.closing_status ='O'
AND glst.application_id = 275
ORDER BY glper.period_year;*/
CURSOR C1 IS SELECT
paperiod.period_name,
paperiod.period_year,
paperiod.status,
paperiod.quarter_num,
glpersts.effective_period_num,
paperiod.status_meaning
FROM pa_periods_v paperiod,
pa_implementations imp,
gl_period_statuses glpersts
WHERE glpersts.period_type = imp.pa_period_type
AND imp.set_of_books_id = paperiod.set_of_books_id
AND glpersts.set_of_books_id = imp.set_of_books_id
AND glpersts.period_name = paperiod.period_name
AND glpersts.period_year = paperiod.period_year
AND glpersts.application_id = 275
AND paperiod.status = 'O'
ORDER BY paperiod.period_year;
CURSOR C2 IS SELECT glper.period_name
,glper.period_year
,glst.closing_status
,glper.quarter_num
,glst.effective_period_num
,gllkups.meaning
FROM pa_implementations imp,
gl_sets_of_books gl,
gl_periods glper,
gl_period_statuses glst,
gl_lookups gllkups
WHERE imp.set_of_books_id = gl.set_of_books_id
AND gl.period_set_name = glper.period_set_name
AND gl.accounted_period_type = glper.period_type
AND glper.period_name = glst.period_name
AND glper.period_type = glst.period_type
AND glst.set_of_books_id=imp.set_of_books_id
AND glst.closing_status ='O'
AND glst.application_id = PA_Period_Process_PKG.Application_ID
AND gllkups.lookup_code = glst.closing_status
AND gllkups.lookup_type = 'CLOSING_STATUS'
ORDER BY glper.period_year;
SELECT meaning, lookup_code
FROM gl_lookups
WHERE lookup_type = 'CLOSING_STATUS'
ANd lookup_code = p_sts;*/
select 'x' into dummy
from pa_implementations;
SELECT USER_PROFILE_OPTION_NAME INTO l_user_profile_option_name1
FROM fnd_profile_options_tl
WHERE profile_option_name='PA_RES_UTIL_DEF_CALC_METHOD'
AND language=userenv('LANG');
SELECT USER_PROFILE_OPTION_NAME INTO l_user_profile_option_name2
FROM fnd_profile_options_tl
WHERE profile_option_name='PA_RES_UTIL_DEF_PERIOD_TYPE'
AND language=userenv('LANG');
SELECT USER_PROFILE_OPTION_NAME INTO l_user_profile_option_name3
FROM fnd_profile_options_tl
WHERE profile_option_name='PA_GLOBAL_WEEK_START_DAY'
AND language=userenv('LANG');
select forecast_thru_date
into l_forecast_thru_date
from pa_utilization_options ut;
SELECT
paperiod.period_name,
paperiod.period_year,
paperiod.status,
paperiod.quarter_num,
glpersts.effective_period_num,
paperiod.status_meaning
INTO l_prd_name,l_prd_yr,l_sts,l_mon_qtr,l_period_num,l_sts_meaning
FROM pa_periods_v paperiod,
pa_implementations imp,
gl_period_statuses glpersts
WHERE glpersts.period_type = imp.pa_period_type
AND imp.set_of_books_id = paperiod.set_of_books_id
AND glpersts.set_of_books_id = imp.set_of_books_id
AND glpersts.period_name = paperiod.period_name
AND glpersts.period_year = paperiod.period_year
AND glpersts.application_id = 275
-- AND paperiod.status = 'O'
AND l_default_date BETWEEN PA_START_DATE and PA_END_DATE
ORDER BY paperiod.period_year; */
SELECT paperiod.period_name,
paperiod.period_year,
paperiod.status,
paperiod.quarter_num,
0 effective_period_num,
paperiod.status_meaning
INTO l_prd_name,l_prd_yr,l_sts,l_mon_qtr,l_period_num,l_sts_meaning
FROM pa_periods_v paperiod
WHERE l_default_date BETWEEN paperiod.pa_start_date AND paperiod.pa_end_date
ORDER BY paperiod.period_year;
SELECT glper.period_name
,glper.period_year
,glst.closing_status
,glper.quarter_num
,glst.effective_period_num
,gllkups.meaning
INTO l_prd_name,l_prd_yr,l_sts,l_mon_qtr,l_period_num,l_sts_meaning
FROM pa_implementations imp,
gl_sets_of_books gl,
gl_periods glper,
gl_period_statuses glst,
gl_lookups gllkups
WHERE imp.set_of_books_id = gl.set_of_books_id
AND gl.period_set_name = glper.period_set_name
AND gl.accounted_period_type = glper.period_type
AND glper.period_name = glst.period_name
AND glper.period_type = glst.period_type
AND glst.set_of_books_id=imp.set_of_books_id
AND glst.application_id = PA_Period_Process_PKG.Application_ID
AND gllkups.lookup_code = glst.closing_status
AND gllkups.lookup_type = 'CLOSING_STATUS'
AND l_default_date BETWEEN glper.start_date AND glper.end_date
/* Bug 2288460 - Start */
/* Added the following clause */
AND glper.adjustment_period_flag = 'N'
/* Bug 2288460 - End */
ORDER BY glper.period_year;
SELECT
TO_CHAR(NEXT_DAY(l_default_date, NVL(TO_NUMBER(FND_PROFILE.VALUE('PA_GLOBAL_WEEK_START_DAY')),0)) -1,'DD-MON-YYYY')
INTO x_def_period_name
FROM sys.dual;
SELECT
TO_CHAR(NEXT_DAY(l_default_date, NVL(TO_NUMBER(FND_PROFILE.VALUE('PA_GLOBAL_WEEK_START_DAY')),0)) -1,l_date_format)
INTO x_def_period_name
FROM sys.dual;
SELECT
paperiod.period_name,
paperiod.period_year,
paperiod.status,
paperiod.quarter_num,
glpersts.effective_period_num,
paperiod.status_meaning
INTO l_prd_name,l_prd_yr,l_sts,l_mon_qtr,l_period_num,l_sts_meaning
FROM pa_periods_v paperiod,
pa_implementations imp,
gl_period_statuses glpersts
WHERE glpersts.period_type = imp.pa_period_type
AND imp.set_of_books_id = paperiod.set_of_books_id
AND glpersts.set_of_books_id = imp.set_of_books_id
AND glpersts.period_name = paperiod.period_name
AND glpersts.period_year = paperiod.period_year
AND glpersts.period_name = x_def_period_name
AND glpersts.period_year = x_def_period_yr
AND glpersts.application_id = 275
-- AND paperiod.status = 'O'
-- AND l_default_date BETWEEN PA_START_DATE and PA_END_DATE
ORDER BY paperiod.period_year; */
SELECT paperiod.period_name,
paperiod.period_year,
paperiod.status,
paperiod.quarter_num,
0 effective_period_num,
paperiod.status_meaning
INTO l_prd_name,l_prd_yr,l_sts,l_mon_qtr,l_period_num,l_sts_meaning
FROM pa_periods_v paperiod
WHERE paperiod.period_name = x_def_period_name
AND paperiod.period_year = x_def_period_yr
ORDER BY paperiod.period_year;
SELECT glper.period_name
,glper.period_year
,glst.closing_status
,glper.quarter_num
,glst.effective_period_num
,gllkups.meaning
INTO l_prd_name,l_prd_yr,l_sts,l_mon_qtr,l_period_num,l_sts_meaning
FROM pa_implementations imp,
gl_sets_of_books gl,
gl_periods glper,
gl_period_statuses glst,
gl_lookups gllkups
WHERE imp.set_of_books_id = gl.set_of_books_id
AND gl.period_set_name = glper.period_set_name
AND gl.accounted_period_type = glper.period_type
AND glper.period_name = glst.period_name
AND glper.period_name = x_def_period_name
AND glper.period_type = glst.period_type
AND glst.set_of_books_id=imp.set_of_books_id
AND glst.application_id = PA_Period_Process_PKG.Application_ID
AND gllkups.lookup_code = glst.closing_status
AND gllkups.lookup_type = 'CLOSING_STATUS'
/* Bug 2288460 - Start */
/* Added the following clause */
AND glper.adjustment_period_flag = 'N'
/* Bug 2288460 - End */
-- AND l_default_date BETWEEN glper.start_date AND glper.end_date
ORDER BY glper.period_year;
PA_REP_UTIL_GLOB.update_util_cache; -- Bug 2447797 added this call