[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select hierarchy_node_id,
node_type
from per_gen_hierarchy_nodes
where parent_hierarchy_node_id = p_hierarchy_node_id;
select node_type
from per_gen_hierarchy_nodes
where hierarchy_node_id = p_hierarchy_node_id;
select hierarchy_node_id
from per_gen_hierarchy_nodes
where hierarchy_version_id = p_hierarchy_version_id
and parent_hierarchy_node_id is null;
select max(level)
from per_gen_hierarchy_nodes
start with parent_hierarchy_node_id is null
and hierarchy_version_id = p_hierarchy_version_id
connect by prior hierarchy_node_id = parent_hierarchy_node_id
and hierarchy_version_id = p_hierarchy_version_id;
select count(distinct nvl(parent_hierarchy_node_id,-1))
from per_gen_hierarchy_nodes
where hierarchy_version_id = p_hierarchy_version_id;
select a.type
from per_gen_hierarchy a,
per_gen_hierarchy_versions b
where a.hierarchy_id = b.hierarchy_id
and b.hierarchy_version_id = p_hierarchy_version_id;
select *
from per_gen_hierarchy
where hierarchy_id = p_hierarchy_id;
select *
from per_gen_hierarchy_versions
where hierarchy_id = p_hierarchy_id;
select *
from per_gen_hierarchy_nodes a
where hierarchy_version_id = p_hierarchy_version_id
order by decode(parent_hierarchy_node_id,null,1,2);
select d.hierarchy_node_id parent_hierarchy_node_id,
a.hierarchy_node_id
from per_gen_hierarchy_nodes a,
per_gen_hierarchy_nodes b,
per_gen_hierarchy_nodes c,
per_gen_hierarchy_nodes d
where a.hierarchy_version_id = l_hierarchy_version_id
and a.entity_id = b.entity_id
and a.node_type = b.node_type
and b.hierarchy_version_id = l_c2.hierarchy_version_id
and a.parent_hierarchy_node_id is not null
and b.parent_hierarchy_node_id = c.hierarchy_node_id
and c.entity_id = d.entity_id
and c.node_type = d.node_type
and c.hierarchy_version_id = l_c2.hierarchy_version_id
and d.hierarchy_version_id = l_hierarchy_version_id;
,p_program_update_date => l_c1.program_update_date
,p_object_version_number => l_object_version_number
,p_attribute_category => l_c1.attribute_category
,p_attribute1 => l_c1.attribute1
,p_attribute2 => l_c1.attribute2
,p_attribute3 => l_c1.attribute3
,p_attribute4 => l_c1.attribute4
,p_attribute5 => l_c1.attribute5
,p_attribute6 => l_c1.attribute6
,p_attribute7 => l_c1.attribute7
,p_attribute8 => l_c1.attribute8
,p_attribute9 => l_c1.attribute9
,p_attribute10 => l_c1.attribute10
,p_attribute11 => l_c1.attribute11
,p_attribute12 => l_c1.attribute12
,p_attribute13 => l_c1.attribute13
,p_attribute14 => l_c1.attribute14
,p_attribute15 => l_c1.attribute15
,p_attribute16 => l_c1.attribute16
,p_attribute17 => l_c1.attribute17
,p_attribute18 => l_c1.attribute18
,p_attribute19 => l_c1.attribute19
,p_attribute20 => l_c1.attribute20
,p_attribute21 => l_c1.attribute21
,p_attribute22 => l_c1.attribute22
,p_attribute23 => l_c1.attribute23
,p_attribute24 => l_c1.attribute24
,p_attribute25 => l_c1.attribute25
,p_attribute26 => l_c1.attribute26
,p_attribute27 => l_c1.attribute27
,p_attribute28 => l_c1.attribute28
,p_attribute29 => l_c1.attribute29
,p_attribute30 => l_c1.attribute30
,p_information_category => l_c1.information_category
,p_information1 => l_c1.information1
,p_information2 => l_c1.information2
,p_information3 => l_c1.information3
,p_information4 => l_c1.information4
,p_information5 => l_c1.information5
,p_information6 => l_c1.information6
,p_information7 => l_c1.information7
,p_information8 => l_c1.information8
,p_information9 => l_c1.information9
,p_information10 => l_c1.information10
,p_information11 => l_c1.information11
,p_information12 => l_c1.information12
,p_information13 => l_c1.information13
,p_information14 => l_c1.information14
,p_information15 => l_c1.information15
,p_information16 => l_c1.information16
,p_information17 => l_c1.information17
,p_information18 => l_c1.information18
,p_information19 => l_c1.information19
,p_information20 => l_c1.information20
,p_information21 => l_c1.information21
,p_information22 => l_c1.information22
,p_information23 => l_c1.information23
,p_information24 => l_c1.information24
,p_information25 => l_c1.information25
,p_information26 => l_c1.information26
,p_information27 => l_c1.information27
,p_information28 => l_c1.information28
,p_information29 => l_c1.information29
,p_information30 => l_c1.information30
,p_effective_date => p_effective_date);
,p_program_update_date => l_c2.program_update_date
,p_object_version_number => l_object_version_number
,p_attribute_category => l_c2.attribute_category
,p_attribute1 => l_c2.attribute1
,p_attribute2 => l_c2.attribute2
,p_attribute3 => l_c2.attribute3
,p_attribute4 => l_c2.attribute4
,p_attribute5 => l_c2.attribute5
,p_attribute6 => l_c2.attribute6
,p_attribute7 => l_c2.attribute7
,p_attribute8 => l_c2.attribute8
,p_attribute9 => l_c2.attribute9
,p_attribute10 => l_c2.attribute10
,p_attribute11 => l_c2.attribute11
,p_attribute12 => l_c2.attribute12
,p_attribute13 => l_c2.attribute13
,p_attribute14 => l_c2.attribute14
,p_attribute15 => l_c2.attribute15
,p_attribute16 => l_c2.attribute16
,p_attribute17 => l_c2.attribute17
,p_attribute18 => l_c2.attribute18
,p_attribute19 => l_c2.attribute19
,p_attribute20 => l_c2.attribute20
,p_attribute21 => l_c2.attribute21
,p_attribute22 => l_c2.attribute22
,p_attribute23 => l_c2.attribute23
,p_attribute24 => l_c2.attribute24
,p_attribute25 => l_c2.attribute25
,p_attribute26 => l_c2.attribute26
,p_attribute27 => l_c2.attribute27
,p_attribute28 => l_c2.attribute28
,p_attribute29 => l_c2.attribute29
,p_attribute30 => l_c2.attribute30
,p_information_category => l_c2.information_category
,p_information1 => l_c2.information1
,p_information2 => l_c2.information2
,p_information3 => l_c2.information3
,p_information4 => l_c2.information4
,p_information5 => l_c2.information5
,p_information6 => l_c2.information6
,p_information7 => l_c2.information7
,p_information8 => l_c2.information8
,p_information9 => l_c2.information9
,p_information10 => l_c2.information10
,p_information11 => l_c2.information11
,p_information12 => l_c2.information12
,p_information13 => l_c2.information13
,p_information14 => l_c2.information14
,p_information15 => l_c2.information15
,p_information16 => l_c2.information16
,p_information17 => l_c2.information17
,p_information18 => l_c2.information18
,p_information19 => l_c2.information19
,p_information20 => l_c2.information20
,p_information21 => l_c2.information21
,p_information22 => l_c2.information22
,p_information23 => l_c2.information23
,p_information24 => l_c2.information24
,p_information25 => l_c2.information25
,p_information26 => l_c2.information26
,p_information27 => l_c2.information27
,p_information28 => l_c2.information28
,p_information29 => l_c2.information29
,p_information30 => l_c2.information30
,p_effective_date => p_effective_date);
,p_program_update_date => l_c3.program_update_date
,p_object_version_number => l_object_version_number
,p_attribute_category => l_c3.attribute_category
,p_attribute1 => l_c3.attribute1
,p_attribute2 => l_c3.attribute2
,p_attribute3 => l_c3.attribute3
,p_attribute4 => l_c3.attribute4
,p_attribute5 => l_c3.attribute5
,p_attribute6 => l_c3.attribute6
,p_attribute7 => l_c3.attribute7
,p_attribute8 => l_c3.attribute8
,p_attribute9 => l_c3.attribute9
,p_attribute10 => l_c3.attribute10
,p_attribute11 => l_c3.attribute11
,p_attribute12 => l_c3.attribute12
,p_attribute13 => l_c3.attribute13
,p_attribute14 => l_c3.attribute14
,p_attribute15 => l_c3.attribute15
,p_attribute16 => l_c3.attribute16
,p_attribute17 => l_c3.attribute17
,p_attribute18 => l_c3.attribute18
,p_attribute19 => l_c3.attribute19
,p_attribute20 => l_c3.attribute20
,p_attribute21 => l_c3.attribute21
,p_attribute22 => l_c3.attribute22
,p_attribute23 => l_c3.attribute23
,p_attribute24 => l_c3.attribute24
,p_attribute25 => l_c3.attribute25
,p_attribute26 => l_c3.attribute26
,p_attribute27 => l_c3.attribute27
,p_attribute28 => l_c3.attribute28
,p_attribute29 => l_c3.attribute29
,p_attribute30 => l_c3.attribute30
,p_information_category => l_c3.information_category
,p_information1 => l_c3.information1
,p_information2 => l_c3.information2
,p_information3 => l_c3.information3
,p_information4 => l_c3.information4
,p_information5 => l_c3.information5
,p_information6 => l_c3.information6
,p_information7 => l_c3.information7
,p_information8 => l_c3.information8
,p_information9 => l_c3.information9
,p_information10 => l_c3.information10
,p_information11 => l_c3.information11
,p_information12 => l_c3.information12
,p_information13 => l_c3.information13
,p_information14 => l_c3.information14
,p_information15 => l_c3.information15
,p_information16 => l_c3.information16
,p_information17 => l_c3.information17
,p_information18 => l_c3.information18
,p_information19 => l_c3.information19
,p_information20 => l_c3.information20
,p_information21 => l_c3.information21
,p_information22 => l_c3.information22
,p_information23 => l_c3.information23
,p_information24 => l_c3.information24
,p_information25 => l_c3.information25
,p_information26 => l_c3.information26
,p_information27 => l_c3.information27
,p_information28 => l_c3.information28
,p_information29 => l_c3.information29
,p_information30 => l_c3.information30
,p_effective_date => p_effective_date);
update per_gen_hierarchy_nodes
set parent_hierarchy_node_id = l_c4.parent_hierarchy_node_id
where hierarchy_node_id = l_c4.hierarchy_node_id;
select *
from per_gen_hierarchy_versions
where hierarchy_version_id = p_hierarchy_version_id;
select *
from per_gen_hierarchy_nodes a
where hierarchy_version_id = p_hierarchy_version_id
order by decode(parent_hierarchy_node_id,null,1,2);
select d.hierarchy_node_id parent_hierarchy_node_id,
a.hierarchy_node_id
from per_gen_hierarchy_nodes a,
per_gen_hierarchy_nodes b,
per_gen_hierarchy_nodes c,
per_gen_hierarchy_nodes d
where a.hierarchy_version_id = l_hierarchy_version_id
and a.entity_id = b.entity_id
and a.node_type = b.node_type
and b.hierarchy_version_id = p_hierarchy_version_id
and a.parent_hierarchy_node_id is not null
and b.parent_hierarchy_node_id = c.hierarchy_node_id
and c.entity_id = d.entity_id
and c.node_type = d.node_type
and c.hierarchy_version_id = p_hierarchy_version_id
and d.hierarchy_version_id = l_hierarchy_version_id;
,p_program_update_date => l_c1.program_update_date
,p_object_version_number => l_object_version_number
,p_attribute_category => l_c1.attribute_category
,p_attribute1 => l_c1.attribute1
,p_attribute2 => l_c1.attribute2
,p_attribute3 => l_c1.attribute3
,p_attribute4 => l_c1.attribute4
,p_attribute5 => l_c1.attribute5
,p_attribute6 => l_c1.attribute6
,p_attribute7 => l_c1.attribute7
,p_attribute8 => l_c1.attribute8
,p_attribute9 => l_c1.attribute9
,p_attribute10 => l_c1.attribute10
,p_attribute11 => l_c1.attribute11
,p_attribute12 => l_c1.attribute12
,p_attribute13 => l_c1.attribute13
,p_attribute14 => l_c1.attribute14
,p_attribute15 => l_c1.attribute15
,p_attribute16 => l_c1.attribute16
,p_attribute17 => l_c1.attribute17
,p_attribute18 => l_c1.attribute18
,p_attribute19 => l_c1.attribute19
,p_attribute20 => l_c1.attribute20
,p_attribute21 => l_c1.attribute21
,p_attribute22 => l_c1.attribute22
,p_attribute23 => l_c1.attribute23
,p_attribute24 => l_c1.attribute24
,p_attribute25 => l_c1.attribute25
,p_attribute26 => l_c1.attribute26
,p_attribute27 => l_c1.attribute27
,p_attribute28 => l_c1.attribute28
,p_attribute29 => l_c1.attribute29
,p_attribute30 => l_c1.attribute30
,p_information_category => l_c1.information_category
,p_information1 => l_c1.information1
,p_information2 => l_c1.information2
,p_information3 => l_c1.information3
,p_information4 => l_c1.information4
,p_information5 => l_c1.information5
,p_information6 => l_c1.information6
,p_information7 => l_c1.information7
,p_information8 => l_c1.information8
,p_information9 => l_c1.information9
,p_information10 => l_c1.information10
,p_information11 => l_c1.information11
,p_information12 => l_c1.information12
,p_information13 => l_c1.information13
,p_information14 => l_c1.information14
,p_information15 => l_c1.information15
,p_information16 => l_c1.information16
,p_information17 => l_c1.information17
,p_information18 => l_c1.information18
,p_information19 => l_c1.information19
,p_information20 => l_c1.information20
,p_information21 => l_c1.information21
,p_information22 => l_c1.information22
,p_information23 => l_c1.information23
,p_information24 => l_c1.information24
,p_information25 => l_c1.information25
,p_information26 => l_c1.information26
,p_information27 => l_c1.information27
,p_information28 => l_c1.information28
,p_information29 => l_c1.information29
,p_information30 => l_c1.information30
,p_effective_date => p_effective_date);
,p_program_update_date => l_c2.program_update_date
,p_object_version_number => l_object_version_number
,p_attribute_category => l_c2.attribute_category
,p_attribute1 => l_c2.attribute1
,p_attribute2 => l_c2.attribute2
,p_attribute3 => l_c2.attribute3
,p_attribute4 => l_c2.attribute4
,p_attribute5 => l_c2.attribute5
,p_attribute6 => l_c2.attribute6
,p_attribute7 => l_c2.attribute7
,p_attribute8 => l_c2.attribute8
,p_attribute9 => l_c2.attribute9
,p_attribute10 => l_c2.attribute10
,p_attribute11 => l_c2.attribute11
,p_attribute12 => l_c2.attribute12
,p_attribute13 => l_c2.attribute13
,p_attribute14 => l_c2.attribute14
,p_attribute15 => l_c2.attribute15
,p_attribute16 => l_c2.attribute16
,p_attribute17 => l_c2.attribute17
,p_attribute18 => l_c2.attribute18
,p_attribute19 => l_c2.attribute19
,p_attribute20 => l_c2.attribute20
,p_attribute21 => l_c2.attribute21
,p_attribute22 => l_c2.attribute22
,p_attribute23 => l_c2.attribute23
,p_attribute24 => l_c2.attribute24
,p_attribute25 => l_c2.attribute25
,p_attribute26 => l_c2.attribute26
,p_attribute27 => l_c2.attribute27
,p_attribute28 => l_c2.attribute28
,p_attribute29 => l_c2.attribute29
,p_attribute30 => l_c2.attribute30
,p_information_category => l_c2.information_category
,p_information1 => l_c2.information1
,p_information2 => l_c2.information2
,p_information3 => l_c2.information3
,p_information4 => l_c2.information4
,p_information5 => l_c2.information5
,p_information6 => l_c2.information6
,p_information7 => l_c2.information7
,p_information8 => l_c2.information8
,p_information9 => l_c2.information9
,p_information10 => l_c2.information10
,p_information11 => l_c2.information11
,p_information12 => l_c2.information12
,p_information13 => l_c2.information13
,p_information14 => l_c2.information14
,p_information15 => l_c2.information15
,p_information16 => l_c2.information16
,p_information17 => l_c2.information17
,p_information18 => l_c2.information18
,p_information19 => l_c2.information19
,p_information20 => l_c2.information20
,p_information21 => l_c2.information21
,p_information22 => l_c2.information22
,p_information23 => l_c2.information23
,p_information24 => l_c2.information24
,p_information25 => l_c2.information25
,p_information26 => l_c2.information26
,p_information27 => l_c2.information27
,p_information28 => l_c2.information28
,p_information29 => l_c2.information29
,p_information30 => l_c2.information30
,p_effective_date => p_effective_date);
update per_gen_hierarchy_nodes
set parent_hierarchy_node_id = l_c3.parent_hierarchy_node_id
where hierarchy_node_id = l_c3.hierarchy_node_id;