APPS.OKC_ART_BLK_PVT SQL Statements

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

Line: 61

PROCEDURE update_art_version_status_blk(
	p_id				    IN	NUMBER ,
	p_status				IN	VARCHAR2 ,
	x_return_status			OUT	NOCOPY VARCHAR2 );

Line: 66

PROCEDURE update_adp_status_type_blk(
	p_id				    IN	NUMBER ,
    p_local_org_id          IN  NUMBER,
	p_adoption_status		IN	VARCHAR2 ,
	p_adoption_type			IN	VARCHAR2 ,
    p_type                  IN  VARCHAR2,
	x_return_status			OUT	NOCOPY VARCHAR2 );

Line: 74

PROCEDURE update_prev_vers_enddate_blk(
	p_id				    IN	NUMBER ,
  	x_return_status			OUT	NOCOPY VARCHAR2 );

Line: 82

PROCEDURE delete_relationships_blk(
	p_id				    IN	NUMBER,
    p_org_id                IN  NUMBER,
	x_return_status			OUT	NOCOPY VARCHAR2 );

Line: 98

        SELECT OKC_ART_BLK_TEMP_S1.NEXTVAL FROM DUAL;

Line: 142

        SELECT sum(to_number(decode(adopt_asis_yn, 'Y', '1', '0'))) adopt_is_count,
            sum(to_number(decode(global_yn, 'Y', '1', '0'))) global_count,
            sum(to_number(decode(localized_yn, 'Y', '1', '0'))) localized_count,
            count(*) total_count
        FROM OKC_ART_BLK_TEMP
        WHERE id = cp_id;

Line: 185

            INSERT INTO OKC_ART_BLK_TEMP
            (
                ID,
                ARTICLE_ID,
                ARTICLE_VERSION_ID,
                ORG_ID,
                ARTICLE_TITLE,
                DISPLAY_NAME,
                ARTICLE_TYPE,
                DEFAULT_SECTION,
                STATUS,
                START_DATE,
                ADOPT_ASIS_YN,
                GLOBAL_YN,
                LOCALIZED_YN
            )
            SELECT x_id, ART.article_id, VER.article_version_id, ART.org_id,
                ART.ARTICLE_TITLE, VER.DISPLAY_NAME, ART.ARTICLE_TYPE,
                nvl(VER.DEFAULT_SECTION, 'UNASSIGNED'), nvl(VER.ARTICLE_STATUS,'DRAFT'),
                VER.start_date, 'N', nvl(VER.GLOBAL_YN, 'N'), 'N'
            FROM OKC_ARTICLES_ALL ART, OKC_ARTICLE_VERSIONS VER
            WHERE VER.article_version_id = p_art_ver_tbl(i)
                AND ART.ARTICLE_ID = VER.ARTICLE_ID
                AND ART.ORG_ID = G_GLOBAL_ORG_ID;

Line: 214

            INSERT INTO OKC_ART_BLK_TEMP
            (
                ID,
                ARTICLE_ID,
                ARTICLE_VERSION_ID,
                ORG_ID,
                ARTICLE_TITLE,
                DISPLAY_NAME,
                ARTICLE_TYPE,
                DEFAULT_SECTION,
                STATUS,
                START_DATE,
                ADOPT_ASIS_YN,
                GLOBAL_YN,
                LOCALIZED_YN
            )
            SELECT x_id, ART.article_id, VER.article_version_id, ART.org_id,
                ART.ARTICLE_TITLE, VER.DISPLAY_NAME, ART.ARTICLE_TYPE,
                nvl(VER.DEFAULT_SECTION, 'UNASSIGNED'), nvl(VER.ARTICLE_STATUS,'DRAFT'),
                VER.start_date,'N', 'N', decode(VER.ADOPTION_TYPE, 'LOCALIZED', 'Y', 'N')
            FROM OKC_ARTICLES_ALL ART, OKC_ARTICLE_VERSIONS VER
            WHERE VER.article_version_id = p_art_ver_tbl(i)
                AND ART.ARTICLE_ID = VER.ARTICLE_ID
                AND ART.ORG_ID = p_org_id;

