APPS.PV_GQ_ELEMENTS_PVT SQL Statements

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

Line: 110

      SELECT pv_ge_qsnr_elements_b_s.NEXTVAL
      FROM dual;

Line: 114

      SELECT 1
      FROM PV_GE_QSNR_ELEMENTS_B
      WHERE qsnr_element_id = l_id;

Line: 190

           l_qsnr_element_rec.last_update_date      := SYSDATE;

Line: 191

           l_qsnr_element_rec.last_updated_by       := FND_GLOBAL.user_id;

Line: 194

           l_qsnr_element_rec.last_update_login     := FND_GLOBAL.conc_login_id;

Line: 222

      Pv_Gq_Elements_Pkg.Insert_Row(
          px_qsnr_element_id         => l_qsnr_element_rec.qsnr_element_id,
          px_object_version_number   => l_qsnr_element_rec.object_version_number,
          p_arc_used_by_entity_code  => l_qsnr_element_rec.arc_used_by_entity_code,
          p_used_by_entity_id        => l_qsnr_element_rec.used_by_entity_id,
          p_qsnr_elmt_seq_num        => l_qsnr_element_rec.qsnr_elmt_seq_num,
          p_qsnr_elmt_type           => l_qsnr_element_rec.qsnr_elmt_type,
          p_entity_attr_id           => l_qsnr_element_rec.entity_attr_id,
          p_qsnr_elmt_page_num       => l_qsnr_element_rec.qsnr_elmt_page_num,
          p_is_required_flag         => l_qsnr_element_rec.is_required_flag,
          p_created_by               => FND_GLOBAL.USER_ID,
          p_creation_date            => SYSDATE,
          p_last_updated_by          => FND_GLOBAL.USER_ID,
          p_last_update_date         => SYSDATE,
          p_last_update_login        => FND_GLOBAL.conc_login_id,
          p_elmt_content             => l_qsnr_element_rec.elmt_content
      );

Line: 334

       SELECT  qsnr_elmt_seq_num, qsnr_elmt_type, entity_attr_id, qsnr_elmt_page_num, is_required_flag
       FROM    pv_ge_qsnr_elements_b
       WHERE   arc_used_by_entity_code = 'PRGM' AND used_by_entity_id = cv_program_id
       order by qsnr_element_id;

Line: 340

      SELECT  tl.qsnr_element_id, elmt_content, source_lang, language
      FROM    pv_ge_qsnr_elements_b b, pv_ge_qsnr_elements_tl tl
      WHERE   b.qsnr_element_id = tl.qsnr_element_id
	  AND     b.arc_used_by_entity_code = 'PRGM'
	  AND 	  b.used_by_entity_id = cv_program_id
	  order by tl.qsnr_element_id;

Line: 348

      SELECT pv_ge_qsnr_elements_b_s.NEXTVAL
      FROM dual;

Line: 352

      SELECT 1
      FROM PV_GE_QSNR_ELEMENTS_B
      WHERE qsnr_element_id = l_id;

Line: 357

      SELECT  QSNR_TITLE, QSNR_HEADER, QSNR_FOOTER, language
      FROM    pv_partner_program_tl tl
      WHERE   program_id = cv_program_id;

Line: 460

      PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' : insert into b table');

Line: 463

      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
	   (
           qsnr_element_id_array(i),
           1,
           'PRGM',
           p_tar_object_id,
           qsnr_elmt_seq_num_array(i),
           qsnr_elmt_type_array(i),
           entity_attr_id_array(i),
           qsnr_elmt_page_num_array(i),
           is_required_flag_array(i),
           FND_GLOBAL.USER_ID,
           SYSDATE,
           FND_GLOBAL.USER_ID,
           SYSDATE,
           FND_GLOBAL.CONC_LOGIN_ID
	   );

