APPS.PV_GQ_ELEMENTS_PKG SQL Statements

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

Line: 42

PROCEDURE Insert_Row(
          px_qsnr_element_id   IN OUT NOCOPY NUMBER,
          px_object_version_number   IN OUT NOCOPY NUMBER,
          p_arc_used_by_entity_code    VARCHAR2,
          p_used_by_entity_id    NUMBER,
          p_qsnr_elmt_seq_num    NUMBER,
          p_qsnr_elmt_type    VARCHAR2,
          p_entity_attr_id    NUMBER,
          p_qsnr_elmt_page_num    NUMBER,
          p_is_required_flag    VARCHAR2,
          p_created_by    NUMBER,
          p_creation_date    DATE,
          p_last_updated_by    NUMBER,
          p_last_update_date    DATE,
          p_last_update_login    NUMBER,
          p_elmt_content    VARCHAR2
)

 IS
   x_rowid    VARCHAR2(30);

Line: 70

   INSERT INTO pv_ge_qsnr_elements_b(
           qsnr_element_id,
           object_version_number,
           arc_used_by_entity_code,
           used_by_entity_id,
           qsnr_elmt_seq_num,
           qsnr_elmt_type,
           entity_attr_id,
           qsnr_elmt_page_num,
           is_required_flag,
           created_by,
           creation_date,
           last_updated_by,
           last_update_date,
           last_update_login
   ) VALUES (
           DECODE( px_qsnr_element_id, FND_API.G_MISS_NUM, NULL, px_qsnr_element_id),
           DECODE( px_object_version_number, FND_API.G_MISS_NUM, 1, px_object_version_number),
           DECODE( p_arc_used_by_entity_code, FND_API.g_miss_char, NULL, p_arc_used_by_entity_code),
           DECODE( p_used_by_entity_id, FND_API.G_MISS_NUM, NULL, p_used_by_entity_id),
           DECODE( p_qsnr_elmt_seq_num, FND_API.G_MISS_NUM, NULL, p_qsnr_elmt_seq_num),
           DECODE( p_qsnr_elmt_type, FND_API.g_miss_char, NULL, p_qsnr_elmt_type),
           DECODE( p_entity_attr_id, FND_API.G_MISS_NUM, NULL, p_entity_attr_id),
           DECODE( p_qsnr_elmt_page_num, FND_API.G_MISS_NUM, NULL, p_qsnr_elmt_page_num),
           DECODE( p_is_required_flag, FND_API.g_miss_char, NULL, p_is_required_flag),
           DECODE( p_created_by, FND_API.G_MISS_NUM, FND_GLOBAL.USER_ID, p_created_by),
           DECODE( p_creation_date, FND_API.G_MISS_DATE, SYSDATE, p_creation_date),
           DECODE( p_last_updated_by, FND_API.G_MISS_NUM, FND_GLOBAL.USER_ID, p_last_updated_by),
           DECODE( p_last_update_date, FND_API.G_MISS_DATE, SYSDATE, p_last_update_date),
           DECODE( p_last_update_login, FND_API.G_MISS_NUM, FND_GLOBAL.CONC_LOGIN_ID, p_last_update_login));

Line: 101

   INSERT INTO pv_ge_qsnr_elements_tl(
           qsnr_element_id ,
           --object_version_number,
           language ,
           last_update_date ,
           last_updated_by ,
           creation_date ,
           created_by ,
           last_update_login ,
           source_lang ,
           elmt_content
)
SELECT
           DECODE( px_qsnr_element_id, FND_API.G_MISS_NUM, NULL, px_qsnr_element_id),
           --DECODE( px_object_version_number, FND_API.G_MISS_NUM, 1, px_object_version_number),
           l.language_code,
           DECODE( p_last_update_date, NULL, SYSDATE, p_last_update_date),
           DECODE( p_last_updated_by, NULL, FND_GLOBAL.USER_ID, p_last_updated_by),
           DECODE( p_creation_date, NULL, SYSDATE, p_creation_date),
           DECODE( p_created_by, NULL, FND_GLOBAL.USER_ID, p_created_by),
           DECODE( p_last_update_login, NULL, FND_GLOBAL.CONC_LOGIN_ID, p_last_update_login),
           USERENV('LANG'),
           DECODE( p_elmt_content, FND_API.G_MISS_CHAR, NULL, p_elmt_content)
   FROM fnd_languages l
   WHERE l.installed_flag IN ('I','B')
   AND   NOT EXISTS(SELECT NULL FROM pv_ge_qsnr_elements_tl t
                    WHERE t.qsnr_element_id = DECODE( px_qsnr_element_id, FND_API.G_MISS_NUM, NULL, px_qsnr_element_id)
                    AND   t.language = l.language_code);

