VIEW: APPS.QPR_SR_SG_HIER_V

Source

View Text - Preformatted

SELECT distinct rep.salesrep_id, nvl(rep.name, qpr_sr_util.get_null_desc), NULL, NULL, NULL, NULL, NULL, nvl(g1.group_id, qpr_sr_util.get_null_pk), nvl(g1.group_name, qpr_sr_util.get_null_desc), NULL, NULL, NULL, NULL, NULL, nvl(g2.parent_group_id, qpr_sr_util.get_null_pk), nvl(gn2.group_name, qpr_sr_util.get_null_desc), NULL, NULL, NULL, NULL, NULL, nvl(g3.parent_group_id, qpr_sr_util.get_null_pk), nvl(gn3.group_name, qpr_sr_util.get_null_desc), NULL, NULL, NULL, NULL, NULL, nvl(g4.parent_group_id,qpr_sr_util.get_null_pk), nvl(gn4.group_name,qpr_sr_util.get_null_desc), NULL, NULL, NULL, NULL, NULL, qpr_sr_util.get_all_rep_pk, qpr_sr_util.get_all_rep_desc, NULL, NULL, NULL, NULL, NULL, qpr_sr_util.GET_MAX_DATE( qpr_sr_util.GET_MAX_DATE( qpr_sr_util.GET_MAX_DATE( qpr_sr_util.GET_MAX_DATE( qpr_sr_util.GET_MAX_DATE( qpr_sr_util.GET_MAX_DATE( qpr_sr_util.GET_MAX_DATE(trunc(rep.last_update_date, 'dd'), trunc(g1.last_update_date)), trunc(g2.last_update_date,'dd')) , trunc(g3.last_update_date,'dd')) ,trunc(g4.last_update_date,'dd')), trunc(gn2.last_update_date,'dd')), trunc(gn3.last_update_date,'dd')), trunc(gn4.last_update_date,'dd')) FROM jtf_rs_salesreps rep, jtf_rs_resource_extns res, jtf_rs_groups_vl g1, jtf_rs_groups_denorm g2, jtf_rs_groups_denorm g3, jtf_rs_groups_denorm g4, jtf_rs_groups_vl gn2, jtf_rs_groups_vl gn3, jtf_rs_groups_vl gn4 WHERE rep.resource_id = res.resource_id and rep.status = 'A' and g1.group_id = ( select f1.group_id from ( select gm.resource_id, gm.group_id, d.lvl from jtf_rs_group_members gm, (select parent_group_id, max(denorm_level) lvl from jtf_rs_groups_denorm where sysdate between start_date_active and nvl(end_date_active, sysdate) group by parent_group_id, denorm_level ) d where gm.delete_flag = 'N' and gm.group_id = d.parent_group_id order by gm.resource_id, gm.group_id desc, d.lvl desc) f1 where f1.resource_id = res.resource_id and rownum = 1) and g2.group_id(+) = g1.group_id and g2.denorm_level(+) = 1 and g2.parent_group_id = gn2.group_id(+) and g3.group_id(+) = g1.group_id and g3.denorm_level(+) = 2 and g3.parent_group_id = gn3.group_id(+) and g4.group_id(+) = g1.group_id and g4.denorm_level(+) = 3 and g4.parent_group_id = gn4.group_id(+) union all SELECT distinct rep.salesrep_id, nvl(rep.name, qpr_sr_util.get_null_desc), NULL, NULL, NULL, NULL, NULL, qpr_sr_util.get_null_pk, qpr_sr_util.get_null_desc, NULL, NULL, NULL, NULL, NULL, qpr_sr_util.get_null_pk, qpr_sr_util.get_null_desc, NULL, NULL, NULL, NULL, NULL, qpr_sr_util.get_null_pk, qpr_sr_util.get_null_desc, NULL, NULL, NULL, NULL, NULL, qpr_sr_util.get_null_pk, qpr_sr_util.get_null_desc, NULL, NULL, NULL, NULL, NULL, qpr_sr_util.get_all_rep_pk, qpr_sr_util.get_all_rep_desc, NULL, NULL, NULL, NULL, NULL, trunc(rep.last_update_date,'dd') from jtf_rs_salesreps rep where rep.status = 'A' and rep.resource_id not in (select distinct resource_id from jtf_rs_group_members where delete_flag = 'N') UNION ALL SELECT qpr_sr_util.get_null_pk, qpr_sr_util.get_null_desc, null, null, null, null, null, qpr_sr_util.get_null_pk, qpr_sr_util.get_null_desc, null, null, null, null, null, qpr_sr_util.get_null_pk, qpr_sr_util.get_null_desc, null, null, null, null, null, qpr_sr_util.get_null_pk, qpr_sr_util.get_null_desc, null, null, null, null, null, qpr_sr_util.get_null_pk, qpr_sr_util.get_null_desc, null, null, null, null, null, QPR_SR_UTIL.get_all_rep_pk, QPR_SR_UTIL.get_all_rep_desc, null, null, null, null, null, null from dual

