[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_last_update_login IN NUMBER DEFAULT fnd_global.user_id,
--start:| 16-FEB-2009 cklee R12.1.2 setup ehancement
P_APPROVAL_TYPE_CODE IN VARCHAR2 DEFAULT 'STANDARD',
P_SUBCONTRACTOR_REPORTING_FLAG IN VARCHAR2 DEFAULT 'N',
P_PREFIX_AUTO_NUMBER IN VARCHAR2 DEFAULT NULL,
--end:| 16-FEB-2009 cklee R12.1.2 setup ehancement
--|start 29-APR-2009 cklee R12.1.2 setup ehancement v2
P_IMPACT_BUDGET_TYPE_CODE IN VARCHAR2 DEFAULT 'NA',
P_COST_COL_FLAG IN VARCHAR2 DEFAULT 'N',
P_REV_COL_FLAG IN VARCHAR2 DEFAULT 'N',
P_DIR_COST_REG_FLAG IN VARCHAR2 DEFAULT 'N',
P_SUPP_COST_REG_FLAG IN VARCHAR2 DEFAULT 'N',
P_DIR_REG_REV_COL_FLAG IN VARCHAR2 DEFAULT 'N',
--|end 29-APR-2009 cklee R12.1.2 setup ehancement v2
x_ci_type_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
x_dist_list_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
p_status_list_id IN NUMBER
)
IS
l_rowid VARCHAR2(30);
SELECT pa_ci_types_b_s.NEXTVAL
INTO x_ci_type_id
FROM sys.dual;
pa_ci_types_pkg.insert_row(
x_rowid => l_rowid,
x_ci_type_id => x_ci_type_id,
x_ci_type_class_code => p_ci_type_class_code,
x_auto_number_flag => p_auto_number_flag,
x_resolution_required_flag => p_resolution_required_flag,
x_approval_required_flag => l_approval_required_flag, --28-oct-2009 cklee fxied bug: 9063248--p_approval_required_flag,
x_source_attrs_enabled_flag => p_source_attrs_enabled_flag,
x_allow_all_usage_flag => p_allow_all_usage_flag,
x_record_version_number => 0,
x_start_date_active => p_start_date_active,
x_end_date_active => p_end_date_active,
x_classification_category => p_classification_category,
x_reason_category => p_reason_category,
x_resolution_category => p_resolution_category,
x_attribute_category => p_attribute_category,
x_attribute1 => p_attribute1,
x_attribute2 => p_attribute2,
x_attribute3 => p_attribute3,
x_attribute4 => p_attribute4,
x_attribute5 => p_attribute5,
x_attribute6 => p_attribute6,
x_attribute7 => p_attribute7,
x_attribute8 => p_attribute8,
x_attribute9 => p_attribute9,
x_attribute10 => p_attribute10,
x_attribute11 => p_attribute11,
x_attribute12 => p_attribute12,
x_attribute13 => p_attribute13,
x_attribute14 => p_attribute14,
x_attribute15 => p_attribute15,
x_name => p_name,
x_short_name => p_short_name,
x_description => p_description,
x_creation_date => p_creation_date,
x_created_by => p_created_by,
x_last_update_date => p_creation_date,
x_last_updated_by => p_created_by,
x_last_update_login => p_last_update_login,
--start:| 16-FEB-2009 cklee R12.1.2 setup ehancement
X_APPROVAL_TYPE_CODE => P_APPROVAL_TYPE_CODE,
X_SUBCONTRACTOR_REPORTING_FLAG => P_SUBCONTRACTOR_REPORTING_FLAG,
X_PREFIX_AUTO_NUMBER => P_PREFIX_AUTO_NUMBER,
--end:| 16-FEB-2009 cklee R12.1.2 setup ehancement
--|start 29-APR-2009 cklee R12.1.2 setup ehancement v2
X_IMPACT_BUDGET_TYPE_CODE => P_IMPACT_BUDGET_TYPE_CODE,
X_COST_COL_FLAG => P_COST_COL_FLAG,
X_REV_COL_FLAG => P_REV_COL_FLAG,
X_DIR_COST_REG_FLAG => P_DIR_COST_REG_FLAG,
X_SUPP_COST_REG_FLAG => P_SUPP_COST_REG_FLAG,
X_DIR_REG_REV_COL_FLAG => P_DIR_REG_REV_COL_FLAG);
SELECT pa_distribution_lists_s.NEXTVAL
INTO x_dist_list_id
FROM sys.dual;
p_last_update_date => p_creation_date,
p_last_updated_by => p_created_by,
p_last_update_login => p_last_update_login,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
SELECT pa_obj_status_lists_s.NEXTVAL
INTO l_obj_status_list_id
FROM sys.dual;
pa_obj_status_lists_pkg.INSERT_ROW (
X_ROWID => l_obj_sl_rowid,
X_OBJ_STATUS_LIST_ID => l_obj_status_list_id,
X_OBJECT_TYPE => 'PA_CI_TYPES',
X_OBJECT_ID => x_ci_type_id,
X_STATUS_LIST_ID => p_status_list_id,
X_STATUS_TYPE => 'CONTROL_ITEM',
X_CREATION_DATE => p_creation_date,
X_CREATED_BY => p_created_by,
X_LAST_UPDATE_DATE => p_creation_date,
X_LAST_UPDATED_BY => p_created_by,
X_LAST_UPDATE_LOGIN => p_last_update_login
);
p_last_update_date => p_creation_date,
p_last_updated_by => p_created_by,
p_last_update_login => p_last_update_login,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
PROCEDURE update_ci_type (
p_api_version IN NUMBER := 1.0,
p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
p_commit IN VARCHAR2 := FND_API.g_false,
p_validate_only IN VARCHAR2 := FND_API.g_true,
p_max_msg_count IN NUMBER := FND_API.g_miss_num,
p_ci_type_id IN NUMBER,
p_name IN VARCHAR2,
p_short_name IN VARCHAR2,
p_description IN VARCHAR2,
p_ci_type_class_code IN VARCHAR2,
p_auto_number_flag IN VARCHAR2,
p_resolution_required_flag IN VARCHAR2,
p_approval_required_flag IN VARCHAR2,
p_source_attrs_enabled_flag IN VARCHAR2,
p_allow_all_usage_flag IN VARCHAR2,
p_start_date_active IN DATE,
p_end_date_active IN DATE,
p_classification_category IN VARCHAR2,
p_reason_category IN VARCHAR2,
p_resolution_category IN VARCHAR2,
p_attribute_category IN VARCHAR2,
p_attribute1 IN VARCHAR2,
p_attribute2 IN VARCHAR2,
p_attribute3 IN VARCHAR2,
p_attribute4 IN VARCHAR2,
p_attribute5 IN VARCHAR2,
p_attribute6 IN VARCHAR2,
p_attribute7 IN VARCHAR2,
p_attribute8 IN VARCHAR2,
p_attribute9 IN VARCHAR2,
p_attribute10 IN VARCHAR2,
p_attribute11 IN VARCHAR2,
p_attribute12 IN VARCHAR2,
p_attribute13 IN VARCHAR2,
p_attribute14 IN VARCHAR2,
p_attribute15 IN VARCHAR2,
p_last_updated_by IN NUMBER DEFAULT fnd_global.user_id,
p_last_update_date IN DATE DEFAULT SYSDATE,
p_last_update_login IN NUMBER DEFAULT fnd_global.user_id,
--start:| 16-FEB-2009 cklee R12.1.2 setup ehancement
P_APPROVAL_TYPE_CODE IN VARCHAR2 DEFAULT 'STANDARD',
P_SUBCONTRACTOR_REPORTING_FLAG IN VARCHAR2 DEFAULT 'N',
P_PREFIX_AUTO_NUMBER IN VARCHAR2 DEFAULT NULL,
--end:| 16-FEB-2009 cklee R12.1.2 setup ehancement
--|start 29-APR-2009 cklee R12.1.2 setup ehancement v2
P_IMPACT_BUDGET_TYPE_CODE IN VARCHAR2 DEFAULT 'NA',
P_COST_COL_FLAG IN VARCHAR2 DEFAULT 'N',
P_REV_COL_FLAG IN VARCHAR2 DEFAULT 'N',
P_DIR_COST_REG_FLAG IN VARCHAR2 DEFAULT 'N',
P_SUPP_COST_REG_FLAG IN VARCHAR2 DEFAULT 'N',
P_DIR_REG_REV_COL_FLAG IN VARCHAR2 DEFAULT 'N',
--|end 29-APR-2009 cklee R12.1.2 setup ehancement v2
p_record_version_number IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
p_status_list_id IN NUMBER,
p_obj_status_list_id IN NUMBER
)
IS
l_temp VARCHAR2(1);
select 1
from pa_control_items ci,
pa_ci_types_b cip
where ci.ci_type_id = cip.ci_type_id
and cip.ci_type_class_code = 'CHANGE_REQUEST'
and cip.ci_type_id = p_ci_type_id
and cip.approval_type_code = 'EXTERNAL_APPROVAL'
and ci.pco_status_code is not null;
select ci.IMPACT_BUDGET_TYPE_CODE,
ci.COST_COL_FLAG,
ci.REV_COL_FLAG,
ci.DIR_COST_REG_FLAG,
ci.SUPP_COST_REG_FLAG,
ci.DIR_REG_REV_COL_FLAG
from pa_ci_types_b ci
where ci.ci_type_id = p_ci_type_id;
select 1
from pa_control_items pci,
pa_ci_impacts pc
where pci.ci_type_id = p_ci_type_id
and pci.ci_id = pc.ci_id;
select meaning
from pa_lookups
where lookup_type = 'PA_CI_IMPACT_BUDGET_TYPES'
and lookup_code = p_lookup_code;
l_update_budget_method varchar2(30);
select pc.impact_type_code,
luk.meaning impact_type_name
from pa_control_items pci,
pa_ci_impacts pc,
pa_lookups luk
where pci.ci_type_id = p_ci_type_id
and pci.ci_id = pc.ci_id
and pc.impact_type_code = luk.lookup_code
and luk.lookup_type = 'PA_CI_IMPACT_TYPES'
and pc.impact_type_code = p_impact_type_code;
select pc.impact_type_code,
luk.meaning impact_type_name
from pa_control_items pci,
pa_ci_impacts pc,
pa_lookups luk,
pa_budget_versions pbv,
pa_resource_assignments pra
where pci.ci_type_id = p_ci_type_id
and pci.ci_id = pc.ci_id
and pc.impact_type_code = luk.lookup_code
and luk.lookup_type = 'PA_CI_IMPACT_TYPES'
and pc.impact_type_code = p_impact_type_code
and pbv.ci_id = pci.ci_id
and pra.budget_version_id = pbv.budget_version_id
and total_plan_raw_cost is not null
and not exists (select 1
from pa_ci_supplier_details sup_det
where sup_det.ci_id=pbv.ci_id
and sup_det.task_id=pra.task_id
and sup_det.resource_list_member_id=pra.resource_list_member_id);
select pc.impact_type_code,
luk.meaning impact_type_name
from pa_control_items pci,
pa_ci_impacts pc,
pa_lookups luk,
pa_budget_versions pbv,
pa_resource_assignments pra
where pci.ci_type_id = p_ci_type_id
and pci.ci_id = pc.ci_id
and pc.impact_type_code = luk.lookup_code
and luk.lookup_type = 'PA_CI_IMPACT_TYPES'
and pc.impact_type_code = p_impact_type_code
and pbv.ci_id = pci.ci_id
and pra.budget_version_id = pbv.budget_version_id
and total_plan_raw_cost is not null
and exists (select 1
from pa_ci_supplier_details sup_det
where sup_det.ci_id=pbv.ci_id
and sup_det.task_id=pra.task_id
and sup_det.resource_list_member_id=pra.resource_list_member_id);
select 1
from pa_ci_types_v pct,
pa_control_items pci,
pa_budget_versions pbv
where pct.ci_type_id = pci.ci_type_id
and pci.ci_id = pbv.ci_id
and pct.ci_type_id = p_ci_type_id;
select luk.meaning impact_type_name
from pa_lookups luk
where luk.lookup_type = 'PA_CI_IMPACT_TYPES'
and luk.lookup_code = p_impact_type_code;
pa_debug.set_err_stack ('PA_CI_TYPES_PVT.UPDATE_CI_TYPE');
SAVEPOINT update_ci_type;
FETCH budget_method INTO l_update_budget_method;
,p_value1 => l_update_budget_method);
FETCH budget_method INTO l_update_budget_method;
,p_value1 => l_update_budget_method);
SELECT classification_category,
reason_category,
resolution_category
INTO l_classification_category,
l_reason_category,
l_resolution_category
FROM pa_ci_types_vl
WHERE ci_type_id = p_ci_type_id
AND record_version_number = p_record_version_number;
SELECT 'X' INTO l_temp
FROM PA_CONTROL_ITEMS
WHERE ci_type_id = p_ci_type_id
AND classification_code_id IS NOT NULL
AND ROWNUM < 2;
SELECT 'X' INTO l_temp
FROM PA_CONTROL_ITEMS
WHERE ci_type_id = p_ci_type_id
AND reason_code_id IS NOT NULL
AND ROWNUM < 2;
SELECT 'X' INTO l_temp
FROM PA_CONTROL_ITEMS
WHERE ci_type_id = p_ci_type_id
AND resolution_code_id IS NOT NULL
AND ROWNUM < 2;
SELECT 'X'
INTO l_temp
FROM pa_ci_types_b cit,
pa_control_items ci
WHERE cit.ci_type_id = p_ci_type_id
AND cit.auto_number_flag <> 'Y'
AND ci.ci_type_id = p_ci_type_id
AND ROWNUM=1;
pa_ci_types_pkg.update_row(
x_ci_type_id => p_ci_type_id,
x_ci_type_class_code => p_ci_type_class_code,
x_auto_number_flag => p_auto_number_flag,
x_resolution_required_flag => p_resolution_required_flag,
x_approval_required_flag => l_approval_required_flag,----28-oct-2009 cklee fxied bug: 9063248p_approval_required_flag,
x_source_attrs_enabled_flag => p_source_attrs_enabled_flag,
x_allow_all_usage_flag => p_allow_all_usage_flag,
x_record_version_number => p_record_version_number,
x_start_date_active => p_start_date_active,
x_end_date_active => p_end_date_active,
x_classification_category => p_classification_category,
x_reason_category => p_reason_category,
x_resolution_category => p_resolution_category,
x_attribute_category => p_attribute_category,
x_attribute1 => p_attribute1,
x_attribute2 => p_attribute2,
x_attribute3 => p_attribute3,
x_attribute4 => p_attribute4,
x_attribute5 => p_attribute5,
x_attribute6 => p_attribute6,
x_attribute7 => p_attribute7,
x_attribute8 => p_attribute8,
x_attribute9 => p_attribute9,
x_attribute10 => p_attribute10,
x_attribute11 => p_attribute11,
x_attribute12 => p_attribute12,
x_attribute13 => p_attribute13,
x_attribute14 => p_attribute14,
x_attribute15 => p_attribute15,
x_name => p_name,
x_short_name => p_short_name,
x_description => p_description,
x_last_update_date => p_last_update_date,
x_last_updated_by => p_last_updated_by,
x_last_update_login => p_last_update_login,
--start:| 16-FEB-2009 cklee R12.1.2 setup ehancement
X_APPROVAL_TYPE_CODE => P_APPROVAL_TYPE_CODE,
X_SUBCONTRACTOR_REPORTING_FLAG => P_SUBCONTRACTOR_REPORTING_FLAG,
X_PREFIX_AUTO_NUMBER => P_PREFIX_AUTO_NUMBER,
--end:| 16-FEB-2009 cklee R12.1.2 setup ehancement
--|start 29-APR-2009 cklee R12.1.2 setup ehancement v2
X_IMPACT_BUDGET_TYPE_CODE => P_IMPACT_BUDGET_TYPE_CODE,
X_COST_COL_FLAG => P_COST_COL_FLAG,
X_REV_COL_FLAG => P_REV_COL_FLAG,
X_DIR_COST_REG_FLAG => P_DIR_COST_REG_FLAG,
X_SUPP_COST_REG_FLAG => P_SUPP_COST_REG_FLAG,
X_DIR_REG_REV_COL_FLAG => P_DIR_REG_REV_COL_FLAG);
pa_obj_status_lists_pkg.UPDATE_ROW (
X_OBJ_STATUS_LIST_ID => p_obj_status_list_id,
X_OBJECT_TYPE => 'PA_CI_TYPES',
X_OBJECT_ID => p_ci_type_id,
X_STATUS_LIST_ID => p_status_list_id,
X_STATUS_TYPE => 'CONTROL_ITEM',
X_LAST_UPDATE_DATE => p_last_update_date,
X_LAST_UPDATED_BY => p_last_updated_by,
X_LAST_UPDATE_LOGIN => p_last_update_login
);
ROLLBACK TO update_ci_type;
ROLLBACK TO update_ci_type;
ROLLBACK TO update_ci_type;
ROLLBACK TO update_ci_type;
p_procedure_name => 'UPDATE_CI_TYPE',
p_error_text => SUBSTRB(SQLERRM,1,240));
END update_ci_type;
PROCEDURE delete_ci_type (
p_api_version IN NUMBER := 1.0,
p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
p_commit IN VARCHAR2 := FND_API.g_false,
p_validate_only IN VARCHAR2 := FND_API.g_true,
p_max_msg_count IN NUMBER := FND_API.g_miss_num,
p_ci_type_id IN NUMBER,
p_record_version_number IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
p_obj_status_list_id IN NUMBER
)
IS
CURSOR c_ci_type_usage IS
SELECT *
FROM pa_ci_type_usage
WHERE ci_type_id = p_ci_type_id;
SELECT *
FROM pa_ci_impact_type_usage
WHERE ci_type_id = p_ci_type_id;
SELECT *
FROM pa_obj_status_lists
WHERE obj_status_list_id = p_obj_status_list_id;
pa_debug.set_err_stack ('PA_CI_TYPES_PVT.DELETE_CI_TYPE');
SAVEPOINT delete_ci_type;
SELECT 'X' INTO l_temp
FROM pa_ci_types_vl
WHERE ci_type_id = p_ci_type_id
AND record_version_number = p_record_version_number;
SELECT 'X' INTO l_temp
FROM pa_control_items
WHERE ci_type_id = p_ci_type_id
AND ROWNUM=1;
fnd_message.set_name('PA','PA_CI_TYPE_NO_DELETE_IN_USE');
if Pa_Fp_Control_Items_Utils.validate_fp_ci_type_delete( p_ci_type_id) <> 'Y' then
x_return_status := 'E';
pa_object_dist_lists_pvt.delete_assoc_dist_lists (
p_validate_only => p_validate_only,
p_object_type => 'PA_CI_TYPES',
p_object_id => p_ci_type_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
pa_ci_type_usage_pvt.delete_ci_type_usage (
p_validate_only => p_validate_only,
p_ci_type_usage_id => rec.ci_type_usage_id,
p_project_type_id => rec.project_type_id,
p_ci_type_id => p_ci_type_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
pa_ci_impact_type_usage_pvt.delete_ci_impact_type_usage (
p_validate_only => p_validate_only,
p_ci_impact_type_usage_id => rec.ci_impact_type_usage_id,
p_impact_type_code => rec.impact_type_code,
p_ci_type_class_code => NULL,
p_ci_type_id => p_ci_type_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
pa_ci_types_pkg.delete_row(
x_ci_type_id => p_ci_type_id);
pa_obj_status_lists_pkg.delete_row(
x_obj_status_list_id => p_obj_status_list_id);
ROLLBACK TO delete_ci_type;
ROLLBACK TO delete_ci_type;
ROLLBACK TO delete_ci_type;
ROLLBACK TO delete_ci_type;
p_procedure_name => 'DELETE_CI_TYPE',
p_error_text => SUBSTRB(SQLERRM,1,240));
END delete_ci_type;