Line: 129

END Insert_Row;

Line: 146

PROCEDURE Update_Row(
          p_qsnr_element_id    NUMBER,
          px_object_version_number   IN OUT NOCOPY NUMBER,
          p_arc_used_by_entity_code    VARCHAR2,
          p_used_by_entity_id    NUMBER,
          p_qsnr_elmt_seq_num    NUMBER,
          p_qsnr_elmt_type    VARCHAR2,
          p_entity_attr_id    NUMBER,
          p_qsnr_elmt_page_num    NUMBER,
          p_is_required_flag    VARCHAR2,
          p_last_updated_by    NUMBER,
          p_last_update_date    DATE,
          p_last_update_login    NUMBER,
          p_elmt_content    VARCHAR2
)

 IS
 BEGIN
    Update pv_ge_qsnr_elements_b
    SET
              qsnr_element_id = DECODE( p_qsnr_element_id, null, qsnr_element_id, FND_API.G_MISS_NUM, null, p_qsnr_element_id),
            --object_version_number = object_version_number + 1 ,
              object_version_number = DECODE( px_object_version_number, NULL, object_version_number, FND_API.g_miss_num, NULL, px_object_version_number+1),
              arc_used_by_entity_code = DECODE( p_arc_used_by_entity_code, null, arc_used_by_entity_code, FND_API.g_miss_char, null, p_arc_used_by_entity_code),
              used_by_entity_id = DECODE( p_used_by_entity_id, null, used_by_entity_id, FND_API.G_MISS_NUM, null, p_used_by_entity_id),
              qsnr_elmt_seq_num = DECODE( p_qsnr_elmt_seq_num, null, qsnr_elmt_seq_num, FND_API.G_MISS_NUM, null, p_qsnr_elmt_seq_num),
              qsnr_elmt_type = DECODE( p_qsnr_elmt_type, null, qsnr_elmt_type, FND_API.g_miss_char, null, p_qsnr_elmt_type),
              entity_attr_id = DECODE( p_entity_attr_id, null, entity_attr_id, FND_API.G_MISS_NUM, null, p_entity_attr_id),
              qsnr_elmt_page_num = DECODE( p_qsnr_elmt_page_num, null, qsnr_elmt_page_num, FND_API.G_MISS_NUM, null, p_qsnr_elmt_page_num),
              is_required_flag = DECODE( p_is_required_flag, null, is_required_flag, FND_API.g_miss_char, null, p_is_required_flag),
              last_updated_by = DECODE( p_last_updated_by, null, last_updated_by, FND_API.G_MISS_NUM, null, p_last_updated_by),
              last_update_date = DECODE( p_last_update_date, null, last_update_date, FND_API.G_MISS_DATE, null, p_last_update_date),
              last_update_login = DECODE( p_last_update_login, null, last_update_login, FND_API.G_MISS_NUM, null, p_last_update_login)
   WHERE qsnr_element_id = p_qsnr_element_id
   AND   object_version_number = px_object_version_number;

Line: 182

   UPDATE pv_ge_qsnr_elements_tl
   set elmt_content = DECODE( p_elmt_content, null, elmt_content, FND_API.g_miss_char, null, p_elmt_content),
       last_update_date   = DECODE( p_last_update_date, null, last_update_date, FND_API.G_MISS_DATE, null, p_last_update_date),
       last_updated_by   = DECODE( p_last_updated_by, null, last_updated_by, FND_API.G_MISS_NUM, null, p_last_updated_by),
       last_update_login   = DECODE( p_last_update_login, null, last_update_login, FND_API.G_MISS_NUM, null, p_last_update_login),
       source_lang = USERENV('LANG')
   WHERE qsnr_element_id = p_qsnr_element_id
   AND USERENV('LANG') IN (language, source_lang);

Line: 197

END Update_Row;

Line: 214

PROCEDURE Delete_Row(
    p_qsnr_element_id  NUMBER,
    p_object_version_number  NUMBER)
 IS
 BEGIN
   DELETE FROM pv_ge_qsnr_elements_b
    WHERE qsnr_element_id = p_qsnr_element_id
    AND object_version_number = p_object_version_number;

Line: 225

 END Delete_Row ;

