[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Select project_system_status_code
From PA_PROJECT_STATUSES
Where project_status_code = p_status_code;
Select ci_type_class_code, auto_number_flag,
start_date_active,end_date_active
From PA_CI_TYPES_B
Where ci_type_id = p_ci_type_id;
select ROWID
into l_rowid
from fnd_currencies_tl
where currency_code = p_price_currency_code
AND language = USERENV('LANG');
PA_CONTROL_ITEMS_PKG.INSERT_ROW (
p_ci_type_id
,p_summary
,p_status_code
,p_owner_id
,p_highlighted_flag
,NVL(p_progress_status_code, 'PROGRESS_STAT_ON_TRACK')
,NVL(p_progress_as_of_date,sysdate)
,p_classification_code
,p_reason_code
,p_project_id
-- ,sysdate
,p_last_modified_by_id
,p_object_type
,p_object_id
,l_ci_number_char
,p_date_required
,p_date_closed
,p_closed_by_id
,p_description
,p_status_overview
,p_resolution
,p_resolution_code
,p_priority_code
,p_effort_level_code
,nvl(p_open_action_num,0)
,p_price
,p_price_currency_code
,p_source_type_code
,p_source_comment
,p_source_number
,p_source_date_received
,p_source_organization
,p_source_person
,p_attribute_category
,p_attribute1
,p_attribute2
,p_attribute3
,p_attribute4
,p_attribute5
,p_attribute6
,p_attribute7
,p_attribute8
,p_attribute9
,p_attribute10
,p_attribute11
,p_attribute12
,p_attribute13
,p_attribute14
,p_attribute15
,px_ci_id
,x_return_status
,x_msg_count
,x_msg_data
);
procedure UPDATE_CONTROL_ITEM (
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_id IN NUMBER
,p_ci_type_id IN NUMBER
,p_summary IN VARCHAR2
,p_status_code IN VARCHAR2 := NULL
,p_owner_id IN NUMBER
,p_highlighted_flag IN VARCHAR2 := 'N'
,p_progress_status_code IN VARCHAR2
,p_progress_as_of_date IN DATE := NULL
,p_classification_code IN NUMBER
,p_reason_code IN NUMBER
,p_record_version_number IN NUMBER
,p_project_id IN NUMBER
,p_last_modified_by_id IN NUMBER
,p_object_type IN VARCHAR2 := NULL
,p_object_id IN NUMBER := NULL
,p_ci_number IN VARCHAR2 := NULL
,p_date_required IN DATE := NULL
,p_date_closed IN DATE := NULL
,p_closed_by_id IN NUMBER := NULL
,p_description IN VARCHAR2 := NULL
,p_status_overview IN VARCHAR2 := NULL
,p_resolution IN VARCHAR2 := NULL
,p_resolution_code IN NUMBER := NULL
,p_priority_code IN VARCHAR2 := NULL
,p_effort_level_code IN VARCHAR2 := NULL
,p_open_action_num IN NUMBER := NULL
,p_price IN NUMBER := NULL
,p_price_currency_code IN VARCHAR2 := NULL
,p_source_type_code IN VARCHAR2 := NULL
,p_source_comment IN VARCHAR2 := NULL
,p_source_number IN VARCHAR2 := NULL
,p_source_date_received IN DATE := NULL
,p_source_organization IN VARCHAR2 := NULL
,p_source_person IN VARCHAR2 := NULL
,p_attribute_category IN VARCHAR2 := NULL
,p_attribute1 IN VARCHAR2 := NULL
,p_attribute2 IN VARCHAR2 := NULL
,p_attribute3 IN VARCHAR2 := NULL
,p_attribute4 IN VARCHAR2 := NULL
,p_attribute5 IN VARCHAR2 := NULL
,p_attribute6 IN VARCHAR2 := NULL
,p_attribute7 IN VARCHAR2 := NULL
,p_attribute8 IN VARCHAR2 := NULL
,p_attribute9 IN VARCHAR2 := NULL
,p_attribute10 IN VARCHAR2 := NULL
,p_attribute11 IN VARCHAR2 := NULL
,p_attribute12 IN VARCHAR2 := NULL
,p_attribute13 IN VARCHAR2 := NULL
,p_attribute14 IN VARCHAR2 := NULL
,p_attribute15 IN VARCHAR2 := NULL
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) is
CURSOR curr_row is
SELECT *
FROM pa_control_items
WHERE ci_id = p_ci_id;
cursor C is select ROWID from PA_CONTROL_ITEMS
where project_id = p_project_id
and ci_number = p_ci_number
and ci_id <> p_ci_id
and ci_type_id = p_ci_type_id;
Select type.auto_number_flag
From PA_CI_TYPES_B type
,pa_control_items ci
Where ci.ci_id = p_ci_id
AND ci.ci_type_id = type.ci_type_id;
PA_DEBUG.init_err_stack('PA_CONTROL_ITEMS_PVT.Update_Control_Item');
SAVEPOINT update_control_item;
select ROWID
into l_ROWID
from fnd_currencies_tl
where currency_code = p_price_currency_code
AND language = USERENV('LANG');
PA_CONTROL_ITEMS_PKG.UPDATE_ROW(
p_ci_id
,p_ci_type_id
,p_summary
,l_new_status_code
,p_owner_id
,p_highlighted_flag
,p_progress_status_code
,l_as_of_date --p_progress_as_of_date
,p_classification_code
,p_reason_code
,p_record_version_number
,p_project_id
,p_last_modified_by_id
,p_object_type
,p_object_id
,l_ci_number --p_ci_number
,p_date_required
,p_date_closed
,p_closed_by_id
,p_description
,p_status_overview
,p_resolution
,p_resolution_code
,p_priority_code
,p_effort_level_code
,p_open_action_num
,p_price
,p_price_currency_code
,p_source_type_code
,p_source_comment
,p_source_number
,p_source_date_received
,p_source_organization
,p_source_person
,p_attribute_category
,p_attribute1
,p_attribute2
,p_attribute3
,p_attribute4
,p_attribute5
,p_attribute6
,p_attribute7
,p_attribute8
,p_attribute9
,p_attribute10
,p_attribute11
,p_attribute12
,p_attribute13
,p_attribute14
,p_attribute15
,x_return_status
,x_msg_count
,x_msg_data
);
ROLLBACK TO update_control_item;
FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_CONTROL_ITEMS_PVT.update_control_item'
,p_procedure_name => PA_DEBUG.G_Err_Stack );
end UPDATE_CONTROL_ITEM;
procedure DELETE_CONTROL_ITEM (
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_id IN NUMBER
,p_record_version_number IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) is
l_status_code pa_project_statuses.project_system_status_code%type;
select pps.project_system_status_code --status_code
from pa_control_items ci
,pa_project_statuses pps
where ci.ci_id = p_ci_id
and ci.status_code = pps.project_status_code;
PA_DEBUG.init_err_stack('PA_CONTROL_ITEMS_PVT.Delete_Control_Item');
SAVEPOINT delete_control_item;
pa_ci_actions_pvt.delete_all_actions(p_validate_only => 'F',
p_init_msg_list => 'F',
p_ci_id => p_ci_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
pa_ci_impacts_util.delete_All_impacts(p_validate_only => 'F',
p_init_msg_list => 'F',
p_ci_id => p_ci_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
delete_all_related_items (p_validate_only => 'F',
p_init_msg_list => 'F',
p_ci_id => p_ci_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
delete_all_included_crs (p_validate_only => 'F',
p_init_msg_list => 'F',
p_ci_id => p_ci_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
pa_ci_doc_attach_pkg.delete_all_attachments (p_validate_only => 'F',
p_init_msg_list => 'F',
p_ci_id => p_ci_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
PA_CONTROL_ITEMS_PKG.DELETE_ROW(
p_ci_id
,p_record_version_number
,x_return_status
,x_msg_count
,x_msg_data
);
ROLLBACK TO delete_control_item;
FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_CONTROL_ITEMS_PVT.delete_control_item'
,p_procedure_name => PA_DEBUG.G_Err_Stack );
end DELETE_CONTROL_ITEM;
procedure DELETE_ALL_CONTROL_ITEMS(
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_project_id IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) is
l_msg_index_out NUMBER;
PA_DEBUG.init_err_stack('PA_CONTROL_ITEMS_PVT.Delete_ALL_Control_Items');
SAVEPOINT delete_all_control_items;
FOR ci_rec IN( SELECT ci_id, record_version_number
FROM pa_control_items
WHERE project_id = p_project_id ) LOOP
DELETE_CONTROL_ITEM(
p_api_version
,'F'
,'F'
,'F'
,p_max_msg_count
,ci_rec.ci_id
,ci_rec.record_version_number
,x_return_status
,x_msg_count
,x_msg_data );
ROLLBACK TO delete_all_control_items;
FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_CONTROL_ITEMS_PVT.delete_all_control_items'
,p_procedure_name => PA_DEBUG.G_Err_Stack );
end DELETE_ALL_CONTROL_ITEMS;
SELECT * FROM pa_control_items
WHERE ci_id = p_ci_id_from;
SELECT 'Y'
FROM pa_ci_types_b pctb, pa_class_codes pcc
WHERE pctb.ci_type_id = p_ci_type_id
AND pctb.classification_category = pcc.class_category
AND pcc.class_code = p_class_code;
SELECT class_code_id
FROM pa_class_codes pcc,pa_ci_types_b pctb
WHERE pctb.ci_type_id = p_ci_type_id
AND pctb.classification_category = pcc.class_category
AND pcc.class_code in (select pcc1.class_code
from pa_class_codes pcc1
where pcc1.class_code_id = p_class_code);
SELECT class_code_id
FROM pa_class_codes pcc,pa_ci_types_b pctb
WHERE pctb.ci_type_id = p_ci_type_id
AND pctb.reason_category = pcc.class_category
AND pcc.class_code in (select pcc1.class_code
from pa_class_codes pcc1
where pcc1.class_code_id = p_reason);
SELECT 'Y'
FROM pa_ci_types_b pctb, pa_class_codes pcc
WHERE pctb.ci_type_id = p_ci_type_id
AND pctb.reason_category = pcc.class_category
AND pcc.class_code = p_reason;
SELECT pci.ci_type_id, pctb.ci_type_class_code,
pci.project_id, pps.project_system_status_code
FROM pa_control_items pci, pa_ci_types_b pctb, pa_project_statuses pps
WHERE pci.ci_id = l_ci_id
and pci.ci_type_id = pctb.ci_type_id
and pci.status_code = pps.project_status_code(+);
PA_OBJECT_RELATIONSHIPS_PKG.INSERT_ROW(
p_user_id => fnd_global.user_id,
p_object_type_from => 'PA_CONTROL_ITEMS',
p_object_id_from1 => to_char(p_from_ci_id),
p_object_id_from2 => NULL,
p_object_id_from3 => NULL,
p_object_id_from4 => NULL,
p_object_id_from5 => NULL,
p_object_type_to => 'PA_CONTROL_ITEMS',
p_object_id_to1 => to_char(p_to_ci_id),
p_object_id_to2 => NULL,
p_object_id_to3 => NULL,
p_object_id_to4 => NULL,
p_object_id_to5 => NULL,
p_relationship_type => l_relationship_type,
p_relationship_subtype => NULL,
p_lag_day => NULL,
p_imported_lag => NULL,
p_priority => NULL,
p_pm_product_code => NULL,
x_object_relationship_id => x_relationship_id,
x_return_status => x_return_status);
SELECT record_version_number
INTO l_record_version_number
FROM PA_CONTROL_ITEMS
WHERE ci_id = p_to_ci_id;
procedure UPDATE_NUMBER_OF_ACTIONS (
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_id IN NUMBER
,p_num_of_actions IN NUMBER
,p_record_version_number IN NUMBER
,x_num_of_actions OUT NOCOPY NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,p_last_updated_by in NUMBER default fnd_global.user_id --Added the parameter for bug# 3877985
,p_last_update_date in DATE default sysdate --Added the parameter for bug# 3877985
,p_last_update_login in NUMBER default fnd_global.user_id --Added the parameter for bug# 3877985
)IS
l_nof_actions NUMBER(15) := 0;
SELECT * FROM pa_control_items
WHERE ci_id = p_ci_id;
PA_DEBUG.init_err_stack('PA_CONTROL_ITEMS_PVT.UPDATE_NUMBER_OF_ACTIONS');
SAVEPOINT UPDATE_NUMBER_OF_ACTIONS;
PA_CONTROL_ITEMS_PKG.UPDATE_ROW(
p_ci_id
,cp.ci_type_id
,cp.summary
,cp.status_code
,cp.owner_id
,cp.highlighted_flag
,cp.progress_status_code
,cp.progress_as_of_date
,cp.classification_code_id
,cp.reason_code_id
,p_record_version_number
,cp.project_id
,cp.last_modified_by_id
,cp.object_type
,cp.object_id
,cp.ci_number
,cp.date_required
,cp.date_closed
,cp.closed_by_id
,cp.description
,cp.status_overview
,cp.resolution
,cp.resolution_code_id
,cp.priority_code
,cp.effort_level_code
,l_nof_actions --open_action_num
,cp.price
,cp.price_currency_code
,cp.source_type_code
,cp.source_comment
,cp.source_number
,cp.source_date_received
,cp.source_organization
,cp.source_person
,cp.attribute_category
,cp.attribute1
,cp.attribute2
,cp.attribute3
,cp.attribute4
,cp.attribute5
,cp.attribute6
,cp.attribute7
,cp.attribute8
,cp.attribute9
,cp.attribute10
,cp.attribute11
,cp.attribute12
,cp.attribute13
,cp.attribute14
,cp.attribute15
,x_return_status
,x_msg_count
,x_msg_data
,p_last_updated_by --Added for bug# 3877985
,p_last_update_date --Added for bug# 3877985
,p_last_update_login --Added for bug# 3877985
);
ROLLBACK TO UPDATE_NUMBER_OF_ACTIONS;
FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_CONTROL_ITEMS_PVT.UPDATE_NUMBER_OF_ACTIONS'
,p_procedure_name => PA_DEBUG.G_Err_Stack );
end UPDATE_NUMBER_OF_ACTIONS;
procedure UPDATE_CONTROL_ITEM_STATUS (
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_id IN NUMBER
,p_status_code IN VARCHAR2
,p_record_version_number IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
API_ERROR EXCEPTION;
SELECT * FROM pa_control_items
WHERE ci_id = p_ci_id;
Select type.auto_number_flag, type.ci_type_id,type.ci_type_class_code
From PA_CI_TYPES_B type
,pa_control_items ci
Where ci.ci_id = p_ci_id
AND ci.ci_type_id = type.ci_type_id;
PA_DEBUG.init_err_stack('PA_CONTROL_ITEMS_PVT.UPDATE_CONTROL_ITEM_STATUS');
SAVEPOINT UPDATE_CONTROL_ITEM_STATUS;
PA_CONTROL_ITEMS_PKG.UPDATE_ROW(
p_ci_id
,cp.ci_type_id
,cp.summary
,p_status_code
,cp.owner_id
,cp.highlighted_flag
,cp.progress_status_code
,cp.progress_as_of_date
,cp.classification_code_id
,cp.reason_code_id
,p_record_version_number
,cp.project_id
,PA_CONTROL_ITEMS_UTILS.GetPartyId(fnd_global.user_id ) --cp.last_modified_by_id
,cp.object_type
,cp.object_id
,l_ci_number_char --cp.ci_number
,cp.date_required
,l_closed_date --cp.date_closed
,l_closed_by --cp.closed_by_id
,cp.description
,cp.status_overview
,cp.resolution
,cp.resolution_code_id
,cp.priority_code
,cp.effort_level_code
,cp.open_action_num
,cp.price
,cp.price_currency_code
,cp.source_type_code
,cp.source_comment
,cp.source_number
,cp.source_date_received
,cp.source_organization
,cp.source_person
,cp.attribute_category
,cp.attribute1
,cp.attribute2
,cp.attribute3
,cp.attribute4
,cp.attribute5
,cp.attribute6
,cp.attribute7
,cp.attribute8
,cp.attribute9
,cp.attribute10
,cp.attribute11
,cp.attribute12
,cp.attribute13
,cp.attribute14
,cp.attribute15
,x_return_status
,x_msg_count
,x_msg_data
);
ROLLBACK TO UPDATE_CONTROL_ITEM_STATUS;
FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_CONTROL_ITEMS_PVT.UPDATE_CONTROL_ITEM_STATUS'
,p_procedure_name => PA_DEBUG.G_Err_Stack );
end UPDATE_CONTROL_ITEM_STATUS;
cursor C is select ROWID from PA_SYSTEM_NUMBERS
where object1_pk1_value = p_project_id
and nvl(object2_pk1_value,0) = nvl(p_ci_type_id,0)
and object1_type = p_object_type
and object2_type = p_ci_type_code;
cursor C is select ROWID from PA_CONTROL_ITEMS
where project_id = p_project_id
and ci_number = p_ci_number
and ci_type_id = p_ci_type_id;
SELECT meaning
INTO l_meaning
FROM pa_lookups
WHERE lookup_type = p_lookup_type
AND lookup_code = p_lookup_code;
SELECT obj.object_id_to1 included_ci_id
,ci.record_version_number record_version_number
FROM pa_object_relationships obj, pa_control_items ci
WHERE obj.object_type_from = 'PA_CONTROL_ITEMS'
AND obj.object_type_to = 'PA_CONTROL_ITEMS'
AND obj.relationship_type = 'CI_INCLUDED_ITEM'
AND obj.object_id_from1 = p_ci_id
AND obj.object_id_to1 = ci.ci_id;
PA_OBJECT_RELATIONSHIPS_PKG.INSERT_ROW(
p_user_id => fnd_global.user_id,
p_object_type_from => 'PA_CONTROL_ITEMS',
p_object_id_from1 => to_char(p_ci_id),
p_object_id_from2 => NULL,
p_object_id_from3 => NULL,
p_object_id_from4 => NULL,
p_object_id_from5 => NULL,
p_object_type_to => 'PA_CONTROL_ITEMS',
p_object_id_to1 => to_char(p_related_ci_id),
p_object_id_to2 => NULL,
p_object_id_to3 => NULL,
p_object_id_to4 => NULL,
p_object_id_to5 => NULL,
p_relationship_type => 'CI_REFERENCED_ITEM',
p_relationship_subtype => NULL,
p_lag_day => NULL,
p_imported_lag => NULL,
p_priority => NULL,
p_pm_product_code => NULL,
x_object_relationship_id => l_object_relationship_id,
x_return_status => x_return_status);
PROCEDURE delete_related_item (
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_id IN NUMBER,
p_related_ci_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_object_relationship_id NUMBER;
pa_debug.set_err_stack ('PA_CONTROL_ITEMS_PVT.DELETE_RELATED_ITEM');
SAVEPOINT delete_related_item;
SELECT object_relationship_id, record_version_number
INTO l_object_relationship_id, l_record_version_number
FROM pa_object_relationships
WHERE object_type_from = 'PA_CONTROL_ITEMS'
AND relationship_type = 'CI_REFERENCED_ITEM'
AND object_type_to = 'PA_CONTROL_ITEMS'
AND ( ( object_id_to1 = p_related_ci_id
AND object_id_from1 = p_ci_id)
OR ( object_id_to1 = p_ci_id
AND object_id_from1 = p_related_ci_id));
pa_object_relationships_pkg.delete_row(
p_object_relationship_id => l_object_relationship_id,
p_object_type_from => 'PA_CONTROL_ITEMS',
p_object_id_from1 => to_char(p_ci_id),
p_object_id_from2 => NULL,
p_object_id_from3 => NULL,
p_object_id_from4 => NULL,
p_object_id_from5 => NULL,
p_object_type_to => 'PA_CONTROL_ITEMS',
p_object_id_to1 => to_char(p_related_ci_id),
p_object_id_to2 => NULL,
p_object_id_to3 => NULL,
p_object_id_to4 => NULL,
p_object_id_to5 => NULL,
p_pm_product_code => NULL,
p_record_version_number => l_record_version_number,
x_return_status => x_return_status);
ROLLBACK TO delete_related_item;
ROLLBACK TO delete_related_item;
p_procedure_name => 'DELETE_RELATED_ITEM',
p_error_text => SUBSTRB(SQLERRM,1,240));
END delete_related_item;
PROCEDURE delete_all_related_items (
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_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
CURSOR items_c IS
SELECT object_id_to1 related_ci_id
FROM pa_object_relationships
WHERE object_type_from = 'PA_CONTROL_ITEMS'
AND object_type_to = 'PA_CONTROL_ITEMS'
AND relationship_type = 'CI_REFERENCED_ITEM'
AND object_id_from1 = p_ci_id
UNION ALL
SELECT object_id_from1 related_ci_id
FROM pa_object_relationships
WHERE object_type_from = 'PA_CONTROL_ITEMS'
AND object_type_to = 'PA_CONTROL_ITEMS'
AND relationship_type = 'CI_REFERENCED_ITEM'
AND object_id_to1 = p_ci_id;
pa_debug.set_err_stack ('PA_CONTROL_ITEMS_PVT.DELETE_ALL_RELATED_ITEMS');
SAVEPOINT delete_all_related_items;
delete_related_item(
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validate_only => FND_API.G_FALSE,
p_max_msg_count => p_max_msg_count,
p_ci_id => p_ci_id,
p_related_ci_id => cur.related_ci_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
ROLLBACK TO delete_all_related_items;
ROLLBACK TO delete_all_related_items;
p_procedure_name => 'DELETE_ALL_RELATED_ITEMS',
p_error_text => SUBSTRB(SQLERRM,1,240));
END delete_all_related_items;
PROCEDURE delete_all_included_crs (
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_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
CURSOR items_c IS
SELECT object_relationship_id, object_id_to1, record_version_number
FROM pa_object_relationships
WHERE object_type_from = 'PA_CONTROL_ITEMS'
AND object_type_to = 'PA_CONTROL_ITEMS'
AND relationship_type = 'CI_INCLUDED_ITEM'
AND object_id_from1 = p_ci_id;
pa_debug.set_err_stack ('PA_CONTROL_ITEMS_PVT.DELETE_ALL_INCLUDED_CRS');
SAVEPOINT delete_all_included_crs;
pa_object_relationships_pkg.delete_row(
p_object_relationship_id => cur.object_relationship_id,
p_object_type_from => 'PA_CONTROL_ITEMS',
p_object_id_from1 => to_char(p_ci_id),
p_object_id_from2 => NULL,
p_object_id_from3 => NULL,
p_object_id_from4 => NULL,
p_object_id_from5 => NULL,
p_object_type_to => 'PA_CONTROL_ITEMS',
p_object_id_to1 => to_char(cur.object_id_to1),
p_object_id_to2 => NULL,
p_object_id_to3 => NULL,
p_object_id_to4 => NULL,
p_object_id_to5 => NULL,
p_pm_product_code => NULL,
p_record_version_number => cur.record_version_number,
x_return_status => x_return_status);
ROLLBACK TO delete_all_included_crs;
ROLLBACK TO delete_all_included_crs;
p_procedure_name => 'DELETE_ALL_INCLUDED_CRS',
p_error_text => SUBSTRB(SQLERRM,1,240));
END delete_all_included_crs;
SELECT object_id_to1 related_ci_id
FROM pa_object_relationships
WHERE object_type_from = 'PA_CONTROL_ITEMS'
AND object_type_to = 'PA_CONTROL_ITEMS'
AND relationship_type = 'CI_REFERENCED_ITEM'
AND object_id_from1 = p_from_ci_id
UNION ALL
SELECT object_id_from1 related_ci_id
FROM pa_object_relationships
WHERE object_type_from = 'PA_CONTROL_ITEMS'
AND object_type_to = 'PA_CONTROL_ITEMS'
AND relationship_type = 'CI_REFERENCED_ITEM'
AND object_id_to1 = p_from_ci_id;