Line: 241

            INSERT INTO OKC_ART_BLK_TEMP
            (
                ID,
                ARTICLE_ID,
                ARTICLE_VERSION_ID,
                ORG_ID,
                ARTICLE_TITLE,
                DISPLAY_NAME,
                ARTICLE_TYPE,
                DEFAULT_SECTION,
                STATUS,
                START_DATE,
                ADOPT_ASIS_YN,
                GLOBAL_YN,
                LOCALIZED_YN
            )
            SELECT x_id, ART.article_id, VER.article_version_id, ART.org_id,
                ART.ARTICLE_TITLE, VER.DISPLAY_NAME, ART.ARTICLE_TYPE,
                nvl(VER.DEFAULT_SECTION, 'UNASSIGNED'), nvl(ADP.ADOPTION_STATUS,'DRAFT'),
                VER.start_date,'Y', 'N', 'N'
            FROM OKC_ARTICLES_ALL ART, OKC_ARTICLE_VERSIONS VER, OKC_ARTICLE_ADOPTIONS ADP
            WHERE VER.article_version_id = p_art_ver_tbl(i)
                AND ART.ARTICLE_ID = VER.ARTICLE_ID
                AND ART.ORG_ID = G_GLOBAL_ORG_ID
                AND ADP.GLOBAL_ARTICLE_VERSION_ID = VER.ARTICLE_VERSION_ID
                AND ADP.LOCAL_ORG_ID = p_org_id;

Line: 334

        SELECT article_id, article_version_id, nvl(display_name, article_title),
        nvl(status, 'DRAFT')
        FROM OKC_ART_BLK_TEMP
        WHERE id = p_id AND
        status NOT IN ('DRAFT', 'REJECTED');

Line: 341

        SELECT article_id, article_version_id,  nvl(display_name, article_title),
        nvl(status, 'DRAFT')
        FROM OKC_ART_BLK_TEMP
        WHERE id = p_id AND
        status <> 'PENDING_APPROVAL';

Line: 348

        SELECT NVL(lookup_code, 'X'), NVL(meaning, 'UNDEFINED')
        FROM fnd_lookup_values_vl
        WHERE lookup_type = 'OKC_ARTICLE_STATUS';

Line: 457

        l_status_code_tbl.DELETE;

Line: 458

        l_status_meaning_tbl.DELETE;

Line: 462

	l_art_id_tbl.DELETE;

Line: 463

	l_art_ver_id_tbl.DELETE;

Line: 464

	l_art_title_tbl.DELETE;

Line: 465

	l_ver_from_status_tbl.DELETE;

Line: 528

        SELECT TMP.article_id, TMP.article_version_id, nvl(TMP.display_name, TMP.article_title),
        BVT.variable_name
        FROM OKC_BUS_VARIABLES_TL BVT,
            OKC_BUS_VARIABLES_B BVB,
            OKC_ARTICLE_VARIABLES AAV,
            OKC_ART_BLK_TEMP TMP
        WHERE TMP.id = cp_id
            AND TMP.adopt_asis_yn = 'N' --check not done for adopt as is clauses
            AND AAV.article_version_id = TMP.article_version_id
            AND BVB.variable_code = AAV.variable_code
            AND nvl(BVB.disabled_yn,'N') = 'Y'
            AND BVT.language = userenv('LANG')
            AND BVT.variable_code = BVB.variable_code;

