[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Pa_Debug.WRITE(g_module_name,' PA_PROJ_ELEMENTS_PKG.Insert_Row Called ',
l_debug_level3);
PA_PROJ_ELEMENTS_PKG.Insert_Row
(
X_ROW_ID => X_ROW_ID
,X_PROJ_ELEMENT_ID => l_proj_element_id
,X_PROJECT_ID => p_project_id
,X_OBJECT_TYPE => p_object_type
,X_ELEMENT_NUMBER => p_dlvr_number
,X_NAME => p_dlvr_name
,X_DESCRIPTION => p_dlvr_description
,X_STATUS_CODE => p_status_code
,X_WF_STATUS_CODE => null
,X_PM_PRODUCT_CODE => p_pm_source_code /* Bug no. 3651113 -- Passed p_pm_source_code instead of null*/
,X_PM_TASK_REFERENCE => p_dlvr_reference
,X_CLOSED_DATE => NULL
,X_LOCATION_ID => NULL
,X_MANAGER_PERSON_ID => p_dlvr_owner_id
,X_CARRYING_OUT_ORGANIZATION_ID => p_carrying_out_org_id
,X_TYPE_ID => p_dlvr_type_id
,X_PRIORITY_CODE => NULL
,X_INC_PROJ_PROGRESS_FLAG => NULL
,X_REQUEST_ID => NULL
,X_PROGRAM_APPLICATION_ID => NULL
,X_PROGRAM_ID => NULL
,X_PROGRAM_UPDATE_DATE => NULL
,X_LINK_TASK_FLAG => NULL
,X_ATTRIBUTE_CATEGORY => p_attribute_category
,X_ATTRIBUTE1 => p_attribute1
,X_ATTRIBUTE2 => p_attribute2
,X_ATTRIBUTE3 => p_attribute3
,X_ATTRIBUTE4 => p_attribute4
,X_ATTRIBUTE5 => p_attribute5
,X_ATTRIBUTE6 => p_attribute6
,X_ATTRIBUTE7 => p_attribute7
,X_ATTRIBUTE8 => p_attribute8
,X_ATTRIBUTE9 => p_attribute9
,X_ATTRIBUTE10 => p_attribute10
,X_ATTRIBUTE11 => p_attribute11
,X_ATTRIBUTE12 => p_attribute12
,X_ATTRIBUTE13 => p_attribute13
,X_ATTRIBUTE14 => p_attribute14
,X_ATTRIBUTE15 => p_attribute15
,X_TASK_WEIGHTING_DERIV_CODE => NULL
,X_WORK_ITEM_CODE => NULL
,X_UOM_CODE => NULL
,X_WQ_ACTUAL_ENTRY_CODE => NULL
,X_TASK_PROGRESS_ENTRY_PAGE_ID => NULL
,X_PARENT_STRUCTURE_ID => p_parent_structure_id
,X_PHASE_CODE => NULL
,X_PHASE_VERSION_ID => NULL
,X_PROGRESS_WEIGHT => l_progress_weight -- 3570283 changed from p_progress_weight
-- ,X_PROG_ROLLUP_METHOD => NULL
,X_FUNCTION_CODE => NULL
,X_SOURCE_OBJECT_ID => p_project_id
,X_SOURCE_OBJECT_TYPE => 'PA_PROJECTS'
);
Pa_Debug.WRITE(g_module_name,' Out of PA_PROJ_ELEMENTS_PKG.Insert_Row Element ['||p_dlvr_number||']['||p_dlvr_number||']',
l_debug_level3);
Pa_Debug.WRITE(g_module_name,' PA_PROJ_ELEMENT_VERSIONS_PKG.Insert_Row Called ',
l_debug_level3);
PA_PROJ_ELEMENT_VERSIONS_PKG.Insert_Row
(
X_ROW_ID => X_ROW_ID
,X_ELEMENT_VERSION_ID => l_dlvr_version_id
,X_PROJ_ELEMENT_ID => l_proj_element_id
,X_OBJECT_TYPE => p_object_type
,X_PROJECT_ID => p_project_id
,X_PARENT_STRUCTURE_VERSION_ID => p_parent_struct_ver_id
,X_DISPLAY_SEQUENCE => NULL
,X_WBS_LEVEL => NULL
,X_WBS_NUMBER => NULL
,X_ATTRIBUTE_CATEGORY => p_attribute_category
,X_ATTRIBUTE1 => p_attribute1
,X_ATTRIBUTE2 => p_attribute2
,X_ATTRIBUTE3 => p_attribute3
,X_ATTRIBUTE4 => p_attribute4
,X_ATTRIBUTE5 => p_attribute5
,X_ATTRIBUTE6 => p_attribute6
,X_ATTRIBUTE7 => p_attribute7
,X_ATTRIBUTE8 => p_attribute8
,X_ATTRIBUTE9 => p_attribute9
,X_ATTRIBUTE10 => p_attribute10
,X_ATTRIBUTE11 => p_attribute11
,X_ATTRIBUTE12 => p_attribute12
,X_ATTRIBUTE13 => p_attribute13
,X_ATTRIBUTE14 => p_attribute14
,X_ATTRIBUTE15 => p_attribute15
,X_TASK_UNPUB_VER_STATUS_CODE => NULL
,X_SOURCE_OBJECT_ID => p_project_id
,X_SOURCE_OBJECT_TYPE => 'PA_PROJECTS'
);
Pa_Debug.WRITE(g_module_name,' Out of PA_PROJ_ELEMENT_VERSIONS_PKG.Insert_Row Element['||l_dlvr_version_id||']',
l_debug_level3);
Pa_Debug.WRITE(g_module_name,' PA_PROJ_ELEMENT_SCH_PKG.Insert_Row Called ',
l_debug_level3);
PA_PROJ_ELEMENT_SCH_PKG.Insert_Row
(
X_ROW_ID => X_Row_Id
,X_PEV_SCHEDULE_ID => l_new_pev_schedule_id
,X_ELEMENT_VERSION_ID => l_dlvr_version_id
,X_PROJECT_ID => p_project_id
,X_PROJ_ELEMENT_ID => l_proj_element_id
,X_SCHEDULED_START_DATE => NULL
,X_SCHEDULED_FINISH_DATE => p_scheduled_finish_date
,X_OBLIGATION_START_DATE => NULL
,X_OBLIGATION_FINISH_DATE => NULL
,X_ACTUAL_START_DATE => NULL
,X_ACTUAL_FINISH_DATE => p_actual_finish_date
,X_ESTIMATED_START_DATE => NULL
,X_ESTIMATED_FINISH_DATE => NULL
,X_DURATION => NULL
,X_EARLY_START_DATE => NULL
,X_EARLY_FINISH_DATE => NULL
,X_LATE_START_DATE => NULL
,X_LATE_FINISH_DATE => NULL
,X_CALENDAR_ID => NULL
,X_MILESTONE_FLAG => NULL
,X_CRITICAL_FLAG => NULL
,X_WQ_PLANNED_QUANTITY => NULL
,X_PLANNED_EFFORT => NULL
,X_ACTUAL_DURATION => NULL
,X_ESTIMATED_DURATION => NULL
,X_ATTRIBUTE_CATEGORY => p_attribute_category
,X_ATTRIBUTE1 => p_attribute1
,X_ATTRIBUTE2 => p_attribute2
,X_ATTRIBUTE3 => p_attribute3
,X_ATTRIBUTE4 => p_attribute4
,X_ATTRIBUTE5 => p_attribute5
,X_ATTRIBUTE6 => p_attribute6
,X_ATTRIBUTE7 => p_attribute7
,X_ATTRIBUTE8 => p_attribute8
,X_ATTRIBUTE9 => p_attribute9
,X_ATTRIBUTE10 => p_attribute10
,X_ATTRIBUTE11 => p_attribute11
,X_ATTRIBUTE12 => p_attribute12
,X_ATTRIBUTE13 => p_attribute13
,X_ATTRIBUTE14 => p_attribute14
,X_ATTRIBUTE15 => p_attribute15
,X_SOURCE_OBJECT_ID => p_project_id
,X_SOURCE_OBJECT_TYPE => 'PA_PROJECTS'
);
Pa_Debug.WRITE(g_module_name,' Out of PA_PROJ_ELEMENT_SCH_PKG.Insert_Row schedule ['||l_new_pev_schedule_id||']',
l_debug_level3);
Pa_Debug.WRITE(g_module_name,' PA_OBJECT_RELATIONSHIPS_PKG.Insert_Row Called Structure-To-Deliverable',
l_debug_level3);
PA_OBJECT_RELATIONSHIPS_PKG.INSERT_ROW
(
p_user_id => FND_GLOBAL.USER_ID
,p_object_type_from => 'PA_STRUCTURES'
,p_object_id_from1 => p_parent_struct_ver_id
,p_object_id_from2 => p_parent_structure_id
,p_object_id_from3 => NULL
,p_object_id_from4 => NULL
,p_object_id_from5 => NULL
,p_object_type_to => 'PA_DELIVERABLES'
,p_object_id_to1 => p_dlvr_version_id
,p_object_id_to2 => l_proj_element_id
,p_object_id_to3 => NULL
,p_object_id_to4 => NULL
,p_object_id_to5 => NULL
,p_relationship_type => 'S'
,p_relationship_subtype => 'STRUCTURE_TO_DELIVERABLE'
,p_lag_day => NULL
,p_imported_lag => NULL
,p_priority => NULL
,p_pm_product_code => p_pm_source_code /* Bug no. 3651113 -- Passed p_pm_source_code instead of null*/
,x_object_relationship_id => l_new_obj_rel_id
,x_return_status => x_return_status
);
Pa_Debug.WRITE(g_module_name,' Out of PA_OBJECT_RELATIONSHIPS_PKG.Insert_Row Structure-To-Deliverable str vers ['
||p_parent_struct_ver_id||']str ['||p_parent_struct_ver_id||']' , l_debug_level3);
PA_OBJECT_RELATIONSHIPS_PKG.INSERT_ROW
(
p_user_id => FND_GLOBAL.USER_ID
,p_object_type_from => 'PA_TASKS'
,p_object_id_from1 => p_task_version_id
,p_object_id_from2 => p_task_id
,p_object_id_from3 => NULL
,p_object_id_from4 => NULL
,p_object_id_from5 => NULL
,p_object_type_to => 'PA_DELIVERABLES'
,p_object_id_to1 => p_dlvr_version_id
,p_object_id_to2 => l_proj_element_id
,p_object_id_to3 => NULL
,p_object_id_to4 => NULL
,p_object_id_to5 => NULL
,p_relationship_type => 'A'
,p_relationship_subtype => 'TASK_TO_DELIVERABLE'
,p_lag_day => NULL
,p_imported_lag => NULL
,p_priority => NULL
,p_pm_product_code => NULL
,x_object_relationship_id => l_new_obj_rel_id
,x_return_status => x_return_status
);
PROCEDURE Update_Deliverable
(
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_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
, p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
, p_debug_mode IN VARCHAR2 := 'N'
, p_max_msg_count IN NUMBER := NULL
, p_record_version_number IN NUMBER := 1
, p_object_type IN PA_PROJ_ELEMENTS.OBJECT_TYPE%TYPE := 'PA_DELIVERABLES'
, p_project_id IN PA_PROJ_ELEMENTS.PROJECT_ID%TYPE
, p_dlvr_number IN PA_PROJ_ELEMENTS.ELEMENT_NUMBER%TYPE
, p_dlvr_name IN PA_PROJ_ELEMENTS.NAME%TYPE
, p_dlvr_description IN PA_PROJ_ELEMENTS.DESCRIPTION%TYPE := NULL
, p_dlvr_owner_id IN PA_PROJ_ELEMENTS.MANAGER_PERSON_ID%TYPE := NULL
, p_dlvr_owner_name IN VARCHAR2 := NULL
, p_carrying_out_org_id IN PA_PROJ_ELEMENTS.CARRYING_OUT_ORGANIZATION_ID%TYPE := NULL
, p_carrying_out_org_name IN VARCHAR2 := NULL
, p_dlvr_version_id IN PA_PROJ_ELEMENT_VERSIONS.ELEMENT_VERSION_ID%TYPE := NULL
, p_status_code IN PA_PROJ_ELEMENTS.STATUS_CODE%TYPE := NULL
, p_parent_structure_id IN PA_PROJ_ELEMENTS.PARENT_STRUCTURE_ID%TYPE
, p_parent_struct_ver_id IN PA_PROJ_ELEMENT_VERSIONS.ELEMENT_VERSION_ID%TYPE
, p_dlvr_type_id IN PA_PROJ_ELEMENTS.TYPE_ID%TYPE := NULL
, p_dlvr_type_name IN VARCHAR2 := NULL
, p_progress_weight IN PA_PROJ_ELEMENTS.PROGRESS_WEIGHT%TYPE := NULL
, p_scheduled_finish_date IN PA_PROJ_ELEM_VER_SCHEDULE.SCHEDULED_FINISH_DATE%TYPE := NULL
, p_actual_finish_date IN PA_PROJ_ELEM_VER_SCHEDULE.ACTUAL_FINISH_DATE%TYPE := NULL
, p_task_id IN NUMBER := NULL
, p_task_version_id IN NUMBER := NULL
, p_task_name IN VARCHAR2 := NULL
, p_attribute_category IN PA_PROJ_ELEMENTS.ATTRIBUTE_CATEGORY%TYPE := NULL
, p_attribute1 IN PA_PROJ_ELEMENTS.ATTRIBUTE1%TYPE := NULL
, p_attribute2 IN PA_PROJ_ELEMENTS.ATTRIBUTE2%TYPE := NULL
, p_attribute3 IN PA_PROJ_ELEMENTS.ATTRIBUTE3%TYPE := NULL
, p_attribute4 IN PA_PROJ_ELEMENTS.ATTRIBUTE4%TYPE := NULL
, p_attribute5 IN PA_PROJ_ELEMENTS.ATTRIBUTE5%TYPE := NULL
, p_attribute6 IN PA_PROJ_ELEMENTS.ATTRIBUTE6%TYPE := NULL
, p_attribute7 IN PA_PROJ_ELEMENTS.ATTRIBUTE7%TYPE := NULL
, p_attribute8 IN PA_PROJ_ELEMENTS.ATTRIBUTE8%TYPE := NULL
, p_attribute9 IN PA_PROJ_ELEMENTS.ATTRIBUTE9%TYPE := NULL
, p_attribute10 IN PA_PROJ_ELEMENTS.ATTRIBUTE10%TYPE := NULL
, p_attribute11 IN PA_PROJ_ELEMENTS.ATTRIBUTE11%TYPE := NULL
, p_attribute12 IN PA_PROJ_ELEMENTS.ATTRIBUTE12%TYPE := NULL
, p_attribute13 IN PA_PROJ_ELEMENTS.ATTRIBUTE13%TYPE := NULL
, p_attribute14 IN PA_PROJ_ELEMENTS.ATTRIBUTE14%TYPE := NULL
, p_attribute15 IN PA_PROJ_ELEMENTS.ATTRIBUTE15%TYPE := NULL
, p_dlvr_item_id IN PA_PROJ_ELEMENTS.PROJ_ELEMENT_ID%TYPE
, p_pm_source_code IN VARCHAR2 :=NULL /* Bug no. 3651113 */
, p_deliverable_reference IN VARCHAR2 := NULL -- added for bug# 3749447
, x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
, x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
, x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
)
IS
l_msg_count NUMBER := 0;
l_calling_mode VARCHAR2(30) := 'UPDATE_DUE_DATE';
l_update_allowed VARCHAR2(1) := NULL;
SELECT
ROWID
,PM_SOURCE_REFERENCE
FROM
PA_PROJ_ELEMENTS PPE
WHERE
PPE.PROJ_ELEMENT_ID = p_dlvr_item_id AND
PPE.PROJECT_ID = p_project_id AND
PPE.OBJECT_TYPE = l_object_type;
SELECT
ROWID
FROM
PA_PROJ_ELEMENT_VERSIONS PEV
WHERE
PEV.PROJ_ELEMENT_ID = p_dlvr_item_id AND
PEV.ELEMENT_VERSION_ID = p_dlvr_version_id AND
PEV.PROJECT_ID = p_project_id AND
PEV.OBJECT_TYPE = l_object_type;
SELECT
PPS.PROJECT_SYSTEM_STATUS_CODE
FROM
PA_PROJECT_STATUSES PPS
WHERE
PPS.PROJECT_STATUS_CODE = p_status_code;
SELECT
pev.pev_schedule_id,
ROWID
FROM
pa_proj_elem_ver_schedule pev
WHERE
pev.proj_element_id = p_dlvr_item_id AND
pev.ELEMENT_VERSION_ID = p_dlvr_version_id AND
pev.PROJECT_ID = p_project_id;
Select
ppe.element_number,
ppe.name,
ppe.description,
ppe.status_code,
ppe.manager_person_id,
ppe.carrying_out_organization_id,
ppe.record_version_number,
ppe.parent_structure_id,
ppe.type_id,
ppe.progress_weight,
ppe.base_percent_comp_deriv_code, --ppe.prog_rollup_method,
pvs.scheduled_finish_date,
pvs.actual_finish_date
From
pa_proj_elements ppe,
pa_proj_elem_ver_schedule pvs
Where
ppe.project_id = p_project_id and
ppe.proj_element_id = p_dlvr_item_id and
ppe.object_type = l_object_type and
pvs.project_id = p_project_id and
ppe.project_id = pvs.project_id and
ppe.proj_element_id = pvs.proj_element_id;
PA_DEBUG.set_curr_function( p_function => 'UPDATE_DELIVERABLE',
p_debug_mode => l_debug_mode );
savepoint UPDATE_DLVR_PVT;
Pa_Debug.WRITE(g_module_name,' Printing Input Params in UPDATE_DELIVERABLE : PADLVPVB.pls ####()',
l_debug_level3);
Pa_Debug.WRITE(g_module_name,' Scheduled_finish_date is changed in update dlvr',
l_debug_level3);
Pa_Debug.WRITE(g_module_name,' PA_PROJ_ELEMENTS_PKG.Update_Row Called ',
l_debug_level3);
PA_PROJ_ELEMENTS_PKG.Update_Row
(
X_ROW_ID => ppe_rec.rowid
,X_PROJ_ELEMENT_ID => l_element_id
,X_PROJECT_ID => p_project_id
,X_OBJECT_TYPE => p_object_type
,X_ELEMENT_NUMBER => p_dlvr_number
,X_NAME => p_dlvr_name
,X_DESCRIPTION => p_dlvr_description
,X_STATUS_CODE => p_status_code
,X_WF_STATUS_CODE => NULL
,X_PM_PRODUCT_CODE => p_pm_source_code /* Bug no. 3651113 -- Passed p_pm_source_code instead of null*/
,X_PM_TASK_REFERENCE => p_deliverable_reference -- 3749447 changed from NULL to retrieved value
,X_CLOSED_DATE => NULL
,X_LOCATION_ID => NULL
,X_MANAGER_PERSON_ID => p_dlvr_owner_id
,X_CARRYING_OUT_ORGANIZATION_ID => p_carrying_out_org_id
,X_TYPE_ID => p_dlvr_type_id
,X_PRIORITY_CODE => NULL
,X_INC_PROJ_PROGRESS_FLAG => NULL
,X_RECORD_VERSION_NUMBER => p_record_version_number
,X_REQUEST_ID => NULL
,X_PROGRAM_APPLICATION_ID => NULL
,X_PROGRAM_ID => NULL
,X_PROGRAM_UPDATE_DATE => NULL
,X_ATTRIBUTE_CATEGORY => p_attribute_category
,X_ATTRIBUTE1 => p_attribute1
,X_ATTRIBUTE2 => p_attribute2
,X_ATTRIBUTE3 => p_attribute3
,X_ATTRIBUTE4 => p_attribute4
,X_ATTRIBUTE5 => p_attribute5
,X_ATTRIBUTE6 => p_attribute6
,X_ATTRIBUTE7 => p_attribute7
,X_ATTRIBUTE8 => p_attribute8
,X_ATTRIBUTE9 => p_attribute9
,X_ATTRIBUTE10 => p_attribute10
,X_ATTRIBUTE11 => p_attribute11
,X_ATTRIBUTE12 => p_attribute12
,X_ATTRIBUTE13 => p_attribute13
,X_ATTRIBUTE14 => p_attribute14
,X_ATTRIBUTE15 => p_attribute15
,X_TASK_WEIGHTING_DERIV_CODE => NULL
,X_WORK_ITEM_CODE => NULL
,X_UOM_CODE => NULL
,X_WQ_ACTUAL_ENTRY_CODE => NULL
,X_TASK_PROGRESS_ENTRY_PAGE_ID => NULL
,X_PARENT_STRUCTURE_ID => p_parent_structure_id
,X_PHASE_CODE => NULL
,X_PHASE_VERSION_ID => NULL
,X_PROGRESS_WEIGHT => l_progress_weight -- 3570283 changed from p_progress_weight
-- ,X_PROG_ROLLUP_METHOD => NULL
,X_FUNCTION_CODE => NULL
);
Pa_Debug.WRITE(g_module_name,' Out of PA_PROJ_ELEMENTS_PKG.Update_Row ',
l_debug_level3);
Pa_Debug.WRITE(g_module_name,' PA_PROJ_ELEMENT_VERSIONS_PKG.Update_Row Called ',
l_debug_level3);
PA_PROJ_ELEMENT_VERSIONS_PKG.Update_Row
(
X_ROW_ID => pev_rec.rowid
,X_ELEMENT_VERSION_ID => p_dlvr_version_id
,X_PROJ_ELEMENT_ID => p_dlvr_item_id
,X_OBJECT_TYPE => p_object_type
,X_PROJECT_ID => p_project_id
,X_PARENT_STRUCTURE_VERSION_ID => p_parent_struct_ver_id
,X_DISPLAY_SEQUENCE => NULL
,X_WBS_LEVEL => NULL
,X_WBS_NUMBER => NULL
,X_RECORD_VERSION_NUMBER => p_record_version_number
,X_ATTRIBUTE_CATEGORY => p_attribute_category
,X_ATTRIBUTE1 => p_attribute1
,X_ATTRIBUTE2 => p_attribute2
,X_ATTRIBUTE3 => p_attribute3
,X_ATTRIBUTE4 => p_attribute4
,X_ATTRIBUTE5 => p_attribute5
,X_ATTRIBUTE6 => p_attribute6
,X_ATTRIBUTE7 => p_attribute7
,X_ATTRIBUTE8 => p_attribute8
,X_ATTRIBUTE9 => p_attribute9
,X_ATTRIBUTE10 => p_attribute10
,X_ATTRIBUTE11 => p_attribute11
,X_ATTRIBUTE12 => p_attribute12
,X_ATTRIBUTE13 => p_attribute13
,X_ATTRIBUTE14 => p_attribute14
,X_ATTRIBUTE15 => p_attribute15
,X_TASK_UNPUB_VER_STATUS_CODE => NULL
);
Pa_Debug.WRITE(g_module_name,' Out of PA_PROJ_ELEMENT_VERSIONS_PKG.Update_Row ',
l_debug_level3);
Pa_Debug.WRITE(g_module_name,' PA_PROJ_ELEMENT_SCH_PKG.Update_Row Called ',
l_debug_level3);
PA_PROJ_ELEMENT_SCH_PKG.Update_Row
(
X_ROW_ID => pes_rec.rowid
,X_PEV_SCHEDULE_ID => pes_rec.pev_schedule_id
,X_ELEMENT_VERSION_ID => p_dlvr_version_id
,X_PROJECT_ID => p_project_id
,X_PROJ_ELEMENT_ID => p_dlvr_item_id
,X_SCHEDULED_START_DATE => NULL
,X_SCHEDULED_FINISH_DATE => p_scheduled_finish_date
,X_OBLIGATION_START_DATE => NULL
,X_OBLIGATION_FINISH_DATE => NULL
,X_ACTUAL_START_DATE => NULL
,X_ACTUAL_FINISH_DATE => p_actual_finish_date
,X_ESTIMATED_START_DATE => NULL
,X_ESTIMATED_FINISH_DATE => NULL
,X_DURATION => NULL
,X_EARLY_START_DATE => NULL
,X_EARLY_FINISH_DATE => NULL
,X_LATE_START_DATE => NULL
,X_LATE_FINISH_DATE => NULL
,X_CALENDAR_ID => NULL
,X_MILESTONE_FLAG => NULL
,X_CRITICAL_FLAG => NULL
,X_WQ_PLANNED_QUANTITY => NULL
,X_PLANNED_EFFORT => NULL
,X_ACTUAL_DURATION => NULL
,X_ESTIMATED_DURATION => NULL
,X_RECORD_VERSION_NUMBER => p_record_version_number
,X_ATTRIBUTE_CATEGORY => p_attribute_category
,X_ATTRIBUTE1 => p_attribute1
,X_ATTRIBUTE2 => p_attribute2
,X_ATTRIBUTE3 => p_attribute3
,X_ATTRIBUTE4 => p_attribute4
,X_ATTRIBUTE5 => p_attribute5
,X_ATTRIBUTE6 => p_attribute6
,X_ATTRIBUTE7 => p_attribute7
,X_ATTRIBUTE8 => p_attribute8
,X_ATTRIBUTE9 => p_attribute9
,X_ATTRIBUTE10 => p_attribute10
,X_ATTRIBUTE11 => p_attribute11
,X_ATTRIBUTE12 => p_attribute12
,X_ATTRIBUTE13 => p_attribute13
,X_ATTRIBUTE14 => p_attribute14
,X_ATTRIBUTE15 => p_attribute15
);
Pa_Debug.WRITE(g_module_name,' Out of PA_PROJ_ELEMENT_SCH_PKG.Update_Row ',
l_debug_level3);
,p_calling_mode => 'UPDATE' -- Added for bug 3911050
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
) ;
ROLLBACK TO UPDATE_DLVR_PVT;
ROLLBACK TO UPDATE_DLVR_PVT;
, p_procedure_name => 'Update_Deliverable'
, p_error_text => x_msg_data);
END Update_Deliverable;
PROCEDURE delete_deliverable
(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_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL
,p_calling_module IN VARCHAR2 :='SELF_SERVICE'
,p_debug_mode IN VARCHAR2 :='N'
,p_max_msg_count IN NUMBER :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_dlv_element_id IN pa_proj_elements.proj_element_id%TYPE
,p_dlv_version_id IN pa_proj_element_versions.element_version_id%TYPE
,p_rec_ver_number IN pa_proj_elements.record_version_number%TYPE
,p_project_id IN pa_projects_all.project_id%TYPE
,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
)
IS
l_debug_mode VARCHAR2(10);
PA_DEBUG.set_curr_function( p_function => 'DELETE_DELIVERABLE',
p_debug_mode => l_debug_mode );
pa_debug.g_err_stage:= 'Inside DELETE_DELIVERABLE ';
savepoint DELETE_DELIVERABLE_SP ;
select 'x' into l_dummy
from PA_PROJ_ELEMENTS
where proj_element_id = p_dlv_element_id
and record_version_number = decode(p_calling_module, 'AMG', record_version_number,p_rec_ver_number)
for update of record_version_number NOWAIT;
SELECT Pa_Deliverable_Utils.IS_Dlvr_Item_Based(element_version_id)
INTO l_item_dlv
FROM Pa_Proj_Element_Versions
WHERE proj_element_id = p_dlv_element_id
AND project_id = p_project_id;
DELETE FROM PA_OBJECT_RELATIONSHIPS
WHERE OBJECT_ID_TO2 = p_dlv_element_id
AND object_type_from = 'PA_TASKS' /* Included these 4 additional Clauses for Performance Bug # 3614361*/
AND object_type_to = 'PA_DELIVERABLES'
AND relationship_type = 'A'
AND RELATIONSHIP_SUBTYPE ='TASK_TO_DELIVERABLE';
DELETE FROM PA_OBJECT_RELATIONSHIPS
WHERE OBJECT_ID_TO2 = p_dlv_element_id
AND object_type_from = 'PA_ASSIGNMENTS'
AND object_type_to = 'PA_DELIVERABLES'
AND relationship_type = 'A'
AND RELATIONSHIP_SUBTYPE ='ASSIGNMENT_TO_DELIVERABLE';
DELETE FROM PA_OBJECT_RELATIONSHIPS
WHERE OBJECT_ID_TO2 = p_dlv_element_id
AND object_type_from = 'PA_STRUCTURES'
AND object_type_to = 'PA_DELIVERABLES'
AND relationship_type = 'S'
AND RELATIONSHIP_SUBTYPE ='STRUCTURE_TO_DELIVERABLE';
DELETE FROM PA_PROJ_ELEM_VER_SCHEDULE
WHERE ELEMENT_VERSION_ID = p_dlv_version_id ;
DELETE FROM PA_PROJ_ELEMENT_VERSIONS
WHERE ELEMENT_VERSION_ID = p_dlv_version_id ;
DELETE FROM PA_PROJ_ELEMENTS
WHERE PROJ_ELEMENT_ID = p_dlv_element_id ;
fnd_attached_documents2_pkg.delete_attachments
(X_entity_name => 'PA_DLVR_DOC_ATTACH',
X_pk1_value => to_char(p_dlv_version_id),
X_delete_document_flag => 'Y');
fnd_attached_documents2_pkg.delete_attachments
(X_entity_name => 'PA_DVLR_ATTACH',
X_pk1_value => to_char(p_dlv_version_id),
X_delete_document_flag => 'Y');
, p_action => 'DELETE'
, p_item_dlv => l_item_dlv
, p_master_inv_org_id => l_master_inv_org_id
, p_dlv_rec => l_dlv_rec
, x_return_status => x_return_status
, x_msg_data => x_msg_data
, x_msg_count => x_msg_count
);
OKE_DELIVERABLE_UTILS_PUB.DELETE_DELIVERABLE
( P_DELIVERABLE_ID => p_dlv_version_id
, X_Return_Status => x_return_status
, X_Msg_Count => x_msg_count
, X_Msg_Data => x_msg_data
) ;
DELETE FROM PA_OBJECT_RELATIONSHIPS
WHERE OBJECT_ID_FROM2 = p_dlv_element_id
RETURNING object_id_to2,object_id_to1
BULK COLLECT INTO l_action_element_id_tbl,l_action_version_id_tbl;
DELETE FROM PA_PROJ_ELEMENTS
WHERE PROJ_ELEMENT_ID = l_action_element_id_tbl(i);
DELETE FROM PA_PROJ_ELEMENT_VERSIONS
WHERE ELEMENT_VERSION_ID = l_action_version_id_tbl(i) ;
DELETE FROM PA_PROJ_ELEM_VER_SCHEDULE
WHERE ELEMENT_VERSION_ID = l_action_version_id_tbl(i) ;
pa_debug.g_err_stage:= 'Exiting DELETE_DELIVERABLE' ;
ROLLBACK TO DELETE_DELIVERABLE_SP;
pa_debug.g_err_stage := 'inside invalid arg exception of DELETE_DELIVERABLE';
ROLLBACK TO DELETE_DELIVERABLE_SP;
pa_debug.write( g_module_name,'DELETE_DELIVERABLE: G_EXC_ERROR msg_count' ||l_msg_count ,5);
ROLLBACK TO DELETE_DELIVERABLE_SP;
,p_procedure_name => 'DELETE_DELIVERABLE');
pa_debug.write('DELETE_DELIVERABLE: ' || g_module_name,pa_debug.g_err_stage,5);
END delete_deliverable ;
PROCEDURE DELETE_DLV_TASK_ASSOCIATION
(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_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL
,p_calling_module IN VARCHAR2 :='SELF_SERVICE'
,p_debug_mode IN VARCHAR2 :='N'
,p_max_msg_count IN NUMBER :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_task_element_id IN pa_proj_elements.proj_element_id%TYPE
,p_task_version_id IN pa_proj_element_versions.element_version_id%TYPE
,p_dlv_element_id IN pa_proj_elements.proj_element_id%TYPE
,p_dlv_version_id IN pa_proj_element_versions.element_version_id%TYPE
,p_object_relationship_id IN pa_object_relationships.object_relationship_id%TYPE
,p_obj_rec_ver_number IN pa_object_relationships.record_version_number%TYPE
,p_project_id IN pa_projects_all.project_id%TYPE
,p_calling_context IN VARCHAR2 -- Bug 3555460
,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
)
IS
l_debug_mode VARCHAR2(10);
PA_DEBUG.set_curr_function( p_function => 'DELETE_DLV_TASK_ASSOCIATION',
p_debug_mode => l_debug_mode );
pa_debug.g_err_stage:= 'Inside DELETE_DLV_TASK_ASSOCIATION ';
PA_OBJECT_RELATIONSHIPS_PKG.DELETE_ROW
( p_object_relationship_id => p_object_relationship_id
,p_object_type_from => NULL
,p_object_id_from1 => NULL
,p_object_id_from2 => NULL
,p_object_id_from3 => NULL
,p_object_id_from4 => NULL
,p_object_id_from5 => NULL
,p_object_type_to => NULL
,p_object_id_to1 => NULL
,p_object_id_to2 => NULL
,p_object_id_to3 => NULL
,p_object_id_to4 => NULL
,p_object_id_to5 => NULL
,p_record_version_number => p_obj_rec_ver_number
,p_pm_product_code => NULL
,x_return_status => x_return_status
);
pa_debug.g_err_stage:= 'Exiting DELETE_DLV_TASK_ASSOCIATION' ;
pa_debug.g_err_stage := 'inside invalid arg exception of DELETE_DLV_TASK_ASSOCIATION';
,p_procedure_name => 'DELETE_DLV_TASK_ASSOCIATION');
pa_debug.write('DELETE_DLV_TASK_ASSOCIATION: ' || g_module_name,pa_debug.g_err_stage,5);
END DELETE_DLV_TASK_ASSOCIATION ;
INSERT INTO PA_OBJECT_RELATIONSHIPS (
object_relationship_id
,object_type_from
,object_id_from1
,object_type_to
,object_id_to1
,relationship_type
,created_by
,creation_date
,last_updated_by
,last_update_date
,object_id_from2
,object_id_to2
,relationship_subtype
,record_version_number
,last_update_login
)
VALUES
(
pa_object_relationships_s.nextval
,'PA_TASKS'
,decode(p_task_or_dlv,'PA_TASKS',p_task_or_dlv_ver_id,p_version_id_tbl(i))
,'PA_DELIVERABLES'
,decode(p_task_or_dlv,'PA_TASKS',p_version_id_tbl(i),p_task_or_dlv_ver_id)
,'A'
,fnd_global.user_id
,SYSDATE
,fnd_global.user_id
,SYSDATE
,decode(p_task_or_dlv,'PA_TASKS',p_task_or_dlv_elt_id,p_element_id_tbl(i))
,decode(p_task_or_dlv,'PA_TASKS',p_element_id_tbl(i),p_task_or_dlv_elt_id)
,'TASK_TO_DELIVERABLE'
,1
,fnd_global.login_id
) ;
SELECT obj.object_relationship_id
FROM PA_OBJECT_RELATIONSHIPS obj
WHERE OBJ.object_id_from2 = p_task_element_id
AND OBJ.object_id_to2 = p_dlv_element_id
AND OBJ.object_type_to = 'PA_DELIVERABLES'
AND OBJ.object_type_from = 'PA_ASSIGNMENTS'
AND OBJ.relationship_type = 'A'
AND OBJ.relationship_subtype = 'ASSIGNMENT_TO_DELIVERABLE';
INSERT INTO PA_OBJECT_RELATIONSHIPS (
object_relationship_id
,object_type_from
,object_id_from1
,object_type_to
,object_id_to1
,relationship_type
,created_by
,creation_date
,last_updated_by
,last_update_date
,object_id_from2
,object_id_to2
,relationship_subtype
,record_version_number
,last_update_login
)
VALUES
(
pa_object_relationships_s.nextval
,decode(p_calling_context,'PA_TASKS','PA_TASKS','PA_ASSIGNMENTS')
,p_task_version_id
,'PA_DELIVERABLES'
,p_dlv_version_id
,'A'
,fnd_global.user_id
,SYSDATE
,fnd_global.user_id
,SYSDATE
,p_task_element_id
,p_dlv_element_id
,decode(p_calling_context,'PA_TASKS','TASK_TO_DELIVERABLE','ASSIGNMENT_TO_DELIVERABLE')
,1
,fnd_global.login_id
) ;
PROCEDURE DELETE_DELIVERABLE_STRUCTURE
(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_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL
,p_calling_module IN VARCHAR2 :='SELF_SERVICE'
,p_debug_mode IN VARCHAR2 :='N'
,p_max_msg_count IN NUMBER :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_project_id IN pa_projects_all.project_id%TYPE
,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
)
IS
l_msg_count NUMBER := 0;
PA_DEBUG.set_curr_function( p_function => 'DELETE_DELIVERABLE_STRUCTURE',
p_debug_mode => p_debug_mode );
pa_debug.g_err_stage:= 'Inside DELETE_DELIVERABLE_STRUCTURE ';
DELETE FROM PA_PROJ_ELEMENTS
WHERE project_id = p_project_id
AND object_type = 'PA_STRUCTURES'
AND proj_element_id in (SELECT proj_element_id
FROM pa_proj_structure_types
WHERE structure_type_id = 8
)
RETURNING proj_element_id INTO l_proj_element_id ;
DELETE FROM PA_PROJ_ELEMENT_VERSIONS
WHERE proj_element_id = l_proj_element_id
AND object_type = 'PA_STRUCTURES'
RETURNING element_version_id INTO l_element_version_id ;
DELETE FROM PA_PROJ_STRUCTURE_TYPES
WHERE proj_element_id = l_proj_element_id ;
DELETE FROM PA_PROJ_ELEM_VER_SCHEDULE
WHERE proj_element_id = l_proj_element_id
AND project_id = p_project_id ;/* Included project_id clause for Performance Bug Fix 3614361 */
/*3614361 Included Delete from PA_PROJ_ELEM_VER_STRUCTURE */
DELETE FROM PA_PROJ_ELEM_VER_STRUCTURE
WHERE proj_element_id = l_proj_element_id
AND project_id = p_project_id ;
BULK DELETE Approach is a better approach in this case */
-- Delete the deliverables,actions and the relationships
DELETE FROM PA_PROJ_ELEMENT_VERSIONS
WHERE project_id = p_project_id
AND object_type in ('PA_DELIVERABLES','PA_ACTIONS')
RETURNING ELEMENT_VERSION_ID ,OBJECT_TYPE,PROJ_ELEMENT_ID --Included Proj_element_id for Bug 3538320
BULK COLLECT INTO l_dlv_version_tbl,l_object_type_tbl,l_dlv_proj_elt_tbl;
DELETE FROM PA_OBJECT_RELATIONSHIPS
WHERE object_id_to2 = l_dlv_proj_elt_tbl(j)
and object_type_to = 'PA_DELIVERABLES'; -- Added for perf bug# 3964586;
DELETE FROM PA_OBJECT_RELATIONSHIPS
WHERE object_id_from1 = l_dlv_version_tbl(j)
and object_type_from = 'PA_DELIVERABLES'
and object_type_to = 'PA_ACTIONS';
DELETE FROM PA_PROJ_ELEM_VER_SCHEDULE
WHERE project_id = p_project_id
AND element_version_id = l_dlv_version_tbl(j);
DELETE FROM PA_PROJ_ELEMENTS
WHERE proj_element_id = l_dlv_proj_elt_tbl(j);
OKE_DELIVERABLE_UTILS_PUB.DELETE_DELIVERABLE
( P_DELIVERABLE_ID => l_dlv_version_tbl(i)
, X_Return_Status => x_return_status
, X_Msg_Count => x_msg_count
, X_Msg_Data => x_msg_data
) ;
fnd_attached_documents2_pkg.delete_attachments
(X_entity_name => 'PA_DLVR_DOC_ATTACH',
X_pk1_value => to_char(l_dlv_version_tbl(i)),
X_delete_document_flag => 'Y');
fnd_attached_documents2_pkg.delete_attachments
(X_entity_name => 'PA_DVLR_ATTACH',
X_pk1_value => to_char(l_dlv_version_tbl(i)),
X_delete_document_flag => 'Y');
pa_debug.g_err_stage := 'inside invalid arg exception of DELETE_DELIVERABLE_STRUCTURE';
,p_procedure_name => 'DELETE_DELIVERABLE_STRUCTURE' );
pa_debug.write('DELETE_DELIVERABLE_STRUCTURE' || g_module_name,SQLERRM,4);
pa_debug.write('DELETE_DELIVERABLE_STRUCTURE' || g_module_name,pa_debug.G_Err_Stack,4);
END DELETE_DELIVERABLE_STRUCTURE ;
PROCEDURE DELETE_DLV_TASK_ASSCN_IN_BULK
(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_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL
,p_calling_module IN VARCHAR2 :='SELF_SERVICE'
,p_debug_mode IN VARCHAR2 :='N'
,p_max_msg_count IN NUMBER :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_calling_context IN VARCHAR2 := 'PA_TASKS'
,p_task_element_id IN pa_proj_elements.proj_element_id%TYPE
,p_task_version_id IN pa_proj_element_versions.element_version_id%TYPE
,p_project_id IN pa_projects_all.project_id%TYPE
,p_delete_or_validate IN VARCHAR2 := 'B' -- 3955848 V- Validate , D - Delete, B - Validate and Delete ( default )
,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
)
IS
l_msg_count NUMBER ;
SELECT 'Y'
FROM DUAL
WHERE EXISTS (SELECT 'Y'
FROM pa_proj_element_versions pev
,pa_object_relationships obj1
,pa_object_relationships obj2
WHERE obj1.object_id_from2 = p_task_element_id
AND obj1.relationship_type = 'A'
AND obj1.relationship_subtype = 'TASK_TO_DELIVERABLE'
AND obj1.object_type_from = 'PA_TASKS'
AND obj1.object_type_to = 'PA_DELIVERABLES'
AND obj2.object_id_from2 = obj1.object_id_to2
AND obj2.relationship_type = 'A'
AND obj2.relationship_subtype = 'DELIVERABLE_TO_ACTION'
AND obj2.object_type_from = 'PA_DELIVERABLES'
AND obj2.object_type_to = 'PA_ACTIONS'
AND obj2.object_id_to2 = pev.proj_element_id
AND (nvl(OKE_DELIVERABLE_UTILS_PUB.WSH_Initiated_Yn(pev.element_version_id),'N') = 'Y'
OR nvl(OKE_DELIVERABLE_UTILS_PUB.REQ_Initiated_Yn(pev.element_version_id),'N') = 'Y'
OR nvl(OKE_DELIVERABLE_UTILS_PUB.MDS_Initiated_Yn(pev.element_version_id),'N') = 'Y'
OR PA_DELIVERABLE_UTILS.GET_FUNCTION_CODE(pev.proj_element_id) = 'BILLING'
-- OR nvl(PA_DELIVERABLE_UTILS.IS_DELIVERABLE_HAS_PROGRESS(c_project_id,pev.proj_element_id),'N') = 'Y' Commented for 3651781
OR nvl(PA_PROGRESS_UTILS.published_dlv_prog_exists(c_project_id , pev.proj_element_id),'N') = 'Y'
)
);
SELECT 'Y'
FROM DUAL
WHERE EXISTS (SELECT 'Y'
FROM pa_proj_elements ppe
,pa_object_relationships obj1
,pa_task_types ptt
WHERE obj1.object_id_from2 = p_task_element_id
AND obj1.relationship_type = 'A'
AND obj1.relationship_subtype = 'TASK_TO_DELIVERABLE'
AND obj1.object_type_from = 'PA_TASKS'
AND obj1.object_type_to = 'PA_DELIVERABLES'
AND obj1.object_id_from2 = ppe.proj_element_id
and ppe.type_id = ptt.task_type_id
and ppe.object_type = 'PA_TASKS'
and nvl(ppe.base_percent_comp_deriv_code,ptt.base_percent_comp_deriv_code) = 'DELIVERABLE'
and nvl(PA_PROGRESS_UTILS.published_dlv_prog_exists(c_project_id , obj1.object_id_to2),'N') = 'Y');
PA_DEBUG.set_curr_function( p_function => 'DELETE_DLV_TASK_ASSOCIATION',
p_debug_mode => p_debug_mode );
pa_debug.g_err_stage:= 'Inside DELETE_DLV_TASK_ASSOCIATION ';
IF p_delete_or_validate IN ('B','V') THEN
OPEN C(p_project_id) ;
pa_debug.g_err_stage:= 'Delete the association';
IF p_delete_or_validate IN ('B','D') THEN
DELETE FROM pa_object_relationships
WHERE object_id_from2 = p_task_element_id
AND object_type_from = 'PA_TASKS'
AND object_type_to = 'PA_DELIVERABLES'
AND relationship_type = 'A'
AND relationship_subtype = 'TASK_TO_DELIVERABLE' ;
DELETE FROM pa_object_relationships
WHERE object_id_from2 = p_task_element_id
AND object_type_from = 'PA_ASSIGNMENTS'
AND object_type_to = 'PA_DELIVERABLES'
AND relationship_type = 'A'
AND relationship_subtype = 'ASSIGNMENT_TO_DELIVERABLE' ;
pa_debug.g_err_stage:= 'Exiting DELETE_DLV_TASK_ASSCN_IN_BULK' ;
pa_debug.g_err_stage := 'inside invalid arg exception of DELETE_DLV_TASK_ASSCN_IN_BULK';
,p_procedure_name => 'DELETE_DLV_TASK_ASSCN_IN_BULK');
pa_debug.write('DELETE_DLV_TASK_ASSCN_IN_BULK: ' || g_module_name,pa_debug.g_err_stage,5);
END DELETE_DLV_TASK_ASSCN_IN_BULK ;
PROCEDURE DELETE_DLV_ASSOCIATIONS
(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_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL
,p_calling_module IN VARCHAR2 :='SELF_SERVICE'
,p_debug_mode IN VARCHAR2 :='N'
,p_max_msg_count IN NUMBER :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_project_id IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
)
IS
l_debug_mode VARCHAR2(10);
/* This cursor is very much similar to the one (cursor c) in DELETE_DLV_TASK_ASSCN_IN_BULK API
But with little modifications - bug 3906015*/
/*
CURSOR C (c_project_id IN NUMBER) IS
SELECT 'Y'
from dual
WHERE EXISTS(
SELECT 'Y'
FROM PA_PROJ_ELEMENT_VERSIONS pev1,
PA_OBJECT_RELATIONSHIPS obj1,
PA_PROJ_ELEMENT_VERSIONS pev,
PA_OBJECT_RELATIONSHIPS obj2
WHERE obj1.object_id_from2 = pev1.proj_element_id
AND pev1.project_id = c_project_id
AND pev1.object_type = 'PA_TASKS'
AND obj1.relationship_type = 'A'
AND obj1.relationship_subtype = 'TASK_TO_DELIVERABLE'
AND obj1.object_type_from = 'PA_TASKS'
AND obj1.object_type_to = 'PA_DELIVERABLES'
AND obj2.object_id_from2 = obj1.object_id_to2
AND obj2.relationship_type = 'A'
AND obj2.relationship_subtype = 'DELIVERABLE_TO_ACTION'
AND obj1.object_type_from = 'PA_DELIVERABLES'
AND obj1.object_type_to = 'PA_ACTIONS'
AND obj2.object_id_to2 = pev.proj_element_id
AND (nvl(OKE_DELIVERABLE_UTILS_PUB.WSH_Initiated_Yn(pev.element_version_id),'N') = 'Y'
OR nvl(OKE_DELIVERABLE_UTILS_PUB.REQ_Initiated_Yn(pev.element_version_id),'N') = 'Y'
OR nvl(OKE_DELIVERABLE_UTILS_PUB.MDS_Initiated_Yn(pev.element_version_id),'N') = 'Y'
OR PA_DELIVERABLE_UTILS.GET_FUNCTION_CODE(pev.proj_element_id) = 'BILLING'
OR nvl(PA_PROGRESS_UTILS.published_dlv_prog_exists(c_project_id , pev.proj_element_id),'N') = 'Y'
)
);
SELECT 'Y'
from dual
WHERE EXISTS(
SELECT 'Y'
FROM pa_proj_elements ppe
,pa_object_relationships obj1
,pa_task_types ptt
WHERE
ppe.project_id = c_project_id
and ppe.object_type = 'PA_TASKS'
and ppe.type_id = ptt.task_type_id
and nvl(ppe.base_percent_comp_deriv_code,ptt.base_percent_comp_deriv_code) = 'DELIVERABLE'
AND obj1.object_id_from2 = ppe.proj_element_id
AND obj1.relationship_type = 'A'
AND obj1.relationship_subtype = 'TASK_TO_DELIVERABLE'
AND obj1.object_type_from = 'PA_TASKS'
AND obj1.object_type_to = 'PA_DELIVERABLES'
and nvl(PA_PROGRESS_UTILS.published_dlv_prog_exists(c_project_id , obj1.object_id_to2),'N') = 'Y');
PA_DEBUG.set_curr_function( p_function => 'DELETE_DLV_ASSOCIATIONS',
p_debug_mode => p_debug_mode );
pa_debug.g_err_stage:= 'Inside DELETE_DLV_TASK_ASSOCIATION ';
pa_debug.g_err_stage:= 'Delete the associations';
DELETE FROM pa_object_relationships
WHERE object_type_from = 'PA_TASKS'
AND object_type_to = 'PA_DELIVERABLES'
AND relationship_type = 'A'
AND relationship_subtype = 'TASK_TO_DELIVERABLE'
AND object_id_from2 in (SELECT proj_element_id
FROM pa_proj_elements
WHERE project_id = p_project_id ) ;
pa_debug.g_err_stage:= 'Exiting DELETE_DLV_ASSOCIATIONS' ;
pa_debug.g_err_stage := 'inside invalid arg exception of DELETE_DLV_ASSOCIATIONS';
,p_procedure_name => 'DELETE_DLV_ASSOCIATIONS');
pa_debug.write('DELETE_DLV_ASSOCIATIONS: ' || g_module_name,pa_debug.g_err_stage,5);
END DELETE_DLV_ASSOCIATIONS ;
SELECT SUBSTR(p_prefix||ppe.element_number,1,100)
,SUBSTR(p_prefix||ppe.name,1,240)
,ppe.manager_person_id
,ppe.carrying_out_organization_id
,ppe.progress_weight
,ppe.pm_source_reference
,ppe.pm_source_code
,ppe.description
,ppe.attribute_category
,ppe.attribute1
,ppe.attribute2
,ppe.attribute3
,ppe.attribute4
,ppe.attribute5
,ppe.attribute6
,ppe.attribute7
,ppe.attribute8
,ppe.attribute9
,ppe.attribute10
,ppe.attribute11
,ppe.attribute12
,ppe.attribute13
,ppe.attribute14
,ppe.proj_element_id --source is maintianed in attribute15 column.
,ppe.proj_element_id --to populate the source
,pev.element_version_id -- Source element version id
,ppe.type_id
,pvs.scheduled_finish_date
,ptt.initial_status_code -- Bug#3515852
FROM pa_proj_elements ppe
,pa_proj_elem_ver_schedule pvs
,pa_proj_element_versions pev
,pa_task_types ptt
WHERE ppe.proj_element_id = c_proj_element_id
AND ppe.object_type = 'PA_DELIVERABLES'
AND pev.proj_element_id = c_proj_element_id
AND pev.object_type = 'PA_DELIVERABLES'
AND pvs.proj_element_id = c_proj_element_id
AND pvs.project_id = ppe.project_id
AND ptt.task_type_id = ppe.type_id -- Bug#3515852
AND ptt.object_type = 'PA_DLVR_TYPES'; -- Bug#3515852
SELECT ppe.element_number
,ppe.name
,ppe.manager_person_id
,ppe.carrying_out_organization_id
,ppe.progress_weight
,ppe.pm_source_reference
,ppe.pm_source_code
,ppe.description
,ppe.attribute_category
,ppe.attribute1
,ppe.attribute2
,ppe.attribute3
,ppe.attribute4
,ppe.attribute5
,ppe.attribute6
,ppe.attribute7
,ppe.attribute8
,ppe.attribute9
,ppe.attribute10
,ppe.attribute11
,ppe.attribute12
,ppe.attribute13
,ppe.attribute14
,ppe.proj_element_id --source is maintianed in attribute15 column.
,ppe.proj_element_id --to populate the source
,pev.element_version_id -- Source element version id
,ppe.type_id
,pvs.scheduled_finish_date
,ptt.initial_status_code -- Bug#3515852
FROM pa_proj_elements ppe
,pa_proj_elem_ver_schedule pvs
,pa_proj_element_versions pev
,pa_task_types ptt
WHERE ppe.project_id = p_source_project_id
AND ppe.object_type = 'PA_DELIVERABLES'
AND pev.proj_element_id = ppe.proj_element_id
AND pev.project_id = p_source_project_id
AND ppe.project_id = pev.project_id -- Added for perf bug# 3964586
AND pev.object_type = 'PA_DELIVERABLES'
AND ppe.project_id=pvs.project_id -- Added for perf bug# 3964586
AND ppe.proj_element_id = pvs.proj_element_id
AND pev.element_version_id=pvs.element_version_id -- Added for perf bug# 3964586
AND pvs.project_id = p_source_project_id
AND ptt.task_type_id = ppe.type_id -- Bug#3515852
AND ptt.object_type = 'PA_DLVR_TYPES'; -- Bug#3515852
SELECT ppe.proj_element_id
,ppe.element_version_id
FROM pa_proj_elem_ver_structure ppe
,pa_proj_structure_types pst
,pa_structure_types sty
WHERE ppe.project_id = p_target_project_id
AND ppe.proj_element_id = pst.proj_element_id
AND pst.structure_type_id = sty.structure_type_id
AND sty.structure_type = 'DELIVERABLE'
AND sty.structure_type_class_code = 'DELIVERABLE'
;
SELECT ppe.proj_element_id
,pev.element_version_id
FROM pa_proj_structure_types pst
,pa_structure_types sty
,pa_proj_elements ppe
,pa_proj_element_versions pev
WHERE ppe.project_id = p_target_project_id
AND ppe.object_type = 'PA_STRUCTURES'
AND pev.proj_element_id = ppe.proj_element_id
AND pev.project_id = ppe.project_id
AND ppe.object_type = pev.object_type
AND ppe.proj_element_id = pst.proj_element_id
AND pst.structure_type_id = sty.structure_type_id
AND sty.structure_type = 'DELIVERABLE'
AND sty.structure_type_class_code = 'DELIVERABLE' ;
SELECT pa.project_type
,pa.project_status_code
,project_system_status_code
,segment1 -- 3671408 added column to retrieve project number
FROM PA_PROJECT_STATUSES pps,
PA_PROJECTS_ALL pa
WHERE pa.PROJECT_ID = p_target_project_id
AND pps.PROJECT_STATUS_CODE = pa.PROJECT_STATUS_CODE ;
SELECT meaning
INTO l_suffix
FROM pa_lookups
WHERE lookup_type = 'PA_STRUCTURE_TYPE_CLASS'
AND lookup_code = 'DELIVERABLE';
SELECT name
INTO l_project_name
FROM pa_projects_all
WHERE project_id = p_target_project_id ;
SELECT pa_tasks_s.nextval
,pa_proj_element_versions_s.nextval
INTO l_parent_structure_id
,l_parent_structure_version_id
FROM dual ;
INSERT INTO PA_PROJ_ELEMENTS
( proj_element_id
,project_id
,object_type
,element_number
,name
,status_code
,creation_date
,created_by
,last_update_date
,last_updated_by
,description
,pm_source_reference
,pm_source_code
,manager_person_id
,carrying_out_organization_id
,record_version_number
,last_update_login
,parent_structure_id
,source_object_id
,source_object_type
)
SELECT l_parent_structure_id
,p_target_project_id
,ppe.object_type
,ppe.element_number
,l_name
,ppe.status_code
,sysdate
,fnd_global.user_id
,sysdate
,fnd_global.user_id
,ppe.description
,ppe.pm_source_reference
,ppe.pm_source_code
,ppe.manager_person_id
,ppe.carrying_out_organization_id
,1
,fnd_global.login_id
,l_parent_structure_id
,p_target_project_id
,'PA_PROJECTS'
FROM pa_proj_elements ppe,
pa_proj_structure_types pst
WHERE ppe.object_type = 'PA_STRUCTURES'
AND ppe.project_id = p_source_project_id
AND pst.proj_element_id = ppe.proj_element_id
AND pst.structure_type_id = 8 ;--For Deliverable
INSERT INTO PA_PROJ_ELEMENT_VERSIONS
( element_version_id
,proj_element_id
,object_type
,project_id
,parent_structure_version_id
,creation_date
,created_by
,last_update_date
,last_updated_by
,record_version_number
,last_update_login
,source_object_id
,source_object_type
)
VALUES
(l_parent_structure_version_id
,l_parent_structure_id
,'PA_STRUCTURES'
,p_target_project_id
,l_parent_structure_version_id
,SYSDATE
,fnd_global.user_id
,SYSDATE
,fnd_global.user_id
,1
,fnd_global.login_id
,p_target_project_id,
'PA_PROJECTS'
) ;
INSERT INTO PA_PROJ_STRUCTURE_TYPES
( proj_structure_type_id
,proj_element_id
,structure_type_id
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,record_version_number
)
VALUES
(pa_proj_structure_types_s.nextval
,l_parent_structure_id
,8
,SYSDATE
,fnd_global.user_id
,SYSDATE
,fnd_global.user_id
,fnd_global.login_id
,1
) ;
INSERT INTO PA_PROJ_ELEM_VER_STRUCTURE
( pev_structure_id
,element_version_id
,version_number
,name
,project_id
,proj_element_id
,current_flag
,original_flag
,latest_eff_published_flag
,creation_date
,created_by
,last_update_date
,last_updated_by
,record_version_number
,pm_source_code
,pm_source_reference
,source_object_id
,source_object_type)
SELECT
pa_proj_elem_ver_structure_s.nextval
,l_parent_structure_version_id
,ppe.element_number
,l_name
,p_target_project_id
,l_parent_structure_id
,ver.current_flag
,ver.original_flag
,ver.latest_eff_published_flag
,sysdate
,fnd_global.user_id
,sysdate
,fnd_global.user_id
,1
,ppe.pm_source_code
,ppe.pm_source_reference
,p_target_project_id
,'PA_PROJECTS'
FROM pa_proj_elem_ver_structure ver
,pa_proj_elements ppe
,pa_proj_structure_types pst
WHERE ver.project_id = p_source_project_id
AND ppe.proj_element_id = ver.proj_element_id
AND ppe.object_type = 'PA_STRUCTURES'
AND ppe.proj_element_id = pst.proj_element_id
AND pst.structure_type_id = 8 ;
INSERT INTO PA_PROJ_ELEMENTS
( proj_element_id
,project_id
,object_type
,element_number
,name
,status_code
,creation_date
,created_by
,last_update_date
,last_updated_by
,description
,pm_source_reference
,pm_source_code
,manager_person_id
,carrying_out_organization_id
,record_version_number
,last_update_login
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,parent_structure_id
,type_id
,progress_weight
,source_object_id
,source_object_type
)
VALUES
( PA_TASKS_S.NEXTVAL
,p_target_project_id
,'PA_DELIVERABLES'
,l_element_number_tbl(i)
,l_name_tbl(i)
,l_status_code_tbl(i) -- Bug#3515852 'DLVR_NOT_STARTED'
,sysdate
,fnd_global.user_id
,sysdate
,fnd_global.user_id
,l_description_tbl(i)
,l_pm_source_reference_tbl(i)
,l_pm_source_code_tbl(i)
,l_manager_person_id_tbl(i)
,l_carrying_out_org_id_tbl(i)
,1
,fnd_global.login_id
,l_attribute_category_tbl(i)
,l_attribute1_tbl(i)
,l_attribute2_tbl(i)
,l_attribute3_tbl(i)
,l_attribute4_tbl(i)
,l_attribute5_tbl(i)
,l_attribute6_tbl(i)
,l_attribute7_tbl(i)
,l_attribute8_tbl(i)
,l_attribute9_tbl(i)
,l_attribute10_tbl(i)
,l_attribute11_tbl(i)
,l_attribute12_tbl(i)
,l_attribute13_tbl(i)
,l_attribute14_tbl(i)
,l_attribute15_tbl(i)
,l_parent_structure_id
,l_type_id_tbl(i)
,l_progress_weight_tbl(i)
,p_target_project_id
,'PA_PROJECTS'
)
RETURNING proj_element_id
BULK COLLECT INTO l_proj_element_id_tbl ;
INSERT INTO PA_PROJ_ELEMENT_VERSIONS
( element_version_id
,proj_element_id
,object_type
,project_id
,parent_structure_version_id
,creation_date
,created_by
,last_update_date
,last_updated_by
,record_version_number
,last_update_login
,source_object_id
,source_object_type
)
VALUES
(pa_proj_element_versions_s.nextval
,l_proj_element_id_tbl(i)
,'PA_DELIVERABLES'
,p_target_project_id
,l_parent_structure_version_id
,SYSDATE
,fnd_global.user_id
,SYSDATE
,fnd_global.user_id
,1
,fnd_global.login_id
,p_target_project_id
,'PA_PROJECTS'
)
RETURNING element_version_id
BULK COLLECT INTO l_target_element_ver_id_tbl ;
INSERT INTO PA_PROJ_ELEM_VER_SCHEDULE(
pev_schedule_id
,element_version_id
,project_id
,proj_element_id
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,scheduled_finish_date
,actual_finish_date
,record_version_number
,source_object_id
,source_object_type
)
VALUES
(
pa_proj_elem_ver_schedule_s.nextval
,l_target_element_ver_id_tbl(i)
,p_target_project_id
,l_proj_element_id_tbl(i)
,SYSDATE
,fnd_global.user_id
,SYSDATE
,fnd_global.user_id
,fnd_global.login_id
,decode(p_calling_context,'COPY_PROJECT', -- 3493612
PA_DELIVERABLE_UTILS.GET_ADJUSTED_DATES
(p_target_project_id
,l_due_date_tbl(i)
,p_delta
), l_due_date_tbl(i))
,null
,1
,p_target_project_id
,'PA_PROJECTS'
) ;
INSERT INTO PA_OBJECT_RELATIONSHIPS(
object_relationship_id
,object_type_from
,object_id_from1
,object_type_to
,object_id_to1
,relationship_type
,created_by
,creation_date
,last_updated_by
,last_update_date
,object_id_from2
,object_id_to2
,relationship_subtype
,record_version_number
,last_update_login
)
VALUES
(
pa_object_relationships_s.nextval
,'PA_STRUCTURES'
,l_parent_structure_version_id
,'PA_DELIVERABLES'
,l_target_element_ver_id_tbl(i)
,'S'
,fnd_global.user_id
,SYSDATE
,fnd_global.user_id
,SYSDATE
,l_parent_structure_id
,l_proj_element_id_tbl(i)
,'STRUCTURE_TO_DELIVERABLE'
,1
,fnd_global.login_id
) ;
INSERT INTO PA_OBJECT_RELATIONSHIPS(
object_relationship_id
,object_type_from
,object_id_from1
,object_type_to
,object_id_to1
,relationship_type
,created_by
,creation_date
,last_updated_by
,last_update_date
,object_id_from2
,object_id_to2
,relationship_subtype
,record_version_number
,last_update_login
)
SELECT
pa_object_relationships_s.nextval
,'PA_TASKS'
,null
,'PA_DELIVERABLES'
,dlv2.element_version_id
,'A'
,fnd_global.user_id
,SYSDATE
,fnd_global.user_id
,SYSDATE
,tsk1.proj_element_id
,dlv1.proj_element_id
,'TASK_TO_DELIVERABLE'
,1
,fnd_global.login_id
FROM pa_proj_elements tsk1
,pa_proj_elements dlv1
,pa_proj_element_versions dlv2
,pa_object_relationships obj
WHERE dlv1.project_id = p_target_project_id
AND dlv1.object_type = 'PA_DELIVERABLES'
AND dlv1.attribute15 = obj.object_id_to2
AND obj.relationship_type = 'A'
AND obj.relationship_subtype = 'TASK_TO_DELIVERABLE'
AND obj.object_type_from = 'PA_TASKS'
AND obj.object_type_to = 'PA_DELIVERABLES'
AND tsk1.project_id = p_target_project_id
AND tsk1.attribute15 = obj.object_id_from2
AND tsk1.object_type = 'PA_TASKS'
AND dlv1.proj_element_id = dlv2.proj_element_id ;
INSERT INTO PA_OBJECT_RELATIONSHIPS(
object_relationship_id
,object_type_from
,object_id_from1
,object_type_to
,object_id_to1
,relationship_type
,created_by
,creation_date
,last_updated_by
,last_update_date
,object_id_from2
,object_id_to2
,relationship_subtype
,record_version_number
,last_update_login
)
SELECT
pa_object_relationships_s.nextval
,'PA_TASKS'
,obj.object_id_from1
,'PA_DELIVERABLES'
,l_target_element_ver_id_tbl(i)
,'A'
,fnd_global.user_id
,SYSDATE
,fnd_global.user_id
,SYSDATE
,obj.object_id_from2
,l_proj_element_id_tbl(i)
,'TASK_TO_DELIVERABLE'
,1
,fnd_global.login_id
FROM pa_object_relationships obj
WHERE obj.object_id_to2 = p_dlv_element_id_tbl(i)
AND obj.object_type_to = 'PA_DELIVERABLES'
AND obj.object_type_from = 'PA_TASKS'
AND obj.relationship_type = 'A'
AND obj.relationship_subtype = 'TASK_TO_DELIVERABLE' ;
,X_last_update_login => fnd_global.login_id
);
,X_last_update_login => fnd_global.login_id
);