DBA Data[Home] [Help]

APPS.OKC_STD_ARTICLE_PVT SQL Statements

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

Line: 149

 PROCEDURE Update_std_article(
	p_api_version 		IN NUMBER,
	p_init_msg_list 	IN VARCHAR2 ,
	x_return_status		OUT NOCOPY VARCHAR2,
	x_msg_count		OUT NOCOPY NUMBER,
	x_msg_data		OUT NOCOPY VARCHAR2,
	p_saev_rec		IN saev_rec_type,
	p_savv_tbl		IN savv_tbl_type,
	p_saiv_tbl		IN saiv_tbl_type,
	p_samv_tbl		IN samv_tbl_type,
	x_saev_rec		OUT NOCOPY saev_rec_type,
	x_savv_tbl		OUT NOCOPY savv_tbl_type,
	x_saiv_tbl		OUT NOCOPY saiv_tbl_type,
	x_samv_tbl		OUT NOCOPY samv_tbl_type) IS
    l_return_status	VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
Line: 165

   	   Update_std_article(
	    p_api_version,
	    p_init_msg_list,
	    x_return_status,
	    x_msg_count,
	    x_msg_data,
	    p_saev_rec,
	    x_saev_rec);
Line: 183

        Update_std_art_version(
	    p_api_version,
	    p_init_msg_list,
	    x_return_status,
	    x_msg_count,
	    x_msg_data,
	    p_savv_tbl,
	    x_savv_tbl);
Line: 199

	Update_std_art_incmpt(
	    p_api_version,
	    p_init_msg_list,
	    x_return_status,
	    x_msg_count,
	    x_msg_data,
	    p_saiv_tbl,
	    x_saiv_tbl);
Line: 215

	Update_std_art_set_mem(
	    p_api_version,
	    p_init_msg_list,
	    x_return_status,
	    x_msg_count,
	    x_msg_data,
	    p_samv_tbl,
	    x_samv_tbl);
Line: 244

 END Update_std_article;
Line: 347

   	OKC_SAE_PVT.insert_row(p_api_version,
			       	p_init_msg_list,
				x_return_status,
				x_msg_count,
				x_msg_data,
				p_saev_tbl,
				x_saev_tbl);
Line: 366

   	OKC_SAE_PVT.insert_row(p_api_version,
			       	p_init_msg_list,
				x_return_status,
				x_msg_count,
				x_msg_data,
				p_saev_rec,
				x_saev_rec);
Line: 408

PROCEDURE update_std_article(
	p_api_version 		IN NUMBER,
	p_init_msg_list 		IN VARCHAR2 ,
	x_return_status		OUT NOCOPY VARCHAR2,
	x_msg_count		OUT NOCOPY NUMBER,
	x_msg_data		OUT NOCOPY VARCHAR2,
	p_saev_tbl		IN saev_tbl_type,
	x_saev_tbl		OUT NOCOPY saev_tbl_type) IS
 BEGIN
   	OKC_SAE_PVT.update_row(p_api_version,
			       	p_init_msg_list,
				x_return_status,
				x_msg_count,
				x_msg_data,
				p_saev_tbl,
				x_saev_tbl);
Line: 424

 END update_std_article;
Line: 427

PROCEDURE update_std_article(
	p_api_version 		IN NUMBER,
	p_init_msg_list 		IN VARCHAR2 ,
	x_return_status		OUT NOCOPY VARCHAR2,
	x_msg_count		OUT NOCOPY NUMBER,
	x_msg_data		OUT NOCOPY VARCHAR2,
	p_saev_rec		IN saev_rec_type,
	x_saev_rec		OUT NOCOPY saev_rec_type) IS
 BEGIN
   	OKC_SAE_PVT.update_row(p_api_version,
			       	p_init_msg_list,
				x_return_status,
				x_msg_count,
				x_msg_data,
				p_saev_rec,
				x_saev_rec);
Line: 443

 END update_std_article;
Line: 446

PROCEDURE delete_std_article(
	p_api_version 		IN NUMBER,
	p_init_msg_list 		IN VARCHAR2 ,
	x_return_status		OUT NOCOPY VARCHAR2,
	x_msg_count		OUT NOCOPY NUMBER,
	x_msg_data		OUT NOCOPY VARCHAR2,
	p_saev_rec		IN saev_rec_type) IS

	 Cursor l_ver(id number) is
	   select '1' from OKC_STD_ART_VERSIONS_B where sae_id=id;
Line: 457

	   select '1' from OKC_STD_ART_SET_MEMS where sae_id=id;