Line: 248

        SELECT *
         FROM pv_ge_qsnr_elements_b
        WHERE qsnr_element_id =  p_qsnr_element_id
        AND object_version_number = p_object_version_number
        FOR UPDATE OF qsnr_element_id NOWAIT;

Line: 287

  INSERT /*+ append parallel(tt) */ INTO pv_ge_qsnr_elements_tl tt
  (
     qsnr_element_id,
     creation_date,
     created_by,
     last_update_date,
     last_updated_by,
     last_update_login,
     elmt_content,
     language,
     source_lang
  )
  SELECT /*+ parallel(v) parallel(t) use_nl(t)  */ v.*
  FROM
     (
         SELECT /*+ no_merge ordered parallel(b) */
         b.qsnr_element_id,
         b.creation_date,
         b.created_by,
         b.last_update_date,
         b.last_updated_by,
         b.last_update_login,
         b.elmt_content,
         l.language_code,
         b.source_lang
         FROM  pv_ge_qsnr_elements_tl B , FND_LANGUAGES L
         WHERE L.INSTALLED_FLAG IN ( 'I','B' ) AND B.LANGUAGE = USERENV ( 'LANG' )
     ) v
     , pv_ge_qsnr_elements_tl t
     WHERE t.qsnr_element_id(+) = v.qsnr_element_id
     AND t.language(+) = v.language_code
     AND t.qsnr_element_id IS NULL ;

Line: 335

procedure INSERT_ROW (
  X_ROWID in out NOCOPY VARCHAR2,
  X_QSNR_ELEMENT_ID in NUMBER,
  X_OBJECT_VERSION_NUMBER in NUMBER,
  X_ARC_USED_BY_ENTITY_CODE in VARCHAR2,
  X_USED_BY_ENTITY_ID in NUMBER,
  X_QSNR_ELMT_SEQ_NUM in NUMBER,
  X_QSNR_ELMT_TYPE in VARCHAR2,
  X_ENTITY_ATTR_ID in NUMBER,
  X_QSNR_ELMT_PAGE_NUM in NUMBER,
  X_IS_REQUIRED_FLAG in VARCHAR2,
  X_ELMT_CONTENT in VARCHAR2,
  X_CREATION_DATE in DATE,
  X_CREATED_BY in NUMBER,
  X_LAST_UPDATE_DATE in DATE,
  X_LAST_UPDATED_BY in NUMBER,
  X_LAST_UPDATE_LOGIN in NUMBER
) is
  cursor C is select ROWID from PV_GE_QSNR_ELEMENTS_B
    where QSNR_ELEMENT_ID = X_QSNR_ELEMENT_ID
    ;

Line: 357

  insert into PV_GE_QSNR_ELEMENTS_B (
    QSNR_ELEMENT_ID,
    OBJECT_VERSION_NUMBER,
    ARC_USED_BY_ENTITY_CODE,
    USED_BY_ENTITY_ID,
    QSNR_ELMT_SEQ_NUM,
    QSNR_ELMT_TYPE,
    ENTITY_ATTR_ID,
    QSNR_ELMT_PAGE_NUM,
    IS_REQUIRED_FLAG,
    CREATION_DATE,
    CREATED_BY,
    LAST_UPDATE_DATE,
    LAST_UPDATED_BY,
    LAST_UPDATE_LOGIN
  ) values (
    X_QSNR_ELEMENT_ID,
    X_OBJECT_VERSION_NUMBER,
    X_ARC_USED_BY_ENTITY_CODE,
    X_USED_BY_ENTITY_ID,
    X_QSNR_ELMT_SEQ_NUM,
    X_QSNR_ELMT_TYPE,
    X_ENTITY_ATTR_ID,
    X_QSNR_ELMT_PAGE_NUM,
    X_IS_REQUIRED_FLAG,
    X_CREATION_DATE,
    X_CREATED_BY,
    X_LAST_UPDATE_DATE,
    X_LAST_UPDATED_BY,
    X_LAST_UPDATE_LOGIN
  );

