[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE select_other_routing_rules
(p_transaction_category_id IN number,
p_attribute_range_id_list in varchar2,
p_routing_type in varchar2,
p_db_ranges OUT NOCOPY other_ranges_tab,
p_db_rows OUT NOCOPY number);
PROCEDURE select_other_member_rules(
p_transaction_category_id in number,
p_routing_category_id in number,
p_routing_type in varchar2,
p_member_id in number,
p_attribute_range_id_list in varchar2,
p_db_ranges out nocopy other_ranges_tab,
p_db_rows out nocopy number);
select att.attribute_name,tca.attribute_id,
att.column_type,tca.value_style_cd,tca.value_set_id
from pqh_txn_category_attributes tca,pqh_attributes_vl att
where tca.transaction_category_id = p_transaction_category_id
and tca.attribute_id = att.attribute_id
and tca.list_identifying_flag='Y'
order by tca.attribute_id;
select att.attribute_name,tca.attribute_id,
att.column_type,tca.value_style_cd,tca.value_set_id
from pqh_txn_category_attributes tca,pqh_attributes_vl att
where tca.transaction_category_id = p_transaction_category_id
and tca.attribute_id = att.attribute_id
and tca.member_identifying_flag='Y'
order by tca.attribute_id;
sql_stmt := 'select attribute_range_id,attribute_id,from_char,to_char,from_date,to_date,from_number,to_number,object_version_number from pqh_attribute_ranges where routing_category_id = :p and range_name = :r and ';
select_other_routing_rules
(p_transaction_category_id => p_transaction_category_id,
p_attribute_range_id_list => p_attribute_range_id_list,
p_routing_type => p_routing_type,
p_db_ranges => exist_db_ranges,
p_db_rows => no_db_rows);
select_other_member_rules
(p_transaction_category_id => p_transaction_category_id,
p_attribute_range_id_list => p_attribute_range_id_list,
p_routing_category_id => p_routing_category_id,
p_routing_type => p_routing_type,
p_member_id => p_member_id,
p_db_ranges => exist_db_ranges,
p_db_rows => no_db_rows);
PROCEDURE select_other_member_rules
(p_transaction_category_id in number,
p_routing_category_id in number,
p_routing_type in varchar2,
p_member_id in number,
p_attribute_range_id_list in varchar2,
p_db_ranges out nocopy other_ranges_tab,
p_db_rows out nocopy number) is
--
--
TYPE cur_type IS REF CURSOR;
l_proc varchar2(72) := g_package||'select_other_member_rules';
sql_stmt := 'Select a.routing_category_id,a.range_name,a.attribute_id,a.from_char,a.to_char,a.from_number,a.to_number,a.from_date,a.to_date '
||' from pqh_attribute_ranges a,pqh_routing_categories b '
||' where a.routing_category_id = :p_routing_category_id AND a.routing_category_id = b.routing_category_id AND b.transaction_category_id = :p_transaction_category_id and a.attribute_id IS NOT NULL'
||' AND a.enable_flag ='
||''''
||'Y'
||''''
||' and nvl(a.delete_flag,'||''''||'N'||''''||') <> '
||''''
||'Y'
||''''
||' AND a.attribute_range_id not in ('
||p_attribute_range_id_list
||') AND decode(:p_routing_type,'
||''''
||'R'
||''''
||',a.routing_list_member_id,'
||''''
||'P'
||''''
||',a.position_id,a.assignment_id) = :p_member_id order by 1,2,3';
End select_other_member_rules;
PROCEDURE select_other_routing_rules
(p_transaction_category_id in number,
p_attribute_range_id_list in varchar2,
p_routing_type in varchar2,
p_db_ranges out nocopy other_ranges_tab,
p_db_rows out nocopy number) is
--
TYPE cur_type IS REF CURSOR;
l_proc varchar2(72) := g_package||'select_other_routing_rules';
sql_stmt := 'Select rng.routing_category_id,rng.range_name,rng.attribute_id,rng.from_char,rng.to_char,rng.from_number,rng.to_number,rng.from_date,rng.to_date '
||' from pqh_attribute_ranges rng,pqh_routing_categories rct ';
||' and nvl(rct.delete_flag,:null_value1) <> :delete_flag'
||' and nvl(rct.default_flag,:null_value2) <> :default_flag '
||' and rct.routing_category_id = rng.routing_category_id'
||' AND rct.transaction_category_id = :t '
||' and rng.attribute_range_id not in ('
||p_attribute_range_id_list
||') and rng.enable_flag = :rule_enable '
||' and nvl(rng.delete_flag,:null_value2) <> :delete_rule'
||' and rng.routing_list_member_id is null and rng.position_id is null and rng.assignment_id is null order by 1,2,3';
End select_other_routing_rules;
Procedure Delete_attribute_ranges(p_attribute_id IN number,
p_delete_attr_ranges_flag IN varchar2,
p_primary_flag IN varchar2) is
--
l_dummy varchar2(1);
Select attribute_range_id , object_version_number
from pqh_attribute_ranges
where attribute_id IS NOT NULL
AND attribute_id = p_attribute_id
AND routing_list_member_id is null
AND position_id is null
AND assignment_id is null;
Select attribute_range_id , object_version_number
from pqh_attribute_ranges
where attribute_id IS NOT NULL
AND attribute_id = p_attribute_id
AND (routing_list_member_id is not null
OR position_id is not null
OR assignment_id is not null);
l_proc varchar2(72) := g_package||'Delete_atrribute_ranges';
If p_delete_attr_ranges_flag = 'Y' then
--
pqh_ATTRIBUTE_RANGES_api.delete_ATTRIBUTE_RANGE
(p_validate => false
,p_attribute_range_id => l_attribute_range_id
,p_object_version_number => l_object_version_number
,p_effective_date => sysdate);
Elsif p_delete_attr_ranges_flag = 'N' then
--
Close c1;
Elsif p_delete_attr_ranges_flag = 'I' then
--
-- Ignore.
--
null;
If p_delete_attr_ranges_flag = 'Y' then
--
pqh_ATTRIBUTE_RANGES_api.delete_ATTRIBUTE_RANGE
(p_validate => false
,p_attribute_range_id => l_attribute_range_id
,p_object_version_number => l_object_version_number
,p_effective_date => sysdate);
Elsif p_delete_attr_ranges_flag = 'N' then
--
Close c2;
Elsif p_delete_attr_ranges_flag = 'I' then
--
-- Ignore.
--
null;
PROCEDURE on_insert_attribute_ranges(
p_routing_category_id IN number,
p_range_name IN varchar2,
p_primary_flag IN varchar2,
p_routing_list_member_id IN number,
p_position_id IN number,
p_assignment_id IN number,
p_approver_flag IN varchar2,
p_enable_flag IN varchar2,
ins_attr_ranges_table IN OUT NOCOPY att_ranges,
p_no_attributes IN number) is
ctr number;
l_proc varchar2(72) := g_package||'on_insert_atrribute_ranges';
End on_insert_attribute_ranges;
PROCEDURE insert_update_delete_ranges (
p_routing_category_id IN number,
p_range_name IN varchar2,
p_primary_flag IN varchar2,
p_routing_list_member_id IN number,
p_position_id IN number,
p_assignment_id IN number,
p_approver_flag IN varchar2,
p_enable_flag IN varchar2,
p_attr_ranges_table IN OUT NOCOPY att_ranges,
p_no_attributes IN number) is
ctr number;
l_proc varchar2(72) := g_package||'insert_update_delete_ranges';
pqh_ATTRIBUTE_RANGES_api.update_ATTRIBUTE_RANGE
(p_validate => false
,p_attribute_range_id => p_attr_ranges_table(ctr).attribute_range_id
,p_approver_flag => p_approver_flag
,p_enable_flag => p_enable_flag
,p_assignment_id => p_assignment_id
,p_attribute_id => p_attr_ranges_table(ctr).attribute_id
,p_from_char => p_attr_ranges_table(ctr).from_char
,p_to_char => p_attr_ranges_table(ctr).to_char
,p_from_date => p_attr_ranges_table(ctr).from_date
,p_to_date => p_attr_ranges_table(ctr).to_date
,p_from_number => p_attr_ranges_table(ctr).from_number
,p_to_number => p_attr_ranges_table(ctr).to_number
,p_position_id => p_position_id
,p_range_name => p_range_name
,p_routing_category_id => p_routing_category_id
,p_routing_list_member_id=> p_routing_list_member_id
,p_object_version_number => p_attr_ranges_table(ctr).ovn
,p_effective_date => sysdate
);
End insert_update_delete_ranges;
PROCEDURE on_delete_attribute_ranges (p_validate IN boolean,
del_attr_ranges_table IN OUT NOCOPY att_ranges,
p_no_attributes IN number) is
--
ctr number;
l_proc varchar2(72) := g_package||'on_delete_attribute_ranges';
pqh_ATTRIBUTE_RANGES_api.delete_ATTRIBUTE_RANGE
(p_validate => p_validate
,p_attribute_range_id => del_attr_ranges_table(ctr).attribute_range_id
,p_object_version_number => del_attr_ranges_table(ctr).ovn
,p_effective_date => sysdate);
End on_delete_attribute_ranges;
sql_stmt := 'Select distinct range_name from pqh_attribute_ranges where routing_category_id = :r AND attribute_id IS NOT NULL AND attribute_range_id not in '||p_attribute_id_list;
Select member_Cd
From pqh_transaction_categories
Where transaction_category_id = p_transaction_category_id;
Select count(*)
from pqh_txn_category_attributes
Where transaction_category_id = p_transaction_category_id
AND list_identifying_flag = 'Y';
sql_stmt := 'Select rct.routing_category_id, rng.range_name , rng.attribute_range_id, rng.attribute_id, att.column_type, rng.from_char, rng.to_char, rng.from_number, rng.to_number, rng.from_date, rng.to_date ';
sql_stmt := sql_stmt || ' and rng.enable_flag = :enable_rule and nvl(rng.delete_flag,:null2) <> :delete_rule and rng.routing_list_member_id IS NULL and rng.position_id IS NULL and rng.assignment_id IS NULL ';
Select member_Cd
From pqh_transaction_categories
Where transaction_category_id = p_transaction_category_id;
sql_stmt := 'Select rct.routing_category_id From pqh_routing_categories rct ';
sql_stmt := sql_stmt ||' Where rct.transaction_category_id = :p_transaction_category_id and rct.enable_flag = :p_enable_flag and nvl(rct.default_flag,:null_value1) <> :default_flag and nvl(rct.delete_flag,:null_value2) <> :delete_flag';
sql_stmt := 'Select decode(RLM.user_id,NULL,RLM.role_name,RLM.user_name'
||'||'||''''||'-'||''''||'||' ||' RLM.role_name) routing_member_name from pqh_routing_list_members_v RLM where RLM.routing_list_member_id = :p_member_id';
sql_stmt := 'Select substr(POS.name,1,240) from hr_all_positions pos where pos.position_id = :p_member_id';
Select member_Cd
From pqh_transaction_categories
Where transaction_category_id = p_transaction_category_id;
Select count(*)
from pqh_txn_category_attributes
Where transaction_category_id = p_transaction_category_id
AND member_identifying_flag = 'Y';
sql_stmt := 'Select rct.routing_category_id, rng.range_name ,';
||' and nvl(rct.delete_flag,:null2) <> :delete_flag and rng.routing_category_id = rct.routing_category_id and rng.attribute_id = att.attribute_id';
sql_stmt := sql_stmt || ' and nvl(rng.delete_flag,:null3) <> :delete_rule';