APPS.OKC_NUMBER_SCHEME_GRP SQL Statements

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

Line: 101

      Select GETALPHABET(Decode( l_mod,0,l_floor-1,l_floor),type) into l_out  from dual;

Line: 106

	   SELECT l_out||fnd_global.local_chr(26+decode(type,'L',96,'U',64))
            into l_out from dual;

Line: 110

     SELECT  l_out||fnd_global.local_chr(l_mod+decode(type,'L',96,'U',64))
             into l_out from dual;

Line: 294

Select decode(p_level,1,l_lvl1_seq_code,2,l_lvl2_seq_code,3,l_lvl3_seq_code,4,l_lvl4_seq_code,5,l_lvl5_seq_code,NULL)
INTO l_vl_seq_code from dual;

Line: 396

SELECT ID,'SECTION' TYPE,section_sequence display_sequence from okc_sections_b
where scn_id=p_scn_id
and   document_type=p_doc_type
and   document_id = p_doc_id
AND   nvl(amendment_operation_code,'?') <> 'DELETED'
AND   nvl(summary_amend_operation_code,'?') <> 'DELETED'
UNION
SELECT ID,'ARTICLE' TYPE,display_sequence display_sequence from okc_k_ARTICLES_b
where scn_id=p_scn_id
and   document_type=p_doc_type
and   document_id = p_doc_id
AND   nvl(amendment_operation_code,'?') <> 'DELETED'
AND   nvl(summary_amend_operation_code,'?') <> 'DELETED'
AND l_number_article_yn = 'Y'
Order by 3;

Line: 447

select decode(p_level,1,NULL,2,l_lvl1_concat_yn,3,l_lvl2_concat_yn,4,l_lvl3_concat_yn,5,l_lvl4_concat_yn,'N'),
       decode(p_level,1,l_lvl1_end_char ,2,l_lvl2_end_char ,3,l_lvl3_end_char ,4,l_lvl4_end_char ,5,l_lvl5_end_char ,NULL)
into l_concat_yn ,l_end_char from dual;

Line: 560

    p_update_db                    IN  VARCHAR2 ,

    p_num_scheme_id                IN NUMBER
      ) IS
   Cursor l_num_sch_dtl_crs IS
   SELECT  decode(Num_Sequence_code, G_NUMBER_SEQUENCE,'1',G_LOWERCASE_ENG_ALPHABETS,'a',G_UPPERCASE_ENG_ALPHABETS,'A',G_LOWERCASE_ROMAN_NUMBER,'i',G_UPPERCASE_ROMAN_NUMBER,'I') label1,
           decode(Num_Sequence_code, G_NUMBER_SEQUENCE,'2',G_LOWERCASE_ENG_ALPHABETS,'b',G_UPPERCASE_ENG_ALPHABETS,'B',G_LOWERCASE_ROMAN_NUMBER,'ii',G_UPPERCASE_ROMAN_NUMBER,'II')label2,
           concatenation_yn,
           sequence_level,
           End_character
   FROM OKC_NUMBER_SCHEME_DTLS
   WHERE Num_scheme_Id = p_num_scheme_id
   order by Sequence_Level;

Line: 616

           SELECT
           decode(l_concat_yn,'Y',l_label||'.'||decode(k,1,decode(i,1,cr.label1,2,cr.label2,cr.label1),cr.label1),decode(k,1,decode(i,1,cr.label1,2,cr.label2,cr.label1),cr.label1))
          INTO l_label FROM DUAL;

Line: 633

IF p_update_db=FND_API.G_TRUE THEN

   Update OKC_NUMBER_SCHEMES_B SET
         num_scheme_preview            = x_out_string,
         object_version_number         = object_version_number+1,
         creation_date                 = sysdate,
         created_by                    = Fnd_Global.User_Id,
         last_update_date              = sysdate,
         last_updated_by               = Fnd_Global.User_Id,
         last_update_login             = Fnd_Global.Login_Id
   WHERE  num_scheme_id         = p_num_scheme_id;

Line: 725

SELECT ID,section_sequence  from okc_sections_b
where document_type = p_doc_type
AND   document_id   = p_doc_id
AND SCN_ID IS NULL
AND    nvl(amendment_operation_code,'?') <> 'DELETED'
AND   nvl(summary_amend_operation_code,'?') <> 'DELETED'
Order by section_sequence;

Line: 734

SELECT number_article_yn  from OKC_NUMBER_SCHEMES_B
where num_scheme_id=p_num_scheme_id;

Line: 738

SELECT num_sequence_code,sequence_level,concatenation_yn,end_character  from OKC_NUMBER_SCHEME_DTLS
where num_scheme_id=p_num_scheme_id;

Line: 742

SELECT template_id,object_version_number  from OKC_template_usages
where document_type=p_doc_type and document_id=p_doc_id;

Line: 746

