APPS.OKC_K_ARTICLES_GRP SQL Statements

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

Line: 29

  G_AMEND_CODE_UPDATED         CONSTANT   varchar2(30) := 'UPDATED';

Line: 31

  G_AMEND_CODE_DELETED         CONSTANT   varchar2(30) := 'DELETED';

Line: 98

   SELECT VAR.VARIABLE_CODE, BUSVAR.VARIABLE_TYPE, BUSVAR.EXTERNAL_YN, BUSVAR.VALUE_SET_ID
   FROM OKC_ARTICLE_VARIABLES VAR,
        OKC_BUS_VARIABLES_B BUSVAR
   WHERE VAR.ARTICLE_VERSION_ID = b_article_version_id
   AND BUSVAR.VARIABLE_CODE=VAR.VARIABLE_CODE;

Line: 104

   CURSOR l_get_deleted_rec_csr IS
   SELECT id FROM okc_k_articles_b
   WHERE  document_type=p_document_type
   AND    document_id  =p_document_id
   AND   ( sav_sae_id  = p_sav_sae_id
          OR sav_sae_id=p_ref_article_id
          OR ref_article_id=p_sav_sae_id
          OR ref_article_id = p_ref_article_id)
  AND   ( amendment_operation_code=G_AMEND_CODE_DELETED OR summary_amend_operation_code=G_AMEND_CODE_DELETED) ;

Line: 136

       OPEN  l_get_deleted_rec_csr;

Line: 137

       FETCH l_get_deleted_rec_csr INTO l_del_cat_id;

Line: 138

       CLOSE l_get_deleted_rec_csr;

Line: 141

                l_amendment_operation_code:=G_AMEND_CODE_UPDATED;

Line: 143

                l_summary_amend_operation_code:=G_AMEND_CODE_UPDATED;

Line: 159

      OKC_K_ARTICLES_GRP.delete_article(
                       p_api_version  => 1,
                       p_init_msg_list => FND_API.G_FALSE,
                       p_validate_commit => FND_API.G_FALSE,
                       p_validation_string => NULL,
                       p_commit            => FND_API.G_FALSE,
                       p_mode              => 'NORMAL',
                       x_return_status     => x_return_status,
                       x_msg_count         => x_msg_count,
                       x_msg_data          => x_msg_data,
                       p_super_user_yn     => 'N',
                       p_amendment_description  => NULL,
                       p_id                     => l_del_cat_id,
                       p_object_version_number  => NULL
              );

Line: 187

    OKC_K_ARTICLES_PVT.insert_row(
      p_validation_level           => p_validation_level,
      x_return_status              => x_return_status,
      p_id                         => p_id,
      p_sav_sae_id                 => p_sav_sae_id,
      p_cat_type                   => p_cat_type, --Bug 3341342
      p_document_type              => p_document_type,
      p_document_id                => p_document_id,
      p_cle_id                     => p_cle_id,
      p_source_flag                => p_source_flag,
      p_mandatory_yn               => p_mandatory_yn,
      p_scn_id                     => p_scn_id,
      p_label                      => p_label,
      p_amendment_description      => l_amendment_description,
      p_amendment_operation_code   => l_amendment_operation_code,
      p_summary_amend_operation_code => l_summary_amend_operation_code,
      p_article_version_id         => p_article_version_id,
      p_change_nonstd_yn           => p_change_nonstd_yn,
      p_orig_system_reference_code => p_orig_system_reference_code,
      p_orig_system_reference_id1  => p_orig_system_reference_id1,
      p_orig_system_reference_id2  => p_orig_system_reference_id2,
      p_display_sequence           => p_display_sequence,
      p_attribute_category         => p_attribute_category,
      p_attribute1                 => p_attribute1,
      p_attribute2                 => p_attribute2,
      p_attribute3                 => p_attribute3,
      p_attribute4                 => p_attribute4,
      p_attribute5                 => p_attribute5,
      p_attribute6                 => p_attribute6,
      p_attribute7                 => p_attribute7,
      p_attribute8                 => p_attribute8,
      p_attribute9                 => p_attribute9,
      p_attribute10                => p_attribute10,
      p_attribute11                => p_attribute11,
      p_attribute12                => p_attribute12,
      p_attribute13                => p_attribute13,
      p_attribute14                => p_attribute14,
      p_attribute15                => p_attribute15,
      p_print_text_yn              => p_print_text_yn,
      p_ref_article_id             => p_ref_article_id,
      p_ref_article_version_id     => p_ref_article_version_id,
      x_id                         => x_id
    );

