APPS.ITA_NOTIFICATION_PKG SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 11

select ORGANIZATION_ID_PARENT, ORGANIZATION_ID_CHILD
from PER_ORG_STRUCTURE_ELEMENTS
where ORG_STRUCTURE_VERSION_ID =
(
select ORG_STRUCTURE_VERSION_ID
from PER_ORG_STRUCTURE_VERSIONS
where
DATE_TO is null and
ORGANIZATION_STRUCTURE_ID =
(
select ORGANIZATION_STRUCTURE_ID
from PER_ORGANIZATION_STRUCTURES
where NAME = FND_PROFILE.VALUE(''AMW_ORG_SECURITY_HIERARCHY'')
)
)
)
';

Line: 40

select distinct ORGANIZATION_ID
from AMW_AUDIT_UNITS_V
where
ORGANIZATION_ID = ' || to_char(p_org_id) || '
)
';

Line: 49

select ORGANIZATION_ID_PARENT from ' || GET_ORG_CONNECTIONS_STRING() || '
union
select ORGANIZATION_ID_CHILD from ' || GET_ORG_CONNECTIONS_STRING() || '
start with ORGANIZATION_ID_PARENT = ' || to_char(p_org_id) || '
connect by ORGANIZATION_ID_PARENT = prior ORGANIZATION_ID_CHILD
)
*/
end GET_AUDIT_UNITS_STRING;

Line: 66

select distinct OBJECT_TYPE, PK1, PK2, PK3, PK4
from AMW_CONTROL_ASSOCIATIONS
where
CONTROL_ID = ' || to_char(p_control_id) || ' and
APPROVAL_DATE is not null and
DELETION_APPROVAL_DATE is null and
(OBJECT_TYPE = ''RISK_ORG'' or OBJECT_TYPE = ''RISK'' or OBJECT_TYPE = ''ENTITY_CONTROL'')
';

Line: 76

select distinct OBJECT_TYPE, PK1, PK2, PK3, PK4
from AMW_CONTROL_ASSOCIATIONS
where
CONTROL_ID = ' || to_char(p_control_id) || ' and
APPROVAL_DATE is not null and
DELETION_APPROVAL_DATE is null and
(
(
OBJECT_TYPE = ''RISK_ORG'' and
PK1 = ' || to_char(p_org_id) || '
)
or
(
OBJECT_TYPE = ''RISK'' and
PK1 in
(
select distinct PROCESS_ID
from AMW_PROCESS_ORGANIZATION
where ORGANIZATION_ID = ' || to_char(p_org_id) || '
)
)
or
(
OBJECT_TYPE = ''ENTITY_CONTROL'' and
PK1 = ' || to_char(p_org_id) || '
)
)
';

Line: 115

select distinct PROCESS_ID
from AMW_PROCESS_ORGANIZATION
where ORGANIZATION_ID in ' || GET_AUDIT_UNITS_STRING(p_org_id) || '
)
)
or
(
OBJECT_TYPE = ''ENTITY_CONTROL'' and
PK1 in ' || GET_AUDIT_UNITS_STRING(p_org_id) || '
)
)
*/
end if;

Line: 150

m_updated_by VARCHAR2(100);

Line: 151

m_updated_on DATE;

Line: 164

select distinct CONTROL_ID, NAME, SOURCE
from AMW_CONTROLS_ALL_VL
where
SOURCE = p_parameter_code or
SOURCE in
(
select PARAMETER_CODE
from ITA_PARAMETER_HIERARCHY
start with OVERRIDE_PARAMETER_CODE = p_parameter_code
connect by prior PARAMETER_CODE = OVERRIDE_PARAMETER_CODE
);

Line: 177

select distinct to_number(replace(grants.GRANTEE_KEY, 'HZ_PARTY:', '')) owner_id
from
FND_GRANTS grants,
FND_OBJECTS objects,
FND_MENUS menus
where
objects.OBJ_NAME = 'AMW_PROCESS_ORGANIZATION' and
grants.OBJECT_ID = objects.OBJECT_ID and
grants.GRANTEE_TYPE = 'USER' and
grants.INSTANCE_TYPE = 'INSTANCE' and
grants.INSTANCE_PK1_VALUE = to_char(p_org_id) and
grants.INSTANCE_PK2_VALUE = to_char(p_process_id) and
grants.GRANTEE_KEY like 'HZ_PARTY%' and
nvl(grants.END_DATE, SYSDATE + 1) >= trunc(sysdate) and
grants.MENU_ID = menus.MENU_ID and
menus.MENU_NAME = 'AMW_ORG_PROC_OWNER_ROLE';

Line: 195

select distinct to_number(replace(grants.GRANTEE_KEY, 'HZ_PARTY:', '')) owner_id
from
FND_GRANTS grants,
FND_OBJECTS objects,
FND_MENUS menus
where
objects.OBJ_NAME = 'AMW_PROCESS_APPR_ETTY' and
grants.OBJECT_ID = objects.OBJECT_ID and
grants.GRANTEE_TYPE = 'USER' and
grants.INSTANCE_TYPE = 'INSTANCE' and
grants.INSTANCE_PK1_VALUE = to_char(p_process_id) and
grants.GRANTEE_KEY like 'HZ_PARTY%' and
nvl(grants.END_DATE, SYSDATE + 1) >= trunc(sysdate) and
grants.MENU_ID = menus.MENU_ID and
menus.MENU_NAME = 'AMW_RL_PROC_OWNER_ROLE';

