VIEW: APPS.JTF_RS_SRP_GROUPS_V

Source

View Text - Preformatted

SELECT resource_id, salesrep_id, org_id, user_id, group_id, group_name, role_type_code, role_id, role_type_priority, role_priority, role_start_date, role_end_date, denorm_count FROM ( SELECT mem.resource_id ,mem.group_id ,grp.group_name ,srp.salesrep_id ,srp.org_id ,res.user_id ,DECODE(rol.role_type_code,'SALES',1,2) role_type_priority ,DECODE('Y',rol.member_flag,1, rol.manager_flag,2) role_priority ,rrl.start_date_active role_start_date ,nvl(rrl.end_date_active,TO_DATE('12/31/4712','MM/DD/RRRR')) role_end_date ,rrl.role_id ,rol.role_type_code ,COUNT(den.group_id) denorm_count FROM jtf_rs_role_relations rrl ,jtf_rs_group_members mem ,jtf_rs_salesreps srp ,jtf_rs_group_usages usg ,jtf_rs_roles_b rol ,jtf_rs_groups_denorm den ,jtf_rs_resource_extns res ,jtf_rs_groups_tl grp WHERE rrl.role_resource_type = 'RS_GROUP_MEMBER' AND rrl.delete_flag = 'N' AND rrl.role_resource_id = mem.group_member_id AND mem.delete_flag = 'N' AND mem.resource_id = srp.resource_id AND mem.group_id = usg.group_id AND usg.usage = 'SALES' AND rrl.role_id = rol.role_id AND 'Y' IN (rol.member_flag, rol.manager_flag) AND mem.resource_id = res.resource_id AND mem.group_id = den.group_id AND ( rrl.start_date_active BETWEEN den.start_date_active AND nvl(den.end_date_active,TO_DATE('12/31/4712','MM/DD/RRRR')) OR den.start_date_active BETWEEN rrl.start_date_active AND nvl(rrl.end_date_active,TO_DATE('12/31/4712','MM/DD/RRRR')) ) AND mem.group_id = grp.group_id AND grp.language = USERENV('LANG') GROUP BY mem.resource_id, mem.group_id, srp.salesrep_id, srp.org_id, res.user_id, rrl.start_date_active, rrl.end_date_active, DECODE(rol.role_type_code,'SALES',1,2), DECODE('Y',rol.member_flag,1,rol.manager_flag, 2) ,rrl.role_id, rol.role_type_code, grp.group_name UNION SELECT TO_NUMBER(-1) resource_id ,TO_NUMBER(-1) group_id ,'DUMMY' group_name ,TO_NUMBER(-9999) salesrep_id ,TO_NUMBER(-9999) org_id ,TO_NUMBER(-9999) user_id ,TO_NUMBER(9999999999) role_type_priority ,TO_NUMBER(9999999999) role_priority ,TO_DATE('01/01/1901','MM/DD/RRRR') role_start_date ,TO_DATE('12/31/4712','MM/DD/RRRR') role_end_date ,TO_NUMBER(0) role_id ,'DUMMY' role_type_code ,TO_NUMBER(99999999999) denorm_count FROM dual ORDER BY denorm_count ASC, role_type_priority ASC, role_priority ASC )

View Text - HTML Formatted

