APPS.PON_USER_PROFILE_PKG SQL Statements

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

Line: 10

procedure update_user_lang(
  p_username        IN VARCHAR2
, p_user_language   IN VARCHAR2
, x_status          OUT NOCOPY VARCHAR2
, x_exception_msg   OUT NOCOPY VARCHAR2
)
IS
  l_exception_msg   VARCHAR2(100);

Line: 40

END update_user_lang;

Line: 44

procedure update_user_info(
  p_username                        IN VARCHAR2
, P_USER_NAME_PREFIX                IN VARCHAR2
, P_USER_NAME_F                             IN VARCHAR2
, P_USER_NAME_M                             IN VARCHAR2
, P_USER_NAME_L                             IN VARCHAR2
, P_USER_NAME_SUFFIX                IN VARCHAR2
, P_USER_TITLE                              IN VARCHAR2
, P_USER_EMAIL                              IN VARCHAR2
, P_USER_COUNTRY_CODE               IN VARCHAR2
, P_USER_AREA_CODE                  IN VARCHAR2
, P_USER_PHONE                              IN VARCHAR2
, P_USER_EXTENSION                  IN VARCHAR2
, P_USER_FAX_COUNTRY_CODE   IN VARCHAR2
, P_USER_FAX_AREA_CODE              IN VARCHAR2
, P_USER_FAX                                IN VARCHAR2
, P_USER_FAX_EXTENSION              IN VARCHAR2
, P_USER_TIMEZONE                   IN VARCHAR2
, P_USER_LANGUAGE                   IN VARCHAR2
, P_USER_DATEFORMAT                   IN VARCHAR2
, P_USER_LOCALE        IN VARCHAR2
, P_USER_ENCODINGOPTION 	IN VARCHAR2
, x_status		OUT NOCOPY VARCHAR2
, x_exception_msg   OUT NOCOPY VARCHAR2
)
IS
  l_user_party_id    NUMBER;

Line: 79

  l_update_date        DATE;

Line: 82

  SELECT person_party_id
  INTO   l_user_party_id
  FROM   fnd_user
  WHERE  user_name = p_username;

Line: 88

   SELECT vendor_party_id
   INTO   l_vendor_party_id
   FROM   pos_supplier_users_v
   WHERE  person_party_id = l_user_party_id;

Line: 96

   POS_SUPP_CONTACT_PKG.update_supplier_contact
    (p_contact_party_id =>  l_user_party_id,
     p_vendor_party_id   => l_vendor_party_id,
     p_first_name        => P_USER_NAME_F,
     p_last_name         => P_USER_NAME_L,
     p_middle_name       => P_USER_NAME_M,
     p_contact_title     => P_USER_TITLE,
     p_job_title         => NULL,
     p_phone_area_code   => P_USER_COUNTRY_CODE,
     p_phone_number      => P_USER_PHONE,
     p_phone_extension   => P_USER_EXTENSION,
     p_fax_area_code     => P_USER_FAX_AREA_CODE,
     p_fax_number        => P_USER_FAX,
     p_email_address     => P_USER_EMAIL,
     x_return_status     => x_return_status,
     x_msg_count         => x_msg_count,
     x_msg_data          => x_msg_data
     );

Line: 117

      RAISE UPDATE_SUPPLIER_CONTACT_E;

Line: 121

  UPDATE fnd_user
  SET email_address = p_user_email
  WHERE person_party_id = l_user_party_id;

Line: 126

   PON_PROFILE_UTIL_PKG.update_or_insert_party_pref(l_user_party_id, 'PON',
      'USER_ENCODING', p_user_encodingoption, 'User Charset Encoding Option', NULL,NULL,NULL,NULL,NULL,
      x_status, x_exception_msg);

Line: 135

    WHEN UPDATE_SUPPLIER_CONTACT_E THEN
      X_STATUS	:= x_return_status;

Line: 149

END update_user_info;

Line: 185

  SELECT person_party_id, user_id
  INTO   x_user_party_id
  ,      l_user_id
  FROM   fnd_user
  WHERE  user_name = p_username;

Line: 192

x_exception_msg:='retrieve_user_info: select person, phone, fax';

