The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE delete_document
(p_validate in number default hr_api.g_false_num
,p_effective_date in date default trunc(sysdate)
,p_datetrack_mode in varchar2
,p_document_id in number
,p_object_version_number in out NOCOPY number
,p_effective_start_date out NOCOPY date
,p_effective_end_date out NOCOPY date
,p_return_status out NOCOPY varchar2
) is
--
-- Define Cursor over Here
-- This cursor fetchs all records from child table irrespective of dates
Cursor CurRetrieveChildRecord is
Select document_attribute_id, object_version_number,effective_start_date
From pqh_document_attributes_f
Where document_id = p_document_id
AND p_effective_date between effective_start_date and effective_end_date;
Select document_attribute_id, object_version_number,effective_start_date
From pqh_document_attributes_f daf
Where daf.document_id = p_document_id
and rowid = (select min(rowid)
From pqh_document_attributes_f
Where document_id = p_document_id
and document_attribute_id = daf.document_attribute_id);
Select document_attribute_id,effective_start_date
from pqh_document_attributes_f pdaf
where pdaf.document_id = p_document_id
and pdaf.effective_start_date >
(
select (effective_end_date) from pqh_documents_f pdf where p_effective_date between effective_start_date and effective_end_date
and pdf.document_id = pdaf.document_id
);
Select 'PRESENT' from pqh_documents_f
where document_id =p_document_id
and p_effective_date between effective_start_date and effective_end_date
and effective_end_date <> hr_general.end_of_time;
Select max(effective_start_date) from
(
Select effective_start_date
from pqh_document_attributes_f
where document_id =p_document_id
and effective_start_date > p_effective_date
and document_attribute_id NOT IN (select document_attribute_id
from pqh_document_attributes_f
where document_id =p_document_id
and p_effective_date between effective_start_date and effective_end_date)
);
Select max(effective_end_date)
from (
Select *
From pqh_document_attributes_f
Where document_id = p_document_id
And p_effective_date between effective_start_date and effective_end_date
AND p_effective_date < decode(effective_end_date , hr_general.end_of_time,p_effective_date-1,effective_end_date)
);
l_proc varchar2(72) := g_package ||'delete_document';
savepoint delete_document_swi;
IF ( p_datetrack_mode = 'DELETE' ) THEN
--
OPEN csr_future_versions;
fnd_message.set_name('PQH','PQH_SS_DELETE_MODE_INVALID');
pqh_document_attributes_api.delete_document_attribute
(p_validate => l_validate
,p_effective_date => p_effective_date
,p_datetrack_mode => p_datetrack_mode
,p_document_attribute_id => docAttributeCursorRow.document_attribute_id
,p_object_version_number => l_ovn
,p_effective_start_date => p_effective_start_date
,p_effective_end_date => p_effective_end_date
);
pqh_document_attributes_api.delete_document_attribute
(p_validate => l_validate
,p_effective_date => l_effective_start_date
,p_datetrack_mode => p_datetrack_mode
,p_document_attribute_id => docAttributeCursorRow.document_attribute_id
,p_object_version_number => l_ovn
,p_effective_start_date => p_effective_start_date
,p_effective_end_date => p_effective_end_date
);
delete from pqh_document_attributes_f where
document_attribute_id = docAttributeCsr.document_attribute_id
and effective_start_date >= docAttributeCsr.effective_start_date;
pqh_documents_api.delete_document
(p_validate => l_validate
,p_effective_date => p_effective_date
,p_datetrack_mode => p_datetrack_mode
,p_document_id => p_document_id
,p_object_version_number => p_object_version_number
,p_effective_start_date => p_effective_start_date
,p_effective_end_date => p_effective_end_date
);
IF ( p_datetrack_mode = 'DELETE' ) THEN
For docAttributeCursor in CurRetrieveChildRecord
loop
update pqh_document_attributes_f t
set t.effective_end_date = hr_general.end_of_time
where t.document_attribute_id = docAttributeCursor.document_attribute_id
and effective_end_date = p_effective_date
and effective_start_date = docAttributeCursor.effective_start_date;
rollback to delete_document_swi;
rollback to delete_document_swi;
end delete_document;