[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Check_Delete_Task_OK_pvt
( p_api_version_number IN NUMBER
, p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
, p_return_status OUT NOCOPY VARCHAR2 /*Added the nocopy check for 4537865 */
, p_msg_count OUT NOCOPY NUMBER /*Added the nocopy check for 4537865 */
, p_msg_data OUT NOCOPY VARCHAR2 /*Added the nocopy check for 4537865 */
, p_project_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
, p_pm_project_reference IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
, p_task_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
, p_pm_task_reference IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
--Project Structure changes done for bug 2765115
, p_structure_type IN VARCHAR2 := 'FINANCIAL'
, p_task_version_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
--END Project Structure changes done for bug 2765115
, p_delete_task_ok_flag OUT NOCOPY VARCHAR2 /*Added the nocopy check for 4537865 */
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Check_Delete_Task_Ok_Pvt';
SELECT segment1
FROM pa_projects p
WHERE p.project_id = p_pa_project_id;
SELECT task_number
FROM pa_tasks p
WHERE p.task_id = p_pa_task_id;
When ever a task was deleted then the error "Task ID invalid" was thrown and the task deletion failed.
Analysis:
This error was also coming up from the Convert_pm_taskref_to_id_all API. There was a call to the
PA_PROJECT_PVT.Convert_pm_taskref_to_id_all API in the flow. MSP calls the PA_PROJECT_PUB.check_task_mfd API
to determine whether a task can be deleted at all or not. This in turn calls the pa_project_pub.Check_Delete_Task_OK
API and that in turn calls the pa_project_check_pvt.Check_Delete_Task_OK_pvt API.
In this API the call to PA_PROJECT_PVT.Convert_pm_taskref_to_id_all the structure type is also passed as shown below.
As the structure type 'FINANCIAL' was passed the Convert_pm_taskref_to_id_all API was looking for the task in the
PA_TASKS table in the cursor l_task_id_csr. As this was not returning any rows the above error is thrown. The task
is there but due to the structure type 'WORKPLAN' it is residing in the elements tables as it is not yet published as well.
Ideally we should pass 'WORKPLAN' as the structure type. This is being done in the PA_PROJECT_PUB.DELETE_TASK API.
-- If the following criteria is satisfied,
-- switch flow to WORKPLAN
-- i) Workplan is enabled;
( p_old_message_code => 'PA_STRUCT_VER_NO_UPDATE'
,p_msg_attribute => 'CHANGE'
,p_resize_flag => 'N'
,p_msg_context => 'GENERAL'
,p_attribute1 => ''
,p_attribute2 => ''
,p_attribute3 => ''
,p_attribute4 => ''
,p_attribute5 => '');
PA_TASK_UTILS.Check_Delete_Task_Ok
( x_task_id => l_task_id_out
--bug 3010538 , x_validation_mode => 'R' --bug 2947492
, x_err_code => l_err_code
, x_err_stage => l_err_stage
, x_err_stack => l_err_stack );
PA_PROJ_ELEMENTS_UTILS.Check_Delete_task_Ver_Ok
(
p_project_id => p_project_id
,p_task_version_id => l_task_version_id
,p_parent_structure_ver_id => l_structure_version_id
--bug 3010538 ,p_validation_mode => 'R' --bug 2947492
,x_return_status => l_return_status
,x_error_message_code => l_error_message_code
);
p_delete_task_ok_flag := 'N';
( p_old_message_code => 'PA_CHECK_DELETE_TASK_FAILED'
,p_msg_attribute => 'CHANGE'
,p_resize_flag => 'Y'
,p_msg_context => 'DELT'
,p_attribute1 => l_amg_segment1
,p_attribute2 => l_amg_task_number
,p_attribute3 => ''
,p_attribute4 => ''
,p_attribute5 => '');
( p_old_message_code => 'PA_CHECK_DELETE_TASK_FAILED'
,p_msg_attribute => 'CHANGE'
,p_resize_flag => 'Y'
,p_msg_context => 'DELT'
,p_attribute1 => l_amg_segment1
,p_attribute2 => l_amg_task_number
,p_attribute3 => ''
,p_attribute4 => ''
,p_attribute5 => '');
p_delete_task_ok_flag := 'Y';
p_delete_task_ok_flag := 'N' ; -- Made this value as 'N' as per logic in the API
p_delete_task_ok_flag := 'N' ; -- Made this value as 'N' as per logic in the API
p_delete_task_ok_flag := 'N' ; -- Made this value as 'N' as per logic in the API
END Check_Delete_Task_Ok_pvt;
SELECT segment1
FROM pa_projects p
WHERE p.project_id = p_pa_project_id;
SELECT task_number
FROM pa_tasks p
WHERE p.task_id = p_pa_task_id;
SELECT 1
FROM pa_tasks
WHERE pm_task_reference = p_pm_task_reference
AND project_id = p_project_id;
SELECT 1
FROM pa_projects
WHERE pm_project_reference = p_pm_project_reference;
PROCEDURE Check_Delete_Project_OK_pvt
(p_api_version_number IN NUMBER
, p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
, p_return_status OUT NOCOPY VARCHAR2 /*Added the nocopy check for 4537865 */
, p_msg_count OUT NOCOPY NUMBER /*Added the nocopy check for 4537865 */
, p_msg_data OUT NOCOPY VARCHAR2 /*Added the nocopy check for 4537865 */
, p_project_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
, p_pm_project_reference IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
, p_delete_project_ok_flag OUT NOCOPY VARCHAR2 /*Added the nocopy check for 4537865 */
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Check_Delete_Project_Ok_Pvt';
SELECT segment1
FROM pa_projects p
WHERE p.project_id = p_pa_project_id;
PA_PROJECT_UTILS.Check_Delete_Project_Ok
( x_project_id => l_project_id_out
--bug3010538 , x_validation_mode => 'R' --bug 2947492
, x_err_code => l_err_code
, x_err_stage => l_err_stage
, x_err_stack => l_err_stack);
p_delete_project_ok_flag := 'N';
p_delete_project_ok_flag := 'Y';
p_delete_project_ok_flag := 'N' ;
p_delete_project_ok_flag := 'N' ;
p_delete_project_ok_flag := 'N' ;
END Check_Delete_Project_Ok_pvt;
SELECT top_task_id
INTO l_top_task_id
FROM pa_tasks
WHERE task_id = l_task_id_out;
SELECT top_task_id
INTO l_new_parent_top_task_id
FROM pa_tasks
WHERE task_id = l_parent_task_id_out;
SELECT segment1
FROM pa_projects p
WHERE p.project_id = p_pa_project_id;
SELECT 1
FROM pa_tasks
WHERE task_number = p_task_number
AND project_id = p_project_id;
SELECT segment1
FROM pa_projects p
WHERE p.project_id = p_pa_project_id;
SELECT task_number
FROM pa_tasks p
WHERE p.task_id = p_pa_task_id;
SELECT 'x' FROM
pa_organizations_v o,
pa_std_bill_rate_schedules brs
WHERE o.organization_id = l_org_id
AND o.organization_id = brs.organization_id
AND brs.schedule_type <> 'NON-LABOR';*/
SELECT 'x'
FROM
pa_std_bill_rate_schedules brs
WHERE brs.schedule_type = 'JOB'
AND brs.BILL_RATE_SCH_ID = l_job_rate_schdid;
SELECT 'x'
FROM
pa_std_bill_rate_schedules brs
WHERE brs.schedule_type = 'EMPLOYEE'
AND brs.BILL_RATE_SCH_ID = l_emp_rate_schdid;*/
SELECT 'x'
FROM pa_std_bill_rate_schedules_all brs, pa_project_types_all pt, pa_projects pa
WHERE bill_rate_sch_id = l_job_rate_schdid
AND pa.project_id = l_project_id
AND brs.job_group_id = pt.bill_job_group_id
and brs.schedule_type = 'JOB'
AND pa.project_type = pt.project_type
AND pa.org_id = pt.org_id --added for Bug 5675391
AND ( pa.multi_currency_BILLING_flag = 'Y'
OR (pa.multi_currency_billing_flag = 'N'
AND brs.rate_sch_currency_code = pa.projfunc_currency_code))
AND ((pa_multi_currency_billing.is_sharing_bill_rates_allowed(pa.org_id) = 'Y')
or (pa_multi_currency_billing.is_sharing_bill_rates_allowed(pa.org_id) = 'N'
and brs.org_id = pa.org_id))
and (brs.share_across_ou_flag = 'Y'
OR (brs.share_across_ou_flag = 'N'
and brs.org_id = pa.org_id
));
SELECT 'x'
FROM pa_std_bill_rate_schedules_all brs, pa_projects pa
WHERE brs.bill_rate_sch_id = l_emp_rate_schdid
AND pa.project_id = l_project_id
and brs.schedule_type = 'EMPLOYEE'
AND ( pa.multi_currency_BILLING_flag = 'Y'
OR (pa.multi_currency_billing_flag='N'
AND brs.rate_sch_currency_code = pa.projfunc_currency_code))
AND ((pa_multi_currency_billing.is_sharing_bill_rates_allowed(pa.org_id) = 'Y')
or (pa_multi_currency_billing.is_sharing_bill_rates_allowed(pa.org_id) = 'N'
and brs.org_id = pa.org_id))
and (brs.share_across_ou_flag = 'Y'
OR (brs.share_across_ou_flag = 'N'
and brs.org_id = pa.org_id
));
SELECT 'x' FROM
pa_organizations_v o,
pa_std_bill_rate_schedules brs
WHERE o.organization_id = l_org_id
AND o.organization_id = brs.organization_id
AND brs.schedule_type = 'NON-LABOR';
SELECT 'x'
FROM
pa_std_bill_rate_schedules brs,
pa_lookups l
WHERE organization_id = l_org_id
AND brs.schedule_type <> 'NON-LABOR'
AND l.lookup_type = 'SCHEDULE TYPE'
AND l.lookup_code (+) = brs.schedule_type
AND brs.std_bill_rate_schedule = l_bill_rate_schdl;*/
SELECT 'x'
FROM
pa_std_bill_rate_schedules brs,
pa_lookups l
WHERE organization_id = l_org_id
AND brs.schedule_type = 'NON-LABOR'
AND l.lookup_type = 'SCHEDULE TYPE'
AND l.lookup_code (+) = brs.schedule_type
AND brs.std_bill_rate_schedule = l_bill_rate_schdl;
SELECT 'x'
FROM pa_lookups l, pa_ind_rate_schedules irs
WHERE l.lookup_type = 'IND RATE SCHEDULE TYPE'
AND l.lookup_code = irs.ind_rate_schedule_type
AND irs.ind_rate_sch_id = l_sch_id
AND irs.project_id IS NULL
AND TRUNC(SYSDATE) BETWEEN TRUNC(irs.start_date_active )
AND NVL (irs.end_date_active,TRUNC(SYSDATE)) ;
SELECT segment1
FROM pa_projects p
WHERE p.project_id = p_pa_project_id;
SELECT project_id
FROM pa_tasks p
WHERE p.task_id = p_pa_task_id;
,p_update_start_date_flag OUT NOCOPY VARCHAR2 /*Added the nocopy check for 4537865 */
,p_update_end_date_flag OUT NOCOPY VARCHAR2 /*Added the nocopy check for 4537865 */
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'check_start_end_date_Pvt';
p_update_start_date_flag := 'Y';
p_update_start_date_flag := 'N';
p_update_end_date_flag := 'Y';
p_update_end_date_flag := 'N';
p_update_end_date_flag := 'Y';
p_update_end_date_flag := 'N';
p_update_end_date_flag := 'N';
p_update_start_date_flag := 'Y';
p_update_start_date_flag := 'N';
p_update_end_date_flag := 'Y';
p_update_end_date_flag := 'N';
p_update_end_date_flag := 'N';
p_update_start_date_flag := 'N';
p_update_end_date_flag := 'Y';
p_update_end_date_flag := 'N';
p_update_end_date_flag := 'Y';
p_update_end_date_flag := 'N';
p_update_end_date_flag := 'N';
p_update_end_date_flag := NULL ;
p_update_start_date_flag := NULL ;
p_update_end_date_flag := NULL ;
p_update_start_date_flag := NULL ;
p_update_end_date_flag := NULL ;
p_update_start_date_flag := NULL ;
SELECT person_id,
start_date_active,
end_date_active
-- begin NEW code for ROLE BASED SECURITY
, PROJECT_PARTY_ID
, RESOURCE_ID
, RESOURCE_TYPE_ID
, RECORD_VERSION_NUMBER
, scheduled_flag
-- end NEW code for ROLE BASED SECURITY
FROM pa_project_players
WHERE project_id = p_project_id
AND project_role_type = 'PROJECT MANAGER'
-- AND nvl(end_date_active,nvl(p_start_date,sysdate)) between nvl(p_start_date,sysdate) and nvl(p_end_date,sysdate);
l_current_manager_updated VARCHAR2(1) := 'N';
SELECT segment1, COMPLETION_DATE
FROM pa_projects p
WHERE p.project_id = p_pa_project_id;
update the end date of the existing manager with the date provided
Else update the end date of the existing manager to either
(a) new manager's start date -1 or (b) sysdate -1
(being done in check_for_one_manager);
Select project_role_id
Into l_project_role_id
From pa_project_role_types
Where project_role_type='PROJECT MANAGER';
l_current_manager_updated := 'N';
UPDATE pa_project_players
SET end_date_active = p_key_members(i).end_date
WHERE project_id = p_project_id
AND person_id = l_current_project_man_rec.person_id
AND project_role_type = 'PROJECT MANAGER';
UPDATE pa_project_players
SET end_date_active =
Decode( nvl(p_start_date,
PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE)
,PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE,
SYSDATE-1,p_start_date-1)
WHERE project_id = p_project_id
AND person_id =
l_current_project_man_rec.person_id
AND project_role_type = 'PROJECT MANAGER';
Select Decode( nvl(p_start_date,
PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE)
,PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE,
SYSDATE-1,p_start_date-1)
Into v_end_date
From dual;
PA_PROJECT_PARTIES_PUB.update_project_party(
p_api_version => 1.0 -- p_api_version
, p_init_msg_list => FND_API.G_TRUE -- p_init_msg_list
, p_commit => FND_API.G_FALSE -- p_commit /* bug#2417448 */
, p_validate_only => FND_API.G_FALSE -- p_validate_only
, p_validation_level => FND_API.G_VALID_LEVEL_FULL -- p_validation_level
, p_debug_mode => 'N' -- p_debug_mode
, p_object_id => p_project_id -- p_object_id
, p_OBJECT_TYPE => 'PA_PROJECTS' -- p_OBJECT_TYPE
, p_project_role_id => l_project_role_id -- p_project_role_id
, p_project_role_type => 'PROJECT MANAGER' -- p_project_role_type
, p_resource_type_id => l_current_project_man_rec.resource_type_id -- p_resource_type_id
, p_resource_source_id => l_current_project_man_rec.person_id -- p_resource_source_id
, p_resource_id => l_current_project_man_rec.resource_id -- Bug 6631033
, p_resource_name => v_null_char -- p_resource_name
, p_start_date_active => l_current_project_man_rec.start_date_active -- p_start_date_active
, p_scheduled_flag => l_current_project_man_rec.scheduled_flag -- p_scheduled_flag
, p_record_version_number => l_current_project_man_rec.record_version_number -- p_record_version_number
, p_calling_module => 'FORM' -- p_calling_module
, p_project_id => p_project_id -- p_project_id
, p_project_end_date => v_completion_date -- p_project_end_date
, p_project_party_id => l_current_project_man_rec.project_party_id -- p_project_party_id
, p_end_date_active => v_end_date -- p_end_date_active
, x_wf_type => l_wf_type
, x_wf_item_type => l_wf_item_type
, x_wf_process => l_wf_process
, x_assignment_id => l_assignment_id
, x_return_status =>x_return_status -- x_return_status
, x_msg_count => x_msg_count -- x_msg_count
, x_msg_data => x_msg_data -- x_msg_data
);
l_current_manager_updated := 'Y';
IF l_current_manager_updated = 'N' THEN
IF ( p_start_date IS NULL OR
p_start_date = PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE ) THEN
l_new_end_date := SYSDATE - 1;
UPDATE pa_project_players
SET end_date_active =
Decode (SIGN(start_date_active-l_new_end_date),1,
start_date_active, l_new_end_date)
WHERE project_id = p_project_id
AND person_id =
l_current_project_man_rec.person_id
AND project_role_type = 'PROJECT MANAGER';
Select
Decode (SIGN(start_date_active-l_new_end_date),1,
start_date_active, l_new_end_date)
Into v_end_date
From pa_project_players
Where project_id = p_project_id
And person_id = l_current_project_man_rec.person_id
And project_role_type = 'PROJECT MANAGER'; */
PA_PROJECT_PARTIES_PUB.UPDATE_PROJECT_PARTY(
p_api_version => 1.0 -- p_api_version
, p_init_msg_list => FND_API.G_TRUE -- p_init_msg_list
, p_commit => FND_API.G_FALSE -- p_commit /* bug#2417448 */
, p_validate_only => FND_API.G_FALSE -- p_validate_only
, p_validation_level => FND_API.G_VALID_LEVEL_FULL -- p_validation_level
, p_debug_mode => 'N' -- p_debug_mode
, p_object_id => p_project_id -- p_object_id
, p_OBJECT_TYPE => 'PA_PROJECTS' -- p_OBJECT_TYPE
, p_project_role_id => l_project_role_id -- p_project_role_id
, p_project_role_type => 'PROJECT MANAGER' -- p_project_role_type
, p_resource_type_id => l_current_project_man_rec.resource_type_id -- p_resource_type_id
, p_resource_source_id => l_current_project_man_rec.person_id -- p_resource_source_id
, p_resource_id => l_current_project_man_rec.resource_id --Added resource_id parameter for Bug 6631033
, p_resource_name => v_null_char -- p_resource_name
, p_start_date_active => l_current_project_man_rec.start_date_active -- p_start_date_active
, p_scheduled_flag => l_current_project_man_rec.scheduled_flag -- p_scheduled_flag
, p_record_version_number => l_current_project_man_rec.record_version_number -- p_record_version_number
, p_calling_module => 'FORM' -- p_calling_module
, p_project_id => p_project_id -- p_project_id
, p_project_end_date => v_completion_date -- p_project_end_date
, p_project_party_id => l_current_project_man_rec.project_party_id -- p_project_party_id
, p_end_date_active => v_end_date -- p_end_date_active
, x_assignment_id => l_assignment_id
, x_wf_type => l_wf_type
, x_wf_item_type => l_wf_item_type
, x_wf_process => l_wf_process
, x_return_status => x_return_status -- x_return_status
, x_msg_count => x_msg_count -- x_msg_count
, x_msg_data => x_msg_data -- x_msg_data
);
p_proceed_with_update_flag OUT NOCOPY VARCHAR2, /*Added the nocopy check for 4537865 */
p_return_status OUT NOCOPY VARCHAR2 /*Added the nocopy check for 4537865 */
) IS
CURSOR l_get_task_number_csr (p_task_id IN NUMBER)
IS
SELECT task_number
FROM pa_tasks
WHERE task_id = p_task_id;
SELECT task_id,pm_task_reference
FROM pa_tasks
WHERE project_id = p_project_id
AND task_number = p_task_number ;
SELECT segment1
FROM pa_projects p
WHERE p.project_id = p_pa_project_id;
p_proceed_with_update_flag := 'Y';
p_proceed_with_update_flag := 'Y';
p_proceed_with_update_flag := 'Y';
IF G_task_num_updated_index_tbl.EXISTS(1) THEN
l_count := G_task_num_updated_index_tbl.COUNT;
G_task_num_updated_index_tbl(l_count).task_index
:= p_array_cell_number;
G_task_num_updated_index_tbl(l_count).task_id
:= p_task_id;
UPDATE pa_tasks
SET task_number = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR ||
TO_CHAR(G_index_counter)
WHERE task_id = p_task_id;
p_proceed_with_update_flag := 'N';
p_proceed_with_update_flag := 'N' ;
p_proceed_with_update_flag := 'N' ;
p_proceed_with_update_flag := 'N';
SELECT task_id,parent_task_id,TRUNC(start_date) start_date,TRUNC(completion_date) completion_date,task_name, -- Bug Fix 4705139
pm_task_reference
FROM pa_tasks pt where project_id = p_project_id
AND
(
parent_task_id IS NOT NULL
OR EXISTS
(SELECT 'x' FROM pa_tasks pt2
WHERE parent_task_id = pt.task_id));
SELECT min(TRUNC(start_date)),max(TRUNC(completion_date)) FROM -- Bug Fix 4705139
PA_TASKS
WHERE project_id = l_project_id
AND parent_task_id = l_task_id;
SELECT TRUNC(start_date) start_date,TRUNC(completion_date) completion_date -- Bug Fix 4705139
FROM
PA_TASKS
WHERE project_id = l_project_id
AND task_id = l_task_id;
SELECT segment1
FROM pa_projects p
WHERE p.project_id = p_pa_project_id;
SELECT task_number
FROM pa_tasks p
WHERE p.task_id = p_pa_task_id;