Line: 522

      INSERT INTO pv_ge_qsnr_elements_tl(
           qsnr_element_id ,
           language ,
           last_update_date ,
           last_updated_by ,
           creation_date ,
           created_by ,
           last_update_login ,
           source_lang ,
           elmt_content
      )
      values
      (
           new_qsnr_element_id_array(j),
           language_array(j),
           SYSDATE,
           FND_GLOBAL.USER_ID,
           SYSDATE,
           FND_GLOBAL.USER_ID,
           FND_GLOBAL.CONC_LOGIN_ID,
           source_lang_array(j),
           elmt_content_array(j)
     );

Line: 563

	update pv_partner_program_tl
	set
        QSNR_TITLE = QSNR_TITLE_ARRAY(i),
        QSNR_HEADER = QSNR_HEADER_ARRAY(i),
        QSNR_FOOTER = QSNR_FOOTER_ARRAY(i),
	LAST_UPDATED_BY = FND_GLOBAL.user_id,
	LAST_UPDATE_DATE = sysdate,
	LAST_UPDATE_LOGIN = FND_GLOBAL.conc_login_id
	where program_id = p_tar_object_id
	and   LANGUAGE = PRGM_LANGUAGE_ARRAY(i);

Line: 667

PROCEDURE Update_Gq_Elements(
    p_api_version_number         IN   NUMBER,
    p_init_msg_list              IN   VARCHAR2     := FND_API.G_FALSE,
    p_commit                     IN   VARCHAR2     := FND_API.G_FALSE,
    p_validation_level           IN  NUMBER       := FND_API.G_VALID_LEVEL_FULL,

    x_return_status              OUT NOCOPY  VARCHAR2,
    x_msg_count                  OUT NOCOPY  NUMBER,
    x_msg_data                   OUT NOCOPY  VARCHAR2,

    p_qsnr_element_rec               IN    qsnr_element_rec_type,
    x_object_version_number      OUT NOCOPY  NUMBER
    )

 IS


CURSOR c_get_gq_elements(qsnr_element_id NUMBER) IS
    SELECT *
    FROM  PV_GE_QSNR_ELEMENTS_B
    WHERE  qsnr_element_id = p_qsnr_element_rec.qsnr_element_id;

Line: 691

L_API_NAME                  CONSTANT VARCHAR2(30) := 'Update_Gq_Elements';

Line: 703

      SAVEPOINT update_gq_elements_pvt;

Line: 737

      PVX_UTILITY_PVT.debug_message('Private API: - Open Cursor to Select');

Line: 745

  PVX_Utility_PVT.Error_Message(p_message_name => 'API_MISSING_UPDATE_TARGET',
   p_token_name   => 'INFO',
 p_token_value  => 'Gq_Elements') ;

Line: 796

            p_validation_mode => JTF_PLSQL_API.g_update,
            p_qsnr_element_rec  =>  p_qsnr_element_rec,
            x_return_status    => x_return_status,
            x_msg_count        => x_msg_count,
            x_msg_data         => x_msg_data);

Line: 809

         PVX_UTILITY_PVT.debug_message('Private API: Calling update table handler');

Line: 845

      Pv_Gq_Elements_Pkg.Update_Row(
          p_qsnr_element_id         => p_qsnr_element_rec.qsnr_element_id,
          px_object_version_number  => l_object_version_number,
          p_arc_used_by_entity_code => p_qsnr_element_rec.arc_used_by_entity_code,
          p_used_by_entity_id       => p_qsnr_element_rec.used_by_entity_id,
          p_qsnr_elmt_seq_num       => p_qsnr_element_rec.qsnr_elmt_seq_num,
          p_qsnr_elmt_type          => p_qsnr_element_rec.qsnr_elmt_type,
          p_entity_attr_id          => p_qsnr_element_rec.entity_attr_id,
          p_qsnr_elmt_page_num      => p_qsnr_element_rec.qsnr_elmt_page_num,
          p_is_required_flag        => p_qsnr_element_rec.is_required_flag,
          p_last_updated_by         => FND_GLOBAL.USER_ID,
          p_last_update_date        => SYSDATE,
          p_last_update_login       => FND_GLOBAL.conc_login_id,
          p_elmt_content            => p_qsnr_element_rec.elmt_content
);