Line: 459

	   select '1' from OKC_STD_ART_INCMPTS where sae_id=id;
Line: 489

       OKC_SAE_PVT.delete_row(p_api_version,
			       	p_init_msg_list,
				x_return_status,
				x_msg_count,
				x_msg_data,
				p_saev_rec);
Line: 499

                        p_msg_name     => G_CHILD_DELETE);
Line: 509

 END delete_std_article;
Line: 512

PROCEDURE delete_std_article(
	p_api_version 		IN NUMBER,
	p_init_msg_list 		IN VARCHAR2 ,
	x_return_status		OUT NOCOPY VARCHAR2,
	x_msg_count		OUT NOCOPY NUMBER,
	x_msg_data		OUT NOCOPY VARCHAR2,
	p_saev_tbl		IN saev_tbl_type) IS
        i number                 :=0;
Line: 526

        delete_std_article(
	    p_api_version,
	    p_init_msg_list,
	    l_return_status,
	    x_msg_count,
	    x_msg_data,
	    p_saev_tbl(i));
Line: 557

 END delete_std_article;
Line: 695

   	OKC_SAV_PVT.insert_row(p_api_version,
			       	p_init_msg_list,
				x_return_status,
				x_msg_count,
				x_msg_data,
				p_savv_tbl,
				x_savv_tbl);
Line: 714

   	OKC_SAV_PVT.insert_row(p_api_version,
			       	p_init_msg_list,
				x_return_status,
				x_msg_count,
				x_msg_data,
				p_savv_rec,
				x_savv_rec);
Line: 756

PROCEDURE update_std_art_version(
	p_api_version 		IN NUMBER,
	p_init_msg_list 		IN VARCHAR2 ,
	x_return_status		OUT NOCOPY VARCHAR2,
	x_msg_count		OUT NOCOPY NUMBER,
	x_msg_data		OUT NOCOPY VARCHAR2,
	p_savv_tbl		IN savv_tbl_type,
	x_savv_tbl		OUT NOCOPY savv_tbl_type) IS
 BEGIN
   	OKC_SAV_PVT.update_row(p_api_version,
			       	p_init_msg_list,
				x_return_status,
				x_msg_count,
				x_msg_data,
				p_savv_tbl,
				x_savv_tbl);
Line: 772

 END update_std_art_version;
Line: 775

PROCEDURE update_std_art_version(
	p_api_version 		IN NUMBER,
	p_init_msg_list 		IN VARCHAR2 ,
	x_return_status		OUT NOCOPY VARCHAR2,
	x_msg_count		OUT NOCOPY NUMBER,
	x_msg_data		OUT NOCOPY VARCHAR2,
	p_savv_rec		IN savv_rec_type,
	x_savv_rec		OUT NOCOPY savv_rec_type) IS
 BEGIN
   	OKC_SAV_PVT.update_row(p_api_version,
			       	p_init_msg_list,
				x_return_status,
				x_msg_count,
				x_msg_data,
				p_savv_rec,
				x_savv_rec);
Line: 791

 END update_std_art_version;
Line: 793

PROCEDURE delete_std_art_version(
	p_api_version 		IN NUMBER,
	p_init_msg_list 		IN VARCHAR2 ,
	x_return_status		OUT NOCOPY VARCHAR2,
	x_msg_count		OUT NOCOPY NUMBER,
	x_msg_data		OUT NOCOPY VARCHAR2,
	p_savv_tbl		IN savv_tbl_type) IS
 BEGIN
   	OKC_SAV_PVT.delete_row(p_api_version,
			       	p_init_msg_list,
				x_return_status,
				x_msg_count,
				x_msg_data,
				p_savv_tbl);
Line: 807

 END delete_std_art_version;
Line: 809

PROCEDURE delete_std_art_version(
	p_api_version 		IN NUMBER,
	p_init_msg_list 		IN VARCHAR2 ,
	x_return_status		OUT NOCOPY VARCHAR2,
	x_msg_count		OUT NOCOPY NUMBER,
	x_msg_data		OUT NOCOPY VARCHAR2,
	p_savv_rec		IN savv_rec_type) IS
 BEGIN
   	OKC_SAV_PVT.delete_row(p_api_version,
			       	p_init_msg_list,
				x_return_status,
				x_msg_count,
				x_msg_data,
				p_savv_rec);
Line: 823

 END delete_std_art_version;
