[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
x_index_tbl.delete;
x_t1.delete;
x_t2.delete;
select 1 from amw_proc_hierarchy_denorm
where process_id = l_ancestor_id and
parent_child_id = l_descendant_id and
hierarchy_type = 'L' and
up_down_ind = 'D';
procedure update_denorm_add_child(p_parent_id number,
p_child_id number,
l_sysdate in Date default sysdate)
is
-- CURSOR TO SELECT ALL THE ANCESTORS OF THE GIVEN PROCESS.
cursor c_ancestors(l_process_id number) is
select parent_child_id
from amw_proc_hierarchy_denorm
where process_id = l_process_id and
up_down_ind = 'U';
select parent_child_id
from amw_proc_hierarchy_denorm
where process_id = l_process_id and
up_down_ind = 'D';
select 1 from amw_proc_hierarchy_denorm
where process_id = l_ancestor_id and
parent_child_id = l_descendant_id and
up_down_ind = 'D';
x_index_tbl.delete;
insert into amw_proc_hierarchy_denorm (PROCESS_ID,
PARENT_CHILD_ID,
UP_DOWN_IND,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER,
HIERARCHY_TYPE)
values
(x_process_tbl(i),
x_parent_child_tbl(i),
x_up_down_ind_tbl(i),
l_sysdate,
G_USER_ID,
l_sysdate,
G_USER_ID,
G_LOGIN_ID,
1,
'L');
end update_denorm_add_child;
procedure update_approved_denorm(p_org_id in number,
l_sysdate in Date default sysdate)
is
cursor c_all_approved_links_rl is
select parent_id, child_id
from amw_approved_hierarchies
where (organization_id is null or organization_id = -1)
and (end_date is null or end_date > l_sysdate);
select parent_id, child_id
from amw_approved_hierarchies
where (organization_id = l_org_id) and
(end_date is null or end_date > l_sysdate);
delete from amw_proc_hierarchy_denorm where hierarchy_type='A';
delete from amw_org_hierarchy_denorm
where organization_id = p_org_id and
hierarchy_type='A';
insert into amw_proc_hierarchy_denorm (PROCESS_ID,
PARENT_CHILD_ID,
UP_DOWN_IND,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER,
HIERARCHY_TYPE)
values
(x_process_tbl(i),
x_parent_child_tbl(i),
x_up_down_ind_tbl(i),
l_sysdate,
G_USER_ID,
l_sysdate,
G_USER_ID,
G_LOGIN_ID,
1,
'A');
insert into amw_org_hierarchy_denorm (ORGANIZATION_ID,
PROCESS_ID,
PARENT_CHILD_ID,
UP_DOWN_IND,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER,
HIERARCHY_TYPE)
values
(p_org_id,
x_process_tbl(i),
x_parent_child_tbl(i),
x_up_down_ind_tbl(i),
l_sysdate,
G_USER_ID,
l_sysdate,
G_USER_ID,
G_LOGIN_ID,
1,
'A');
end update_approved_denorm;
procedure update_denorm(p_org_id in number,
l_sysdate in Date default sysdate)
is
--l_sysdate DATE := sysdate;
(select distinct parent_id
from amw_latest_hierarchies
where (organization_id is null or organization_id = -1)
)
minus
(select distinct child_id parent_id
from amw_latest_hierarchies
where (organization_id is null or organization_id = -1)
);
(select distinct parent_id
from amw_latest_hierarchies
where (organization_id = l_org_id)
)
minus
(select distinct child_id parent_id
from amw_latest_hierarchies
where (organization_id = l_org_id)
);
delete from amw_proc_hierarchy_denorm where hierarchy_type='L';
delete from amw_org_hierarchy_denorm
where organization_id = p_org_id and
hierarchy_type='L';
insert into amw_proc_hierarchy_denorm (PROCESS_ID,
PARENT_CHILD_ID,
UP_DOWN_IND,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER,
HIERARCHY_TYPE)
values
(x_process_tbl(i),
x_parent_child_tbl(i),
x_up_down_ind_tbl(i),
l_sysdate,
G_USER_ID,
l_sysdate,
G_USER_ID,
G_LOGIN_ID,
1,
'L');
insert into amw_org_hierarchy_denorm (ORGANIZATION_ID,
PROCESS_ID,
PARENT_CHILD_ID,
UP_DOWN_IND,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER,
HIERARCHY_TYPE)
values
(p_org_id,
x_process_tbl(i),
x_parent_child_tbl(i),
x_up_down_ind_tbl(i),
l_sysdate,
G_USER_ID,
l_sysdate,
G_USER_ID,
G_LOGIN_ID,
1,
'L');
end update_denorm;
l_program_update_date amw_process.program_update_date%type;
select process_id, process_rev_id, item_type, name, process_code,
revision_number, approval_status, control_count,
risk_count, org_count, significant_process_flag,
standard_process_flag, certification_status,
process_category, process_owner_id, finance_owner_id,
application_owner_id, standard_variation,
object_version_number, deletion_date,
process_type, control_activity_type,
attribute_category, attribute1,
attribute2, attribute3, attribute4,
attribute5, attribute6, attribute7,
attribute8, attribute9, attribute10,
attribute11, attribute12, attribute13,
attribute14, attribute15, created_from,
program_id, program_application_id,
request_id, program_update_date, risk_count_latest,
control_count_latest, classification
into l_process_id, l_process_rev_id, l_item_type, l_name, l_process_code,
l_revision_number, l_approval_status, l_control_count,
l_risk_count, l_org_count, l_significant_process_flag,
l_standard_process_flag, l_certification_status,
l_process_category, l_process_owner_id, l_finance_owner_id,
l_application_owner_id, l_standard_variation,
l_object_version_number, l_deletion_date,
l_process_type, l_control_activity_type,
l_attribute_category, l_attribute1,
l_attribute2, l_attribute3, l_attribute4,
l_attribute5, l_attribute6, l_attribute7,
l_attribute8, l_attribute9, l_attribute10,
l_attribute11, l_attribute12, l_attribute13,
l_attribute14, l_attribute15, l_created_from,
l_program_id, l_program_application_id,
l_request_id, l_program_update_date,
l_risk_count_latest, l_control_count_latest,
l_classification
from amw_process
where process_id = p_process_id
and (end_date is null or end_date > l_sysdate);
insert into amw_process (PROCESS_ID,
ITEM_TYPE,
NAME,
PROCESS_CODE,
REVISION_NUMBER,
PROCESS_REV_ID,
APPROVAL_STATUS,
START_DATE,
CONTROL_COUNT,
RISK_COUNT,
ORG_COUNT,
SIGNIFICANT_PROCESS_FLAG,
STANDARD_PROCESS_FLAG,
CERTIFICATION_STATUS,
PROCESS_CATEGORY,
PROCESS_OWNER_ID,
FINANCE_OWNER_ID,
APPLICATION_OWNER_ID,
STANDARD_VARIATION,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER,
DELETION_DATE,
PROCESS_TYPE,
CONTROL_ACTIVITY_TYPE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
CREATED_FROM,
PROGRAM_ID,
PROGRAM_APPLICATION_ID,
REQUEST_ID,
PROGRAM_UPDATE_DATE,
RISK_COUNT_LATEST,
CONTROL_COUNT_LATEST,
CLASSIFICATION)
VALUES
(l_process_id,
l_item_type,
l_name,
l_process_code,
l_revision_number + 1,
AMW_PROCESS_S.nextval,
'D',
l_sysdate,
l_control_count,
l_risk_count,
l_org_count,
l_significant_process_flag,
l_standard_process_flag,
l_certification_status,
l_process_category,
l_process_owner_id,
l_finance_owner_id,
l_application_owner_id,
l_standard_variation,
l_sysdate,
G_USER_ID,
l_sysdate,
G_USER_ID,
G_LOGIN_ID,
1,
l_deletion_date,
l_process_type,
l_control_activity_type,
l_attribute_category,
l_attribute1,
l_attribute2,
l_attribute3,
l_attribute4,
l_attribute5,
l_attribute6,
l_attribute7,
l_attribute8,
l_attribute9,
l_attribute10,
l_attribute11,
l_attribute12,
l_attribute13,
l_attribute14,
l_attribute15,
l_created_from,
l_program_id,
l_program_application_id,
l_request_id,
l_program_update_date,
l_risk_count_latest,
l_control_count_latest,
l_classification)
RETURNING
PROCESS_REV_ID
INTO
l_new_process_rev_id;
update amw_process set
last_update_date = l_sysdate,
last_updated_by = G_USER_ID,
last_update_login = G_LOGIN_ID,
end_date = l_sysdate,
object_version_number = l_object_version_number + 1
where
process_id = p_process_id and
revision_number = l_revision_number and
object_version_number = l_object_version_number;
/* The only reason why the above insert could fail is:
* The object version number had already been incremented
* By some other process and thus the where clause failed to
* update any row
*/
if(sql%notfound)
then
raise FND_API.G_EXC_ERROR;
select display_name, description, language, source_lang
into l_display_name, l_description, l_language, l_source_lang
from amw_process_names_tl
where process_id = l_process_id and
revision_number = l_revision_number and
language = userenv('LANG');
insert into amw_process_names_tl
(
process_id
,revision_number
,process_rev_id
,display_name
,description
,language
,source_lang
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,object_version_number
)
select
l_process_id
,l_revision_number+1
,l_new_process_rev_id
,l_display_name
,l_description
,L.LANGUAGE_CODE
,USERENV('LANG')
,l_sysdate
,g_user_id
,l_sysdate
,g_user_id
,g_login_id
,1
from FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B');
X_last_update_login => g_login_id,
X_program_id => FND_GLOBAL.CONC_PROGRAM_ID,
X_request_id => FND_GLOBAL.conc_request_id);
/* this procedure updates the instance_id in the
* amw_approved_hierarchy table if necessary
* it should be called after a link is inserted in the
* latest hierarchy (or an instance_id is updated)
* the reason is that: the child order numbers of any
* link in the latest and approved hierarchies MUST MATCH
*/
procedure update_appr_ch_ord_num_if_reqd
(p_org_id in number,
p_parent_id in number,
p_child_id in number,
p_instance_id in number)
is
l_dummy pls_integer;
select 1 into l_dummy from amw_approved_hierarchies
where parent_id = p_parent_id
and child_id = p_child_id
and end_date is null
and ((-1 = p_org_id and (organization_id is null or organization_id = -1)) OR
(p_org_id <> -1 and organization_id = p_org_id));
update amw_approved_hierarchies set
last_update_date = sysdate,
last_updated_by = G_USER_ID,
last_update_login = G_LOGIN_ID,
child_order_number = p_instance_id,
object_version_number = object_version_number + 1
where parent_id = p_parent_id
and child_id = p_child_id
and end_date is null
and ((-1 = p_org_id and (organization_id is null or organization_id = -1)) OR
(p_org_id <> -1 and organization_id = p_org_id));
end update_appr_ch_ord_num_if_reqd;
select approval_status into l_approval_status
from amw_process where process_id = p_process_id and
end_date is null;
(select parent_process_id,
child_process_id,
child_order_number from AMW_LATEST_HIERARCHY_RL_V
start with parent_process_id = -1 and
parent_approval_status = 'A'
connect by prior child_process_id = parent_process_id and
child_approval_status = 'A' )
MINUS
(select parent_process_id,
child_process_id,
child_order_number
from AMW_CURR_APP_HIERARCHY_RL_V);
insert into amw_latest_hierarchies(ORGANIZATION_ID,
PARENT_ID,
CHILD_ID,
CHILD_ORDER_NUMBER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
OBJECT_VERSION_NUMBER
)
VALUES (-1,
p_parent_process_id,
p_child_process_id,
AMW_CHILD_ORDER_S.nextval,
l_sysdate,
g_user_id,
g_login_id,
l_sysdate,
g_user_id,
1)
returning CHILD_ORDER_NUMBER
into l_child_order_number;
update_appr_ch_ord_num_if_reqd(-1, p_parent_process_id, p_child_process_id,
l_child_order_number);
/* update the denorm table
* can throw an amw_processing_exception, so it has been handled.
*/
update_denorm_add_child(p_parent_id => p_parent_process_id,
p_child_id => p_child_process_id,
l_sysdate => l_sysdate);
insert into amw_approved_hierarchies
(organization_id,
parent_id,
child_id,
start_date,
child_order_number,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
OBJECT_VERSION_NUMBER)
values
(-1,
a_link.parent_process_id,
a_link.child_process_id,
l_sysdate,
a_link.child_order_number,
l_sysdate,
g_user_id,
g_login_id,
l_sysdate,
g_user_id,
1);
/* now update the denorm table */
update_approved_denorm(-1,l_sysdate);
update_latest_control_counts(p_parent_process_id);
update_latest_risk_counts(p_parent_process_id);
procedure delete_child(
p_parent_process_id in number,
p_child_process_id in number,
l_sysdate in Date default sysdate,
x_return_status out nocopy varchar2,
x_msg_count out nocopy number,
x_msg_data out nocopy varchar2)
is
l_api_name constant varchar2(30) := 'delete_child';
(select parent_process_id, child_process_id
from AMW_CURR_APP_HIERARCHY_RL_V
where parent_process_id is not null)
MINUS
(select parent_process_id, child_process_id
from AMW_CURR_APP_HIERARCHY_RL_V
start with parent_process_id = -1
connect by prior child_process_id = parent_process_id);
/* update the latest hierarchy table
* first we update the denorm table
*/
delete from amw_latest_hierarchies
where
parent_id = p_parent_process_id and
child_id = p_child_process_id and
(organization_id is null or organization_id = -1);
update_denorm(p_org_id => -1,
l_sysdate =>l_sysdate);
select 1 into l_dummy from amw_approved_hierarchies
where (organization_id is null or organization_id = -1)
and parent_id = -1
and child_id = p_child_process_id
and end_date is null;
delete from amw_approved_hierarchies
where parent_id = -1
and child_id = p_child_process_id
and (organization_id is null or organization_id = -1)
and end_date is null;
update amw_approved_hierarchies
set end_date = l_sysdate,
object_version_number = object_version_number + 1
where (organization_id is null or organization_id = -1)
and parent_id = defunct_link.parent_process_id
and child_id = defunct_link.child_process_id
and end_date is null;
/* finally update the denorm table */
update_approved_denorm(-1,l_sysdate);
update_latest_control_counts(p_parent_process_id);
update_latest_risk_counts(p_parent_process_id);
p_procedure_name => 'delete_child',
p_error_text => x_msg_data);
end delete_child;
select locked_process_id from amw_process_locks
where (organization_id is null or organization_id=-1) and
locked_process_id=l_process_id;
select process_id into l_process_id from amw_process
where name = p_name
and item_type = p_item_type
and end_date is null;
select 1 into l_dummy from amw_process
where name = p_name
and item_type = p_item_type
and end_date is null;
select l_prefix || to_char(AMW_PROCESS_CODE_S.nextval) into l_code from dual;
select 1 into l_dummy from amw_process
where process_code = l_code
and end_date is null;
insert into amw_process (PROCESS_ID,
ITEM_TYPE,
NAME,
PROCESS_CODE,
REVISION_NUMBER,
PROCESS_REV_ID,
APPROVAL_STATUS,
START_DATE,
CONTROL_COUNT,
RISK_COUNT,
ORG_COUNT,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER,
PROCESS_TYPE,
PROGRAM_ID,
REQUEST_ID,
PROGRAM_UPDATE_DATE,
RISK_COUNT_LATEST,
CONTROL_COUNT_LATEST,
SIGNIFICANT_PROCESS_FLAG,
STANDARD_PROCESS_FLAG,
PROCESS_CATEGORY)
VALUES
(AMW_PROCESS_S.nextval,
p_item_type,
p_name,
l_new_code,
1,
AMW_PROCESS_S.nextval,
'D',
g_sysdate,
0,
0,
0,
g_sysdate,
G_USER_ID,
g_sysdate,
G_USER_ID,
G_LOGIN_ID,
1,
'P',
FND_GLOBAL.CONC_PROGRAM_ID,
FND_GLOBAL.CONC_REQUEST_ID,
DECODE(FND_GLOBAL.CONC_PROGRAM_ID,null,null,g_sysdate),
0,
0,
'Y',
'Y',
'R')
RETURNING
PROCESS_ID, PROCESS_REV_ID
INTO
l_process_id, l_process_rev_id;
insert into amw_process_names_tl
(
process_id
,revision_number
,process_rev_id
,display_name
,description
,language
,source_lang
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,object_version_number
)
select
l_process_id
,1
,l_process_rev_id
,p_display_name
,p_description
,L.LANGUAGE_CODE
,USERENV('LANG')
,g_sysdate
,g_user_id
,g_sysdate
,g_user_id
,g_login_id
,1
from FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B');
function is_deleted_process(p_process_id in number)
return boolean is
l_dummy pls_integer;
select 1 into l_dummy from amw_process
where process_id = p_process_id
and end_date is null
and deletion_date is not null;
end is_deleted_process;
select activity_item_type, activity_name,
activity_display_name, activity_description,
instance_id
from amw_wf_process_hierarchy_vl
where process_name = l_name and
process_item_type = l_item_type;
(select activity_name, activity_item_type
from amw_wf_process_hierarchy_vl
where process_name = l_name
and process_item_type = l_item_type
)
MINUS
(select child_name activity_name, child_item_type activity_item_type
from amw_latest_hierarchy_rl_v
where parent_name = l_name
and parent_item_type = l_item_type
);
(select child_name, child_item_type
from amw_latest_hierarchy_rl_v
where parent_name = l_name
and parent_item_type = l_item_type
)
MINUS
(select activity_name child_name, activity_item_type child_item_type
from amw_wf_process_hierarchy_vl
where process_name = l_name
and process_item_type = l_item_type
);
(select parent_process_id,
child_process_id,
child_order_number from AMW_LATEST_HIERARCHY_RL_V
start with parent_process_id = -1 and
parent_approval_status = 'A'
connect by prior child_process_id = parent_process_id and
child_approval_status = 'A' )
MINUS
(select parent_process_id,
child_process_id,
child_order_number
from AMW_CURR_APP_HIERARCHY_RL_V);
select 1 into l_dummy from amw_latest_hierarchies
where parent_id = p_parent_process_id
and child_id = l_process_id
and (organization_id is null or organization_id = -1);
insert into amw_latest_hierarchies (ORGANIZATION_ID,
PARENT_ID,
CHILD_ID,
CHILD_ORDER_NUMBER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
OBJECT_VERSION_NUMBER
)
values (-1,
p_parent_process_id,
l_process_id,
p_instance_id,
g_sysdate,
g_user_id,
g_login_id,
g_sysdate,
g_user_id,
1);
update_appr_ch_ord_num_if_reqd(-1, p_parent_process_id,
l_process_id,
p_instance_id);
if(is_deleted_process(l_process_id))
then
raise amw_process_deleted_exception;
select 1 into l_dummy from amw_latest_hierarchies
where parent_id = p_parent_process_id
and child_id = l_process_id
and (organization_id is null or organization_id = -1);
insert into amw_latest_hierarchies (ORGANIZATION_ID,
PARENT_ID,
CHILD_ID,
CHILD_ORDER_NUMBER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
OBJECT_VERSION_NUMBER)
values (-1,
p_parent_process_id,
l_process_id,
p_instance_id,
g_sysdate,
g_user_id,
g_login_id,
g_sysdate,
g_user_id,
1);
update_appr_ch_ord_num_if_reqd(-1, p_parent_process_id,
l_process_id,
p_instance_id);
delete from amw_latest_hierarchies
where (organization_id is null or organization_id = -1)
and parent_id = l_process_id
and child_id = cur_child_id;
select approval_status into l_approval_status
from amw_process
where process_id = l_process_id
and end_date is null;
insert into amw_approved_hierarchies
(organization_id,
parent_id,
child_id,
start_date,
child_order_number,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
OBJECT_VERSION_NUMBER)
values
(-1,
a_link.parent_process_id,
a_link.child_process_id,
g_sysdate,
a_link.child_order_number,
g_sysdate,
g_user_id,
g_login_id,
g_sysdate,
g_user_id,
1);
/* now update the denorm table */
update_approved_denorm(-1,g_sysdate);
update_appr_control_counts;
update_appr_risk_counts;
insert into amw_latest_hierarchies (organization_id,
parent_id,
child_id,
child_order_number,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
OBJECT_VERSION_NUMBER)
values
(-1,
p_parent_process_id,
l_process_id,
p_instance_id,
g_sysdate,
g_user_id,
g_login_id,
g_sysdate,
g_user_id,
1);
p_update_denorm_count IN VARCHAR2 := 'Y',
x_return_status out nocopy varchar2,
x_msg_count out nocopy number,
x_msg_data out nocopy varchar2)
is
iStart pls_integer := 1;
visited_tbl.delete;
select display_name, description, AMW_CHILD_ORDER_S.nextval
into cur_display_name, cur_description, cur_instance_id
from wf_activities_vl
where name = cur_name
and item_type = cur_item_type
and end_date is null;
IF p_update_denorm_count = 'Y' THEN
/* update the denorm tables .. */
update_denorm(p_org_id => -1,
l_sysdate => g_sysdate);
/* then update the risk_control_counts */
update_all_latest_rc_counts(p_mode => 'RC');
when amw_process_deleted_exception then
ROLLBACK;
fnd_message.set_name('AMW','AMW_DELETED_PROC_MODIF');
(select parent_process_id,
child_process_id,
child_order_number from AMW_LATEST_HIERARCHY_RL_V
start with parent_process_id = -1 and
parent_approval_status = 'A'
connect by prior child_process_id = parent_process_id and
child_approval_status = 'A' )
MINUS
(select parent_process_id,
child_process_id,
child_order_number
from AMW_CURR_APP_HIERARCHY_RL_V);
SELECT AMW_CHILD_ORDER_S.nextval INTO L_CHILD_ORDER FROM DUAL;
insert into amw_latest_hierarchies(ORGANIZATION_ID,
PARENT_ID,
CHILD_ID,
CHILD_ORDER_NUMBER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
OBJECT_VERSION_NUMBER)
VALUES (-1,
p_parent_process_id,
p_child_process_id,
L_CHILD_ORDER,
l_sysdate,
g_user_id,
g_login_id,
l_sysdate,
g_user_id,
1)
returning CHILD_ORDER_NUMBER
into l_child_order_number;
update_appr_ch_ord_num_if_reqd(-1, p_parent_process_id, p_child_process_id,
l_child_order_number);
/* update the denorm table
* can throw an amw_processing_exception, so it has been handled.
*/
---COMMENTING THE BELOW ... DON'T NEED THIS FROM WEBADI
/*
update_denorm_add_child(p_parent_id => p_parent_process_id,
p_child_id => p_child_process_id,
l_sysdate => l_sysdate);
select approval_status into l_approval_status
from amw_process where process_id = p_child_process_id and
end_date is null;
insert into amw_approved_hierarchies
(organization_id,
parent_id,
child_id,
start_date,
child_order_number,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
OBJECT_VERSION_NUMBER)
values
(-1,
a_link.parent_process_id,
a_link.child_process_id,
l_sysdate,
a_link.child_order_number,
l_sysdate,
g_user_id,
g_login_id,
l_sysdate,
g_user_id,
1);
/* now update the denorm table */
update_approved_denorm(-1,l_sysdate);
/* update the approved risk counts for all processes above
* p_process_id in RL Approved Hierarchy
* Call this ONLY AFTER making the denorm tables reflect
* the current denormed approved hierarchy
* This can be called for post approval processing
*** BE CAREFUL WITH THE USE OF THIS PROCEDURE ***
* This process only affects procedures in the approved_hierarchy
* The approved_hierarchy is always connected to the root
* It is possible that a process was approved and yet not present
* in the approved hierarchy. Thus after some process P was approved
* and its child Q (approved earlier) was added to the approved
* hierarchy WHEN P was approved: IF THIS PROCEDURE WAS CALLED ONLY FOR
* P it will not affect Q and the final result would be INCORRECT.
*/
procedure update_approved_risk_counts(p_process_id in number)
is
cursor c is
(select process_id from amw_process where process_id in
((select parent_child_id
from amw_proc_hierarchy_denorm
where process_id = p_process_id
and up_down_ind = 'U'
and hierarchy_type = 'A'
)
union all
(select p_process_id from dual)));
update amw_process
set risk_count = (select count(*) from (
select distinct risk_id from amw_risk_associations
where pk1 in ( ( select parent_child_id
from amw_proc_hierarchy_denorm
where process_id = x(ctr)
and up_down_ind = 'D'
and hierarchy_type = 'A') union all (select x(ctr) from dual) )
and approval_date is not null
and deletion_approval_date is null
and object_type = 'PROCESS'
) ),
object_version_number = object_version_number + 1,
last_update_date = sysdate,
last_updated_by = G_USER_ID,
last_update_login = G_LOGIN_ID
where process_id = x(ctr)
and approval_date is not null
and approval_end_date is null
and process_id <> -1;
end update_approved_risk_counts;
/* update the approved control counts for all processes above
* p_process_id in RL Approved Hierarchy
* Call this ONLY AFTER making the denorm tables reflect
* the current denormed approved hierarchy
* This can be called for post approval processing
*** BE CAREFUL WITH THE USE OF THIS PROCEDURE ***
* This process only affects procedures in the approved_hierarchy
* The approved_hierarchy is always connected to the root
* It is possible that a process was approved and yet not present
* in the approved hierarchy. Thus after some process P was approved
* and its child Q (approved earlier) was added to the approved
* hierarchy WHEN P was approved: IF THIS PROCEDURE WAS CALLED ONLY FOR
* P it will not affect Q and the final result would be INCORRECT.
*/
procedure update_approved_control_counts(p_process_id in number)
is
cursor c is
(select process_id from amw_process where process_id in
((select parent_child_id
from amw_proc_hierarchy_denorm
where process_id = p_process_id
and up_down_ind = 'U'
and hierarchy_type = 'A'
)
union all
(select p_process_id from dual)));
update amw_process
set control_count = (select count(*) from (
select distinct control_id from amw_control_associations
where pk1 in ( ( select parent_child_id
from amw_proc_hierarchy_denorm
where process_id = x(ctr)
and up_down_ind = 'D'
and hierarchy_type = 'A') union all (select x(ctr) from dual) )
and approval_date is not null
and deletion_approval_date is null
and object_type = 'RISK'
) ),
object_version_number = object_version_number + 1,
last_update_date = sysdate,
last_updated_by = G_USER_ID,
last_update_login = G_LOGIN_ID
where process_id = x(ctr)
and approval_date is not null
and approval_end_date is null
and process_id <> -1;
end update_approved_control_counts;
/* updates risk counts for every process in
* the approved hierarchy
*/
procedure update_appr_risk_counts is
cursor c is
(select process_id from amw_process where process_id in
(select parent_child_id
from amw_proc_hierarchy_denorm
where process_id = -1
and up_down_ind = 'D'
and hierarchy_type = 'A'
));
update amw_process
set risk_count = (select count(*) from (
select distinct risk_id from amw_risk_associations
where pk1 in ( ( select parent_child_id
from amw_proc_hierarchy_denorm
where process_id = x(ctr)
and up_down_ind = 'D'
and hierarchy_type = 'A') union all (select x(ctr) from dual) )
and approval_date is not null
and deletion_approval_date is null
and object_type = 'PROCESS'
) ),
object_version_number = object_version_number + 1,
last_update_date = sysdate,
last_updated_by = G_USER_ID,
last_update_login = G_LOGIN_ID
where process_id = x(ctr)
and approval_date is not null
and approval_end_date is null
and process_id <> -1;
end update_appr_risk_counts;
procedure update_appr_control_counts is
cursor c is
(select process_id from amw_process where process_id in
(select parent_child_id
from amw_proc_hierarchy_denorm
where process_id = -1
and up_down_ind = 'D'
and hierarchy_type = 'A'
));
update amw_process
set control_count = (select count(*) from (
select distinct control_id from amw_control_associations
where pk1 in ( (select parent_child_id
from amw_proc_hierarchy_denorm
where process_id = x(ctr)
and up_down_ind = 'D'
and hierarchy_type = 'A') union all (select x(ctr) from dual) )
and approval_date is not null
and deletion_approval_date is null
and object_type = 'RISK'
) )
,object_version_number = object_version_number + 1,
last_update_date = sysdate,
last_updated_by = G_USER_ID,
last_update_login = G_LOGIN_ID
where process_id = x(ctr)
and approval_date is not null
and approval_end_date is null
and process_id <> -1;
end update_appr_control_counts;
/* update the latest control counts for all processes above
* p_process_id in RL
* Call this ONLY AFTER making the denorm tables reflect
* the current denormed hierarchy
*/
procedure update_latest_control_counts(p_process_id in number)
is
cursor c is
(select process_id from amw_process where process_id in
(select parent_child_id
from amw_proc_hierarchy_denorm
where process_id = p_process_id
and up_down_ind = 'U'
and hierarchy_type = 'L'
) union all
(select p_process_id from dual));
update amw_process
set control_count_latest = (select count(*) from (
select distinct control_id from amw_control_associations
where pk1 in ( ( select parent_child_id
from amw_proc_hierarchy_denorm
where process_id = x(ctr)
and up_down_ind = 'D'
and hierarchy_type = 'L') union all (select x(ctr) from dual) )
and deletion_date is null
and object_type = 'RISK'
) )
--unsure whether FWK validation may throw error if ovn is incremented here
--so am removing it here.
--.object_version_number = object_version_number + 1,
,last_update_date = sysdate
,last_updated_by = G_USER_ID
,last_update_login = G_LOGIN_ID
where process_id = x(ctr)
and end_date is null
and process_id <> -1;
end update_latest_control_counts;
procedure update_latest_risk_counts(p_process_id in number)
is
cursor c is
(select process_id from amw_process where process_id in
(select parent_child_id
from amw_proc_hierarchy_denorm
where process_id = p_process_id
and up_down_ind = 'U'
and hierarchy_type = 'L'
) union all
(select p_process_id from dual));
update amw_process
set risk_count_latest = (select count(*) from (
select distinct risk_id from amw_risk_associations
where pk1 in ( (select parent_child_id
from amw_proc_hierarchy_denorm
where process_id = x(ctr)
and up_down_ind = 'D'
and hierarchy_type = 'L') union all (select x(ctr) from dual) )
and deletion_date is null
and object_type = 'PROCESS'
) )
--,object_version_number = object_version_number + 1
,last_update_date = sysdate
,last_updated_by = G_USER_ID
,last_update_login = G_LOGIN_ID
where process_id = x(ctr)
and end_date is null
and process_id <> -1;
end update_latest_risk_counts;
procedure update_rc_latest_counts(p_process_id in number,
x_return_status out nocopy varchar2,
x_msg_count out nocopy number,
x_msg_data out nocopy varchar2)
is
l_api_name constant varchar2(30) := 'update_rc_latest_counts';
update_latest_risk_counts(p_process_id => p_process_id);
update_latest_control_counts(p_process_id => p_process_id);
end update_rc_latest_counts;
/* update the latest risk-control counts for ALL processes in RL */
procedure update_all_latest_rc_counts(p_mode in varchar2)
is
cursor c is
(select process_id from amw_process where end_date is null and process_id <> -1);
update amw_process
set risk_count_latest = (select count(*) from (
select distinct risk_id
from amw_risk_associations
where pk1 in ((select parent_child_id
from amw_proc_hierarchy_denorm
where process_id = x(ctr)
and up_down_ind = 'D'
and hierarchy_type = 'L') union all (select x(ctr) from dual) )
and deletion_date is null
and object_type = 'PROCESS'
)),
object_version_number = object_version_number + 1
,last_update_date = sysdate
,last_updated_by = G_USER_ID
,last_update_login = G_LOGIN_ID
where process_id = x(ctr)
and end_date is null;
update amw_process
set control_count_latest = (select count(*) from
(select distinct control_id from amw_control_associations
where pk1 in ((select parent_child_id
from amw_proc_hierarchy_denorm
where process_id = x(ctr)
and up_down_ind = 'D'
and hierarchy_type = 'L') union all (select x(ctr) from dual) )
and deletion_date is null
and object_type = 'RISK'
)),
object_version_number = object_version_number + 1
,last_update_date = sysdate
,last_updated_by = G_USER_ID
,last_update_login = G_LOGIN_ID
where process_id = x(ctr)
and end_date is null;
update amw_process
set risk_count_latest = (select count(*) from (
select distinct risk_id
from amw_risk_associations
where pk1 in ((select parent_child_id
from amw_proc_hierarchy_denorm
where process_id = x(ctr)
and up_down_ind = 'D'
and hierarchy_type = 'L') union all (select x(ctr) from dual) )
and deletion_date is null
and object_type = 'PROCESS'
)),
control_count_latest = (select count(*) from
(select distinct control_id from amw_control_associations
where pk1 in ((select parent_child_id
from amw_proc_hierarchy_denorm
where process_id = x(ctr)
and up_down_ind = 'D'
and hierarchy_type = 'L') union all (select x(ctr) from dual) )
and deletion_date is null
and object_type = 'RISK')),
object_version_number = object_version_number + 1
,last_update_date = sysdate
,last_updated_by = G_USER_ID
,last_update_login = G_LOGIN_ID
where process_id = x(ctr)
and end_date is null;
end update_all_latest_rc_counts;
/* Update the org_counts for all approved processes in RL
* Amit, in my opinion we need something that does it per process
* rather than for everything -- pls, give this a thought
*/
procedure update_all_org_counts
is
cursor c is (select process_id from amw_process where
approval_date is not null
and approval_end_date is null
and process_id <> -1);
update amw_process
set org_count = (select count(*) from
(select distinct organization_id
from amw_process_organization
where process_id = x(ctr)
and end_date is null
and (deletion_date is null or (deletion_date is not null and approval_date is null)))),
object_version_number = object_version_number + 1
,last_update_date = sysdate
,last_updated_by = G_USER_ID
,last_update_login = G_LOGIN_ID
where process_id = x(ctr)
and approval_date is not null
and approval_end_date is null;
end update_all_org_counts;
/* update the org count for p_process_id */
procedure update_org_count(p_process_id in number)
is
begin
update amw_process
set org_count = (select count(*) from
(select distinct organization_id
from amw_process_organization
where process_id = p_process_id
and end_date is null
and (deletion_date is null or (deletion_date is not null and approval_date is null)))),
object_version_number = object_version_number + 1
,last_update_date = sysdate
,last_updated_by = G_USER_ID
,last_update_login = G_LOGIN_ID
where approval_date is not null
and approval_end_date is null
and process_id <> -1 --retained for safety
and process_id = p_process_id;
end update_org_count;
procedure update_attachments(p_old_prev_id in varchar2,
p_new_prev_id in varchar2,
x_return_status out nocopy varchar2,
x_msg_count out nocopy number,
x_msg_data out nocopy varchar2)
is
l_api_name constant varchar2(30) := 'update_attachments';
FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments(X_entity_name => 'AMW_PROCESS',
X_pk1_value => p_old_prev_id);
X_last_update_login => g_login_id,
X_program_id => FND_GLOBAL.CONC_PROGRAM_ID,
X_request_id => FND_GLOBAL.conc_request_id);
FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments(X_entity_name => 'AMW_PROCESS',
X_pk1_value => p_new_prev_id);
end update_attachments;
select amw_process_name_s.nextval into l_name from dual;
update amw_process
set PROCESS_TYPE = decode(p_control_type, 'A', 'C', 'M', 'C', 'B', 'C', PROCESS_TYPE),
CONTROL_ACTIVITY_TYPE = decode(p_control_type, '-1', CONTROL_ACTIVITY_TYPE, p_control_type)
where process_id = l_pid
and end_date is null;
select process_id
into l_pid
from AMW_LATEST_REVISIONS_V
where display_name = p_display_name;
select person_party_id
into l_party_id
from fnd_user
where user_id = G_USER_ID;
cursor_select INTEGER;
query_to_exec := 'select process_id from AMW_CURRENT_APPRVD_REV_V where process_id = -1 and '||p_predicate;
cursor_select := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cursor_select, query_to_exec, DBMS_SQL.NATIVE);
cursor_execute := DBMS_SQL.EXECUTE(cursor_select);
IF DBMS_SQL.FETCH_ROWS(cursor_select) > 0 THEN
l_hasaccess := 'Y';
DBMS_SQL.CLOSE_CURSOR(cursor_select);
--updates latest hier denorm
amw_rl_hierarchy_pkg.update_denorm (-1, sysdate);
--updates approved hier denorm
amw_rl_hierarchy_pkg.update_approved_denorm (-1, sysdate);
update amw_process
set risk_count = null,
control_count = null,
risk_count_latest = null,
control_count_latest = null;
--updates latest risk/control counts
amw_rl_hierarchy_pkg.update_all_latest_rc_counts('RC');
--updates approved risk counts
amw_rl_hierarchy_pkg.update_appr_risk_counts;
--updates approved control counts
amw_rl_hierarchy_pkg.update_appr_control_counts;
-- update approved org counts
amw_rl_hierarchy_pkg.update_all_org_counts;
select 1 into ret_val
from dual
where (exists
(select parent_id from amw_latest_hierarchies
where parent_id = p_process_id
and (organization_id = -1 or organization_id is null)))
or (exists
(select child_id from amw_latest_hierarchies
where child_id = p_process_id
and (organization_id = -1 or organization_id is null)));
select child_id
into l_dummy
from amw_approved_hierarchies
where parent_id = p_process_id
and (organization_id is null or organization_id = -1)
and (end_date is null or end_date > sysdate)
and child_id not in
(select child_id
from amw_latest_hierarchies
where parent_id = p_process_id
and (organization_id is null or organization_id = -1));
select child_id
into l_dummy
from amw_latest_hierarchies
where parent_id = p_process_id
and (organization_id is null or organization_id = -1)
and child_id not in
(select child_id
from amw_approved_hierarchies
where parent_id = p_process_id
and (organization_id is null or organization_id = -1)
and (end_date is null or end_date > sysdate));
select 1
into l_dummy
from amw_process
where process_id = p_process_id
and approval_status = 'A';
select approval_status into appstatus from amw_process
where process_id = p_process_id and end_date is null;
procedure delete_draft (p_process_id in number,
x_return_status out nocopy varchar2,
x_msg_count out nocopy number,
x_msg_data out nocopy varchar2) is
l_api_name constant varchar2(30) := 'delete_draft';
select parent_id
from amw_latest_hierarchies
where child_id = pid
and (organization_id is null or organization_id = -1);
select process_rev_id
into previd from amw_process
where process_id = p_process_id
and end_date is null;
p_procedure_name => 'delete_draft',
p_error_text => err_msg);
delete from amw_process
where process_id = p_process_id
and end_date is null;
delete from amw_process_names_tl
where process_rev_id = previd;
update amw_process
set end_date = null
where process_id = p_process_id
and approval_date is not null
and approval_end_date is null;
select standard_process_flag, process_rev_id
into l_flag, previd from amw_process
where process_id = p_process_id;
select 1 into l_dummy from dual
where exists
(select 1 from amw_process
where standard_variation = previd
and end_date is null);
p_procedure_name => 'delete_draft',
p_error_text => err_msg);
delete from amw_latest_hierarchies
where parent_id = parent_rec.parent_id
and child_id = p_process_id
and (organization_id is null or organization_id = -1);
delete from amw_latest_hierarchies
where parent_id = p_process_id
and (organization_id is null or organization_id = -1);
delete from amw_process where process_id = p_process_id;
delete from amw_process_names_tl where process_rev_id = previd;
delete from amw_risk_associations
where pk1 = p_process_id
and approval_date is null
and object_type = 'PROCESS';
update amw_risk_associations
set deletion_date = null
where pk1 = p_process_id
and object_type = 'PROCESS'
and deletion_date is not null
and deletion_approval_date is null;
delete from amw_control_associations
where pk1 = p_process_id
and approval_date is null
and object_type = 'RISK';
update amw_control_associations
set deletion_date = null
where pk1 = p_process_id
and object_type = 'RISK'
and deletion_date is not null
and deletion_approval_date is null;
delete from amw_acct_associations
where pk1 = p_process_id
and approval_date is null
and object_type = 'PROCESS';
update amw_acct_associations
set deletion_date = null
where pk1 = p_process_id
and object_type = 'PROCESS'
and deletion_date is not null
and deletion_approval_date is null;
delete from amw_objective_associations
where pk1 = p_process_id
and approval_date is null
and object_type in ('PROCESS', 'CONTROL');
update amw_objective_associations
set deletion_date = null
where pk1 = p_process_id
and object_type in ('PROCESS', 'CONTROL')
and deletion_date is not null
and deletion_approval_date is null;
delete from amw_significant_elements
where pk1 = p_process_id
and approval_date is null
and object_type = 'PROCESS';
update amw_significant_elements
set deletion_date = null
where pk1 = p_process_id
and object_type = 'PROCESS'
and deletion_date is not null
and deletion_approval_date is null;
FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments(X_entity_name => 'AMW_PROCESS',
X_pk1_value => previd);
amw_rl_hierarchy_pkg.update_denorm (-1, sysdate);
update_latest_risk_counts(p_process_id);
update_latest_control_counts(p_process_id);
select person_party_id
into l_party_id
from fnd_user
where user_id = G_USER_ID;
procedure delete_activities(p_parent_process_id in number,
p_child_id_string in varchar2,
x_return_status out nocopy varchar2,
x_msg_count out nocopy number,
x_msg_data out nocopy varchar2)
is
l_api_name constant varchar2(30) := 'delete_activities';
select LTRIM(l_child_string, '1234567890') into str from dual;
select SUBSTR(l_child_string, 1, diff) into childstr from dual;
delete from amw_latest_hierarchies where parent_id = p_parent_process_id
and child_id = l_child_id and organization_id = -1;
select LTRIM(str, 'x') into l_child_string from dual;
end delete_activities;
select LTRIM(l_child_string, '1234567890') into str from dual;
select SUBSTR(l_child_string, 1, diff) into childstr from dual;
--update the latest hierarchy table
insert into amw_latest_hierarchies(ORGANIZATION_ID,
PARENT_ID,
CHILD_ID,
CHILD_ORDER_NUMBER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
OBJECT_VERSION_NUMBER
)
VALUES (-1,
p_parent_process_id,
l_child_id,
AMW_CHILD_ORDER_S.nextval,
p_sysdate,
g_user_id,
g_login_id,
p_sysdate,
g_user_id,
1)
returning CHILD_ORDER_NUMBER
into l_child_order_number;
update_appr_ch_ord_num_if_reqd(-1, p_parent_process_id, l_child_id,
l_child_order_number);
select LTRIM(str, 'x') into l_child_string from dual;
PROCEDURE update_latest_denorm_counts
( p_process_id IN NUMBER,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2)
IS
L_API_NAME CONSTANT VARCHAR2(30) := 'update_latest_denorm_counts';
AMW_RL_HIERARCHY_PKG.update_denorm(p_org_id => -1);
update_latest_risk_counts(p_process_id => p_process_id);
update_latest_control_counts( p_process_id => p_process_id);
END update_latest_denorm_counts;