SELECT RESOURCE_ID
, SALESREP_ID
, ORG_ID
, USER_ID
, GROUP_ID
, GROUP_NAME
, ROLE_TYPE_CODE
, ROLE_ID
, ROLE_TYPE_PRIORITY
, ROLE_PRIORITY
, ROLE_START_DATE
, ROLE_END_DATE
, DENORM_COUNT
FROM ( SELECT MEM.RESOURCE_ID
, MEM.GROUP_ID
, GRP.GROUP_NAME
, SRP.SALESREP_ID
, SRP.ORG_ID
, RES.USER_ID
, DECODE(ROL.ROLE_TYPE_CODE
, 'SALES'
, 1
, 2) ROLE_TYPE_PRIORITY
, DECODE('Y'
, ROL.MEMBER_FLAG
, 1
, ROL.MANAGER_FLAG
, 2) ROLE_PRIORITY
, RRL.START_DATE_ACTIVE ROLE_START_DATE
, NVL(RRL.END_DATE_ACTIVE
, TO_DATE('12/31/4712'
, 'MM/DD/RRRR')) ROLE_END_DATE
, RRL.ROLE_ID
, ROL.ROLE_TYPE_CODE
, COUNT(DEN.GROUP_ID) DENORM_COUNT
FROM JTF_RS_ROLE_RELATIONS RRL
, JTF_RS_GROUP_MEMBERS MEM
, JTF_RS_SALESREPS SRP
, JTF_RS_GROUP_USAGES USG
, JTF_RS_ROLES_B ROL
, JTF_RS_GROUPS_DENORM DEN
, JTF_RS_RESOURCE_EXTNS RES
, JTF_RS_GROUPS_TL GRP
WHERE RRL.ROLE_RESOURCE_TYPE = 'RS_GROUP_MEMBER'
AND RRL.DELETE_FLAG = 'N'
AND RRL.ROLE_RESOURCE_ID = MEM.GROUP_MEMBER_ID
AND MEM.DELETE_FLAG = 'N'
AND MEM.RESOURCE_ID = SRP.RESOURCE_ID
AND MEM.GROUP_ID = USG.GROUP_ID
AND USG.USAGE = 'SALES'
AND RRL.ROLE_ID = ROL.ROLE_ID
AND 'Y' IN (ROL.MEMBER_FLAG
, ROL.MANAGER_FLAG)
AND MEM.RESOURCE_ID = RES.RESOURCE_ID
AND MEM.GROUP_ID = DEN.GROUP_ID
AND ( RRL.START_DATE_ACTIVE BETWEEN DEN.START_DATE_ACTIVE
AND NVL(DEN.END_DATE_ACTIVE
, TO_DATE('12/31/4712'
, 'MM/DD/RRRR')) OR DEN.START_DATE_ACTIVE BETWEEN RRL.START_DATE_ACTIVE
AND NVL(RRL.END_DATE_ACTIVE
, TO_DATE('12/31/4712'
, 'MM/DD/RRRR')) )
AND MEM.GROUP_ID = GRP.GROUP_ID
AND GRP.LANGUAGE = USERENV('LANG') GROUP BY MEM.RESOURCE_ID
, MEM.GROUP_ID
, SRP.SALESREP_ID
, SRP.ORG_ID
, RES.USER_ID
, RRL.START_DATE_ACTIVE
, RRL.END_DATE_ACTIVE
, DECODE(ROL.ROLE_TYPE_CODE
, 'SALES'
, 1
, 2)
, DECODE('Y'
, ROL.MEMBER_FLAG
, 1
, ROL.MANAGER_FLAG
, 2)
, RRL.ROLE_ID
, ROL.ROLE_TYPE_CODE
, GRP.GROUP_NAME UNION SELECT TO_NUMBER(-1) RESOURCE_ID
, TO_NUMBER(-1) GROUP_ID
, 'DUMMY' GROUP_NAME
, TO_NUMBER(-9999) SALESREP_ID
, TO_NUMBER(-9999) ORG_ID
, TO_NUMBER(-9999) USER_ID
, TO_NUMBER(9999999999) ROLE_TYPE_PRIORITY
, TO_NUMBER(9999999999) ROLE_PRIORITY
, TO_DATE('01/01/1901'
, 'MM/DD/RRRR') ROLE_START_DATE
, TO_DATE('12/31/4712'
, 'MM/DD/RRRR') ROLE_END_DATE
, TO_NUMBER(0) ROLE_ID
, 'DUMMY' ROLE_TYPE_CODE
, TO_NUMBER(99999999999) DENORM_COUNT
FROM DUAL ORDER BY DENORM_COUNT ASC
, ROLE_TYPE_PRIORITY ASC
, ROLE_PRIORITY ASC )