Line: 544

        SELECT TMP.article_id, TMP.article_version_id, nvl(TMP.display_name, TMP.article_title),
        BVT.variable_name,
        nvl(FVS.flex_value_set_id, -99)
        FROM OKC_BUS_VARIABLES_TL BVT,
            FND_FLEX_VALUE_SETS FVS,
            OKC_BUS_VARIABLES_B BVB,
            OKC_ARTICLE_VARIABLES AAV,
            OKC_ART_BLK_TEMP TMP
        WHERE TMP.id = cp_id
            AND TMP.adopt_asis_yn = 'N' --check not done for adopt as is clauses
            AND AAV.article_version_id = TMP.article_version_id
            AND BVB.variable_code = AAV.variable_code
            AND BVB.variable_type = 'U'
            AND BVB.value_set_id = FVS.flex_value_set_id (+)
            AND BVT.language = userenv('LANG')
            AND nvl(BVB.MRV_FLAG,'N')='N'   -- Exempt MRV from validation
            AND BVT.variable_code = BVB.variable_code;

Line: 631

    l_art_id_tbl.DELETE;

Line: 632

	l_art_ver_id_tbl.DELETE;

Line: 633

	l_art_title_tbl.DELETE;

Line: 634

	l_var_name_tbl.DELETE;

Line: 635

	l_val_set_tbl.DELETE;

Line: 678

        SELECT TMP.article_id, TMP.article_version_id, nvl(TMP.display_name, TMP.article_title),
        TMP.article_type
        FROM OKC_ART_BLK_TEMP TMP
        WHERE TMP.id = cp_id
            AND TMP.adopt_asis_yn = 'N' --check not done for adopt as is clauses
        	AND NOT EXISTS (
                SELECT '1' from FND_LOOKUPS F
                WHERE F.lookup_type = 'OKC_SUBJECT'
                AND	  F.lookup_code =  TMP.article_type
                AND trunc(cp_date) BETWEEN trunc(nvl(F.start_date_active, cp_date)) AND
                                nvl(F.end_date_active, cp_date));

Line: 692

        SELECT TMP.article_id, TMP.article_version_id, nvl(TMP.display_name, TMP.article_title),
        TMP.default_section
        FROM OKC_ART_BLK_TEMP TMP
        WHERE TMP.id = cp_id
            AND TMP.adopt_asis_yn = 'N' --check not done for adopt as is clauses
            AND TMP.default_section <> 'UNASSIGNED'
        	AND NOT EXISTS (
                SELECT '1' from FND_LOOKUPS F
                WHERE F.lookup_type = 'OKC_ARTICLE_SECTION'
                AND	  F.lookup_code =  TMP.default_section
                AND trunc(cp_date) BETWEEN trunc(nvl(F.start_date_active, cp_date)) AND
                            nvl(F.end_date_active, cp_date));

Line: 772

    l_art_id_tbl.DELETE;

Line: 773

	l_art_ver_id_tbl.DELETE;

Line: 774

	l_art_title_tbl.DELETE;

Line: 775

	l_art_typ_tbl.DELETE;

Line: 776

	l_def_sec_tbl.DELETE;

Line: 809

PROCEDURE update_art_version_status_blk(
	p_id				    IN	NUMBER ,
	p_status				IN	VARCHAR2 ,
	x_return_status			OUT	NOCOPY VARCHAR2 )
IS

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

Line: 821

        okc_debug.log('100: Entering update_art_version_status_blk: p_id='||p_id||' p_status='||p_status);

Line: 828

        UPDATE OKC_ARTICLE_VERSIONS
		    SET
       			ARTICLE_STATUS              = p_status,
                -- date approved must also be updated
                DATE_APPROVED               = l_date,
                OBJECT_VERSION_NUMBER       = OBJECT_VERSION_NUMBER + 1,
                LAST_UPDATED_BY             = G_USER_ID,
                LAST_UPDATE_LOGIN           = G_LOGIN_ID,
                LAST_UPDATE_DATE            = l_date
            WHERE
                 ARTICLE_VERSION_ID IN
                    (SELECT article_version_id FROM OKC_ART_BLK_TEMP
                        WHERE id = p_id AND adopt_asis_yn = 'N');

