APPS.JTF_UM_USERTYPE_CREDENTIALS SQL Statements

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

Line: 53

    /*  SELECT generate_customer_number INTO l_gen_cust_no
        FROM ar_system_parameters;*/

Line: 62

   SELECT hz_account_num_s.nextval into p_account_number FROM DUAL;

Line: 169

SELECT CUSTOMER_ID
FROM FND_USER
WHERE USER_ID = X_USER_ID
AND (NVL(END_DATE,SYSDATE+1) > SYSDATE OR to_char(END_DATE) = to_char(FND_API.G_MISS_DATE)) ;

Line: 176

SELECT FU.CUSTOMER_ID,HZR.SUBJECT_ID,HZR.OBJECT_ID
FROM hz_relationships HZR,FND_USER FU
WHERE FU.USER_ID = X_USER_ID
AND FU.CUSTOMER_ID = HZR.PARTY_ID
AND HZR.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND HZR.subject_type = 'PERSON'
AND HZR.relationship_code in ('EMPLOYEE_OF', 'CONTACT_OF')
AND HZR.object_table_name = 'HZ_PARTIES'
AND HZR.START_DATE <= SYSDATE
AND NVL(HZR.END_DATE, SYSDATE + 1) > SYSDATE
AND ( NVL(FU.end_date,SYSDATE+1) > SYSDATE OR to_char(FU.END_DATE) = to_char(FND_API.G_MISS_DATE));

Line: 230

	 SELECT COUNT(*) into L_ACCT_CNT
	 FROM HZ_CUST_ACCOUNTS WHERE
	 CUST_ACCOUNT_ID IN( SELECT CUST_ACCOUNT_ID
	 FROM HZ_CUST_ACCOUNT_ROLES
	 WHERE PARTY_ID = p_party_id
	 AND STATUS = 'A') AND STATUS='A' ;

Line: 261

		SELECT COUNT(*) into L_ACCT_CNT
		FROM HZ_CUST_ACCOUNTS
		WHERE PARTY_ID =p_party_id
		AND STATUS = 'A';

Line: 310

 Removed this direct update call as fnd_user_resp_groups is no
 longer a table. Converted this call to use an API instead.

UPDATE FND_USER_RESP_GROUPS SET END_DATE = SYSDATE
WHERE USER_ID = X_USER_ID
AND   RESPONSIBILITY_ID = X_RESPONSIBILITY_ID
AND   RESPONSIBILITY_APPLICATION_ID = X_APPLICATION_ID;

Line: 381

CURSOR RESP_KEY IS SELECT RESPONSIBILITY_ID
FROM FND_RESPONSIBILITY_VL
WHERE RESPONSIBILITY_KEY = X_RESPONSIBILITY_KEY;

Line: 412

CURSOR RESP_KEY IS SELECT RESPONSIBILITY_ID
FROM FND_RESPONSIBILITY_VL
WHERE RESPONSIBILITY_KEY = X_RESPONSIBILITY_KEY;

Line: 463

CURSOR RESP_KEY_ID IS SELECT RESPONSIBILITY_ID
FROM FND_RESPONSIBILITY_VL
WHERE RESPONSIBILITY_KEY = X_RESPONSIBILITY_KEY;

Line: 510

        select NOTIFICATION_PREFERENCE into l_notif_pref
        from jtf_um_usertype_reg ut,
                   wf_local_roles wlr
        where ut.status_code='PENDING'
              and '__JTA_UM'||USERTYPE_REG_ID = name
              and ut.user_id = userId;

Line: 578

CURSOR USERTYPE_RESP is select FR.RESPONSIBILITY_ID, UT.APPLICATION_ID, FR.VERSION
FROM JTF_UM_USERTYPE_RESP UT, FND_RESPONSIBILITY_VL FR
WHERE UT.USERTYPE_ID = X_USERTYPE_ID
AND   FR.APPLICATION_ID  = UT.APPLICATION_ID
AND   FR.RESPONSIBILITY_KEY = UT.RESPONSIBILITY_KEY
AND   (UT.EFFECTIVE_END_DATE IS NULL OR UT.EFFECTIVE_END_DATE > SYSDATE)
AND   UT.EFFECTIVE_START_DATE < SYSDATE;

Line: 586

CURSOR USERTYPE_ROLES IS SELECT PRINCIPAL_NAME
FROM JTF_UM_USERTYPE_ROLE
WHERE USERTYPE_ID = X_USERTYPE_ID
AND   (EFFECTIVE_END_DATE IS NULL OR EFFECTIVE_END_DATE > SYSDATE)
AND   EFFECTIVE_START_DATE < SYSDATE;

