eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.JTF_UM_SUBSCRIPTION_INFO SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 11


CURSOR USER_ENROLLMENT IS SELECT SUBSCRIPTION_REG_ID
FROM JTF_UM_SUBSCRIPTION_REG
WHERE SUBSCRIPTION_ID = p_subscription_id
AND   USER_ID         = p_user_id
AND   NVL(EFFECTIVE_END_DATE, SYSDATE+1) > SYSDATE
AND   STATUS_CODE IN ('APPROVED', 'PENDING');

Line: 73


CURSOR FIND_SUB_INFO IS SELECT A.SUBSCRIPTION_FLAG, A.SUBSCRIPTION_ID, A.SUBSCRIPTION_DISPLAY_ORDER, B.SUBSCRIPTION_KEY, B.SUBSCRIPTION_NAME, B.DESCRIPTION, B.AUTH_DELEGATION_ROLE_ID, B.APPROVAL_ID
FROM JTF_UM_USERTYPE_SUBSCRIP A, JTF_UM_SUBSCRIPTIONS_VL B
WHERE A.SUBSCRIPTION_ID = B.SUBSCRIPTION_ID
AND A.USERTYPE_ID = p_usertype_id
AND NVL(A.EFFECTIVE_END_DATE, SYSDATE+1) > SYSDATE
AND B.ENABLED_FLAG = 'Y'
AND A.EFFECTIVE_START_DATE < SYSDATE
ORDER BY A.SUBSCRIPTION_DISPLAY_ORDER, B.SUBSCRIPTION_NAME;

Line: 173


SELECT SUBSCRIPTION_STATUS, SUBSCRIPTION_FLAG, SUBSCRIPTION_ID, SUBSCRIPTION_DISPLAY_ORDER,
SUBSCRIPTION_KEY, SUBSCRIPTION_NAME, DESCRIPTION, AUTH_DELEGATION_ROLE_ID, REG_ID FROM
(
SELECT DECODE(SUBREG.STATUS_CODE,'APPROVED','CURRENT','PENDING','PENDING') SUBSCRIPTION_STATUS, A.SUBSCRIPTION_FLAG, A.SUBSCRIPTION_ID, A.SUBSCRIPTION_DISPLAY_ORDER,
B.SUBSCRIPTION_KEY, B.SUBSCRIPTION_NAME, B.DESCRIPTION, B.AUTH_DELEGATION_ROLE_ID, SUBREG.SUBSCRIPTION_REG_ID REG_ID
FROM JTF_UM_USERTYPE_SUBSCRIP A, JTF_UM_SUBSCRIPTIONS_VL B, JTF_UM_SUBSCRIPTION_REG SUBREG
WHERE SUBREG.SUBSCRIPTION_ID = B.SUBSCRIPTION_ID
AND A.SUBSCRIPTION_ID = B.SUBSCRIPTION_ID
AND A.USERTYPE_ID  = p_usertype_id
AND SUBREG.USER_ID = p_user_id
AND SUBREG.STATUS_CODE = p_sub_status
AND NVL(A.EFFECTIVE_END_DATE, SYSDATE+1) > SYSDATE
AND NVL(SUBREG.EFFECTIVE_END_DATE, SYSDATE+1) > SYSDATE
AND SUBREG.EFFECTIVE_START_DATE < SYSDATE

UNION ALL

SELECT DECODE(SUBREG.STATUS_CODE,'APPROVED','CURRENT','PENDING','PENDING') SUBSCRIPTION_STATUS, A.SUBSCRIPTION_FLAG, A.SUBSCRIPTION_ID, A.SUBSCRIPTION_DISPLAY_ORDER,
B.SUBSCRIPTION_KEY, B.SUBSCRIPTION_NAME, B.DESCRIPTION, B.AUTH_DELEGATION_ROLE_ID, SUBREG.SUBSCRIPTION_REG_ID REG_ID

FROM JTF_UM_USERTYPE_SUBSCRIP A, JTF_UM_SUBSCRIPTIONS_VL B, JTF_UM_SUBSCRIPTION_REG SUBREG
WHERE SUBREG.SUBSCRIPTION_ID = B.SUBSCRIPTION_ID
AND A.SUBSCRIPTION_ID = B.SUBSCRIPTION_ID
AND A.USERTYPE_ID  = p_usertype_id
AND SUBREG.USER_ID = p_user_id
AND SUBREG.STATUS_CODE = p_sub_status
AND A.EFFECTIVE_END_DATE IS NOT NULL
AND A.EFFECTIVE_END_DATE < SYSDATE
AND NVL(SUBREG.EFFECTIVE_END_DATE, SYSDATE+1) > SYSDATE
AND SUBREG.EFFECTIVE_START_DATE < SYSDATE
AND NOT EXISTS (SELECT 'X'
FROM JTF_UM_USERTYPE_SUBSCRIP A, JTF_UM_SUBSCRIPTIONS_VL B, JTF_UM_SUBSCRIPTION_REG SUBREG
WHERE SUBREG.SUBSCRIPTION_ID = B.SUBSCRIPTION_ID
AND A.SUBSCRIPTION_ID = B.SUBSCRIPTION_ID
AND A.USERTYPE_ID  = p_usertype_id
AND SUBREG.USER_ID = p_user_id
AND SUBREG.STATUS_CODE = p_sub_status
AND NVL(A.EFFECTIVE_END_DATE, SYSDATE+1) > SYSDATE
AND NVL(SUBREG.EFFECTIVE_END_DATE, SYSDATE+1) > SYSDATE
AND SUBREG.EFFECTIVE_START_DATE < SYSDATE)
AND A.EFFECTIVE_END_DATE IN(
    SELECT MAX(EFFECTIVE_END_DATE) FROM JTF_UM_USERTYPE_SUBSCRIP USUB
    WHERE USUB.USERTYPE_ID = p_usertype_id
    AND   USUB.SUBSCRIPTION_ID = A.SUBSCRIPTION_ID
    )
) ALL_ENROLLMENTS ORDER BY SUBSCRIPTION_NAME;