Line: 843

       	UPDATE OKC_ARTICLE_VERSIONS
            SET
                ARTICLE_STATUS              = p_status,
                OBJECT_VERSION_NUMBER       = OBJECT_VERSION_NUMBER + 1,
                LAST_UPDATED_BY             = G_USER_ID,
                LAST_UPDATE_LOGIN           = G_LOGIN_ID,
                LAST_UPDATE_DATE            = l_date
            WHERE
                 ARTICLE_VERSION_ID IN
                    (SELECT article_version_id FROM OKC_ART_BLK_TEMP
                        WHERE id = p_id AND adopt_asis_yn = 'N');

Line: 858

        okc_debug.log('200: Leaving update_art_version_status_blk: Success');

Line: 866

            okc_debug.log('201: Leaving update_art_version_status_blk: Unknown Error');

Line: 873

END update_art_version_status_blk;

Line: 877

PROCEDURE update_adp_status_type_blk(
	p_id				    IN	NUMBER ,
    p_local_org_id          IN  NUMBER,
	p_adoption_status		IN	VARCHAR2 ,
	p_adoption_type			IN	VARCHAR2 ,
    p_type                  IN  VARCHAR2,
	x_return_status			OUT	NOCOPY VARCHAR2 )
IS

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

Line: 892

        okc_debug.log('100: Entering update_adp_status_type_blk: p_id='||p_id||' p_local_org_id='||p_local_org_id||' p_adoption_status='||p_adoption_status||' p_adoption_type='||p_adoption_type||' p_type='||p_type);

Line: 899

        UPDATE OKC_ARTICLE_ADOPTIONS
            SET
                ADOPTION_TYPE               = nvl(p_adoption_type, ADOPTION_TYPE),
                ADOPTION_STATUS             = nvl(p_adoption_status, ADOPTION_STATUS),
                OBJECT_VERSION_NUMBER       = OBJECT_VERSION_NUMBER + 1,
                LAST_UPDATED_BY             = G_USER_ID,
                LAST_UPDATE_LOGIN           = G_LOGIN_ID,
                LAST_UPDATE_DATE            = l_date
            WHERE
                GLOBAL_ARTICLE_VERSION_ID IN
                (SELECT article_version_id FROM OKC_ART_BLK_TEMP
                        WHERE id = p_id AND adopt_asis_yn = 'Y')
                AND LOCAL_ORG_ID = p_local_org_id;

Line: 915

        UPDATE OKC_ARTICLE_ADOPTIONS
            SET
                ADOPTION_TYPE               = nvl(p_adoption_type, ADOPTION_TYPE),
                ADOPTION_STATUS             = nvl(p_adoption_status, ADOPTION_STATUS),
                OBJECT_VERSION_NUMBER       = OBJECT_VERSION_NUMBER + 1,
                LAST_UPDATED_BY             = G_USER_ID,
                LAST_UPDATE_LOGIN           = G_LOGIN_ID,
                LAST_UPDATE_DATE            = l_date
            WHERE
                LOCAL_ARTICLE_VERSION_ID IN
                (SELECT article_version_id FROM OKC_ART_BLK_TEMP
                        WHERE id = p_id AND adopt_asis_yn = 'N' AND localized_yn = 'Y')
                AND LOCAL_ORG_ID = p_local_org_id;

Line: 941

        okc_debug.log('200: Leaving update_adp_status_type_blk: Success');

Line: 949

            okc_debug.log('201: Leaving update_adp_status_type_blk: Error');

Line: 955

            okc_debug.log('202: Leaving update_adp_status_type_blk: Unknown Error');

Line: 962

END update_adp_status_type_blk;

Line: 966

PROCEDURE update_prev_vers_enddate_blk(
	p_id				    IN	NUMBER ,
  	x_return_status			OUT	NOCOPY VARCHAR2 )

IS
    l_api_name			CONSTANT VARCHAR2(30) := 'update_prev_vers_enddate_blk';

