VIEW: APPS.CN_QM_MGR_GROUPS

Source

View Text - Preformatted

SELECT jgm.role_relate_id qm_mgr_group_id, jrr.role_relate_id srp_role_id, jgm.group_id comp_group_id, jg.group_name group_name, jgm.resource_id, jrs.salesrep_id manager_srp_id, jgm.role_id, jgm.role_name, jgm.role_desc, jgm.member_flag, jgm.admin_flag, jgm.manager_flag, jgm.lead_flag, jgm.start_date_active, jgm.end_date_active from jtf_rs_groups_vl jg, jtf_rs_role_relations jrr, jtf_rs_salesreps jrs, jtf_rs_roles_b jr, jtf_rs_group_mbr_role_vl jgm, jtf_rs_group_usages u, cn_srp_role_dtls srd WHERE jg.group_id = jgm.group_id and jgm.manager_flag = 'Y' and jrs.resource_id = jgm.resource_id and u.group_id = jgm.group_id and u.usage = 'SF_PLANNING' and jrr.role_resource_type = 'RS_INDIVIDUAL' and jrr.role_resource_id = jrs.resource_id and jrr.role_id = jgm.role_id and jrr.role_id = jr.role_id and jr.role_type_code = 'SALES_COMP' and jrr.delete_flag <> 'Y' and jrr.start_date_active <= jgm.start_date_active and (jrr.end_date_active is null or jrr.end_date_active >= jgm.end_date_active) AND jrs.SALESREP_ID > 0 AND srd.srp_role_id = jrr.role_relate_id AND NVL(jrs.ORG_ID,NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1), ' ', NULL, SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)) = NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1), ' ', NULL, SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)

View Text - HTML Formatted

SELECT JGM.ROLE_RELATE_ID QM_MGR_GROUP_ID
, JRR.ROLE_RELATE_ID SRP_ROLE_ID
, JGM.GROUP_ID COMP_GROUP_ID
, JG.GROUP_NAME GROUP_NAME
, JGM.RESOURCE_ID
, JRS.SALESREP_ID MANAGER_SRP_ID
, JGM.ROLE_ID
, JGM.ROLE_NAME
, JGM.ROLE_DESC
, JGM.MEMBER_FLAG
, JGM.ADMIN_FLAG
, JGM.MANAGER_FLAG
, JGM.LEAD_FLAG
, JGM.START_DATE_ACTIVE
, JGM.END_DATE_ACTIVE
FROM JTF_RS_GROUPS_VL JG
, JTF_RS_ROLE_RELATIONS JRR
, JTF_RS_SALESREPS JRS
, JTF_RS_ROLES_B JR
, JTF_RS_GROUP_MBR_ROLE_VL JGM
, JTF_RS_GROUP_USAGES U
, CN_SRP_ROLE_DTLS SRD
WHERE JG.GROUP_ID = JGM.GROUP_ID
AND JGM.MANAGER_FLAG = 'Y'
AND JRS.RESOURCE_ID = JGM.RESOURCE_ID
AND U.GROUP_ID = JGM.GROUP_ID
AND U.USAGE = 'SF_PLANNING'
AND JRR.ROLE_RESOURCE_TYPE = 'RS_INDIVIDUAL'
AND JRR.ROLE_RESOURCE_ID = JRS.RESOURCE_ID
AND JRR.ROLE_ID = JGM.ROLE_ID
AND JRR.ROLE_ID = JR.ROLE_ID
AND JR.ROLE_TYPE_CODE = 'SALES_COMP'
AND JRR.DELETE_FLAG <> 'Y'
AND JRR.START_DATE_ACTIVE <= JGM.START_DATE_ACTIVE
AND (JRR.END_DATE_ACTIVE IS NULL OR JRR.END_DATE_ACTIVE >= JGM.END_DATE_ACTIVE)
AND JRS.SALESREP_ID > 0
AND SRD.SRP_ROLE_ID = JRR.ROLE_RELATE_ID
AND NVL(JRS.ORG_ID
, NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 1)
, ' '
, NULL
, SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 10)))
, -99)) = NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 1)
, ' '
, NULL
, SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 10)))
, -99)