[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
x_index_tbl.delete;
x_t1.delete;
x_t2.delete;
update amw_process_organization
set approval_status = 'PA'
where process_id = p_process_id
and organization_id = p_org_id
and end_date is null;
insert into amw_process_locks
(organization_id,
locking_process_id,
locked_process_id,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
object_version_number
)
values
(
p_org_id,
p_process_id,
p_process_id,
sysdate,
G_USER_ID,
G_LOGIN_ID,
sysdate,
G_USER_ID,
1
);
insert into amw_process_locks
(organization_id,
locking_process_id,
locked_process_id,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
object_version_number
)
(select distinct p_org_id, p_process_id, child_id, sysdate, G_USER_ID, G_LOGIN_ID, sysdate, G_USER_ID, 1
from amw_latest_hierarchies
start with parent_id = p_process_id and organization_id = p_org_id
connect by prior child_id = parent_id and organization_id = p_org_id );
/* (select p_org_id, p_process_id, parent_child_id, sysdate, G_USER_ID, G_LOGIN_ID, sysdate, G_USER_ID, 1
from amw_org_hierarchy_denorm
where process_id = p_process_id
and up_down_ind = 'D'
and organization_id = p_org_id
and hierarchy_type = 'L');
insert into amw_process_locks
(organization_id,
locking_process_id,
locked_process_id,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
object_version_number
)
(select p_org_id, p_process_id, child_process_id, sysdate, G_USER_ID, G_LOGIN_ID, sysdate, G_USER_ID, 1
from amw_latest_hierarchy_ORG_V
where parent_process_id = p_process_id
and child_organization_id = p_org_id);
insert into amw_process_locks
(organization_id,
locking_process_id,
locked_process_id,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
object_version_number
)(select distinct p_org_id, p_process_id, child_id, sysdate, G_USER_ID, G_LOGIN_ID, sysdate, G_USER_ID, 1
from amw_latest_hierarchies
start with parent_id = p_process_id and organization_id = p_org_id
connect by prior child_id = parent_id and organization_id = p_org_id );
/* (select p_org_id, p_process_id, parent_child_id, sysdate, G_USER_ID, G_LOGIN_ID, sysdate, G_USER_ID, 1
from amw_org_hierarchy_denorm
where process_id = p_process_id
and up_down_ind = 'D'
and organization_id = p_org_id
and hierarchy_type = 'L');
p_update_count in varchar2 := FND_API.G_TRUE) is
rev_num number;
select distinct child_id parent_child_id
from amw_latest_hierarchies
start with parent_id = p_process_id and organization_id = p_org_id
connect by prior child_id = parent_id and organization_id = p_org_id;
select distinct child_id parent_child_id,parent_id
from amw_latest_hierarchies
start with parent_id = p_process_id and organization_id = (-1*p_org_id)
connect by prior child_id = parent_id and organization_id = (-1*p_org_id);
/* select parent_child_id
from amw_org_hierarchy_denorm
where process_id = p_process_id
and up_down_ind = 'D'
and organization_id = p_org_id
and hierarchy_type = 'L';
select approval_status
into curr_app_status
from amw_process_organization
where process_id = p_process_id
and organization_id = p_org_id
and end_date is null;
delete from amw_process_locks
where organization_id = p_org_id
and locking_process_id = p_process_id;
update amw_process_organization
set approval_status = 'A',
approval_date = APPROV_TXN_DATE
where process_id = p_process_id
and organization_id = p_org_id
and end_date is null
returning revision_number into rev_num;
update amw_process_organization
set approval_end_date = APPROV_TXN_DATE
where process_id = p_process_id
and organization_id = p_org_id
and revision_number = (rev_num-1);
IF p_update_count = FND_API.G_TRUE THEN
-- Now updat the Org Count......
amw_rl_hierarchy_pkg.update_org_count(p_process_id);
update amw_process_organization
set approval_status = 'A',
approval_date = APPROV_TXN_DATE
where process_id = c2_rec.parent_child_id
and organization_id = p_org_id
and end_date is null
returning revision_number into rev_num;
update amw_process_organization
set approval_end_date = APPROV_TXN_DATE
where process_id = c2_rec.parent_child_id
and organization_id = p_org_id
and revision_number = (rev_num-1);
IF p_update_count = FND_API.G_TRUE THEN
-- Update the Org Count
amw_rl_hierarchy_pkg.update_org_count(c2_rec.parent_child_id);
delete from amw_latest_hierarchies
where child_id = c2_rec.parent_child_id
and parent_id = c2_rec.parent_id
and organization_id = -p_org_id;
IF p_update_count = FND_API.G_TRUE THEN
amw_org_hierarchy_pkg.upd_appr_control_count(p_org_id, null); --ko, commenting this.. -2);
update amw_process_organization
set approval_status = 'A',
approval_date = APPROV_TXN_DATE
where process_id = c1_rec.parent_child_id
and organization_id = p_org_id
and end_date is null
returning revision_number into rev_num;
update amw_process_organization
set approval_end_date = APPROV_TXN_DATE
where process_id = c1_rec.parent_child_id
and organization_id = p_org_id
and revision_number = (rev_num-1);
IF p_update_count = FND_API.G_TRUE THEN
-- Update the Org Count
amw_rl_hierarchy_pkg.update_org_count(c1_rec.parent_child_id);
amw_rl_hierarchy_pkg.update_approved_denorm(p_org_id);
IF p_update_count = FND_API.G_TRUE THEN
amw_org_hierarchy_pkg.upd_appr_control_count(p_org_id, null); --ko, commenting this.. -2);
update amw_process_organization
set approval_status = 'D'
where process_id = p_process_id
and organization_id = p_org_id
and end_date is null;
delete from amw_process_locks
where organization_id = p_org_id
and locking_process_id = p_process_id;
select 1 --parent_child_id, a.approval_status
into l_dummy
from amw_process_organization a
where a.organization_id = p_org_id
and a.end_date is null
and a.approval_status <> 'A'
and a.process_id in ( select alh.child_id
from amw_latest_hierarchies alh
start with alh.parent_id = p_process_id and alh.organization_id = p_org_id
connect by prior alh.child_id = alh.parent_id and alh.organization_id = p_org_id);
select 1 --parent_child_id, a.approval_status
into l_dummy
from amw_org_hierarchy_denorm d, amw_process_organization a
where d.process_id = p_process_id
and d.organization_id = p_org_id
and up_down_ind = 'D'
and hierarchy_type = 'L'
and a.process_id = d.parent_child_id
and a.organization_id = p_org_id
and a.end_date is null
and a.approval_status <> 'A';
select 1 --parent_child_id, a.approval_status
into l_dummy
from amw_process_organization a
where a.organization_id = p_org_id
and a.end_date is null
and a.approval_status <> 'A'
and a.process_id in ( select alh.child_id
from amw_latest_hierarchies alh
start with alh.parent_id = p_process_id and alh.organization_id = p_org_id
connect by prior alh.child_id = alh.parent_id and alh.organization_id = p_org_id);
select 1 --parent_child_id, a.approval_status
into l_dummy
from amw_org_hierarchy_denorm d, amw_process_organization a
where d.process_id = p_process_id
and d.organization_id = p_org_id
and up_down_ind = 'D'
and hierarchy_type = 'L'
and a.process_id = d.parent_child_id
and a.organization_id = p_org_id
and a.end_date is null
and a.approval_status <> 'A';
update amw_risk_associations
set approval_date = APPROV_TXN_DATE
where pk2 = p_process_id
and pk1 = p_org_id
and object_type = 'PROCESS_ORG'
and approval_date is null;
update amw_risk_associations
set deletion_approval_date = APPROV_TXN_DATE
where pk2 = p_process_id
and pk1 = p_org_id
and object_type = 'PROCESS_ORG'
and deletion_date is not null
and deletion_approval_date is null;
update amw_control_associations
set approval_date = APPROV_TXN_DATE
where pk2 = p_process_id
and pk1 = p_org_id
and object_type = 'RISK_ORG'
and approval_date is null;
update amw_control_associations
set deletion_approval_date = APPROV_TXN_DATE
where pk2 = p_process_id
and pk1 = p_org_id
and object_type = 'RISK_ORG'
and deletion_date is not null
and deletion_approval_date is null;
update amw_acct_associations
set approval_date = APPROV_TXN_DATE
where pk2 = p_process_id
and pk1 = p_org_id
and object_type = 'PROCESS_ORG'
and approval_date is null;
update amw_acct_associations
set deletion_approval_date = APPROV_TXN_DATE
where pk2 = p_process_id
and pk1 = p_org_id
and object_type = 'PROCESS_ORG'
and deletion_date is not null
and deletion_approval_date is null;
update amw_objective_associations
set approval_date = APPROV_TXN_DATE
where pk1 = p_org_id
and pk2 = p_process_id
and object_type in ( 'PROCESS_ORG' , 'CONTROL_ORG')
and approval_date is null;
update amw_objective_associations
set deletion_approval_date = APPROV_TXN_DATE
where pk1 = p_org_id
and pk2 = p_process_id
and object_type in ( 'PROCESS_ORG' , 'CONTROL_ORG')
and deletion_date is not null
and deletion_approval_date is null;
update amw_ap_associations
set association_creation_date = sysdate
where pk1 = p_org_id
and pk2 = p_process_id
and object_type = 'CTRL_ORG'
and association_creation_date is null;
update AMW_AP_ASSOCIATIONS
set deletion_date = sysdate
WHERE object_type = 'CTRL_ORG'
and pk1 = p_org_id
and pk2 = p_process_id
and association_creation_date is not null
and pk3 not in ( SELECT control_id from
amw_control_associations
where object_type = 'RISK_ORG'
AND PK1 = p_org_id
AND PK2 = p_process_id
AND approval_date is not null
and deletion_approval_date is null);
/* update amw_objective_associations
set approval_date = APPROV_TXN_DATE
where pk1 = p_process_id
and object_type = 'PROCESS_ORG'
and approval_date is null;
update amw_objective_associations
set deletion_approval_date = APPROV_TXN_DATE
where pk1 = p_process_id
and object_type = 'PROCESS_ORG'
and deletion_date is not null
and deletion_approval_date is null;
update amw_significant_elements
set approval_date = APPROV_TXN_DATE
where pk1 = p_process_id
and object_type = 'PROCESS_ORG'
and approval_date is null;
update amw_significant_elements
set deletion_approval_date = APPROV_TXN_DATE
where pk1 = p_process_id
and object_type = 'PROCESS_ORG'
and deletion_date is not null
and deletion_approval_date is null; */
(select parent_process_id, child_process_id, child_order_number
from (select * from amw_latest_hierarchy_ORG_V where child_organization_id = p_org_id and parent_approval_status = 'A' and child_approval_status = 'A')
start with parent_process_id = -2
connect by prior child_process_id = parent_process_id)
MINUS
(select parent_process_id, child_process_id, child_order_number
from AMW_CURR_APP_HIERARCHY_ORG_V
where child_organization_id = p_org_id);
(select parent_process_id, child_process_id from AMW_CURR_APP_HIERARCHY_ORG_V
where parent_process_id = p_process_id
and child_organization_id = p_org_id)
MINUS
(select parent_process_id, child_process_id from amw_latest_hierarchy_ORG_V
where parent_process_id = p_process_id
and child_organization_id = p_org_id);
(select parent_process_id, child_process_id from AMW_CURR_APP_HIERARCHY_ORG_V
where parent_process_id is not null
and child_organization_id = p_org_id)
MINUS
(select parent_process_id, child_process_id from
(select * from AMW_CURR_APP_HIERARCHY_ORG_V where child_organization_id = p_org_id)
start with parent_process_id = -2
connect by prior child_process_id = parent_process_id);
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
(p_org_id,
a_link.parent_process_id,
a_link.child_process_id,
APPROV_TXN_DATE,
a_link.child_order_number,
sysdate,
G_USER_ID,
G_LOGIN_ID,
sysdate,
G_USER_ID,
1
);
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
(p_org_id,
x_parent_tbl(i),
x_child_tbl(i),
APPROV_TXN_DATE,
x_child_ord_tbl(i),
sysdate,
G_USER_ID,
G_LOGIN_ID,
sysdate,
G_USER_ID,
1
);
update amw_approved_hierarchies
set end_date = APPROV_TXN_DATE,
object_version_number = object_version_number + 1
where organization_id = p_org_id
and parent_id = defunct_link.parent_process_id
and child_id = defunct_link.child_process_id
and end_date is null;
update amw_approved_hierarchies
set end_date = APPROV_TXN_DATE,
object_version_number = object_version_number + 1
where organization_id = p_org_id
and parent_id = defunct_link.parent_process_id
and child_id = defunct_link.child_process_id
and end_date is null;
update amw_approved_hierarchies
set end_date = APPROV_TXN_DATE,
object_version_number = object_version_number + 1
where organization_id = p_org_id
and parent_id = x_parent_tbl(i)
and child_id = x_child_tbl(i)
and end_date is null;
select distinct alh.child_id parent_child_id
from amw_latest_hierarchies alh
start with alh.child_id = p_process_id and alh.organization_id = p_org_id
connect by prior alh.child_id = alh.parent_id and alh.organization_id = p_org_id;
select parent_child_id
from amw_org_hierarchy_denorm
where process_id = pid
and up_down_ind = 'D'
and hierarchy_type = 'L'
and organization_id = p_org_id
union
select pid from dual;
select risk_id from amw_risk_associations where pk2 = pid and pk1 = poid and object_type = 'PROCESS_ORG';
select control_id from amw_control_associations where pk2 = pid and pk1 = poid and object_type = 'RISK_ORG';
select 1
into l_dummy
from amw_risks_b
where risk_id = ass_risks_rec.risk_id
and approval_status = 'A';
select 1
into l_dummy
from amw_controls_b
where control_id = ass_controls_rec.control_id
and approval_status = 'A';
select 1
into l_dummy
from amw_risks_b
where risk_id = ass_risks_rec.risk_id
and approval_status = 'A';
select 1
into l_dummy
from amw_controls_b
where control_id = ass_controls_rec.control_id
and approval_status = 'A';
CURSOR deleted_exceptions(org_id NUMBER, process_id NUMBER) IS
SELECT exception_object_id,
old_pk1,
old_pk2,
old_pk3,
old_pk4,
old_pk5,
old_pk6,
object_type
FROM amw_exceptions_b
WHERE approved_flag = 'N'
AND old_PK1 = org_Id
AND old_PK2 = process_id
AND transaction_type = 'DEL'
AND object_type IN ('PROCESS', 'RISK', 'CTRL')
AND end_date IS NULL;
SELECT exception_object_id,
new_pk1,
new_pk2,
new_pk3,
new_pk4,
new_pk5,
new_pk6,
object_type
FROM amw_exceptions_b
WHERE approved_flag = 'N'
AND new_pk1 = org_Id
AND new_pk2 = process_id
AND transaction_type = 'ADD'
AND object_type IN ('PROCESS', 'RISK', 'CTRL')
AND end_date IS NULL;
SELECT exception_object_id
FROM amw_exceptions_b
WHERE NVL(new_pk1, -99) = NVL(p_pk1, -99)
AND NVL(new_pk2, -99) = NVL(p_pk2, -99)
AND NVL(new_pk3, -99) = NVL(p_pk3, -99)
AND NVL(new_pk4, -99) = NVL(p_pk4, -99)
AND NVL(new_pk5, -99) = NVL(p_pk5, -99)
AND NVL(new_pk6, -99) = NVL(p_pk6, -99)
AND object_type = p_obj_type
AND transaction_type = 'ADD'
AND end_date IS NULL;
SELECT exception_object_id
FROM amw_exceptions_b
WHERE NVL(old_pk1, -99) = NVL(p_pk1, -99)
AND NVL(old_pk2, -99) = NVL(p_pk2, -99)
AND NVL(old_pk3, -99) = NVL(p_pk3, -99)
AND NVL(old_pk4, -99) = NVL(p_pk4, -99)
AND NVL(old_pk5, -99) = NVL(p_pk5, -99)
AND NVL(old_pk6, -99) = NVL(p_pk6, -99)
AND object_type = p_obj_type
AND transaction_type = 'DEL'
AND end_date IS NULL;
deleted_exceptions_rec deleted_exceptions%ROWTYPE;
UPDATE amw_exceptions_b SET end_date = SYSDATE where exception_object_id = past_appr_del_ex_rec.exception_object_id;
UPDATE amw_exceptions_b SET end_date = SYSDATE, approved_flag = 'Y' where exception_object_id = added_exceptions_rec.exception_object_id;
-- Similarly Check For the Delete Exceptions .... End Date if any previous exceptions exists..
for deleted_exceptions_rec in deleted_exceptions(p_org_id, p_process_id) LOOP
EXIT WHEN deleted_exceptions%NOTFOUND;
deleted_exceptions_rec.OLD_PK1,
deleted_exceptions_rec.OLD_PK2,
deleted_exceptions_rec.OLD_PK3,
deleted_exceptions_rec.OLD_PK4,
deleted_exceptions_rec.OLD_PK5,
deleted_exceptions_rec.OLD_PK6,
deleted_exceptions_rec.OBJECT_TYPE) LOOP
EXIT WHEN past_appr_addd_ex%NOTFOUND;
UPDATE amw_exceptions_b SET end_date = SYSDATE where exception_object_id = past_appr_addd_ex_rec.exception_object_id;
UPDATE amw_exceptions_b SET end_date = SYSDATE, approved_flag = 'Y' where exception_object_id = deleted_exceptions_rec.exception_object_id;
UPDATE amw_exceptions_b
SET approved_flag = 'Y'
WHERE end_date is null
AND ( (old_pk1 = p_org_id AND old_pk2 = p_process_id AND transaction_type = 'DEL')
OR (new_pk1 = p_org_id AND new_pk2 = p_process_id AND transaction_type = 'ADD') )
AND object_type IN ('PROCESS' , 'RISK' , 'CTRL');
select ah.parent_id , ah.child_id, AH.CHILD_ORDER_NUMBER
from amw_latest_hierarchies ah
where ah.parent_id =(select pp.process_id
from amw_process_organization pp
where pp.organization_id = ah.organization_id
and pp.process_id = ah.parent_id
and pp.end_date is null
and pp.APPROVAL_STATUS ='A')
and ah.child_id = ( select Cp.process_id
from amw_process_organization Cp
where Cp.organization_id = ah.organization_id
and Cp.process_id = ah.child_id
and Cp.end_date is null
and Cp.APPROVAL_STATUS ='A')
and ah.organization_id = l_org_id;
select ah.parent_id , ah.child_id
from amw_approved_hierarchies ah
where ah.parent_id =(select pp.process_id
from amw_process_organization pp
where pp.organization_id = ah.organization_id
and pp.process_id = ah.parent_id
and pp.approval_date is not null
and pp.approval_end_date is null
and pp.deletion_date is null)
and ah.child_id = ( select Cp.process_id
from amw_process_organization Cp
where Cp.organization_id = ah.organization_id
and Cp.process_id = ah.child_id
and Cp.approval_date is not null
and Cp.approval_end_date is null
and Cp.deletion_date is null)
and ah.start_date is not null
and ah.end_date is null
and ah.organization_id = l_org_id;
select ah.parent_id , ah.child_id
from amw_approved_hierarchies ah
where ah.parent_id =(select pp.process_id
from amw_process_organization pp
where pp.organization_id = ah.organization_id
and pp.process_id = ah.parent_id
and pp.approval_date is not null
and pp.approval_end_date is null
and pp.deletion_date is null)
and ah.child_id = ( select Cp.process_id
from amw_process_organization Cp
where Cp.organization_id = ah.organization_id
and Cp.process_id = ah.child_id
and Cp.approval_date is not null
and Cp.approval_end_date is null
and Cp.deletion_date is null)
and ah.start_date is not null
and ah.end_date is null
and ah.organization_id = l_org_id;