[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
-- Enter the procedure variables here. As shown below
l_error_msg_code varchar2(30);
select record_version_number
from pa_control_items
where ci_id = p_ci_id;
select sv.project_system_status_code
from pa_control_items ci , pa_ci_statuses_v sv where
ci.ci_id = p_ci_id and ci.ci_type_id = sv.ci_type_id
and sv.project_status_code = ci.status_code ;
PA_CI_ACTIONS_PKG.INSERT_ROW(
P_CI_ACTION_ID => l_ci_action_id,
P_CI_ID => P_CI_ID,
P_CI_ACTION_NUMBER => l_action_number,
P_STATUS_CODE => 'CI_ACTION_OPEN',
P_TYPE_CODE => P_TYPE_CODE,
P_ASSIGNED_TO => P_ASSIGNED_TO,
P_DATE_REQUIRED => P_DATE_REQUIRED,
P_SIGN_OFF_REQUIRED_FLAG => P_SIGN_OFF_REQUIRED_FLAG,
P_DATE_CLOSED => NULL,
P_SIGN_OFF_FLAG => 'N',
P_SOURCE_CI_ACTION_ID => P_SOURCE_CI_ACTION_ID,
P_LAST_UPDATED_BY => P_LAST_UPDATED_BY,
P_CREATED_BY => P_CREATED_BY,
P_CREATION_DATE => P_CREATION_DATE,
P_LAST_UPDATE_DATE => P_LAST_UPDATE_DATE,
P_LAST_UPDATE_LOGIN => P_LAST_UPDATE_LOGIN,
P_RECORD_VERSION_NUMBER => 1);
PA_CONTROL_ITEMS_PVT.UPDATE_NUMBER_OF_ACTIONS (
p_api_version => P_API_VERSION,
p_init_msg_list => P_INIT_MSG_LIST,
p_commit => P_COMMIT,
p_validate_only => P_VALIDATE_ONLY,
p_max_msg_count => P_MAX_MSG_COUNT,
p_ci_id =>P_CI_ID,
p_num_of_actions => 1,
p_record_version_number =>l_ci_record_version_number,
x_num_of_actions => l_num_of_actions,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
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,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
Cursor check_record_changed IS
select rowid
from pa_ci_actions
where ci_action_id = p_ci_action_id
and record_version_number = p_record_version_number
for update;
select ci_id, type_code, assigned_to, date_required,
sign_off_required_flag, source_ci_action_id, created_by, creation_date
from pa_ci_actions
where ci_action_id = p_ci_action_id;
select record_version_number
from pa_control_items
where ci_id = l_ci_id;
PA_CI_ACTIONS_PKG.UPDATE_ROW(
P_CI_ACTION_ID => P_CI_ACTION_ID,
P_CI_ID => l_ci_id,
P_STATUS_CODE => l_action_status_code,
P_TYPE_CODE => l_type_code,
P_ASSIGNED_TO => l_assigned_to,
P_DATE_REQUIRED => l_date_required,
P_SIGN_OFF_REQUIRED_FLAG => l_sign_off_required_flag,
P_DATE_CLOSED => sysdate,
P_SIGN_OFF_FLAG => P_SIGN_OFF_FLAG,
P_SOURCE_CI_ACTION_ID => l_source_ci_action_id,
P_LAST_UPDATED_BY => P_LAST_UPDATED_BY,
P_CREATED_BY => l_created_by,
P_CREATION_DATE => l_creation_date,
P_LAST_UPDATE_DATE => P_LAST_UPDATE_DATE,
P_LAST_UPDATE_LOGIN => p_last_update_login,
P_RECORD_VERSION_NUMBER => P_RECORD_VERSION_NUMBER);
PA_CONTROL_ITEMS_PVT.UPDATE_NUMBER_OF_ACTIONS (
p_api_version => P_API_VERSION,
p_init_msg_list => P_INIT_MSG_LIST,
p_commit => P_COMMIT,
p_validate_only => P_VALIDATE_ONLY,
p_max_msg_count => P_MAX_MSG_COUNT,
p_ci_id =>l_CI_ID,
p_num_of_actions => -1,
p_record_version_number =>l_ci_record_version_number,
x_num_of_actions => l_num_of_actions,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
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,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
Cursor check_record_changed IS
select rowid
from pa_ci_actions
where ci_action_id = p_ci_action_id
and record_version_number = p_record_version_number
for update;
select ci_id, type_code, assigned_to, date_required,
sign_off_required_flag, source_ci_action_id
from pa_ci_actions
where ci_action_id = p_ci_action_id;
select assigned_to
into l_assigned_to_party
from pa_ci_actions
where ci_action_id = p_ci_action_id;
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,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
Cursor check_record_changed IS
select rowid
from pa_ci_actions
where ci_action_id = p_ci_action_id
and record_version_number = p_record_version_number
for update;
select ci_id, type_code, assigned_to, date_required,
sign_off_required_flag, source_ci_action_id, created_by, creation_date, sign_off_flag
from pa_ci_actions
where ci_action_id = p_ci_action_id;
select record_version_number
from pa_control_items
where ci_id = l_ci_id;
l_party_id := PA_UTILS.get_party_id(P_LAST_UPDATED_BY);
PA_CI_ACTIONS_PKG.UPDATE_ROW(
P_CI_ACTION_ID => P_CI_ACTION_ID,
P_CI_ID => l_ci_id,
P_STATUS_CODE => 'CI_ACTION_CANCELED',
P_TYPE_CODE => l_type_code,
P_ASSIGNED_TO => l_assigned_to,
P_DATE_REQUIRED => l_date_required,
P_SIGN_OFF_REQUIRED_FLAG => l_sign_off_required_flag,
P_DATE_CLOSED => sysdate,
P_SIGN_OFF_FLAG => l_sign_off_flag,
P_SOURCE_CI_ACTION_ID => l_source_ci_action_id,
P_LAST_UPDATED_BY => P_LAST_UPDATED_BY,
P_CREATED_BY => l_created_by,
P_CREATION_DATE => l_creation_date,
P_LAST_UPDATE_DATE => P_LAST_UPDATE_DATE,
P_LAST_UPDATE_LOGIN => P_LAST_UPDATE_LOGIN,
P_RECORD_VERSION_NUMBER => P_RECORD_VERSION_NUMBER);
PA_CONTROL_ITEMS_PVT.UPDATE_NUMBER_OF_ACTIONS (
p_api_version => P_API_VERSION,
p_init_msg_list => P_INIT_MSG_LIST,
p_commit => P_COMMIT,
p_validate_only => P_VALIDATE_ONLY,
p_max_msg_count => P_MAX_MSG_COUNT,
p_ci_id =>l_ci_id,
p_num_of_actions => -1,
p_record_version_number =>l_ci_record_version_number,
x_num_of_actions => l_num_of_actions,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
PROCEDURE UPDATE_CI_COMMENT(
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_comment_id IN NUMBER,
p_comment_text IN VARCHAR2,
p_record_version_number IN NUMBER,
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,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
l_error_msg_code varchar2(30);
select ci_id, type_code, created_by, creation_date, ci_action_id from pa_ci_comments
where ci_comment_id = p_ci_comment_id;
select rowid
from pa_ci_comments
where ci_comment_id = p_ci_comment_id
and record_version_number = p_record_version_number
for update;
SAVEPOINT UPDATE_CI_COMMENT;
PA_CI_COMMENTS_PKG.UPDATE_ROW(
P_CI_COMMENT_ID => P_CI_COMMENT_ID,
P_CI_ID => l_ci_id,
P_TYPE_CODE => l_type_code,
P_COMMENT_TEXT => P_COMMENT_TEXT,
P_LAST_UPDATED_BY => P_LAST_UPDATED_BY,
P_CREATED_BY => l_created_by,
P_CREATION_DATE => l_creation_date,
P_LAST_UPDATE_DATE => P_LAST_UPDATE_DATE,
P_LAST_UPDATE_LOGIN => P_LAST_UPDATE_LOGIN,
P_RECORD_VERSION_NUMBER => p_record_version_number,
P_CI_ACTION_ID => l_ci_action_id);
ROLLBACK TO UPDATE_CI_COMMENT;
ROLLBACK TO UPDATE_CI_COMMENT;
p_procedure_name => 'UPDATE_CI_COMMENT',
p_error_text => SUBSTRB(SQLERRM,1,240));
END UPDATE_CI_COMMENT;
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,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
l_error_msg_code varchar2(30);
PA_CI_COMMENTS_PKG.INSERT_ROW(
P_CI_COMMENT_ID => P_CI_COMMENT_ID,
P_CI_ID => P_CI_ID,
P_TYPE_CODE => P_TYPE_CODE,
P_COMMENT_TEXT => P_COMMENT_TEXT,
P_LAST_UPDATED_BY => P_LAST_UPDATED_BY,
P_CREATED_BY => P_CREATED_BY,
P_CREATION_DATE => P_CREATION_DATE,
P_LAST_UPDATE_DATE => P_LAST_UPDATE_DATE,
P_LAST_UPDATE_LOGIN => P_LAST_UPDATE_LOGIN,
P_CI_ACTION_ID => P_CI_ACTION_ID);
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,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
l_error_msg_code varchar2(30);
SELECT ci_action_id, record_version_number
FROM PA_CI_ACTIONS pca
WHERE pca.ci_id = p_ci_id
AND status_code = 'CI_ACTION_OPEN';
p_procedure_name => 'DELETE_ALL_ACTIONS',
p_error_text => SUBSTRB(SQLERRM,1,240));
PROCEDURE DELETE_ALL_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,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
l_error_msg_code varchar2(30);
CURSOR delete_action IS
SELECT ci_action_id
FROM PA_CI_ACTIONS
WHERE ci_id = p_ci_id;
CURSOR delete_comment IS
SELECT ci_comment_id
FROM PA_CI_COMMENTS
WHERE ci_action_id = l_ci_action_id;
SAVEPOINT DELETE_ALL_ACTIONS;
OPEN delete_action;
FETCH delete_action INTO l_ci_action_id;
EXIT WHEN delete_action%NOTFOUND;
PA_CI_ACTIONS_PKG.DELETE_ROW(
P_CI_ACTION_ID => l_ci_action_id);
OPEN delete_comment;
FETCH delete_comment INTO l_ci_comment_id;
EXIT WHEN delete_comment%NOTFOUND;
PA_CI_COMMENTS_PKG.DELETE_ROW(
P_CI_COMMENT_ID => l_ci_comment_id);
CLOSE delete_comment;
CLOSE delete_action;
p_procedure_name => 'DELETE_ALL_ACTIONS',
p_error_text => SUBSTRB(SQLERRM,1,240));
END DELETE_ALL_ACTIONS;