Line: 244

                   OKC_K_ART_VARIABLES_PVT.insert_row(
                                           p_validation_level       => p_validation_level,
                                           x_return_status          => x_return_status,
                                           p_cat_id                 => x_id,
                                           p_variable_code          => l_article_var_rec.variable_code,
                                           p_variable_type          => l_article_var_rec.variable_type,
                                           p_external_yn            => l_article_var_rec.external_yn,
                                           p_variable_value_id      => NULL,
                                           p_variable_value         => NULL,
                                           p_attribute_value_set_id => l_article_var_rec.value_set_id,
                                           x_cat_id                 => l_cat_id,
                                           x_variable_code          => l_variable_code
                                         );

Line: 577

  PROCEDURE update_article(
    p_api_version                IN NUMBER,
    p_init_msg_list              IN VARCHAR2 ,
    p_validation_level	         IN NUMBER,
    p_validate_commit            IN VARCHAR2,
    p_validation_string          IN VARCHAR2,
    p_commit                     IN VARCHAR2,
    p_mode                       IN VARCHAR2,
    x_return_status              OUT NOCOPY VARCHAR2,
    x_msg_count                  OUT NOCOPY NUMBER,
    x_msg_data                   OUT NOCOPY VARCHAR2,
    p_id                         IN NUMBER,
    p_sav_sae_id                 IN NUMBER,
    p_document_type              IN VARCHAR2,
    p_document_id                IN NUMBER,
    p_cle_id                     IN NUMBER,
    p_source_flag                IN VARCHAR2,
    p_mandatory_yn               IN VARCHAR2,
    p_scn_id                     IN NUMBER,
    p_label                      IN VARCHAR2,
    p_amendment_description      IN VARCHAR2,
    p_article_version_id         IN NUMBER,
    p_change_nonstd_yn           IN VARCHAR2,
    p_orig_system_reference_code IN VARCHAR2,
    p_orig_system_reference_id1  IN NUMBER,
    p_orig_system_reference_id2  IN NUMBER,
    p_display_sequence           IN NUMBER,
    p_attribute_category         IN VARCHAR2 ,
    p_attribute1                 IN VARCHAR2 ,
    p_attribute2                 IN VARCHAR2 ,
    p_attribute3                 IN VARCHAR2 ,
    p_attribute4                 IN VARCHAR2 ,
    p_attribute5                 IN VARCHAR2 ,
    p_attribute6                 IN VARCHAR2 ,
    p_attribute7                 IN VARCHAR2 ,
    p_attribute8                 IN VARCHAR2 ,
    p_attribute9                 IN VARCHAR2 ,
    p_attribute10                IN VARCHAR2 ,
    p_attribute11                IN VARCHAR2 ,
    p_attribute12                IN VARCHAR2 ,
    p_attribute13                IN VARCHAR2 ,
    p_attribute14                IN VARCHAR2 ,
    p_attribute15                IN VARCHAR2 ,
    p_print_text_yn              IN VARCHAR2 ,
    p_ref_article_id               IN NUMBER ,
    p_ref_article_version_id       IN NUMBER ,
    p_object_version_number        IN NUMBER

   ) IS

    l_api_version                  CONSTANT NUMBER := 1;

Line: 628

    l_api_name                     CONSTANT VARCHAR2(30) := 'g_update_article';

Line: 645

    SELECT DOCUMENT_ID,DOCUMENT_TYPE
    FROM OKC_K_ARTIClES_B
    WHERE ID=P_ID;

Line: 650

    SELECT ARTICLE_VERSION_ID,standard_yn
    FROM OKC_K_ARTIClES_B kart,okc_articles_all art
    WHERE ID=P_ID
    AND   art.article_id=kart.sav_sae_id;

