[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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;
DELETE FROM pa_adv_action_lines_order_temp;
SELECT asl.action_set_line_id,
asl.action_code,
aslc.condition_code,
aslc.condition_attribute1,
aslc.condition_attribute2,
aslc.action_set_line_condition_id
BULK COLLECT INTO l_action_line_id_tbl,
l_action_code_tbl,
l_condition_code_tbl,
l_condition_attribute1_tbl,
l_condition_attribute2_tbl,
l_action_line_cond_id_tbl
FROM pa_action_set_lines asl,
pa_action_set_line_cond aslc,
pa_action_sets asets,
pa_action_sets asets2
WHERE asets2.action_set_id = p_action_set_id
AND asets.object_id = asets2.object_id
AND asets.object_type = 'OPEN_ASSIGNMENT'
AND asets.action_set_type_code = 'ADVERTISEMENT'
AND asl.action_set_id = asets.action_set_id
AND asl.action_set_line_id = aslc.action_set_line_id;
SELECT asl.action_set_line_id,
asl.action_code,
aslc.condition_code,
aslc.condition_attribute1,
aslc.condition_attribute2,
aslc.action_set_line_condition_id
BULK COLLECT INTO l_action_line_id_tbl,
l_action_code_tbl,
l_condition_code_tbl,
l_condition_attribute1_tbl,
l_condition_attribute2_tbl,
l_action_line_cond_id_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;
INSERT INTO pa_adv_action_lines_order_temp (
action_set_line_id
, action_code
, condition_code
, condition_attribute1
, condition_attribute2
, condition_date
, action_set_line_condition_id
)
VALUES (
l_action_line_id_tbl(i)
, l_action_code_tbl(i)
, l_condition_code_tbl(i)
, l_condition_attribute1_tbl(i)
, l_condition_attribute2_tbl(i)
, DECODE(l_condition_code_tbl(i),
'ADVERTISEMENT_DAYS_OPEN', TRUNC(l_adv_action_set_start_date+l_condition_attribute1_tbl(i)),
'ADVERTISEMENT_DAYS_REMAINING', TRUNC(l_start_date-l_condition_attribute2_tbl(i)),
'ADVERTISEMENT_DAYS_OPN_REMAIN', TRUNC(LEAST(l_adv_action_set_start_date+l_condition_attribute1_tbl(i), l_start_date-l_condition_attribute2_tbl(i))))
, l_action_line_cond_id_tbl(i)
);
UPDATE pa_adv_action_lines_order_temp
SET condition_date = condition_date+0.1
WHERE action_code = 'ADVERTISEMENT_ESC_TO_NEXT_LVL';
UPDATE pa_adv_action_lines_order_temp
SET condition_date = condition_date+0.2
WHERE action_code = 'ADVERTISEMENT_REMOVE_ADV';
SELECT action_set_line_id, TRUNC(condition_date), action_set_line_condition_id
BULK COLLECT INTO l_action_line_id_tbl2, l_condition_date_tbl2, l_action_line_cond_id_tbl2
FROM pa_adv_action_lines_order_temp
ORDER BY condition_date;
INSERT INTO pa_adv_action_lines_order_temp (
action_set_line_id
, action_code
, condition_code
, condition_attribute1
, condition_attribute2
, condition_date
, action_set_line_condition_id
)
VALUES (
l_action_line_id_tbl(i)
, l_action_code_tbl(i)
, l_condition_code_tbl(i)
, l_condition_attribute1_tbl(i)
, l_condition_attribute2_tbl(i)
, null
, l_action_line_cond_id_tbl(i)
);
SELECT action_set_line_id, condition_date, action_set_line_condition_id
BULK COLLECT INTO l_action_line_id_tbl2, l_condition_date_tbl2, l_action_line_cond_id_tbl2
FROM pa_adv_action_lines_order_temp
ORDER BY to_number(condition_attribute1), to_number(condition_attribute2) desc;
SELECT rownum
BULK COLLECT INTO l_action_line_number_tbl2
FROM pa_adv_action_lines_order_temp;
PA_ACTION_SETS_PVT.Bulk_Update_Condition_Date(
p_action_line_condition_id_tbl => l_action_line_cond_id_tbl2
,p_condition_date_tbl => l_condition_date_tbl2
,x_return_status => l_return_status
);
PA_ACTION_SETS_PVT.Bulk_Update_Line_Number(
p_action_set_line_id_tbl => l_action_line_id_tbl2
,p_line_number_tbl => l_action_line_number_tbl2
,x_return_status => x_return_status
);
, p_insert_audit_flag IN VARCHAR2 := 'T'
, x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
) IS
l_return_status VARCHAR2(1);
l_action_line_audit_rec pa_action_set_utils.insert_audit_lines_rec_type;
IF p_action_status_code = 'PENDING' AND p_insert_audit_flag = 'T' THEN
-- insert the single audit line into the global audit record
l_action_line_audit_rec.reason_code := 'CONDITION_MET';
, p_insert_audit_flag IN VARCHAR2 := 'T'
, x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
--, x_msg_count OUT NUMBER
--, x_msg_data OUT VARCHAR2
) IS
TYPE number_tbl IS TABLE OF NUMBER
INDEX BY BINARY_INTEGER;
l_action_line_audit_rec pa_action_set_utils.insert_audit_lines_rec_type;
SELECT child_organization_id, pa_resource_utils.get_organization_name(child_organization_id) -- Added for Bug 4866284
FROM pa_org_hierarchy_denorm
WHERE org_hierarchy_version_id = p_org_hierarchy_version_id
AND parent_organization_id = p_starting_organization_id
AND pa_org_use_type = 'EXPENDITURES';
ELSIF p_insert_audit_flag = 'T' THEN
FOR i IN l_organization_id_tbl.FIRST..l_organization_id_tbl.LAST LOOP
-- insert into into the global audit record
l_action_line_audit_rec.reason_code := 'CONDITION_MET';
IF p_insert_audit_flag = 'T' THEN
FND_MSG_PUB.get (
p_encoded => FND_API.G_TRUE
,p_msg_index => FND_MSG_PUB.Count_Msg
,p_data => l_encoded_message_text
,p_msg_index_out => l_msg_index_out);
, p_insert_audit_flag IN VARCHAR2 := 'T'
, x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
) IS
l_org_hierarchy_version_id per_org_structure_versions.org_structure_version_id%TYPE;
l_action_line_audit_rec pa_action_set_utils.insert_audit_lines_rec_type;
SELECT organization_id_parent
FROM per_org_structure_elements
WHERE org_structure_version_id = c_org_hierarchy_version_id
AND organization_id_child = c_start_org_id;
SELECT ohd.child_organization_id, pa_resource_utils.get_organization_name(ohd.child_organization_id) -- Added for Bug 4866284
FROM pa_org_hierarchy_denorm ohd
WHERE ohd.org_hierarchy_version_id = c_org_hierarchy_version_id
AND ohd.parent_organization_id = c_start_org_id
AND ohd.pa_org_use_type = 'EXPENDITURES'
MINUS
-- SELECT ohd.child_organization_id, pa_expenditures_utils.GetOrgTlName(ohd.child_organization_id) -- Commented for Bug 4866284
SELECT ohd.child_organization_id, pa_resource_utils.get_organization_name(ohd.child_organization_id) -- Added for Bug 4866284
FROM pa_org_hierarchy_denorm ohd,
pa_action_set_line_aud asla
WHERE ohd.org_hierarchy_version_id = c_org_hierarchy_version_id
AND ohd.parent_organization_id = c_start_org_id
AND ohd.pa_org_use_type = 'EXPENDITURES'
AND ohd.child_organization_id = to_number(asla.audit_attribute)
AND (asla.action_code = 'ADVERTISEMENT_PUB_TO_START_ORG'
OR asla.action_code = 'ADVERTISEMENT_ESC_TO_NEXT_LVL')
AND asla.active_flag = 'Y'
AND asla.object_id = p_object_id
AND asla.object_type = 'OPEN_ASSIGNMENT'
AND asla.action_set_type_code = 'ADVERTISEMENT';
SELECT to_number(action_attribute1), to_number(action_attribute2) into
l_org_hierarchy_version_id, l_start_org_id
FROM pa_action_set_lines
WHERE action_set_id = p_action_set_id
AND action_set_line_number = (
SELECT MAX(action_set_line_number)
FROM pa_action_set_lines
WHERE (action_code = 'ADVERTISEMENT_PUB_TO_START_ORG'
OR action_code = 'ADVERTISEMENT_ESC_TO_NEXT_LVL')
AND (status_code = 'PENDING'
OR status_code = 'COMPLETE')
AND ACTION_SET_LINE_NUMBER < p_action_set_line_number
AND action_set_id = p_action_set_id);
PA_ACTION_SETS_PUB.Update_Action_Set_Line (
p_action_set_line_id => p_action_set_line_id
,p_action_set_line_number => p_action_set_line_number
,p_record_version_number => p_action_set_line_rec_ver_num
,p_action_code => p_action_code
,p_action_attribute1 => to_char(l_org_hierarchy_version_id)
,p_action_attribute2 => to_char(l_new_start_org_id)
,p_condition_tbl => p_action_set_line_cond_tbl
,p_validate_only => FND_API.G_FALSE
,p_commit => FND_API.G_FALSE
,p_init_msg_list => FND_API.G_FALSE
,x_return_status => x_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
IF p_insert_audit_flag = 'T' THEN
FOR i IN l_organization_id_tbl.FIRST..l_organization_id_tbl.LAST LOOP
-- insert into into the global audit record
l_action_line_audit_rec.reason_code := 'CONDITION_MET';
IF p_insert_audit_flag = 'T' THEN
FND_MSG_PUB.get (
p_encoded => FND_API.G_TRUE
,p_msg_index => FND_MSG_PUB.Count_Msg
,p_data => l_encoded_message_text
,p_msg_index_out => l_msg_index_out);
, p_insert_audit_flag IN VARCHAR2 := 'T'
, x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
) IS
TYPE number_tbl IS TABLE OF NUMBER
INDEX BY BINARY_INTEGER;
l_action_line_audit_rec pa_action_set_utils.insert_audit_lines_rec_type;
SELECT pro.person_name, pro.person_id
FROM pa_people_role_on_orgs_v pro, per_people_f pf
WHERE pro.organization_id = to_char(p_organization_id)
AND pro.project_role_type = '3'
AND sysdate between pro.start_date_active and
nvl(pro.end_date_active, sysdate)
AND TRUNC(sysdate) between TRUNC(PF.EFFECTIVE_START_DATE) AND
TRUNC(PF.EFFECTIVE_END_DATE)
AND nvl(PF.CURRENT_EMPLOYEE_FLAG,nvl(PF.CURRENT_NPW_FLAG,'N'))='Y'
AND PF.person_id=pro.person_id;
SELECT person_name, person_id
FROM pa_people_role_on_orgs_v
WHERE organization_id = to_char(p_organization_id)
AND project_role_type = '3'
AND sysdate between start_date_active and nvl(end_date_active, sysdate);
ELSIF p_insert_audit_flag = 'T' THEN
-- Insert the people into the global audit record
FOR i IN l_person_id_tbl.FIRST..l_person_id_tbl.LAST LOOP
l_action_line_audit_rec.reason_code := 'CONDITION_MET';
IF p_insert_audit_flag = 'T' THEN
FND_MSG_PUB.get (
p_encoded => FND_API.G_TRUE
,p_msg_index => FND_MSG_PUB.Count_Msg
,p_data => l_encoded_message_text
,p_msg_index_out => l_msg_index_out);
, p_insert_audit_flag IN VARCHAR2 := 'T'
, x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
) IS
l_return_status VARCHAR2(1);
l_action_line_audit_rec pa_action_set_utils.insert_audit_lines_rec_type;
IF p_action_status_code = 'PENDING' AND p_insert_audit_flag = 'T' THEN
-- insert the single audit line into the global audit record
l_action_line_audit_rec.reason_code := 'CONDITION_MET';
, p_insert_audit_flag IN VARCHAR2 := 'T'
, x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
--, x_msg_count OUT NUMBER
--, x_msg_data OUT VARCHAR2
) IS
--cursor to get tpeople on the project role
-- Commented for Performance Fix 4898314 SQL ID 14906391
--CURSOR get_person_on_project_role IS
--SELECT rv.resource_source_id, rv.resource_name
--FROM pa_project_parties pp,
-- pa_c_resource_v rv
--WHERE pp.project_id = p_project_id
-- AND pp.project_role_id = p_project_role_id
-- AND pp.resource_source_id = rv.resource_source_id;
SELECT per.person_id resource_source_id, per.full_name resource_name
FROM pa_project_parties pp,
per_people_f per
WHERE pp.project_id = p_project_id
AND pp.project_role_id = p_project_role_id
AND pp.resource_source_id = per.person_id
AND (per.employee_number IS NOT NULL OR per.npw_number IS NOT NULL)
AND (per.current_employee_flag = 'Y' OR per.current_npw_flag = 'Y')
AND trunc(sysdate) BETWEEN per.effective_start_date
AND per.effective_end_date
AND trunc(sysdate) BETWEEN pp.start_date_active and nvl(pp.end_date_active, sysdate); --bug#9500452
SELECT per.person_id resource_source_id, per.full_name resource_name
FROM per_people_f per
WHERE per.person_id = p_person_id
AND (per.employee_number IS NOT NULL OR per.npw_number IS NOT NULL)
AND (per.current_employee_flag = 'Y' OR per.current_npw_flag = 'Y')
AND trunc(sysdate) BETWEEN per.effective_start_date
AND per.effective_end_date;
l_action_line_audit_rec pa_action_set_utils.insert_audit_lines_rec_type;
, p_insert_audit_flag => p_insert_audit_flag
, x_return_status => x_return_status);
IF p_insert_audit_flag = 'T' THEN
FOR i in 1..FND_MSG_PUB.Count_Msg LOOP
FND_MSG_PUB.get (
p_encoded => FND_API.G_TRUE
,p_msg_index => i
,p_data => l_encoded_message_text
,p_msg_index_out => l_msg_index_out);
l_action_line_audit_rec.reason_code := 'DELETED';
ELSIF l_action_status_code = 'UPDATE_PENDING' THEN
l_action_line_audit_rec.reversed_action_set_line_id := p_action_set_line_id;
l_action_line_audit_rec.reason_code := 'UPDATED';
, p_insert_audit_flag IN VARCHAR2 := 'T'
, x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
) IS
l_ntf_recipient_person_id_tbl system.pa_num_tbl_type;
l_action_line_audit_rec pa_action_set_utils.insert_audit_lines_rec_type;
SELECT asgn.assignment_name,
asgn.start_date,
asgn.end_date,
-- pa_expenditures_utils.GetOrgTlName(proj.carrying_out_organization_id), -- Commented for Bug 4866284
pa_resource_utils.get_organization_name(proj.carrying_out_organization_id), -- Added for Bug 4866284
prt.meaning,
asgn.min_resource_job_level,
asgn.max_resource_job_level,
sp.meaning staffing_priority_name,
nvl(asgn.assignment_effort, PA_SCHEDULE_UTILS.get_num_hours(asgn.project_id, asgn.assignment_id)),
(trunc(asgn.end_date) - trunc(asgn.start_date) +1 ),
asgn.description,
asgn.additional_information,
asgn.revenue_bill_rate,
asgn.revenue_currency_code,
asgn.bill_rate_override,
asgn.bill_rate_curr_override,
asgn.markup_percent_override,
fcst.meaning,
asgn.tp_rate_override,
asgn.tp_currency_override,
asgn.tp_calc_base_code_override,
asgn.tp_percent_applied_override,
wt.name,
asgn.transfer_price_rate, -- Added for bug 3051110
asgn.transfer_pr_rate_curr
FROM pa_project_assignments asgn,
pa_projects_all proj,
pa_project_role_types prt,
pa_lookups sp,
pa_lookups fcst,
pa_work_types_v wt
WHERE assignment_id = p_assignment_id
AND asgn.project_role_id = prt.project_role_id
AND asgn.project_id = proj.project_id
AND sp.lookup_type(+) = 'STAFFING_PRIORITY_CODE'
AND asgn.staffing_priority_code = sp.lookup_code(+)
AND fcst.lookup_type(+) = 'TP_AMOUNT_TYPE'
AND asgn.fcst_tp_amount_type = fcst.lookup_code(+)
AND asgn.work_type_id = wt.work_type_id;
SELECT plks.meaning
FROM pa_lookups plks
WHERE plks.lookup_type = 'CC_MARKUP_BASE_CODE'
AND plks.lookup_code = p_override_basis_code;
SELECT proj.name,
proj.segment1,
hou.name,
PA_PROJECT_PARTIES_UTILS.GET_PROJECT_MANAGER(proj.project_id),
PA_PROJECT_PARTIES_UTILS.GET_PROJECT_MANAGER_NAME,
PA_PROJECTS_MAINT_UTILS.GET_PRIMARY_CUSTOMER_NAME(proj.project_id)
FROM pa_projects_all proj,
HR_ALL_ORGANIZATION_UNITS HOU
WHERE proj.project_id = p_project_id
AND proj.CARRYING_OUT_ORGANIZATION_ID = HOU.ORGANIZATION_ID;
SELECT -999
BULK COLLECT INTO l_ntf_recipient_person_id_tbl
FROM pa_project_assignments
WHERE assignment_id = p_assignment_id;
SELECT pa_advertisement_ntf_wf_s.nextval
INTO l_wf_item_key
FROM dual;
PA_WORKFLOW_UTILS.Insert_WF_Processes
(p_wf_type_code => 'ADVERTISEMENTS_NTF_WF'
,p_item_type => p_wf_item_type
,p_item_key => l_wf_item_key
,p_entity_key1 => to_char(p_project_id)
,p_entity_key2 => to_char(l_ntf_recipient_person_id_tbl(i))
,p_description => NULL
,p_err_code => l_err_code
,p_err_stage => l_err_stage
,p_err_stack => l_err_stack );
IF p_insert_audit_flag = 'T' AND
(p_action_code = 'ADVERTISEMENT_SEND_NTF_PERSON' OR
p_action_code = 'ADVERTISEMENT_SEND_NTF_ROLE') THEN
l_action_line_audit_rec.reason_code := 'CONDITION_MET';
PROCEDURE Update_Staffing_Priority (
p_action_set_line_id IN pa_action_set_lines.action_set_line_id%TYPE
, p_object_id IN pa_action_sets.object_id%TYPE
, p_action_code IN pa_action_set_lines.action_code%TYPE
, p_action_status_code IN pa_action_set_lines.status_code%TYPE
, p_staffing_priority_code IN pa_project_assignments.staffing_priority_code%TYPE
, p_record_version_number IN pa_project_assignments.record_version_number%TYPE
, p_insert_audit_flag IN VARCHAR2 := 'T'
, x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
) IS
l_staffing_priority_name pa_lookups.meaning%TYPE;
l_action_line_audit_rec pa_action_set_utils.insert_audit_lines_rec_type;
l_update_sp_display_attribute VARCHAR2(80);
PA_DEBUG.init_err_stack('PA_ADVERTISEMENTS_PVT.Update_Staffing_Priority');
PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ADVERTISEMENTS_PVT.Update_Staffing_Priority'
,x_msg => 'Beginning of PA_ADVERTISEMENTS_PVT.Update_Staffing_Priority'
,x_log_level => 5);
SELECT meaning INTO l_staffing_priority_name
FROM pa_lookups
WHERE lookup_type = 'STAFFING_PRIORITY_CODE'
AND lookup_code = p_staffing_priority_code;
PA_PROJECT_ASSIGNMENTS_PKG.Update_Row
( p_assignment_id => p_object_id
,p_record_version_number => p_record_version_number
,p_staffing_priority_code => p_staffing_priority_code
,x_return_status => x_return_status );
IF x_return_status = FND_API.G_RET_STS_SUCCESS AND p_insert_audit_flag = 'T' THEN
-- insert into into the global audit record
l_action_line_audit_rec.reason_code := 'CONDITION_MET';
IF p_insert_audit_flag = 'T' THEN
FND_MSG_PUB.get (
p_encoded => FND_API.G_TRUE
,p_msg_index => FND_MSG_PUB.Count_Msg
,p_data => l_encoded_message_text
,p_msg_index_out => l_msg_index_out);
END IF; -- IF p_insert_audit_flag = 'T'
ELSE -- action status is REVERSE PENDING or UPDATE PENDING
IF p_insert_audit_flag = 'T' THEN
-- Get the Audit Display Attribute
SELECT meaning INTO l_update_sp_display_attribute
FROM pa_lookups
WHERE lookup_type = 'ADVERTISEMENT'
AND lookup_code = 'NO_ACTION_PERFORMED';
l_action_line_audit_rec.reason_code := 'DELETED';
l_action_line_audit_rec.reason_code := 'UPDATED';
l_action_line_audit_rec.audit_display_attribute := l_update_sp_display_attribute;
, p_procedure_name => 'Update_Staffing_Priority'
, p_error_text => SUBSTRB(SQLERRM,1,240));
END Update_Staffing_Priority;
, p_insert_audit_flag IN VARCHAR2 := 'T'
, x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
) IS
TYPE number_tbl IS TABLE OF NUMBER
INDEX BY BINARY_INTEGER;
l_action_line_audit_rec pa_action_set_utils.insert_audit_lines_rec_type;
l_update_sp_display_attribute VARCHAR2(80);
SELECT action_set_line_id, action_code, audit_attribute, audit_display_attribute, 'REVERSED'
FROM pa_action_set_line_aud
WHERE object_id = p_object_id
AND object_type = 'OPEN_ASSIGNMENT'
AND action_set_type_code = 'ADVERTISEMENT'
AND active_flag = 'Y'
AND reversed_action_set_line_id is null
AND encoded_error_message is null;
SELECT action_set_line_id, 'REVERSED'
FROM pa_action_set_line_aud
WHERE object_id = p_object_id
AND object_type = 'OPEN_ASSIGNMENT'
AND action_set_type_code = 'ADVERTISEMENT'
AND active_flag = 'Y'
AND reversed_action_set_line_id is null
GROUP BY action_set_line_id; -- CH2M Performance Bug fix: 2768530
SELECT asl.action_set_line_id, asl.action_code, asa.audit_attribute, asa.audit_display_attribute, 'COMPLETE'
FROM pa_action_set_line_aud asa,
pa_action_set_lines asl,
pa_action_set_line_cond aslc
WHERE asa.object_id = p_object_id
AND asa.object_type = 'OPEN_ASSIGNMENT'
AND asa.action_set_type_code = 'ADVERTISEMENT'
AND asa.action_set_line_id = p_action_set_line_id
AND asa.reversed_action_set_line_id IS NOT NULL
AND asl.action_set_line_id = asa.reversed_action_set_line_id
AND nvl(asl.line_deleted_flag, 'N') = 'N'
AND encoded_error_message is null
AND aslc.action_set_line_id = asl.action_set_line_id
AND aslc.condition_date <= sysdate
AND asl.action_set_line_id <> p_action_set_line_id;
PA_DEBUG.init_err_stack('PA_ADVERTISEMENTS_PVT.Update_Staffing_Priority');
IF p_insert_audit_flag = 'T' THEN
-- insert into into the global audit record
l_action_line_audit_rec.reason_code := 'CONDITION_MET';
PA_ACTION_SETS_PVT.Bulk_Update_Line_Status(
p_action_set_line_id_tbl => l_action_set_line_id_tbl2
,p_line_status_tbl => l_line_status_tbl2
,x_return_status => l_return_status
);
UPDATE pa_action_set_line_aud
SET active_flag = 'N'
WHERE action_set_line_id = l_action_set_line_id_tbl2(i);
SELECT meaning INTO l_remove_adv_display_attribute
FROM pa_lookups
WHERE lookup_type = 'ADVERTISEMENT'
AND lookup_code = 'NO_ACTION_PERFORMED';
IF p_insert_audit_flag = 'T' THEN
-- insert into into the global audit record
IF p_action_status_code = 'REVERSE_PENDING' THEN
l_action_line_audit_rec.reason_code := 'DELETED';
l_action_line_audit_rec.reason_code := 'UPDATED';
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_line_status_tbl
,x_return_status => l_return_status
);
SELECT project_id, record_version_number
INTO l_project_id, l_record_version_number
FROM pa_project_assignments
WHERE assignment_id = p_object_id;
, p_insert_audit_flag => 'F'
, x_return_status => l_return_status
);
, p_insert_audit_flag => 'F'
, x_return_status => l_return_status -- Changed from x_return_status to l_return_status : 4537865
);
, p_insert_audit_flag => 'F'
, x_return_status => l_return_status -- Changed from x_return_status to l_return_status : 4537865
);
ELSIF l_action_code_tbl(i) = 'ADVERTISEMENT_UPDATE_SP' OR
l_action_code_tbl(i) = 'REVERSE_UPDATE_SP' THEN
Update_Staffing_Priority(
p_action_set_line_id => p_action_set_line_id
, p_object_id => p_object_id
, p_action_code => 'ADVERTISEMENT_UPDATE_SP'
, p_action_status_code => l_action_status_code
, p_staffing_priority_code => l_audit_attribute_tbl(i)
, p_record_version_number => l_record_version_number
, p_insert_audit_flag => 'F'
, x_return_status => l_return_status -- Changed from x_return_status to l_return_status : 4537865
);
l_audit_action_code := 'REVERSE_UPDATE_SP';
SELECT meaning INTO l_update_sp_display_attribute
FROM pa_lookups
WHERE lookup_type = 'ADVERTISEMENT'
AND lookup_code = 'NO_ACTION_PERFORMED';
l_audit_action_code := 'ADVERTISEMENT_UPDATE_SP';
IF p_insert_audit_flag = 'T' AND l_perform_return_status=FND_API.G_RET_STS_SUCCESS THEN
l_action_line_audit_rec.reason_code := l_audit_reason_code;
IF l_audit_action_code = 'ADVERTISEMENT_UPDATE_SP' OR
l_audit_action_code = 'REVERSE_UPDATE_SP' THEN
l_action_line_audit_rec.audit_display_attribute := l_update_sp_display_attribute;
ELSIF p_insert_audit_flag = 'T' AND l_perform_return_status <> FND_API.G_RET_STS_SUCCESS THEN
FND_MSG_PUB.get (
p_encoded => FND_API.G_TRUE
,p_msg_index => FND_MSG_PUB.Count_Msg
,p_data => l_encoded_message_text
,p_msg_index_out => l_msg_index_out);
END IF; -- if p_insert_audit_flag = 'T'
select 'Y' into v_dummy from pa_project_assignments
where assignment_id = v_assig_id
and STATUS_CODE in
(select PROJECT_STATUS_CODE from pa_project_statuses
where PROJECT_SYSTEM_STATUS_CODE = 'OPEN_ASGMT');