Line: 196

      SELECT  hp1.person_pre_name_adjunct
      ,       hp1.person_first_name
      ,       hp1.person_middle_name
      ,       hp1.person_last_name
      ,       hp1.person_name_suffix
      ,       hp1.person_title
      ,       hc3.email_address
      ,       hc1.phone_country_code
      ,       hc1.phone_area_code
      ,       hc1.phone_number
      ,       hc1.phone_extension
      ,       hc4.phone_country_code
      ,       hc4.phone_area_code
      ,       hc4.phone_number
      ,       hc4.phone_extension
      INTO
         X_USER_NAME_PREFIX
      ,  X_USER_NAME_F
      ,  X_USER_NAME_M
      ,  X_USER_NAME_L
      ,  X_USER_NAME_SUFFIX
      ,  X_USER_TITLE
      ,  X_USER_EMAIL
      ,  X_USER_COUNTRY_CODE
      ,  X_USER_AREA_CODE
      ,  X_USER_PHONE
      ,  X_USER_EXTENSION
      ,  X_USER_FAX_COUNTRY_CODE
      ,  X_USER_FAX_AREA_CODE
      ,  X_USER_FAX
      ,  X_USER_FAX_EXTENSION
      FROM    HZ_PARTIES              hp1  -- Person
      ,       HZ_CONTACT_POINTS       hc1  -- Phone
      ,       HZ_CONTACT_POINTS       hc3  -- Email
      ,       HZ_CONTACT_POINTS       hc4  -- Fax
      ,       POS_SUPPLIER_USERS_V  posv
      WHERE   hp1.party_id               = x_user_party_id
      AND     hp1.party_id = posv.person_party_id
      AND     hp1.status                 = 'A'
      AND     hc1.owner_table_name(+)    = 'HZ_PARTIES'
      AND     hc1.owner_table_id(+)      = posv.rel_party_id
      AND     hc1.contact_point_type(+)  = 'PHONE'
      AND     hc1.phone_line_type(+)     = 'GEN'
      AND     hc1.status(+)              = 'A'
      AND     hc1.primary_flag(+)        = 'Y'
      AND     hc3.owner_table_name(+)    = 'HZ_PARTIES'
      AND     hc3.owner_table_id(+)      = posv.rel_party_id
      AND     hc3.contact_point_type(+)  = 'EMAIL'
      AND     hc3.primary_flag(+)        = 'Y'
      AND     hc3.status(+)              = 'A'
      AND     hc4.owner_table_name(+)    = 'HZ_PARTIES'
      AND     hc4.owner_table_id(+)      = posv.rel_party_id
      AND     hc4.contact_point_type(+)  = 'PHONE'
      AND     hc4.phone_line_type(+)     = 'FAX'
      AND     hc4.status(+)              = 'A'
      AND     hc4.primary_flag(+)        = 'Y'
      AND     nvl(posv.USER_END_DATE,sysdate) >= sysdate;

Line: 262

      SELECT  hp1.person_pre_name_adjunct
      ,       hp1.person_first_name
      ,       hp1.person_middle_name
      ,       hp1.person_last_name
      ,       hp1.person_name_suffix
      ,       hp1.person_title
      ,       hc3.email_address
      ,       hc1.phone_country_code
      ,       hc1.phone_area_code
      ,       hc1.phone_number
      ,       hc1.phone_extension
      ,       hc4.phone_country_code
      ,       hc4.phone_area_code
      ,       hc4.phone_number
      ,       hc4.phone_extension
      INTO
      X_USER_NAME_PREFIX
      ,  X_USER_NAME_F
      ,  X_USER_NAME_M
      ,  X_USER_NAME_L
      ,  X_USER_NAME_SUFFIX
      ,  X_USER_TITLE
      ,  X_USER_EMAIL
      ,  X_USER_COUNTRY_CODE
      ,  X_USER_AREA_CODE
      ,  X_USER_PHONE
      ,  X_USER_EXTENSION
      ,  X_USER_FAX_COUNTRY_CODE
      ,  X_USER_FAX_AREA_CODE
      ,  X_USER_FAX
      ,  X_USER_FAX_EXTENSION
      FROM    HZ_PARTIES              hp1  -- Person
      ,       HZ_CONTACT_POINTS       hc1  -- Phone
      ,       HZ_CONTACT_POINTS       hc3  -- Email
      ,       HZ_CONTACT_POINTS       hc4  -- Fax
      ,       POS_SUPPLIER_USERS_V  posv
      WHERE          hp1.party_id               = x_user_party_id
             AND     hp1.party_id = posv.person_party_id
             AND     hp1.status                 = 'A'
             AND     hc1.owner_table_name(+)    = 'HZ_PARTIES'
             AND     hc1.owner_table_id(+)      = posv.rel_party_id
             AND     hc1.contact_point_type(+)  = 'PHONE'
             AND     hc1.phone_line_type(+)     = 'GEN'
             AND     hc1.status(+)              = 'A'
             AND     hc1.primary_flag(+)        = 'Y'
             AND     hc3.owner_table_name(+)    = 'HZ_PARTIES'
             AND     hc3.owner_table_id(+)      = posv.rel_party_id
             AND     hc3.contact_point_type(+)  = 'EMAIL'
             AND     hc3.primary_flag(+)        = 'Y'
             AND     hc3.status(+)              = 'A'
             AND     hc4.owner_table_name(+)    = 'HZ_PARTIES'
             AND     hc4.owner_table_id(+)      = posv.rel_party_id
             AND     hc4.contact_point_type(+)  = 'PHONE'
             AND     hc4.phone_line_type(+)     = 'FAX'
             AND     hc4.status(+)              = 'A'
             AND     hc4.primary_flag(+)        = 'Y'
             AND     nvl(posv.USER_END_DATE,sysdate) >= sysdate
	     AND     rownum                     = 1;