Line: 656

    SELECT KART.ID CAT_ID,
           VAR.VARIABLE_CODE,
           BUSVAR.VARIABLE_TYPE,
           BUSVAR.EXTERNAL_YN,
           BUSVAR.VALUE_SET_ID,
           NULL VARIABLE_VALUE,
           NULL VARIABLE_VALUE_ID
   FROM  OKC_ARTICLE_VARIABLES VAR,
         OKC_K_ARTICLES_B KART,
         OKC_BUS_VARIABLES_B BUSVAR
   WHERE KART.ARTICLE_VERSION_ID=VAR.ARTICLE_VERSION_ID
   AND KART.ID = P_ID
   AND BUSVAR.VARIABLE_CODE=VAR.VARIABLE_CODE AND NOT EXISTS
   ( SELECT 'X' FROM OKC_K_ART_VARIABLES
     WHERE CAT_ID=KART.ID
     AND VARIABLE_CODE = BUSVAR.VARIABLE_CODE);

Line: 673

    CURSOR l_update_variable_csr is
    SELECT KART.ID CAT_ID,
           VAR.VARIABLE_CODE,
           BUSVAR.VARIABLE_TYPE,
           BUSVAR.EXTERNAL_YN,
           BUSVAR.VALUE_SET_ID,
           ARTVAR.VARIABLE_VALUE,
           ARTVAR.VARIABLE_VALUE_ID,
           ARTVAR.OBJECT_VERSION_NUMBER
   FROM  OKC_ARTICLE_VARIABLES VAR,
         OKC_K_ARTICLES_B KART,
         OKC_BUS_VARIABLES_B BUSVAR,
         OKC_K_ART_VARIABLES ARTVAR
   WHERE KART.ARTICLE_VERSION_ID=VAR.ARTICLE_VERSION_ID
   AND BUSVAR.VARIABLE_CODE=VAR.VARIABLE_CODE
   AND ARTVAR.CAT_ID = KART.ID
   AND ARTVAR.VARIABLE_CODE = VAR.VARIABLE_CODE
   AND KART.ID = P_ID
   AND ARTVAR.VARIABLE_VALUE IS NULL;

Line: 693

    CURSOR l_delete_variable_csr is
    SELECT ARTVAR.CAT_ID,
           ARTVAR.VARIABLE_CODE,
           ARTVAR.OBJECT_VERSION_NUMBER
    FROM OKC_K_ART_VARIABLES ARTVAR,
         OKC_K_ARTICLES_B KART
    WHERE KART.ID = ARTVAR.CAT_ID
    AND   KART.ID = P_ID
    AND   NOT EXISTS ( SELECT 'X' FROM  OKC_ARTICLE_VARIABLES VAR
                      WHERE VAR.ARTICLE_VERSION_ID = KART.ARTICLE_VERSION_ID
                      AND   VAR.VARIABLE_CODE = ARTVAR.VARIABLE_CODE );

Line: 706

    SELECT SUMMARY_AMEND_OPERATION_CODE ,amendment_operation_code
    FROM OKC_K_ARTICLES_B
    WHERE ID=P_ID;

Line: 714

       okc_debug.log('1600: Entered update_article', 2);

Line: 718

    SAVEPOINT g_update_article_grp;

Line: 775

        l_amendment_operation_code := nvl(l_existing_operation_code,G_AMEND_CODE_UPDATED);

Line: 779

   p_amend_operation_code=>G_AMEND_CODE_UPDATED);