Line: 1201

   	OKC_SAI_PVT.insert_row(p_api_version,
			       	p_init_msg_list,
				x_return_status,
				x_msg_count,
				x_msg_data,
				p_saiv_tbl,
				x_saiv_tbl);
Line: 1231

   		OKC_SAI_PVT.insert_row(p_api_version,
			       	p_init_msg_list,
				x_return_status,
				x_msg_count,
				x_msg_data,
				p_saiv_rec,
				x_saiv_rec);
Line: 1279

        SELECT '1'
        FROM   okc_std_art_incmpts  sai
   	WHERE  sai.sae_id = p_saiv_rec.sae_id_for and sai.sae_id_for = p_saiv_rec.sae_id;
Line: 1320

PROCEDURE update_std_art_incmpt(
	p_api_version 		IN NUMBER,
	p_init_msg_list 		IN VARCHAR2 ,
	x_return_status		OUT NOCOPY VARCHAR2,
	x_msg_count		OUT NOCOPY NUMBER,
	x_msg_data		OUT NOCOPY VARCHAR2,
	p_saiv_tbl		IN saiv_tbl_type,
	x_saiv_tbl		OUT NOCOPY saiv_tbl_type) IS
 BEGIN
   	OKC_SAI_PVT.update_row(p_api_version,
			       	p_init_msg_list,
				x_return_status,
				x_msg_count,
				x_msg_data,
				p_saiv_tbl,
				x_saiv_tbl);
Line: 1336

 END update_std_art_incmpt;
Line: 1338

PROCEDURE update_std_art_incmpt(
	p_api_version 		IN NUMBER,
	p_init_msg_list 		IN VARCHAR2 ,
	x_return_status		OUT NOCOPY VARCHAR2,
	x_msg_count		OUT NOCOPY NUMBER,
	x_msg_data		OUT NOCOPY VARCHAR2,
	p_saiv_rec		IN saiv_rec_type,
	x_saiv_rec		OUT NOCOPY saiv_rec_type) IS

        l_api_version           CONSTANT NUMBER := 1;
Line: 1352

        SELECT '1'
        FROM   okc_std_art_incmpts  sai
   	    WHERE  sai.sae_id = p_saiv_rec.sae_id_for and sai.sae_id_for = p_saiv_rec.sae_id;
Line: 1364

       		--update the record if the combination exists like this
    		OKC_SAI_PVT.update_row(p_api_version,
			       		p_init_msg_list,
					x_return_status,
					x_msg_count,
					x_msg_data,
					p_saiv_rec,
					x_saiv_rec);
Line: 1376

       		-- update the opposite combination as the record might exist the opposite combination way
   		 OKC_SAI_PVT.update_row(p_api_version,
			       		p_init_msg_list,
					x_return_status,
					x_msg_count,
					x_msg_data,
					l_saiv_rec,
					x_saiv_rec);
Line: 1396

 END update_std_art_incmpt;
Line: 1398

PROCEDURE delete_std_art_incmpt(
	p_api_version 		IN NUMBER,
	p_init_msg_list 		IN VARCHAR2 ,
	x_return_status		OUT NOCOPY VARCHAR2,
	x_msg_count		OUT NOCOPY NUMBER,
	x_msg_data		OUT NOCOPY VARCHAR2,
	p_saiv_tbl		IN saiv_tbl_type) IS
 BEGIN
   	OKC_SAI_PVT.delete_row(p_api_version,
			       	p_init_msg_list,
				x_return_status,
				x_msg_count,
				x_msg_data,
				p_saiv_tbl);
Line: 1412

 END delete_std_art_incmpt;
Line: 1414

PROCEDURE delete_std_art_incmpt(
	p_api_version 		IN NUMBER,
	p_init_msg_list 		IN VARCHAR2 ,
	x_return_status		OUT NOCOPY VARCHAR2,
	x_msg_count		OUT NOCOPY NUMBER,
	x_msg_data		OUT NOCOPY VARCHAR2,
	p_saiv_rec		IN saiv_rec_type) IS

        l_saiv_rec                saiv_rec_type;
Line: 1424

        SELECT '1'
        FROM   okc_std_art_incmpts  sai
   	    WHERE  sai.sae_id = p_saiv_rec.sae_id_for and sai.sae_id_for = p_saiv_rec.sae_id;
Line: 1434

       		--delete the record if the combination exists like this
   		 OKC_SAI_PVT.delete_row(p_api_version,
			       		p_init_msg_list,
					x_return_status,
					x_msg_count,
					x_msg_data,
					p_saiv_rec);
