The following lines contain the word 'select', 'insert', 'update' or 'delete':
select name from ame_approval_groups where
approval_group_id = p_group_id;
PROCEDURE insert_ame_approver(
p_application_id in number,
p_transaction_type in varchar2,
p_transaction_id in varchar2,
p_approverIn in ame_approver_record2_table_ss,
p_positionIn in number,
p_insertionIn in ame_insertion_record2_table_ss,
p_warning_msg_name OUT NOCOPY varchar2,
p_error_msg_text OUT NOCOPY varchar2
) as
PRAGMA AUTONOMOUS_TRANSACTION;
l_proc constant varchar2(100) := 'insert_ame_approver';
c_insertion_record2 ame_util.insertionRecord2;
hr_utility.set_location('Entering: insert_ame_approver', 1);
hr_utility.trace('inserting approver ' || p_approverIn(1).name);
hr_utility.trace('inserting approver id= ' || p_approverIn(1).orig_system_id);
c_approver_rec2.item_class := p_insertionIn(1).item_class;
c_approver_rec2.item_id := p_insertionIn(1).item_id;
c_approver_rec2.action_type_id := p_insertionIn(1).action_type_id;
c_approver_rec2.group_or_chain_id := p_insertionIn(1).group_or_chain_id;
c_approver_rec2.api_insertion := p_insertionIn(1).api_insertion;
c_approver_rec2.authority := p_insertionIn(1).authority;
c_insertion_record2.item_class := p_insertionIn(1).item_class;
c_insertion_record2.item_id := p_insertionIn(1).item_id;
c_insertion_record2.action_type_id := p_insertionIn(1).action_type_id;
c_insertion_record2.group_or_chain_id := p_insertionIn(1).group_or_chain_id;
c_insertion_record2.api_insertion := p_insertionIn(1).api_insertion;
c_insertion_record2.authority := p_insertionIn(1).authority;
c_insertion_record2.order_type := p_insertionIn(1).order_type;
lv_parameter := p_insertionIn(1).parameter;
c_insertion_record2.parameter := lv_parameter;
c_insertion_record2.description := p_insertionIn(1).description;
if (ame_util.firstAuthority = c_insertion_record2.order_type) then
begin
-- for now, we are not inserting COA first Authoriry approvers
-- setFirstAuthority is accepting only Authority Insertion, but
-- getAvailableOption returning 'Y'
c_approver_rec2.api_insertion := 'A';
c_insertion_record2.action_type_id := ame_util.nullInsertionActionTypeId;
c_insertion_record2.group_or_chain_id := ame_util.nullInsertionGroupOrChainId;
c_approver_rec2.action_type_id := ame_util.nullInsertionActionTypeId;
c_approver_rec2.group_or_chain_id := ame_util.nullInsertionGroupOrChainId;
ame_api3.insertApprover(applicationIdIn =>p_application_id,
transactionIdIn =>p_transaction_id,
approverIn =>c_approver_rec2,
positionIn => p_positionIn,
insertionIn =>c_insertion_record2,
transactionTypeIn=>p_transaction_type );
hr_utility.set_location('Leaving: insert_ame_approver', 2);
END insert_ame_approver;
PROCEDURE delete_ame_approver(
p_application_id in number,
p_transaction_type in varchar2,
p_transaction_id in varchar2,
p_approverIn in ame_approver_record2_table_ss,
p_warning_msg_name OUT NOCOPY varchar2,
p_error_msg_text OUT NOCOPY varchar2
)
as
PRAGMA AUTONOMOUS_TRANSACTION;
l_proc constant varchar2(100) := 'delete_ame_approver';
hr_utility.set_location('Entering: delete_ame_approver', 1);
c_approver_rec2.api_insertion := p_approverIn(1).api_insertion;
if(p_approverIn(1).api_Insertion = ame_util.apiInsertion) then
begin
-- API Inserrted Adhoc approver, so call clearInsertion
ame_api3.clearInsertion(applicationIdIn =>p_application_id,
transactionTypeIn => p_transaction_type,
transactionIdIn =>p_transaction_id,
approverIn =>c_approver_rec2
);
hr_utility.set_location('Leaving: delete_ame_approver', 2);
END delete_ame_approver;
p_coa_insertions_flag in varchar2 default 'N',
p_ame_approvers_list OUT NOCOPY ame_approver_record2_table_ss,
p_ame_order_type_list OUT NOCOPY ame_insertion_record2_table_ss,
-- We need this value to add "Append to list" option
p_all_approvers_count out NOCOPY varchar2,
p_warning_msg_name OUT NOCOPY varchar2,
p_error_msg_text OUT NOCOPY varchar2
)
is
--local variables
l_proc constant varchar2(100) := 'get_ame_apprs_and_ins_list';
select description
from fnd_lookups
where lookup_type like 'AME_DA_ACTIVE_ORDER_TYPE' and lookup_code = p_order_type;
select meaning
from fnd_lookups
where lookup_type like 'AME_APPR_INSERTION_ORDER_TYPE' and lookup_code = p_order_type;
select meaning
from fnd_lookups
where lookup_type like 'AME_APPROVAL_STATUS' and lookup_code = p_status;
select meaning
from fnd_lookups
where lookup_type like 'AME_APPROVER_CATEGORY' and lookup_code = p_category;
l_default_insertions_list ame_insertion_record2_table_ss := ame_insertion_record2_table_ss();
l_default_insertion ame_insertion_record2_ss;
c_all_insertions ame_util2.insertionsTable3;
ln_insertions_index NUMBER;
ln_insertion_record2_num number;
allow_delete varchar2(30);
ame_api5.getAllApproversAndInsertions(applicationIdIn =>p_application_id,
transactionIdIn=>p_transaction_id,
transactionTypeIn =>p_transaction_type,
activeApproversYNIn => bactiveApproversYNIn,
-- currently not getting COA approvers
coaInsertionsYNIN => ame_util.booleanFalse,
approvalProcessCompleteYNOut =>lv_approval_status,
approversOut=>c_all_approvers,
availableInsertionsOut => c_all_insertions);
ln_insertion_record2_num := 0;
allow_delete := 'AMEDeleteEnabled';
if(c_all_approvers(tempIndex).api_insertion = ame_util.oamGenerated or
sourceDescription = ame_util.ruleGeneratedSource ) then
begin
allowDeletingOamApprovers :=
ame_engine.getHeaderAttValue2(attributeNameIn => ame_util.allowDeletingOamApprovers);
allow_delete := 'AMEDeleteDisabled';
allow_delete := 'AMEDeleteDisabled';
c_all_approvers(tempIndex).api_insertion,
c_all_approvers(tempIndex).authority, -- authority
ln_apr_status, --c_all_approvers(i).approval_status,
c_all_approvers(tempIndex).action_type_id,
c_all_approvers(tempIndex).group_or_chain_id, -- group_or_chain_id
c_all_approvers(tempIndex).occurrence, -- occurrence
null, -- source
c_all_approvers(tempIndex).item_class, -- item_class
c_all_approvers(tempIndex).item_id, -- item_id
c_all_approvers(tempIndex).approver_order_number,
allow_delete, -- allow detele
ln_approver_group_name -- approver_group_name
);
ln_insertions_index := c_all_insertions.count;
ln_approver_index := c_all_insertions(ln_insertions_index).position;
FOR J IN 1..ln_insertions_index LOOP
-- increment the active_approver_index once we are done with current position
if( active_apr_index = 0 or ((active_apr_index < ln_approver_list_cnt+1)
and c_all_insertions(j).position > l_default_approvers_list(active_apr_index).line_no))
then
begin
active_apr_index := active_apr_index + 1;
if( (ln_approver_index+1 = c_all_insertions(j).position)
or ((active_apr_index < ln_approver_list_cnt+1)
and
c_all_insertions(j).position =
l_default_approvers_list(active_apr_index).line_no))
then
begin
-- getting lookup values based on active or all approvers mode
if( bactiveApproversYNIn = 'Y' and c_all_insertions(j).order_type
in ('absolute order','before approver','after approver')
) then
begin
OPEN c_active_order_type_name ( p_order_type=>upper(c_all_insertions(j).order_type));
OPEN c_order_type_name ( p_order_type=>upper(c_all_insertions(j).order_type));
if( c_all_insertions(j).order_type = 'absolute order') then
ln_order_type_name := ln_order_type_name || ' : ' || active_apr_index;
if ( c_all_insertions(j).order_type = 'after approver' ) then
begin
-- in case of first approver and order_type='after approver'
-- skip this insertion order type for end user
if(active_apr_index <= 1)then goto End_of_Insertions_Loop; end if;
if ( c_all_insertions(j).order_type = 'before approver') then
ln_order_type_name := ln_order_type_name || ' : '
|| l_default_approvers_list(active_apr_index).display_name;
lv_parameter := c_all_insertions(j).parameter;
l_default_insertion := ame_insertion_record2_ss(
c_all_insertions(j).position, -- position index
c_all_insertions(j).item_class, -- item_class
c_all_insertions(j).item_id, -- item_id
c_all_insertions(j).action_type_id, -- null for adhoc
c_all_insertions(j).group_or_chain_id, -- null for adhoc
c_all_insertions(j).order_type, -- order_type
lv_parameter, -- parameter
c_all_insertions(j).api_Insertion,
c_all_insertions(j).authority, -- authority
ln_order_type_name -- appended order type
);
l_default_insertions_list.EXTEND;
ln_insertion_record2_num := ln_insertion_record2_num + 1;
l_default_insertions_list(ln_insertion_record2_num) := l_default_insertion ;
<>
-- this statement included just for above End_of_Insertions_Loop label
hr_utility.trace('end of insertion records for loop');
END LOOP; -- insertions loop
p_ame_order_type_list := l_default_insertions_list;