SELECT id,object_version_number
FROM okc_k_articles_b
WHERE document_type=p_doc_type
AND document_id=p_doc_id
AND nvl(amendment_operation_code,'?') <> 'DELETED'
AND nvl(summary_amend_operation_code,'?') <> 'DELETED'
AND label is NOT NULL;

Line: 759

SELECT COUNT(*)
FROM OKC_NUMBER_SCHEME_DTLS
WHERE num_scheme_id=p_num_scheme_id;

Line: 805

    l_structure_tbl.DELETE;

Line: 865

       UPDATE okc_sections_b
          SET label = NULL
       WHERE document_type = p_doc_type
         AND   document_id   = p_doc_id ;

Line: 871

       UPDATE okc_k_articles_b
          SET label = NULL
       WHERE document_type = p_doc_type
         AND   document_id   = p_doc_id ;

Line: 1045

                        okc_debug.log('170: Calling OKC_TERMS_SECTIONS_GRP.update_section', 2);

Line: 1052

		             G_PKG_NAME, '170: Calling OKC_TERMS_SECTIONS_GRP.update_section' );

Line: 1059

                  OKC_TERMS_SECTIONS_GRP.update_section(
                                     p_api_version  => 1,
                                     p_init_msg_list => 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_id            => l_structure_tbl(k).id,
                                     p_label         => l_structure_tbl(k).label,
                                     p_object_version_number  => NULL
                                                   );

Line: 1072

                      okc_debug.log('170: After Calling OKC_TERMS_SECTIONS_GRP.update_section x_return_status : '||x_return_status, 2);

Line: 1077

			         G_PKG_NAME, '170: After Calling OKC_TERMS_SECTIONS_GRP.update_section x_return_status : '||x_return_status );

Line: 1088

                        okc_debug.log('180: Calling OKC_K_ARTICLES_GRP.update_article', 2);

Line: 1095

			         G_PKG_NAME, '180: Calling OKC_K_ARTICLES_GRP.update_article');

Line: 1102

                  OKC_K_ARTICLES_GRP.update_article(
                                     p_api_version  => 1,
                                     p_init_msg_list => 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_id            => l_structure_tbl(k).id,
                                     p_label         => l_structure_tbl(k).label,
                                     p_object_version_number  => NULL
                                                   );

Line: 1114

                      okc_debug.log('180: After Calling OKC_TERMS_SECTIONS_GRP.update_section x_return_status : '||x_return_status, 2);

Line: 1119

			         G_PKG_NAME, '180: After Calling OKC_TERMS_SECTIONS_GRP.update_section x_return_status : '||x_return_status );

Line: 1131

 /******* Not needed as all the labels are being updated in the begining itself
 IF l_number_article_yn <>'Y' THEN
   -- if any articles are already numbered, make label to null
   FOR cr in l_get_art_csr LOOP
                     OKC_K_ARTICLES_GRP.update_article(
                                     p_api_version  => 1,
                                     p_init_msg_list => 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_id            => cr.id,
                                     p_label         => OKC_API.G_MISS_CHAR,
                                     p_object_version_number  => cr.object_version_number
                                                   );

Line: 1167

           okc_debug.log('300: OKC_TERMS_TEMPLATES_GRP.update_template', 2);

Line: 1174

               G_PKG_NAME, '300: OKC_TERMS_TEMPLATES_GRP.update_template' );

Line: 1177

    OKC_TERMS_TEMPLATES_GRP.update_template(
                             p_api_version   => 1,
                             p_init_msg_list => FND_API.G_FALSE,
                             x_return_status => x_return_status,
                             x_msg_count     => x_msg_count,
                             x_msg_data      => x_msg_data,
                             p_template_name => NULL,
                             p_template_id   => p_doc_id,
                             p_working_copy_flag =>NULL,
                             p_intent                  =>NULL,
                             p_status_code             =>NULL,
                             p_start_date              =>NULL,
                             p_end_date                =>NULL,
                             p_global_flag             =>NULL,
                             p_parent_template_id      =>NULL,
                             p_print_template_id       =>NULL,
                             p_contract_expert_enabled =>NULL,
                             p_template_model_id       =>NULL,
                             p_instruction_text        =>NULL,
                             p_tmpl_numbering_scheme   =>p_num_scheme_id,
                             p_description             =>NULL,
                             p_org_id                  => NULL,
                             p_object_version_number   => NULL
                             );

Line: 1203

                     okc_debug.log('300: After Call to OKC_TERMS_TEMPLATES_GRP.update_template x_return_status : '||x_return_status, 2);

Line: 1208

	                  G_PKG_NAME, '300: After Call to OKC_TERMS_TEMPLATES_GRP.update_template x_return_status : '||x_return_status );

Line: 1223

           okc_debug.log('400: OKC_TEMPLATE_USAGES_GRP.update_template_usages', 2);

Line: 1228

	       G_PKG_NAME, '400: OKC_TEMPLATE_USAGES_GRP.update_template_usages' );

Line: 1231

     OKC_TEMPLATE_USAGES_GRP.update_template_usages(
                          p_api_version  =>1,
                          p_init_msg_list => FND_API.G_FALSE,
                          x_return_status => x_return_status,
                          x_msg_count     => x_msg_count,
                          x_msg_data      => x_msg_data,
                          p_document_type => p_doc_type,
                          p_document_id   => p_doc_id,
                          p_template_id   => cr.template_id,
                          p_doc_numbering_scheme=>p_num_scheme_id ,
                          p_document_number        =>NULL,
                          p_article_effective_date => NULL,
                          p_config_header_id       =>NULL,
                          p_config_revision_number =>NULL,
                          p_valid_config_yn        =>NULL,
                          p_object_version_number  =>cr.object_version_number
                          );

Line: 1250

           okc_debug.log('400: After Call to OKC_TEMPLATE_USAGES_GRP.update_template_usages x_return_status : '||x_return_status, 2);

Line: 1255

	       G_PKG_NAME, '400: After Call to OKC_TEMPLATE_USAGES_GRP.update_template_usages x_return_status : '||x_return_status );

Line: 1331

  FUNCTION Ok_To_Delete(
    p_num_scheme_id         IN NUMBER
   ) RETURN VARCHAR2 IS
    CURSOR used_in_tmpl_crs IS
     SELECT 'N' from OKC_TERMS_TEMPLATES_ALL
      WHERE TMPL_NUMBERING_SCHEME=p_num_scheme_id;

Line: 1338

     SELECT 'N' from OKC_TEMPLATE_USAGES_V
      WHERE DOC_NUMBERING_SCHEME=p_num_scheme_id;

Line: 1360

  END Ok_To_Delete;

Line: 1370

SELECT review_upld_terms_id,
       object_type TYPE,
	  display_seq,
	  object_id
from okc_review_upld_terms
where new_parent_id=p_review_upld_terms_id
and   document_type=p_doc_type
and   document_id = p_doc_id
and   (object_type = 'SECTION'
      OR (object_type = 'ARTICLE'
         and   l_number_article_yn = 'Y'))
/***********
UNION
SELECT review_upld_terms_id,'ARTICLE' TYPE,display_seq, object_id from okc_review_upld_terms
where new_parent_id=p_review_upld_terms_id
and   document_type=p_doc_type
and   document_id = p_doc_id
and   object_type = 'ARTICLE'
*********/
Order by 3;

