[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select count(*)
into l_msgCnt
from ZPB_ACCOUNT_STATES
where USER_ID = p_user
and BUSINESS_AREA_ID = p_business_area_id
and ACCOUNT_STATUS = 0;
select count(*)
into l_msgCnt
from ZPB_USERS
where USER_ID = p_user
and BUSINESS_AREA_ID = p_business_area_id;
insert into ZPB_USERS
(BUSINESS_AREA_ID,
USER_ID,
LAST_BUSAREA_LOGIN,
SHADOW_ID,
PERSONAL_AW,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY)
values
(p_business_area_id,
p_user,
'N',
p_user,
'ZPB'||p_user||'A'||p_business_area_id,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID);
select to_char(RESPONSIBILITY_ID)
into l_resp
from FND_RESPONSIBILITY
where RESPONSIBILITY_KEY = 'ZPB_MANAGER_RESP';
VIEWS_UPDATE (g_personalAW, p_user);
l_ignore := MEASURES_SHARED_UPDATE (p_user, l_vIgnore);
update ZPB_ACCOUNT_STATES
set READ_SCOPE = 1, WRITE_SCOPE = 1, OWNERSHIP = 1,
metadata_scope = 1,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
LAST_LOGIN_DATE = null
where USER_ID = p_user
and BUSINESS_AREA_ID = p_business_area_id;
UPDATE zpb_dc_objects
SET copy_source_type_flag = 'Y',
create_solve_program_flag = 'Y',
create_instance_measures_flag = 'Y',
status = 'DISTRIBUTION_PENDING'
WHERE object_type IN ('C', 'W')
AND object_user_id = p_user
AND business_area_id = p_business_area_id;
UPDATE zpb_dc_objects
SET copy_source_type_flag = 'Y',
create_solve_program_flag = 'Y',
create_instance_measures_flag = 'Y'
WHERE object_type IN ('E')
AND object_user_id = p_user
AND business_area_id = p_business_area_id;
procedure AW_DELETE(p_user in varchar2,
p_business_area_id in number)
is
l_count number;
select to_char(RESPONSIBILITY_ID)
into l_resp
from FND_RESPONSIBILITY
where RESPONSIBILITY_KEY = 'ZPB_MANAGER_RESP';
zpb_metadata_pkg.delete_user(g_personalAW);
zpb_aw.execute ('aw delete '||g_olapSchema||'.'||g_personalAW);
end AW_DELETE;
function AW_UPDATE(p_user IN VARCHAR2,
x_return_status IN OUT NOCOPY VARCHAR2,
p_read_only IN VARCHAR2)
return BOOLEAN
is
cursor state_cur is
select nvl (READ_SCOPE, 0) +
nvl(WRITE_SCOPE, 0) +
nvl(OWNERSHIP, 0) +
nvl(METADATA_SCOPE, 0) needs_update
from ZPB_ACCOUNT_STATES
where USER_ID = p_user
and RESP_ID = nvl(to_number(sys_context('ZPB_CONTEXT', 'resp_id')), FND_GLOBAL.RESP_ID)
and business_area_id = sys_context('ZPB_CONTEXT', 'business_area_id');
l_proc varchar2(9) := 'aw_update';
ZPB_LOG.WRITE ('zpb_metadata_map.aw_update.begin', 'Begin Metadata Update');
ZPB_AW.EXECUTE ('PA.VIEW.DELETED = no');
if (each.needs_update > 0) then
l_upd := true;
l_dims := METADATA_UPDATE(p_user, x_return_status);
ZPB_AW.EXECUTE ('PA.VIEW.DELETED = yes');
SECURITY_UPDATE(p_user, x_return_status);
VIEWS_UPDATE(g_personalAW, p_user, l_dims, 'Y');
update ZPB_DC_OBJECTS
set CREATE_SOLVE_PROGRAM_FLAG = 'Y',
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
where BUSINESS_AREA_ID = sys_context('ZPB_CONTEXT', 'business_area_id')
and OBJECT_USER_ID = p_user
and STATUS <> 'SUBMITTED_TO_SHARED';
update ZPB_ACCOUNT_STATES
set METADATA_SCOPE = 0, READ_SCOPE = 0, WRITE_SCOPE = 0, OWNERSHIP = 0,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
where USER_ID = p_user
and BUSINESS_AREA_ID = sys_context('ZPB_CONTEXT', 'business_area_id')
and nvl(READ_SCOPE, -1) <> G_LOCK_OUT
and nvl(WRITE_SCOPE, -1) <> G_LOCK_OUT
and nvl(OWNERSHIP, -1) <> G_LOCK_OUT
and nvl(METADATA_SCOPE, -1) <> G_LOCK_OUT;
VIEWS_UPDATE(g_personalAW, p_user, l_dims, 'Y');
l_ret2 := MEASURES_SHARED_UPDATE(p_user, x_return_status);
end AW_UPDATE;
zpb_aw.execute ('prp delete ''__READSCOPE''');
procedure MEASURES_DELETE(p_user in varchar2,
p_instance in varchar2,
p_type in varchar2,
p_template in varchar2,
p_approvee in varchar2)
is
l_mode varchar2(30);
end MEASURES_DELETE;
function MEASURES_SHARED_UPDATE(p_user IN VARCHAR2,
x_return_status IN OUT NOCOPY VARCHAR2)
return BOOLEAN
is
l_value varchar2(20);
zpb_log.write('zpb_personal_aw.measures_shared_update.begin',
'Creating structures for shared measures');
zpb_log.write('zpb_personal_aw.measures_shared_update.end', 'Done');
end MEASURES_SHARED_UPDATE;
zpb_aw.execute('mnt '||l_dim_ecm.LevelDim||' delete '||
l_dim_ecm.LevelDimScpFrm||' eq ''N''');
zpb_aw.execute('mnt '||l_dim_ecm.HierDim||' delete '||
l_dim_ecm.HierDimScpFrm||' eq ''N''');
zpb_aw.execute('mnt '||l_global_ecm.DimDim||' delete '||
l_global_attr_ecm.RangeDimRel);
zpb_aw.execute('mnt '||l_global_ecm.AttrDim||' delete '||
l_global_ecm.AttrDimScpFrm||' eq ''N''');
function METADATA_UPDATE(p_user IN VARCHAR2,
x_return_status IN OUT NOCOPY VARCHAR2)
return VARCHAR2
is
l_ret varchar2(500);
l_proc varchar2(15) := 'metadata_update';
l_ret := zpb_aw.interp('shw PA.META.UPDATE('''||p_user||''' '''||
g_olapSchema||'.'||g_sharedAW||''' '''||
g_olapSchema||'.'||g_personalAW||''')');
end METADATA_UPDATE;
procedure SECURITY_UPDATE(p_user IN VARCHAR2,
x_return_status IN OUT NOCOPY VARCHAR2)
is
l_proc varchar2(31) := 'security_update';
end SECURITY_UPDATE;
l_updated boolean;
select distinct TEMPLATE_ID, AC_INSTANCE_ID
from ZPB_DC_OBJECTS
where DELETE_INSTANCE_MEASURES_FLAG = 'Y'
and OBJECT_USER_ID = p_user
and business_area_id = sys_context('ZPB_CONTEXT', 'business_area_id');
select RESPONSIBILITY_ID
into l_mgrResp
from FND_RESPONSIBILITY
where RESPONSIBILITY_KEY = 'ZPB_MANAGER_RESP';
select APPLICATION_ID
into l_app
from FND_APPLICATION
where APPLICATION_SHORT_NAME = 'ZPB';
l_updated := false;
l_updated := AW_UPDATE(p_user, x_return_status, p_read_only);
MEASURES_DELETE (p_user, each.AC_INSTANCE_ID,
'PERSONAL', each.TEMPLATE_ID);
MEASURES_APPROVER_UPDATE(p_user, x_return_status);
procedure VIEWS_UPDATE(p_aw in varchar2,
p_user in varchar2,
p_dims in varchar2,
p_doPers in varchar2)
is
l_dims VARCHAR2(500);
zpb_log.write('zpb_personal_aw.views_update.begin',
'Updating metadata views for dims: '||p_dims);
zpb_log.write('zpb_personal_aw.views_update.end',
'Updated metadata views');
end VIEWS_UPDATE;
select awseq#
into l_val
from sys.aw$
where awname = upper(zpb_aw.get_personal_aw(p_user, p_business_area));
select sid
into l_val
from v$lock
where id1 = 2
and id2 = l_val
and lmode = 5;
select sid, serial#, username, osuser, status, schemaname, machine
into p_SID, p_serial_no, p_sess_user, p_os_user,
p_status, p_schema_name, p_machine
from v$session
where sid = l_val
and status <> 'KILLED';
zpb_log.write('zpb_personal_aw.views_update.end',
'Personal r/w AW sessions scanned for '||p_user);
select sysdate into l_starttime from dual;
select count(1) into l_stillExists
from v$session ses, v$aw_olap vao
where ses.sid=p_SID and
ses.logon_time
procedure MEASURES_APPROVER_UPDATE(p_user IN VARCHAR2,
x_return_status IN OUT NOCOPY VARCHAR2)
is
l_proc varchar2(25) := 'measures_approver_update';
cursor deleted_measures is
select AC_INSTANCE_ID,
OBJECT_USER_ID,
TEMPLATE_ID
from ZPB_DC_OBJECTS
where APPROVER_USER_ID = p_user
and DELETE_APPROVAL_MEASURES_FLAG = 'Y'
and business_area_id = sys_context('ZPB_CONTEXT', 'business_area_id');
select distinct
OBJECT_USER_ID
from ZPB_DC_OBJECTS A, ZPB_ANALYSIS_CYCLES B , ZPB_MEASURES Z
where APPROVER_USER_ID = p_user
and A.AC_INSTANCE_ID = B.ANALYSIS_CYCLE_ID
and A.AC_INSTANCE_ID = Z.INSTANCE_ID
and A.TEMPLATE_ID = Z.TEMPLATE_ID
and Z.TYPE = 'APPROVER_DATA'
and A.OBJECT_USER_ID = Z.APPROVEE_ID
and A.OBJECT_TYPE in ('C', 'W')
and B.STATUS_CODE <> 'MARKED_FOR_DELETION'
and A.business_area_id = sys_context('ZPB_CONTEXT', 'business_area_id')
and A.STATUS='SUBMITTED';
for del_meas in deleted_measures
loop
begin
MEASURES_DELETE (p_user, del_meas.AC_INSTANCE_ID, 'APPROVER',
del_meas.TEMPLATE_ID, del_meas.OBJECT_USER_ID);
update ZPB_DC_OBJECTS
set DELETE_APPROVAL_MEASURES_FLAG = 'N'
where APPROVER_USER_ID = p_user
and AC_INSTANCE_ID = del_meas.AC_INSTANCE_ID
and TEMPLATE_ID = del_meas.TEMPLATE_ID
and OBJECT_USER_ID = del_meas.OBJECT_USER_ID;
end MEASURES_APPROVER_UPDATE;
PROCEDURE UPDATE_SHADOW (p_business_area_id IN NUMBER,
p_shadow_id IN NUMBER)
is
l_id_to_set_to number;
update ZPB_USERS
set SHADOW_ID = l_id_to_set_to,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_DATE = sysdate
where USER_ID = FND_GLOBAL.USER_ID
and BUSINESS_AREA_ID = p_business_area_id;
end UPDATE_SHADOW;