[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT report_status_code
FROM pa_progress_report_vers
WHERE version_id = p_version_id;
SELECT meaning FROM pa_lookups
WHERE lookup_type = 'PA_PAGE_TYPES'
AND lookup_code = 'PPR';
pa_progress_report_pkg.INSERT_progress_report_VAL_ROW (
P_VERSION_ID,
P_REGION_SOURCE_TYPE,
P_REGION_CODE,
P_RECORD_SEQUENCE,
P_ATTRIBUTE1 ,
P_ATTRIBUTE2 ,
P_ATTRIBUTE3 ,
P_ATTRIBUTE4 ,
P_ATTRIBUTE5 ,
P_ATTRIBUTE6 ,
P_ATTRIBUTE7 ,
P_ATTRIBUTE8 ,
P_ATTRIBUTE9 ,
P_ATTRIBUTE10 ,
P_ATTRIBUTE11 ,
P_ATTRIBUTE12 ,
P_ATTRIBUTE13 ,
P_ATTRIBUTE14 ,
P_ATTRIBUTE15 ,
P_ATTRIBUTE16 ,
P_ATTRIBUTE17 ,
P_ATTRIBUTE18 ,
P_ATTRIBUTE19 ,
P_ATTRIBUTE20 ,
P_UDS_ATTRIBUTE_CATEGORY ,
P_UDS_ATTRIBUTE1 ,
P_UDS_ATTRIBUTE2 ,
P_UDS_ATTRIBUTE3 ,
P_UDS_ATTRIBUTE4 ,
P_UDS_ATTRIBUTE5 ,
P_UDS_ATTRIBUTE6 ,
P_UDS_ATTRIBUTE7 ,
P_UDS_ATTRIBUTE8 ,
P_UDS_ATTRIBUTE9 ,
P_UDS_ATTRIBUTE10 ,
P_UDS_ATTRIBUTE11 ,
P_UDS_ATTRIBUTE12 ,
P_UDS_ATTRIBUTE13 ,
P_UDS_ATTRIBUTE14 ,
P_UDS_ATTRIBUTE15 ,
P_UDS_ATTRIBUTE16 ,
P_UDS_ATTRIBUTE17 ,
P_UDS_ATTRIBUTE18 ,
P_UDS_ATTRIBUTE19 ,
P_UDS_ATTRIBUTE20 ,
x_return_status,
x_msg_count,
x_msg_data
) ;
--update_project_perccomplete(p_version_id,
-- x_return_status,
-- x_msg_count,
-- x_msg_data);
PROCEDURE Update_REPORT_REGION
(
p_api_version IN NUMBER := 1.0,
p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
p_commit IN VARCHAR2 := FND_API.g_false,
p_validate_only IN VARCHAR2 := FND_API.g_true,
p_max_msg_count IN NUMBER := FND_API.g_miss_num,
P_VERSION_ID in NUMBER,
P_REGION_SOURCE_TYPE in VARCHAR2,
P_REGION_CODE in VARCHAR2,
P_RECORD_SEQUENCE in NUMBER,
P_RECORD_VERSION_NUMBER in NUMBER,
P_ATTRIBUTE1 in VARCHAR2,
P_ATTRIBUTE2 in VARCHAR2,
P_ATTRIBUTE3 in VARCHAR2,
P_ATTRIBUTE4 in VARCHAR2,
P_ATTRIBUTE5 in VARCHAR2,
P_ATTRIBUTE6 in VARCHAR2,
P_ATTRIBUTE7 in VARCHAR2,
P_ATTRIBUTE8 in VARCHAR2,
P_ATTRIBUTE9 in VARCHAR2,
P_ATTRIBUTE10 in VARCHAR2,
P_ATTRIBUTE11 in VARCHAR2,
P_ATTRIBUTE12 in VARCHAR2,
P_ATTRIBUTE13 in VARCHAR2,
P_ATTRIBUTE14 in VARCHAR2,
P_ATTRIBUTE15 in VARCHAR2,
P_ATTRIBUTE16 in VARCHAR2,
P_ATTRIBUTE17 in VARCHAR2,
P_ATTRIBUTE18 in VARCHAR2,
P_ATTRIBUTE19 in VARCHAR2,
P_ATTRIBUTE20 in VARCHAR2,
P_UDS_ATTRIBUTE_CATEGORY in VARCHAR2 ,
P_UDS_ATTRIBUTE1 in VARCHAR2 ,
P_UDS_ATTRIBUTE2 in VARCHAR2 ,
P_UDS_ATTRIBUTE3 in VARCHAR2 ,
P_UDS_ATTRIBUTE4 in VARCHAR2 ,
P_UDS_ATTRIBUTE5 in VARCHAR2 ,
P_UDS_ATTRIBUTE6 in VARCHAR2 ,
P_UDS_ATTRIBUTE7 in VARCHAR2 ,
P_UDS_ATTRIBUTE8 in VARCHAR2 ,
P_UDS_ATTRIBUTE9 in VARCHAR2 ,
P_UDS_ATTRIBUTE10 in VARCHAR2 ,
P_UDS_ATTRIBUTE11 in VARCHAR2 ,
P_UDS_ATTRIBUTE12 in VARCHAR2 ,
P_UDS_ATTRIBUTE13 in VARCHAR2 ,
P_UDS_ATTRIBUTE14 in VARCHAR2 ,
P_UDS_ATTRIBUTE15 in VARCHAR2 ,
P_UDS_ATTRIBUTE16 in VARCHAR2 ,
P_UDS_ATTRIBUTE17 in VARCHAR2 ,
P_UDS_ATTRIBUTE18 in VARCHAR2 ,
P_UDS_ATTRIBUTE19 in VARCHAR2 ,
P_UDS_ATTRIBUTE20 in VARCHAR2 ,
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_rowid ROWID;
SELECT ROWID
FROM PA_Progress_report_VALS
WHERE version_id = p_version_id
AND region_source_type = p_region_source_type
AND region_code = p_region_code
AND record_sequence = p_record_sequence
AND record_version_number = p_record_version_number;
SELECT report_status_code
FROM pa_progress_report_vers
WHERE version_id = p_version_id;
SELECT meaning FROM pa_lookups
WHERE lookup_type = 'PA_PAGE_TYPES'
AND lookup_code = 'PPR';
PA_DEBUG.init_err_stack('PA_PROGRESS_REPORT_PVT.Update_Report_Region');
SAVEPOINT Update_Report_Region;
PA_PROGRESS_REPORT_PKG.update_progress_report_val_row
(
P_VERSION_ID ,
P_REGION_SOURCE_TYPE,
P_REGION_CODE ,
P_RECORD_SEQUENCE ,
P_RECORD_VERSION_NUMBER ,
P_ATTRIBUTE1 ,
P_ATTRIBUTE2 ,
P_ATTRIBUTE3 ,
P_ATTRIBUTE4 ,
P_ATTRIBUTE5 ,
P_ATTRIBUTE6 ,
P_ATTRIBUTE7 ,
P_ATTRIBUTE8 ,
P_ATTRIBUTE9 ,
P_ATTRIBUTE10 ,
P_ATTRIBUTE11 ,
P_ATTRIBUTE12 ,
P_ATTRIBUTE13 ,
P_ATTRIBUTE14 ,
P_ATTRIBUTE15 ,
P_ATTRIBUTE16 ,
P_ATTRIBUTE17 ,
P_ATTRIBUTE18 ,
P_ATTRIBUTE19 ,
P_ATTRIBUTE20 ,
P_UDS_ATTRIBUTE_CATEGORY ,
P_UDS_ATTRIBUTE1 ,
P_UDS_ATTRIBUTE2 ,
P_UDS_ATTRIBUTE3 ,
P_UDS_ATTRIBUTE4 ,
P_UDS_ATTRIBUTE5 ,
P_UDS_ATTRIBUTE6 ,
P_UDS_ATTRIBUTE7 ,
P_UDS_ATTRIBUTE8 ,
P_UDS_ATTRIBUTE9 ,
P_UDS_ATTRIBUTE10 ,
P_UDS_ATTRIBUTE11 ,
P_UDS_ATTRIBUTE12 ,
P_UDS_ATTRIBUTE13 ,
P_UDS_ATTRIBUTE14 ,
P_UDS_ATTRIBUTE15 ,
P_UDS_ATTRIBUTE16 ,
P_UDS_ATTRIBUTE17 ,
P_UDS_ATTRIBUTE18 ,
P_UDS_ATTRIBUTE19 ,
P_UDS_ATTRIBUTE20 ,
x_return_status,
x_msg_count,
x_msg_data
);
--update_project_perccomplete(p_version_id,
-- x_return_status,
-- x_msg_count,
-- x_msg_data);
UPDATE pa_progress_report_vers
SET summary_version_number = summary_version_number +1
WHERE version_id = p_version_id;
ROLLBACK TO update_report_region;
FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_PROGRESS_REPORT_PVT.Update_Progress_Report'
,p_procedure_name => PA_DEBUG.G_Err_Stack );
END update_report_region;
PROCEDURE Delete_Report_Region
(
p_api_version IN NUMBER := 1.0,
p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
p_commit IN VARCHAR2 := FND_API.g_false,
p_validate_only IN VARCHAR2 := FND_API.g_true,
p_max_msg_count IN NUMBER := FND_API.g_miss_num,
p_version_id IN number,
P_REGION_SOURCE_TYPE in VARCHAR2,
P_REGION_CODE in VARCHAR2,
P_RECORD_SEQUENCE in NUMBER,
p_record_version_number 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_rowid ROWID;
SELECT ROWID
FROM PA_progress_report_VALS
WHERE version_id = p_version_id
AND region_source_type = p_region_source_type
AND region_code = p_region_code
AND record_sequence = p_record_sequence
AND record_version_number = p_record_version_number;
SELECT report_status_code
FROM pa_progress_report_vers
WHERE version_id = p_version_id;
SELECT meaning FROM pa_lookups
WHERE lookup_type = 'PA_PAGE_TYPES'
AND lookup_code = 'PPR';
PA_DEBUG.init_err_stack('PA_PROGRESS_REPORT_PVT.Update_Report_Region');
SAVEPOINT Delete_Progress_Report;
pa_progress_report_pkg.delete_progress_report_region(
P_VERSION_ID,
P_REGION_SOURCE_TYPE,
P_REGION_CODE,
x_return_status,
x_msg_count,
x_msg_data
);
pa_progress_report_pkg.DELETE_progress_report_VAL_ROW (
P_VERSION_ID,
P_REGION_SOURCE_TYPE,
P_REGION_CODE,
P_RECORD_SEQUENCE,
P_RECORD_VERSION_NUMBER,
x_return_status,
x_msg_count,
x_msg_data);
ROLLBACK TO Delete_Progress_Report;
FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_PROGRESS_Report_PVT.Delete_Progress_Report'
,p_procedure_name => PA_DEBUG.G_Err_Stack );
END delete_report_region;
SELECT ROWID
FROM pa_progress_report_vers
WHERE version_id = p_version_id
AND report_status_code = 'PROGRESS_REPORT_PUBLISHED';
SELECT meaning FROM pa_lookups
WHERE lookup_type = 'PA_PAGE_TYPES'
AND lookup_code = 'PPR';
CURSOR update_last_published_rep is
SELECT version_id,record_version_number,summary_version_number
FROM pa_progress_report_vers p1
WHERE
p1.object_id = l_object_id
AND p1.object_type = l_object_type
and p1.report_type_id = l_report_type_id
AND p1.report_status_code = 'PROGRESS_REPORT_PUBLISHED'
AND p1.version_id =
(
SELECT MAX(version_id)
FROM pa_progress_report_vers
WHERE
object_id = l_object_id
AND object_type = l_object_type
and report_type_id = l_report_type_id
AND report_status_code = 'PROGRESS_REPORT_PUBLISHED'
and current_flag <> 'Y'
AND report_end_Date =
(SELECT max(report_end_date)
FROM pa_progress_report_vers
WHERE
object_id = l_object_id
AND object_type = l_object_type
and report_Type_id = l_report_type_id
AND report_status_code = 'PROGRESS_REPORT_PUBLISHED'
and current_flag <> 'Y'));
select object_id, object_Type, current_flag, report_Type_id
from pa_progress_report_vers
where version_id = p_version_id;
/* pa_progress_report_pkg.UPDATE_PROGRESS_REPORT_VER_ROW
(
p_version_id,
NULL,
NULL,
NULL,
NULL,
'PROGRESS_REPORT_CANCELED',
NULL ,
NULL ,
NULL,
NULL ,
NULL ,
NULL,
NULL,
p_cancel_comments,
sysdate,
p_record_version_number,
NULL,
x_return_status ,
x_msg_count ,
x_msg_data
);*/
open update_last_published_rep;
fetch update_last_published_rep into l_version_id,l_RECORD_VERSION_NUMBER,l_summary_version_number;
IF update_last_published_rep%found then
pa_progress_report_pkg.UPDATE_PROGRESS_REPORT_VER_ROW
(
l_version_id,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
'Y',
null,
null,
null,
l_RECORD_VERSION_NUMBER ,
l_summary_version_number ,
l_report_type_id,
x_return_status ,
x_msg_count ,
x_msg_data
);
close update_last_published_rep;
SELECT ROWID
FROM pa_progress_report_vers
WHERE version_id = p_version_id
AND report_status_code = 'PROGRESS_REPORT_SUBMITTED';
SELECT meaning FROM pa_lookups
WHERE lookup_type = 'PA_PAGE_TYPES'
AND lookup_code = 'PPR';
pa_progress_report_pkg.UPDATE_PROGRESS_REPORT_VER_ROW
(
p_version_id,
NULL,
NULL,
NULL,
NULL,
'PROGRESS_REPORT_APPROVED',
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
p_record_version_number,
null,
x_return_status ,
x_msg_count ,
x_msg_data
);*/
SELECT ROWID
FROM pa_progress_report_vers
WHERE version_id = p_version_id
AND report_status_code = 'PROGRESS_REPORT_SUBMITTED';
SELECT meaning FROM pa_lookups
WHERE lookup_type = 'PA_PAGE_TYPES'
AND lookup_code = 'PPR';
pa_progress_report_pkg.UPDATE_PROGRESS_REPORT_VER_ROW
(
p_version_id,
NULL,
NULL,
NULL,
NULL,
'PROGRESS_REPORT_REJECTED',
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
p_record_version_number,
NULL,
x_return_status ,
x_msg_count ,
x_msg_data
);*/
PROCEDURE update_report_details
(
p_api_version IN NUMBER := 1.0,
p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
p_commit IN VARCHAR2 := FND_API.g_false,
p_validate_only IN VARCHAR2 := FND_API.g_true,
p_max_msg_count IN NUMBER := FND_API.g_miss_num,
p_version_id IN NUMBER := NULL,
p_report_start_date IN DATE:= NULL,
p_report_end_date IN DATE:= NULL,
p_reported_by IN NUMBER:= NULL,
p_reported_by_name IN VARCHAR2:= NULL,
p_progress_status IN VARCHAR2:= NULL,
p_overview IN VARCHAR2:= NULL,
p_record_version_number IN NUMBER := NULL,
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_reported_by NUMBER;
SELECT Decode(report_status_code, 'PROGRESS_REPORT_WORKING','Y', 'N')
FROM pa_progress_report_vers
WHERE version_id = p_version_id;
SELECT person_id
FROM pa_employees
WHERE full_name = p_reported_by_name
AND active = '*';
SELECT meaning FROM pa_lookups
WHERE lookup_type = 'PA_PAGE_TYPES'
AND lookup_code = 'PPR';
PA_DEBUG.init_err_stack('PA_PROGRESS_REPORT_PVT.update_report_details');
SAVEPOINT update_report_details;
,p_msg_name => 'PA_UPDATE_REPORT_INV');
pa_progress_report_pkg.UPDATE_PROGRESS_REPORT_VER_ROW
(
p_version_id,
NULL,
NULL,
NULL,
NULL,
NULL,
Trunc(p_report_start_date) ,
Trunc(p_report_end_date) ,
l_reported_by,
p_progress_status ,
p_overview ,
'N',
NULL,
NULL,
NULL,
p_record_version_number,
NULL,
null,
x_return_status ,
x_msg_count ,
x_msg_data
);
ROLLBACK TO update_report_details;
FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_PROGRESS_REPORT_PVT.update_report_details'
,p_procedure_name => PA_DEBUG.G_Err_Stack );
END update_report_details;
SELECT page_id, approval_required
FROM pa_object_page_layouts
WHERE
object_page_layout_id = p_object_page_layout_id;
SELECT page_id
FROM pa_page_layouts
WHERE page_name = p_page_name
AND page_type_code = p_page_type_code;
SELECT ROWID
FROM pa_report_types
WHERE
report_type_id = p_report_type_id;
IF pa_progress_report_utils.is_delete_page_layout_ok(p_page_type_code, p_object_type, p_object_id, p_report_Type_id ) <> 'Y' THEN
-- we have to quit,
PA_UTILS.Add_Message( p_app_short_name => 'PA'
,p_msg_name => 'PA_REPORT_TYPE_REMOVE_INV');
DELETE FROM pa_object_page_layouts
WHERE object_id = p_object_id AND object_type = p_object_type
AND page_type_code = p_page_type_code
and nvl(report_Type_id,-99) = nvl(p_report_type_id,-99);
PA_OBJECT_DIST_LISTS_PVT.DELETE_ASSOC_DIST_LISTS(p_validate_only => 'F',
P_OBJECT_TYPE => 'PA_OBJECT_PAGE_LAYOUT',
P_OBJECT_ID => p_object_page_layout_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
pa_proj_stat_actset.delete_action_set (p_object_id => p_object_page_layout_id
,p_validate_only => 'F'
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
SELECT resource_source_id, resource_type_id
INTO l_approver_source_id, l_approver_source_type
FROM pa_people_lov_v
WHERE name = p_approver_source_name;
-- we will insert into pa_object_page_layouts
IF (p_validate_only <>FND_API.g_true AND x_return_status = FND_API.g_ret_sts_success) THEN
pa_progress_report_pkg.insert_object_page_layout_row
(
P_OBJECT_ID ,
P_OBJECT_TYPE ,
L_PAGE_ID ,
P_PAGE_TYPE_CODE ,
P_APPROVAL_REQUIRED ,
--P_AUTO_PUBLISH ,
P_REPORT_CYCLE_ID ,
P_REPORT_OFFSET_DAYS ,
-- by msundare request, store end date in the table
l_report_end_date ,
P_REMINDER_DAYS ,
P_REMINDER_DAYS_TYPE ,
P_INITIAL_PROGRESS_STATUS,
P_FINAL_PROGRESS_STATUS,
P_ROLLUP_PROGRESS_STATUS,
p_report_type_id,
l_approver_source_id,
l_approver_source_type,
p_effective_from,
p_effective_to,
p_function_name,
x_object_page_layout_id ,
x_return_status ,
x_msg_count ,
x_msg_data
);
-- we will insert into pa_object_page_layouts
IF (p_validate_only <>FND_API.g_true AND x_return_status = FND_API.g_ret_sts_success) THEN
pa_progress_report_pkg.update_object_page_layout_row
(
P_OBJECT_ID ,
P_OBJECT_TYPE ,
L_PAGE_ID ,
P_PAGE_TYPE_CODE ,
P_APPROVAL_REQUIRED ,
--P_AUTO_PUBLISH ,
P_REPORT_CYCLE_ID ,
P_REPORT_OFFSET_DAYS ,
l_report_end_date ,
P_REMINDER_DAYS ,
P_REMINDER_DAYS_TYPE ,
P_INITIAL_PROGRESS_STATUS,
P_FINAL_PROGRESS_STATUS,
P_ROLLUP_PROGRESS_STATUS,
p_report_type_id,
l_approver_source_id,
l_approver_source_type,
p_effective_from,
p_effective_to,
p_object_page_layout_id,
p_record_version_number,
p_function_name,
x_return_status ,
x_msg_count ,
x_msg_data
);
PA_PROJ_STAT_ACTSET.update_action_set(
p_action_set_id => p_action_set_id
,p_object_id => l_object_page_layout_id
,p_commit => p_commit
,p_validate_only => p_validate_only
,p_init_msg_list => 'F'
,x_new_action_set_id => l_new_action_set_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
PA_TASK_PROG_ACTSET.update_action_set(
p_action_set_id => p_action_set_id
,p_object_id => p_object_id
,p_commit => p_commit
,p_validate_only => p_validate_only
,p_init_msg_list => 'F'
,x_new_action_set_id => l_new_action_set_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
PROCEDURE delete_report
(
p_api_version IN NUMBER := 1.0,
p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
p_commit IN VARCHAR2 := FND_API.g_false,
p_validate_only IN VARCHAR2 := FND_API.g_true,
p_max_msg_count IN NUMBER := FND_API.g_miss_num,
p_version_id IN NUMBER :=NULL,
p_record_version_number IN NUMBER := NULL,
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_rowid ROWID;
SELECT MAX(pwp.item_key), max(pwp.item_type)
from pa_wf_processes pwp, pa_project_statuses pps,
pa_progress_report_vers pprv
where pwp.item_type = pps.WORKFLOW_ITEM_TYPE
and pps.status_type = 'PROGRESS_REPORT'
and pps.project_status_code = 'PROGRESS_REPORT_SUBMITTED'
AND entity_key2 = p_version_id
AND pwp.wf_type_code = 'Progress Report'
AND entity_key1 = pprv.object_id
AND pprv.version_id = p_version_id
AND pprv.object_type = 'PA_PROJECTS';
select 'Y' FROM dual
WHERE exists
(SELECT *
from wf_item_activity_statuses wias, pa_project_statuses pps
WHERE wias.item_type = pps.WORKFLOW_ITEM_TYPE
AND wias.item_key = l_item_key
AND wias.activity_status = 'ACTIVE'
AND pps.status_type = 'PROGRESS_REPORT'
AND pps.project_status_code = 'PROGRESS_REPORT_SUBMITTED');
SELECT meaning FROM pa_lookups
WHERE lookup_type = 'PA_PAGE_TYPES'
AND lookup_code = 'PPR';
SELECT ROWID
FROM pa_progress_report_vers
WHERE version_id = p_version_id
AND report_status_code = 'PROGRESS_REPORT_SUBMITTED';
PA_DEBUG.init_err_stack('PA_PROGRESS_REPORT_PVT.Delete_Report');
SAVEPOINT delete_report;
pa_progress_report_pkg.delete_progress_report_vals
(
p_version_id,
x_return_status ,
x_msg_count ,
x_msg_data
);
-- delete record in pa_progress_report_vers under the p_version_id
pa_progress_report_pkg.delete_progress_report_ver_row
(
p_version_id,
p_record_version_number,
x_return_status ,
x_msg_count ,
x_msg_data
);
ROLLBACK TO delete_report;
FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_PROGRESS_REPORT_PVT.Delete_Report'
,p_procedure_name => PA_DEBUG.G_Err_Stack );
END delete_report;
SELECT 'Y' FROM dual
WHERE exists
(
SELECT p1.approval_required
FROM pa_object_page_layouts p1,
pa_progress_report_vers p2
WHERE
p2.version_id = p_version_id
AND p2.object_id = p1.object_id
AND p2.object_type = p1.object_type
AND (p1.approval_required = 'Y'
OR p1.approval_required = 'A'
)
);
SELECT MAX(pwp.item_key), max(pwp.item_type)
from pa_wf_processes pwp, pa_project_statuses pps,
pa_progress_report_vers pprv
where pwp.item_type = pps.WORKFLOW_ITEM_TYPE
and pps.status_type = 'PROGRESS_REPORT'
and pps.project_status_code = 'PROGRESS_REPORT_SUBMITTED'
AND entity_key2 = p_version_id
AND entity_key1 = pprv.object_id
AND wf_type_code = 'Progress Report'
AND pprv.version_id = p_version_id
AND pprv.object_type = 'PA_PROJECTS';
select 'Y' FROM dual
WHERE exists
(SELECT *
from wf_item_activity_statuses wias, pa_project_statuses pps
WHERE wias.item_type = pps.WORKFLOW_ITEM_TYPE
AND wias.item_key = l_item_key
AND wias.activity_status = 'ACTIVE'
AND pps.status_type = 'PROGRESS_REPORT'
AND pps.project_status_code = 'PROGRESS_REPORT_SUBMITTED');
SELECT meaning FROM pa_lookups
WHERE lookup_type = 'PA_PAGE_TYPES'
AND lookup_code = 'PPR';
pa_progress_report_pkg.UPDATE_PROGRESS_REPORT_VER_ROW
(
p_version_id ,
NULL,
NULL,
NULL,
NULL,
'PROGRESS_REPORT_WORKING',
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
P_RECORD_VERSION_NUMBER ,
NULL,
x_return_status ,
x_msg_count ,
x_msg_data
);*/
SELECT p1.approval_required
FROM pa_object_page_layouts p1,
pa_progress_report_vers p2
WHERE
p2.version_id = l_version_id
AND p2.object_id = p1.object_id
AND p2.object_type = p1.object_type;
SELECT meaning FROM pa_lookups
WHERE lookup_type = 'PA_PAGE_TYPES'
AND lookup_code = 'PPR';
IS SELECT
popl.object_id, popl.object_type, popl.reporting_cycle_id,
popl.report_offset_days, popl.approval_required,
popl.reminder_days, popl.reminder_days_type, popl.record_version_number,popl.pers_function_name,
popl.page_type_code, popl.initial_progress_status, popl.final_progress_status,popl.rollup_progress_status,
popl.report_type_id, popl.approver_source_id, popl.approver_source_type, popl.effective_from,
popl.effective_to, popl.object_page_layout_id
FROM pa_object_page_layouts popl, pa_progress_report_vers pprv
WHERE pprv.version_id = p_version_id
AND popl.object_id = pprv.object_id
AND popl.object_type = pprv.object_type
AND popl.page_type_code = 'PPR'
AND popl.report_type_id = pprv.report_type_id;
pa_progress_report_pkg.UPDATE_PROGRESS_REPORT_VER_ROW
(
p_version_id,
NULL,
NULL,
NULL,
NULL,
'PROGRESS_REPORT_PUBLISHED',
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
SYSDATE,
NULL,
NULL,
--NULL,
P_RECORD_VERSION_NUMBER ,
P_SUMMARY_VERSION_NUMBER ,
x_return_status ,
x_msg_count ,
x_msg_data
);*/
-- update information in other tables
--update_project_perccomplete(p_version_id,
-- x_return_status,
-- x_msg_count,
-- x_msg_data);
--debug_msg('update_object_page_layout_row');
pa_progress_report_pkg.update_object_page_layout_row
(
l_OBJECT_ID ,
l_OBJECT_TYPE ,
null ,
l_page_type_code ,
l_approval_required ,
--P_AUTO_PUBLISH ,
l_report_cycle_id ,
l_report_offset_days,
l_report_end_date ,
l_REMINDER_DAYS ,
l_REMINDER_DAYS_TYPE ,
l_initial_progress_status,
l_final_progress_status,
l_rollup_progress_status,
l_report_type_id,
l_approver_source_id,
l_approver_source_type,
l_effective_from,
l_effective_to,
l_object_page_layout_id,
l_record_version_number,
l_function_name,
x_return_status ,
x_msg_count ,
x_msg_data
);
-- debug_msg('update_object_page_layout_row' || x_return_status);
SELECT object_id
FROM pa_progress_report_vers
WHERE version_id = p_version_id
AND object_type = 'PA_PROJECTS'
AND report_status_code = 'PROGRESS_REPORT_WORKING';
SELECT meaning FROM pa_lookups
WHERE lookup_type = 'PA_PAGE_TYPES'
AND lookup_code = 'PPR';
select
WORKFLOW_ITEM_TYPE,
workflow_process, enable_wf_flag from pa_project_statuses
where status_type = 'PROGRESS_REPORT'
AND project_status_code = 'PROGRESS_REPORT_SUBMITTED';
pa_progress_report_pkg.UPDATE_PROGRESS_REPORT_VER_ROW
(
p_version_id,
NULL,
NULL,
NULL,
NULL,
'PROGRESS_REPORT_SUBMITTED',
NULL,
NULL,
NULL,
NULL,
NULL,
'N',
NULL,
NULL,
NULL,
P_RECORD_VERSION_NUMBER ,
p_summary_version_number ,
null,
x_return_status ,
x_msg_count ,
x_msg_data
);
-- update information in other tables
--update_project_perccomplete(p_version_id,
-- x_return_status,
-- x_msg_count,
-- x_msg_data);
-- update pa_wf_process_table
PA_WORKFLOW_UTILS.Insert_WF_Processes
(p_wf_type_code => 'Progress Report'
-- ,p_item_type => 'PAWFPPRA'
,p_item_type => l_wf_item_type
,p_item_key => l_item_key
,p_entity_key1 => l_project_id
,p_entity_key2 => p_version_id
,p_description => l_wf_process_name
,p_err_code => l_err_code
,p_err_stage => l_err_stage
,p_err_stack => l_err_stack
);
SELECT object_id, report_end_date, report_type_id
FROM pa_progress_report_vers
WHERE version_id = p_version_id
AND object_type = 'PA_PROJECTS';
select enable_wf_flag, workflow_item_type,
workflow_process,wf_success_status_code,
wf_failure_status_code from pa_project_statuses
where project_status_code = p_report_status;
select version_id,report_end_Date ,
record_version_number, summary_version_number
into x_version_id,x_report_end_date,
l_record_version_number, l_summary_version_number
from pa_progress_report_vers
where object_id = l_project_id
and object_Type = 'PA_PROJECTS'
and report_type_id = l_report_Type_id
and page_type_code = 'PPR'
and current_flag = 'Y';
pa_progress_report_pkg.UPDATE_PROGRESS_REPORT_VER_ROW
(
x_version_id,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
'N',
null,
null,
null,
l_RECORD_VERSION_NUMBER ,
l_summary_version_number ,
null,
x_return_status ,
x_msg_count ,
x_msg_data
);
pa_progress_report_pkg.UPDATE_PROGRESS_REPORT_VER_ROW
(
p_version_id,
NULL,
NULL,
NULL,
NULL,
p_report_status,
NULL,
NULL,
NULL,
NULL,
NULL,
l_current_flag,
p_published_date,
p_cancel_comment,
p_cancel_date,
P_RECORD_VERSION_NUMBER ,
p_summary_version_number ,
null,
x_return_status ,
x_msg_count ,
x_msg_data
);
-- update pa_wf_process_table
PA_WORKFLOW_UTILS.Insert_WF_Processes
(p_wf_type_code => 'Progress Report'
-- ,p_item_type => 'PAWFPPRA'
,p_item_type => l_wf_item_type
,p_item_key => l_item_key
,p_entity_key1 => l_project_id
,p_entity_key2 => p_version_id
,p_description => l_wf_process
,p_err_code => l_err_code
,p_err_stage => l_err_stage
,p_err_stack => l_err_stack
);
SELECT popl.page_id, popl.page_type_code, popl.reporting_cycle_id, popl.report_offset_days,popl.effective_from
FROM pa_object_page_layouts popl, pa_page_layouts ppl
WHERE popl.object_id = p_object_id
AND popl.object_type = p_object_type
AND popl.report_type_id = p_report_type_id
AND popl.page_id = ppl.page_id
AND popl.page_type_code = 'PPR';
SELECT rowid
FROM pa_progress_report_vers
WHERE object_id = p_object_id
AND object_type = p_object_type
AND report_type_id = p_report_type_id
AND report_status_code = 'PROGRESS_REPORT_PUBLISHED';
SELECT version_id
FROM pa_progress_report_vers p1
WHERE
p1.object_id = p_object_id
AND p1.object_type = p_object_type
ANd p1.report_type_id = p_report_type_id
AND p1.report_status_code = 'PROGRESS_REPORT_PUBLISHED'
AND ROWNUM =1
AND p1.report_end_date =
(
SELECT MAX(report_end_date)
FROM pa_progress_report_vers
WHERE
object_id = p_object_id
AND object_type = p_object_type
AND report_type_id = p_report_type_id
AND report_status_code = 'PROGRESS_REPORT_PUBLISHED'
); */
SELECT version_id
FROM pa_progress_report_vers p1
WHERE
p1.object_id = p_object_id
AND p1.object_type = p_object_type
ANd p1.report_type_id = p_report_type_id
AND p1.report_status_code = 'PROGRESS_REPORT_PUBLISHED'
AND ROWNUM =1
AND p1.published_date =
(
SELECT MAX(published_date)
FROM pa_progress_report_vers
WHERE
object_id = p_object_id
AND object_type = p_object_type
AND report_type_id = p_report_type_id
AND report_status_code = 'PROGRESS_REPORT_PUBLISHED'
);
SELECT *
FROM pa_progress_report_vals
WHERE version_id = l_last_published_version_id;
SELECT overview, progress_status_code, REPORT_START_DATE
FROM pa_progress_report_vers
WHERE version_id = l_last_published_version_id;
SELECT pplr.*
FROM pa_page_layout_regions pplr
WHERE pplr.page_id = l_page_id;
select RESOURCE_SOURCE_ID
from pa_project_parties where project_id = p_object_id;
IS SELECT MAX(report_end_date)
FROM pa_progress_report_vers
WHERE object_id = p_object_id
AND object_type = p_object_type
AND report_Type_id = p_report_Type_id
;
select usr.employee_id
from
fnd_user usr
WHERE
usr.user_id = fnd_global.user_id
and trunc(sysdate) between USR.START_DATE and nvl(USR.END_DATE, sysdate+1);
select resource_id
from pa_project_parties_v
where user_id = fnd_global.user_id;*/
--debug_msg('*********before insert: reported by ' || To_char(fnd_global.user_id));
pa_progress_report_pkg.insert_PROGRESS_REPORT_ver_row
(
p_object_id,
p_object_type,
l_page_id,
l_page_type,
'PROGRESS_REPORT_WORKING',
Trunc(l_report_start_date) ,
Trunc(l_report_end_date) ,
--fnd_global.user_id ,
l_person_id,
'PROGRESS_STAT_ON_TRACK',
--'PROGRESS_STAT_NOT_STARTED',
l_overview,
'N', --- current_flag
NULL,
NULL,
NULL,
p_report_Type_id,
X_version_id,
x_return_status,
x_msg_count,
x_msg_data
);
-- debug_msg('insert val' || obj_page_value_rec.view_region_code);
pa_progress_report_pkg.insert_progress_report_val_row
(
x_version_id,
obj_page_value_rec.region_source_type,
obj_page_value_rec.region_source_code,
1,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL, -- uds_attribute_category
NULL, -- uds_attribute1
NULL, -- uds_attribute2
NULL, -- uds_attribute3
NULL, -- uds_attribute4
NULL, -- uds_attribute5
NULL, -- uds_attribute6
NULL, -- uds_attribute7
NULL, -- uds_attribute8
NULL, -- uds_attribute9
NULL, -- uds_attribute10
NULL, -- uds_attribute11
NULL, -- uds_attribute12
NULL, -- uds_attribute13
NULL, -- uds_attribute14
NULL, -- uds_attribute15
NULL, -- uds_attribute16
NULL, -- uds_attribute17
NULL, -- uds_attribute18
NULL, -- uds_attribute19
NULL, -- uds_attribute20
x_return_status ,
x_msg_count ,
x_msg_data
);
-- update the overview field
OPEN get_publish_overview;
pa_progress_report_pkg.UPDATE_PROGRESS_REPORT_VER_ROW (
x_version_id,
null,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
l_PROGRESS_STATUS_CODE,
l_overview,
'N',
NULL,
NULL,
NULL,
NULL,
NULL,
p_report_Type_id,
x_return_status,
x_msg_count ,
x_msg_data
) ;
pa_progress_report_pkg.insert_progress_report_val_row
(
x_version_id,
obj_page_value_rec.region_source_type,
obj_page_value_rec.region_code,
obj_page_value_rec.record_sequence,
obj_page_value_rec.ATTRIBUTE1 ,
obj_page_value_rec.ATTRIBUTE2 ,
obj_page_value_rec.ATTRIBUTE3 ,
obj_page_value_rec.ATTRIBUTE4 ,
obj_page_value_rec.ATTRIBUTE5 ,
obj_page_value_rec.ATTRIBUTE6 ,
obj_page_value_rec.ATTRIBUTE7 ,
obj_page_value_rec.ATTRIBUTE8 ,
obj_page_value_rec.ATTRIBUTE9 ,
obj_page_value_rec.ATTRIBUTE10 ,
obj_page_value_rec.ATTRIBUTE11 ,
obj_page_value_rec.ATTRIBUTE12 ,
obj_page_value_rec.ATTRIBUTE13 ,
obj_page_value_rec.ATTRIBUTE14 ,
obj_page_value_rec.ATTRIBUTE15 ,
obj_page_value_rec.ATTRIBUTE16 ,
obj_page_value_rec.ATTRIBUTE17 ,
obj_page_value_rec.ATTRIBUTE18 ,
obj_page_value_rec.ATTRIBUTE19 ,
obj_page_value_rec.ATTRIBUTE20 ,
obj_page_value_rec.UDS_ATTRIBUTE_CATEGORY ,
obj_page_value_rec.UDS_ATTRIBUTE1 ,
obj_page_value_rec.UDS_ATTRIBUTE2 ,
obj_page_value_rec.UDS_ATTRIBUTE3 ,
obj_page_value_rec.UDS_ATTRIBUTE4 ,
obj_page_value_rec.UDS_ATTRIBUTE5 ,
obj_page_value_rec.UDS_ATTRIBUTE6 ,
obj_page_value_rec.UDS_ATTRIBUTE7 ,
obj_page_value_rec.UDS_ATTRIBUTE8 ,
obj_page_value_rec.UDS_ATTRIBUTE9 ,
obj_page_value_rec.UDS_ATTRIBUTE10 ,
obj_page_value_rec.UDS_ATTRIBUTE11 ,
obj_page_value_rec.UDS_ATTRIBUTE12 ,
obj_page_value_rec.UDS_ATTRIBUTE13 ,
obj_page_value_rec.UDS_ATTRIBUTE14 ,
obj_page_value_rec.UDS_ATTRIBUTE15 ,
obj_page_value_rec.UDS_ATTRIBUTE16 ,
obj_page_value_rec.UDS_ATTRIBUTE17 ,
obj_page_value_rec.UDS_ATTRIBUTE18 ,
obj_page_value_rec.UDS_ATTRIBUTE19 ,
obj_page_value_rec.UDS_ATTRIBUTE20 ,
x_return_status ,
x_msg_count ,
x_msg_data
);
PROCEDURE update_project_perccomplete
(
p_version_id 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_percent_complete number;
SELECT attribute7 FROM pa_progress_report_vals
WHERE version_id = p_version_id
AND region_code = 'PA_PROGRESS_PROJECT_DATES';
SELECT object_id, object_type, report_end_date
FROM pa_progress_report_vers
WHERE version_id = p_version_id;
SELECT project_id FROM pa_tasks
WHERE task_id = l_task_id;
SELECT scheduled_start_date, scheduled_finish_date,
start_date,completion_date,actual_start_date
,actual_finish_date
FROM pa_projects_all
WHERE project_id = l_project_id;
PA_DEBUG.init_err_stack('PA_PROGRESS_REPORT_PVT.update_project_perccomplete');
SAVEPOINT update_project_perccomplete;
pa_percent_complete_pkg.insert_row
(
l_project_id,
l_task_id,
l_percent_complete,
l_asof_date,
NULL,
Sysdate,
fnd_global.user_id,
Sysdate,
fnd_global.user_id,
fnd_global.user_id,
x_return_status,
x_msg_data
);
ROLLBACK TO update_project_perccomplete;
ROLLBACK TO update_project_perccomplete;
ROLLBACK TO update_project_perccomplete;
ROLLBACK TO update_project_perccomplete;
FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_Progress_Report_PVT.update_project_perccomplete'
,p_procedure_name => PA_DEBUG.G_Err_Stack );
END update_project_perccomplete;
Bug 3684164. This API is called when the user updates a status
report page layout. If any sections have been deleted, this API
would take care of deleting the data from the working and rejected
status report versions using this page layout.
*/
PROCEDURE delete_version_data
(
p_page_id IN pa_page_layouts.page_id%TYPE
,p_region_source_type_tbl IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE
,p_region_code_tbl IN SYSTEM.PA_VARCHAR2_2000_TBL_TYPE
,p_region_source_code_tbl IN SYSTEM.PA_VARCHAR2_2000_TBL_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
AS
l_msg_count NUMBER := 0;
This cursor fetches all non mandatory regions selected currently for this page
Currently data can be associated only for the regions of style STD,
STD_CUST and DFF. So this cursor will fetch only these regions.
*/
cursor c_page_layout_regions(c_page_id pa_page_layouts.page_id%TYPE)
is
select
layout.region_source_type,
layout.view_region_code,
layout.region_source_code
from pa_page_layout_regions layout, pa_page_type_regions type
where layout.page_id = c_page_id
and layout.region_source_type in ('STD','STD_CUST','DFF')
and type.page_type_code = 'PPR'
and type.region_source_type = layout.region_source_type
and type.region_source_code = decode(layout.region_source_type,'STD_CUST',layout.view_region_code,layout.region_source_code)
and nvl(layout.region_style, 'N') <> 'LINK'
and type.mandatory_flag = 'N';
pa_debug.set_err_stack('PA_PROGRESS_REPORT_PVT.delete_version_data');
delete from pa_progress_report_vals
where region_code = l_temp_region_src_code
and region_source_type = l_region_source_type_tbl(i)
and version_id in
(select version_id
from pa_progress_report_vers
where page_id = p_page_id
and report_status_code in ('PROGRESS_REPORT_WORKING','PROGRESS_REPORT_REJECTED'));
,p_procedure_name => 'delete_version_data');
END delete_version_data;