Line: 610

	select start_date,end_date into userStartDate,userEndDate from FND_USER
    where user_id = X_USER_ID;

Line: 710

UPDATE JTF_UM_USERTYPE_REG SET STATUS_CODE='APPROVED'
WHERE USERTYPE_ID = X_USERTYPE_ID
AND USER_ID = X_USER_ID
and status_code='PENDING' and nvl(effective_end_date, SYSDATE + 1) > SYSDATE;

Line: 750

		Select ut.APPLICATION_ID,ut.USERTYPE_KEY,reg.USERTYPE_REG_ID
		Into l_app_id, l_usertype_key, l_usertype_reg_id
		From JTF_UM_USERTYPES_B ut ,  JTF_UM_USERTYPE_REG reg
		where  ut.USERTYPE_ID=reg.USERTYPE_ID and reg.USER_ID=X_USER_ID
		and reg.status_code='APPROVED' and
		nvl(reg.EFFECTIVE_END_DATE,sysdate+1) > sysdate;

Line: 783

			   --  delete parameter list as it is no longer required
		     		l_parameter_list.DELETE;

Line: 804

CURSOR FIND_APPROVAL IS SELECT APPROVAL_ID FROM JTF_UM_USERTYPES_B
WHERE USERTYPE_KEY = P_USERTYPE_KEY
AND   (EFFECTIVE_END_DATE IS NULL OR EFFECTIVE_END_DATE > SYSDATE);

Line: 843

SELECT hz_account_num_s.nextval into p_account_number FROM DUAL;

Line: 933

PROCEDURE REJECT_DELETED_PEND_USER (P_USERNAME     in  VARCHAR2,
                                    X_PENDING_USER out NOCOPY VARCHAR2)

IS

CURSOR FIND_UT_APPWF_INFO IS
SELECT reg.WF_ITEM_TYPE, to_char (reg.USERTYPE_REG_ID)
FROM JTF_UM_USERTYPE_REG reg, FND_USER fu
WHERE fu.USER_NAME = P_USERNAME
AND   fu.USER_ID = reg.USER_ID
AND   STATUS_CODE = 'PENDING'
AND   (reg.EFFECTIVE_END_DATE is null
OR     reg.EFFECTIVE_END_DATE > sysdate);

Line: 961

  JTF_UM_WF_APPROVAL.COMPLETEAPPROVALACTIVITY (itemtype, itemkey, 'REJECTED', 'User deleted');

Line: 967

END REJECT_DELETED_PEND_USER;

Line: 979

cursor find_default_resp is select responsibility_key, application_id from
jtf_um_usertype_resp where usertype_id = l_usertype_id
and (effective_end_date is null or effective_end_date > sysdate) ;

Line: 983

cursor find_usertype is select usertype_id from jtf_um_usertypes_b
where usertype_key = l_usertype_key
and (effective_end_date is null or effective_end_date > sysdate) ;

Line: 987

cursor find_user_id is select user_id from fnd_user where user_name = P_USERNAME;

Line: 1041

cursor find_default_role is select principal_name from
jtf_um_usertype_role where usertype_id = l_usertype_id
and (effective_end_date is null or effective_end_date > sysdate) ;

Line: 1045

cursor find_usertype is select usertype_id from jtf_um_usertypes_b
where usertype_key = l_usertype_key
and (effective_end_date is null or effective_end_date > sysdate) ;

Line: 1120

CURSOR FIND_UT_RESP IS SELECT UTRESP.APPLICATION_ID, FURESP.RESPONSIBILITY_ID
FROM   JTF_UM_USERTYPE_RESP UTRESP, FND_RESPONSIBILITY_VL FURESP
WHERE  UTRESP.USERTYPE_ID = p_usertype_id
AND    NVL(UTRESP.EFFECTIVE_END_DATE,SYSDATE+1) > SYSDATE
AND    UTRESP.EFFECTIVE_START_DATE < SYSDATE
AND    UTRESP.RESPONSIBILITY_KEY = FURESP.RESPONSIBILITY_KEY;

Line: 1183

CURSOR FIND_ROLE_NAME IS SELECT PRINCIPAL_NAME FROM JTF_AUTH_PRINCIPALS_B
WHERE JTF_AUTH_PRINCIPAL_ID = p_role_id AND IS_USER_FLAG = 0;

Line: 1448

CURSOR FIND_RESP_INFO IS SELECT RESPONSIBILITY_KEY, RESPONSIBILITY_NAME
FROM FND_RESPONSIBILITY_VL
WHERE RESPONSIBILITY_ID = x_resp_id
AND   APPLICATION_ID    = x_app_id;