Line: 1416

select decode(p_level,1,NULL,2,l_lvl1_concat_yn,3,l_lvl2_concat_yn,4,l_lvl3_concat_yn,5,l_lvl4_concat_yn,'N'),
       decode(p_level,1,l_lvl1_end_char ,2,l_lvl2_end_char ,3,l_lvl3_end_char ,4,l_lvl4_end_char ,5,l_lvl5_end_char ,NULL)
into l_concat_yn ,l_end_char from dual;

Line: 1522

SELECT review_upld_terms_id,display_seq, object_id
from okc_review_upld_terms
where document_type = p_doc_type
AND   document_id   = p_doc_id
AND   object_type = 'SECTION'
--and (action <> 'DELETED' OR action is null)
AND new_parent_id = (select review_upld_terms_id
                     from okc_review_upld_terms rt
				 where rt.document_type = p_doc_type
				 and rt.document_id = p_doc_id
				 and rt.object_type = p_doc_type
				 and rt.object_id = p_doc_id)
Order by display_seq;

Line: 1537

SELECT number_article_yn  from OKC_NUMBER_SCHEMES_B
where num_scheme_id=p_num_scheme_id;

Line: 1541

SELECT num_sequence_code,sequence_level,concatenation_yn,end_character  from OKC_NUMBER_SCHEME_DTLS
where num_scheme_id=p_num_scheme_id;

Line: 1547

SELECT review_upld_terms_id,object_version_number, object_id
FROM okc_review_upld_terms
WHERE document_type=p_doc_type
AND document_id=p_doc_id
AND   object_type = 'ARTICLE'
and action <> 'DELETED';

Line: 1559

SELECT COUNT(*)
FROM OKC_NUMBER_SCHEME_DTLS
WHERE num_scheme_id=p_num_scheme_id;

Line: 1592

    l_review_tbl.DELETE;

Line: 1641

  UPDATE okc_review_upld_terms
       SET label = NULL
       WHERE document_type = p_doc_type
       AND   document_id   = p_doc_id ;

Line: 1783

       		         G_PKG_NAME, '170: Calling OKC_REVIEW_UPLD_TERMS_PVT.update_row');

Line: 1790

                 OKC_REVIEW_UPLD_TERMS_PVT.update_row(
                                     x_return_status => x_return_status,
                                     p_review_upld_terms_id  => l_review_tbl(k).review_upld_terms_id,
                                     p_label         => l_review_tbl(k).label,
                                     p_object_version_number  => NULL);

Line: 1798

			         G_PKG_NAME, '170: After Calling OKC_REVIEW_UPLD_TERMS_PVT.update_row x_return_status : '||x_return_status );