Line: 891

     ROLLBACK TO UPDATE_Gq_Elements_PVT;

Line: 901

     ROLLBACK TO UPDATE_Gq_Elements_PVT;

Line: 911

     ROLLBACK TO UPDATE_Gq_Elements_PVT;

Line: 923

End Update_Gq_Elements;

Line: 960

PROCEDURE Delete_Gq_Elements(
    p_api_version_number         IN   NUMBER,
    p_init_msg_list              IN   VARCHAR2     := FND_API.G_FALSE,
    p_commit                     IN   VARCHAR2     := FND_API.G_FALSE,
    p_validation_level           IN   NUMBER       := FND_API.G_VALID_LEVEL_FULL,
    x_return_status              OUT NOCOPY  VARCHAR2,
    x_msg_count                  OUT NOCOPY  NUMBER,
    x_msg_data                   OUT NOCOPY  VARCHAR2,
    p_qsnr_element_id                   IN  NUMBER,
    p_object_version_number      IN   NUMBER
    )

 IS

CURSOR c_get_elmt_greater_than (cv_qsnr_element_id NUMBER) IS
   select gq.qsnr_element_id, gq.qsnr_elmt_seq_num, gq.qsnr_elmt_page_num, gq.object_version_number
   from pv_ge_qsnr_elements_vl gq,
        (select arc_used_by_entity_code, used_by_entity_id, qsnr_elmt_seq_num
         from pv_ge_qsnr_elements_vl
         where qsnr_element_id = cv_qsnr_element_id) tmp
   where gq.arc_used_by_entity_code = tmp.arc_used_by_entity_code
   and   gq.used_by_entity_id = tmp.used_by_entity_id
   and   gq.qsnr_elmt_seq_num > tmp.qsnr_elmt_seq_num;

Line: 984

L_API_NAME                  CONSTANT VARCHAR2(30) := 'Delete_Gq_Elements';

Line: 992

      SAVEPOINT delete_gq_elements_pvt;

Line: 1047

         Update_Gq_Elements ( p_api_version_number         => p_api_version_number
                             ,p_init_msg_list              => p_init_msg_list
                             ,p_commit                     => p_commit
                             ,p_validation_level           => p_validation_level
                             ,x_return_status              => x_return_status
                             ,x_msg_count                  => x_msg_count
                             ,x_msg_data                   => x_msg_data
                             ,p_qsnr_element_rec           => l_qsnr_element_rec
                             ,x_object_version_number      => l_object_version_number
                            );

Line: 1063

      PVX_UTILITY_PVT.debug_message( 'Private API: Calling delete table handler');

Line: 1067

      Pv_Gq_Elements_Pkg.Delete_Row(
          p_qsnr_element_id  => p_qsnr_element_id,
          p_object_version_number => p_object_version_number     );

Line: 1101

     ROLLBACK TO DELETE_Gq_Elements_PVT;

Line: 1111

     ROLLBACK TO DELETE_Gq_Elements_PVT;

Line: 1121

     ROLLBACK TO DELETE_Gq_Elements_PVT;

Line: 1133

End Delete_Gq_Elements;

Line: 1170

PROCEDURE Delete_Gq_PB_Elements(
    p_api_version_number         IN   NUMBER,
    p_init_msg_list              IN   VARCHAR2     := FND_API.G_FALSE,
    p_commit                     IN   VARCHAR2     := FND_API.G_FALSE,
    p_validation_level           IN   NUMBER       := FND_API.G_VALID_LEVEL_FULL,
    x_return_status              OUT NOCOPY  VARCHAR2,
    x_msg_count                  OUT NOCOPY  NUMBER,
    x_msg_data                   OUT NOCOPY  VARCHAR2,
    p_qsnr_element_id            IN  NUMBER,
    p_object_version_number      IN   NUMBER
    )

