VIEW: APPS.ASG_CREATE_NEW_USERS_VL

Source

View Text - Preformatted

SELECT distinct d.user_name, e.full_name from asg_pub a, asg_pub_responsibility b, fnd_user_resp_groups c, fnd_user d , jtf_rs_emp_dtls_vl e WHERE a.pub_id=b.pub_id and b.RESPONSIBILITY_ID=c.RESPONSIBILITY_ID and c.user_id =d.user_id and to_char(c.START_DATE, 'YYYY-MM-DD')<=to_char(sysdate, 'YYYY-MM-DD') and (c.end_date is null or to_char(c.END_DATE, 'YYYY-MM-DD')>=to_char(sysdate, 'YYYY-MM-DD')) and (d.end_date is null or to_char(d.END_DATE, 'YYYY-MM-DD')>=to_char(sysdate, 'YYYY-MM-DD')) and d.user_name not in (select user_name from asg_user) and c.user_id = e.user_id UNION select distinct d.user_name, e.full_name from fnd_user d, fnd_user_resp_groups c , jtf_rs_emp_dtls_vl e where d.user_id = c.user_id and d.user_id = e.user_id and to_char(c.START_DATE, 'YYYY-MM-DD')<=to_char(sysdate, 'YYYY-MM-DD') and (c.end_date is null or to_char(c.END_DATE, 'YYYY-MM-DD')>=to_char(sysdate, 'YYYY-MM-DD')) and (d.end_date is null or to_char(d.END_DATE, 'YYYY-MM-DD')>=to_char(sysdate, 'YYYY-MM-DD')) and c.responsibility_id in ( select responsibility_id from fnd_responsibility a where a.application_id = 513 and nvl( fnd_profile.value_specific('ASG_MOBILE_USER', null, a.responsibility_id, a.application_id ), 'N') = 'Y' ) and d.user_name not in (select user_name from asg_user)

View Text - HTML Formatted

SELECT DISTINCT D.USER_NAME
, E.FULL_NAME
FROM ASG_PUB A
, ASG_PUB_RESPONSIBILITY B
, FND_USER_RESP_GROUPS C
, FND_USER D
, JTF_RS_EMP_DTLS_VL E
WHERE A.PUB_ID=B.PUB_ID
AND B.RESPONSIBILITY_ID=C.RESPONSIBILITY_ID
AND C.USER_ID =D.USER_ID
AND TO_CHAR(C.START_DATE
, 'YYYY-MM-DD')<=TO_CHAR(SYSDATE
, 'YYYY-MM-DD')
AND (C.END_DATE IS NULL OR TO_CHAR(C.END_DATE
, 'YYYY-MM-DD')>=TO_CHAR(SYSDATE
, 'YYYY-MM-DD'))
AND (D.END_DATE IS NULL OR TO_CHAR(D.END_DATE
, 'YYYY-MM-DD')>=TO_CHAR(SYSDATE
, 'YYYY-MM-DD'))
AND D.USER_NAME NOT IN (SELECT USER_NAME
FROM ASG_USER)
AND C.USER_ID = E.USER_ID UNION SELECT DISTINCT D.USER_NAME
, E.FULL_NAME
FROM FND_USER D
, FND_USER_RESP_GROUPS C
, JTF_RS_EMP_DTLS_VL E
WHERE D.USER_ID = C.USER_ID
AND D.USER_ID = E.USER_ID
AND TO_CHAR(C.START_DATE
, 'YYYY-MM-DD')<=TO_CHAR(SYSDATE
, 'YYYY-MM-DD')
AND (C.END_DATE IS NULL OR TO_CHAR(C.END_DATE
, 'YYYY-MM-DD')>=TO_CHAR(SYSDATE
, 'YYYY-MM-DD'))
AND (D.END_DATE IS NULL OR TO_CHAR(D.END_DATE
, 'YYYY-MM-DD')>=TO_CHAR(SYSDATE
, 'YYYY-MM-DD'))
AND C.RESPONSIBILITY_ID IN ( SELECT RESPONSIBILITY_ID
FROM FND_RESPONSIBILITY A
WHERE A.APPLICATION_ID = 513
AND NVL( FND_PROFILE.VALUE_SPECIFIC('ASG_MOBILE_USER'
, NULL
, A.RESPONSIBILITY_ID
, A.APPLICATION_ID )
, 'N') = 'Y' )
AND D.USER_NAME NOT IN (SELECT USER_NAME
FROM ASG_USER)