Line: 212

select distinct to_number(replace(grants.GRANTEE_KEY, 'HZ_PARTY:', '')) owner_id
from
FND_GRANTS grants,
FND_OBJECTS objects,
FND_MENUS menus
where
objects.OBJ_NAME = 'AMW_ORGANIZATION' and
grants.OBJECT_ID = objects.OBJECT_ID and
grants.GRANTEE_TYPE = 'USER' and
grants.INSTANCE_TYPE = 'INSTANCE' and
grants.INSTANCE_PK1_VALUE = to_char(p_org_id) and
grants.GRANTEE_KEY like 'HZ_PARTY%' and
nvl(grants.END_DATE, SYSDATE + 1) >= trunc(sysdate) and
grants.MENU_ID = menus.MENU_ID and
menus.MENU_NAME = 'AMW_ORG_MANAGER_ROLE';

Line: 231

select
change.PARAMETER_CODE,
(
select PARAMETER_NAME
from ITA_SETUP_PARAMETERS_VL
where PARAMETER_CODE = change.PARAMETER_CODE
),
change.SETUP_GROUP_CODE,
change.PK1_VALUE,
decode(change.SETUP_GROUP_CODE, 'FND.FND_PROFILE_OPTION_VALUES', nvl(change.PK6_VALUE, ' '), nvl(change.PK1_VALUE, ' ')),
change.PK2_VALUE,
change.RECOMMENDED_VALUE,
change.PRIOR_VALUE,
change.CURRENT_VALUE,
change.CHANGE_AUTHOR,
change.CHANGE_DATE
into
m_parameter_code,
m_parameter_name,
m_setup_group_code,
m_pk1_value,
m_org_name,
m_org_id,
m_rec_value,
m_prior_value,
m_current_value,
m_updated_by,
m_updated_on
from ITA_SETUP_CHANGE_HISTORY change
where
change.INSTANCE_CODE = 'CURRENT' and
change.CHANGE_ID = p_change_id;

Line: 264

select
setup_gp.SETUP_GROUP_NAME,
(
select APPLICATION_NAME
from FND_APPLICATION_VL
where APPLICATION_ID = setup_gp.TABLE_APP_ID
),
setup_gp.HIERARCHY_LEVEL,
decode(setup_gp.SETUP_GROUP_CODE, 'FND.FND_PROFILE_OPTION_VALUES',
(
select MEANING
from FND_LOOKUP_VALUES
where
LANGUAGE = USERENV('LANG') and
VIEW_APPLICATION_ID = 438 and
LOOKUP_TYPE = 'ITA_PROFILE_LEVEL_ID' and
LOOKUP_CODE = m_pk1_value
),
(
select HIERARCHY_LEVEL_NAME
from ITA_SETUP_HIERARCHY_VL
where HIERARCHY_LEVEL_CODE = setup_gp.HIERARCHY_LEVEL
)
)
into
m_setup_group_name,
m_application,
m_org_type_code,
m_org_type
from ITA_SETUP_GROUPS_VL setup_gp
where
setup_gp.SETUP_GROUP_CODE = m_setup_group_code;

Line: 302

select 1 into m_is_audit_unit
from HR_ORGANIZATION_INFORMATION org_info
where
org_info.ORGANIZATION_ID = m_org_id and
org_info.ORG_INFORMATION_CONTEXT = 'CLASS' and
org_info.ORG_INFORMATION1 = 'AMW_AUDIT_UNIT';

Line: 318

select MEANING into m_org_type_test
from FND_LOOKUP_VALUES
where
LANGUAGE = USERENV('LANG') and
VIEW_APPLICATION_ID = 438 and
LOOKUP_TYPE = 'ITA_PROFILE_LEVEL_ID' and
LOOKUP_CODE = '10001';

Line: 336

m_rec_value, m_prior_value, m_current_value, m_updated_on, m_updated_by);

Line: 354

m_rec_value, m_prior_value, m_current_value, m_updated_on, m_updated_by);

Line: 360

m_rec_value, m_prior_value, m_current_value, m_updated_on, m_updated_by);

Line: 369

m_rec_value, m_prior_value, m_current_value, m_updated_on, m_updated_by);

Line: 378

m_rec_value, m_prior_value, m_current_value, m_updated_on, m_updated_by);

Line: 407

p_updated_on IN DATE,
p_updated_by IN VARCHAR2) is

m_message_subject VARCHAR2(1000);

Line: 420

select NAME, substrb(DISPLAY_NAME, 1, 360) display_name
from WF_ROLES
where
ORIG_SYSTEM = 'PER' and
ORIG_SYSTEM_ID = p_orig_system_id
order by STATUS, START_DATE;

Line: 448

FND_MESSAGE.SET_TOKEN('UPDATED_ON', to_char(p_updated_on), TRUE);

Line: 449

FND_MESSAGE.SET_TOKEN('UPDATED_BY', p_updated_by, TRUE);

Line: 457

select EMPLOYEE_ID into m_process_owner_emp_id
from AMW_EMPLOYEES_CURRENT_V
where PARTY_ID = p_process_owner_id;

Line: 495

WF_NOTIFICATION.SetAttrText(m_notification_id, 'UPDATED_ON', p_updated_on);

Line: 496

WF_NOTIFICATION.SetAttrText(m_notification_id, 'UPDATED_BY', p_updated_by);