Line: 352

  UPDATE fnd_user
  SET    encrypted_user_password = p_new_password
  ,      password_date = sysdate
  ,      password_accesses_left = 9999999
  ,      last_update_date = sysdate
  ,      last_updated_by = fnd_global.user_id
  WHERE user_name = p_username
  AND   description = 'Oracle Exchange User';

Line: 384

  SELECT user_id
  INTO l_user_id
  FROM fnd_user
  WHERE user_name = p_username
  AND   description = 'Oracle Exchange User';

Line: 404

PROCEDURE delete_user(
  p_username      IN VARCHAR2
, x_status        OUT NOCOPY VARCHAR2
, x_exception_msg OUT NOCOPY VARCHAR2
)
IS
  l_user_party_id NUMBER;

Line: 413

    SELECT party_site_id
    FROM hz_party_sites
    WHERE party_id = l_user_party_id;

Line: 417

  SELECT person_party_id
  INTO l_user_party_id
  FROM fnd_user
  WHERE user_name = p_username;

Line: 422

  DELETE FROM fnd_user
  WHERE user_name = p_username;

Line: 426

  DELETE FROM hz_person_profiles
  WHERE party_id = l_user_party_id;

Line: 430

  DELETE FROM hz_parties
  WHERE party_id = l_user_party_id;

Line: 434

  DELETE FROM hz_contact_points
  WHERE owner_table_name = 'HZ_PARTIES'
  AND owner_table_id = l_user_party_id;

Line: 442

    x_exception_msg := 'Failed to delete user, rolling back';

Line: 444

END delete_user;

Line: 513

procedure update_pwd_challenge (
  p_user_party_id               IN NUMBER
, p_user_pwd_question           IN VARCHAR2
, p_user_pwd_response           IN VARCHAR2
, p_enc_foundation              IN VARCHAR2
, x_status                      OUT NOCOPY VARCHAR2
, x_exception_msg               OUT NOCOPY VARCHAR2
)
IS
  -- l_pwd_response_encrypted              VARCHAR2(240);

Line: 529

  pon_profile_util_pkg.update_or_insert_party_pref(
      p_party_id       => p_user_party_id,
      p_app_short_name => 'PON',
      p_pref_name      => 'PON_USER_PWD_QUESTION',
      p_pref_value     => p_user_pwd_question,
      p_pref_meaning   => NULL,
      p_attribute1     => NULL,
      p_attribute2     => NULL,
      p_attribute3     => NULL,
      p_attribute4     => NULL,
      p_attribute5     => NULL,
      x_status         => x_status,
      x_exception_msg  => x_exception_msg);

Line: 543

  pon_profile_util_pkg.update_or_insert_party_pref(
      p_party_id       => p_user_party_id,
      p_app_short_name => 'PON',
      p_pref_name      => 'PON_USER_PWD_RESPONSE',
      p_pref_value     => p_user_pwd_response,
      p_pref_meaning   => NULL,
      p_attribute1     => p_enc_foundation,
      p_attribute2     => NULL,
      p_attribute3     => NULL,
      p_attribute4     => NULL,
      p_attribute5     => NULL,
      x_status         => x_status,
      x_exception_msg  => x_exception_msg);