Line: 305


SELECT 'AVAILABLE' SUBSCRIPTION_STATUS, A.SUBSCRIPTION_FLAG, A.SUBSCRIPTION_ID, A.SUBSCRIPTION_DISPLAY_ORDER, B.SUBSCRIPTION_KEY, B.SUBSCRIPTION_NAME, B.DESCRIPTION, B.AUTH_DELEGATION_ROLE_ID, B.APPROVAL_ID, to_number(NULL) REG_ID
FROM JTF_UM_USERTYPE_SUBSCRIP A, JTF_UM_SUBSCRIPTIONS_VL B
WHERE A.SUBSCRIPTION_ID = B.SUBSCRIPTION_ID
AND A.USERTYPE_ID = p_usertype_id
AND NVL(A.EFFECTIVE_END_DATE, SYSDATE+1) > SYSDATE
AND B.ENABLED_FLAG = 'Y'
AND A.EFFECTIVE_START_DATE < SYSDATE
AND NOT EXISTS (SELECT SUBSCRIPTION_REG_ID FROM JTF_UM_SUBSCRIPTION_REG REG WHERE
    USER_ID = p_user_id
    AND REG.SUBSCRIPTION_ID = A.SUBSCRIPTION_ID
    AND REG.STATUS_CODE IN ('APPROVED', 'PENDING')
	AND NVL(REG.EFFECTIVE_END_DATE, SYSDATE +1 ) > Sysdate
	)
ORDER BY A.SUBSCRIPTION_DISPLAY_ORDER, B.SUBSCRIPTION_NAME;

Line: 534


SELECT A.SUBSCRIPTION_FLAG, B.SUBSCRIPTION_NAME, B.DESCRIPTION, B.APPROVAL_ID, C.SUBSCRIPTION_REG_ID
FROM JTF_UM_USERTYPE_SUBSCRIP A, JTF_UM_SUBSCRIPTIONS_VL B, JTF_UM_SUBSCRIPTION_REG C
WHERE A.SUBSCRIPTION_ID = B.SUBSCRIPTION_ID
AND A.USERTYPE_ID = p_usertype_id
AND NVL(A.EFFECTIVE_END_DATE, SYSDATE+1) > SYSDATE
AND B.ENABLED_FLAG = 'Y'
AND A.EFFECTIVE_START_DATE < SYSDATE
AND C.USER_ID = p_user_id
AND C.SUBSCRIPTION_ID = l_subscription_id
AND C.SUBSCRIPTION_ID = B.SUBSCRIPTION_ID
AND NVL(C.EFFECTIVE_END_DATE, SYSDATE+1) > SYSDATE;

Line: 675


SELECT A.SUBSCRIPTION_FLAG, B.SUBSCRIPTION_NAME, B.DESCRIPTION, B.APPROVAL_ID, TMPL.TEMPLATE_HANDLER, TMPL.PAGE_NAME
FROM JTF_UM_USERTYPE_SUBSCRIP A, JTF_UM_SUBSCRIPTIONS_VL B, JTF_UM_SUBSCRIPTION_TMPL SUBTMPL, JTF_UM_TEMPLATES_B TMPL
WHERE A.SUBSCRIPTION_ID = B.SUBSCRIPTION_ID
AND A.USERTYPE_ID = p_usertype_id
AND NVL(A.EFFECTIVE_END_DATE, SYSDATE+1) > SYSDATE
AND NVL(SUBTMPL.EFFECTIVE_END_DATE, SYSDATE+1) > SYSDATE
AND NVL(TMPL.EFFECTIVE_END_DATE, SYSDATE+1) > SYSDATE
AND B.ENABLED_FLAG = 'Y'
AND A.EFFECTIVE_START_DATE < SYSDATE
AND B.SUBSCRIPTION_ID = SUBTMPL.SUBSCRIPTION_ID
AND SUBTMPL.TEMPLATE_ID = TMPL.TEMPLATE_ID
AND B.SUBSCRIPTION_ID   = l_subscription_id
AND TMPL.TEMPLATE_TYPE_CODE  = 'ENROLLMENT_TEMPLATE';