Line: 1445

       		-- try to delete the opposite combination as the record might exist the opposite combination way
      		 OKC_SAI_PVT.delete_row(p_api_version,
			       		p_init_msg_list,
					x_return_status,
					x_msg_count,
					x_msg_data,
					l_saiv_rec);
Line: 1464

 END delete_std_art_incmpt;
Line: 1497

        SELECT '1'
        FROM   okc_std_art_incmpts  sai
   	    WHERE  sai.sae_id = p_saiv_rec.sae_id_for and sai.sae_id_for = p_saiv_rec.sae_id;
Line: 1635

   	OKC_SAC_PVT.insert_row(p_api_version,
			       	p_init_msg_list,
				x_return_status,
				x_msg_count,
				x_msg_data,
				p_sacv_tbl,
				x_sacv_tbl);
Line: 1653

   	OKC_SAC_PVT.insert_row(p_api_version,
			       	p_init_msg_list,
				x_return_status,
				x_msg_count,
				x_msg_data,
				p_sacv_rec,
				x_sacv_rec);
Line: 1696

PROCEDURE update_std_art_classing(
	p_api_version 		IN NUMBER,
	p_init_msg_list 		IN VARCHAR2 ,
	x_return_status		OUT NOCOPY VARCHAR2,
	x_msg_count		OUT NOCOPY NUMBER,
	x_msg_data		OUT NOCOPY VARCHAR2,
	p_sacv_tbl		IN sacv_tbl_type,
	x_sacv_tbl		OUT NOCOPY sacv_tbl_type) IS
 BEGIN
   	OKC_SAC_PVT.update_row(p_api_version,
			       	p_init_msg_list,
				x_return_status,
				x_msg_count,
				x_msg_data,
				p_sacv_tbl,
				x_sacv_tbl);
Line: 1712

 END update_std_art_classing;
Line: 1715

PROCEDURE update_std_art_classing(
	p_api_version 		IN NUMBER,
	p_init_msg_list 		IN VARCHAR2 ,
	x_return_status		OUT NOCOPY VARCHAR2,
	x_msg_count		OUT NOCOPY NUMBER,
	x_msg_data		OUT NOCOPY VARCHAR2,
	p_sacv_rec		IN sacv_rec_type,
	x_sacv_rec		OUT NOCOPY sacv_rec_type) IS
 BEGIN
   	OKC_SAC_PVT.update_row(p_api_version,
			       	p_init_msg_list,
				x_return_status,
				x_msg_count,
				x_msg_data,
				p_sacv_rec,
				x_sacv_rec);
Line: 1731

 END update_std_art_classing;
Line: 1733

PROCEDURE delete_std_art_classing(
	p_api_version 		IN NUMBER,
	p_init_msg_list 		IN VARCHAR2 ,
	x_return_status		OUT NOCOPY VARCHAR2,
	x_msg_count		OUT NOCOPY NUMBER,
	x_msg_data		OUT NOCOPY VARCHAR2,
	p_sacv_tbl		IN sacv_tbl_type) IS
 BEGIN
   	OKC_SAC_PVT.delete_row(p_api_version,
			       	p_init_msg_list,
				x_return_status,
				x_msg_count,
				x_msg_data,
				p_sacv_tbl);
Line: 1747

 END delete_std_art_classing;
Line: 1750

PROCEDURE delete_std_art_classing(
	p_api_version 		IN NUMBER,
	p_init_msg_list 		IN VARCHAR2 ,
	x_return_status		OUT NOCOPY VARCHAR2,
	x_msg_count		OUT NOCOPY NUMBER,
	x_msg_data		OUT NOCOPY VARCHAR2,
	p_sacv_rec		IN sacv_rec_type) IS
 BEGIN
   	OKC_SAC_PVT.delete_row(p_api_version,
			       	p_init_msg_list,
				x_return_status,
				x_msg_count,
				x_msg_data,
				p_sacv_rec);
Line: 1764

 END delete_std_art_classing;
Line: 1975

   	OKC_SAM_PVT.Insert_row(p_api_version,
			       	p_init_msg_list,
				x_return_status,
				x_msg_count,
				x_msg_data,
				p_samv_tbl,
				x_samv_tbl);
Line: 1994

   	OKC_SAM_PVT.Insert_row(p_api_version,
			       	p_init_msg_list,
				x_return_status,
				x_msg_count,
				x_msg_data,
				p_samv_rec,
				x_samv_rec);
Line: 2037