IS

CURSOR c_get_elmt_greater_than (cv_qsnr_element_id NUMBER) IS
   select gq.qsnr_element_id, gq.qsnr_elmt_seq_num, gq.qsnr_elmt_page_num, gq.object_version_number
   from pv_ge_qsnr_elements_vl gq,
        (select arc_used_by_entity_code, used_by_entity_id, qsnr_elmt_seq_num
         from pv_ge_qsnr_elements_vl
         where qsnr_element_id = cv_qsnr_element_id) tmp
   where gq.arc_used_by_entity_code = tmp.arc_used_by_entity_code
   and   gq.used_by_entity_id = tmp.used_by_entity_id
   and   gq.qsnr_elmt_seq_num > tmp.qsnr_elmt_seq_num;

Line: 1194

L_API_NAME                  CONSTANT VARCHAR2(30) := 'Delete_Gq_PB_Elements';

Line: 1202

      SAVEPOINT Delete_Gq_PB_Elements_pvt;

Line: 1275

         Update_Gq_Elements ( p_api_version_number         => p_api_version_number
                             ,p_init_msg_list              => p_init_msg_list
                             ,p_commit                     => p_commit
                             ,p_validation_level           => p_validation_level
                             ,x_return_status              => x_return_status
                             ,x_msg_count                  => x_msg_count
                             ,x_msg_data                   => x_msg_data
                             ,p_qsnr_element_rec           => l_qsnr_element_rec
                             ,x_object_version_number      => l_object_version_number
                            );

Line: 1292

      PVX_UTILITY_PVT.debug_message( 'Private API: Calling delete table handler...');

Line: 1296

      Pv_Gq_Elements_Pkg.Delete_Row(
          p_qsnr_element_id  => p_qsnr_element_id,
          p_object_version_number => p_object_version_number     );

Line: 1329

     ROLLBACK TO Delete_Gq_PB_Elements_PVT;

Line: 1339

     ROLLBACK TO Delete_Gq_PB_Elements_PVT;

Line: 1349

     ROLLBACK TO Delete_Gq_PB_Elements_PVT;

Line: 1361

End Delete_Gq_PB_Elements;

