APPS.JTF_UM_USER_ADMIN_PVT SQL Statements

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

Line: 34

    select status_code
    from   jtf_um_usertype_reg
    where  user_id = p_userid
    and    nvl (effective_end_date, sysdate + 1) > sysdate;

Line: 73

    select user_id
    from   fnd_user
    where  user_name = p_username
    and    (nvl (end_date, sysdate + 1) > sysdate or
           to_char(END_DATE) = to_char(FND_API.G_MISS_DATE))     ;

Line: 158

    select wf_item_type, to_char (usertype_reg_id)
    from   jtf_um_usertype_reg
    where  user_id = l_userid
    and    status_code = 'PENDING'
    and    nvl (effective_end_date, sysdate + 1) > sysdate;

Line: 165

    select wf_item_type, subscription_reg_id
    from   jtf_um_subscription_reg
    where  user_id = l_userid
    and    status_code = 'PENDING'
    and    nvl (effective_end_date, sysdate + 1) > sysdate;

Line: 172

    select customer_id
    from   fnd_user
    where  user_id = l_userid
    and    (nvl (end_date, sysdate + 1) > sysdate OR
           to_char(END_DATE) = to_char(FND_API.G_MISS_DATE)) ;

Line: 237

      UPDATE JTF_UM_USERTYPE_REG
      SET    status_code = 'REJECTED',
             last_updated_by = fnd_global.user_id,
             last_update_date = sysdate,
             last_approver_comment = 'USER DELETED',
             effective_end_date = sysdate
      WHERE  user_id = l_userid
      AND    nvl (effective_end_date, sysdate + 1) > sysdate
      AND    status_code = 'PENDING';

Line: 248

      UPDATE JTF_UM_SUBSCRIPTION_REG
      SET    status_code = 'USER_REJECTED',
             last_updated_by = fnd_global.user_id,
             last_update_date = sysdate,
             last_approver_comment = 'USER DELETED',
             effective_end_date = sysdate
      WHERE  user_id = l_userid
      AND    nvl (effective_end_date, sysdate + 1) > sysdate
      AND    status_code = 'PENDING';

Line: 267

      UPDATE JTF_UM_USERTYPE_REG
      SET    status_code = 'REJECTED',
             last_updated_by = fnd_global.user_id,
             last_update_date = sysdate,
             last_approver_comment = 'USER DELETED',
             effective_end_date = sysdate
      WHERE  user_id = l_userid
      AND    nvl (effective_end_date, sysdate + 1) > sysdate
      AND    status_code = 'PENDING';

Line: 315

      task_mgr.update_task ('1.0', '1',
                            l_task_table(l_task_table.first).task_id,
                            'USER DELETED', 'REJECT', x_msg_data,
                            x_return_status, x_msg_count);

Line: 334

      UPDATE JTF_UM_SUBSCRIPTION_REG
      SET    status_code = 'USER_REJECTED',
             last_updated_by = fnd_global.user_id,
             last_update_date = sysdate,
             last_approver_comment = 'USER DELETED',
             effective_end_date = sysdate
      WHERE  user_id = l_userid
      AND    nvl (effective_end_date, sysdate + 1) > sysdate
      AND    status_code = 'PENDING';

Line: 423

SELECT WF_ITEM_TYPE
FROM JTF_UM_APPROVALS_B APR,
     JTF_UM_USERTYPES_B UT
WHERE UT.APPROVAL_ID = APR.APPROVAL_ID
AND UT.USERTYPE_ID = p_usertype_id;

Line: 430

SELECT APPROVAL_ID, USERTYPE_KEY
FROM JTF_UM_USERTYPES_B
WHERE USERTYPE_ID = p_usertype_id;

Line: 443

     JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'before insert um reg row');

Line: 447

  JTF_UM_USERTYPES_PKG.INSERT_UMREG_ROW(
    X_USERTYPE_ID => p_usertype_id,
    X_LAST_APPROVER_COMMENT => null,
    X_APPROVER_USER_ID => null,
    X_EFFECTIVE_END_DATE => null,
    X_WF_ITEM_TYPE => l_wf_item_type,
    X_EFFECTIVE_START_DATE => sysdate,
    X_USERTYPE_REG_ID => x_user_reg_id,
    X_USER_ID => p_user_id,
    X_STATUS_CODE => l_status,
    X_CREATION_DATE => sysdate,
    X_CREATED_BY    => l_user_id,
    X_LAST_UPDATE_DATE => sysdate,
    X_LAST_UPDATED_BY => l_user_id,
    X_LAST_UPDATE_LOGIN => l_login_id);

Line: 464

     JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'After insert um reg row');

Line: 472

	UPDATE JTF_UM_SUBSCRIPTION_REG
	SET effective_END_DATE =SYSDATE, last_update_login=l_user_id,last_update_date=sysdate
	WHERE USER_ID = p_user_id and status_code='APPROVED'
	and effective_start_date < sysdate and nvl(effective_end_date,sysdate +1) > sysdate
	AND SUBSCRIPTION_ID IN
	(select SUBSCRIPTION_ID from JTF_UM_USERTYPE_SUBSCRIP
			where userTYpe_id=p_usertype_id and
			effective_start_date < sysdate and nvl(effective_end_date,sysdate +1) > sysdate);

Line: 485

     JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'returning from insert um reg row reg id '|| x_user_reg_id);

Line: 524

  SELECT UT.USERTYPE_KEY, UT.USERTYPE_NAME, TMPL.TEMPLATE_ID,
         TMPL.PAGE_NAME, TMPL.TEMPLATE_HANDLER
  FROM JTF_UM_USERTYPES_VL UT, JTF_UM_USERTYPE_TMPL UTMPL,
       JTF_UM_TEMPLATES_B TMPL
  WHERE UT.usertype_id = p_usertype_id
  AND   UT.usertype_id = UTMPL.usertype_id
  AND   UTMPL.template_id = TMPL.template_id
  AND   SYSDATE between nvl(UT.EFFECTIVE_START_DATE, sysdate)
                    and nvl(UT.EFFECTIVE_END_DATE, sysdate)
  AND   SYSDATE between nvl(TMPL.EFFECTIVE_START_DATE, sysdate)
                    and nvl(TMPL.EFFECTIVE_END_DATE, sysdate)
  AND   SYSDATE between nvl(UTMPL.EFFECTIVE_START_DATE, sysdate)
                    and nvl(UTMPL.EFFECTIVE_END_DATE, sysdate);