[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT rrs_hier_header_rec(
RSGB.SITE_GROUP_ID,
RSGB.SITE_GROUP_TYPE_CODE,
RSGT.NAME,
RSGB.GROUP_PURPOSE_CODE,
LKUP.MEANING,
RSGT.DESCRIPTION,
RSGB.START_DATE,
RSGB.END_DATE)
INTO x_hier_header_rec
FROM RRS_SITE_GROUP_VERSIONS RSGV, RRS_SITE_GROUPS_B RSGB, RRS_SITE_GROUPS_TL RSGT, RRS_LOOKUPS_V LKUP
WHERE RSGT.LANGUAGE = userenv('LANG')
AND RSGV.SITE_GROUP_VERSION_ID = p_hier_version_id
AND RSGB.SITE_GROUP_ID = RSGV.SITE_GROUP_ID
AND RSGT.SITE_GROUP_ID = RSGB.SITE_GROUP_ID
AND 'RRS_HIERARCHY_PURPOSE' = LKUP.LOOKUP_TYPE(+)
AND RSGB.GROUP_PURPOSE_CODE = LKUP.LOOKUP_CODE(+);
SELECT MAX(VERSION_NUMBER)
INTO l_hier_version_number
FROM RRS_SITE_GROUP_VERSIONS
WHERE SITE_GROUP_ID = p_hier_id;
SELECT SITE_GROUP_VERSION_ID
INTO l_hier_version_id
FROM RRS_SITE_GROUP_VERSIONS
WHERE SITE_GROUP_ID = p_hier_id
AND VERSION_NUMBER = l_hier_version_number;
SELECT SITE_GROUP_ID
INTO l_hier_id
FROM RRS_SITE_GROUPS_VL
WHERE NAME = p_hier_name;
SELECT SITE_GROUP_ID
INTO l_site_group_id
FROM RRS_SITE_GROUP_VERSIONS
WHERE SITE_GROUP_VERSION_ID = p_hier_version_id;
SELECT SITE_ID
INTO l_parent_id
FROM RRS_SITES_B
WHERE SITE_IDENTIFICATION_NUMBER = p_parent_member_id_num;
SELECT SITE_GROUP_NODE_ID
INTO l_parent_id
FROM RRS_SITE_GROUP_NODES_B
WHERE NODE_IDENTIFICATION_NUMBER = p_parent_member_id_num;
SELECT rrs_hier_members_rec(
SiteGroupMembers.SITE_GROUP_ID,
SiteGroupMembers.SITE_GROUP_VERSION_ID,
DECODE(SiteGroupMembers.PARENT_MEMBER_ID, -1, NULL, DECODE(SiteGroupNodes2.SITE_GROUP_NODE_ID, NULL, Sites2.SITE_ID,
SiteGroupNodes2.SITE_GROUP_NODE_ID)),
DECODE(SiteGroupMembers.PARENT_MEMBER_ID, -1, NULL, DECODE(SiteGroupNodes2.SITE_GROUP_NODE_ID, NULL, Sites2.SITE_IDENTIFICATION_NUMBER,
SiteGroupNodes2.NODE_IDENTIFICATION_NUMBER)),
DECODE(SiteGroupMembers.PARENT_MEMBER_ID, -1, NULL, DECODE(SiteGroupNodes2.NAME, NULL, Sites2.NAME,SiteGroupNodes2.NAME)),
DECODE(SiteGroupMembers.PARENT_MEMBER_ID, -1, NULL, DECODE(SiteGroupNodes2.SITE_GROUP_NODE_ID, NULL, 'SITE', 'NODE')),
DECODE(SiteGroupMembers.PARENT_MEMBER_ID, -1, NULL,
DECODE(SiteGroupNodes2.SITE_GROUP_NODE_ID, NULL,
(SELECT LKUP.MEANING
FROM RRS_SITE_USES RSU, AR_LOOKUPS LKUP
WHERE RSU.SITE_ID = SiteGroupMembers.PARENT_MEMBER_ID AND RSU.IS_PRIMARY_FLAG = 'Y' AND LKUP.LOOKUP_TYPE = 'PARTY_SITE_USE_CODE' AND RSU.SITE_USE_TYPE_CODE = LKUP.LOOKUP_CODE),
(SELECT LKUP.MEANING
FROM RRS_SITE_GROUP_NODES_B RSGNB, RRS_LOOKUPS_V LKUP
WHERE RSGNB.SITE_GROUP_NODE_ID = SiteGroupMembers.PARENT_MEMBER_ID AND LKUP.LOOKUP_TYPE = 'RRS_NODE_PURPOSE' AND RSGNB.NODE_PURPOSE_CODE = LKUP.LOOKUP_CODE))),
DECODE(SiteGroupMembers.PARENT_MEMBER_ID, -1, 'Y', 'N'),
DECODE(SiteGroupNodes1.SITE_GROUP_NODE_ID, NULL, Sites1.SITE_ID,
SiteGroupNodes1.SITE_GROUP_NODE_ID),
DECODE(SiteGroupNodes1.SITE_GROUP_NODE_ID, NULL, Sites1.SITE_IDENTIFICATION_NUMBER,
SiteGroupNodes1.NODE_IDENTIFICATION_NUMBER),
DECODE(SiteGroupNodes1.NAME, NULL, Sites1.NAME, SiteGroupNodes1.NAME),
DECODE(SiteGroupNodes1.SITE_GROUP_NODE_ID, NULL, 'SITE', 'NODE'),
DECODE(SiteGroupNodes1.SITE_GROUP_NODE_ID, NULL,
(SELECT SITE_USE_TYPE_CODE FROM RRS_SITE_USES WHERE RRS_SITE_USES.SITE_ID = SiteGroupMembers.CHILD_MEMBER_ID AND RRS_SITE_USES.IS_PRIMARY_FLAG = 'Y'),
(SELECT NODE_PURPOSE_CODE FROM RRS_SITE_GROUP_NODES_B RSGNB WHERE RSGNB.SITE_GROUP_NODE_ID = SiteGroupMembers.CHILD_MEMBER_ID)),
DECODE(SiteGroupNodes1.SITE_GROUP_NODE_ID, NULL,
(SELECT LKUP.MEANING
FROM RRS_SITE_USES, AR_LOOKUPS LKUP
WHERE RRS_SITE_USES.SITE_ID = SiteGroupMembers.CHILD_MEMBER_ID AND RRS_SITE_USES.IS_PRIMARY_FLAG = 'Y' AND LKUP.LOOKUP_TYPE = 'PARTY_SITE_USE_CODE' AND RRS_SITE_USES.SITE_USE_TYPE_CODE = LKUP.LOOKUP_CODE),
(SELECT LKUP.MEANING
FROM RRS_SITE_GROUP_NODES_B RSGNB, RRS_LOOKUPS_V LKUP
WHERE RSGNB.SITE_GROUP_NODE_ID = SiteGroupMembers.CHILD_MEMBER_ID AND RSGNB.NODE_PURPOSE_CODE = LKUP.LOOKUP_CODE AND LKUP.LOOKUP_TYPE = 'RRS_NODE_PURPOSE')),
DECODE(SiteGroupNodes1.SITE_GROUP_NODE_ID, NULL, Sites1.DESCRIPTION, SiteGroupNodes1.DESCRIPTION),
DECODE(SiteGroupNodes1.SITE_GROUP_NODE_ID, NULL, Sites1.SITE_STATUS_CODE, NULL),
DECODE(SiteGroupNodes1.SITE_GROUP_NODE_ID, NULL, (SELECT MEANING FROM RRS_LOOKUPS_V WHERE LOOKUP_TYPE = 'RRS_SITE_STATUS' AND LOOKUP_CODE = Sites1.SITE_STATUS_CODE), NULL),
SiteGroupMembers.SEQUENCE_NUMBER,
LEVEL
)
BULK COLLECT
INTO x_hier_members_tab
FROM RRS_SITE_GROUP_MEMBERS SiteGroupMembers,
RRS_SITE_GROUP_NODES_VL SiteGroupNodes1,
RRS_SITE_GROUP_NODES_VL SiteGroupNodes2,
RRS_SITES_VL Sites1,
RRS_SITES_VL Sites2
WHERE SiteGroupMembers.CHILD_MEMBER_ID = SiteGroupNodes1.SITE_GROUP_NODE_ID(+)
AND SiteGroupMembers.CHILD_MEMBER_ID = Sites1.SITE_ID(+)
AND SiteGroupMembers.PARENT_MEMBER_ID = SiteGroupNodes2.SITE_GROUP_NODE_ID(+)
AND SiteGroupMembers.PARENT_MEMBER_ID = Sites2.SITE_ID(+)
AND SiteGroupMembers.DELETED_FLAG = 'N'
START WITH PARENT_MEMBER_ID = l_parent_id AND SiteGroupMembers.SITE_GROUP_ID = l_site_group_id AND SiteGroupMembers.SITE_GROUP_VERSION_ID = p_hier_version_id
CONNECT BY PRIOR CHILD_MEMBER_ID = PARENT_MEMBER_ID AND SiteGroupMembers.SITE_GROUP_ID = l_site_group_id AND SiteGroupMembers.SITE_GROUP_VERSION_ID = p_hier_version_id
ORDER SIBLINGS BY SEQUENCE_NUMBER;
SELECT SITE_GROUP_VERSION_ID
INTO l_hier_version_id
FROM RRS_SITE_GROUP_VERSIONS
WHERE SITE_GROUP_ID = p_hier_id
AND VERSION_NUMBER = DECODE(p_hier_version_number, NULL, (SELECT MAX(VERSION_NUMBER)
FROM RRS_SITE_GROUP_VERSIONS
WHERE SITE_GROUP_ID = p_hier_id),
p_hier_version_number);
SELECT SITE_ID
INTO l_parent_id
FROM RRS_SITES_B
WHERE SITE_IDENTIFICATION_NUMBER = p_parent_member_id_num;
SELECT SITE_GROUP_NODE_ID
INTO l_parent_id
FROM RRS_SITE_GROUP_NODES_B
WHERE NODE_IDENTIFICATION_NUMBER = p_parent_member_id_num;
SELECT SITE_GROUP_ID
INTO l_hier_id
FROM RRS_SITE_GROUPS_VL
WHERE NAME = p_hier_name;
SELECT SITE_ID
INTO l_parent_id
FROM RRS_SITES_B
WHERE SITE_IDENTIFICATION_NUMBER = p_parent_member_id_num;
SELECT SITE_GROUP_NODE_ID
INTO l_parent_id
FROM RRS_SITE_GROUP_NODES_B
WHERE NODE_IDENTIFICATION_NUMBER = p_parent_member_id_num;
SELECT RSG.SITE_GROUP_ID, RSG.GROUP_PURPOSE_CODE
INTO l_site_group_id, l_group_purpose_code
FROM RRS_SITE_GROUPS_VL RSG, RRS_SITE_GROUP_VERSIONS RSGV
WHERE RSG.SITE_GROUP_ID = RSGV.SITE_GROUP_ID
AND RSGV.SITE_GROUP_VERSION_ID = p_hier_version_id;
SELECT MEANING
INTO l_rrs_entity_name
FROM RRS_LOOKUPS_V
WHERE LOOKUP_TYPE = 'RRS_ENTITY'
AND LOOKUP_CODE = 'RRS_HIERARCHY';
SELECT ext.DESCRIPTIVE_FLEX_CONTEXT_CODE
BULK COLLECT
INTO l_attr_grp_names
FROM EGO_OBJ_AG_ASSOCS_B eoab
,FND_OBJECTS fo
,EGO_FND_DSC_FLX_CTX_EXT ext
WHERE eoab.OBJECT_ID = fo.OBJECT_ID
AND fo.OBJ_NAME = 'RRS_HIERARCHY'
AND eoab.ATTR_GROUP_ID = ext.ATTR_GROUP_ID
AND eoab.CLASSIFICATION_CODE = l_group_purpose_code
AND RRS_SECURITY_PUB.Check_UDA_View_Privilege(
p_api_version => 1.0,
p_attr_group_id => eoab.ATTR_GROUP_ID,
p_object_name => fo.OBJ_NAME,
p_object_key => p_hier_version_id) = FND_API.G_TRUE;
SELECT PAGE_ID,
DISPLAY_NAME
INTO l_page_id,
l_display_name
FROM EGO_PAGES_V
WHERE OBJECT_NAME = 'RRS_HIERARCHY'
AND DISPLAY_NAME = p_page_name
AND CLASSIFICATION_CODE = l_group_purpose_code
ORDER BY SEQUENCE;
SELECT PE.ATTR_GROUP_NAME
BULK COLLECT
INTO l_attr_grp_names
FROM EGO_PAGE_ENTRIES_V PE
,EGO_ATTR_GROUPS_V AG
WHERE
PE.PAGE_ID = l_page_id
AND PE.ATTR_GROUP_NAME = AG.ATTR_GROUP_NAME
AND RRS_SECURITY_PUB.Check_UDA_View_Privilege(
p_api_version => 1.0,
p_attr_group_id => AG.ATTR_GROUP_ID,
p_object_name => 'RRS_HIERARCHY',
p_object_key => p_hier_version_id) = FND_API.G_TRUE
ORDER BY SEQUENCE;
SELECT SITE_GROUP_VERSION_ID
INTO l_hier_version_id
FROM RRS_SITE_GROUP_VERSIONS
WHERE SITE_GROUP_ID = p_hier_id
AND VERSION_NUMBER = DECODE(p_hier_version_number, NULL, (SELECT MAX(VERSION_NUMBER)
FROM RRS_SITE_GROUP_VERSIONS
WHERE SITE_GROUP_ID = p_hier_id),
p_hier_version_number);
SELECT SITE_GROUP_ID
INTO l_hier_id
FROM RRS_SITE_GROUPS_VL
WHERE NAME = p_hier_name;