View Text - HTML Formatted

SELECT DISTINCT REP.SALESREP_ID
, NVL(REP.NAME
, QPR_SR_UTIL.GET_NULL_DESC)
, NULL
, NULL
, NULL
, NULL
, NULL
, NVL(G1.GROUP_ID
, QPR_SR_UTIL.GET_NULL_PK)
, NVL(G1.GROUP_NAME
, QPR_SR_UTIL.GET_NULL_DESC)
, NULL
, NULL
, NULL
, NULL
, NULL
, NVL(G2.PARENT_GROUP_ID
, QPR_SR_UTIL.GET_NULL_PK)
, NVL(GN2.GROUP_NAME
, QPR_SR_UTIL.GET_NULL_DESC)
, NULL
, NULL
, NULL
, NULL
, NULL
, NVL(G3.PARENT_GROUP_ID
, QPR_SR_UTIL.GET_NULL_PK)
, NVL(GN3.GROUP_NAME
, QPR_SR_UTIL.GET_NULL_DESC)
, NULL
, NULL
, NULL
, NULL
, NULL
, NVL(G4.PARENT_GROUP_ID
, QPR_SR_UTIL.GET_NULL_PK)
, NVL(GN4.GROUP_NAME
, QPR_SR_UTIL.GET_NULL_DESC)
, NULL
, NULL
, NULL
, NULL
, NULL
, QPR_SR_UTIL.GET_ALL_REP_PK
, QPR_SR_UTIL.GET_ALL_REP_DESC
, NULL
, NULL
, NULL
, NULL
, NULL
, QPR_SR_UTIL.GET_MAX_DATE( QPR_SR_UTIL.GET_MAX_DATE( QPR_SR_UTIL.GET_MAX_DATE( QPR_SR_UTIL.GET_MAX_DATE( QPR_SR_UTIL.GET_MAX_DATE( QPR_SR_UTIL.GET_MAX_DATE( QPR_SR_UTIL.GET_MAX_DATE(TRUNC(REP.LAST_UPDATE_DATE
, 'DD')
, TRUNC(G1.LAST_UPDATE_DATE))
, TRUNC(G2.LAST_UPDATE_DATE
, 'DD'))
, TRUNC(G3.LAST_UPDATE_DATE
, 'DD'))
, TRUNC(G4.LAST_UPDATE_DATE
, 'DD'))
, TRUNC(GN2.LAST_UPDATE_DATE
, 'DD'))
, TRUNC(GN3.LAST_UPDATE_DATE
, 'DD'))
, TRUNC(GN4.LAST_UPDATE_DATE
, 'DD'))
FROM JTF_RS_SALESREPS REP
, JTF_RS_RESOURCE_EXTNS RES
, JTF_RS_GROUPS_VL G1
, JTF_RS_GROUPS_DENORM G2
, JTF_RS_GROUPS_DENORM G3
, JTF_RS_GROUPS_DENORM G4
, JTF_RS_GROUPS_VL GN2
, JTF_RS_GROUPS_VL GN3
, JTF_RS_GROUPS_VL GN4
WHERE REP.RESOURCE_ID = RES.RESOURCE_ID
AND REP.STATUS = 'A'
AND G1.GROUP_ID = ( SELECT F1.GROUP_ID
FROM ( SELECT GM.RESOURCE_ID
, GM.GROUP_ID
, D.LVL
FROM JTF_RS_GROUP_MEMBERS GM
, (SELECT PARENT_GROUP_ID
, MAX(DENORM_LEVEL) LVL
FROM JTF_RS_GROUPS_DENORM
WHERE SYSDATE BETWEEN START_DATE_ACTIVE
AND NVL(END_DATE_ACTIVE
, SYSDATE) GROUP BY PARENT_GROUP_ID
, DENORM_LEVEL ) D
WHERE GM.DELETE_FLAG = 'N'
AND GM.GROUP_ID = D.PARENT_GROUP_ID ORDER BY GM.RESOURCE_ID
, GM.GROUP_ID DESC
, D.LVL DESC) F1
WHERE F1.RESOURCE_ID = RES.RESOURCE_ID
AND ROWNUM = 1)
AND G2.GROUP_ID(+) = G1.GROUP_ID
AND G2.DENORM_LEVEL(+) = 1
AND G2.PARENT_GROUP_ID = GN2.GROUP_ID(+)
AND G3.GROUP_ID(+) = G1.GROUP_ID
AND G3.DENORM_LEVEL(+) = 2
AND G3.PARENT_GROUP_ID = GN3.GROUP_ID(+)
AND G4.GROUP_ID(+) = G1.GROUP_ID
AND G4.DENORM_LEVEL(+) = 3
AND G4.PARENT_GROUP_ID = GN4.GROUP_ID(+) UNION ALL SELECT DISTINCT REP.SALESREP_ID
, NVL(REP.NAME
, QPR_SR_UTIL.GET_NULL_DESC)
, NULL
, NULL
, NULL
, NULL
, NULL
, QPR_SR_UTIL.GET_NULL_PK
, QPR_SR_UTIL.GET_NULL_DESC
, NULL
, NULL
, NULL
, NULL
, NULL
, QPR_SR_UTIL.GET_NULL_PK
, QPR_SR_UTIL.GET_NULL_DESC
, NULL
, NULL
, NULL
, NULL
, NULL
, QPR_SR_UTIL.GET_NULL_PK
, QPR_SR_UTIL.GET_NULL_DESC
, NULL
, NULL
, NULL
, NULL
, NULL
, QPR_SR_UTIL.GET_NULL_PK
, QPR_SR_UTIL.GET_NULL_DESC
, NULL
, NULL
, NULL
, NULL
, NULL
, QPR_SR_UTIL.GET_ALL_REP_PK
, QPR_SR_UTIL.GET_ALL_REP_DESC
, NULL
, NULL
, NULL
, NULL
, NULL
, TRUNC(REP.LAST_UPDATE_DATE
, 'DD')
FROM JTF_RS_SALESREPS REP
WHERE REP.STATUS = 'A'
AND REP.RESOURCE_ID NOT IN (SELECT DISTINCT RESOURCE_ID
FROM JTF_RS_GROUP_MEMBERS
WHERE DELETE_FLAG = 'N') UNION ALL SELECT QPR_SR_UTIL.GET_NULL_PK
, QPR_SR_UTIL.GET_NULL_DESC
, NULL
, NULL
, NULL
, NULL
, NULL
, QPR_SR_UTIL.GET_NULL_PK
, QPR_SR_UTIL.GET_NULL_DESC
, NULL
, NULL
, NULL
, NULL
, NULL
, QPR_SR_UTIL.GET_NULL_PK
, QPR_SR_UTIL.GET_NULL_DESC
, NULL
, NULL
, NULL
, NULL
, NULL
, QPR_SR_UTIL.GET_NULL_PK
, QPR_SR_UTIL.GET_NULL_DESC
, NULL
, NULL
, NULL
, NULL
, NULL
, QPR_SR_UTIL.GET_NULL_PK
, QPR_SR_UTIL.GET_NULL_DESC
, NULL
, NULL
, NULL
, NULL
, NULL
, QPR_SR_UTIL.GET_ALL_REP_PK
, QPR_SR_UTIL.GET_ALL_REP_DESC
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
FROM DUAL