Line: 572

END update_pwd_challenge;

Line: 618

        select user_id into
        l_user_id
        from fnd_user
        where user_name = p_username;

Line: 828

  SELECT person_party_id, user_id
  INTO   x_user_party_id
  ,      l_user_id
  FROM   fnd_user
  WHERE  user_name = p_username;

Line: 836

x_exception_msg:='retrieve_user_info: select person, phone, fax';

Line: 839

SELECT  hp1.person_pre_name_adjunct
,       hp1.person_first_name
,       hp1.person_middle_name
,       hp1.person_last_name
,       hp1.person_name_suffix
,       hp1.person_title
,       hc3.email_address
,       hc1.phone_country_code
,       hc1.phone_area_code
,       hc1.phone_number
,       hc1.phone_extension
,       hc4.phone_country_code
,       hc4.phone_area_code
,       hc4.phone_number
,       hc4.phone_extension
INTO
X_USER_NAME_PREFIX
,  X_USER_NAME_F
,  X_USER_NAME_M
,  X_USER_NAME_L
,  X_USER_NAME_SUFFIX
,  X_USER_TITLE
,  X_USER_EMAIL
,  X_USER_COUNTRY_CODE
,  X_USER_AREA_CODE
,  X_USER_PHONE
,  X_USER_EXTENSION
,  X_USER_FAX_COUNTRY_CODE
,  X_USER_FAX_AREA_CODE
,  X_USER_FAX
,  X_USER_FAX_EXTENSION
FROM    HZ_PARTIES              hp1  -- Person
,       HZ_CONTACT_POINTS       hc1  -- Phone
,       HZ_CONTACT_POINTS       hc3  -- Email
,       HZ_CONTACT_POINTS       hc4  -- Fax
WHERE   hp1.party_id               = x_user_party_id
AND     hc1.owner_table_name(+)    = 'HZ_PARTIES'
AND     hc1.owner_table_id(+)      = hp1.party_id
AND     hc1.contact_point_type(+)  = 'PHONE'
AND     hc1.phone_line_type(+)     = 'GEN'
AND     hc1.status(+)              = 'A'
AND     hc1.primary_flag(+)        = 'Y'
AND     hc3.owner_table_name(+)    = 'HZ_PARTIES'
AND     hc3.owner_table_id(+)      = hp1.party_id
AND     hc3.contact_point_type(+)  = 'EMAIL'
AND     hc3.EMAIL_FORMAT(+)        = 'MAILTEXT'
AND     hc3.status(+)              = 'A'
AND     hc3.primary_flag(+)        = 'Y'
AND     hc4.owner_table_name(+)    = 'HZ_PARTIES'
AND     hc4.owner_table_id(+)      = hp1.party_id
AND     hc4.contact_point_type(+)  = 'PHONE'
AND     hc4.phone_line_type(+)     = 'FAX'
AND     hc4.status(+)              = 'A'
AND     hc4.primary_flag(+)        = 'Y';

Line: 952

  SELECT person_party_id, user_id, employee_id
  INTO   x_user_party_id
  ,      l_user_id
  ,      l_employee_id
  FROM   fnd_user
  WHERE  user_name = p_username;

Line: 960

    x_exception_msg:='retrieve_user_info: select person, phone, fax';

Line: 963

SELECT  hp1.person_pre_name_adjunct
,       hp1.person_first_name
,       hp1.person_middle_name
,       hp1.person_last_name
,       hp1.person_name_suffix
,       hp1.person_title
INTO
X_USER_NAME_PREFIX
,  X_USER_NAME_F
,  X_USER_NAME_M
,  X_USER_NAME_L
,  X_USER_NAME_SUFFIX
,  X_USER_TITLE
FROM    HZ_PARTIES              hp1  -- Person
WHERE   hp1.party_id               = x_user_party_id
        and hp1.status = 'A';

Line: 981

        select email_address
        into x_user_email
        from per_all_people_f
        where person_id = l_employee_id
        and effective_start_date < sysdate
        and nvl(effective_end_date,sysdate) >= sysdate;

Line: 994

        select phone_number
        into x_user_phone
        from per_phones
        where phone_type = 'W1'
        and parent_id = l_employee_id;

Line: 1005

        select phone_number
        into x_user_fax
        from per_phones
        where phone_type = 'WF'
        and parent_id = l_employee_id;