Line: 975

        SELECT PREV.article_version_id, TMP.start_date
        FROM OKC_ART_BLK_TEMP TMP,
            OKC_ARTICLE_VERSIONS PREV
        WHERE TMP.id = cp_id AND
            TMP.adopt_asis_yn = 'N' AND
            PREV.article_id = TMP.article_id AND
            PREV.article_version_id <> TMP.article_version_id AND
            PREV.start_date = (SELECT max(VER.start_date)
                                FROM OKC_ARTICLE_VERSIONS VER
                                WHERE VER.article_id = TMP.article_id AND
                                    VER.article_version_id <> TMP.article_version_id)
            AND PREV.end_date IS NULL;

Line: 997

        okc_debug.log('100: Entering update_prev_vers_enddate_blk: p_id='||p_id);

Line: 1012

			UPDATE OKC_ARTICLE_VERSIONS
			SET
				END_DATE = l_start_date_tbl(i) - l_one_sec,
				OBJECT_VERSION_NUMBER	= OBJECT_VERSION_NUMBER + 1,
				LAST_UPDATED_BY			= G_USER_ID,
				LAST_UPDATE_LOGIN		= G_LOGIN_ID,
				LAST_UPDATE_DATE		= l_date
			WHERE
				ARTICLE_VERSION_ID = l_prev_ver_id_tbl(i);

Line: 1023

	l_prev_ver_id_tbl.DELETE;

Line: 1024

	l_start_date_tbl.DELETE;

Line: 1027

        okc_debug.log('200: Leaving update_prev_vers_enddate_blk: Success');

Line: 1038

            okc_debug.log('201: Leaving update_prev_vers_enddate_blk: Unknown Error');

Line: 1045

END update_prev_vers_enddate_blk;

Line: 1076

	INSERT INTO OKC_ARTICLE_RELATNS_ALL
    (
        SOURCE_ARTICLE_ID,
        TARGET_ARTICLE_ID,
        ORG_ID,
        RELATIONSHIP_TYPE,
        OBJECT_VERSION_NUMBER,
        CREATED_BY,
        CREATION_DATE,
        LAST_UPDATED_BY,
        LAST_UPDATE_LOGIN,
        LAST_UPDATE_DATE
    )
	SELECT REL.source_article_id,
        REL.target_article_id,
        TMP.org_id,
        REL.relationship_type,
        1.0,
        G_USER_ID,
        l_date,
        G_USER_ID,
        G_LOGIN_ID,
        l_date
		FROM OKC_ART_BLK_TEMP TMP, OKC_ARTICLE_RELATNS_ALL REL
		WHERE TMP.id = p_id AND
            REL.org_id = G_GLOBAL_ORG_ID AND
            REL.source_article_id = TMP.article_id AND
			EXISTS
				(SELECT 1 FROM OKC_ARTICLE_VERSIONS AV1, OKC_ARTICLE_ADOPTIONS ADP
					WHERE AV1.article_id = REL.target_article_id AND
					ADP.global_article_version_id = AV1.article_version_id AND
					ADP.local_org_id = TMP.org_id AND
					ADP.adoption_type = 'ADOPTED')
			AND NOT EXISTS
				(SELECT 1 FROM OKC_ARTICLE_RELATNS_ALL ARL1
                      WHERE REL.source_article_id = ARL1.source_article_id AND
                      REL.target_article_id = ARL1.target_article_id AND
                      REL.relationship_type = ARL1.relationship_type AND
                      ARL1.org_id = TMP.org_id);

