The following lines contain the word 'select', 'insert', 'update' or 'delete':
gv_update_mode wf_activity_attr_values.text_value%TYPE
DEFAULT 'OFF';
SELECT peo.full_name
FROM per_people_f peo
WHERE peo.person_id = p_person;
SELECT person_id
FROM per_all_people_f
WHERE full_name = p_full_name ;
SELECT paf.assignment_id
FROM
per_all_assignments_f paf,
per_all_people_f peo
WHERE
peo.person_id = paf.person_id
AND peo.person_id = p_person_id
AND p_effective_date BETWEEN paf.effective_start_date
AND NVL(paf.effective_end_date, TRUNC(SYSDATE))
ORDER BY paf.effective_start_date DESC;
SELECT pj.name
FROM per_jobs_vl pj,
per_assignments_f paf
WHERE paf.assignment_id = p_assignment_id
AND paf.job_id = pj.job_id
AND paf.primary_flag = 'Y'
AND paf.business_group_id+0 = pj.business_group_id+0
AND p_effective_date BETWEEN paf.effective_start_date
AND NVL(paf.effective_end_date, TRUNC(SYSDATE))
ORDER BY paf.effective_start_date DESC;
SELECT pj.job_id
FROM per_jobs pj,
per_assignments_f paf
WHERE paf.assignment_id = p_assignment_id
AND paf.job_id = pj.job_id
AND paf.primary_flag = 'Y'
AND paf.business_group_id+0 = pj.business_group_id+0
AND p_effective_date BETWEEN paf.effective_start_date
AND NVL(paf.effective_end_date, TRUNC(SYSDATE))
ORDER BY paf.effective_start_date DESC;
select 1
from wf_item_attribute_values wiav
where wiav.item_type = p_item_type
and wiav.item_key = p_item_key
and wiav.name = p_name;
select 1
from WF_ACTIVITY_ATTR_VALUES wfaav
where wfaav.name = p_name
and wfaav.PROCESS_ACTIVITY_ID = p_id;
PROCEDURE update_ame_approvers_list(
p_item_type IN WF_ITEMS.ITEM_TYPE%TYPE ,
p_item_key IN WF_ITEMS.ITEM_KEY%TYPE ,
p_act_id IN NUMBER ,
p_approver_name hr_util_misc_web.g_varchar2_tab_type
DEFAULT hr_util_misc_web.g_varchar2_tab_default,
p_approver_flag hr_util_misc_web.g_varchar2_tab_type
DEFAULT hr_util_misc_web.g_varchar2_tab_default)
IS
-- Local variables
ln_person_id per_people_f.person_id%TYPE;
l_proc constant varchar2(100) := g_package || ' update_ame_approvers_list';
v_additional_approver_order ame_util.insertionRecord2;
/* ame_api.clearInsertions(applicationIdIn =>c_application_id ,
transactionIdIn =>c_transaction_id,
transactionTypeIn=>c_transaction_type);*/
AME_API3.clearInsertions(applicationIdIn =>c_application_id ,
transactionTypeIn =>c_transaction_type,
transactionIdIn =>c_transaction_id);
c_next_approver_rec.api_insertion:= ame_util.apiInsertion;
insertionRecord2Out => v_additional_approver_order);
AME_API3.insertApprover(applicationIdIn =>c_application_id,
transactionTypeIn =>c_transaction_type,
transactionIdIn =>c_transaction_id,
approverIn =>v_next_approver_rec,
positionIn =>I,
insertionIn =>v_additional_approver_order);
/* ame_api.insertApprover(applicationIdIn =>c_application_id,
transactionIdIn =>c_transaction_id,
approverIn =>c_next_approver_rec,
positionIn =>I,
orderIn =>c_additional_approver_order,
transactionTypeIn=>c_transaction_type );*/
END update_ame_approvers_list;
update_ame_approvers_list(
p_item_type =>p_item_type,
p_item_key =>p_item_key,
p_act_id =>p_act_id,
p_approver_name =>p_approver_name,
p_approver_flag=>p_approver_flag);
avalue => 'DELETED');
if(c_default_approvers(i).api_insertion <> ame_util.apiInsertion) then
lv_forward_to_id := c_default_approvers(i).person_id;
lv_approver_deleted VARCHAR2(100) DEFAULT NULL;
if(c_all_approvers(i).api_insertion <> ame_util.apiInsertion) then
grt_approver_details_table(ln_approver_list_index).default_approver := 'Y';
grt_person_details_rec_table.DELETE;
grt_approver_details_table.DELETE;
grt_person_details_rec_table.DELETE;
grt_approver_details_table.DELETE;
lv_approver_deleted VARCHAR2(100) DEFAULT NULL;
lv_approver_deleted := wf_engine.GetItemAttrText
(itemtype => p_item_type,
itemkey => p_item_key,
aname => lv_item_name);
lv_approver_deleted := NVL(lv_approver_deleted,' ');
IF lv_default_approver <> 'Y' AND lv_approver_deleted <>'DELETED' THEN
-- check if an error exists for this additional approver index
IF hr_errors_api.errorExists THEN
grt_approver_details_table(I).full_name := gv_error_table(ln_error_count).full_name;
END IF; -- for lv_default_approver <> 'Y' AND lv_approver_deleted <>'DELETED'
:= hr_util_misc_web.g_new_line || htf.formselectopen
(cname => upper(p_variable_name)
,nsize => 1
,cattributes => p_attributes) ||
hr_util_misc_web.g_new_line ;
l_checked := 'SELECTED';
l_lov := l_lov || htf.formselectoption
(cvalue => l_ddl_data(i).label
,cselected => l_checked
,cattributes => 'VALUE="'|| l_ddl_data(i).code
||'"' ||'INDEX="'|| l_ddl_data(i).code_index
||'"' ) ;--|| hr_util_misc_web.g_new_line;
l_lov := l_lov || htf.formselectclose || hr_util_misc_web.g_new_line;
PROCEDURE delete_approver(p_approver_name IN OUT NOCOPY hr_util_misc_web.g_varchar2_tab_type,
p_approver_flag IN OUT NOCOPY hr_util_misc_web.g_varchar2_tab_type,
p_item_type IN wf_items.item_type%TYPE,
p_item_key IN wf_items.item_key%TYPE,
p_approver_index IN NUMBER DEFAULT 1)
AS
-- Local Variables
ln_number_of_approvers NUMBER ;
l_proc constant varchar2(100) := g_package || ' delete_approver';
IF lv_valid_approver<>'DELETED' THEN
-- get the person_id for this index
ln_from_index_id := wf_engine.GetItemAttrNumber
(itemtype => p_item_type,
itemkey => p_item_key,
aname => lv_item_name);
avalue => 'DELETED');
END delete_approver;
PROCEDURE delete_notifier(p_notifier_name IN OUT NOCOPY hr_util_misc_web.g_varchar2_tab_type,
p_notify_onsubmit_flag IN OUT NOCOPY hr_util_misc_web.g_varchar2_tab_type,
p_notify_onapproval_flag IN OUT NOCOPY hr_util_misc_web.g_varchar2_tab_type,
p_item_type IN wf_items.item_type%TYPE,
p_item_key IN wf_items.item_key%TYPE,
p_notifier_index IN NUMBER DEFAULT 1)
AS
-- Local Variables
lv_notifier_name hr_util_misc_web.g_varchar2_tab_type;
l_proc constant varchar2(100) := g_package || ' delete_notifier';
END delete_notifier;
PROCEDURE update_notifiers(
p_item_type IN WF_ITEMS.ITEM_TYPE%TYPE ,
p_item_key IN WF_ITEMS.ITEM_KEY%TYPE ,
p_act_id IN NUMBER ,
p_notifiers_num IN NUMBER DEFAULT 0,
p_Notify_On_Submit hr_util_misc_web.g_varchar2_tab_type DEFAULT
hr_util_misc_web.g_varchar2_tab_default,
p_Notify_On_Approval hr_util_misc_web.g_varchar2_tab_type DEFAULT
hr_util_misc_web.g_varchar2_tab_default
)
AS
-- Local Variables
lv_error_flag BOOLEAN DEFAULT FALSE;
lv_approver_deleted VARCHAR2(100);
l_proc constant varchar2(100) := g_package || ' update_notifiers';
END update_notifiers;
lv_approver_deleted VARCHAR2(20);
lv_approver_deleted := wf_engine.GetItemAttrText
(itemtype => p_item_type,
itemkey => p_item_key,
aname => lv_item_name);
lv_approver_deleted := NVL(lv_approver_deleted,' ');
IF lv_default_approver <> 'Y' AND lv_approver_deleted <>'DELETED' THEN
-- get the approver ID for this index
ln_person_id := wf_engine.GetItemAttrNumber
(itemtype => p_item_type,
itemkey => p_item_key,
aname => lv_item_name);
/* hr_dynamic_approval_web.delete_approver(p_item_type=>p_item_type,
p_item_key=>p_item_key,
p_approver_index=>I);
END IF; -- for lv_default_approver <> 'Y' AND lv_approver_deleted <>'DELETED'
IF lv_exists <>'N' AND lv_isvalid <>'DELETED' THEN
hr_utility.trace('In ( IF lv_exists <>N AND lv_isvalid <>DELETED):'|| l_proc);
hr_utility.trace('In else of ( IF lv_exists <>N AND lv_isvalid <>DELETED):'|| l_proc);
ame_api.updateApprovalStatus2(applicationIdIn =>c_application_id,
transactionIdIn =>c_transaction_id,
approvalStatusIn =>ame_util.approvedStatus,
approverPersonIdIn =>l_forward_to_person_id,
approverUserIdIn =>null,
transactionTypeIn =>c_transaction_type,
forwardeeIn =>null); */
ame_api2.updateApprovalStatus2(applicationIdIn=>c_application_id,
transactionTypeIn =>c_transaction_type,
transactionIdIn=>c_transaction_id,
approvalStatusIn =>ame_util.approvedStatus,
approverNameIn =>l_current_forward_to_username,
itemClassIn => null,
itemIdIn =>null,
actionTypeIdIn=> null,
groupOrChainIdIn =>null,
occurrenceIn =>null,
forwardeeIn =>ame_util.emptyApproverRecord2,
updateItemIn =>false);
end if; -- call for AME update status
grt_approver_details_table.DELETE;
v_additional_approver_order ame_util.insertionRecord2;
/*ame_api.clearInsertions(applicationIdIn =>c_application_id ,
transactionIdIn =>c_transaction_id,
transactionTypeIn=>c_transaction_type);*/
AME_API3.clearInsertions(applicationIdIn =>c_application_id ,
transactionTypeIn =>c_transaction_type,
transactionIdIn =>c_transaction_id);
c_next_approver_rec.api_insertion:= ame_util.apiInsertion;
insertionRecord2Out => v_additional_approver_order);
AME_API3.insertApprover(applicationIdIn =>c_application_id,
transactionTypeIn =>c_transaction_type,
transactionIdIn =>c_transaction_id,
approverIn =>v_next_approver_rec,
positionIn =>I,
insertionIn =>v_additional_approver_order);
/* ame_api.insertApprover(applicationIdIn =>c_application_id,
transactionIdIn =>c_transaction_id,
approverIn =>c_next_approver_rec,
positionIn =>I,
orderIn =>c_additional_approver_order,
transactionTypeIn=>c_transaction_type );*/
grt_notifier_details_table.DELETE;