The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_undeleted_action_code_tbl varchar_tbl;
SELECT pa.start_date,
ast.status_code,
ast.actual_start_date
FROM pa_project_assignments pa,
pa_action_sets ast
WHERE pa.assignment_id = ast.object_id
AND ast.action_set_id = p_action_set_id;
SELECT action_code
FROM pa_action_set_lines
WHERE action_set_id = p_action_set_id
AND (status_code = 'PENDING'
OR status_code = 'UPDATE_PENDING'
OR status_code = 'COMPLETE');
CURSOR get_undeleted_action_lines IS
SELECT action_code
FROM pa_action_set_lines
WHERE action_set_id = p_action_set_id
AND line_deleted_flag = 'N';
SELECT 'T'
FROM pa_action_set_lines
WHERE action_set_id = p_action_set_id
AND action_code = 'ADVERTISEMENT_ESC_TO_NEXT_LVL'
AND (status_code = 'PENDING'
OR status_code = 'UPDATE_PENDING'
OR status_code = 'COMPLETE')
AND rownum=1;
SELECT action_set_line_id
FROM pa_action_set_lines
WHERE action_set_id = p_action_set_id
AND rownum = 1
AND action_code = 'ADVERTISEMENT_PUB_TO_START_ORG'
AND (status_code = 'PENDING'
OR status_code = 'UPDATE_PENDING'
OR status_code = 'COMPLETE')
AND action_set_line_number < (
SELECT MIN(action_set_line_number)
FROM pa_action_set_lines
WHERE action_code = 'ADVERTISEMENT_ESC_TO_NEXT_LVL'
AND action_set_id = p_action_set_id
AND (status_code = 'PENDING'
OR status_code = 'UPDATE_PENDING'
OR status_code = 'COMPLETE'));
SELECT action_set_line_id
FROM pa_action_set_lines
WHERE action_set_id = p_action_set_id
AND rownum = 1
AND action_code = 'ADVERTISEMENT_PUB_TO_START_ORG'
AND action_set_line_number < (
SELECT MIN(action_set_line_number)
FROM pa_action_set_lines
WHERE action_code = 'ADVERTISEMENT_ESC_TO_NEXT_LVL'
AND action_set_id = p_action_set_id);
OPEN get_undeleted_action_lines;
FETCH get_undeleted_action_lines BULK COLLECT INTO l_undeleted_action_code_tbl;
CLOSE get_undeleted_action_lines;
IF l_undeleted_action_code_tbl.COUNT=0 THEN
PA_UTILS.Add_Message ( p_app_short_name => 'PA'
,p_msg_name => 'PA_NO_ACTION_LINE');
SELECT aslc.condition_code, aslc.condition_attribute1, aslc.condition_attribute2
BULK COLLECT INTO l_condition_code_tbl, l_condition_attribute1_tbl, l_condition_attribute2_tbl
FROM pa_action_set_lines asl,
pa_action_set_line_cond aslc
WHERE asl.action_set_id = p_action_set_id
AND asl.action_set_line_id = aslc.action_set_line_id
AND (asl.action_code = 'ADVERTISEMENT_ESC_TO_NEXT_LVL'
OR asl.action_code = 'ADVERTISEMENT_PUB_TO_START_ORG')
AND (asl.status_code = 'PENDING'
OR asl.status_code = 'UPDATE_PENDING'
OR asl.status_code = 'COMPLETE')
ORDER BY asl.action_set_line_number;
, x_action_line_audit_tbl OUT NOCOPY pa_action_set_utils.insert_audit_lines_tbl_type -- For 1159 mandate changes bug#2674619
, x_action_line_result_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
) IS
TYPE varchar_tbl IS TABLE OF VARCHAR2(30)
INDEX BY BINARY_INTEGER;
SELECT pa.assignment_id,
pa.project_id,
pa.start_date,
pa.record_version_number
FROM pa_project_assignments pa,
pa_action_sets ast
WHERE pa.assignment_id = ast.object_id
AND ast.action_set_id = p_action_set_line_rec.action_set_id;
g_action_line_audit_tbl.DELETE;
IF p_action_line_conditions_tbl(p_action_line_conditions_tbl.COUNT).condition_date <= SYSDATE OR p_action_set_line_rec.status_code = 'UPDATE_PENDING' THEN
-- CASE 1: Publish to All
IF p_action_set_line_rec.action_code = 'ADVERTISEMENT_PUB_TO_ALL' THEN
PA_ADVERTISEMENTS_PVT.Publish_To_all(
p_action_set_line_id => p_action_set_line_rec.action_set_line_id
, p_object_id => l_object_id
, p_action_code => p_action_set_line_rec.action_code
, p_action_status_code => p_action_set_line_rec.status_code
, x_return_status => l_return_status
);
ELSIF p_action_set_line_rec.action_code = 'ADVERTISEMENT_UPDATE_SP' THEN
PA_ADVERTISEMENTS_PVT.Update_Staffing_Priority(
p_action_set_line_id => p_action_set_line_rec.action_set_line_id
, p_object_id => l_object_id
, p_action_code => p_action_set_line_rec.action_code
, p_action_status_code => p_action_set_line_rec.status_code
, p_staffing_priority_code => p_action_set_line_rec.action_attribute1
, p_record_version_number => l_record_version_number
, x_return_status => l_return_status
);
p_action_set_line_rec.action_code = 'ADVERTISEMENT_UPDATE_SP' THEN
x_action_line_result_code := pa_action_set_utils.G_REVERSED_CUSTOM_AUDIT;
ELSE -- update pending
IF p_action_set_line_rec.action_code = 'ADVERTISEMENT_REMOVE_ADV' OR
p_action_set_line_rec.action_code = 'ADVERTISEMENT_UPDATE_SP' THEN
x_action_line_result_code := pa_action_set_utils.G_UPDATED_CUSTOM_AUDIT;
x_action_line_result_code := pa_action_set_utils.G_UPDATED_CUSTOM_AUDIT;
x_action_line_result_code := pa_action_set_utils.G_UPDATED_DEFAULT_AUDIT;
SELECT pa.project_id,
pa.start_date,
ast.status_code,
ast.actual_start_date,
ast.action_set_id,
ast.action_set_template_flag
FROM pa_project_assignments pa,
pa_action_sets ast
WHERE pa.assignment_id = p_object_id
AND ast.object_id = p_object_id
AND ast.object_type = p_object_type
AND ast.action_set_type_code = 'ADVERTISEMENT'
AND ast.status_code <> 'DELETED';
SELECT asl.action_set_line_id, 'UPDATE_PENDING', aslc.condition_date, aslc.action_set_line_condition_id
BULK COLLECT INTO l_action_set_line_id_tbl, l_action_status_code_tbl, l_condition_date_tbl, l_action_line_cond_id_tbl
FROM pa_action_set_lines asl,
pa_action_set_line_cond aslc
WHERE asl.action_set_id = l_action_set_id
AND asl.status_code = 'COMPLETE'
AND asl.action_set_line_id = aslc.action_set_line_id
AND aslc.condition_date > SYSDATE;
PA_ACTION_SETS_PVT.Bulk_Update_Line_Status(
p_action_set_line_id_tbl => l_action_set_line_id_tbl
,p_line_status_tbl => l_action_status_code_tbl
,x_return_status => l_return_status
);
PA_ACTION_SETS_PVT.Bulk_Update_Condition_Date(
p_action_line_condition_id_tbl => l_action_line_cond_id_tbl
,p_condition_date_tbl => l_condition_date_tbl
,x_return_status => l_return_status
);
l_action_set_line_id_tbl.DELETE;
l_action_status_code_tbl.DELETE;
l_condition_date_tbl.DELETE;
l_action_line_cond_id_tbl.DELETE;
SELECT asl.action_set_line_id, 'PENDING', aslc.condition_date, aslc.action_set_line_condition_id
BULK COLLECT INTO l_action_set_line_id_tbl, l_action_status_code_tbl, l_condition_date_tbl, l_action_line_cond_id_tbl
FROM pa_action_set_lines asl,
pa_action_set_line_cond aslc
WHERE asl.action_set_id = l_action_set_id
AND asl.status_code = 'REVERSED'
AND nvl(asl.line_deleted_flag, 'N') = 'N'
AND asl.action_set_line_id = aslc.action_set_line_id
AND aslc.condition_date > SYSDATE;
PA_ACTION_SETS_PVT.Bulk_Update_Line_Status(
p_action_set_line_id_tbl => l_action_set_line_id_tbl
,p_line_status_tbl => l_action_status_code_tbl
,x_return_status => l_return_status
);
PA_ACTION_SETS_PVT.Bulk_Update_Condition_Date(
p_action_line_condition_id_tbl => l_action_line_cond_id_tbl
,p_condition_date_tbl => l_condition_date_tbl
,x_return_status => l_return_status
);
SELECT proj.start_adv_action_set_flag
INTO l_action_set_start_flag
FROM pa_project_assignments asgn,
pa_projects_all proj
WHERE asgn.assignment_id = p_object_id
AND asgn.project_id = proj.project_id;