VIEW: APPS.BIL_TX_SALES_PERSON_V

Source

View Text - Preformatted

SELECT distinct jrb.resource_id , jrb.source_first_name, jrb.source_last_name, jrb.source_name, jrgm.group_id FROM jtf_rs_group_members jrgm, jtf_rs_group_usages jrgu, jtf_rs_resource_extns jrb, fnd_territories_tl ftl WHERE jrgu.group_id = jrgm.group_id AND jrgu.usage = 'SALES' AND NVL(jrgm.delete_flag, 'N') = 'N' AND jrgm.resource_id = jrb.resource_id AND jrb.category = 'EMPLOYEE' AND TRUNC(jrb.start_date_active) <= TRUNC(SYSDATE) AND TRUNC(nvl(jrb.end_date_active, SYSDATE)) >= TRUNC(SYSDATE) AND ftl.language(+) = USERENV('LANG') AND jrb.source_country = ftl.territory_code(+) AND EXISTS (SELECT 1 FROM jtf_rs_role_relations jrrr WHERE jrrr.role_resource_id= jrgm.group_member_id AND TRUNC(NVL(jrrr.start_date_active, SYSDATE)) <= TRUNC(SYSDATE) AND TRUNC(NVL(jrrr.end_date_active, SYSDATE)) >= TRUNC(SYSDATE) AND jrrr.role_resource_type='RS_GROUP_MEMBER' AND NVL(jrrr.delete_flag, 'N') = 'N' AND ROWNUM= 1)

View Text - HTML Formatted

SELECT DISTINCT JRB.RESOURCE_ID
, JRB.SOURCE_FIRST_NAME
, JRB.SOURCE_LAST_NAME
, JRB.SOURCE_NAME
, JRGM.GROUP_ID
FROM JTF_RS_GROUP_MEMBERS JRGM
, JTF_RS_GROUP_USAGES JRGU
, JTF_RS_RESOURCE_EXTNS JRB
, FND_TERRITORIES_TL FTL
WHERE JRGU.GROUP_ID = JRGM.GROUP_ID
AND JRGU.USAGE = 'SALES'
AND NVL(JRGM.DELETE_FLAG
, 'N') = 'N'
AND JRGM.RESOURCE_ID = JRB.RESOURCE_ID
AND JRB.CATEGORY = 'EMPLOYEE'
AND TRUNC(JRB.START_DATE_ACTIVE) <= TRUNC(SYSDATE)
AND TRUNC(NVL(JRB.END_DATE_ACTIVE
, SYSDATE)) >= TRUNC(SYSDATE)
AND FTL.LANGUAGE(+) = USERENV('LANG')
AND JRB.SOURCE_COUNTRY = FTL.TERRITORY_CODE(+)
AND EXISTS (SELECT 1
FROM JTF_RS_ROLE_RELATIONS JRRR
WHERE JRRR.ROLE_RESOURCE_ID= JRGM.GROUP_MEMBER_ID
AND TRUNC(NVL(JRRR.START_DATE_ACTIVE
, SYSDATE)) <= TRUNC(SYSDATE)
AND TRUNC(NVL(JRRR.END_DATE_ACTIVE
, SYSDATE)) >= TRUNC(SYSDATE)
AND JRRR.ROLE_RESOURCE_TYPE='RS_GROUP_MEMBER'
AND NVL(JRRR.DELETE_FLAG
, 'N') = 'N'
AND ROWNUM= 1)