Line: 389

  insert into PV_GE_QSNR_ELEMENTS_TL (
    QSNR_ELEMENT_ID,
    ELMT_CONTENT,
    CREATED_BY,
    CREATION_DATE,
    LAST_UPDATED_BY,
    LAST_UPDATE_DATE,
    LAST_UPDATE_LOGIN,
    LANGUAGE,
    SOURCE_LANG
  ) select
    X_QSNR_ELEMENT_ID,
    X_ELMT_CONTENT,
    X_CREATED_BY,
    X_CREATION_DATE,
    X_LAST_UPDATED_BY,
    X_LAST_UPDATE_DATE,
    X_LAST_UPDATE_LOGIN,
    L.LANGUAGE_CODE,
    userenv('LANG')
  from FND_LANGUAGES L
  where L.INSTALLED_FLAG in ('I', 'B')
  and not exists
    (select NULL
    from PV_GE_QSNR_ELEMENTS_TL T
    where T.QSNR_ELEMENT_ID = X_QSNR_ELEMENT_ID
    and T.LANGUAGE = L.LANGUAGE_CODE);

Line: 425

end INSERT_ROW;

Line: 439

  cursor c is select
      OBJECT_VERSION_NUMBER,
      ARC_USED_BY_ENTITY_CODE,
      USED_BY_ENTITY_ID,
      QSNR_ELMT_SEQ_NUM,
      QSNR_ELMT_TYPE,
      ENTITY_ATTR_ID,
      QSNR_ELMT_PAGE_NUM,
      IS_REQUIRED_FLAG
    from PV_GE_QSNR_ELEMENTS_B
    where QSNR_ELEMENT_ID = X_QSNR_ELEMENT_ID
    for update of QSNR_ELEMENT_ID nowait;

Line: 453

  cursor c1 is select
      ELMT_CONTENT,
      decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
    from PV_GE_QSNR_ELEMENTS_TL
    where QSNR_ELEMENT_ID = X_QSNR_ELEMENT_ID
    and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
    for update of QSNR_ELEMENT_ID nowait;

Line: 465

    fnd_message.set_name('FND', 'FORM_RECORD_DELETED');

Line: 500

procedure UPDATE_ROW (
  X_QSNR_ELEMENT_ID in NUMBER,
  X_OBJECT_VERSION_NUMBER in NUMBER,
  X_ARC_USED_BY_ENTITY_CODE in VARCHAR2,
  X_USED_BY_ENTITY_ID in NUMBER,
  X_QSNR_ELMT_SEQ_NUM in NUMBER,
  X_QSNR_ELMT_TYPE in VARCHAR2,
  X_ENTITY_ATTR_ID in NUMBER,
  X_QSNR_ELMT_PAGE_NUM in NUMBER,
  X_IS_REQUIRED_FLAG in VARCHAR2,
  X_ELMT_CONTENT in VARCHAR2,
  X_LAST_UPDATE_DATE in DATE,
  X_LAST_UPDATED_BY in NUMBER,
  X_LAST_UPDATE_LOGIN in NUMBER
) is
begin
  update PV_GE_QSNR_ELEMENTS_B set
    OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
    ARC_USED_BY_ENTITY_CODE = X_ARC_USED_BY_ENTITY_CODE,
    USED_BY_ENTITY_ID = X_USED_BY_ENTITY_ID,
    QSNR_ELMT_SEQ_NUM = X_QSNR_ELMT_SEQ_NUM,
    QSNR_ELMT_TYPE = X_QSNR_ELMT_TYPE,
    ENTITY_ATTR_ID = X_ENTITY_ATTR_ID,
    QSNR_ELMT_PAGE_NUM = X_QSNR_ELMT_PAGE_NUM,
    IS_REQUIRED_FLAG = X_IS_REQUIRED_FLAG,
    LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
    LAST_UPDATED_BY = X_LAST_UPDATED_BY,
    LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
  where QSNR_ELEMENT_ID = X_QSNR_ELEMENT_ID;

Line: 534

  update PV_GE_QSNR_ELEMENTS_TL set
    ELMT_CONTENT = X_ELMT_CONTENT,
    LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
    LAST_UPDATED_BY = X_LAST_UPDATED_BY,
    LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
    SOURCE_LANG = userenv('LANG')
  where QSNR_ELEMENT_ID = X_QSNR_ELEMENT_ID
  and userenv('LANG') in (LANGUAGE, SOURCE_LANG);

Line: 546

end UPDATE_ROW;

Line: 548

procedure DELETE_ROW (
  X_QSNR_ELEMENT_ID in NUMBER
) is
begin
  delete from PV_GE_QSNR_ELEMENTS_TL
  where QSNR_ELEMENT_ID = X_QSNR_ELEMENT_ID;

Line: 559

  delete from PV_GE_QSNR_ELEMENTS_B
  where QSNR_ELEMENT_ID = X_QSNR_ELEMENT_ID;

Line: 565

end DELETE_ROW;