SELECT distinct(RES.RESOURCE_ID) RESOURCE_ID , (DECODE(RES.CATEGORY, 'EMPLOYEE', RES.SOURCE_ID, 'PARTNER' , RES.MANAGING_EMPLOYEE_ID, null)) PERSON_ID, res.source_last_name, res.source_first_name, res.user_name,MEM.GROUP_ID, gp.period_set_name, gp.period_name,count(*) cnt FROM JTF_RS_GROUP_MEMBERS MEM , JTF_RS_RESOURCE_EXTNS RES , JTF_RS_ROLE_RELATIONS RREL , JTF_RS_ROLES_B ROLEB ,gl_periods gp WHERE ROLEB.ROLE_TYPE_CODE in ('SALES','TELESALES','FIELDSALES','PRM') AND RES.RESOURCE_ID = MEM.RESOURCE_ID AND MEM.GROUP_MEMBER_ID = RREL.ROLE_RESOURCE_ID AND RREL.ROLE_RESOURCE_TYPE = 'RS_GROUP_MEMBER' AND RREL.ROLE_ID = ROLEB.ROLE_ID AND (ROLEB.MEMBER_FLAG= 'Y' or (nvl(ROLEB.MEMBER_FLAG,'N')='N' and ROLEB.MANAGER_FLAG='Y')) and MEM.DELETE_FLAG <> 'Y' and RREL.DELETE_FLAG <> 'Y' AND ((RREL.START_DATE_ACTIVE is null or RREL.START_DATE_ACTIVE <= gp.end_date) AND (RREL.END_DATE_ACTIVE is null or RREL.END_DATE_ACTIVE > gp.start_date)) and MEM.GROUP_ID IS NOT NULL group BY (RES.RESOURCE_ID) , (DECODE(RES.CATEGORY, 'EMPLOYEE', RES.SOURCE_ID, 'PARTNER' , RES.MANAGING_EMPLOYEE_ID, null)), res.source_last_name, res.source_first_name , res.user_name, MEM.GROUP_ID, gp.period_set_name, gp.period_name
[Home] [Help]