Line: 1116

	INSERT INTO OKC_ARTICLE_RELATNS_ALL
    (
        SOURCE_ARTICLE_ID,
        TARGET_ARTICLE_ID,
        ORG_ID,
        RELATIONSHIP_TYPE,
        OBJECT_VERSION_NUMBER,
        CREATED_BY,
        CREATION_DATE,
        LAST_UPDATED_BY,
        LAST_UPDATE_LOGIN,
        LAST_UPDATE_DATE
    )
	SELECT REL.source_article_id,
        REL.target_article_id,
        TMP.org_id,
        REL.relationship_type,
        1.0,
        G_USER_ID,
        l_date,
        G_USER_ID,
        G_LOGIN_ID,
        l_date
		FROM OKC_ART_BLK_TEMP TMP, OKC_ARTICLE_RELATNS_ALL REL
		WHERE TMP.id = p_id AND
            REL.org_id = G_GLOBAL_ORG_ID AND
            REL.target_article_id = TMP.article_id AND
			EXISTS
				(SELECT 1 FROM OKC_ARTICLE_VERSIONS AV1, OKC_ARTICLE_ADOPTIONS ADP
					WHERE AV1.article_id = REL.source_article_id AND
					ADP.global_article_version_id = AV1.article_version_id AND
					ADP.local_org_id = TMP.org_id AND
					ADP.adoption_type = 'ADOPTED')
			AND NOT EXISTS
				(SELECT 1 FROM OKC_ARTICLE_RELATNS_ALL ARL1
                      WHERE REL.source_article_id = ARL1.source_article_id AND
                      REL.target_article_id = ARL1.target_article_id AND
                      REL.relationship_type = ARL1.relationship_type AND
                      ARL1.org_id = TMP.org_id);

Line: 1178

PROCEDURE delete_relationships_blk(
	p_id				    IN	NUMBER ,
    p_org_id                IN  NUMBER,
	x_return_status			OUT	NOCOPY VARCHAR2 )
IS

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

Line: 1192

	SELECT article_id, article_version_id
    	FROM OKC_ART_BLK_TEMP TMP
	    WHERE	TMP.id = cp_id AND TMP.adopt_asis_yn = 'Y';

Line: 1199

        okc_debug.log('100: Entering delete_relationships_blk: p_id='||p_id||' p_org_id='||p_org_id);

Line: 1204

	-- get the article id's first, makes the NOT EXISTS clause in Delete below, less costly
	OPEN l_art_id_csr(p_id);

Line: 1219

	-- delete where source article has not been adopted
   	FORALL i IN l_art_id_tbl.FIRST..l_art_id_tbl.LAST
    	DELETE FROM OKC_ARTICLE_RELATNS_ALL
		WHERE	org_id = p_org_id
		AND source_article_id = l_art_id_tbl(i)
		AND NOT EXISTS
			( SELECT 1 FROM OKC_ARTICLE_ADOPTIONS ADP, OKC_ARTICLE_VERSIONS AV1
				WHERE AV1.article_id = l_art_id_tbl(i)
				AND AV1.article_version_id <> l_art_ver_id_tbl(i)
				AND ADP.adoption_type = 'ADOPTED'
				AND ADP.global_article_version_id = AV1.article_version_id
                AND ADP.local_org_id = p_org_id
			);

Line: 1233

	-- delete where target article has not been adopted
	FORALL i IN l_art_id_tbl.FIRST..l_art_id_tbl.LAST
		DELETE FROM OKC_ARTICLE_RELATNS_ALL
		WHERE	org_id = p_org_id
		AND target_article_id = l_art_id_tbl(i)
		AND NOT EXISTS
			( SELECT 1 FROM OKC_ARTICLE_ADOPTIONS ADP, OKC_ARTICLE_VERSIONS AV2
				WHERE AV2.article_id = l_art_id_tbl(i)
				AND AV2.article_version_id <> l_art_ver_id_tbl(i)
				AND ADP.adoption_type = 'ADOPTED'
				AND ADP.global_article_version_id = AV2.article_version_id
                AND ADP.local_org_id = p_org_id
			);

Line: 1247

	l_art_id_tbl.DELETE;

Line: 1248

    l_art_ver_id_tbl.DELETE;

Line: 1251

        okc_debug.log('200: Leaving delete_relationships_blk: Success');

Line: 1263

            okc_debug.log('201: Leaving delete_relationships_blk: Unknown error');

Line: 1270

END delete_relationships_blk;

