[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT fiin.calendar_id,
sob.accounted_period_type,
sob.set_of_books_id
INTO x_calendar_id,
x_accnt_period_type,
x_sets_of_books_id
FROM
pa_time_cal_name fiin,
pa_implementations_all imp, -- Bug Fix 8284858
gl_sets_of_books sob
WHERE
imp.set_of_books_id = sob.set_of_books_id
AND imp.org_id = p_org_id -- MOAC Changes
AND sob.period_set_name = imp.period_set_name
AND fiin.period_type = sob.accounted_period_type
AND sob.period_set_name = fiin.period_set_name;
SELECT fiin.calendar_id,
imp.pa_period_type,
imp.set_of_books_id
INTO x_calendar_id,
x_accnt_period_type,
x_sets_of_books_id
FROM
pa_time_cal_name fiin,
pa_implementations_all imp -- Bug Fix 8284858
WHERE
imp.period_set_name = fiin.period_set_name
AND imp.org_id = p_org_id -- MOAC Changes
AND fiin.period_type = imp.pa_period_type;
SELECT -1
INTO x_calendar_id
FROM
DUAL;
/*delete from pji_pmv_time_dim_tmp;
--Insert records for the current period and corresponding
--periods backwards and forward
INSERT INTO PJI_PMV_TIME_DIM_TMP
(
ID,
PRIOR_ID,
NAME,
ORDER_BY_ID,
PERIOD_TYPE,
AMOUNT_TYPE,
CALENDAR_TYPE
)
SELECT period_id as id,
null as prior_id,
period_name as name,
sequence - g_curr_period_seq as order_by_id,
32 as period_type,
1 as amount_type,
p_calendar_type as calendar_type
FROM pji_time_mv
WHERE period_type = DECODE(p_calendar_type,'E','FII_TIME_ENT_PERIOD', 'FII_TIME_CAL_PERIOD')
AND calendar_id = p_calendar_id
AND sequence between g_curr_period_seq-g_backward_periods
and g_curr_period_seq+g_forward_periods;
--Update records for the current period in the prior year
--and corresponding periods backwards and forward
UPDATE PJI_PMV_TIME_DIM_TMP pmv
SET pmv.PRIOR_ID =
( SELECT period_id as prior_id
FROM pji_time_mv lower
WHERE
lower.period_type = DECODE(p_calendar_type,'E','FII_TIME_ENT_PERIOD', 'FII_TIME_CAL_PERIOD')
AND lower.calendar_id = p_calendar_id
AND lower.sequence between g_prev_yr_period_seq-g_backward_periods
and g_prev_yr_period_seq+g_forward_periods
AND pmv.order_by_id = lower.sequence-g_prev_yr_period_seq
);
INSERT INTO PJI_PMV_TIME_DIM_TMP
(
ID,
PRIOR_ID,
NAME,
ORDER_BY_ID,
PERIOD_TYPE,
AMOUNT_TYPE,
CALENDAR_TYPE
)
SELECT r1.id as id,
r1.prior_id as prior_id,
r1.name as name,
r1.sequence as order_by_id,
r1.period_type as period_type,
r1.amount_type as amount_type,
r1.calendar_type as calendar_type
FROM
(
SELECT period_id as id,
null as prior_id,
period_name as name,
32 as period_type,
p_table_amount_type as amount_type,
p_calendar_type as calendar_type,
1 as sequence
FROM pji_time_mv
WHERE period_id = g_curr_period_id
and calendar_id = p_calendar_id
and period_type = DECODE(p_calendar_type,'E','FII_TIME_ENT_PERIOD', 'FII_TIME_CAL_PERIOD')
UNION ALL
select period_id as id,
null as prior_id,
quarter_name as name,
64 as period_type,
p_table_amount_type as amount_type,
p_calendar_type as calendar_type,
2 as sequence
FROM pji_time_mv
WHERE quarter_id = g_curr_quarter_id
and calendar_id = p_calendar_id
and period_type = DECODE(p_calendar_type,'E','FII_TIME_ENT_QTR', 'FII_TIME_CAL_QTR')
UNION ALL
select period_id as id,
null as prior_id,
year as name,
128 as period_type,
p_table_amount_type as amount_type,
p_calendar_type as calendar_type,
3 as sequence
FROM pji_time_mv
WHERE year_id = g_curr_year_id
and calendar_id = p_calendar_id
and period_type = DECODE(p_calendar_type,'E','FII_TIME_ENT_YEAR', 'FII_TIME_CAL_YEAR')
) r1;
SELECT period_id,
quarter_id,
year_id
INTO
l_prev_period_id,
l_prev_quarter_id,
l_prev_year_id
FROM pji_time_mv
WHERE sequence = g_prev_yr_period_seq
and calendar_id = p_calendar_id
and period_type = DECODE(p_calendar_type,'E','FII_TIME_ENT_PERIOD', 'FII_TIME_CAL_PERIOD');
UPDATE PJI_PMV_TIME_DIM_TMP pmv
SET pmv.PRIOR_ID = l_prev_period_id
where order_by_id = 1;
UPDATE PJI_PMV_TIME_DIM_TMP pmv
SET pmv.PRIOR_ID = l_prev_quarter_id
where order_by_id = 2;
UPDATE PJI_PMV_TIME_DIM_TMP pmv
SET pmv.PRIOR_ID = l_prev_year_id
where order_by_id = 3;
/*delete from pji_pmv_time_dim_tmp;
--Insert records for the current period and corresponding
--periods backwards and forward
INSERT INTO PJI_PMV_TIME_DIM_TMP
(
ID,
PRIOR_ID,
NAME,
ORDER_BY_ID,
PERIOD_TYPE,
AMOUNT_TYPE,
CALENDAR_TYPE
)
SELECT period_id as id,
null as prior_id,
period_name as name,
sequence - g_curr_period_seq as order_by_id,
32 as period_type,
DECODE(sign(sequence-g_last_summ_pd_seq),1,0,-1,1,0,1) as amount_type,
p_calendar_type as calendar_type
FROM pji_time_mv
WHERE period_type = DECODE(p_calendar_type,'E','FII_TIME_ENT_PERIOD', 'FII_TIME_CAL_PERIOD')
AND calendar_id = p_calendar_id
AND sequence between g_curr_period_seq-g_backward_periods
and g_curr_period_seq+g_forward_periods;
--Update records for the current period in the prior year
--and corresponding periods backwards and forward
UPDATE PJI_PMV_TIME_DIM_TMP pmv
SET pmv.PRIOR_ID =
( SELECT period_id as prior_id
FROM pji_time_mv lower
WHERE
lower.period_type = DECODE(p_calendar_type,'E','FII_TIME_ENT_PERIOD', 'FII_TIME_CAL_PERIOD')
AND lower.calendar_id = p_calendar_id
AND lower.sequence between g_prev_yr_period_seq-g_backward_periods
and g_prev_yr_period_seq+g_forward_periods
AND pmv.order_by_id = lower.sequence-g_prev_yr_period_seq
)
WHERE pmv.period_type = 32;
SELECT
ID
BULK COLLECT INTO
l_curr_period_id_tbl
FROM
PJI_PMV_TIME_DIM_TMP pmv
where pmv.period_type = 32;
--Update the table to set the period id to null
--where the last summarization date resides
UPDATE PJI_PMV_TIME_DIM_TMP pmv
SET pmv.ID = null
where pmv.id = g_last_summ_period_id
and pmv.calendar_type = p_calendar_type
and pmv.period_type = 32
RETURNING order_by_id
INTO l_last_sum_pmv_seq;
--Call the API to insert daily records
PJI_PMV_ENGINE.Convert_NViewBY_AS_OF_DATE
(
to_char(g_last_summ_date,'j')
, l_period_type
, null
, null
, p_calendar_id
, g_last_summ_pd_name
, g_last_summ_period_id
);
--Update sequence for daily records
UPDATE PJI_PMV_TIME_DIM_TMP pmv
set pmv.order_by_id = l_last_sum_pmv_seq
where pmv.period_type = 1 OR pmv.period_type = 16;
INSERT INTO PJI_PMV_TIME_DIM_TMP
(
ID,
PRIOR_ID,
NAME,
ORDER_BY_ID,
PERIOD_TYPE,
AMOUNT_TYPE,
CALENDAR_TYPE
)
SELECT period_id as id,
null as prior_id,
period_name as name,
1 as order_by_id,
32 as period_type,
DECODE(sign(sequence-g_last_summ_pd_seq),1,0,-1,1,0,1) as amount_type,
p_calendar_type as calendar_type
FROM pji_time_mv
WHERE period_id = g_curr_period_id
and calendar_id = p_calendar_id
and period_type = DECODE(p_calendar_type,'E','FII_TIME_ENT_PERIOD', 'FII_TIME_CAL_PERIOD');
--Call the API to insert daily records
PJI_PMV_ENGINE.Convert_NViewBY_AS_OF_DATE
(
to_char(g_last_summ_date,'j')
, l_period_type
, null
, null
, p_calendar_id
, g_last_summ_pd_name
, 1
);
SELECT
period_id
BULK COLLECT INTO
l_curr_period_id_tbl
FROM
pji_time_mv pt
where quarter_id = g_curr_quarter_id
and calendar_id = p_calendar_id
and period_type = DECODE(p_calendar_type,'E','FII_TIME_ENT_PERIOD', 'FII_TIME_CAL_PERIOD');
INSERT INTO PJI_PMV_TIME_DIM_TMP
(
ID,
PRIOR_ID,
NAME,
ORDER_BY_ID,
PERIOD_TYPE,
AMOUNT_TYPE,
CALENDAR_TYPE
)
SELECT period_id as id,
null as prior_id,
g_curr_quarter_name as name,
2 as order_by_id,
32 as period_type,
DECODE(sign(sequence-g_last_summ_pd_seq),1,0,-1,1,0,1) as amount_type,
p_calendar_type as calendar_type
FROM pji_time_mv
WHERE quarter_id = g_curr_quarter_id
and calendar_id = p_calendar_id
and period_type = DECODE(p_calendar_type,'E','FII_TIME_ENT_PERIOD', 'FII_TIME_CAL_PERIOD');
--Call the API to insert daily records
PJI_PMV_ENGINE.Convert_NViewBY_AS_OF_DATE
(
to_char(g_last_summ_date,'j')
, l_qtr_period_type
, null
, null
, p_calendar_id
, g_curr_quarter_name
, 2
);
--Call the API to insert daily records
PJI_PMV_ENGINE.Convert_NViewBY_AS_OF_DATE
(
to_char(g_last_summ_date,'j')
, l_year_period_type
, null
, null
, p_calendar_id
, g_curr_year_name
, 3
);
SELECT period_id,
quarter_id,
year_id
INTO
l_prev_period_id,
l_prev_quarter_id,
l_prev_year_id
FROM pji_time_mv
WHERE sequence = g_prev_yr_period_seq
and calendar_id = p_calendar_id
and period_type = DECODE(p_calendar_type,'E','FII_TIME_ENT_PERIOD', 'FII_TIME_CAL_PERIOD');
INSERT INTO PJI_PMV_TIME_DIM_TMP
(
ID,
PRIOR_ID,
NAME,
ORDER_BY_ID,
PERIOD_TYPE,
AMOUNT_TYPE,
CALENDAR_TYPE
)
VALUES
(
null ,
l_prev_period_id,
g_curr_period_name,
1 ,
32 ,
1 ,
p_calendar_type
);
INSERT INTO PJI_PMV_TIME_DIM_TMP
(
ID,
PRIOR_ID,
NAME,
ORDER_BY_ID,
PERIOD_TYPE,
AMOUNT_TYPE,
CALENDAR_TYPE
)
VALUES
(
null ,
l_prev_quarter_id,
g_curr_quarter_name,
2 ,
64 ,
1 ,
p_calendar_type
);
INSERT INTO PJI_PMV_TIME_DIM_TMP
(
ID,
PRIOR_ID,
NAME,
ORDER_BY_ID,
PERIOD_TYPE,
AMOUNT_TYPE,
CALENDAR_TYPE
)
VALUES
(
null ,
l_prev_year_id,
g_curr_year_name,
3 ,
128 ,
1 ,
p_calendar_type
);
SELECT trunc(to_date(PJI_UTILS.GET_PARAMETER('LAST_FM_EXTR_DATE'),'YYYY/MM/DD'))
INTO g_last_summ_date
FROM dual;
SELECT period.ent_period_id
,period.name
INTO g_last_summ_period_id,
g_last_summ_pd_name
FROM fii_time_day day,
fii_time_ent_period period
WHERE report_date = g_last_summ_date
AND period.ent_period_id = day.ent_period_id;
SELECT day.cal_period_id
,pmv.period_name
INTO g_last_summ_period_id,
g_last_summ_pd_name
FROM
fii_time_cal_day_mv day
,pji_time_mv pmv
WHERE
report_date = g_last_summ_date
and pmv.period_type = 'FII_TIME_CAL_PERIOD' /* Added this condition for bug 4312361 *
AND day.cal_period_id = pmv.period_id
and pmv.calendar_id = l_calendar_id
and day.calendar_id = pmv.calendar_id;
SELECT sequence
INTO g_last_summ_pd_seq
FROM pji_time_mv
WHERE period_type = DECODE(p_calendar_type,'E','FII_TIME_ENT_PERIOD', 'FII_TIME_CAL_PERIOD')
AND period_id = g_last_summ_period_id
AND calendar_id = l_calendar_id;
--Get the sequence and other information for the current selected period
SELECT sequence,
period_id,
period_name,
quarter_id,
quarter_name,
year_id,
year
INTO g_curr_period_seq,
g_curr_period_id,
g_curr_period_name,
g_curr_quarter_id,
g_curr_quarter_name,
g_curr_year_id,
g_curr_year_name
FROM pji_time_mv
WHERE period_type = DECODE(p_calendar_type,'E','FII_TIME_ENT_PERIOD', 'FII_TIME_CAL_PERIOD')
AND period_id = p_period_id
AND calendar_id = l_calendar_id;
SELECT start_date
INTO g_curr_yr_pd_start_date
FROM fii_time_ent_period
WHERE ent_period_id = p_period_id;
SELECT Fii_Time_Api.ent_sd_lysper_end(g_curr_yr_pd_start_date)
INTO g_prev_yr_pd_start_date
FROM DUAL;
SELECT ent_period_id
INTO g_prev_yr_period_id
FROM fii_time_day
WHERE report_date_julian = to_char(g_prev_yr_pd_start_date,'j');
SELECT start_date
INTO g_curr_yr_pd_start_date
FROM fii_time_cal_period
WHERE cal_period_id = p_period_id
AND calendar_id = l_calendar_id;
SELECT Fii_Time_Api.cal_sd_lysper_end(g_curr_yr_pd_start_date, l_calendar_id)
INTO g_prev_yr_pd_start_date
FROM DUAL;
SELECT cal_period_id
INTO g_prev_yr_period_id
FROM fii_time_cal_day_mv
WHERE report_date_julian = to_char(g_prev_yr_pd_start_date,'j')
and calendar_id = l_calendar_id;
SELECT NVL(sequence,0)
INTO g_prev_yr_period_seq
FROM pji_time_mv
WHERE period_type = DECODE(p_calendar_type,'E','FII_TIME_ENT_PERIOD', 'FII_TIME_CAL_PERIOD')
AND period_id = g_prev_yr_period_id
AND calendar_id = l_calendar_id;
SELECT NVL(fnd_profile.value('PA_RES_UTIL_DEF_CALC_METHOD'),'CAPACITY')
INTO g_calc_mthd
FROM DUAL;
DELETE FROM PJI_RES_UTILZ_TMP2;
INSERT INTO PJI_RES_UTILZ_TMP2
(
period_name,
curr_yr_actual_utiliz,
curr_yr_sched_utiliz,
curr_yr_exp_utiliz,
prev_yr_utiliz,
sequence
)
SELECT
r1.period_name as period_name,
round(SUM(r1.actual_utilz) * 100,2) as curr_yr_actual_utiliz,
round(SUM(r1.sched_utilz) * 100,2) as curr_yr_sched_utiliz,
round(SUM(r1.exp_utilz) * 100,2) as curr_yr_exp_utiliz,
round(SUM(r1.prev_yr_utilz) * 100,2) as prev_yr_utiliz,
r1.id as sequence
FROM
(
SELECT
SUM(total_wtd_res_hrs_a)/DECODE(SUM(DECODE(g_calc_mthd,'CAPACITY',cur2.capacity_hrs - cur2.reduce_capacity_hrs_a, cur2.total_hrs_a)),
0, null,SUM(DECODE(g_calc_mthd,'CAPACITY',cur2.capacity_hrs - cur2.reduce_capacity_hrs_a, cur2.total_hrs_a))) as actual_utilz,
SUM(CONF_WTD_RES_HRS_S)/DECODE(SUM(DECODE(g_calc_mthd,'CAPACITY',cur2.capacity_hrs - cur2.reduce_capacity_hrs_s, cur2.conf_hrs_s)),
0, null,SUM(DECODE(g_calc_mthd,'CAPACITY',cur2.capacity_hrs - cur2.reduce_capacity_hrs_s, cur2.conf_hrs_s))) as sched_utilz,
SUM(DECODE(pmv.amount_type,1, total_wtd_res_hrs_a,CONF_WTD_RES_HRS_S))/DECODE(SUM(DECODE(g_calc_mthd,
'CAPACITY',cur2.capacity_hrs - DECODE(pmv.amount_type,1,cur2.reduce_capacity_hrs_a,cur2.reduce_capacity_hrs_s),
DECODE(pmv.amount_type,1, cur2.total_hrs_a, cur2.conf_hrs_s))),
0, null,SUM(DECODE(g_calc_mthd,'CAPACITY',cur2.capacity_hrs -
DECODE(pmv.amount_type,1,cur2.reduce_capacity_hrs_a,cur2.reduce_capacity_hrs_s),
DECODE(pmv.amount_type,1, cur2.total_hrs_a, cur2.conf_hrs_s)))) as exp_utilz,
0 as prev_yr_utilz,
pmv.name as period_name,
pmv.order_by_id as id
from pji_rm_res_f cur2, PJI_PMV_TIME_DIM_TMP pmv
where person_id = p_person_id
and pmv.period_type IN (1,16)
and pmv.period_type = cur2.period_type_id
and pmv.calendar_type = 'C'
and pmv.calendar_type = cur2.calendar_type
and pmv.id = cur2.time_id
group by pmv.name, pmv.order_by_id
UNION ALL
SELECT
SUM(total_wtd_res_hrs_a)/DECODE(SUM(DECODE(g_calc_mthd,'CAPACITY',cur2.capacity_hrs - cur2.reduce_capacity_hrs_a, cur2.total_hrs_a)),
0, null,SUM(DECODE(g_calc_mthd,'CAPACITY',cur2.capacity_hrs - cur2.reduce_capacity_hrs_a, cur2.total_hrs_a))) as actual_utilz,
SUM(CONF_WTD_RES_HRS_S)/DECODE(SUM(DECODE(g_calc_mthd,'CAPACITY',cur2.capacity_hrs - cur2.reduce_capacity_hrs_s, cur2.conf_hrs_s)),
0, null,SUM(DECODE(g_calc_mthd,'CAPACITY',cur2.capacity_hrs - cur2.reduce_capacity_hrs_s, cur2.conf_hrs_s))) as sched_utilz,
SUM(DECODE(pmv.amount_type,1, total_wtd_res_hrs_a,CONF_WTD_RES_HRS_S))/DECODE(SUM(DECODE(g_calc_mthd,
'CAPACITY',cur2.capacity_hrs - DECODE(pmv.amount_type,1,cur2.reduce_capacity_hrs_a,cur2.reduce_capacity_hrs_s),
DECODE(pmv.amount_type,1, cur2.total_hrs_a, cur2.conf_hrs_s))),
0, null,SUM(DECODE(g_calc_mthd,'CAPACITY',cur2.capacity_hrs -
DECODE(pmv.amount_type,1,cur2.reduce_capacity_hrs_a,cur2.reduce_capacity_hrs_s),
DECODE(pmv.amount_type,1, cur2.total_hrs_a, cur2.conf_hrs_s)))) as exp_utilz,
0 as prev_yr_utilz,
pmv.name as period_name,
pmv.order_by_id as id
from pji_rm_res_f cur2, PJI_PMV_TIME_DIM_TMP pmv
where person_id = p_person_id
and pmv.period_type = 32
and pmv.period_type = cur2.period_type_id
and pmv.calendar_type = p_calendar_type
and pmv.calendar_type = cur2.calendar_type
and pmv.id = cur2.time_id
and pmv.id is not null
group by pmv.name, pmv.order_by_id
UNION ALL
SELECT
0 as actual_utilz,
0 as sched_utilz,
0 as exp_utilz,
SUM(total_wtd_res_hrs_a)/DECODE(SUM(DECODE(g_calc_mthd,'CAPACITY',cur2.capacity_hrs - cur2.reduce_capacity_hrs_a, cur2.total_hrs_a)),
0, null,SUM(DECODE(g_calc_mthd,'CAPACITY',cur2.capacity_hrs - cur2.reduce_capacity_hrs_a, cur2.total_hrs_a))) as prev_yr_utilz,
pmv.name as period_name,
pmv.order_by_id as id
from pji_rm_res_f cur2, PJI_PMV_TIME_DIM_TMP pmv
where person_id = p_person_id
and pmv.period_type = 32
and pmv.period_type = cur2.period_type_id
and pmv.calendar_type = p_calendar_type
and pmv.calendar_type = cur2.calendar_type
and pmv.prior_id = cur2.time_id
group by pmv.name, order_by_id
) r1
group by r1.period_name, r1.id
order by id;
SELECT meaning
INTO l_act_utilz_label
FROM pji_lookups
WHERE lookup_type = 'PJI_RM_UTILZ_GRAPH_HEADER'
and lookup_code = 'ACT_UTILZ';
SELECT meaning
INTO l_sched_utilz_label
FROM pji_lookups
WHERE lookup_type = 'PJI_RM_UTILZ_GRAPH_HEADER'
and lookup_code = 'SCHED_UTILZ';
SELECT year
INTO
l_prev_year_name
FROM pji_time_mv
WHERE sequence = g_prev_yr_period_seq
and calendar_id = l_calendar_id
and period_type = DECODE(p_calendar_type,'E','FII_TIME_ENT_PERIOD', 'FII_TIME_CAL_PERIOD');
INSERT INTO PJI_RES_UTILZ_TMP2
(
period_name,
series_label,
value,
secondary_sequence,
sequence
)
SELECT DISTINCT r3.period_name,
case when r3.tmp_index = 1 then
l_curr_yr_act_utilz_label
when r3.tmp_index = 2 then
l_curr_yr_sched_utilz_label
when r3.tmp_index = 3 then
l_prev_yr_act_utilz_label
end series_label,
case when r3.tmp_index = 1 then
r3.curr_yr_actual_utiliz
when r3.tmp_index = 2 then
r3.curr_yr_sched_utiliz
when r3.tmp_index = 3 then
r3.prev_yr_utiliz
end value,
case when r3.tmp_index = 1 then
1
when r3.tmp_index = 2 then
2
when r3.tmp_index = 3 then
3
end secnd_seq,
r3.sequence
FROM
(
select r1.period_name,
r1.curr_yr_actual_utiliz,
r1.curr_yr_sched_utiliz,
r1.prev_yr_utiliz,
r1.sequence,
r2.tmp_index
FROM
(
select period_name,
curr_yr_actual_utiliz,
curr_yr_sched_utiliz,
prev_yr_utiliz,
sequence
FROM PJI_RES_UTILZ_TMP2
) r1,
(
SELECT 1 as tmp_index from dual
UNION ALL
SELECT 2 as tmp_index from dual
UNION ALL
SELECT 3 as tmp_index from dual
) r2
) r3;
--Delete records that were inserted initially
DELETE FROM PJI_RES_UTILZ_TMP2
WHERE series_label IS NULL;Commented for bug 13011859 */
SELECT
min(sequence),
max(sequence)
INTO
g_curr_yr_max_sequence,
g_curr_yr_min_sequence
FROM
pji_time_mv pt
WHERE year_id = g_curr_year_id
and calendar_id = l_calendar_id
and period_type = DECODE(p_calendar_type,'E','FII_TIME_ENT_PERIOD', 'FII_TIME_CAL_PERIOD');
DELETE FROM PJI_RES_UTILZ_TMP3;
INSERT INTO PJI_RES_UTILZ_TMP3
(
period,
actual_utilization,
sched_utilization,
expected_utilization,
prior_yr_utilization,
sequence
)
SELECT r1.period_name as period,
round(SUM(r1.actual_utilz) * 100,2) as actual_utilization,
round(SUM(r1.sched_utilz) * 100,2) as sched_utilization,
round(SUM(r1.exp_utilz) * 100,2) as expected_utilization,
round(SUM(r1.prev_yr_utilz) * 100,2) as prior_yr_utilization,
r1.id as sequence
FROM
( /* Modified the select for Bug 9898007 *
SELECT pmv.name as period_name,
SUM(total_wtd_res_hrs_a)/DECODE(SUM(DECODE(g_calc_mthd,'CAPACITY',cur2.capacity_hrs - cur2.reduce_capacity_hrs_a, cur2.total_hrs_a)),0, null,
SUM(DECODE(g_calc_mthd,'CAPACITY',cur2.capacity_hrs - cur2.reduce_capacity_hrs_a, cur2.total_hrs_a))) as actual_utilz,
SUM(CONF_WTD_RES_HRS_S)/DECODE(SUM(DECODE(g_calc_mthd,'CAPACITY',cur2.capacity_hrs - cur2.reduce_capacity_hrs_s, cur2.conf_hrs_s)),0, null,
SUM(DECODE(g_calc_mthd,'CAPACITY',cur2.capacity_hrs - cur2.reduce_capacity_hrs_s, cur2.conf_hrs_s))) as sched_utilz,
SUM(nvl(total_wtd_res_hrs_a,0) + nvl(conf_wtd_res_hrs_s,0))/DECODE(SUM(DECODE(g_calc_mthd, 'CAPACITY', cur2.capacity_hrs -
(nvl(cur2.reduce_capacity_hrs_a,0) + nvl(cur2.reduce_capacity_hrs_s,0)),
( nvl(cur2.total_hrs_a,0)+ nvl(cur2.conf_hrs_s,0)))), 0, NULL, SUM( DECODE(g_calc_mthd, 'CAPACITY', cur2.capacity_hrs -
(nvl(cur2.reduce_capacity_hrs_a,0) + nvl(cur2.reduce_capacity_hrs_s,0)),
(nvl(cur2.total_hrs_a,0)+ nvl(cur2.conf_hrs_s,0))))) AS exp_utilz,
0 as prev_yr_utilz,
pmv.order_by_id as id
FROM pji_rm_res_f cur2, PJI_PMV_TIME_DIM_TMP pmv
WHERE person_id = p_person_id
and pmv.period_type = cur2.period_type_id
and pmv.calendar_type = cur2.calendar_type
and pmv.id = cur2.time_id
and pmv.id is not null
group by pmv.name, pmv.order_by_id
UNION ALL
SELECT pmv.name as period_name,
0 as actual_utilz,
0 as sched_utilz,
0 as exp_utilz,
SUM(total_wtd_res_hrs_a)/DECODE(SUM(DECODE(g_calc_mthd,'CAPACITY',cur2.capacity_hrs - cur2.reduce_capacity_hrs_a, cur2.total_hrs_a)),
0, null,SUM(DECODE(g_calc_mthd,'CAPACITY',cur2.capacity_hrs - cur2.reduce_capacity_hrs_a, cur2.total_hrs_a))) as prev_yr_utilz,
pmv.order_by_id as id
from pji_rm_res_f cur2, PJI_PMV_TIME_DIM_TMP pmv
where person_id = p_person_id
and pmv.period_type = cur2.period_type_id
and pmv.calendar_type = cur2.calendar_type
and pmv.prior_id = cur2.time_id
and pmv.prior_id is not null
group by pmv.name, pmv.order_by_id
) r1
group by r1.period_name, r1.id;Commented for bug 13011859 */
that are shown to the user for selection on
the Personal Resource Utilization Page.
The periods are populated in a global temporary
table, and based on whether it is a PA period or
a GL/Enterprise period, the data is shown on the
page either in a LOV or a pop list respectively
*/
PROCEDURE PJI_POPULATE_PERIODS
(
p_period_type_id IN NUMBER
,p_calendar_type IN VARCHAR2
,p_period_id IN NUMBER
,p_org_id IN NUMBER
,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
,x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
,x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
)
IS
l_calendar_id NUMBER;
/* DELETE FROM PJI_RES_UTILZ_TMP1;
SELECT pa_period_process_pkg.application_id
INTO l_application_id
FROM dual;
SELECT application_id
INTO l_application_id
FROM fnd_application
WHERE application_short_name = 'PA';
SELECT sequence
INTO l_sequence
FROM pji_time_mv
WHERE
period_type = DECODE(p_calendar_type,'E','FII_TIME_ENT_YEAR', 'FII_TIME_CAL_YEAR')
AND calendar_id = l_calendar_id
and year_id in
(
SELECT year_id
FROM pji_time_mv
WHERE period_id = p_period_id
AND calendar_id = l_calendar_id
);
select same_pa_gl_period into
l_same_pa_gl_period
from pa_implementations_all
where org_id = p_org_id;
SELECT period_id,
period_name,
null,
to_char(period_start_date,'j')
BULK COLLECT INTO
l_period_id_tbl,
l_period_name_tbl,
l_period_status_tbl,
l_period_st_dt_tbl
FROM pji_time_mv
WHERE calendar_id = l_calendar_id
and period_type = 'FII_TIME_ENT_PERIOD'
and year_id in
(
SELECT year_id
FROM pji_time_mv
WHERE period_type = 'FII_TIME_ENT_YEAR'
and calendar_id = l_calendar_id
and sequence in
(l_sequence-1,l_sequence,l_sequence+1)
)
ORDER BY period_start_date;
SELECT r1.period_id,
r1.period_name,
r2.show_status,
to_char(r1.period_start_date,'j')
BULK COLLECT INTO
l_period_id_tbl,
l_period_name_tbl,
l_period_status_tbl,
l_period_st_dt_tbl
FROM
(
SELECT period_id,
period_name,
period_start_date
FROM pji_time_mv
WHERE calendar_id = l_calendar_id
and period_type = 'FII_TIME_CAL_PERIOD'
and year_id in
(
SELECT year_id
FROM pji_time_mv
WHERE period_type = 'FII_TIME_CAL_YEAR'
and calendar_id = l_calendar_id
and sequence in
(l_sequence-1,l_sequence,l_sequence+1)
)
) r1, gl_period_statuses_v r2
where r2.period_type = l_accnt_period_type
and r2.set_of_books_id = l_sets_of_books_id
and r2.application_id = DECODE(l_same_pa_gl_period,'N',l_application_id, 8721) --12331139
and r1.period_name = r2.period_name
ORDER BY r1.period_start_date;
INSERT INTO PJI_RES_UTILZ_TMP1
(
period_id,
period_name,
period_status,
period_start_date
)
VALUES
(
l_period_id_tbl(k),
l_period_name_tbl(k),
l_period_status_tbl(k),
l_period_st_dt_tbl(k)
);Commented for bug 13011859 */
SELECT fnd_profile.value('PA_RES_UTIL_DEF_PERIOD_TYPE')
INTO l_pa_res_util_def_pd_types
FROM dual;
SELECT employee_id
INTO x_person_id
FROM fnd_user
WHERE user_id = l_user_id;
SELECT meaning
INTO x_period_type
FROM pa_lookups
WHERE lookup_type = 'PA_RES_UTIL_DEF_PERIOD_TYPES'
and lookup_code = l_pa_res_util_def_pd_types;
SELECT period_id,
period_name
INTO x_curr_period_id,
x_curr_period_name
FROM pji_time_mv
WHERE
(SELECT DECODE(SIGN(TRUNC(SYSDATE) - MAX(period_end_date)), 1, MAX(period_end_date), TRUNC(SYSDATE))
FROM pji_time_mv
WHERE 1=1
AND calendar_id = l_calendar_id
AND period_type = 'FII_TIME_ENT_PERIOD') BETWEEN period_start_date and period_end_date
AND calendar_id = l_calendar_id
AND period_type = 'FII_TIME_ENT_PERIOD';Commented for bug 13011859 *
SELECT period_id,
period_name
INTO x_curr_period_id,
x_curr_period_name
FROM pji_time_mv
WHERE
(SELECT DECODE(SIGN(TRUNC(SYSDATE) - MAX(period_end_date)), 1, MAX(period_end_date), TRUNC(SYSDATE))
FROM pji_time_mv
WHERE 1=1
AND calendar_id = l_calendar_id
AND period_type = 'FII_TIME_CAL_PERIOD') BETWEEN period_start_date and period_end_date
AND calendar_id = l_calendar_id
AND period_type = 'FII_TIME_CAL_PERIOD';
SELECT period_id,
period_name
INTO x_curr_period_id,
x_curr_period_name
FROM pji_time_mv
WHERE
(SELECT DECODE(SIGN(TRUNC(SYSDATE) - MAX(period_end_date)), 1, MAX(period_end_date), TRUNC(SYSDATE))
FROM pji_time_mv
WHERE 1=1
AND calendar_id = l_calendar_id
AND period_type = 'FII_TIME_PA_PERIOD') BETWEEN period_start_date and period_end_date
AND calendar_id = l_calendar_id
AND period_type = 'FII_TIME_PA_PERIOD';
SELECT NVL(person_id,-1)
INTO x_person_id
FROM pa_resource_txn_attributes
WHERE resource_id = p_resource_id;