[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select fp.fin_plan_version_id budget_version_id
, fp.project_id
, fp.proj_fp_options_id
, nvl(pa.org_id,-99) org_id
, fp.fin_plan_type_id /* bug 3804286: added fin_plan_type_id */
, fp.fin_plan_preference_code /* bug 3804286: added fin_plan_preference_code */
, pa.start_date /* bug 3804286: added start_date */
, fp.fin_plan_option_level_code
--Bug 4046524
, pa.project_currency_code
, pa.projfunc_currency_code
from pa_proj_fp_options fp, pa_projects_all pa,
pa_budget_versions pbv
where fp.project_id = pa.project_id
and fp.project_id = c_project_id
and pbv.budget_version_id=c_budget_ver_id
and (fp.fin_plan_version_id = c_budget_ver_id OR
(fin_plan_option_level_code <> 'PLAN_VERSION' AND nvl(fp.fin_plan_type_id,-99)=nvl(pbv.fin_plan_type_id,-99)) )
and decode(fp.fin_plan_preference_code,
'COST_ONLY',cost_time_phased_code,
'REVENUE_ONLY',revenue_time_phased_code,
'COST_AND_REV_SAME',all_time_phased_code,
'COST_AND_REV_SEP',decode(cost_time_phased_code,
'R',cost_time_phased_code,
revenue_time_phased_code)) = 'R';
select project_id,'Y' from pa_proj_fp_options fp
where fin_plan_version_id = c_budget_ver_id and
fin_plan_option_level_code like 'PLAN_VERSION';
select pi.pa_period_type,sob.accounted_period_type, sob.period_set_name from
pa_implementations_all pi,
gl_sets_of_books sob
where nvl(pi.org_id,-99) = c_org_id
and sob.set_of_books_id = pi.set_of_books_id;
select 'Y' from dual
where exists (select 'Y' from pa_resource_assignments ra
where budget_version_id = c_budget_version_id
and ra.planning_start_date is NOT NULL /* bug 3673111 */
and ra.planning_end_date is NOT NULL /* bug 3673111 */
and not exists
((
select 'Y' from
gl_date_period_map g
where trunc(g.accounting_date) between ra.planning_start_date and ra.planning_end_date
and g.period_set_name = c_period_set_name
and g.period_type = c_period_type
)));
select 'Y' from dual
where exists (select 'Y' from pa_budget_lines bl
where bl.budget_version_id = c_budget_version_id
group by resource_assignment_id
having count(*) > 1);
select pi.pa_period_type,sob.accounted_period_type
from pa_implementations_all pi,
gl_sets_of_books sob
where nvl(pi.org_id,-99) = c_org_id
and sob.set_of_books_id = pi.set_of_books_id
and exists
(select 1
from gl_date_period_map g
where g.period_set_name=c_period_set_name);
select 'Y' from dual
where exists ( select 'Y'
from pa_budget_lines bl
,gl_periods gl
where bl.budget_version_id = c_budget_version_id
and gl.period_type = c_period_type
and gl.period_set_name = c_period_set_name
and gl.ADJUSTMENT_PERIOD_FLAG = 'N'
and (bl.start_date between gl.start_date and gl.end_date
or bl.end_date between gl.start_date and gl.end_date
or (gl.start_date > bl.start_date and gl.end_date < bl.end_date)));
select min(start_date) min_date ,max(end_date) max_date,
sum(quantity) sum_quantity,
sum(raw_cost) sum_raw_cost,
sum(burdened_cost) sum_burdened_cost,
sum(revenue) sum_revenue,
sum(project_raw_cost) sum_project_raw_cost,
sum(project_burdened_cost) sum_project_burdened_cost,
sum(project_revenue) sum_project_revenue,
sum(txn_raw_cost) sum_txn_raw_cost,
sum(txn_burdened_cost) sum_txn_burdened_cost,
sum(txn_revenue) sum_txn_revenue,
resource_assignment_id,txn_currency_code
from pa_budget_lines
where budget_version_id = c_budget_version_id
group by resource_assignment_id, txn_currency_code ;
select resource_assignment_id,planning_start_date from pa_resource_assignments
where budget_version_id = c_budget_version_id;
select
rs.resource_assignment_id resource_assignment_id,
rs.txn_currency_code txn_currency_code,
rs.gl_start_date gl_start_date,
rs.gl_end_date gl_end_date,
rs.PERIOD_NAME period_name,
rs.rate_based_flag rate_based_flag,
round(sum(rs.spr_quantity * factor),5) spr_quantity,
sum(rs.spr_raw_cost * factor) spr_raw_cost,
sum(rs.spr_burdened_cost * factor) spr_burdened_cost,
sum(rs.spr_revenue * factor) spr_revenue,
sum(rs.spr_project_raw_cost * factor) spr_project_raw_cost,
sum(rs.spr_project_burdened_cost * factor) spr_project_burdened_cost,
sum(rs.spr_project_revenue * factor) spr_project_revenue,
round(sum(rs.spr_txn_raw_cost * factor),5) spr_txn_raw_cost,
round(sum(rs.spr_txn_burdened_cost * factor),5) spr_txn_burdened_cost,
round(sum(rs.spr_txn_revenue * factor),5) spr_txn_revenue,
--Bug 4299635. The below columns will have the total amounts for the budget line accumulated into the first
--PA/GL period into which the budget line falls. These amounts will be used later in comparing the actual
--amounts that should get upgraded and the amounts that got upgraded
sum(rs.spr_quantity * tot_amt_factor) total_qty,
sum(rs.spr_txn_raw_cost * tot_amt_factor) total_txn_raw_cost,
sum(rs.spr_txn_burdened_cost * tot_amt_factor) total_txn_burd_cost,
sum(rs.spr_txn_revenue * tot_amt_factor) total_txn_revenue
from(
select
bl.resource_assignment_id resource_assignment_id,
ra.rate_based_flag rate_based_flag,
bl.txn_currency_code txn_currency_code,
gl.start_date gl_start_date,
gl.end_date gl_end_date,
gl.PERIOD_NAME period_name,
bl.quantity spr_quantity,
bl.raw_cost spr_raw_cost,
bl.burdened_cost spr_burdened_cost,
bl.revenue spr_revenue,
bl.project_raw_cost spr_project_raw_cost,
bl.project_burdened_cost spr_project_burdened_cost,
bl.project_revenue spr_project_revenue,
bl.txn_raw_cost spr_txn_raw_cost,
bl.txn_burdened_cost spr_txn_burdened_cost,
bl.txn_revenue spr_txn_revenue,
ratio_to_report((decode(least(bl.start_date, gl.start_date),
bl.start_date,decode(least(bl.end_date,gl.end_date),
gl.end_date,gl.end_date-gl.start_date+1,
bl.end_date,bl.end_date-gl.start_date+1),
gl.start_date,decode(least(bl.end_date,gl.end_date),
gl.end_date,gl.end_date-bl.start_date+1,
bl.end_date,bl.end_date-bl.start_date+1))
)) OVER (PARTITION BY bl.budget_line_id) factor,
--Bug 4299635. This factor will be used to derive the total amount that should get upgraded
--for a planning txn
DECODE(least(bl.start_date, gl.start_date),
gl.start_date,1,
0) tot_amt_factor
from pa_budget_lines bl,gl_periods gl, pa_resource_assignments ra
where bl.budget_version_id = l_budget_version_id
and ra.resource_assignment_id = l_res_assign_id
and bl.resource_assignment_id = l_res_assign_id
and gl.period_type = l_per_type
and gl.period_set_name = l_period_set_name
and gl.ADJUSTMENT_PERIOD_FLAG = 'N' /* Bug 3807889: Added this filter */
and (bl.start_date between gl.start_date and gl.end_date
or bl.end_date between gl.start_date and gl.end_date
or (gl.start_date > bl.start_date and gl.end_date < bl.end_date)))rs
group by resource_assignment_id, gl_start_date, PERIOD_NAME, txn_currency_code, gl_end_date,rate_based_flag;
select period_mask_id
from pa_period_masks_b
where pre_defined_flag='Y'
and time_phase_code = c_time_phased_code;
select trunc(sysdate) into l_min_date from dual;
SELECT min(start_date)
INTO l_project_start_date
FROM pa_budget_lines
WHERE budget_version_id = l_get_elig_bud_ver_csr.budget_version_id;
select trunc(sysdate) into l_project_start_date from dual;
SELECT gl.PERIOD_NAME
INTO l_curr_plan_period
FROM gl_periods gl
WHERE gl.period_type = l_accounted_per_type
and l_project_start_date between gl.START_DATE and gl.END_DATE
AND gl.period_set_name = l_sob_period_set_name
AND gl.ADJUSTMENT_PERIOD_FLAG = 'N';
SELECT gl.PERIOD_NAME
INTO l_curr_plan_period
FROM gl_periods gl
WHERE gl.period_type = l_PA_period_type
and l_project_start_date between gl.START_DATE and gl.END_DATE
AND gl.period_set_name = l_sob_period_set_name
AND gl.ADJUSTMENT_PERIOD_FLAG = 'N';
UPDATE pa_proj_fp_options
SET cost_time_phased_code = decode(cost_time_phased_code,'R',l_time_phased_mode,cost_time_phased_code), /* Bug 3792821 */
revenue_time_phased_code = decode(revenue_time_phased_code,'R',l_time_phased_mode,revenue_time_phased_code), /* Bug 3792821 */
all_time_phased_code = decode(all_time_phased_code,'R',l_time_phased_mode,all_time_phased_code), /* Bug 3792821 */
cost_current_planning_period = l_cost_current_planning_period,
cost_period_mask_id = l_cost_period_mask_id,
rev_current_planning_period = l_rev_current_planning_period,
rev_period_mask_id = l_rev_period_mask_id,
all_current_planning_period = l_all_current_planning_period ,
all_period_mask_id = l_all_period_mask_id
WHERE proj_fp_options_id = l_get_elig_bud_ver_csr.proj_fp_options_id;
/* Including this IF so that we can avoid an update (though it would do nothing) in case
of project/plan type level record */
IF l_get_elig_bud_ver_csr.fin_plan_option_level_code = 'PLAN_VERSION' THEN
update pa_budget_versions
SET current_planning_period = l_curr_plan_period,
period_mask_id = l_period_mask_id
where budget_version_id = l_get_elig_bud_ver_csr.budget_version_id;
insert into pa_budget_lines_m_upg_dtrange
(
resource_assignment_id,
start_date,
last_update_date,
last_updated_by,
creation_date,
created_by ,
last_update_login,
end_date,
period_name,
quantity,
raw_cost,
burdened_cost ,
revenue ,
change_reason_code,
description ,
attribute_category ,
attribute1,
attribute2 ,
attribute3 ,
attribute4,
attribute5,
attribute6,
attribute7 ,
attribute8 ,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13 ,
attribute14 ,
attribute15,
raw_cost_source,
burdened_cost_source,
quantity_source ,
revenue_source ,
pm_product_code,
pm_budget_line_reference ,
cost_rejection_code ,
revenue_rejection_code ,
burden_rejection_code ,
other_rejection_code ,
code_combination_id ,
ccid_gen_status_code,
ccid_gen_rej_message ,
request_id ,
borrowed_revenue ,
tp_revenue_in,
tp_revenue_out ,
revenue_adj,
lent_resource_cost,
tp_cost_in ,
tp_cost_out,
cost_adj ,
unassigned_time_cost,
utilization_percent,
utilization_hours ,
utilization_adj ,
capacity,
head_count ,
head_count_adj,
projfunc_currency_code,
projfunc_cost_rate_type ,
projfunc_cost_exchange_rate,
projfunc_cost_rate_date_type ,
projfunc_cost_rate_date ,
projfunc_rev_rate_type ,
projfunc_rev_exchange_rate ,
projfunc_rev_rate_date_type ,
projfunc_rev_rate_date ,
project_currency_code ,
project_cost_rate_type ,
project_cost_exchange_rate ,
project_cost_rate_date_type ,
project_cost_rate_date,
project_raw_cost ,
project_burdened_cost ,
project_rev_rate_type ,
project_rev_exchange_rate,
project_rev_rate_date_type ,
project_rev_rate_date ,
project_revenue,
txn_currency_code,
txn_raw_cost,
txn_burdened_cost ,
txn_revenue,
bucketing_period_code,
budget_line_id ,
budget_version_id)
( select
resource_assignment_id,
start_date,
last_update_date,
last_updated_by,
creation_date,
created_by ,
last_update_login,
end_date,
period_name,
quantity,
raw_cost,
burdened_cost ,
revenue ,
change_reason_code,
description ,
attribute_category ,
attribute1,
attribute2 ,
attribute3 ,
attribute4,
attribute5,
attribute6,
attribute7 ,
attribute8 ,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13 ,
attribute14 ,
attribute15,
raw_cost_source,
burdened_cost_source,
quantity_source ,
revenue_source ,
pm_product_code,
pm_budget_line_reference ,
cost_rejection_code ,
revenue_rejection_code ,
burden_rejection_code ,
other_rejection_code ,
code_combination_id ,
ccid_gen_status_code,
ccid_gen_rej_message ,
request_id ,
borrowed_revenue ,
tp_revenue_in,
tp_revenue_out ,
revenue_adj,
lent_resource_cost,
tp_cost_in ,
tp_cost_out,
cost_adj ,
unassigned_time_cost,
utilization_percent,
utilization_hours ,
utilization_adj ,
capacity,
head_count ,
head_count_adj,
projfunc_currency_code,
projfunc_cost_rate_type ,
projfunc_cost_exchange_rate,
projfunc_cost_rate_date_type ,
projfunc_cost_rate_date ,
projfunc_rev_rate_type ,
projfunc_rev_exchange_rate ,
projfunc_rev_rate_date_type ,
projfunc_rev_rate_date ,
project_currency_code ,
project_cost_rate_type ,
project_cost_exchange_rate ,
project_cost_rate_date_type ,
project_cost_rate_date,
project_raw_cost ,
project_burdened_cost ,
project_rev_rate_type ,
project_rev_exchange_rate,
project_rev_rate_date_type ,
project_rev_rate_date ,
project_revenue,
txn_currency_code,
txn_raw_cost,
txn_burdened_cost ,
txn_revenue,
bucketing_period_code,
budget_line_id ,
budget_version_id from pa_budget_lines where
resource_assignment_id = l_get_non_time_multi_csr.resource_assignment_id
and txn_currency_code = l_get_non_time_multi_csr.txn_currency_code
and budget_version_id = l_get_elig_bud_ver_csr.budget_version_id);
delete from pa_budget_lines
where resource_assignment_id = l_get_non_time_multi_csr.resource_assignment_id
and txn_currency_code = l_get_non_time_multi_csr.txn_currency_code
and start_date <> l_get_non_time_multi_csr.min_date
and budget_version_id = l_get_elig_bud_ver_csr.budget_version_id;
update pa_budget_lines
set start_date = l_get_non_time_multi_csr.min_date,
end_date = l_get_non_time_multi_csr.max_date,
quantity = l_get_non_time_multi_csr.sum_quantity,
raw_cost = l_get_non_time_multi_csr.sum_raw_cost,
burdened_cost = l_get_non_time_multi_csr.sum_burdened_cost,
revenue = l_get_non_time_multi_csr.sum_revenue,
project_raw_cost = l_get_non_time_multi_csr.sum_project_raw_cost,
project_burdened_cost = l_get_non_time_multi_csr.sum_project_burdened_cost,
project_revenue = l_get_non_time_multi_csr.sum_project_revenue,
txn_raw_cost = l_get_non_time_multi_csr.sum_txn_raw_cost,
txn_burdened_cost = l_get_non_time_multi_csr.sum_txn_burdened_cost,
txn_revenue = l_get_non_time_multi_csr.sum_txn_revenue
where resource_assignment_id = l_get_non_time_multi_csr.resource_assignment_id
and txn_currency_code = l_get_non_time_multi_csr.txn_currency_code
and start_date = l_get_non_time_multi_csr.min_date
and budget_version_id = l_get_elig_bud_ver_csr.budget_version_id;
select decode(g_upgrade_mode,'PA_Period_Upgrade',l_pa_period_type,l_accounted_per_type) into l_per_type from dual;
select
attribute_category ,
attribute1 ,
attribute2 ,
attribute3,
attribute4,
attribute5 ,
attribute6,
attribute7 ,
attribute8 ,
attribute9,
attribute10,
attribute11,
attribute12 ,
attribute13,
attribute14 ,
attribute15
into
l_attribute_category ,
l_attribute1 ,
l_attribute2 ,
l_attribute3,
l_attribute4,
l_attribute5 ,
l_attribute6,
l_attribute7 ,
l_attribute8 ,
l_attribute9,
l_attribute10,
l_attribute11,
l_attribute12 ,
l_attribute13,
l_attribute14 ,
l_attribute15
from pa_budget_lines where
start_date = l_get_res_assign_id_csr.planning_start_date
and resource_assignment_id = l_get_res_assign_id_csr.resource_assignment_id;
pa_debug.g_err_stage := 'Inserting into Backup Table';
insert into pa_budget_lines_m_upg_dtrange
(
resource_assignment_id,
start_date,
last_update_date,
last_updated_by,
creation_date,
created_by ,
last_update_login,
end_date,
period_name,
quantity,
raw_cost,
burdened_cost ,
revenue ,
change_reason_code,
description ,
attribute_category ,
attribute1,
attribute2 ,
attribute3 ,
attribute4,
attribute5,
attribute6,
attribute7 ,
attribute8 ,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13 ,
attribute14 ,
attribute15,
raw_cost_source,
burdened_cost_source,
quantity_source ,
revenue_source ,
pm_product_code,
pm_budget_line_reference ,
cost_rejection_code ,
revenue_rejection_code ,
burden_rejection_code ,
other_rejection_code ,
code_combination_id ,
ccid_gen_status_code,
ccid_gen_rej_message ,
request_id ,
borrowed_revenue ,
tp_revenue_in,
tp_revenue_out ,
revenue_adj,
lent_resource_cost,
tp_cost_in ,
tp_cost_out,
cost_adj ,
unassigned_time_cost,
utilization_percent,
utilization_hours ,
utilization_adj ,
capacity,
head_count ,
head_count_adj,
projfunc_currency_code,
projfunc_cost_rate_type ,
projfunc_cost_exchange_rate,
projfunc_cost_rate_date_type ,
projfunc_cost_rate_date ,
projfunc_rev_rate_type ,
projfunc_rev_exchange_rate ,
projfunc_rev_rate_date_type ,
projfunc_rev_rate_date ,
project_currency_code ,
project_cost_rate_type ,
project_cost_exchange_rate ,
project_cost_rate_date_type ,
project_cost_rate_date,
project_raw_cost ,
project_burdened_cost ,
project_rev_rate_type ,
project_rev_exchange_rate,
project_rev_rate_date_type ,
project_rev_rate_date ,
project_revenue,
txn_currency_code,
txn_raw_cost,
txn_burdened_cost ,
txn_revenue,
bucketing_period_code,
budget_line_id ,
budget_version_id)
select
resource_assignment_id,
start_date,
last_update_date,
last_updated_by,
creation_date,
created_by ,
last_update_login,
end_date,
period_name,
quantity,
raw_cost,
burdened_cost ,
revenue ,
change_reason_code,
description ,
attribute_category ,
attribute1,
attribute2 ,
attribute3 ,
attribute4,
attribute5,
attribute6,
attribute7 ,
attribute8 ,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13 ,
attribute14 ,
attribute15,
raw_cost_source,
burdened_cost_source,
quantity_source ,
revenue_source ,
pm_product_code,
pm_budget_line_reference ,
cost_rejection_code ,
revenue_rejection_code ,
burden_rejection_code ,
other_rejection_code ,
code_combination_id ,
ccid_gen_status_code,
ccid_gen_rej_message ,
request_id ,
borrowed_revenue ,
tp_revenue_in,
tp_revenue_out ,
revenue_adj,
lent_resource_cost,
tp_cost_in ,
tp_cost_out,
cost_adj ,
unassigned_time_cost,
utilization_percent,
utilization_hours ,
utilization_adj ,
capacity,
head_count ,
head_count_adj,
projfunc_currency_code,
projfunc_cost_rate_type ,
projfunc_cost_exchange_rate,
projfunc_cost_rate_date_type ,
projfunc_cost_rate_date ,
projfunc_rev_rate_type ,
projfunc_rev_exchange_rate ,
projfunc_rev_rate_date_type ,
projfunc_rev_rate_date ,
project_currency_code ,
project_cost_rate_type ,
project_cost_exchange_rate ,
project_cost_rate_date_type ,
project_cost_rate_date,
project_raw_cost ,
project_burdened_cost ,
project_rev_rate_type ,
project_rev_exchange_rate,
project_rev_rate_date_type ,
project_rev_rate_date ,
project_revenue,
txn_currency_code,
txn_raw_cost,
txn_burdened_cost ,
txn_revenue,
bucketing_period_code,
budget_line_id ,
budget_version_id from pa_budget_lines where
budget_version_id = l_get_elig_bud_ver_csr.budget_version_id
and resource_assignment_id = l_get_res_assign_id_csr.resource_assignment_id;
l_get_budget_lines_tbl.delete; /* bug 3673111: moved here from loop below */
l_plan_txn_post_upg_qty_tbl.delete;
l_plan_txn_act_qty_tbl.delete;
l_plan_txn_post_upg_rc_tbl.delete;
l_plan_txn_act_rc_tbl.delete;
l_plan_txn_post_upg_bc_tbl.delete;
l_plan_txn_act_bc_tbl.delete;
l_plan_txn_post_upg_rev_tbl.delete;
l_plan_txn_act_rev_tbl.delete;
l_last_bl_indx_in_plan_txn_tbl.delete;
l_max_st_dt_in_plan_txn_tbl.delete;
delete pa_budget_lines
where resource_assignment_id = l_get_res_assign_id_csr.resource_assignment_id and
budget_version_id = l_get_elig_bud_ver_csr.budget_version_id and
txn_currency_code = l_get_budget_lines_tbl(i).txn_currency_code;
insert into pa_budget_lines(
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
start_date,
end_date,
resource_assignment_id,
txn_currency_code,
quantity,
raw_cost,
burdened_cost,
revenue,
project_raw_cost,
project_burdened_cost,
project_revenue,
txn_raw_cost,
txn_burdened_cost,
txn_revenue,
budget_line_id,
budget_version_id,
PERIOD_NAME, /* bug 3673111 */
--Bug 4046524.Columns included for this bug start here
project_currency_code,
projfunc_currency_code,
projfunc_cost_rate_type ,
projfunc_cost_exchange_rate,
projfunc_cost_rate_date_type,
projfunc_cost_rate_date,
projfunc_rev_rate_type,
projfunc_rev_exchange_rate,
projfunc_rev_rate_date_type,
projfunc_rev_rate_date,
project_cost_rate_type ,
project_cost_exchange_rate ,
project_cost_rate_date_type ,
project_cost_rate_date,
project_rev_rate_type,
project_rev_exchange_rate,
project_rev_rate_date_type,
project_rev_rate_date
--Bug 4046524.Columns included for this bug end here
)
select
sysdate,
-1,
sysdate,
-1,
-1,
l_get_budget_lines_tbl(i).gl_start_date,
l_get_budget_lines_tbl(i).gl_end_date,
l_get_budget_lines_tbl(i).resource_assignment_id, /* bug 3673111 */
l_get_budget_lines_tbl(i).txn_currency_code,
l_get_budget_lines_tbl(i).spr_quantity,
l_get_budget_lines_tbl(i).spr_raw_cost,
l_get_budget_lines_tbl(i).spr_burdened_cost,
l_get_budget_lines_tbl(i).spr_revenue,
l_get_budget_lines_tbl(i).spr_project_raw_cost,
l_get_budget_lines_tbl(i).spr_project_burdened_cost,
l_get_budget_lines_tbl(i).spr_project_revenue,
l_get_budget_lines_tbl(i).spr_txn_raw_cost,
l_get_budget_lines_tbl(i).spr_txn_burdened_cost,
l_get_budget_lines_tbl(i).spr_txn_revenue,
pa_budget_lines_s.nextval,
l_get_elig_bud_ver_csr.budget_version_id,
l_get_budget_lines_tbl(i).PERIOD_NAME, /* bug 3673111 */
--Bug 4046524.Columns included for this bug start here
l_get_elig_bud_ver_csr.project_currency_code,
l_get_elig_bud_ver_csr.projfunc_currency_code,
'User', --projfunc_cost_rate_type
DECODE(NVL(l_get_budget_lines_tbl(i).spr_txn_raw_cost,0), --projfunc_cost_exchange_rate
0,0,
l_get_budget_lines_tbl(i).spr_raw_cost/l_get_budget_lines_tbl(i).spr_txn_raw_cost),
NULL, --projfunc_cost_rate_date_type
NULL, --projfunc_cost_rate_date
'User', --projfunc_rev_rate_type
DECODE(NVL(l_get_budget_lines_tbl(i).spr_txn_revenue,0), --projfunc_rev_exchange_rate
0,0,
l_get_budget_lines_tbl(i).spr_revenue/l_get_budget_lines_tbl(i).spr_txn_revenue),
NULL, --projfunc_rev_rate_date_type
NULL, --projfunc_rev_rate_date
'User', --project_cost_rate_type
DECODE(NVL(l_get_budget_lines_tbl(i).spr_txn_raw_cost,0), --project_cost_exchange_rate
0,0,
l_get_budget_lines_tbl(i).spr_project_raw_cost/l_get_budget_lines_tbl(i).spr_txn_raw_cost),
NULL, --project_cost_rate_date_type
NULL, --project_cost_rate_date
'User', --project_rev_rate_type
DECODE(NVL(l_get_budget_lines_tbl(i).spr_txn_revenue,0), --project_rev_exchange_rate
0,0,
l_get_budget_lines_tbl(i).spr_project_revenue/l_get_budget_lines_tbl(i).spr_txn_revenue),
NULL, --project_rev_rate_date_type
NULL --project_rev_rate_date
--Bug 4046524.Columns included for this bug end here
from dual;
update pa_budget_lines
set
attribute_category = l_attribute_category ,
attribute1 = l_attribute1 ,
attribute2 = l_attribute2 ,
attribute3 = l_attribute3,
attribute4 = l_attribute4,
attribute5 = l_attribute5 ,
attribute6 = l_attribute6,
attribute7 = l_attribute7 ,
attribute8 = l_attribute8 ,
attribute9 = l_attribute9,
attribute10 = l_attribute10,
attribute11 = l_attribute11,
attribute12 = l_attribute12 ,
attribute13 = l_attribute13,
attribute14 = l_attribute14 ,
attribute15 = l_attribute15
where start_date = l_min_date
and resource_assignment_id = l_get_res_assign_id_csr.resource_assignment_id;
SELECT 1
FROM dual
WHERE EXISTS
(SELECT 1
FROM (SELECT to_number(NVL(SUM(
(decode(least(bl.start_date, gl.start_date),
bl.start_date,decode(least(bl.end_date,gl.end_date),
gl.end_date,gl.end_date-gl.start_date+1,
bl.end_date,bl.end_date-gl.start_date+1),
gl.start_date,decode(least(bl.end_date,gl.end_date),
gl.end_date,gl.end_date-bl.start_date+1,
bl.end_date,bl.end_date-bl.start_date+1))
)),0)-(bl.end_date-bl.start_date+1)) factor
FROM pa_budget_lines bl,
(SELECT gl.start_date start_date,
gl.end_date end_date,
gl.period_name period_name
FROM gl_periods gl, pa_implementations_all pi, gl_sets_of_books sob
WHERE gl.period_type=c_period_type
AND sob.set_of_books_id=pi.set_of_books_id
AND nvl(pi.org_id,-99)=nvl(p_org_id,-99)
AND gl.adjustment_period_flag='N'
AND gl.period_set_name=sob.period_set_name
UNION ALL
SELECT to_date(NULL) start_date,
to_date(NULL) end_date,
to_char(NULL) period_name
FROM dual) gl
WHERE bl.budget_version_id = p_budget_version_id
AND( (bl.start_date BETWEEN gl.start_date AND gl.end_date
OR bl.end_date BETWEEN gl.start_date AND gl.end_date
OR (gl.start_date > bl.start_date AND gl.end_date < bl.end_date))
OR gl.start_date IS NULL)
GROUP BY bl.budget_line_id,bl.start_date,bl.end_date) pbl
WHERE pbl.factor<>0);