APPS.JTF_UM_SUBSCRIPTION_INFO dependencies on JTF_UM_SUBSCRIPTION_REG

Line 12: FROM JTF_UM_SUBSCRIPTION_REG

8: p_user_id number) return NUMBER IS
9:
10:
11: CURSOR USER_ENROLLMENT IS SELECT SUBSCRIPTION_REG_ID
12: FROM JTF_UM_SUBSCRIPTION_REG
13: WHERE SUBSCRIPTION_ID = p_subscription_id
14: AND USER_ID = p_user_id
15: AND NVL(EFFECTIVE_END_DATE, SYSDATE+1) > SYSDATE
16: AND STATUS_CODE IN ('APPROVED', 'PENDING');

Line 178: FROM JTF_UM_USERTYPE_SUBSCRIP A, JTF_UM_SUBSCRIPTIONS_VL B, JTF_UM_SUBSCRIPTION_REG SUBREG

174: SUBSCRIPTION_KEY, SUBSCRIPTION_NAME, DESCRIPTION, AUTH_DELEGATION_ROLE_ID, REG_ID FROM
175: (
176: SELECT DECODE(SUBREG.STATUS_CODE,'APPROVED','CURRENT','PENDING','PENDING') SUBSCRIPTION_STATUS, A.SUBSCRIPTION_FLAG, A.SUBSCRIPTION_ID, A.SUBSCRIPTION_DISPLAY_ORDER,
177: B.SUBSCRIPTION_KEY, B.SUBSCRIPTION_NAME, B.DESCRIPTION, B.AUTH_DELEGATION_ROLE_ID, SUBREG.SUBSCRIPTION_REG_ID REG_ID
178: FROM JTF_UM_USERTYPE_SUBSCRIP A, JTF_UM_SUBSCRIPTIONS_VL B, JTF_UM_SUBSCRIPTION_REG SUBREG
179: WHERE SUBREG.SUBSCRIPTION_ID = B.SUBSCRIPTION_ID
180: AND A.SUBSCRIPTION_ID = B.SUBSCRIPTION_ID
181: AND A.USERTYPE_ID = p_usertype_id
182: AND SUBREG.USER_ID = p_user_id

Line 193: FROM JTF_UM_USERTYPE_SUBSCRIP A, JTF_UM_SUBSCRIPTIONS_VL B, JTF_UM_SUBSCRIPTION_REG SUBREG

189:
190: SELECT DECODE(SUBREG.STATUS_CODE,'APPROVED','CURRENT','PENDING','PENDING') SUBSCRIPTION_STATUS, A.SUBSCRIPTION_FLAG, A.SUBSCRIPTION_ID, A.SUBSCRIPTION_DISPLAY_ORDER,
191: B.SUBSCRIPTION_KEY, B.SUBSCRIPTION_NAME, B.DESCRIPTION, B.AUTH_DELEGATION_ROLE_ID, SUBREG.SUBSCRIPTION_REG_ID REG_ID
192:
193: FROM JTF_UM_USERTYPE_SUBSCRIP A, JTF_UM_SUBSCRIPTIONS_VL B, JTF_UM_SUBSCRIPTION_REG SUBREG
194: WHERE SUBREG.SUBSCRIPTION_ID = B.SUBSCRIPTION_ID
195: AND A.SUBSCRIPTION_ID = B.SUBSCRIPTION_ID
196: AND A.USERTYPE_ID = p_usertype_id
197: AND SUBREG.USER_ID = p_user_id

Line 204: FROM JTF_UM_USERTYPE_SUBSCRIP A, JTF_UM_SUBSCRIPTIONS_VL B, JTF_UM_SUBSCRIPTION_REG SUBREG

200: AND A.EFFECTIVE_END_DATE < SYSDATE
201: AND NVL(SUBREG.EFFECTIVE_END_DATE, SYSDATE+1) > SYSDATE
202: AND SUBREG.EFFECTIVE_START_DATE < SYSDATE
203: AND NOT EXISTS (SELECT 'X'
204: FROM JTF_UM_USERTYPE_SUBSCRIP A, JTF_UM_SUBSCRIPTIONS_VL B, JTF_UM_SUBSCRIPTION_REG SUBREG
205: WHERE SUBREG.SUBSCRIPTION_ID = B.SUBSCRIPTION_ID
206: AND A.SUBSCRIPTION_ID = B.SUBSCRIPTION_ID
207: AND A.USERTYPE_ID = p_usertype_id
208: AND SUBREG.USER_ID = p_user_id

Line 312: AND NOT EXISTS (SELECT SUBSCRIPTION_REG_ID FROM JTF_UM_SUBSCRIPTION_REG REG WHERE

308: AND A.USERTYPE_ID = p_usertype_id
309: AND NVL(A.EFFECTIVE_END_DATE, SYSDATE+1) > SYSDATE
310: AND B.ENABLED_FLAG = 'Y'
311: AND A.EFFECTIVE_START_DATE < SYSDATE
312: AND NOT EXISTS (SELECT SUBSCRIPTION_REG_ID FROM JTF_UM_SUBSCRIPTION_REG REG WHERE
313: USER_ID = p_user_id
314: AND REG.SUBSCRIPTION_ID = A.SUBSCRIPTION_ID
315: AND REG.STATUS_CODE IN ('APPROVED', 'PENDING')
316: AND NVL(REG.EFFECTIVE_END_DATE, SYSDATE +1 ) > Sysdate

Line 535: FROM JTF_UM_USERTYPE_SUBSCRIP A, JTF_UM_SUBSCRIPTIONS_VL B, JTF_UM_SUBSCRIPTION_REG C

531: l_subscription_id NUMBER;
532:
533: CURSOR FIND_SUB_INFO IS
534: SELECT A.SUBSCRIPTION_FLAG, B.SUBSCRIPTION_NAME, B.DESCRIPTION, B.APPROVAL_ID, C.SUBSCRIPTION_REG_ID
535: FROM JTF_UM_USERTYPE_SUBSCRIP A, JTF_UM_SUBSCRIPTIONS_VL B, JTF_UM_SUBSCRIPTION_REG C
536: WHERE A.SUBSCRIPTION_ID = B.SUBSCRIPTION_ID
537: AND A.USERTYPE_ID = p_usertype_id
538: AND NVL(A.EFFECTIVE_END_DATE, SYSDATE+1) > SYSDATE
539: AND B.ENABLED_FLAG = 'Y'