APPS.PON_PROFILE_UTIL_PKG SQL Statements

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

Line: 4

PROCEDURE update_organization_start_date(
  p_party_id IN NUMBER
, x_status		OUT NOCOPY VARCHAR2
, x_exception_msg          OUT NOCOPY VARCHAR2
)
IS
BEGIN
  UPDATE hz_organization_profiles
  SET effective_start_date=trunc(SYSDATE)
  WHERE party_id=p_party_id;

Line: 22

END update_organization_start_date;

Line: 24

FUNCTION get_update_date_from_party (
  p_party_id IN NUMBER
) RETURN DATE
IS
  l_date DATE;

Line: 30

  SELECT last_update_date
  INTO l_date
  FROM hz_parties
  WHERE party_id = p_party_id;

Line: 35

END get_update_date_from_party;

Line: 38

FUNCTION get_update_date_from_location (
  p_location_id IN NUMBER
) RETURN DATE
IS
  l_date DATE;

Line: 44

  SELECT last_update_date
  INTO l_date
  FROM hz_locations
  WHERE location_id = p_location_id;

Line: 49

END get_update_date_from_location;

Line: 51

FUNCTION get_update_date_from_contact (
  p_contact_id IN NUMBER
) RETURN DATE
IS
  l_date DATE;

Line: 57

  SELECT last_update_date
  INTO l_date
  FROM hz_contact_points
  WHERE contact_point_id = p_contact_id;

Line: 62

END get_update_date_from_contact;

Line: 64

PROCEDURE update_ins_party_pref_cover(
  p_party_id          in NUMBER
, p_app_short_name    in VARCHAR2
, p_pref_name         in VARCHAR2
, p_pref_value        in VARCHAR2
, p_pref_meaning      in VARCHAR2
, x_status            out nocopy VARCHAR2
, x_exception_msg     out nocopy VARCHAR2
)
IS
  att1 VARCHAR2(150):= NULL;

Line: 80

  update_or_insert_party_pref(
    p_party_id
  , p_app_short_name
  , p_pref_name
  , p_pref_value
  , p_pref_meaning
  , att1, att2, att3, att4, att5
  , x_status
  , x_exception_msg
  );

Line: 96

PROCEDURE UPDATE_OR_INSERT_PARTY_PREF(
  p_party_id          in NUMBER
, p_app_short_name    in VARCHAR2
, p_pref_name         in VARCHAR2
, p_pref_value        in VARCHAR2
, p_pref_meaning        in VARCHAR2
, p_attribute1        in VARCHAR2
, p_attribute2        in VARCHAR2
, p_attribute3        in VARCHAR2
, p_attribute4        in VARCHAR2
, p_attribute5        in VARCHAR2
, x_status            out nocopy VARCHAR2
, x_exception_msg     out nocopy VARCHAR2
)
IS
 l_count   NUMBER;

Line: 113

 x_exception_msg :='entering update_or_insert_party_perference';

Line: 114

  select count(*)
  into l_count
  from PON_PARTY_PREFERENCES
  where party_id = p_party_id
  AND preference_name = p_pref_name
  AND APP_SHORT_NAME = p_app_short_name;

Line: 124

  UPDATE PON_PARTY_PREFERENCES
  SET PREFERENCE_VALUE = p_pref_value
  , PREFERENCE_MEANING = p_pref_meaning
  , ATTRIBUTE1 = p_attribute1
  , ATTRIBUTE2 = p_attribute2
  , ATTRIBUTE3 = p_attribute3
  , ATTRIBUTE4 = p_attribute4
  , ATTRIBUTE5 = p_attribute5
  WHERE party_id = p_party_id
  AND preference_name = p_pref_name
  AND app_short_name = p_app_short_name;

Line: 136

  x_exception_msg :='inserting party_perference';

Line: 138

  insert into PON_PARTY_PREFERENCES
  (
     party_id
   , APP_SHORT_NAME
   , preference_name
   , PREFERENCE_VALUE
   , PREFERENCE_MEANING
   , ATTRIBUTE1
   , ATTRIBUTE2
   , ATTRIBUTE3
   , ATTRIBUTE4
   , ATTRIBUTE5
  )
  VALUES