Line: 789

    OKC_K_ARTICLES_PVT.Update_Row(
      p_validation_level           => p_validation_level,
      x_return_status              => x_return_status,
      p_id                         => p_id,
      p_sav_sae_id                 => p_sav_sae_id,
      p_document_type              => p_document_type,
      p_document_id                => p_document_id,
      p_cle_id                     => p_cle_id,
      p_source_flag                => p_source_flag,
      p_mandatory_yn               => p_mandatory_yn,
      p_scn_id                     => p_scn_id,
      p_label                      => p_label,
      p_amendment_description      => l_amendment_description,
      p_amendment_operation_code   => l_amendment_operation_code,
      p_summary_amend_operation_code   => l_summary_amend_operation_code,
      p_article_version_id         => p_article_version_id,
      p_change_nonstd_yn           => p_change_nonstd_yn,
      p_orig_system_reference_code => p_orig_system_reference_code,
      p_orig_system_reference_id1  => p_orig_system_reference_id1,
      p_orig_system_reference_id2  => p_orig_system_reference_id2,
      p_display_sequence           => p_display_sequence,
      p_attribute_category         => p_attribute_category,
      p_attribute1                 => p_attribute1,
      p_attribute2                 => p_attribute2,
      p_attribute3                 => p_attribute3,
      p_attribute4                 => p_attribute4,
      p_attribute5                 => p_attribute5,
      p_attribute6                 => p_attribute6,
      p_attribute7                 => p_attribute7,
      p_attribute8                 => p_attribute8,
      p_attribute9                 => p_attribute9,
      p_attribute10                => p_attribute10,
      p_attribute11                => p_attribute11,
      p_attribute12                => p_attribute12,
      p_attribute13                => p_attribute13,
      p_attribute14                => p_attribute14,
      p_attribute15                => p_attribute15,
      p_print_text_yn              => p_print_text_yn,
      p_ref_article_id             => p_ref_article_id,
      p_ref_article_version_id     => p_ref_article_version_id,
      p_object_version_number      => p_object_version_number
    );

Line: 852

                OKC_K_ART_VARIABLES_PVT.insert_row(
                                  x_return_status          => x_return_status,
                                  p_cat_id                 => p_id,
                                  p_variable_code          => l_create_variable_rec.variable_code,
                                  p_variable_type          => l_create_variable_rec.variable_type,
                                  p_external_yn            => l_create_variable_rec.external_yn,
                                  p_variable_value_id      => l_create_variable_rec.variable_value_id,
                                  p_variable_value         => l_create_variable_rec.variable_value,
                                  p_attribute_value_set_id => l_create_variable_rec.value_set_id,
                                  x_cat_id                 => x_cat_id,
                                  x_variable_code          => x_variable_code
                                       );

Line: 877

        FOR l_delete_variable_rec IN l_delete_variable_csr LOOP
                  OKC_K_ART_VARIABLES_PVT.delete_row(
                                    x_return_status         => x_return_status,
                                    p_cat_id                => l_delete_variable_rec.cat_id,
                                    p_variable_code         => l_delete_variable_rec.variable_code,
                                    p_object_version_number => l_delete_variable_rec.object_version_number
                                             );

Line: 898

        FOR l_update_variable_rec IN l_update_variable_csr LOOP

                OKC_K_ART_VARIABLES_PVT.update_row(
                                            x_return_status          => x_return_status,
                                            p_cat_id                 => p_id,
                                            p_variable_code          => l_update_variable_rec.variable_code,
                                            p_variable_type          => l_update_variable_rec.variable_type,
                                            p_external_yn            => l_update_variable_rec.external_yn,
                                            p_variable_value_id      => l_update_variable_rec.variable_value_id,
                                            p_variable_value         => l_update_variable_rec.variable_value,
                                            p_attribute_value_set_id => l_update_variable_rec.value_set_id,
                                            p_object_version_number  => l_update_variable_rec.object_version_number
                                            );

Line: 929

      okc_debug.log('1800: Leaving update_article', 2);

Line: 935

         okc_debug.log('1900: Leaving update_article: OKC_API.G_EXCEPTION_ERROR Exception', 2);

Line: 946

      IF l_update_variable_csr%ISOPEN THEN
         CLOSE  l_update_variable_csr;

Line: 950

      IF l_delete_variable_csr%ISOPEN THEN
         CLOSE  l_delete_variable_csr;

Line: 958

      ROLLBACK TO g_update_article_grp;

