[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select 1 into dummy
from dual
where exists (select 1
from pa_lookups
where lookup_type = 'PA_XC_PROGRESS_STATUS'
and lookup_code = p_progress_code);
/** Commented for progress update
PROCEDURE update_progress_report(
P_USER_ID IN NUMBER
,P_COMMIT_FLAG IN VARCHAR2 default 'N'
,P_DEBUG_MODE IN VARCHAR2 default 'N'
,P_PROJECT_ID_OLD NUMBER := null
,P_TASK_ID_OLD NUMBER := null
,P_PROGRESS_STATUS_CODE_OLD VARCHAR2 := null
,P_SHORT_DESCRIPTION_OLD VARCHAR2 := null
,P_PROGRESS_ASOF_DATE_OLD VARCHAR2 := null
,P_LONG_DESCRIPTION_OLD VARCHAR2 := null
,P_ISSUES_OLD VARCHAR2 := null
,P_ESTIMATED_START_DATE_OLD VARCHAR2 := null
,P_ESTIMATED_END_DATE_OLD VARCHAR2 := null
,P_ACTUAL_START_DATE_OLD VARCHAR2 := null
,P_ACTUAL_END_DATE_OLD VARCHAR2 := null
,P_PERCENT_COMPLETE_OLD NUMBER := null
,P_ESTIMATE_TO_COMPLETE_OLD NUMBER := null
,P_UNIT_TYPE_OLD VARCHAR2 := null
,p_wf_status_code_old VARCHAR2 := null
,p_wf_item_type_old VARCHAR2 := null
,p_wf_item_key_old NUMBER := NULL
,p_wf_process_old VARCHAR2 := null
,P_PROJECT_ID_NEW NUMBER := null
,P_TASK_ID_NEW NUMBER := null
,P_PROGRESS_STATUS_CODE_NEW VARCHAR2 := null
,P_SHORT_DESCRIPTION_NEW VARCHAR2 := null
,P_PROGRESS_ASOF_DATE_NEW VARCHAR2 := null
,P_LONG_DESCRIPTION_NEW VARCHAR2 := null
,P_ISSUES_NEW VARCHAR2 := null
,P_ESTIMATED_START_DATE_NEW VARCHAR2 := null
,P_ESTIMATED_END_DATE_NEW VARCHAR2 := null
,P_ACTUAL_START_DATE_NEW VARCHAR2 := null
,P_ACTUAL_END_DATE_NEW VARCHAR2 := null
,P_PERCENT_COMPLETE_NEW NUMBER := null
,P_ESTIMATE_TO_COMPLETE_NEW NUMBER := null
,P_UNIT_TYPE_NEW VARCHAR2 := null
,p_wf_status_code_new VARCHAR2 := null
,p_wf_item_type_new VARCHAR2 := null
,p_wf_item_key_new NUMBER := null
,p_wf_process_new VARCHAR2 := null
,p_create_item_key_flag VARCHAR2 := 'N'
,x_item_key OUT number
,X_RETURN_STATUS OUT VARCHAR2
,X_MSG_COUNT IN OUT NUMBER
,X_MSG_DATA IN OUT pa_vc_1000_2000
)
IS
CURSOR C IS
SELECT *
FROM PA_PROJ_PROGRESS_REPORTS
WHERE project_id = P_project_id_old
AND task_id = p_task_id_old
FOR UPDATE of progress_status_code NOWAIT;
SELECT pa_workflow_itemkey_s.nextval
INTO l_wf_item_key_new
from dual;
--debug_msg ('In update_progress_report 4');
PA_XC_PRJ_PROGRESS_REPORTS_PKG.Update_Row(
P_PROJECT_ID_NEW
,P_TASK_ID_NEW
,P_PROGRESS_STATUS_CODE_NEW
,P_SHORT_DESCRIPTION_NEW
,fnd_date.canonical_to_date(P_PROGRESS_ASOF_DATE_NEW)
,P_LONG_DESCRIPTION_NEW
,P_ISSUES_NEW
,fnd_date.canonical_to_date(P_ESTIMATED_START_DATE_NEW)
,fnd_date.canonical_to_date(P_ESTIMATED_END_DATE_NEW)
,fnd_date.canonical_to_date(P_ACTUAL_START_DATE_NEW)
,fnd_date.canonical_to_date(P_ACTUAL_END_DATE_NEW)
,P_PERCENT_COMPLETE_NEW
,P_ESTIMATE_TO_COMPLETE_NEW
,p_unit_type_new
,p_wf_status_code_new
,p_wf_item_type_new
,l_wf_item_key_new
,p_wf_process_new
,P_USER_ID
,sysdate
,P_USER_ID
);
--debug_msg ('In update_progress_report 13');
END update_progress_report;
PROCEDURE Insert_Row(
-- P_ROWID IN OUT VARCHAR2
P_PROGRESS_REPORT_ID IN OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
,P_RECORD_VERSION_NUMBER NUMBER DEFAULT 1
,P_PROJECT_ID NUMBER
,P_TASK_ID NUMBER default 0
,P_PROGRESS_STATUS_CODE VARCHAR2 default 'ON_TRACK'
,P_SHORT_DESCRIPTION VARCHAR2 default null
,P_PROGRESS_ASOF_DATE DATE default sysdate
,P_LONG_DESCRIPTION VARCHAR2 default null
,P_ISSUES VARCHAR2 default null
,P_ESTIMATED_START_DATE DATE default null
,P_ESTIMATED_END_DATE DATE default null
,P_ACTUAL_START_DATE DATE default null
,P_ACTUAL_END_DATE DATE default null
,P_PERCENT_COMPLETE NUMBER default null
,P_ESTIMATE_TO_COMPLETE NUMBER default null
,P_UNIT_TYPE VARCHAR2 default null
,P_PLANNED_ACTIVITIES VARCHAR2 DEFAULT NULL
,P_REPORT_STATUS VARCHAR2 DEFAULT 'WIP'
,P_CREATED_BY NUMBER default -1
,P_CREATION_DATE DATE default sysdate
,P_LAST_UPDATED_BY NUMBER default -1
,P_LAST_UPDATE_DATE DATE default sysdate
,P_LAST_UPDATE_LOGIN NUMBER default -1
,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_progress_report_id number;
CURSOR C IS SELECT rowid FROM PA_PROJ_PROGRESS_REPORTS
WHERE progress_report_id = l_progress_report_id;
SELECT PA_PROJ_PROGRESS_REPORTS_S.NEXTVAL
INTO l_progress_report_id
FROM dual;
INSERT INTO PA_PROJ_PROGRESS_REPORTS(
PROGRESS_REPORT_ID
,RECORD_VERSION_NUMBER
,PROJECT_ID
,TASK_ID
,PROGRESS_STATUS_CODE
,SHORT_DESCRIPTION
,PROGRESS_ASOF_DATE
,LONG_DESCRIPTION
,ISSUES
,ESTIMATED_START_DATE
,ESTIMATED_END_DATE
,ACTUAL_START_DATE
,ACTUAL_END_DATE
,PERCENT_COMPLETE
,ESTIMATE_TO_COMPLETE
,UNIT_TYPE
,PLANNED_ACTIVITIES
,REPORT_STATUS
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
) VALUES (
L_PROGRESS_REPORT_ID
,P_RECORD_VERSION_NUMBER
,P_PROJECT_ID
,P_TASK_ID
,P_PROGRESS_STATUS_CODE
,P_SHORT_DESCRIPTION
,trunc(P_PROGRESS_ASOF_DATE)
,P_LONG_DESCRIPTION
,P_ISSUES
,trunc(P_ESTIMATED_START_DATE)
,trunc(P_ESTIMATED_END_DATE)
,trunc(P_ACTUAL_START_DATE)
,trunc(P_ACTUAL_END_DATE)
,P_PERCENT_COMPLETE
,P_ESTIMATE_TO_COMPLETE
,P_UNIT_TYPE
,P_PLANNED_ACTIVITIES
,P_REPORT_STATUS
,P_CREATED_BY
,P_CREATION_DATE
,P_LAST_UPDATED_BY
,P_LAST_UPDATE_DATE
,P_LAST_UPDATE_LOGIN
);
END Insert_Row;
PROCEDURE Update_Row(
P_PROGRESS_REPORT_ID NUMBER
,P_RECORD_VERSION_NUMBER NUMBER
,P_PROJECT_ID NUMBER
,P_TASK_ID NUMBER
,P_PROGRESS_STATUS_CODE VARCHAR2
,P_SHORT_DESCRIPTION VARCHAR2
,P_PROGRESS_ASOF_DATE DATE
,P_LONG_DESCRIPTION VARCHAR2
,P_ISSUES VARCHAR2
,P_ESTIMATED_START_DATE DATE default trunc(to_date('01/01/1851','DD/MM/YYYY'))
,P_ESTIMATED_END_DATE DATE default trunc(to_date('01/01/1851','DD/MM/YYYY'))
,P_ACTUAL_START_DATE DATE default trunc(to_date('01/01/1851','DD/MM/YYYY'))
,P_ACTUAL_END_DATE DATE default trunc(to_date('01/011851','DD/MM/YYYY'))
,P_PERCENT_COMPLETE NUMBER default -9999
,P_ESTIMATE_TO_COMPLETE NUMBER default -9999
,P_UNIT_TYPE VARCHAR2 default '####'
,P_PLANNED_ACTIVITIES VARCHAR2 default '####'
,P_REPORT_STATUS VARCHAR2 default '####'
,p_wf_status_code VARCHAR2 default '####'
,p_wf_item_type VARCHAR2 default '####'
,p_wf_item_key NUMBER default -9999
,p_wf_process VARCHAR2 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
CURSOR new_wip is
select * from
pa_proj_progress_reports
where progress_report_id = p_progress_report_id;
UPDATE PA_PROJ_PROGRESS_REPORTS
SET RECORD_VERSION_NUMBER = P_RECORD_VERSION_NUMBER + 1
,PROJECT_ID = P_PROJECT_ID
,TASK_ID = P_TASK_ID
,PROGRESS_STATUS_CODE = P_PROGRESS_STATUS_CODE
,SHORT_DESCRIPTION = P_SHORT_DESCRIPTION
,PROGRESS_ASOF_DATE = nvl(P_PROGRESS_ASOF_DATE,trunc(sysdate))
,LONG_DESCRIPTION = P_LONG_DESCRIPTION
,ISSUES = P_ISSUES
,ESTIMATED_START_DATE = decode( to_char(P_ESTIMATED_START_DATE,'DD/MM/YYYY'), '01/01/1851', trunc(ESTIMATED_START_DATE), trunc(P_ESTIMATED_START_DATE))
,ESTIMATED_END_DATE = decode( to_char(P_ESTIMATED_END_DATE,'DD/MM/YYYY'), '01/01/1851', trunc(ESTIMATED_END_DATE), trunc(P_ESTIMATED_END_DATE) )
,ACTUAL_START_DATE = decode( to_char(P_ACTUAL_START_DATE,'DD/MM/YYYY'), '01/01/1851', trunc(ACTUAL_START_DATE), trunc(P_ACTUAL_START_DATE) )
,ACTUAL_END_DATE = decode( to_char(P_ACTUAL_END_DATE,'DD/MM/YYYY'), '01/01/1851', trunc(ACTUAL_END_DATE), trunc(P_ACTUAL_END_DATE) )
,PERCENT_COMPLETE = decode( P_PERCENT_COMPLETE, -9999, PERCENT_COMPLETE, P_PERCENT_COMPLETE )
,ESTIMATE_TO_COMPLETE = decode( P_ESTIMATE_TO_COMPLETE, -9999, ESTIMATE_TO_COMPLETE, P_ESTIMATE_TO_COMPLETE )
,UNIT_TYPE = decode( P_UNIT_TYPE, '####', UNIT_TYPE, P_UNIT_TYPE )
,PLANNED_ACTIVITIES = decode(P_PLANNED_ACTIVITIES ,'####',PLANNED_ACTIVITIES,P_PLANNED_ACTIVITIES)
,REPORT_STATUS = decode(P_REPORT_STATUS ,'####',REPORT_STATUS,P_REPORT_STATUS)
--,wf_status_code = decode( p_wf_status_code, '####', wf_status_code, p_wf_status_code )
--,wf_item_type = decode(p_wf_item_type, '####', wf_item_type, p_wf_item_type )
--,wf_item_key = decode(p_wf_item_key, -9999, wf_item_key, p_wf_item_key)
--,wf_process = decode(p_wf_process,'####', wf_process, p_wf_process)
,LAST_UPDATED_BY = -1
,LAST_UPDATE_DATE = sysdate
,LAST_UPDATE_LOGIN = -1
WHERE PROGRESS_REPORT_ID = P_PROGRESS_REPORT_ID
AND NVL(P_RECORD_VERSION_NUMBER,RECORD_VERSION_NUMBER) = RECORD_VERSION_NUMBER;
Insert_row(
--P_ROWID => l_row_id
P_PROGRESS_REPORT_ID => l_progress_report_id
,P_PROJECT_ID => c_rec.project_id
,P_TASK_ID => c_rec.TASK_ID
,P_PROGRESS_STATUS_CODE => c_rec.PROGRESS_STATUS_CODE
,P_SHORT_DESCRIPTION => c_rec.SHORT_DESCRIPTION
--,P_PROGRESS_ASOF_DATE DATE default sysdate
,P_LONG_DESCRIPTION => c_rec.LONG_DESCRIPTION
,P_ISSUES => c_rec.ISSUES
,P_ESTIMATED_START_DATE => trunc(c_rec.ESTIMATED_START_DATE)
,P_ESTIMATED_END_DATE => trunc(c_rec.ESTIMATED_END_DATE)
,P_ACTUAL_START_DATE => trunc(c_rec.ACTUAL_START_DATE)
,P_ACTUAL_END_DATE => trunc(c_rec.ACTUAL_END_DATE)
,P_PERCENT_COMPLETE => c_rec.PERCENT_COMPLETE
,P_ESTIMATE_TO_COMPLETE => c_rec.ESTIMATE_TO_COMPLETE
,P_UNIT_TYPE => c_rec.UNIT_TYPE
,P_PLANNED_ACTIVITIES => c_rec.PLANNED_ACTIVITIES
--,P_REPORT_STATUS VARCHAR2 DEFAULT 'WIP'
,P_CREATED_BY => -1
,P_CREATION_DATE => sysdate
,P_LAST_UPDATED_BY => -1
,P_LAST_UPDATE_DATE => sysdate
,P_LAST_UPDATE_LOGIN => -1
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data );
END Update_row;
PROCEDURE Delete_Row( P_PROGRESS_REPORT_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
BEGIN
x_return_status := FND_API.G_RET_STS_SUCCESS;
DELETE FROM PA_PROJ_PROGRESS_REPORTS
WHERE progress_report_id = p_progress_report_id;
END Delete_Row;
--,P_LAST_UPDATED_BY NUMBER default -1
--,P_LAST_UPDATE_DATE DATE default sysdate
--,P_LAST_UPDATE_LOGIN NUMBER default -1
--,x_return_status OUT VARCHAR2
--,x_msg_count OUT NUMBER
--,x_msg_data OUT VARCHAR2 )
IS
CURSOR new_wip is
select * from
pa_proj_progress_reports
where progress_report_id = (select max(progress_report_id)
from pa_proj_progress_reports
where project_id = p_project_id
and report_status = 'PUBLISHED');
Insert_row(
--P_ROWID => l_row_id
P_PROGRESS_REPORT_ID => l_progress_report_id
,P_PROJECT_ID => c_rec.project_id
,P_TASK_ID => c_rec.TASK_ID
,P_PROGRESS_STATUS_CODE => c_rec.PROGRESS_STATUS_CODE
,P_SHORT_DESCRIPTION => c_rec.SHORT_DESCRIPTION
,P_PROGRESS_ASOF_DATE => sysdate
,P_LONG_DESCRIPTION => c_rec.LONG_DESCRIPTION
,P_ISSUES => c_rec.ISSUES
,P_ESTIMATED_START_DATE => trunc(c_rec.ESTIMATED_START_DATE)
,P_ESTIMATED_END_DATE => trunc(c_rec.ESTIMATED_END_DATE)
,P_ACTUAL_START_DATE => trunc(c_rec.ACTUAL_START_DATE)
,P_ACTUAL_END_DATE => trunc(c_rec.ACTUAL_END_DATE)
,P_PERCENT_COMPLETE => c_rec.PERCENT_COMPLETE
,P_ESTIMATE_TO_COMPLETE => c_rec.ESTIMATE_TO_COMPLETE
,P_UNIT_TYPE => c_rec.UNIT_TYPE
,P_PLANNED_ACTIVITIES => c_rec.PLANNED_ACTIVITIES
,P_REPORT_STATUS => 'WIP'
,P_CREATED_BY => -1
,P_CREATION_DATE => sysdate
,P_LAST_UPDATED_BY => -1
,P_LAST_UPDATE_DATE => sysdate
,P_LAST_UPDATE_LOGIN => -1
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data );