Line: 1318

        SELECT TMP.article_id,  TMP.article_version_id, nvl(TMP.display_name, TMP.article_title)
        FROM OKC_ART_BLK_TEMP TMP
        WHERE TMP.id = cp_id AND
            TMP.adopt_asis_yn = 'Y';

Line: 1490

        l_adopt_asis_art_id_tbl.DELETE;

Line: 1491

        l_adopt_asis_art_ver_id_tbl.DELETE;

Line: 1492

	    l_adopt_asis_art_title_tbl.DELETE;

Line: 1582

        SELECT ORG.organization_id, decode(ORG.org_information1, 'Y', 'ADOPTED', 'AVAILABLE')
        FROM HR_ORGANIZATION_INFORMATION ORG
        WHERE ORG.org_information_context = 'OKC_TERMS_LIBRARY_DETAILS' AND
            ORG.organization_id <> cp_global_org_id;

Line: 1590

        SELECT TMP.article_version_id , ART.org_id
        FROM OKC_ART_BLK_TEMP TMP, OKC_ARTICLES_ALL ART
        WHERE TMP.id = cp_id AND
            TMP.global_yn = 'Y' AND
            ART.article_title = TMP.article_title AND
            ART.org_id <> cp_global_org_id ;

Line: 1639

	-- first insert rows in adoptions table for each clause and local org
	FORALL i IN l_org_id_tbl.FIRST.. l_org_id_tbl.LAST
		INSERT INTO OKC_ARTICLE_ADOPTIONS
			(
			GLOBAL_ARTICLE_VERSION_ID,
			ADOPTION_TYPE,
			LOCAL_ORG_ID,
			ADOPTION_STATUS,
			LOCAL_ARTICLE_VERSION_ID,
			OBJECT_VERSION_NUMBER,
			CREATED_BY,
			CREATION_DATE,
			LAST_UPDATED_BY,
			LAST_UPDATE_LOGIN,
			LAST_UPDATE_DATE
			)
			SELECT TMP.article_version_id, l_adp_typ_tbl(i), l_org_id_tbl(i),
                decode(l_adp_typ_tbl(i), 'ADOPTED', 'APPROVED', NULL),
				NULL, 1, G_USER_ID, l_date,
				G_USER_ID, G_LOGIN_ID, l_date
			FROM OKC_ART_BLK_TEMP TMP
			WHERE TMP.id = p_id AND
                TMP.global_yn = 'Y';

Line: 1675

		-- need to update adoption type to 'AVAILABLE', status to NULL

		FORALL i IN l_non_uniq_art_ver_tbl.FIRST..l_non_uniq_art_ver_tbl.LAST
			UPDATE OKC_ARTICLE_ADOPTIONS
			SET
				ADOPTION_TYPE = 'AVAILABLE',
				ADOPTION_STATUS = NULL
			WHERE
			GLOBAL_ARTICLE_VERSION_ID = l_non_uniq_art_ver_tbl(i)
			AND LOCAL_ORG_ID =  l_non_uniq_org_id_tbl(i);

Line: 1692

    INSERT INTO OKC_ART_BLK_TEMP
        (
        ID,
        ARTICLE_ID,
        ARTICLE_VERSION_ID,
        ORG_ID
        )
        SELECT l_id, TMP.article_id, TMP.article_version_id, ADP.local_org_id
        FROM OKC_ART_BLK_TEMP TMP, OKC_ARTICLE_ADOPTIONS ADP
        WHERE TMP.id = p_id AND
            TMP.global_yn = 'Y' AND
            ADP.global_article_version_id = TMP.article_version_id AND
            ADP.adoption_type = 'ADOPTED';

Line: 1716

    l_non_uniq_art_ver_tbl.DELETE;

Line: 1717

	l_non_uniq_org_id_tbl.DELETE;

Line: 1719

	l_org_id_tbl.DELETE;

Line: 1720

	l_adp_typ_tbl.DELETE;