Line: 1515

  SELECT USERTYPE_ID
  FROM   JTF_UM_USERTYPES_B
  WHERE  USERTYPE_KEY = l_new_ut_key
  AND    NVL(EFFECTIVE_END_DATE, SYSDATE + 1) > SYSDATE;

Line: 1520

  CURSOR PRIMARY_USERS IS SELECT USERTYPE_REG_ID, USER_ID
  FROM   JTF_UM_USERTYPE_REG UTREG
  WHERE  UTREG.USERTYPE_ID IN (SELECT USERTYPE_ID FROM JTF_UM_USERTYPES_B
                               WHERE USERTYPE_KEY = l_old_ut_key)
  AND    UTREG.STATUS_CODE = 'APPROVED'
  AND    NVL(UTREG.EFFECTIVE_END_DATE, SYSDATE + 1) > SYSDATE
  AND    NOT EXISTS
         (SELECT SUBSCRIPTION_REG_ID FROM JTF_UM_SUBSCRIPTION_REG SUBREG
          WHERE  SUBREG.USER_ID = UTREG.USER_ID
          AND    SUBREG.STATUS_CODE = 'PENDING'
          AND    NVL(SUBREG.EFFECTIVE_END_DATE, SYSDATE + 1) > SYSDATE
         );

Line: 1533

 CURSOR PENDING_USERS IS SELECT f.user_name, p.party_name, f.email_address
  FROM JTF_UM_USERTYPE_REG UTREG, JTF_UM_USERTYPES_B UT, hz_parties p, hz_relationships r, fnd_user f
  WHERE UT.USERTYPE_KEY = l_old_ut_key
  AND   UT.USERTYPE_ID = UTREG.USERTYPE_ID
  AND   UTREG.user_id = f.user_Id
  AND   UTREG.STATUS_CODE = 'PENDING'
  AND   p.party_id = r.object_id and r.party_id = f.customer_id
  AND   NVL(utreg.EFFECTIVE_END_DATE, SYSDATE + 1) > SYSDATE;

Line: 1543

  SELECT f.user_name, p.party_name, f.email_address
  FROM JTF_UM_USERTYPE_REG UTREG, JTF_UM_USERTYPES_B UT, hz_parties p, hz_relationships r, fnd_user f
  WHERE UT.USERTYPE_KEY = 'PRIMARYUSER'
  AND   UT.USERTYPE_ID = UTREG.USERTYPE_ID
  AND   R.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
  AND   R.object_table_name = 'HZ_PARTIES'
  AND   R.START_DATE < SYSDATE
  AND   NVL(R.END_DATE, SYSDATE + 1) > SYSDATE
  AND   p.party_id = r.object_id and r.party_id = f.customer_id
  AND   NVL(utreg.EFFECTIVE_END_DATE, SYSDATE + 1) > SYSDATE
  AND   UTREG.user_id = f.user_Id
  AND   p.party_type = 'ORGANIZATION'
  AND
 (
 	UTREG.STATUS_CODE = 'PENDING'
	OR
	 ( UTREG.STATUS_CODE = 'APPROVED'
	    AND
	    EXISTS
	    (
		   SELECT SUBSCRIPTION_REG_ID
		    FROM JTF_UM_SUBSCRIPTION_REG SUBREG
		    WHERE  SUBREG.USER_ID = UTREG.USER_ID
		    AND
		    SUBREG.STATUS_CODE = 'PENDING'
		    AND
		    NVL(SUBREG.EFFECTIVE_END_DATE, SYSDATE + 1) > SYSDATE
	   )
	 )
 );

Line: 1596

         UPDATE JTF_UM_USERTYPE_REG SET USERTYPE_ID = l_new_usertype_id
         WHERE  USERTYPE_REG_ID = i.USERTYPE_REG_ID;

Line: 1603

         select f.user_name, p.party_name, f.email_address into l_user_name, l_org_name, l_email
         from hz_parties p, hz_relationships r, fnd_user f
         where
            p.party_id = r.object_id and r.party_id = f.customer_id
            AND R.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
            AND R.object_table_name = 'HZ_PARTIES'
            AND R.START_DATE < SYSDATE
            AND NVL(R.END_DATE, SYSDATE + 1) > SYSDATE
            and   f.user_id = i.user_id
	    and  r.relationship_code='EMPLOYEE_OF';

Line: 1644

  SELECT COUNT(*) INTO l_pending_users
  FROM JTF_UM_USERTYPE_REG UTREG, JTF_UM_USERTYPES_B UT
  WHERE UT.USERTYPE_KEY = l_old_ut_key
  AND   UT.USERTYPE_ID = UTREG.USERTYPE_ID
  AND   UTREG.STATUS_CODE = 'PENDING';