Line: 1539

        ELSIF p_validation_mode = JTF_PLSQL_API.g_update THEN
            l_valid_flag := PVX_Utility_PVT.check_uniqueness(
            'pv_ge_qsnr_elements_b',
            'used_by_entity_id = ''' || p_qsnr_element_rec.used_by_entity_id ||''' AND entity_attr_id = ''' || p_qsnr_element_rec.entity_attr_id || ''' AND QSNR_ELEMENT_ID <> ''' || p_qsnr_element_rec.QSNR_ELEMENT_ID || '''');

Line: 1627

      IF p_qsnr_element_rec.last_updated_by = FND_API.G_MISS_NUM OR p_qsnr_element_rec.last_updated_by IS NULL THEN
               PVX_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD', 'LAST_UPDATED_BY' );

Line: 1633

      IF p_qsnr_element_rec.last_update_date = FND_API.G_MISS_DATE OR p_qsnr_element_rec.last_update_date IS NULL THEN
               PVX_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD', 'LAST_UPDATE_DATE' );

Line: 1702

      IF p_qsnr_element_rec.last_updated_by = FND_API.G_MISS_NUM THEN
               PVX_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD', 'LAST_UPDATED_BY' );

Line: 1708

      IF p_qsnr_element_rec.last_update_date = FND_API.G_MISS_DATE THEN
               PVX_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD', 'LAST_UPDATE_DATE' );

Line: 1844

      SELECT *
      FROM pv_ge_qsnr_elements_b
      WHERE qsnr_element_id = p_qsnr_element_rec.qsnr_element_id;

Line: 1912

   IF p_qsnr_element_rec.last_updated_by IS NULL THEN
      x_complete_rec.last_updated_by := l_qsnr_element_rec.last_updated_by;

Line: 1917

   IF p_qsnr_element_rec.last_update_date IS NULL THEN
      x_complete_rec.last_update_date := l_qsnr_element_rec.last_update_date;

Line: 1922

   IF p_qsnr_element_rec.last_update_login IS NULL THEN
      x_complete_rec.last_update_login := l_qsnr_element_rec.last_update_login;

Line: 2210

      select arc_used_by_entity_code, used_by_entity_id, qsnr_elmt_seq_num
      from pv_ge_qsnr_elements_vl
      where qsnr_element_id = cv_qsnr_element_id;

Line: 2217

      SELECT *
      FROM PV_GE_QSNR_ELEMENTS_VL
      WHERE qsnr_elmt_seq_num = cv_qsnr_elmt_seq_num
            AND arc_used_by_entity_code = cv_arc_used_by_entity_code
            AND used_by_entity_id = cv_used_by_entity_id;

Line: 2346

         Update_Gq_Elements( p_api_version_number     => 1.0,
                             p_init_msg_list          => FND_API.G_FALSE,
                             p_validation_level       => p_validation_level,
                             p_commit                 => p_commit,
                             x_return_status          => x_return_status,
                             x_msg_count              => x_msg_count,
                             x_msg_data               => x_msg_data,
                             p_qsnr_element_rec       => l_current_qsnr_element_rec,
                             x_object_version_number  => l_object_version_number
                             );

Line: 2367

         Update_Gq_Elements( p_api_version_number     => 1.0,
                             p_init_msg_list          => FND_API.G_FALSE,
                             p_validation_level       => p_validation_level,
                             p_commit                 => p_commit,
                             x_return_status          => x_return_status,
                             x_msg_count              => x_msg_count,
                             x_msg_data               => x_msg_data,
                             p_qsnr_element_rec       => l_up_qsnr_element_rec,
                             x_object_version_number  => l_object_version_number
                             );

Line: 2412

         Update_Gq_Elements( p_api_version_number     => 1.0,
                             p_init_msg_list          => FND_API.G_FALSE,
                             p_validation_level       => p_validation_level,
                             p_commit                 => p_commit,
                             x_return_status          => x_return_status,
                             x_msg_count              => x_msg_count,
                             x_msg_data               => x_msg_data,
                             p_qsnr_element_rec       => l_current_qsnr_element_rec,
                             x_object_version_number  => l_object_version_number
                             );

Line: 2433

         Update_Gq_Elements( p_api_version_number     => 1.0,
                             p_init_msg_list          => FND_API.G_FALSE,
                             p_validation_level       => p_validation_level,
                             p_commit                 => p_commit,
                             x_return_status          => x_return_status,
                             x_msg_count              => x_msg_count,
                             x_msg_data               => x_msg_data,
                             p_qsnr_element_rec       => l_up_qsnr_element_rec,
                             x_object_version_number  => l_object_version_number
                             );

Line: 2491

         Update_Gq_Elements( p_api_version_number     => 1.0,
                             p_init_msg_list          => FND_API.G_FALSE,
                             p_validation_level       => p_validation_level,
                             p_commit                 => p_commit,
                             x_return_status          => x_return_status,
                             x_msg_count              => x_msg_count,
                             x_msg_data               => x_msg_data,
                             p_qsnr_element_rec       => l_current_qsnr_element_rec,
                             x_object_version_number  => l_object_version_number
                             );

Line: 2508

         Update_Gq_Elements( p_api_version_number     => 1.0,
                             p_init_msg_list          => FND_API.G_FALSE,
                             p_validation_level       => p_validation_level,
                             p_commit                 => p_commit,
                             x_return_status          => x_return_status,
                             x_msg_count              => x_msg_count,
                             x_msg_data               => x_msg_data,
                             p_qsnr_element_rec       => l_up_qsnr_element_rec,
                             x_object_version_number  => l_object_version_number
                             );