Line: 964

         okc_debug.log('2000: Leaving update_article: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);

Line: 975

      IF l_update_variable_csr%ISOPEN THEN
         CLOSE  l_update_variable_csr;

Line: 979

      IF l_delete_variable_csr%ISOPEN THEN
         CLOSE  l_delete_variable_csr;

Line: 987

      ROLLBACK TO g_update_article_grp;

Line: 993

        okc_debug.log('2100: Leaving update_article because of EXCEPTION: '||sqlerrm, 2);

Line: 1004

      IF l_update_variable_csr%ISOPEN THEN
         CLOSE  l_update_variable_csr;

Line: 1008

      IF l_delete_variable_csr%ISOPEN THEN
         CLOSE  l_delete_variable_csr;

Line: 1016

      ROLLBACK TO g_update_article_grp;

Line: 1023

  END update_article;

Line: 1028

  PROCEDURE delete_article(
    p_api_version                IN NUMBER,
    p_init_msg_list              IN VARCHAR2,
    p_validate_commit            IN VARCHAR2,
    p_validation_string          IN VARCHAR2,
    p_commit                     IN VARCHAR2,
    p_mode                       IN VARCHAR2,

    x_return_status              OUT NOCOPY VARCHAR2,
    x_msg_count                  OUT NOCOPY NUMBER,
    x_msg_data                   OUT NOCOPY VARCHAR2,
    p_super_user_yn              IN VARCHAR2,
    p_amendment_description      IN VARCHAR2,
    p_print_text_yn              IN VARCHAR2,
    p_id                         IN NUMBER,
    p_object_version_number      IN NUMBER,
    p_mandatory_clause_delete    IN VARCHAR2
  ) IS
    l_api_version                  CONSTANT NUMBER := 1;

Line: 1047

    l_api_name                     CONSTANT VARCHAR2(30) := 'g_delete_article';

Line: 1060

    l_delete_rec                 BOOLEAN := FALSE;

Line: 1063

    SELECT kart.DOCUMENT_ID,kart.DOCUMENT_TYPE,kart.mandatory_yn, art.standard_yn
    FROM OKC_K_ARTIClES_B kart, OKC_ARTICLES_ALL art
    WHERE kart.sav_sae_id = art.article_id
      AND ID=P_ID;

Line: 1069

    SELECT SUMMARY_AMEND_OPERATION_CODE,AMENDMENT_OPERATION_CODE
    FROM OKC_K_ARTICLES_B
    WHERE ID=P_ID;

Line: 1077

       okc_debug.log('2200: Entered delete_article', 2);

Line: 1081

    SAVEPOINT g_delete_article_grp;

Line: 1099

    IF l_mandatory_yn='Y' and l_document_type<>'TEMPLATE' and p_super_user_yn='N' and p_mandatory_clause_delete='N' THEN

        okc_Api.Set_Message(p_app_name     => G_APP_NAME,
                            p_msg_name     => 'OKC_DEL_MAND_ARTICLE');

Line: 1146

       l_amendment_operation_code:=G_AMEND_CODE_DELETED;

Line: 1153

        p_amend_operation_code=>G_AMEND_CODE_DELETED);

Line: 1155

            OKC_K_ARTICLES_PVT.Update_Row(
                         x_return_status              => x_return_status,
                         p_id                         => p_id,
                         p_amendment_operation_code   => l_amendment_operation_code,
                         p_amendment_description        => l_amendment_description,
                         p_print_text_yn                => p_print_text_yn,
                         p_summary_amend_operation_code => l_summary_amend_operation_code,
                         p_object_version_number        => NULL
                               );

Line: 1171

           l_delete_rec := TRUE;

Line: 1175

   IF p_mode<>'AMEND' or l_delete_rec THEN
      -- Delete Child records from OKC_K_ART_VARIABLES_TABLE
      IF (l_debug = 'Y') THEN
          okc_debug.log('2260: Deleting Child record from okc_k_art_varibles', 2) ;

Line: 1181

      OKC_K_ART_VARIABLES_PVT.delete_set(
                                    x_return_status         => x_return_status,
                                    p_cat_id                => p_id
                                       );

Line: 1198

     OKC_K_ARTICLES_PVT.Delete_Row(
           x_return_status              => x_return_status,
           p_id                         => p_id,
           p_object_version_number      => p_object_version_number
         );

Line: 1219

       okc_debug.log('2300: Leaving delete_article', 2);

Line: 1225

         okc_debug.log('2400: Leaving delete_article: OKC_API.G_EXCEPTION_ERROR Exception', 2);

Line: 1233

      ROLLBACK TO g_delete_article_grp;

Line: 1239

         okc_debug.log('2500: Leaving delete_article: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);

Line: 1247

      ROLLBACK TO g_delete_article_grp;

Line: 1253

        okc_debug.log('2600: Leaving delete_article because of EXCEPTION: '||sqlerrm, 2);

Line: 1261

      ROLLBACK TO g_delete_article_grp;

Line: 1268

  END delete_article;