PROCEDURE update_std_art_set_mem(
	p_api_version 		IN NUMBER,
	p_init_msg_list 		IN VARCHAR2 ,
	x_return_status		OUT NOCOPY VARCHAR2,
	x_msg_count		OUT NOCOPY NUMBER,
	x_msg_data		OUT NOCOPY VARCHAR2,
	p_samv_tbl		IN samv_tbl_type,
	x_samv_tbl		OUT NOCOPY samv_tbl_type) IS
 BEGIN
   	OKC_SAM_PVT.update_row(p_api_version,
			       	p_init_msg_list,
				x_return_status,
				x_msg_count,
				x_msg_data,
				p_samv_tbl,
				x_samv_tbl);
Line: 2053

 END update_std_art_set_mem;
Line: 2055

PROCEDURE update_std_art_set_mem(
	p_api_version 		IN NUMBER,
	p_init_msg_list 		IN VARCHAR2 ,
	x_return_status		OUT NOCOPY VARCHAR2,
	x_msg_count		OUT NOCOPY NUMBER,
	x_msg_data		OUT NOCOPY VARCHAR2,
	p_samv_rec		IN samv_rec_type,
	x_samv_rec		OUT NOCOPY samv_rec_type) IS
 BEGIN
   	OKC_SAM_PVT.update_row(p_api_version,
			       	p_init_msg_list,
				x_return_status,
				x_msg_count,
				x_msg_data,
				p_samv_rec,
				x_samv_rec);
Line: 2071

 END update_std_art_set_mem;
Line: 2073

PROCEDURE delete_std_art_set_mem(
	p_api_version 		IN NUMBER,
	p_init_msg_list 		IN VARCHAR2 ,
	x_return_status		OUT NOCOPY VARCHAR2,
	x_msg_count		OUT NOCOPY NUMBER,
	x_msg_data		OUT NOCOPY VARCHAR2,
	p_samv_tbl		IN samv_tbl_type) IS
 BEGIN
   	OKC_SAM_PVT.delete_row(p_api_version,
			       	p_init_msg_list,
				x_return_status,
				x_msg_count,
				x_msg_data,
				p_samv_tbl);
Line: 2087

 END delete_std_art_set_mem;
Line: 2089

PROCEDURE delete_std_art_set_mem(
	p_api_version 		IN NUMBER,
	p_init_msg_list 		IN VARCHAR2 ,
	x_return_status		OUT NOCOPY VARCHAR2,
	x_msg_count		OUT NOCOPY NUMBER,
	x_msg_data		OUT NOCOPY VARCHAR2,
	p_samv_rec		IN samv_rec_type) IS
 BEGIN
   	OKC_SAM_PVT.delete_row(p_api_version,
			       	p_init_msg_list,
				x_return_status,
				x_msg_count,
				x_msg_data,
				p_samv_rec);
Line: 2103

 END delete_std_art_set_mem;
Line: 2217

  If this is 'Y' we disable delete for the release
*/

l_count    NUMBER;
Line: 2223

SELECT COUNT(*)
FROM okc_k_articles_v
WHERE sav_sae_id = p_sav_sae_id
  AND sav_sav_release = p_sav_sav_release ;
Line: 2276

SELECT sav_release
  FROM okc_std_art_versions_b
 WHERE sae_id = p_sav_sae_id
   AND date_active <= sysdate
 ORDER BY date_active DESC, creation_date DESC ;
Line: 2326

 This is used in view okc_kol_std_art_lib_v to decide if an article can be updated

 Article can be update if :
 1. It is NOT used in any contracts
 2. It is the latest release OR future dated release.
*/

l_latest_release  okc_std_art_versions_b.sav_release%TYPE := '';
Line: 2365

  If this is 'Y' we disable delete for the release
*/

l_count    NUMBER;
Line: 2371

SELECT COUNT(*)
FROM okc_article_versions a, okc_k_articles_b b
WHERE b.sav_sae_id = p_sav_sae_id
  AND b.sav_sae_id = a.article_id
  AND ( a.article_version_number = p_article_version_number OR
        a.sav_release = to_char(p_article_version_number) );
Line: 2413

SELECT NVL(sav_release, to_char(article_version_number))
  FROM okc_article_versions
 WHERE article_id = p_sav_sae_id
   AND start_date <= sysdate
 ORDER BY start_date DESC, creation_date DESC ;
Line: 2468

 This is used in view okc_kol_std_art_lib_v to decide if an article can be updated

 Article can be update if :
 1. It is NOT used in any contracts
 2. It is the latest release OR future dated release.
*/

l_latest_release  okc_article_versions.sav_release%TYPE := ' ';