VIEW: APPS.BIX_DM_GROUP_BIN_PARAM_V

Source

View Text - Preformatted

SELECT 3 sort_col , 'a' || to_char(resource_id) group_id , resource_name group_name from jtf_rs_resource_extns_vl WHERE user_id = fnd_global.user_id union all select distinct 2 sort_col , 'g' || den.group_id group_id , grp.group_name group_name from jtf_rs_resource_extns rsc , jtf_rs_group_members mem , jtf_rs_roles_b rol , jtf_rs_role_relations rlt , jtf_rs_groups_vl grp , jtf_rs_groups_denorm den where rsc.user_id = fnd_global.user_id and rsc.resource_id = mem.resource_id and nvl(mem.delete_flag,'N') <> 'Y' and mem.group_member_id = rlt.role_resource_id and rlt.role_resource_type = 'RS_GROUP_MEMBER' and nvl(rlt.delete_flag, 'N') <> 'Y' and sysdate between rlt.start_date_active and nvl(rlt.end_date_active, sysdate) and rlt.role_id = rol.role_id and (rol.manager_flag = 'Y' OR rol.admin_flag = 'Y') and mem.group_id = den.parent_group_id and sysdate between den.start_date_active and nvl(den.end_date_active, sysdate) and den.group_id = grp.group_id union all select 1 sort_col , 'g' || lookup_code group_id , meaning group_name from fnd_lookups where lookup_type = 'BIX_DM_ALL_LOOKUP' and exists ( SELECT 1 FROM jtf_rs_resource_extns rsc , jtf_rs_group_members mem , jtf_rs_roles_b rol , jtf_rs_role_relations rlt WHERE rsc.user_id = fnd_global.user_id AND rsc.resource_id = mem.resource_id AND nvl(mem.delete_flag,'N') <> 'Y' AND mem.group_member_id = rlt.role_resource_id AND rlt.role_resource_type = 'RS_GROUP_MEMBER' AND nvl(rlt.delete_flag, 'N') <> 'Y' AND sysdate between rlt.start_date_active and nvl(rlt.end_date_active, sysdate) AND rlt.role_id = rol.role_id AND (rol.manager_flag = 'Y' OR rol.admin_flag = 'Y'))

View Text - HTML Formatted

SELECT 3 SORT_COL
, 'A' || TO_CHAR(RESOURCE_ID) GROUP_ID
, RESOURCE_NAME GROUP_NAME
FROM JTF_RS_RESOURCE_EXTNS_VL
WHERE USER_ID = FND_GLOBAL.USER_ID UNION ALL SELECT DISTINCT 2 SORT_COL
, 'G' || DEN.GROUP_ID GROUP_ID
, GRP.GROUP_NAME GROUP_NAME
FROM JTF_RS_RESOURCE_EXTNS RSC
, JTF_RS_GROUP_MEMBERS MEM
, JTF_RS_ROLES_B ROL
, JTF_RS_ROLE_RELATIONS RLT
, JTF_RS_GROUPS_VL GRP
, JTF_RS_GROUPS_DENORM DEN
WHERE RSC.USER_ID = FND_GLOBAL.USER_ID
AND RSC.RESOURCE_ID = MEM.RESOURCE_ID
AND NVL(MEM.DELETE_FLAG
, 'N') <> 'Y'
AND MEM.GROUP_MEMBER_ID = RLT.ROLE_RESOURCE_ID
AND RLT.ROLE_RESOURCE_TYPE = 'RS_GROUP_MEMBER'
AND NVL(RLT.DELETE_FLAG
, 'N') <> 'Y'
AND SYSDATE BETWEEN RLT.START_DATE_ACTIVE
AND NVL(RLT.END_DATE_ACTIVE
, SYSDATE)
AND RLT.ROLE_ID = ROL.ROLE_ID
AND (ROL.MANAGER_FLAG = 'Y' OR ROL.ADMIN_FLAG = 'Y')
AND MEM.GROUP_ID = DEN.PARENT_GROUP_ID
AND SYSDATE BETWEEN DEN.START_DATE_ACTIVE
AND NVL(DEN.END_DATE_ACTIVE
, SYSDATE)
AND DEN.GROUP_ID = GRP.GROUP_ID UNION ALL SELECT 1 SORT_COL
, 'G' || LOOKUP_CODE GROUP_ID
, MEANING GROUP_NAME
FROM FND_LOOKUPS
WHERE LOOKUP_TYPE = 'BIX_DM_ALL_LOOKUP'
AND EXISTS ( SELECT 1
FROM JTF_RS_RESOURCE_EXTNS RSC
, JTF_RS_GROUP_MEMBERS MEM
, JTF_RS_ROLES_B ROL
, JTF_RS_ROLE_RELATIONS RLT
WHERE RSC.USER_ID = FND_GLOBAL.USER_ID
AND RSC.RESOURCE_ID = MEM.RESOURCE_ID
AND NVL(MEM.DELETE_FLAG
, 'N') <> 'Y'
AND MEM.GROUP_MEMBER_ID = RLT.ROLE_RESOURCE_ID
AND RLT.ROLE_RESOURCE_TYPE = 'RS_GROUP_MEMBER'
AND NVL(RLT.DELETE_FLAG
, 'N') <> 'Y'
AND SYSDATE BETWEEN RLT.START_DATE_ACTIVE
AND NVL(RLT.END_DATE_ACTIVE
, SYSDATE)
AND RLT.ROLE_ID = ROL.ROLE_ID
AND (ROL.MANAGER_FLAG = 'Y' OR ROL.ADMIN_FLAG = 'Y'))