Line: 1930

    update_art_version_status_blk(
        p_id    		=> l_id,
        p_status		=> 'PENDING_APPROVAL',
        x_return_status	=> x_return_status
        );

Line: 1947

            update_adp_status_type_blk(
                p_id			    => l_id,
                p_local_org_id		=> p_org_id,
                p_adoption_status	=> 'PENDING_APPROVAL',
                p_adoption_type		=> 'LOCALIZED',
                p_type              => 'LOCALIZED',
                x_return_status		=> x_return_status
                );

Line: 1964

	    	-- 1. Update Adoption Row
			-- 2. Insert Relationships if first version of any articles are being adopted.

            update_adp_status_type_blk(
                p_id			    => l_id,
                p_local_org_id		=> p_org_id,
                p_adoption_status	=> 'PENDING_APPROVAL',
                p_adoption_type		=> 'ADOPTED',
                p_type              => 'ADOPTED',
                x_return_status		=> x_return_status
                );

Line: 1984

            INSERT INTO OKC_ART_BLK_TEMP
                (
                ID,
                ARTICLE_ID,
                ARTICLE_VERSION_ID,
                ORG_ID
                )
                SELECT l_rel_id, TMP.article_id, TMP.article_version_id, p_org_id
                FROM OKC_ART_BLK_TEMP TMP
                WHERE TMP.id = l_id AND
                    TMP.adopt_asis_yn = 'Y';

Line: 2011

    DELETE FROM OKC_ART_BLK_TEMP
        WHERE id IN (l_id, l_rel_id);

Line: 2156

    update_art_version_status_blk(
        p_id    		=> l_id,
        p_status		=> 'APPROVED',
        x_return_status	=> x_return_status
        );

Line: 2169

    update_prev_vers_enddate_blk(
	    p_id				    => l_id,
  	    x_return_status			=> x_return_status
        );

Line: 2207

		    -- we are adopting some article versions as is, so update Adoption Row

            update_adp_status_type_blk(
                p_id			    => l_id,
                p_local_org_id		=> p_org_id,
                p_adoption_status	=> 'APPROVED',
                p_adoption_type		=> 'ADOPTED',
                p_type              => 'ADOPTED',
                x_return_status		=> x_return_status
                );

Line: 2226

		    -- if some articles are localized update adoption row

            update_adp_status_type_blk(
                p_id			    => l_id,
                p_local_org_id		=> p_org_id,
                p_adoption_status	=> 'APPROVED',
                p_adoption_type		=> 'LOCALIZED',
                p_type              => 'LOCALIZED',
                x_return_status		=> x_return_status
                );

Line: 2248

    DELETE FROM OKC_ART_BLK_TEMP
        WHERE id = l_id;

Line: 2392

    update_art_version_status_blk(
        p_id    		=> l_id,
        p_status		=> 'REJECTED',
        x_return_status	=> x_return_status
        );

Line: 2410

			-- 1. Update Adoption Row
			-- 2. Delete relationship rows if the first version is being rejected

            update_adp_status_type_blk(
                p_id			    => l_id,
                p_local_org_id		=> p_org_id,
                p_adoption_status	=> 'REJECTED',
                p_adoption_type		=> 'AVAILABLE',
                p_type              => 'ADOPTED',
                x_return_status		=> x_return_status
                );

Line: 2427

            delete_relationships_blk(
                p_id    			=> l_id,
                p_org_id            => p_org_id,
                x_return_status		=> x_return_status
                );

Line: 2441

		    -- update adoption row if localized

            update_adp_status_type_blk(
                p_id			    => l_id,
                p_local_org_id		=> p_org_id,
                p_adoption_status	=> 'REJECTED',
                p_adoption_type		=> 'LOCALIZED',
                p_type              => 'LOCALIZED',
                x_return_status		=> x_return_status
                );

Line: 2463

    DELETE FROM OKC_ART_BLK_TEMP
        WHERE id = l_id;