(
    p_party_id
  , p_app_short_name
  , p_pref_name
  , p_pref_value
  , p_pref_meaning
  , p_attribute1
  , p_attribute2
  , p_attribute3
  , p_attribute4
  , p_attribute5
);

Line: 172

END UPDATE_OR_INSERT_PARTY_PREF;

Line: 175

PROCEDURE DELETE_PARTY_PREF(
  p_party_id          in NUMBER
, p_app_short_name    in VARCHAR2
, p_pref_name         in VARCHAR2
, x_status            out nocopy VARCHAR2
, x_exception_msg     out nocopy VARCHAR2
)
IS

 l_count   NUMBER;

Line: 186

  x_exception_msg :='entering DELETE_PARTY_PREF';

Line: 188

  SELECT count(*)
  INTO l_count
  FROM PON_PARTY_PREFERENCES
  WHERE party_id = p_party_id
        AND preference_name = p_pref_name
        AND APP_SHORT_NAME = p_app_short_name;

Line: 197

    DELETE FROM PON_PARTY_PREFERENCES
    WHERE party_id = p_party_id
          AND preference_name = p_pref_name
          AND APP_SHORT_NAME = p_app_short_name;

Line: 214

END DELETE_PARTY_PREF;

Line: 265

  SELECT preference_value, preference_meaning, attribute1, attribute2,
        attribute3, attribute4, attribute5
  INTO x_pref_value,x_pref_meaning,x_attribute1,x_attribute2,x_attribute3,
       x_attribute4, x_attribute5
  FROM PON_PARTY_PREFERENCES
  WHERE app_short_name= p_app_short_name
  AND preference_name=p_pref_name
  AND rownum = 1;

Line: 375

  SELECT NLS_LANGUAGE
  INTO l_language
  FROM FND_LANGUAGES
  WHERE LANGUAGE_CODE = p_language_code;

Line: 394

    SELECT user_id
    INTO   ln_user_id
    FROM   fnd_user
    WHERE  user_name = upper(p_user_name);

Line: 404

  SELECT LANGUAGE_CODE
  INTO x_language_code
  FROM FND_LANGUAGES
  WHERE NLS_LANGUAGE = l_language;

Line: 421

  SELECT LANGUAGE_CODE
  INTO x_language_code
  FROM FND_LANGUAGES
  WHERE NLS_LANGUAGE = l_language;

Line: 436

  SELECT NLS_TERRITORY
  INTO l_territory
  FROM FND_TERRITORIES
  WHERE TERRITORY_CODE = p_territory_code;

Line: 454

  SELECT user_id
  INTO   ln_user_id
  FROM   fnd_user
  WHERE  user_name = upper(p_user_name);

Line: 461

  SELECT TERRITORY_CODE
  INTO x_territory_code
  FROM FND_TERRITORIES
  WHERE NLS_TERRITORY = l_territory;

Line: 480

  SELECT NLS_LANGUAGE
  INTO   l_language
  FROM   FND_LANGUAGES
  WHERE  LANGUAGE_CODE = p_language_code;

Line: 485

  SELECT NLS_TERRITORY
  INTO   l_territory
  FROM   FND_TERRITORIES
  WHERE  TERRITORY_CODE = p_territory_code;

Line: 521

     CURSOR c1(name_arg VARCHAR2) IS SELECT message_text
       FROM fnd_new_messages m, fnd_application a
       WHERE name_arg = m.message_name
       AND m.language_code = langin
       AND appin = a.application_short_name
       AND m.application_id = a.application_id;

Line: 528

     CURSOR c2(name_arg VARCHAR2) IS SELECT message_text
       FROM fnd_new_messages m, fnd_application a
       WHERE name_arg = m.message_name
       AND 'US' = m.language_code
       AND appin = a.application_short_name
       AND m.application_id = a.application_id;

Line: 613

  SELECT count(*)
  INTO   l_count
  FROM   hz_relationships
  WHERE  subject_id = p_subject_id
  AND    object_id = p_object_id
  AND    relationship_type = p_relationship_type
  AND    relationship_code = p_relationship_code
  AND    status = 'A'
  AND    start_date <= sysdate
  AND    end_date  >= sysdate
  AND    ROWNUM < 2;