[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Select nvl(setup_type_cd,'INCOMPLETE') , nvl(freeze_status_cd,'X')
from pqh_transaction_categories_vl
where transaction_category_id = p_transaction_category_id;
Select pqh_system_rule_s.nextval
from dual;
,p_delete_flag => NULL
,p_routing_list_id => p_routing_list_id
,p_position_structure_id => p_position_structure_id
,p_override_position_id => p_override_position_id
,p_override_assignment_id => p_override_assignment_id
,p_override_role_id => p_override_role_id
,p_override_user_id => p_override_user_id
,p_object_version_number => p_object_version_number
,p_effective_date => p_effective_date);
,p_delete_flag => NULL
,p_assignment_id => NULL
,p_attribute_id => NULL
,p_from_char => NULL
,p_from_date => NULL
,p_from_number => NULL
,p_position_id => NULL
,p_range_name => l_rule_name
,p_routing_category_id => p_routing_category_id
,p_routing_list_member_id => NULL
,p_to_char => NULL
,p_to_date => NULL
,p_to_number => NULL
,p_object_version_number => p_object_version_number
,p_effective_date => p_effective_date);
PROCEDURE update_default_hierarchy
(
p_validate in boolean default false
,p_old_routing_category_id in number
,p_routing_category_id in out nocopy number
,p_transaction_category_id in number default null
,p_enable_flag in varchar2 default 'Y'
,p_default_flag in varchar2 default null
,p_routing_list_id in number default null
,p_position_structure_id in number default null
,p_override_position_id in number default null
,p_override_assignment_id in number default null
,p_override_role_id in number default null
,p_override_user_id in number default null
,p_object_version_number in out nocopy number
,p_effective_date in date
)
is
Cursor csr_get_attribute_range(p_routing_category_id number) is
Select *
from pqh_attribute_ranges
where routing_category_id = p_routing_category_id;
Select * from pqh_routing_categories
Where routing_category_id = p_old_routing_category_id;
l_proc varchar2(72) := 'update_default_hierarchy';
pqh_attribute_ranges_api.update_attribute_range
(
p_validate => p_validate
,p_attribute_range_id => l_rng_record.attribute_range_id
,p_approver_flag => l_rng_record.approver_flag
,p_enable_flag => 'N'
,p_delete_flag => l_rng_record.delete_flag
,p_assignment_id => l_rng_record.assignment_id
,p_attribute_id => l_rng_record.attribute_id
,p_from_char => l_rng_record.from_char
,p_from_date => l_rng_record.from_date
,p_from_number => l_rng_record.from_number
,p_position_id => l_rng_record.position_id
,p_range_name => l_rng_record.range_name
,p_routing_category_id => l_rng_record.routing_category_id
,p_routing_list_member_id => l_rng_record.routing_list_member_id
,p_to_char => l_rng_record.to_char
,p_to_date => l_rng_record.to_date
,p_to_number => l_rng_record.to_number
,p_object_version_number => l_rng_record.object_version_number
,p_effective_date => p_effective_date);
pqh_routing_categories_api.update_routing_category
(
p_validate => p_validate
,p_routing_category_id => p_old_routing_category_id
,p_transaction_category_id => l_rct_record.transaction_category_id
,p_enable_flag => 'N'
,p_delete_flag => l_rct_record.delete_flag
,p_default_flag => l_rct_record.default_flag
,p_routing_list_id => l_rct_record.routing_list_id
,p_position_structure_id => l_rct_record.position_structure_id
,p_override_position_id => l_rct_record.override_position_id
,p_override_assignment_id => l_rct_record.override_assignment_id
,p_override_role_id => l_rct_record.override_role_id
,p_override_user_id => l_rct_record.override_user_id
,p_object_version_number => l_rct_record.object_version_number
,p_effective_date => p_effective_date);
pqh_attribute_ranges_api.update_attribute_range
(
p_validate => p_validate
,p_attribute_range_id => l_rng_record.attribute_range_id
,p_approver_flag => l_rng_record.approver_flag
,p_enable_flag => 'Y'
,p_delete_flag => l_rng_record.delete_flag
,p_assignment_id => l_rng_record.assignment_id
,p_attribute_id => l_rng_record.attribute_id
,p_from_char => l_rng_record.from_char
,p_from_date => l_rng_record.from_date
,p_from_number => l_rng_record.from_number
,p_position_id => l_rng_record.position_id
,p_range_name => l_rng_record.range_name
,p_routing_category_id => p_routing_category_id
,p_routing_list_member_id => l_rng_record.routing_list_member_id
,p_to_char => l_rng_record.to_char
,p_to_date => l_rng_record.to_date
,p_to_number => l_rng_record.to_number
,p_object_version_number => l_rng_record.object_version_number
,p_effective_date => p_effective_date);
pqh_routing_categories_api.update_routing_category
(
p_validate => p_validate
,p_routing_category_id => p_routing_category_id
,p_transaction_category_id => p_transaction_category_id
,p_enable_flag => 'Y'
,p_delete_flag => NULL
,p_default_flag => p_default_flag
,p_routing_list_id => p_routing_list_id
,p_position_structure_id => p_position_structure_id
,p_override_position_id => p_override_position_id
,p_override_assignment_id => p_override_assignment_id
,p_override_role_id => p_override_role_id
,p_override_user_id => p_override_user_id
,p_object_version_number => p_object_version_number
,p_effective_date => p_effective_date);
,p_delete_flag => NULL
,p_routing_list_id => p_routing_list_id
,p_position_structure_id => p_position_structure_id
,p_override_position_id => p_override_position_id
,p_override_assignment_id => p_override_assignment_id
,p_override_role_id => p_override_role_id
,p_override_user_id => p_override_user_id
,p_object_version_number => p_object_version_number
,p_effective_date => p_effective_date);
,p_delete_flag => NULL
,p_assignment_id => NULL
,p_attribute_id => NULL
,p_from_char => NULL
,p_from_date => NULL
,p_from_number => NULL
,p_position_id => NULL
,p_range_name => l_rule_name
,p_routing_category_id => p_routing_category_id
,p_routing_list_member_id => NULL
,p_to_char => NULL
,p_to_date => NULL
,p_to_number => NULL
,p_object_version_number => p_object_version_number
,p_effective_date => p_effective_date);
Select attribute_range_id,object_version_number
from pqh_attribute_ranges
Where routing_category_id = p_routing_category_id
And attribute_id is null
And nvl(routing_list_member_id,-99) = nvl(p_routing_list_member_id,-99)
And nvl(position_id,-99) = nvl(p_position_id,-99)
And nvl(p_assignment_id,-99) = nvl(p_assignment_id,-99)
And enable_flag = 'N';
,p_delete_flag => NULL
,p_assignment_id => p_assignment_id
,p_attribute_id => p_attribute_id
,p_from_char => NULL
,p_from_date => NULL
,p_from_number => NULL
,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_to_char => NULL
,p_to_date => NULL
,p_to_number => NULL
,p_object_version_number => p_object_version_number
,p_effective_date => p_effective_date);
pqh_attribute_ranges_api.update_attribute_range(
p_validate => p_validate
,p_attribute_range_id => p_attribute_range_id
,p_approver_flag => 'Y'
,p_enable_flag => 'Y'
,p_delete_flag => NULL
,p_object_version_number => p_object_version_number
,p_effective_date => p_effective_date);
PROCEDURE update_default_approver
( p_validate in boolean default false
,p_attribute_range_id in number
,p_approver_flag in varchar2 default null
,p_enable_flag in varchar2 default 'Y'
,p_assignment_id in number default null
,p_attribute_id in number default null
,p_position_id in number default null
,p_range_name in varchar2
,p_routing_category_id in number
,p_routing_list_member_id in number default null
,p_object_version_number in out nocopy number
,p_effective_date in date
) is
--
l_proc varchar2(72) := 'update_default_approver';
pqh_attribute_ranges_api.update_attribute_range
(
p_validate => p_validate
,p_attribute_range_id => p_attribute_range_id
,p_approver_flag => p_approver_flag
,p_enable_flag => p_enable_flag
,p_delete_flag => NULL
,p_assignment_id => p_assignment_id
,p_attribute_id => p_attribute_id
,p_from_char => NULL
,p_from_date => NULL
,p_from_number => NULL
,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_to_char => NULL
,p_to_date => NULL
,p_to_number => NULL
,p_object_version_number => p_object_version_number
,p_effective_date => p_effective_date);
PROCEDURE delete_default_approver
( p_validate in boolean default false
,p_attribute_range_id in number
,p_object_version_number in number
,p_effective_date in date
) is
l_proc varchar2(72) := 'delete_default_approver';
pqh_attribute_ranges_api.delete_attribute_range
(
p_validate => p_validate
,p_attribute_range_id => p_attribute_range_id
,p_object_version_number => p_object_version_number
,p_effective_date => p_effective_date
);
PROCEDURE select_routing_attribute
(p_txn_category_attribute_id in number,
p_attribute_id in number,
p_transaction_category_id in number) is
--
-- The following cursor determines the ovn of the master attribute id
--
Cursor csr_master_attribute is
Select tca.object_version_number
From pqh_txn_category_attributes tca
Where txn_category_attribute_id = p_txn_category_attribute_id
for update nowait;
Select tca.txn_category_attribute_id,tca.object_version_number
From pqh_txn_category_attributes tca
Where tca.transaction_category_id = p_transaction_category_id
and tca.attribute_id in
(Select attribute_id
From pqh_attributes
Where master_attribute_id = p_attribute_id)
for update nowait;
l_proc varchar2(72) := 'select_routing_attribute';
pqh_txn_cat_attributes_api.update_TXN_CAT_ATTRIBUTE
(
p_validate => false
,p_txn_category_attribute_id => p_txn_category_attribute_id
,p_object_version_number => l_ovn
,p_list_identifying_flag => 'Y'
,p_value_style_cd => 'RANGE'
,p_effective_date => sysdate
,p_delete_attr_ranges_flag => 'N'
);
pqh_txn_cat_attributes_api.update_TXN_CAT_ATTRIBUTE
(
p_validate => false
,p_txn_category_attribute_id => child_rec.txn_category_attribute_id
,p_object_version_number => child_rec.object_version_number
,p_list_identifying_flag => 'Y'
,p_value_style_cd => 'RANGE'
,p_effective_date => sysdate
,p_delete_attr_ranges_flag => 'N'
);
PROCEDURE unselect_routing_attribute
(p_txn_category_attribute_id in number,
p_attribute_id in number,
p_transaction_category_id in number) is
--
-- The following cursor determines the master attribute of the passed attribute
--
Cursor csr_master_attribute is
Select master_attribute_id
From pqh_attributes
Where attribute_id = p_attribute_id;
Select tca.txn_category_attribute_id,tca.object_version_number
From pqh_txn_category_attributes tca
Where (tca.transaction_category_id = p_transaction_category_id and
tca.attribute_id = p_master_attribute
) OR
tca.txn_category_attribute_id = p_txn_category_attribute_id
for update nowait;
l_proc varchar2(72) := 'unselect_routing_attribute';
pqh_txn_cat_attributes_api.update_TXN_CAT_ATTRIBUTE
(
p_validate => false
,p_txn_category_attribute_id => child_rec.txn_category_attribute_id
,p_object_version_number => child_rec.object_version_number
,p_list_identifying_flag => 'N'
,p_value_style_cd => 'RANGE'
,p_effective_date => sysdate
,p_delete_attr_ranges_flag => 'I'
);
PROCEDURE select_authorization_attribute
(p_txn_category_attribute_id in number,
p_attribute_id in number,
p_transaction_category_id in number) is
--
-- The following cursor determines the ovn of the master attribute id
--
Cursor csr_master_attribute is
Select tca.object_version_number
From pqh_txn_category_attributes tca
Where txn_category_attribute_id = p_txn_category_attribute_id
for update nowait;
Select tca.txn_category_attribute_id,tca.object_version_number
From pqh_txn_category_attributes tca
Where tca.transaction_category_id = p_transaction_category_id
and tca.attribute_id in
(Select attribute_id
From pqh_attributes
Where master_attribute_id = p_attribute_id)
for update nowait;
l_proc varchar2(72) := 'select_authorization_attribute';
pqh_txn_cat_attributes_api.update_TXN_CAT_ATTRIBUTE
(
p_validate => false
,p_txn_category_attribute_id => p_txn_category_attribute_id
,p_object_version_number => l_ovn
,p_member_identifying_flag => 'Y'
,p_value_style_cd => 'RANGE'
,p_effective_date => sysdate
,p_delete_attr_ranges_flag => 'N'
);
pqh_txn_cat_attributes_api.update_TXN_CAT_ATTRIBUTE
(
p_validate => false
,p_txn_category_attribute_id => child_rec.txn_category_attribute_id
,p_object_version_number => child_rec.object_version_number
,p_member_identifying_flag => 'Y'
,p_value_style_cd => 'RANGE'
,p_effective_date => sysdate
,p_delete_attr_ranges_flag => 'N'
);
PROCEDURE unselect_auth_attribute
(p_txn_category_attribute_id in number,
p_attribute_id in number,
p_transaction_category_id in number) is
--
-- The following cursor determines the master attribute of the passed attribute
--
Cursor csr_master_attribute is
Select master_attribute_id
From pqh_attributes
Where attribute_id = p_attribute_id;
Select tca.txn_category_attribute_id,tca.object_version_number
From pqh_txn_category_attributes tca
Where (tca.transaction_category_id = p_transaction_category_id and
tca.attribute_id = p_master_attribute
) OR
tca.txn_category_attribute_id = p_txn_category_attribute_id
for update nowait;
l_proc varchar2(72) := 'unselect_auth_attribute';
pqh_txn_cat_attributes_api.update_TXN_CAT_ATTRIBUTE
(
p_validate => false
,p_txn_category_attribute_id => child_rec.txn_category_attribute_id
,p_object_version_number => child_rec.object_version_number
,p_member_identifying_flag => 'N'
,p_value_style_cd => 'RANGE'
,p_effective_date => sysdate
,p_delete_attr_ranges_flag => 'I'
);
Select rct.routing_category_id
From pqh_routing_categories rct
Where rct.transaction_category_id = p_transaction_category_id
and nvl(rct.default_flag,'N') <> 'Y' ;
Select attribute_id
from pqh_txn_category_attributes ptca
Where transaction_category_id = p_transaction_category_id
and list_identifying_flag = 'Y'
and not exists
(Select null
From pqh_attribute_ranges rng,pqh_routing_categories rct
Where rct.transaction_category_id = p_transaction_category_id
and nvl(rct.default_flag,'N') <> 'Y'
and rct.routing_category_id = rng.routing_category_id
and routing_list_member_id IS NULL
and position_id IS NULL
and assignment_id IS NULL
and ptca.attribute_id = rng.attribute_id);
Select distinct rng.range_name,rng.enable_flag, nvl(rng.delete_flag,'N') delete_flag
From pqh_attribute_ranges rng
Where rng.routing_category_id = p_routing_category_id
and routing_list_member_id IS NULL
and position_id IS NULL
and assignment_id IS NULL
and attribute_id IS NOT NULL;
Select attribute_id
from pqh_txn_category_attributes tca
Where transaction_category_id = p_transaction_category_id
and member_identifying_flag = 'Y'
and not exists
(Select null
From pqh_attribute_ranges rng,pqh_routing_categories rct
Where rct.transaction_category_id = p_transaction_category_id
and nvl(rct.default_flag,'N') <> 'Y'
and rct.routing_category_id = rng.routing_category_id
and (routing_list_member_id IS NOT NULL or
position_id IS NOT NULL or
assignment_id IS NOT NULL)
and tca.attribute_id = rng.attribute_id);
Select distinct
decode(routing_list_member_id,NULL,decode(position_id,NULL,'S','P'),'R') routing_style ,
nvl(routing_list_member_id,nvl(position_id,assignment_id)) member_id,
rng.range_name,
rng.approver_flag,
rng.enable_flag,
nvl(rng.delete_flag,'N') delete_flag
From pqh_attribute_ranges rng
Where rng.routing_category_id = p_routing_category_id
and (routing_list_member_id IS NOT NULL or
position_id IS NOT NULL or
assignment_id IS NOT NULL)
and attribute_id IS NOT NULL;
,p_delete_flag => rule_rec.delete_flag
,p_attribute_id => attr_rec.attribute_id
,p_range_name => rule_rec.range_name
,p_routing_category_id => rct_rec.routing_category_id
,p_object_version_number => l_ovn
,p_effective_date => sysdate
);
,p_delete_flag => rule_rec.delete_flag
,p_attribute_id => attr_rec.attribute_id
,p_range_name => rule_rec.range_name
,p_routing_category_id => rct_rec.routing_category_id
,p_routing_list_member_id => l_routing_list_member_id
,p_position_id => l_position_id
,p_assignment_id => l_assignment_id
,p_approver_flag => rule_rec.approver_flag
,p_object_version_number => l_ovn
,p_effective_date => sysdate
);
Select rct.routing_category_id
From pqh_routing_categories rct
Where rct.transaction_category_id = p_transaction_category_id;
Select rng.attribute_range_id,rng.object_version_number
From pqh_attribute_ranges rng
Where rng.routing_category_id = p_routing_category_id
and routing_list_member_id IS NULL
and position_id IS NULL
and assignment_id IS NULL
and attribute_id IS NOT NULL
and attribute_id not in
(Select attribute_id
from pqh_txn_category_attributes
Where transaction_category_id = p_transaction_category_id
and list_identifying_flag = 'Y');
SELECT ATTRIBUTE_ID FROM PQH_TXN_CATEGORY_ATTRIBUTES
WHERE TRANSACTION_CATEGORY_ID = p_transaction_category_id AND LIST_IDENTIFYING_FLAG = 'Y';
SELECT RNG.ATTRIBUTE_RANGE_ID,RNG.OBJECT_VERSION_NUMBER,RNG.ATTRIBUTE_ID
FROM PQH_ATTRIBUTE_RANGES RNG
WHERE RNG.ROUTING_CATEGORY_ID = p_routing_category_id
and routing_list_member_id IS NULL
and position_id IS NULL
and assignment_id IS NULL
AND ATTRIBUTE_ID IS NOT NULL ;
hr_utility.set_location('calling delete_ATTRIBUTE_RANGE', 5);
pqh_ATTRIBUTE_RANGES_api.delete_ATTRIBUTE_RANGE
(p_validate => false
,p_attribute_range_id => old_rec.attribute_range_id
,p_object_version_number => old_rec.object_version_number
,p_effective_date => sysdate);
Select rct.routing_category_id
From pqh_routing_categories rct
Where rct.transaction_category_id = p_transaction_category_id;
Select rng.attribute_range_id,rng.object_version_number
From pqh_attribute_ranges rng
Where rng.routing_category_id = p_routing_category_id
and (routing_list_member_id IS NOT NULL or
position_id IS NOT NULL or
assignment_id IS NOT NULL)
and attribute_id IS NOT NULL
and attribute_id not in
(Select attribute_id
from pqh_txn_category_attributes
Where transaction_category_id = p_transaction_category_id
and member_identifying_flag = 'Y');
SELECT ATTRIBUTE_ID FROM PQH_TXN_CATEGORY_ATTRIBUTES
WHERE TRANSACTION_CATEGORY_ID = p_transaction_category_id AND MEMBER_IDENTIFYING_FLAG = 'Y';
SELECT RNG.ATTRIBUTE_RANGE_ID,RNG.OBJECT_VERSION_NUMBER,RNG.ATTRIBUTE_ID
FROM PQH_ATTRIBUTE_RANGES RNG
WHERE RNG.ROUTING_CATEGORY_ID = p_routing_category_id
AND (ROUTING_LIST_MEMBER_ID IS NOT NULL OR POSITION_ID IS NOT NULL OR ASSIGNMENT_ID IS NOT NULL)
AND ATTRIBUTE_ID IS NOT NULL ;
pqh_ATTRIBUTE_RANGES_api.delete_ATTRIBUTE_RANGE
(p_validate => false
,p_attribute_range_id => old_rec.attribute_range_id
,p_object_version_number => old_rec.object_version_number
,p_effective_date => sysdate);
Select attribute_id
from pqh_txn_category_attributes
Where transaction_category_id = p_transaction_category_id
and nvl(identifier_flag,'N') = 'Y'
and list_identifying_flag = 'Y';
Select routing_category_id,object_version_number
from pqh_routing_categories
Where transaction_category_id = p_transaction_category_id
and decode(routing_list_id,NULL,decode( Position_structure_id,NULL,'S','P'),'R') = (Select member_cd
from pqh_transaction_categories
Where transaction_category_id = p_transaction_category_id)
and nvl(default_flag,'N') <> 'Y'
and nvl(enable_flag,'Y') = 'Y';
pqh_ROUTING_CATEGORIES_api.update_ROUTING_CATEGORY
(
p_validate => false
,p_routing_category_id => old_rec.routing_category_id
,p_enable_flag => 'N'
,p_object_version_number => old_rec.object_version_number
,p_effective_date => sysdate
);
Select null
From pqh_attribute_ranges
Where routing_category_id = p_routing_category_id
and attribute_range_id IS NOT NULL;
Select null
From pqh_routing_history
Where routing_category_id = p_routing_category_id;
Select attribute_range_id
From pqh_attribute_ranges
Where routing_category_id = p_routing_category_id
and range_name = p_range_name
and routing_list_member_id is NULL
and position_id IS NULL
and assignment_id IS NULL;
Select attribute_range_id
From pqh_attribute_ranges
Where routing_category_id = p_routing_category_id
and range_name = p_range_name
and (routing_list_member_id is NOT NULL or
position_id IS NOT NULL or
assignment_id IS NOT NULL);
p_delete_flag in varchar2,
p_enable_flag in varchar2,
p_all_attribute_range_id out nocopy varchar2) is
--
Cursor csr_rout_attr is
Select attribute_id
from pqh_txn_category_attributes
Where transaction_category_id = p_transaction_category_id
and list_identifying_flag = 'Y';
,p_delete_flag => p_delete_flag
,p_attribute_id => attr_rec.attribute_id
,p_range_name => p_range_name
,p_routing_category_id => p_routing_category_id
,p_object_version_number => l_ovn
,p_effective_date => sysdate
);
PROCEDURE update_rule(p_routing_category_id in number,
p_range_name in varchar2,
p_enable_flag in varchar2,
p_delete_flag in varchar2 default NULL,
p_approver_flag in varchar2 default NULL,
p_all_attribute_range_id in varchar2) is
--
type cur_type IS REF CURSOR;
csr_update_rule cur_type;
l_proc varchar2(72) := 'update_rule';
sql_stmt := 'Select * from pqh_attribute_ranges where attribute_range_id in ('
|| l_all_attribute_range_id
||') for update nowait';
Open csr_update_rule for sql_stmt;
Fetch csr_update_rule into att_range_rec;
If csr_update_rule%NOTFOUND then
Exit;
pqh_attribute_ranges_api.update_ATTRIBUTE_RANGE
(
p_validate => false
,p_attribute_range_id => att_range_rec.attribute_range_id
,p_approver_flag => p_approver_flag
,p_enable_flag => p_enable_flag
,p_delete_flag => p_delete_flag
,p_range_name => p_range_name
,p_routing_category_id => p_routing_category_id
,p_object_version_number => att_range_rec.object_version_number
,p_effective_date => sysdate
);
Close csr_update_rule;
PROCEDURE update_routing_rule(p_routing_category_id in number,
p_range_name in varchar2,
p_enable_flag in varchar2,
p_approver_flag in varchar2 default NULL,
p_delete_flag in varchar2 default NULL,
p_all_attribute_range_id in varchar2) is
--
l_proc varchar2(72) := 'update_routing_rule';
update_rule(p_routing_category_id => p_routing_category_id,
p_range_name => p_range_name,
p_enable_flag => p_enable_flag,
p_delete_flag => p_delete_flag,
p_approver_flag => p_approver_flag,
p_all_attribute_range_id => p_all_attribute_range_id);
PROCEDURE delete_rule(p_routing_category_id in number,
p_all_attribute_range_id in varchar2) is
--
type cur_type IS REF CURSOR;
csr_delete_rule cur_type;
l_proc varchar2(72) := 'delete_rule';
sql_stmt := 'Select attribute_range_id,object_version_number from pqh_attribute_ranges where routing_category_id = :routing_category_id and attribute_range_id in ('
|| l_all_attribute_range_id
||') for update nowait';
Open csr_delete_rule for sql_stmt using p_routing_category_id;
Fetch csr_delete_rule into l_id,l_ovn;
If csr_delete_rule%NOTFOUND then
Exit;
pqh_attribute_ranges_api.delete_ATTRIBUTE_RANGE
(
p_validate => false
,p_attribute_range_id => l_id
,p_object_version_number => l_ovn
,p_effective_date => sysdate
);
Close csr_delete_rule;
PROCEDURE delete_routing_rule(p_routing_category_id in number,
p_all_attribute_range_id in varchar2) is
--
l_proc varchar2(72) := 'delete_routing_rule';
delete_rule(p_routing_category_id => p_routing_category_id,
p_all_attribute_range_id => p_all_attribute_range_id);
Select attribute_id
from pqh_txn_category_attributes
Where transaction_category_id = p_transaction_category_id
and member_identifying_flag = 'Y';
Select attribute_range_id,object_version_number
from pqh_attribute_ranges
Where routing_category_id = p_routing_category_id
And nvl(routing_list_member_id,-99) = nvl(p_routing_list_member_id,-99)
And nvl(position_id,-99) = nvl(p_position_id,-99)
And nvl(p_assignment_id,-99) = nvl(p_assignment_id,-99)
And attribute_id is not null
And enable_flag = 'N';
pqh_attribute_ranges_api.update_attribute_range(
p_validate => false
,p_attribute_range_id => exist_appr_rec.attribute_range_id
,p_approver_flag => 'Y'
,p_enable_flag => 'Y'
,p_delete_flag => NULL
,p_object_version_number => exist_appr_rec.object_version_number
,p_effective_date => trunc(sysdate));
,p_delete_flag => NULL
,p_approver_flag => p_approver_flag
,p_object_version_number => l_ovn
,p_effective_date => sysdate
);
PROCEDURE update_approver (p_routing_category_id in number,
p_routing_style in varchar2,
p_routing_list_member_id in number,
p_position_id in number,
p_assignment_id in number,
p_approver_flag in varchar2 ) is
--
type cur_type IS REF CURSOR;
csr_update_approver cur_type;
l_proc varchar2(72) := 'update_approver';
sql_stmt := 'Select attribute_range_id,object_version_number from pqh_attribute_ranges where routing_category_id = :routing_category_id and attribute_id is not null and ';
sql_stmt := sql_stmt || ' For update nowait';
Open csr_update_approver for sql_stmt using p_routing_category_id,l_dummy_id;
Fetch csr_update_approver into l_id,l_ovn;
If csr_update_approver%notfound then
exit;
pqh_attribute_ranges_api.update_ATTRIBUTE_RANGE
(
p_validate => false
,p_attribute_range_id => l_id
,p_approver_flag => p_approver_flag
,p_enable_flag => 'Y'
,p_delete_flag => NULL
,p_routing_category_id => p_routing_category_id
,p_object_version_number => l_ovn
,p_effective_date => sysdate
);
Close csr_update_approver;
PROCEDURE delete_approver (p_routing_category_id in number,
p_routing_style in varchar2,
p_routing_list_member_id in number,
p_position_id in number,
p_assignment_id in number ) is
--
type cur_type IS REF CURSOR;
csr_delete_approver cur_type;
l_proc varchar2(72) := 'delete_approver';
sql_stmt := 'Select attribute_range_id,object_version_number from pqh_attribute_ranges where routing_category_id = :routing_category_id and attribute_id is not null and ';
sql_stmt := sql_stmt || ' For update nowait';
Open csr_delete_approver for sql_stmt using p_routing_category_id,l_dummy_id;
Fetch csr_delete_approver into l_id,l_ovn;
If csr_delete_approver%notfound then
exit;
pqh_attribute_ranges_api.delete_ATTRIBUTE_RANGE
(
p_validate => false
,p_attribute_range_id => l_id
,p_object_version_number => l_ovn
,p_effective_date => sysdate
);
Close csr_delete_approver;
PROCEDURE update_approver_flag(p_routing_category_id in number,
p_routing_style in varchar2,
p_routing_list_member_id in number,
p_position_id in number,
p_assignment_id in number,
p_approver_flag in varchar2 ) is
--
type cur_type IS REF CURSOR;
csr_delete_approver cur_type;
l_proc varchar2(72) := 'update_approver_flag';
sql_stmt := 'Select attribute_range_id,object_version_number,approver_flag from pqh_attribute_ranges where routing_category_id = :routing_category_id and attribute_id is not null and ';
sql_stmt := sql_stmt || ' For update nowait';
Open csr_delete_approver for sql_stmt using p_routing_category_id,l_dummy_id;
Fetch csr_delete_approver into l_id,l_ovn,l_appr_flag;
If csr_delete_approver%notfound then
exit;
pqh_attribute_ranges_api.update_ATTRIBUTE_RANGE
(p_validate => false
,p_attribute_range_id => l_id
,p_approver_flag => p_approver_flag
,p_object_version_number => l_ovn
,p_effective_date => sysdate
);
Close csr_delete_approver;
p_delete_flag in varchar2,
p_enable_flag in varchar2,
p_range_name in varchar2,
p_all_attribute_range_id out nocopy varchar2) is
--
Cursor csr_auth_attr is
Select attribute_id
from pqh_txn_category_attributes
Where transaction_category_id = p_transaction_category_id
and member_identifying_flag = 'Y';
Select attribute_range_id,object_version_number
From pqh_attribute_ranges
Where routing_category_id = p_routing_category_id
and range_name like 'PQH_$$SYS$$%'
and attribute_id is NOT NULL
and nvl(routing_list_member_id,-99) = nvl(p_routing_list_member_id,-99)
and nvl(position_id,-99) = nvl(p_position_id,-99)
and nvl(assignment_id ,-99) = nvl(p_assignment_id,-99)
For update nowait;
,p_delete_flag => p_delete_flag
,p_approver_flag => p_approver_flag
,p_object_version_number => l_ovn
,p_effective_date => sysdate
);
pqh_attribute_ranges_api.delete_ATTRIBUTE_RANGE
(
p_validate => false
,p_attribute_range_id => sys_rec.attribute_range_id
,p_object_version_number => sys_rec.object_version_number
,p_effective_date => sysdate
);
PROCEDURE update_authorization_rule
(p_routing_category_id in number,
p_range_name in varchar2,
p_enable_flag in varchar2,
p_approver_flag in varchar2 default NULL,
p_delete_flag in varchar2 default NULL,
p_all_attribute_range_id in varchar2) is
--
l_proc varchar2(72) := 'update_authorization_rule';
update_rule(p_routing_category_id => p_routing_category_id,
p_range_name => p_range_name,
p_enable_flag => p_enable_flag,
p_approver_flag => p_approver_flag,
p_delete_flag => p_delete_flag,
p_all_attribute_range_id => p_all_attribute_range_id);
PROCEDURE delete_authorization_rule (p_routing_category_id in number,
p_all_attribute_range_id in varchar2) is
--
--
l_proc varchar2(72) := 'delete_authorization_rule';
delete_rule(p_routing_category_id => p_routing_category_id,
p_all_attribute_range_id => p_all_attribute_range_id);
SELECT *
FROM PQH_TRANSACTION_CATEGORIES
WHERE TRANSACTION_CATEGORY_ID = p_transaction_category_id
AND BUSINESS_GROUP_ID IS NULL;
SELECT *
FROM PQH_ROUTING_CATEGORIES
WHERE TRANSACTION_CATEGORY_ID = p_transaction_category_id
AND NVL(ENABLE_FLAG, 'N') = 'Y'
AND NVL(DEFAULT_FLAG,'N') = 'Y';
SELECT *
FROM pqh_attribute_ranges
WHERE ROUTING_CATEGORY_ID = p_routing_category_id
AND NVL(ENABLE_FLAG, 'N')= 'Y'
AND NVL(APPROVER_FLAG,'N')= 'Y';
Select *
from pqh_txn_category_attributes
Where transaction_category_id = p_transaction_category_id;
,p_select_flag => attr_rec.select_flag
,p_value_style_cd => attr_rec.value_style_cd
,p_effective_date => sysdate
);
Select pqh_routing_categories_s.nextval into rct_id from dual;
insert into pqh_routing_categories (
routing_category_id,
transaction_category_id,
enable_flag,
default_flag,
routing_list_id,
position_structure_id,
override_position_id,
override_assignment_id,
override_role_id,
object_version_number)
Values (
rct_id,
tct_id,
rct_rec.enable_flag,
rct_rec.default_flag,
rct_rec.routing_list_id,
rct_rec.position_structure_id,
rct_rec.override_position_id,
rct_rec.override_assignment_id,
rct_rec.override_role_id,
1);
Select pqh_attribute_ranges_s.nextval into rng_id from dual;
insert into pqh_attribute_ranges(
attribute_range_id,
approver_flag,
enable_flag,
assignment_id,
attribute_id,
from_char,
from_date,
from_number,
position_id,
range_name,
routing_category_id,
routing_list_member_id,
to_char,
to_date,
to_number,
object_version_number)
Values(
rng_id,
rng_rec.approver_flag,
rng_rec.enable_flag,
rng_rec.assignment_id,
rng_rec.attribute_id,
rng_rec.from_char,
rng_rec.from_date,
rng_rec.from_number,
rng_rec.position_id,
rng_rec.range_name,
rct_id,
rng_rec.routing_list_member_id,
rng_rec.to_char,
rng_rec.to_date,
rng_rec.to_number,
1);
Update pqh_transaction_categories
set freeze_status_cd = p_freeze_status_cd
,setup_type_cd = p_setup_type_cd
where transaction_category_id = p_transaction_category_id;
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 || ') ';
p_last_update_date in varchar2 ) IS
--
l_language varchar2(30) ;
l_last_updated_by pqh_wizard_canvases.last_updated_by%TYPE;
l_last_update_date pqh_wizard_canvases.last_update_date%TYPE;
l_last_update_login pqh_wizard_canvases.last_update_login%TYPE;
cursor c1 is select userenv('LANG') from dual ;
select rowid
from pqh_wizard_canvases
where canvas_name = p_canvas_name
and form_name = p_form_name;
l_last_updated_by := 1;
l_last_updated_by := 0;
l_last_updated_by := fnd_load_util.owner_id(p_owner);
l_last_update_date := sysdate;
l_creation_date := nvl(to_date(p_last_update_date,'YYYY/MM/DD'),trunc(sysdate));
l_last_update_date := nvl(to_date(p_last_update_date,'YYYY/MM/DD'),trunc(sysdate));
l_last_update_login := 0;
select pqh_wizard_canvases_s.NEXTVAL into l_wizard_canvas_id from dual;
insert into pqh_wizard_canvases (
WIZARD_CANVAS_ID ,
CANVAS_NAME ,
CURRENT_ITEM ,
PREVIOUS_ITEM ,
NEXT_ITEM ,
ENABLE_FINISH_FLAG ,
POST_FLAG ,
SEQ_NO ,
FINISH_ITEM ,
REFRESH_MSG_FLAG ,
FORM_NAME ,
IMAGE_NAME ,
WARNING_ITEM ,
IMAGE_ITEM ,
LINE_SIZE ,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE )
values (
l_wizard_canvas_id ,
P_CANVAS_NAME ,
P_CURRENT_ITEM ,
P_PREVIOUS_ITEM ,
P_NEXT_ITEM ,
P_ENABLE_FINISH_FLAG ,
P_POST_FLAG ,
P_SEQ_NO ,
P_FINISH_ITEM ,
P_REFRESH_MSG_FLAG ,
P_FORM_NAME ,
P_IMAGE_NAME ,
P_WARNING_ITEM ,
P_IMAGE_ITEM ,
P_LINE_SIZE ,
l_last_update_date,
l_last_updated_by,
l_last_update_login,
l_created_by,
l_creation_date );
update pqh_wizard_canvases
set
CANVAS_NAME = P_CANVAS_NAME ,
CURRENT_ITEM = P_CURRENT_ITEM ,
PREVIOUS_ITEM = P_PREVIOUS_ITEM ,
NEXT_ITEM = P_NEXT_ITEM ,
ENABLE_FINISH_FLAG = P_ENABLE_FINISH_FLAG ,
POST_FLAG = P_POST_FLAG ,
SEQ_NO = P_SEQ_NO ,
FINISH_ITEM = P_FINISH_ITEM ,
REFRESH_MSG_FLAG = P_REFRESH_MSG_FLAG ,
FORM_NAME = P_FORM_NAME ,
IMAGE_NAME = P_IMAGE_NAME ,
WARNING_ITEM = P_WARNING_ITEM ,
IMAGE_ITEM = P_IMAGE_ITEM ,
LINE_SIZE = P_LINE_SIZE ,
LAST_UPDATE_DATE = l_LAST_UPDATE_DATE ,
LAST_UPDATED_BY = l_LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN = l_LAST_UPDATE_LOGIN ,
CREATED_BY = l_CREATED_BY ,
CREATION_DATE = l_CREATION_DATE
where ROWID = l_rowid ;
Select member_cd
from pqh_transaction_categories
Where transaction_category_id = p_transaction_category_id;
Select routing_category_id
from pqh_routing_categories_v
where member_cd = p_member_cd
and transaction_category_id = p_transaction_category_id
and nvl(enable_flag,'Y') = 'Y'
and nvl(default_flag,'N') = 'Y';
Select null
from pqh_attribute_ranges_v3
Where routing_category_id = p_routing_category_id
and nvl(approver_flag,'N') = 'Y'
and nvl(enable_flag,'Y') = 'Y';
delete_flag pqh_routing_categories.delete_flag%type);
Select rct.routing_category_id,rct.default_flag, rct.delete_flag
from pqh_routing_categories rct
Where rct.transaction_category_id = p_transaction_category_id
and rct.enable_flag = 'Y'
and rct.position_structure_id IS NOT NULL;
Select rct.routing_category_id,rct.default_flag, rct.delete_flag
from pqh_routing_categories rct
Where rct.transaction_category_id = p_transaction_category_id
and rct.enable_flag = 'Y'
and rct.routing_list_id IS NULL and rct.position_structure_id IS NULL;
Select rct.routing_category_id,rct.default_flag, rct.delete_flag
from pqh_routing_categories rct
Where rct.transaction_category_id = p_transaction_category_id
and rct.enable_flag = 'Y'
and rct.routing_list_id IS NOT NULL;
Select 'x' from pqh_attribute_ranges rng
Where rng.routing_category_id = p_routing_category_id
and rng.enable_flag = 'Y'
and nvl(delete_flag,'N') <> 'Y'
and rng.routing_list_member_id IS NULL
and rng.position_id IS NULL
and rng.assignment_id IS NULL;
If nvl(rl_rec.default_flag,'X') <> 'Y' and nvl(rl_rec.delete_flag,'X') <> 'Y' then
l_cnt := l_cnt + 1;
If nvl(ph_rec.default_flag,'X') <> 'Y' and nvl(ph_rec.delete_flag,'X') <> 'Y' then
l_cnt := l_cnt + 1;
If nvl(sh_rec.default_flag,'X') <> 'Y' and nvl(sh_rec.delete_flag,'X') <> 'Y' then
l_cnt := l_cnt + 1;
sql_stmt := 'Select rct.routing_category_id, count(rng.range_name)'
|| ' from pqh_routing_categories rct,pqh_attribute_ranges rng'
|| ' Where rct.transaction_category_id = :p_transaction_category_id'
|| ' and rct.enable_flag = :p_enable_flag'
|| ' and nvl(rct.default_flag,:null1) <> :p_default_flag'
|| ' and nvl(rct.delete_flag,:null2) <> :p_delete_flag';
|| ' and nvl(rng.delete_flag(+),:null3) <> :p_rule_delete'
|| ' and rng.routing_list_member_id(+) IS NULL'
|| ' and rng.position_id(+) IS NULL'
|| ' and rng.assignment_id(+) IS NULL'
|| ' group by rct.routing_category_id'
|| ' order by rct.routing_category_id';
Select rct.routing_category_id,default_flag,delete_flag
From pqh_routing_categories rct
Where rct.transaction_category_id = :p_transaction_category_id
and rct.enable_flag = 'Y'
and rct.routing_list_id is not null
**/
Begin
--
hr_utility.set_location('Entering:'||l_proc, 5);
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_value) <> :default_flag and nvl(rct.delete_flag,:null2) <> :delete_flag ';
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 ,';
sql_stmt := sql_stmt ||' Where rct.transaction_category_id = :p_transaction_category_id and rct.enable_flag = :enable_flag and nvl(rct.default_flag,:null_value) <> :default_flag and nvl(rct.delete_flag,:null2) <> :delete_flag ';
sql_stmt := sql_stmt || ' and rng.enable_flag = :rule_enable and nvl(rng.delete_flag,:null3) <> :delete_flag ';
Select member_Cd
From pqh_transaction_categories
Where transaction_category_id = p_transaction_category_id;
Select member_cd
from pqh_transaction_categories
Where transaction_category_id = p_transaction_category_id;
Select routing_category_id
from pqh_routing_categories_v a
where a.transaction_category_id = p_transaction_category_id
and a.member_cd = p_member_cd
and nvl(a.enable_flag,'Y') = 'Y'
and nvl(a.delete_flag,'N') <> 'Y'
and nvl(a.default_flag,'N') <> 'Y';
Procedure delete_hierarchy_and_rules(p_transaction_category_id in number,
p_routing_style in varchar2) is
--
--
type cur_type IS REF CURSOR;
l_proc varchar2(72) := 'delete_hierarchy_and_rules';
sql_stmt1 := 'Delete From pqh_attribute_ranges rng Where';
|| ' Select routing_category_id '
|| ' from pqh_routing_categories rct '
||' Where rct.transaction_category_id = :p_transaction_category_id and nvl(rct.default_flag,:null_value) <> :default_flag and nvl(rct.delete_flag,:null2) = :delete_flag ';
sql_stmt1 := 'Delete From pqh_routing_categories rct';
sql_stmt1 := sql_stmt1 ||' Where rct.transaction_category_id = :p_transaction_category_id and nvl(rct.default_flag,:null_value) <> :default_flag and nvl(rct.delete_flag,:null2) = :delete_flag ';
sql_stmt1 := 'Delete From pqh_attribute_ranges rng '
||' Where nvl(rng.delete_flag,:null1) = :delete_flag ';
||' Select routing_category_id '
||' from pqh_routing_categories rct '
||' Where rct.transaction_category_id = :p_transaction_category_id ';
END delete_hierarchy_and_rules;
sql_stmt1 := 'Select approver_flag From pqh_attribute_ranges rng '
|| ' Where rng.routing_category_id = :routing_category_id';
Select full_name
from per_all_assignments_f ASG, per_all_people_f PPL, fnd_sessions ses
Where asg.assignment_id = p_assignment_id
AND asg.person_id = PPL.person_id
AND SES.SESSION_ID = USERENV('sessionid')
AND SES.EFFECTIVE_DATE BETWEEN ASG.EFFECTIVE_START_DATE AND ASG.EFFECTIVE_END_DATE
AND SES.EFFECTIVE_DATE BETWEEN PPL.EFFECTIVE_START_DATE AND PPL.EFFECTIVE_END_DATE;