[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE pji_sys_settings_update_row (p_organization_structure IN VARCHAR2,
p_org_structure_version IN VARCHAR2,
p_dflt_prjpip_period_type IN VARCHAR2,
p_dflt_prjpip_as_of_date IN VARCHAR2,
p_dflt_prjpip_cycle IN VARCHAR2,
p_dflt_prjbab_period_type IN VARCHAR2,
p_dflt_prjbab_as_of_date IN VARCHAR2,
p_dflt_prjbab_cycle IN VARCHAR2,
p_dflt_resutl_period_type IN VARCHAR2,
p_dflt_resutl_as_of_date IN VARCHAR2,
p_dflt_resutl_cycle IN VARCHAR2,
p_dflt_resavl_period_type IN VARCHAR2,
p_dflt_resavl_as_of_date IN VARCHAR2,
p_dflt_resavl_cycle IN VARCHAR2,
p_dflt_respln_period_type IN VARCHAR2,
p_dflt_respln_as_of_date IN VARCHAR2,
p_dflt_respln_cycle IN VARCHAR2,
p_dflt_prjhlt_period_type IN VARCHAR2,
p_dflt_prjhlt_as_of_date IN VARCHAR2,
p_dflt_prjhlt_cycle IN VARCHAR2,
p_dflt_prjact_period_type IN VARCHAR2,
p_dflt_prjact_as_of_date IN VARCHAR2,
p_dflt_prjact_cycle IN VARCHAR2,
p_dflt_prjprf_period_type IN VARCHAR2,
p_dflt_prjprf_as_of_date IN VARCHAR2,
p_dflt_prjprf_cycle IN VARCHAR2,
p_dflt_prjcst_period_type IN VARCHAR2,
p_dflt_prjcst_as_of_date IN VARCHAR2,
p_dflt_prjcst_cycle IN VARCHAR2,
p_pa_period_flag IN VARCHAR2,
p_gl_period_flag IN VARCHAR2,
p_conversion_ratio_days IN NUMBER,
p_book_to_bill_days IN NUMBER,
p_dso_days IN NUMBER,
p_dormant_backlog_days IN NUMBER,
p_cost_budget_type IN VARCHAR2,
p_cost_budget_conv_rule IN VARCHAR2,
p_revenue_budget_type IN VARCHAR2,
p_revenue_budget_conv_rule IN VARCHAR2,
p_cost_forecast_type IN VARCHAR2,
p_cost_forecast_conv_rule IN VARCHAR2,
p_revenue_forecast_type IN VARCHAR2,
p_revenue_forecast_conv_rule IN VARCHAR2,
p_report_cost_type IN VARCHAR2,
p_report_labor_units IN VARCHAR2,
p_rolling_weeks IN NUMBER,
p_config_proj_perf_flag IN VARCHAR2,
p_config_cost_flag IN VARCHAR2,
p_config_profit_flag IN VARCHAR2,
p_config_util_flag IN VARCHAR2,
p_cost_fp_type_id IN NUMBER,
p_revenue_fp_type_id IN NUMBER,
p_cost_forecast_fp_type_id IN NUMBER,
p_revenue_forecast_fp_type_id IN NUMBER,
p_global_curr2_flag IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_error_message_code OUT NOCOPY VARCHAR2
) IS
l_dflt_prjpip_cycle_id NUMBER ;
l_last_updated_by NUMBER;
l_last_update_date DATE;
l_last_update_login NUMBER;
l_last_updated_by := Fnd_Profile.value('USER_ID');
l_last_update_date := SYSDATE;
l_last_update_login := Fnd_Profile.value('USER_ID');
pji_insert_events_log (
p_organization_structure_id => l_organization_structure_id,
p_org_structure_version_id => l_org_structure_version_id,
p_dflt_prjpip_period_type => p_dflt_prjpip_period_type,
p_dflt_prjpip_as_of_date => p_dflt_prjpip_as_of_date,
p_dflt_prjbab_period_type => p_dflt_prjbab_period_type,
p_dflt_prjbab_as_of_date => p_dflt_prjbab_as_of_date,
p_dflt_resutl_period_type => p_dflt_resutl_period_type,
p_dflt_resutl_as_of_date => p_dflt_resutl_as_of_date,
p_dflt_resavl_period_type => p_dflt_resavl_period_type,
p_dflt_resavl_as_of_date => p_dflt_resavl_as_of_date,
p_dflt_respln_period_type => p_dflt_respln_period_type,
p_dflt_respln_as_of_date => p_dflt_respln_as_of_date,
p_dflt_prjhlt_period_type => p_dflt_prjhlt_period_type,
p_dflt_prjhlt_as_of_date => p_dflt_prjhlt_as_of_date,
p_dflt_prjact_period_type => p_dflt_prjact_period_type,
p_dflt_prjact_as_of_date => p_dflt_prjact_as_of_date,
p_dflt_prjprf_period_type => p_dflt_prjprf_period_type,
p_dflt_prjprf_as_of_date => p_dflt_prjprf_as_of_date,
p_dflt_prjcst_period_type => p_dflt_prjcst_period_type,
p_dflt_prjcst_as_of_date => p_dflt_prjcst_as_of_date,
p_pa_period_flag => p_pa_period_flag,
p_gl_period_flag => p_gl_period_flag,
p_conversion_ratio_days => p_conversion_ratio_days,
p_book_to_bill_days => p_book_to_bill_days,
p_dso_days => p_dso_days,
p_dormant_backlog_days => p_dormant_backlog_days,
p_cost_budget_type_code => l_cost_budget_type_code,
p_cost_budget_conv_rule => p_cost_budget_conv_rule,
p_revenue_budget_type_code => l_revenue_budget_type_code,
p_revenue_budget_conv_rule => p_revenue_budget_conv_rule,
p_cost_forecast_type_code => l_cost_forecast_type_code,
p_cost_forecast_conv_rule => p_cost_forecast_conv_rule,
p_revenue_forecast_type_code => l_revenue_forecast_type_code,
p_revenue_forecast_conv_rule => p_revenue_forecast_conv_rule,
p_report_cost_type => p_report_cost_type,
p_report_labor_units => p_report_labor_units,
p_rolling_weeks => p_rolling_weeks,
p_config_proj_perf_flag => p_config_proj_perf_flag,
p_config_cost_flag => p_config_cost_flag,
p_config_profit_flag => p_config_profit_flag,
p_config_util_flag => p_config_util_flag,
p_cost_fp_type_id => p_cost_fp_type_id,
p_revenue_fp_type_id => p_revenue_fp_type_id,
p_cost_forecast_fp_type_id => p_cost_forecast_fp_type_id,
p_revenue_forecast_fp_type_id => p_revenue_forecast_fp_type_id,
p_global_curr2_flag => p_global_curr2_flag,
x_return_status => x_return_status,
x_error_message_code => x_error_message_code);
UPDATE pji_system_settings
SET last_update_date = l_last_update_date,
last_updated_by = l_last_updated_by,
last_update_login = l_last_update_login,
organization_structure_id = l_organization_structure_id,
org_structure_version_id = l_org_structure_version_id,
dflt_prjpip_period_type = p_dflt_prjpip_period_type,
dflt_prjpip_as_of_date = p_dflt_prjpip_as_of_date,
dflt_prjbab_period_type = p_dflt_prjbab_period_type,
dflt_prjbab_as_of_date = p_dflt_prjbab_as_of_date,
dflt_resutl_period_type = p_dflt_resutl_period_type,
dflt_resutl_as_of_date = p_dflt_resutl_as_of_date,
dflt_resavl_period_type = p_dflt_resavl_period_type,
dflt_resavl_as_of_date = p_dflt_resavl_as_of_date,
dflt_respln_period_type = p_dflt_respln_period_type,
dflt_respln_as_of_date = p_dflt_respln_as_of_date,
dflt_prjhlt_period_type = p_dflt_prjhlt_period_type,
dflt_prjhlt_as_of_date = p_dflt_prjhlt_as_of_date,
dflt_prjact_period_type = p_dflt_prjact_period_type,
dflt_prjact_as_of_date = p_dflt_prjact_as_of_date,
dflt_prjprf_period_type = p_dflt_prjprf_period_type,
dflt_prjprf_as_of_date = p_dflt_prjprf_as_of_date,
dflt_prjcst_period_type = p_dflt_prjcst_period_type,
dflt_prjcst_as_of_date = p_dflt_prjcst_as_of_date,
pa_period_flag = p_pa_period_flag,
gl_period_flag = p_gl_period_flag,
conversion_ratio_days = p_conversion_ratio_days,
book_to_bill_days = p_book_to_bill_days,
dso_days = p_dso_days,
dormant_backlog_days = p_dormant_backlog_days,
cost_budget_type_code = l_cost_budget_type_code,
cost_budget_conv_rule = p_cost_budget_conv_rule,
revenue_budget_type_code = l_revenue_budget_type_code,
revenue_budget_conv_rule = p_revenue_budget_conv_rule,
cost_forecast_type_code = l_cost_forecast_type_code,
cost_forecast_conv_rule = p_cost_forecast_conv_rule,
revenue_forecast_type_code = l_revenue_forecast_type_code,
revenue_forecast_conv_rule = p_revenue_forecast_conv_rule,
report_cost_type = p_report_cost_type,
report_labor_units = p_report_labor_units,
rolling_weeks = p_rolling_weeks,
config_proj_perf_flag = p_config_proj_perf_flag,
config_cost_flag = p_config_cost_flag,
config_profit_flag = p_config_profit_flag,
config_util_flag = p_config_util_flag,
cost_fp_type_id = p_cost_fp_type_id,
revenue_fp_type_id = p_revenue_fp_type_id,
cost_forecast_fp_type_id = p_cost_forecast_fp_type_id,
revenue_forecast_fp_type_id = p_revenue_forecast_fp_type_id,
global_curr2_flag = p_global_curr2_flag;
END pji_sys_settings_update_row;
PROCEDURE pji_mt_pip_update_row (p_name IN VARCHAR2,
p_seq IN NUMBER,
p_bucket_set_code IN VARCHAR2,
p_default_flag IN VARCHAR2,
p_from_value IN NUMBER,
p_to_value IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_error_message_code OUT NOCOPY VARCHAR2
) IS
l_created_by NUMBER;
l_last_updated_by NUMBER;
l_last_update_date DATE;
l_last_update_login NUMBER;
l_last_updated_by := Fnd_Profile.value('USER_ID');
l_last_update_date := SYSDATE;
l_last_update_login := Fnd_Profile.value('USER_ID');
UPDATE pji_mt_buckets
SET name = p_name,
last_update_date = l_last_update_date,
seq = p_seq,
bucket_set_code = p_bucket_set_code,
default_flag = p_default_flag,
from_value = p_from_value,
to_value = p_to_value
WHERE bucket_set_code = p_bucket_set_code
AND seq = p_seq;
END pji_mt_pip_update_row;
PROCEDURE pji_mt_res_avl_dur_update_row (p_name IN VARCHAR2,
p_seq IN NUMBER,
p_bucket_set_code IN VARCHAR2,
p_default_flag IN VARCHAR2,
p_from_value IN NUMBER,
p_to_value IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_error_message_code OUT NOCOPY VARCHAR2
) IS
l_created_by NUMBER;
l_last_updated_by NUMBER;
l_last_update_date DATE;
l_last_update_login NUMBER;
l_last_updated_by := Fnd_Profile.value('USER_ID');
l_last_update_date := SYSDATE;
l_last_update_login := Fnd_Profile.value('USER_ID');
UPDATE pji_mt_buckets
SET name = p_name,
last_update_date = l_last_update_date,
seq = p_seq,
bucket_set_code = p_bucket_set_code,
default_flag = p_default_flag,
from_value = p_from_value,
to_value = p_to_value
WHERE bucket_set_code = p_bucket_set_code
AND seq = p_seq;
END pji_mt_res_avl_dur_update_row;
PROCEDURE pji_mt_dls_update_row (p_name IN VARCHAR2,
p_seq IN NUMBER,
p_bucket_set_code IN VARCHAR2,
p_default_flag IN VARCHAR2,
p_from_value IN NUMBER,
p_to_value IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_error_message_code OUT NOCOPY VARCHAR2
) IS
l_created_by NUMBER;
l_last_updated_by NUMBER;
l_last_update_date DATE;
l_last_update_login NUMBER;
l_last_updated_by := Fnd_Profile.value('USER_ID');
l_last_update_date := SYSDATE;
l_last_update_login := Fnd_Profile.value('USER_ID');
UPDATE pji_mt_buckets
SET name = p_name,
last_update_date = l_last_update_date,
seq = p_seq,
bucket_set_code = p_bucket_set_code,
default_flag = p_default_flag,
from_value = p_from_value,
to_value = p_to_value
WHERE bucket_set_code = p_bucket_set_code
AND seq = p_seq;
END pji_mt_dls_update_row;
PROCEDURE pji_mt_avl_update_row (p_name IN VARCHAR2,
p_seq IN NUMBER,
p_bucket_set_code IN VARCHAR2,
p_default_flag IN VARCHAR2,
p_from_value IN NUMBER,
p_to_value IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_error_message_code OUT NOCOPY VARCHAR2
) IS
l_created_by NUMBER;
l_last_updated_by NUMBER;
l_last_update_date DATE;
l_last_update_login NUMBER;
l_last_updated_by := Fnd_Profile.value('USER_ID');
l_last_update_date := SYSDATE;
l_last_update_login := Fnd_Profile.value('USER_ID');
UPDATE pji_mt_buckets
SET name = p_name,
last_update_date = l_last_update_date,
seq = p_seq,
bucket_set_code = p_bucket_set_code,
default_flag = p_default_flag,
from_value = p_from_value,
to_value = p_to_value
WHERE bucket_set_code = p_bucket_set_code
AND seq = p_seq;
END pji_mt_avl_update_row;
SELECT 1 FROM DUAL
WHERE EXISTS (
SELECT 'X'
FROM pji_mt_buckets a,
pji_mt_buckets b
WHERE a.bucket_set_code = b.bucket_set_code
AND a.bucket_set_code = c_bucket_set_code
AND a.from_value <= b.to_value
AND b.from_value < b.to_value
AND NVL(a.to_value, 99999999999999999999999) > b.to_value );
SELECT 2 FROM DUAL
WHERE EXISTS (
SELECT 'X'
FROM pji_mt_buckets a,
pji_mt_buckets b
WHERE a.bucket_set_code = b.bucket_set_code
AND a.bucket_set_code = c_bucket_set_code
AND (b.from_value - a.to_value) > 1
AND NVL(b.to_value, 99999999999999999999999) > b.from_value
AND a.to_value > a.from_value
AND NOT EXISTS ( SELECT 'X'
FROM pji_mt_buckets c
WHERE c.from_value > a.to_value
AND c.to_value < b.from_value ));
SELECT 3 FROM DUAL
WHERE EXISTS (SELECT 'X'
FROM pji_mt_buckets a,
pji_mt_buckets b
WHERE a.bucket_set_code = b.bucket_set_code
AND a.bucket_set_code = 'PJI_RESOURCE_AVAILABILITY'
AND a.seq > b.seq
AND b.to_value > a.to_value) ;
SELECT MIN(a.from_value), MAX(a.to_value)
INTO l_check_min_probability, l_check_max_probability
FROM pji_mt_buckets a
WHERE a.bucket_set_code = 'PJI_PIPELINE_PROBABILITY' ;
SELECT MIN(a.from_value), MIN(NVL(a.to_value,1000))
INTO l_check_min_probability, l_check_max_probability
FROM pji_mt_buckets a
WHERE a.bucket_set_code = 'PJI_RES_AVL_DAYS' ;
PROCEDURE pji_insert_events_log (
p_organization_structure_id IN NUMBER,
p_org_structure_version_id IN NUMBER,
p_dflt_prjpip_period_type IN VARCHAR2,
p_dflt_prjpip_as_of_date IN VARCHAR2,
p_dflt_prjbab_period_type IN VARCHAR2,
p_dflt_prjbab_as_of_date IN VARCHAR2,
p_dflt_resutl_period_type IN VARCHAR2,
p_dflt_resutl_as_of_date IN VARCHAR2,
p_dflt_resavl_period_type IN VARCHAR2,
p_dflt_resavl_as_of_date IN VARCHAR2,
p_dflt_respln_period_type IN VARCHAR2,
p_dflt_respln_as_of_date IN VARCHAR2,
p_dflt_prjhlt_period_type IN VARCHAR2,
p_dflt_prjhlt_as_of_date IN VARCHAR2,
p_dflt_prjact_period_type IN VARCHAR2,
p_dflt_prjact_as_of_date IN VARCHAR2,
p_dflt_prjprf_period_type IN VARCHAR2,
p_dflt_prjprf_as_of_date IN VARCHAR2,
p_dflt_prjcst_period_type IN VARCHAR2,
p_dflt_prjcst_as_of_date IN VARCHAR2,
p_pa_period_flag IN VARCHAR2,
p_gl_period_flag IN VARCHAR2,
p_conversion_ratio_days IN VARCHAR2,
p_book_to_bill_days IN NUMBER,
p_dso_days IN NUMBER,
p_dormant_backlog_days IN NUMBER,
p_cost_budget_type_code IN VARCHAR2,
p_cost_budget_conv_rule IN VARCHAR2,
p_revenue_budget_type_code IN VARCHAR2,
p_revenue_budget_conv_rule IN VARCHAR2,
p_cost_forecast_type_code IN VARCHAR2,
p_cost_forecast_conv_rule IN VARCHAR2,
p_revenue_forecast_type_code IN VARCHAR2,
p_revenue_forecast_conv_rule IN VARCHAR2,
p_report_cost_type IN VARCHAR2,
p_report_labor_units IN VARCHAR2,
p_rolling_weeks IN NUMBER,
p_config_proj_perf_flag IN VARCHAR2,
p_config_cost_flag IN VARCHAR2,
p_config_profit_flag IN VARCHAR2,
p_config_util_flag IN VARCHAR2,
p_cost_fp_type_id IN NUMBER,
p_revenue_fp_type_id IN NUMBER,
p_cost_forecast_fp_type_id IN NUMBER,
p_revenue_forecast_fp_type_id IN NUMBER,
p_global_curr2_flag IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_error_message_code OUT NOCOPY VARCHAR2) IS
CURSOR Cur_PjiSysSettings IS
SELECT *
FROM pji_system_settings ;
Pa_Pji_Proj_Events_Log_Pkg.Insert_Row(
X_ROW_ID => l_pji_rowid
,X_EVENT_ID => l_pji_event_id
,X_EVENT_TYPE => 'PJI_SETUP_CHANGE'
,X_EVENT_OBJECT => EventAttribTabTyp(ROWS).attribute_name
,X_OPERATION_TYPE => 'U' -- update mode
,X_STATUS => 'X' --NULL
,X_ATTRIBUTE_CATEGORY => NULL
,X_ATTRIBUTE1 => EventAttribTabTyp(ROWS).attribute_new_value
,X_ATTRIBUTE2 => EventAttribTabTyp(ROWS).attribute_old_value
,X_ATTRIBUTE3 => NULL
,X_ATTRIBUTE4 => NULL
,X_ATTRIBUTE5 => NULL
,X_ATTRIBUTE6 => NULL
,X_ATTRIBUTE7 => NULL
,X_ATTRIBUTE8 => NULL
,X_ATTRIBUTE9 => NULL
,X_ATTRIBUTE10 => NULL
,X_ATTRIBUTE11 => NULL
,X_ATTRIBUTE12 => NULL
,X_ATTRIBUTE13 => NULL
,X_ATTRIBUTE14 => NULL
,X_ATTRIBUTE15 => NULL
,X_ATTRIBUTE16 => NULL
,X_ATTRIBUTE17 => NULL
,X_ATTRIBUTE18 => NULL
,X_ATTRIBUTE19 => NULL
,X_ATTRIBUTE20 => NULL
);
END pji_insert_events_log ;
SELECT s.organization_structure_id
FROM per_organization_structures s
WHERE s.name = p_Org_structure ;
SELECT v.org_structure_version_id
FROM per_org_structure_versions v
WHERE v.organization_structure_id = p_Org_structure_id
AND v.version_number = p_Org_structure_version ;
SELECT bt.budget_type_code
FROM pa_budget_types bt
WHERE bt.budget_type = p_budget_type
AND bt.budget_amount_code = p_amount_type_code ;