Rem drv: <create type="pkgbodys" pos="ecm/patch/plsql/patch_pkgbodys.sql+"/>
Rem
Rem $Header: swlib_pkgbody.sql 25-jul-2007.05:51:01 kashukla Exp $
Rem
Rem swlib_pkgbody.sql
Rem
Rem Copyright (c) 2004, 2006, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      swlib_pkgbody.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    kashukla    07/25/07 - 
Rem    sacgoel     09/06/06 - checking for null condition using "IS NULL
Rem    rattipal    09/13/06 - Backport sacgoel_net2 from main
Rem    kashukla    08/29/05 - fixing bug-4535569 
Rem    kashukla    08/26/05 - modifying update_entity 
Rem    wsmit       08/01/05 - purging
Rem    kashukla    07/25/05 - 
Rem    gsbhatia    07/01/05 - New repmgr header impl 
Rem    wsmit       06/14/05 - sql inject issues 
Rem    kashukla    04/01/05 - inclusion of revision parameter in revision record type 
Rem    wsmit       02/18/05 - delete of version in directory 
Rem    wsmit       01/20/04 - ensure reference order
Rem    kashukla    01/19/05 - edited the function update_entity() 
Rem    kashukla    12/20/04 - deleting references while deleting an entity 
Rem    kashukla    12/08/04 - adding procedure delete_entity 
Rem    kashukla    12/07/04 - edit the insert_reference() to include the 
Rem                           target_revision_info correctly 
Rem    kashukla    11/23/04 - to edit the procedure update_entity 
Rem    wsmit       10/14/04 - add place to store root filepath 
Rem    wsmit       09/23/04 - 
Rem    wsmit       09/10/04 - documents
Rem    wsmit       08/26/04 - add directory 
Rem    rmadampa    07/25/04 - 
Rem    rmadampa    07/22/04 - rmadampa_swlib_sql_scripts
Rem    rmadampa    07/19/04 - Created
Rem

CREATE OR REPLACE PACKAGE BODY MGMT_SWLIB AS

SUCCESS_RET CONSTANT INTEGER := 0;
ERROR_RET CONSTANT INTEGER := 1;
ENTITY_EXISTS_ERR CONSTANT INTEGER := 2;
ENTITY_DOES_NOT_EXIST_ERR CONSTANT INTEGER := 3;
DIRECTORY_DOES_NOT_EXIST_ERR CONSTANT INTEGER := 4;
REFERENCE_EXISTS_ERR CONSTANT INTEGER := 5;
DIRECTORY_EXISTS_ERR CONSTANT INTEGER :=6;

MAX_MAJOR_VER CONSTANT VARCHAR2(2) := '99';
MAX_MINOR_VER CONSTANT VARCHAR2(1) := '9';
NEW_ENTITY_VER CONSTANT VARCHAR2(3) := '0.0';

CURRENT_REVISION CONSTANT NUMBER(1) := 1;
NON_CURRENT_REVISION CONSTANT NUMBER(1) := 0;

PRODUCTION_VERSION CONSTANT NUMBER(1) := 1;
CURRENT_VERSION CONSTANT NUMBER(1) := 0;
PRODUCTION_MATURITY CONSTANT VARCHAR2(16) := 'PRODUCTION';
NEW_REVISION CONSTANT NUMBER(1) := 1;

CRLF CONSTANT VARCHAR2(5) := CHR(13)||CHR(10);
-----------------------------------------------------------
ASSERTFAIL     EXCEPTION;
ASSERTFAIL_C   CONSTANT INTEGER := -20999;
PRAGMA EXCEPTION_INIT(ASSERTFAIL, -20999);
PKGNAME_C      CONSTANT VARCHAR2(20) := 'MGMT_SWLIB';
PROCEDURE assert (bool_IN IN BOOLEAN
                 ,msg_IN IN VARCHAR2 := null);
-----------------------------------------------------------
PROCEDURE insert_revision_i(p_revision_rec    IN MGMT_SWLIB_REVISION_RECORD,
                            p_entity_id       IN RAW,
                            p_revision        OUT VARCHAR2,
                            p_revision_id     OUT RAW,
                            p_result          OUT NUMBER,--success/error code
                            p_err_desc        OUT VARCHAR2);

PROCEDURE update_revision(p_revision_rec   IN MGMT_SWLIB_REVISION_RECORD,
                          p_entity_id      IN RAW,
                          p_reference_list IN MGMT_SWLIB_REFERENCE_LIST,
			  p_en_is_dirty	   IN NUMBER,
			  p_revision	   OUT VARCHAR2,
			  p_revision_id    OUT RAW,
                          p_result         OUT NUMBER,--success/error code
                          p_err_desc       OUT VARCHAR2);

PROCEDURE insert_reference(p_reference      IN MGMT_SWLIB_REFERENCE_RECORD,
                           p_target_rev_id  IN RAW,
                           p_index          IN NUMBER,
                           p_revision_id    IN RAW);
                           
PROCEDURE update_reference(p_reference      IN MGMT_SWLIB_REFERENCE_RECORD,
                           p_target_rev_id  IN RAW,
                           p_revision_id    IN RAW,
                           p_reference_id   IN RAW);

PROCEDURE add_revision_documents(p_revision    IN MGMT_SWLIB_REVISION_RECORD,
                                 p_revision_id IN RAW);

PROCEDURE modify_documents(p_reference      IN MGMT_SWLIB_REFERENCE_RECORD,
                           p_revision_id    IN RAW,
                           p_reference_id   IN RAW,
                           p_new_ref        IN NUMBER);

PROCEDURE insert_document(p_ref_document      IN MGMT_SWLIB_DOCUMENT_RECORD,
                          p_revision_id       IN RAW,
                          p_reference_id      IN RAW);

PROCEDURE update_document(p_ref_document      IN MGMT_SWLIB_DOCUMENT_RECORD,
                          p_revision_id       IN RAW,
                          p_reference_id      IN RAW);
                          
PROCEDURE insert_platforms(p_platforms MGMT_SWLIB_PLATFORM_LIST, 
                           p_entity_id RAW);
                           
PROCEDURE update_platforms(p_platforms MGMT_SWLIB_PLATFORM_LIST, 
                           p_entity_id RAW);

PROCEDURE insert_rev_parameters(p_params MGMT_SWLIB_PARAMS_LIST, 
                            p_revision_id RAW);

PROCEDURE insert_parameters(p_params MGMT_SWLIB_PARAMS_LIST, 
                            p_entity_id RAW);

PROCEDURE update_rev_parameters(p_params MGMT_SWLIB_PARAMS_LIST, 
                            p_revision_id RAW);
                           
PROCEDURE update_parameters(p_params MGMT_SWLIB_PARAMS_LIST, 
                            p_entity_id RAW);
----------------------------------------------------------------------
--
--Name: get_major_version
--Package: mgmt_swlib
--Purpose: Extract the major version from a version string mm.n
--IN Parameters:
-- curr_ver  VARCHAR2  the version string
--RETURN Value:
-- VARCHAR2  the major version of the version string
--ERROR Codes:
--Notes:
--
FUNCTION get_major_version(curr_ver VARCHAR2)
RETURN VARCHAR2
IS
maj_ver VARCHAR2(2);
dot_index NUMBER(1);
BEGIN
    --find the dot
    dot_index := instr(curr_ver, '.');
    IF dot_index > 0 THEN
        IF dot_index = 1 THEN --first is dot
            maj_ver := '0';
        ELSE -- dot after first digit
            maj_ver := substr(curr_ver, 1, dot_index-1);
        END IF;
    ELSE
        --no dot, only major
        maj_ver := curr_ver;
    END IF;
    RETURN maj_ver;
END get_major_version;


--
--Name: get_minor_version
--Package: mgmt_swlib
--Purpose: Extract the minor version from a version string mm.n
--IN Parameters:
-- curr_ver  VARCHAR2  the version string
--RETURN Value:
-- VARCHAR2  the minor version of the version string
--ERROR Codes:
--Notes:
--
FUNCTION get_minor_version(curr_ver VARCHAR2)
RETURN VARCHAR2
IS
min_ver VARCHAR2(1);
dot_index NUMBER(1);
BEGIN
    --find the dot
    dot_index := instr(curr_ver, '.');
    IF dot_index > 0 THEN --there is a dot
        min_ver := substr(curr_ver, dot_index+1);
    ELSE
        --no dot, only major
        min_ver := '0';
    END IF;
    RETURN min_ver;
END get_minor_version;


--
--Name: get_next_version
--Package: mgmt_swlib
--Purpose: Generate the next version for a given version string 
--          of the form mm.n. Rolls over to version 0.1 on maxing
--          out at 99.9.
--IN Parameters:
-- curr_ver  VARCHAR2  the version string
--RETURN Value:
-- VARCHAR2  the next version of specified version string
--ERROR Codes:
-- -1 for invalid input
--Notes:
--
FUNCTION get_next_version(curr_ver VARCHAR2)
RETURN VARCHAR2
IS
maj_ver VARCHAR2(2);
min_ver VARCHAR2(1);
next_ver VARCHAR2(4);
BEGIN
    IF length(curr_ver) > 4 OR curr_ver IS NULL THEN
        --DBMS_OUTPUT.PUT_LINE('Invalid input found for curr_ver=' || curr_ver);
        RETURN '-1';
    END IF;
    
    maj_ver := get_major_version(curr_ver);
    min_ver := get_minor_version(curr_ver);
    IF min_ver = MAX_MINOR_VER THEN 
        -- after 99.9, next version is 0.1
        IF maj_ver = MAX_MAJOR_VER THEN 
            maj_ver := '0';
            min_ver := '1';
        ELSE
            maj_ver := maj_ver + 1;
            min_ver := '0';
        END IF;
    ELSE
        min_ver := min_ver + 1;
    END IF;

    next_ver := maj_ver || '.' || min_ver;
    RETURN next_ver;
END get_next_version;

PROCEDURE get_current_entity_version(entityId raw)
AS
TYPE cur_typ IS REF CURSOR;
c cur_typ;
query_str VARCHAR2(1000);
version VARCHAR2(64);
next_ver VARCHAR2(16);
BEGIN
    query_str := 'SELECT version FROM MGMT_SWLIB_ENTITY_VERSIONS' 
              || ' WHERE entity_id = :entityId'
              || ' AND is_current = 1';
    OPEN c FOR query_str USING entityId;
    LOOP
        FETCH c INTO version;
        EXIT WHEN c%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('Current version is '||version);
        next_ver := get_next_version(version);
        DBMS_OUTPUT.PUT_LINE('Next version is '||next_ver);
    END LOOP;
    CLOSE c;    
END get_current_entity_version;

PROCEDURE test_gnv(v VARCHAR2)
AS
inp_ver VARCHAR2(10);
exp_ver VARCHAR2(10);
ver VARCHAR2(10);
errMsgs VARCHAR2(1000);
BEGIN
errMsgs := ' ';
inp_ver := '.0';
exp_ver := '0.1';
ver := get_next_version(inp_ver);
--DBMS_OUTPUT.PUT_LINE('Next version for .0 is ' || ver);
if ver <> exp_ver then
    errMsgs := errMsgs || 'Input ver=' ||inp_ver||', Exp ver='||exp_ver||', Actual ver='||ver||CRLF;
end if;

inp_ver := '0.0';
exp_ver := '0.1';
ver := get_next_version(inp_ver);
--DBMS_OUTPUT.PUT_LINE('Next version for 0.0 is ' || ver);
if ver <> exp_ver then
    errMsgs := errMsgs || 'Input ver=' ||inp_ver||', Exp ver='||exp_ver||', Actual ver='||ver||CRLF;
end if;

inp_ver := '0.1';
exp_ver := '0.2';
ver := get_next_version(inp_ver);
--DBMS_OUTPUT.PUT_LINE('Next version for 0.1 is ' || ver);
if ver <> exp_ver then
    errMsgs := errMsgs || 'Input ver=' ||inp_ver||', Exp ver='||exp_ver||', Actual ver='||ver||CRLF;
end if;

inp_ver := '0.9';
exp_ver := '1.0';
ver := get_next_version(inp_ver);
--DBMS_OUTPUT.PUT_LINE('Next version for 0.9 is ' || ver);
if ver <> exp_ver then
    errMsgs := errMsgs || 'Input ver=' ||inp_ver||', Exp ver='||exp_ver||', Actual ver='||ver||CRLF;
end if;

inp_ver := '0.8';
exp_ver := '0.9';
ver := get_next_version(inp_ver);
--DBMS_OUTPUT.PUT_LINE('Next version for .8 is ' || ver);
if ver <> exp_ver then
    errMsgs := errMsgs || 'Input ver=' ||inp_ver||', Exp ver='||exp_ver||', Actual ver='||ver||CRLF;
end if;

inp_ver := '0.7';
exp_ver := '0.8';
ver := get_next_version(inp_ver);
--DBMS_OUTPUT.PUT_LINE('Next version for 0.7 is ' || ver);
if ver <> exp_ver then
    errMsgs := errMsgs || 'Input ver=' ||inp_ver||', Exp ver='||exp_ver||', Actual ver='||ver||CRLF;
end if;

inp_ver := '12.5';
exp_ver := '12.6';
ver := get_next_version(inp_ver);
--DBMS_OUTPUT.PUT_LINE('Next version for 12.5 is ' || ver);
if ver <> exp_ver then
    errMsgs := errMsgs || 'Input ver=' ||inp_ver||', Exp ver='||exp_ver||', Actual ver='||ver||CRLF;
end if;

inp_ver := '99.9';
exp_ver := '0.1';
ver := get_next_version(inp_ver);
--DBMS_OUTPUT.PUT_LINE('Next version for 99.9 is ' || ver);
if ver <> exp_ver then
    errMsgs := errMsgs || 'Input ver=' ||inp_ver||', Exp ver='||exp_ver||', Actual ver='||ver||CRLF;
end if;

inp_ver := '1.989';
exp_ver := '-1';
ver := get_next_version(inp_ver);
--DBMS_OUTPUT.PUT_LINE('Next version for 1.989 is ' || ver);
if ver <> exp_ver then
    errMsgs := errMsgs || 'Input ver=' ||inp_ver||', Exp ver='||exp_ver||', Actual ver='||ver||CRLF;
end if;

inp_ver := '111.9';
exp_ver := '-1';
ver := get_next_version(inp_ver);
--DBMS_OUTPUT.PUT_LINE('Next version for 111.9 is ' || ver);
if ver <> exp_ver then
    errMsgs := errMsgs || 'Input ver=' ||inp_ver||', Exp ver='||exp_ver||', Actual ver='||ver||CRLF;
end if;

inp_ver := '';
exp_ver := '-1';
ver := get_next_version(inp_ver);
--DBMS_OUTPUT.PUT_LINE('Next version for - is ' || ver);
if ver <> exp_ver then
    errMsgs := errMsgs || 'Input ver=' ||inp_ver||', Exp ver='||exp_ver||', Actual ver='||ver||CRLF;
end if;

if errMsgs <> ' ' then
    DBMS_OUTPUT.PUT_LINE('FOLLOWING ERRORS FOUND.....');
    DBMS_OUTPUT.PUT_LINE(errMsgs);
end if;

END test_gnv;

PROCEDURE insert_entity(p_entity            IN MGMT_SWLIB_ENTITY_RECORD,
                        p_entity_id_hex     OUT VARCHAR2,
                        p_revision_id_hex   OUT VARCHAR2,
                        p_revision          OUT VARCHAR2,
                        p_result            OUT NUMBER,--success/error code
                        p_err_desc          OUT VARCHAR2)
IS
cnt INTEGER                                     := 0;
l_platforms mgmt_swlib_platform_list            := p_entity.platform_info;
l_params mgmt_swlib_params_list                 := p_entity.params_info;
l_revision_rec mgmt_swlib_revision_record       := p_entity.revision_info;
l_entity_id mgmt_swlib_entities.entity_id%TYPE  := null;
l_directory_id mgmt_swlib_directories.directory_id%TYPE := p_entity.directory_id;
l_revision_id mgmt_swlib_entity_revisions.revision_id%TYPE := null;
BEGIN
    p_err_desc := null;
    p_result := SUCCESS_RET;
    -- Validate that there is no other entity with the same name
    SELECT COUNT(1) INTO cnt 
    FROM mgmt_swlib_entities mse, mgmt_swlib_entity_revisions mser
    WHERE mse.name=p_entity.name AND mse.entity_id = mser.entity_id AND rawtohex(mse.directory_id) = l_directory_id
    AND mser.status != 2;--upper case check??
    --how do we get a set of error codes allocated for swlib pl/sql code??
    IF cnt > 0 
    THEN
        p_result := ENTITY_EXISTS_ERR;
        p_err_desc := 'An entity with the specified name already exists';
        RETURN;
    END IF;
    
    INSERT INTO mgmt_swlib_entities(name, owner, description, type, 
        directory_id, vendor) 
    VALUES(p_entity.name, p_entity.owner, p_entity.description, 
        p_entity.type, hextoraw(p_entity.directory_id), p_entity.vendor) 
    RETURNING entity_id INTO l_entity_id;
    
    p_entity_id_hex := rawtohex(l_entity_id);
    DBMS_OUTPUT.PUT_LINE('EntityId is ' || rawtohex(p_entity_id_hex));
    
    --insert platforms
    insert_platforms(l_platforms, l_entity_id);
    
    --insert the parameter name value pairs
    insert_parameters(l_params, l_entity_id);
    
    --insert a new revision for this entity
    insert_revision_i(l_revision_rec, l_entity_id, p_revision, 
                      l_revision_id, p_result, p_err_desc);
    
    IF l_revision_id IS NULL
    THEN
        RETURN;
    END IF;
    p_revision_id_hex := rawtohex(l_revision_id);
    
END insert_entity;

PROCEDURE update_entity(p_entity            IN MGMT_SWLIB_ENTITY_RECORD,
			p_reference_list    IN MGMT_SWLIB_REFERENCE_LIST,
			p_en_is_dirty	    IN NUMBER,
			p_revision	    OUT VARCHAR2,
                        p_revision_id_hex   OUT VARCHAR2,
                        p_result            OUT NUMBER,--success/error code
                        p_err_desc          OUT VARCHAR2)
IS
cnt INTEGER                                     := 0;
l_platforms MGMT_SWLIB_PLATFORM_LIST            := p_entity.platform_info;
l_params MGMT_SWLIB_PARAMS_LIST                 := p_entity.params_info;
l_revision_rec mgmt_swlib_revision_record       := p_entity.revision_info;
l_entity_id MGMT_SWLIB_ENTITIES.ENTITY_ID%TYPE  := null;
l_revision_id mgmt_swlib_entity_revisions.revision_id%TYPE := null;
BEGIN
    p_err_desc := null;
    p_result := SUCCESS_RET;
DBMS_OUTPUT.PUT_LINE('Entity id '||p_entity.entity_id);
    l_entity_id := hextoraw(p_entity.entity_id);
    -- Validate that the entity exists
    SELECT COUNT(1) INTO cnt
    FROM mgmt_swlib_entities en, mgmt_swlib_entity_revisions er
    where en.entity_id = p_entity.entity_id and 
        en.entity_id = er.entity_id and er.status <> 2;
    IF cnt = 0
    THEN
        p_result := ENTITY_DOES_NOT_EXIST_ERR;
        p_err_desc := 'Entity doenot exist';
        RETURN;
    END IF;

    -- Validate that the name is not updated to an existing entity in the same directory
    SELECT COUNT(1) INTO cnt 
    FROM mgmt_swlib_entities en, mgmt_swlib_entity_revisions er
    where en.name = p_entity.name and en.entity_id <> p_entity.entity_id and 
          en.entity_id = er.entity_id and er.status <> 2 and 
          (en.directory_id = p_entity.directory_id or (en.directory_id is null and p_entity.directory_id is null));  
    IF cnt = 1 
    THEN
        p_result := ENTITY_DOES_NOT_EXIST_ERR;
        p_err_desc := 'Cannot rename an entity to an existing entity name';
        RETURN;
    END IF;
    
    UPDATE mgmt_swlib_entities
    SET name = p_entity.name, 
        description = p_entity.description, 
        type = p_entity.type, 
        directory_id = hextoraw(p_entity.directory_id),
        vendor = p_entity.vendor
    WHERE entity_id = l_entity_id;
    assert(SQL%ROWCOUNT = 1, 'Entity must exist');
    
    --Update the platforms assoc with the entity
    update_platforms(l_platforms, l_entity_id);
    
    --Update the parameters assoc with the entity
    update_parameters(l_params, l_entity_id);
    
    --update the existing revision record
    update_revision(l_revision_rec, l_entity_id, 
                    p_reference_list, p_en_is_dirty, p_revision, l_revision_id, p_result, 
                    p_err_desc);
    p_revision_id_hex := rawtohex(l_revision_id);
    
END update_entity;


PROCEDURE delete_entity(p_entity_id	IN VARCHAR2,
			p_entity_rev	IN VARCHAR2,
			p_result	OUT NUMBER,
			p_err_desc	OUT VARCHAR2)
IS
cnt		NUMBER					     := 0;
max_revision	mgmt_swlib_entity_revisions.revision%TYPE    := null;
l_is_current	mgmt_swlib_entity_revisions.is_current%TYPE  := 0;
l_reference_id  mgmt_swlib_entity_revisions.revision_id%TYPE := null;
CURSOR C1 
IS
	SELECT revision_id 
	FROM mgmt_swlib_entity_revisions
	WHERE RAWTOHEX(entity_id) = p_entity_id;

BEGIN
    p_result := SUCCESS_RET;
    p_err_desc := null;

    if (p_entity_rev is null) then	
	SELECT count(1) into cnt 
	FROM mgmt_swlib_entities
	WHERE RAWTOHEX(entity_id) = p_entity_id ;

	if (cnt > 0) then
	    UPDATE mgmt_swlib_entity_revisions
	    SET is_current = 0, status = 2
	    WHERE RAWTOHEX(entity_id) = p_entity_id;


	    --set the directory id to null

	    UPDATE mgmt_swlib_entities 
	    SET directory_id = null
	    WHERE RAWTOHEX(entity_id) = p_entity_id;


	    --delete all references
	    OPEN C1;
	    LOOP
		FETCH C1 INTO l_reference_id;
		EXIT when C1%notfound;
		DELETE FROM mgmt_swlib_entity_references
		WHERE source_id = l_reference_id;
		
	    END LOOP;
	    CLOSE C1;
	
	    --delete all references which are referring to this entity
	    DELETE FROM mgmt_swlib_entity_references 
	    WHERE rawtohex(target_id) = p_entity_id;

	else
	    p_result := ENTITY_DOES_NOT_EXIST_ERR;
	    p_err_desc := 'Entity to be deleted does not exist';
	    RETURN;
	end if;
    else
	SELECT count(1) into cnt 
	FROM mgmt_swlib_entity_revisions
	WHERE RAWTOHEX(entity_id) = p_entity_id  and revision = p_entity_rev and status <> 2;

	if (cnt > 0) then
	    SELECT IS_CURRENT, REVISION_ID into l_is_current, l_reference_id 
	    FROM mgmt_swlib_entity_revisions 
	    WHERE RAWTOHEX(entity_id) = p_entity_id and revision = p_entity_rev and status <> 2;

	    UPDATE mgmt_swlib_entity_revisions
	    SET is_current = 0, status = 2
	    WHERE RAWTOHEX(revision_id) = l_reference_id;

	    if (l_is_current = 1) then 
	        SELECT MAX(revision) into max_revision 
		FROM mgmt_swlib_entity_revisions
		WHERE status <> 2 and RAWTOHEX(entity_id) = p_entity_id;

		if (max_revision is not null) then
		    UPDATE mgmt_swlib_entity_revisions
		    SET is_current = 1
		    WHERE RAWTOHEX(entity_id) = p_entity_id and revision = max_revision; 
                else
               	    --set directory_id to null
	            UPDATE mgmt_swlib_entities 
	            SET directory_id = null
	            WHERE RAWTOHEX(entity_id) = p_entity_id;     
		end if;
	    end if;

	    --delete the references
	    DELETE FROM mgmt_swlib_entity_references
	    WHERE source_id = l_reference_id;

	    --delete the references which are referring to this entity
	    DELETE FROM mgmt_swlib_entity_references
	    WHERE RAWTOHEX(target_id) = p_entity_id and target_revision_id = l_reference_id;


	else
	    p_result := ENTITY_DOES_NOT_EXIST_ERR;
	    p_err_desc := 'Entity to be deleted does not exist';
	    RETURN;
	end if;
    end if;
END delete_entity;


--for internal consumption only
PROCEDURE insert_revision_i(p_revision_rec    IN MGMT_SWLIB_REVISION_RECORD,
                            p_entity_id       IN RAW,
                            p_revision        OUT VARCHAR2,
                            p_revision_id     OUT RAW,
                            p_result          OUT NUMBER,--success/error code
                            p_err_desc        OUT VARCHAR2)
IS
l_params     MGMT_SWLIB_PARAMS_LIST     := p_revision_rec.rev_params_info;
l_curr_revision MGMT_SWLIB_ENTITY_REVISIONS.REVISION%TYPE := null;
l_maturity_status_id MGMT_SWLIB_ENTITY_REVISIONS.MATURITY_STATUS_ID%TYPE := 0;
BEGIN
    p_err_desc := null;
    p_result := SUCCESS_RET;

    BEGIN
        --pick up the current in-use version
        SELECT revision INTO l_curr_revision
        FROM mgmt_swlib_entity_revisions
        WHERE entity_id = p_entity_id
        AND is_current = CURRENT_REVISION;
        
        --update the current is_current=1 row to 0
        UPDATE mgmt_swlib_entity_revisions
        SET is_current = NON_CURRENT_REVISION
        WHERE entity_id = p_entity_id
        AND is_current = CURRENT_REVISION;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN 
            l_curr_revision := NEW_ENTITY_VER;
    END;
    
    DBMS_OUTPUT.PUT_LINE('Updated revision record ' || SQL%ROWCOUNT);
    
    --incoming revision to be used???
    --create a new revision
    DBMS_OUTPUT.PUT_LINE('Curr revision is ' || l_curr_revision);
    p_revision := get_next_version(l_curr_revision);
    DBMS_OUTPUT.PUT_LINE('Next revision is ' || p_revision);
    IF p_revision_rec.maturity_status IS NOT NULL THEN
        SELECT maturity_status_id INTO l_maturity_status_id
        FROM MGMT_SWLIB_MATURITY_STATUS
        WHERE maturity_status = p_revision_rec.maturity_status;
    END IF;
    
    INSERT INTO mgmt_swlib_entity_revisions(entity_id, revision, revision_author, 
        maturity_status_id, status, product, product_version, data_type, checksum, 
        is_current) 
    VALUES(p_entity_id, p_revision, p_revision_rec.revision_author,
        l_maturity_status_id, p_revision_rec.status, p_revision_rec.product, 
        p_revision_rec.product_version, p_revision_rec.data_type, p_revision_rec.checksum, 
        CURRENT_REVISION)
    RETURNING revision_id INTO p_revision_id;

    add_revision_documents(p_revision_rec, p_revision_id); 
    
    insert_rev_parameters(l_params, p_revision_id);

    DBMS_OUTPUT.PUT_LINE('RevisionId is ' || rawtohex(p_revision_id));
    
    --insert references
    --insert_references(p_references, 
    --                  l_revision_id, 
    --                  p_result, 
    --                  p_err_desc);
END insert_revision_i;


PROCEDURE insert_revision(p_revision_rec    IN MGMT_SWLIB_REVISION_RECORD,
                          p_entity_id_hex   IN VARCHAR2,
                          p_revision_id_hex OUT VARCHAR2,
			  p_revision	    OUT VARCHAR2,
                          p_result          OUT NUMBER,--success/error code
                          p_err_desc        OUT VARCHAR2)
IS
l_entity_id MGMT_SWLIB_ENTITIES.ENTITY_ID%TYPE := hextoraw(p_entity_id_hex);
l_revision_id mgmt_swlib_entity_revisions.revision_id%TYPE := null;
BEGIN
    l_entity_id := hextoraw(p_entity_id_hex);
    insert_revision_i(p_revision_rec, l_entity_id, p_revision, 
                      l_revision_id, p_result, p_err_desc);
    
    p_revision_id_hex := rawtohex(l_revision_id);
END insert_revision;

--For internal consumption only
PROCEDURE update_revision(p_revision_rec   IN MGMT_SWLIB_REVISION_RECORD,
                          p_entity_id      IN RAW,
                          p_reference_list IN MGMT_SWLIB_REFERENCE_LIST,
			  p_en_is_dirty	   IN NUMBER,
			  p_revision	   OUT VARCHAR2,
			  p_revision_id    OUT RAW,
                          p_result         OUT NUMBER,--success/error code
                          p_err_desc       OUT VARCHAR2)
IS
l_params   mgmt_swlib_params_list := p_revision_rec.rev_params_info;
l_revision mgmt_swlib_entity_revisions.revision%TYPE;
--l_revision_flag  NUMBER(1) := 0;
l_maturity_status_id MGMT_SWLIB_ENTITY_REVISIONS.MATURITY_STATUS_ID%TYPE := 0;
BEGIN
    p_err_desc := null;
    p_result := SUCCESS_RET;
    
    --check if a new revision record(first time entry) is required
    --or update the existing revision record
    --SELECT COUNT(1) INTO l_revision_flag
    --FROM mgmt_swlib_entity_revisions
    --WHERE entity_id = p_entity_id;
    
    --IF l_revision_flag = 0
    IF p_en_is_dirty = 1
    THEN
        insert_revision_i(p_revision_rec, p_entity_id, p_revision, 
                          p_revision_id, p_result, p_err_desc);
	insert_references(p_reference_list, p_revision_id, 
			  p_result, p_err_desc);
    ELSE
        --FOR UPDATE;--wait till row becomes available???
        IF p_revision_rec.maturity_status IS NOT NULL THEN
            SELECT maturity_status_id INTO l_maturity_status_id
            FROM MGMT_SWLIB_MATURITY_STATUS
            WHERE maturity_status = p_revision_rec.maturity_status;
        END IF;

        UPDATE mgmt_swlib_entity_revisions
        SET revision_author = p_revision_rec.revision_author, 
            maturity_status_id = l_maturity_status_id, 
            status = p_revision_rec.status, 
            product = p_revision_rec.product, 
            product_version = p_revision_rec.product_version, 
            data_type = p_revision_rec.data_type, 
            checksum = p_revision_rec.checksum,
            modified_date = SYSDATE
        WHERE entity_id = p_entity_id
        AND revision = p_revision_rec.revision
        AND status <> 2
        RETURNING revision_id, revision INTO p_revision_id, p_revision;
        assert(SQL%ROWCOUNT = 1, 'One revision record must be updated');

        --update/add revision documents
        add_revision_documents(p_revision_rec, p_revision_id);

        --update the parameters info 
        update_rev_parameters(l_params, p_revision_id);
    END IF;
END update_revision;

PROCEDURE insert_references(p_references        IN MGMT_SWLIB_REFERENCE_LIST,
                            p_revision_id_hex   IN VARCHAR2,
                            p_result            OUT NUMBER,--success/error code
                            p_err_desc          OUT VARCHAR2)
IS
cnt INTEGER                                 := 0;
l_target_rev_id MGMT_SWLIB_ENTITY_REVISIONS.REVISION_ID%TYPE := null;--logic not decided
BEGIN
    p_err_desc := null;
    p_result := SUCCESS_RET;
    --(check uniquenes of reference names???)
    IF p_references IS NOT NULL 
    THEN

        -- delete all the current references if present
        DELETE FROM MGMT_SWLIB_ENTITY_REFERENCES WHERE rawtohex(source_id) = p_revision_id_hex;

        FOR i IN 1..p_references.count
        LOOP
            --need to retrieve the target revision id from the target id
            --and target production values
            --if target_id is set and target_revision not set
            --then production version is checked
        
            IF p_references(i).target_revision IS NOT NULL THEN
		SELECT revision_id INTO l_target_rev_id
		FROM MGMT_SWLIB_ENTITY_REVISIONS
		WHERE entity_id = p_references(i).target_id
		AND revision = p_references(i).target_revision;
            END IF;
    
            
            insert_reference(p_references(i), l_target_rev_id, i, hextoraw(p_revision_id_hex));
        
        END LOOP;
    END IF;
END insert_references;

PROCEDURE insert_reference(p_reference      IN MGMT_SWLIB_REFERENCE_RECORD,
                           p_target_rev_id  IN RAW,
			   p_index          IN NUMBER,
                           p_revision_id    IN RAW)
IS
l_reference_id MGMT_SWLIB_ENTITY_REFERENCES.REFERENCE_ID%TYPE;
BEGIN
    INSERT INTO mgmt_swlib_entity_references(source_id, name, type, refindex, target_id, 
        target_revision_id, target_production) 
    VALUES(p_revision_id, p_reference.name, p_reference.type, p_index, 
        hextoraw(p_reference.target_id), p_target_rev_id, 
	p_reference.target_production)
    RETURNING reference_id INTO l_reference_id;

    DBMS_OUTPUT.PUT_LINE('Inserted reference name=' || p_reference.name);

    modify_documents(p_reference, p_revision_id, l_reference_id, 1);

END insert_reference;

PROCEDURE update_reference(p_reference      IN MGMT_SWLIB_REFERENCE_RECORD,
                           p_target_rev_id  IN RAW,
                           p_revision_id    IN RAW,
                           p_reference_id   IN RAW)
IS
BEGIN
    UPDATE mgmt_swlib_entity_references
    SET target_id = hextoraw(p_reference.target_id), 
        target_revision_id = p_target_rev_id,
	target_production = p_reference.target_production
    WHERE reference_id = p_reference_id;

    assert(SQL%ROWCOUNT = 1, 'One reference record must be updated');
    DBMS_OUTPUT.PUT_LINE('Updated reference name=' || p_reference.name);

    modify_documents(p_reference, p_revision_id, p_reference_id, 0);

END update_reference;

PROCEDURE add_revision_documents(p_revision    IN MGMT_SWLIB_REVISION_RECORD,
                                 p_revision_id IN RAW)
IS
cnt INTEGER := 0;
l_documents MGMT_SWLIB_DOCUMENT_LIST;
BEGIN
    l_documents := p_revision.documents;
    IF l_documents IS NOT NULL
    THEN
        FOR i IN 1..l_documents.count
        LOOP
            SELECT COUNT(1) INTO cnt
            FROM mgmt_swlib_entity_documents
            WHERE name = l_documents(i).name
            AND revision_id = p_revision_id
            AND reference_id IS NULL;

            IF cnt > 0
            THEN
                update_document(l_documents(i), p_revision_id, NULL);
            ELSE
                insert_document(l_documents(i), p_revision_id, NULL);
            END IF;
        END LOOP;
    END IF;
END add_revision_documents;

PROCEDURE modify_documents(p_reference      IN MGMT_SWLIB_REFERENCE_RECORD,
                           p_revision_id    IN RAW,
                           p_reference_id   IN RAW,
                           p_new_ref        IN NUMBER)
IS
cnt INTEGER                                 := 0;
l_ref_documents MGMT_SWLIB_DOCUMENT_LIST;
BEGIN
    --modify associated documents
    l_ref_documents := p_reference.documents;
    IF l_ref_documents IS NOT NULL 
    THEN
        FOR i IN 1..l_ref_documents.count
        LOOP
            IF p_new_ref = 1
            THEN
                --new reference with new document
                insert_document(l_ref_documents(i), p_revision_id, p_reference_id);
                DBMS_OUTPUT.PUT_LINE('New Ref Doc inserted');
            ELSE
                DBMS_OUTPUT.PUT_LINE('Old Ref Doc : '||l_ref_documents(i).name);
                SELECT COUNT(1) INTO cnt 
                FROM mgmt_swlib_entity_documents
                WHERE name = l_ref_documents(i).name --upper case check??
                AND reference_id = p_reference_id;
                
                --check if document exists
                IF cnt > 0 
                THEN
                    --existing reference with existing document
                    update_document(l_ref_documents(i), p_revision_id, p_reference_id);
                ELSE
                    --existing reference with new document
                    insert_document(l_ref_documents(i), p_revision_id, p_reference_id);
                END IF;
            END IF;
        END LOOP;
    END IF;
END modify_documents;

PROCEDURE insert_document(p_ref_document      IN MGMT_SWLIB_DOCUMENT_RECORD,
                          p_revision_id       IN RAW,
                          p_reference_id      IN RAW)
IS
BEGIN
    INSERT INTO mgmt_swlib_entity_documents(revision_id, name, 
        reference_id, value) 
    VALUES(p_revision_id, p_ref_document.name, 
        p_reference_id, p_ref_document.value);

    DBMS_OUTPUT.PUT_LINE('-->Inserted doc name=' || p_ref_document.name);
END insert_document;

PROCEDURE update_document(p_ref_document      IN MGMT_SWLIB_DOCUMENT_RECORD,
                          p_revision_id       IN RAW,
                          p_reference_id      IN RAW)
IS
BEGIN
    IF p_reference_id IS NULL
    THEN
	UPDATE mgmt_swlib_entity_documents
	SET name = p_ref_document.name,
	    value = p_ref_document.value
	WHERE reference_id IS NULL
	AND revision_id = p_revision_id;
    ELSE
    	UPDATE mgmt_swlib_entity_documents
    	SET name = p_ref_document.name, 
            value = p_ref_document.value
    	WHERE reference_id = p_reference_id
    	AND revision_id = p_revision_id;
    END IF;
    
    DBMS_OUTPUT.PUT_LINE('-->Updated doc name=' || p_ref_document.name);
END update_document;

PROCEDURE insert_platforms(p_platforms MGMT_SWLIB_PLATFORM_LIST, 
                           p_entity_id RAW)
IS
BEGIN
    IF p_platforms IS NOT NULL AND p_platforms.count > 0 
    THEN
        FOR i IN 1..p_platforms.count
        LOOP
            INSERT INTO mgmt_swlib_entity_platforms(entity_id, platform)
            VALUES(p_entity_id, p_platforms(i));
            DBMS_OUTPUT.PUT_LINE('Inserted platform ' || p_platforms(i));
        END LOOP;
    END IF;
END insert_platforms;

PROCEDURE update_platforms(p_platforms MGMT_SWLIB_PLATFORM_LIST, 
                           p_entity_id RAW)
IS
cnt INTEGER                                 := 0;
l_sel_platforms VARCHAR2(4000);
BEGIN
    IF p_platforms IS NOT NULL AND p_platforms.count > 0
    THEN
        FOR p IN 1..p_platforms.count
        LOOP
            IF p = 1 
            THEN
                l_sel_platforms := EM_CHECK.ENQUOTE_LITERAL(p_platforms(p));
            ELSE
                l_sel_platforms := l_sel_platforms || ','|| EM_CHECK.ENQUOTE_LITERAL(p_platforms(p));
            END IF;

            SELECT COUNT(1) INTO cnt 
            FROM mgmt_swlib_entity_platforms
            WHERE platform = p_platforms(p)
            AND entity_id = p_entity_id;
            
            IF cnt = 0 
            THEN
                --insert the new platforms
                INSERT INTO mgmt_swlib_entity_platforms(entity_id, platform)
                VALUES(p_entity_id, p_platforms(p));
                
                DBMS_OUTPUT.PUT_LINE('Inserted platform ' || p_platforms(p));
            END IF;
        END LOOP;
        DBMS_OUTPUT.PUT_LINE('Selected Platforms '||l_sel_platforms);

        --delete the previously inserted, currently removed
        EXECUTE IMMEDIATE 'DELETE FROM mgmt_swlib_entity_platforms'
                        ||' WHERE platform NOT IN('||l_sel_platforms||')'
                        ||' AND entity_id = :1' USING p_entity_id;
        DBMS_OUTPUT.PUT_LINE('Deleted Platforms '||SQL%ROWCOUNT);
    ELSE
	DELETE FROM mgmt_swlib_entity_platforms WHERE entity_id = p_entity_id;
	DBMS_OUTPUT.PUT_LINE('Deleted Platforms '||SQL%ROWCOUNT);
    END IF;
END update_platforms;

PROCEDURE insert_parameters(p_params MGMT_SWLIB_PARAMS_LIST, 
                            p_entity_id RAW)
IS
BEGIN
    IF p_params IS NOT NULL 
    THEN
        FOR i IN 1..p_params.count
        LOOP
            INSERT INTO mgmt_swlib_entity_parameters(entity_id, name, value)
            VALUES(p_entity_id, p_params(i).name, p_params(i).value);
            DBMS_OUTPUT.PUT_LINE('Inserted parameter name=' || p_params(i).name
                ||' value='||p_params(i).value);
        END LOOP;
    END IF;
END insert_parameters;
                           
PROCEDURE update_parameters(p_params MGMT_SWLIB_PARAMS_LIST, 
                            p_entity_id RAW)
IS
cnt INTEGER                                 := 0;
l_sel_params VARCHAR2(4000);
BEGIN
    IF p_params IS NOT NULL AND p_params.count > 0
    THEN
        FOR p IN 1..p_params.count
        LOOP
            IF p = 1 
            THEN
                l_sel_params := EM_CHECK.ENQUOTE_LITERAL(p_params(p).name);
            ELSE
                l_sel_params := l_sel_params || ',' || EM_CHECK.ENQUOTE_LITERAL(p_params(p).name);
            END IF;

            SELECT COUNT(1) INTO cnt 
            FROM mgmt_swlib_entity_parameters
            WHERE name = p_params(p).name
            AND entity_id = p_entity_id;
            
            IF cnt > 0 
            THEN 
                --update existing parameter
                UPDATE mgmt_swlib_entity_parameters
                SET value = p_params(p).value
                WHERE name = p_params(p).name
                AND entity_id = p_entity_id;
                assert(SQL%ROWCOUNT = 1, 'One parameter record must be updated for the entity');
                DBMS_OUTPUT.PUT_LINE('Updated parameter ' || p_params(p).name);
            ELSE
                --insert the new parameter
                INSERT INTO mgmt_swlib_entity_parameters(entity_id, name, value)
                VALUES(p_entity_id, p_params(p).name, p_params(p).value);
                
                DBMS_OUTPUT.PUT_LINE('Inserted parameter ' || p_params(p).name);
            END IF;
        END LOOP;
        DBMS_OUTPUT.PUT_LINE('Selected Parameters '||l_sel_params);
        --delete the previously inserted, currently removed
        EXECUTE IMMEDIATE 'DELETE FROM mgmt_swlib_entity_parameters'
                        ||' WHERE name NOT IN('||l_sel_params||')'
                        ||' AND entity_id = :1' USING p_entity_id;
        DBMS_OUTPUT.PUT_LINE('Deleted Parameters '||SQL%ROWCOUNT||' for entity '||rawtohex(p_entity_id));
    ELSE
	DELETE FROM mgmt_swlib_entity_parameters WHERE entity_id = p_entity_id;
	DBMS_OUTPUT.PUT_LINE('Deleted Parameters '||SQL%ROWCOUNT||' for entity '||rawtohex(p_entity_id));
    END IF;
END update_parameters;


PROCEDURE insert_rev_parameters(p_params MGMT_SWLIB_PARAMS_LIST, 
                            p_revision_id RAW)
IS
BEGIN
    IF p_params IS NOT NULL 
    THEN
        FOR i IN 1..p_params.count
        LOOP
            INSERT INTO mgmt_swlib_revision_parameters(revision_id, name, value)
            VALUES(p_revision_id, p_params(i).name, p_params(i).value);
            DBMS_OUTPUT.PUT_LINE('Inserted revision parameter name=' || p_params(i).name
                ||' value='||p_params(i).value);
        END LOOP;
    END IF;
END insert_rev_parameters;
                           
PROCEDURE update_rev_parameters(p_params MGMT_SWLIB_PARAMS_LIST, 
                            p_revision_id RAW)
IS
cnt INTEGER                                 := 0;
l_sel_params VARCHAR2(4000);
BEGIN
    IF p_params IS NOT NULL AND p_params.count > 0
    THEN
        FOR p IN 1..p_params.count
        LOOP
            IF p = 1 
            THEN
                l_sel_params := EM_CHECK.ENQUOTE_LITERAL(p_params(p).name) ;
            ELSE
                l_sel_params := l_sel_params || ',' || EM_CHECK.ENQUOTE_LITERAL(p_params(p).name) ;
            END IF;

            SELECT COUNT(1) INTO cnt 
            FROM mgmt_swlib_revision_parameters
            WHERE name = p_params(p).name
            AND revision_id = p_revision_id;
            
            IF cnt > 0 
            THEN 
                --update existing parameter
                UPDATE mgmt_swlib_revision_parameters
                SET value = p_params(p).value
                WHERE name = p_params(p).name
                AND revision_id = p_revision_id;
                assert(SQL%ROWCOUNT = 1, 'One parameter record must be updated for the revision');
                DBMS_OUTPUT.PUT_LINE('Updated revision parameter ' || p_params(p).name);
            ELSE
                --insert the new parameter
                INSERT INTO mgmt_swlib_revision_parameters(revision_id, name, value)
                VALUES(p_revision_id, p_params(p).name, p_params(p).value);
                
                DBMS_OUTPUT.PUT_LINE('Inserted revision parameter ' || p_params(p).name);
            END IF;
        END LOOP;
        DBMS_OUTPUT.PUT_LINE('Selected Parameters '||l_sel_params);
        --delete the previously inserted, currently removed
        EXECUTE IMMEDIATE 'DELETE FROM mgmt_swlib_revision_parameters'
                        ||' WHERE name NOT IN('||l_sel_params||')'
                        ||' AND revision_id = :1' USING p_revision_id;
        DBMS_OUTPUT.PUT_LINE('Deleted revision Parameters '||SQL%ROWCOUNT||' for revision '||rawtohex(p_revision_id));
    ELSE
	DELETE FROM mgmt_swlib_revision_parameters WHERE revision_id = p_revision_id;
	DBMS_OUTPUT.PUT_LINE('Deleted revision Parameters '||SQL%ROWCOUNT||' for revision '||rawtohex(p_revision_id));
    END IF;
END update_rev_parameters;


PROCEDURE insert_directory(p_directory MGMT_SWLIB_DIRECTORY_RECORD,
                           p_directory_id_hex	OUT VARCHAR2,
			   p_result		OUT NUMBER,--success/error code
                           p_err_desc           OUT VARCHAR2)
IS
l_directory_id mgmt_swlib_directories.directory_id%TYPE  := null;
cnt INTEGER := 0;
BEGIN
    p_err_desc := null;
    p_result := SUCCESS_RET;
    -- Validate that parent/name combination unique
    SELECT COUNT(1) INTO cnt
    FROM MGMT_SWLIB_DIRECTORIES
    WHERE name=p_directory.name AND parent_id=p_directory.parent_id;
    IF cnt > 0
    THEN
        p_result := DIRECTORY_EXISTS_ERR;
        p_err_desc := 'A Directory with the specified absolute name exists';
        RETURN;
    END IF;
    INSERT INTO mgmt_swlib_directories(name, parent_id, description, owner)
    VALUES(p_directory.name, hextoraw(p_directory.parent_id), 
	p_directory.description, p_directory.owner)
    RETURNING directory_id INTO l_directory_id;
    
    p_directory_id_hex := rawtohex(l_directory_id);

END insert_directory;


PROCEDURE update_directory(p_directory MGMT_SWLIB_DIRECTORY_RECORD,
                           p_result		OUT NUMBER,--success/error code
                           p_err_desc           OUT VARCHAR2)
IS
BEGIN
    p_err_desc := null;
    p_result := SUCCESS_RET;
    UPDATE mgmt_swlib_directories
    SET name = p_directory.name,
	parent_id = hextoraw(p_directory.parent_id),
	description = p_directory.description,
	owner = p_directory.owner
    WHERE directory_id = hextoraw(p_directory.directory_id);
    
    assert(SQL%ROWCOUNT = 1, 'Directory must  exist');
    
END update_directory;


PROCEDURE update_filepaths(p_paths MGMT_SWLIB_PATH_LIST,
                           p_result             OUT NUMBER,--success/error code
                           p_err_desc           OUT VARCHAR2)
IS
BEGIN
    p_err_desc := null;
    p_result := SUCCESS_RET;
    DELETE FROM mgmt_swlib_data_directories;

    IF p_paths IS NOT NULL AND p_paths.count > 0
    THEN
        FOR i IN 1..p_paths.count
        LOOP
            INSERT INTO mgmt_swlib_data_directories(filepath)
            VALUES(p_paths(i));
            DBMS_OUTPUT.PUT_LINE('Inserted file path ' || p_paths(i));
        END LOOP;
    END IF;
END update_filepaths;


--
-- PROCEDURE : purge
--
-- PURPOSE
--
--   this is a method that will:
--    i) delete rows for revisions when those revisions are purged
--    ii) return any file paths that are not being referenced by anyone
--        following the deletion, so they can be deleted in java
--
PROCEDURE purge(p_files  	OUT MGMT_SWLIB_PATH_LIST,
		p_result        OUT NUMBER,--success/error code
                p_err_desc     	OUT VARCHAR2)
IS
l_files MGMT_SWLIB_PATH_LIST := MGMT_SWLIB_PATH_LIST();
i1 INTEGER := 0;
cnt INTEGER := 0;
BEGIN
	p_err_desc := null;
	p_result := SUCCESS_RET;
	p_files := MGMT_SWLIB_PATH_LIST();

	SELECT MSED.EXTERNAL_KEY BULK COLLECT INTO l_files 
	FROM MGMT_SWLIB_ENTITY_DATA MSED, MGMT_SWLIB_ENTITY_REVISIONS MSER
	WHERE MSED.REVISION_ID = MSER.REVISION_ID AND MSER.STATUS = 2; 

	DELETE FROM MGMT_SWLIB_ENTITY_DOCUMENTS WHERE REVISION_ID IN (
	SELECT REVISION_ID FROM MGMT_SWLIB_ENTITY_REVISIONS WHERE STATUS = 2);

	DELETE FROM MGMT_SWLIB_ENTITY_REVISIONS WHERE STATUS = 2;

	DELETE FROM MGMT_SWLIB_ENTITIES WHERE NOT EXISTS (
	SELECT REVISION_ID FROM MGMT_SWLIB_ENTITY_REVISIONS WHERE 
	MGMT_SWLIB_ENTITY_REVISIONS.ENTITY_ID = MGMT_SWLIB_ENTITIES.ENTITY_ID
	);

	IF l_files.COUNT > 0
	THEN
	  FOR i IN l_files.FIRST..l_files.LAST
	  LOOP	

	    SELECT COUNT(1) INTO cnt
	    FROM MGMT_SWLIB_ENTITY_DATA MSED
	    WHERE MSED.EXTERNAL_KEY = l_files(i); 

    	    IF cnt = 0 -- ie no other references
    	    THEN
              p_files.extend(1);
	      i1 := i1+1;
	      p_files(i1) := l_files(i);
    	    END IF;	    
	  END LOOP;

	END IF;

END purge;

PROCEDURE assert (bool_IN IN BOOLEAN
                 ,msg_IN IN VARCHAR2 := null)
IS
BEGIN
   IF NOT NVL(bool_IN,FALSE) -- fail on null input
   THEN
      RAISE_APPLICATION_ERROR
         ( ASSERTFAIL_C, 'ASSERTFAIL:'||
               PKGNAME_C||':'||SUBSTR(msg_IN,1,200)
         ) ;
   END IF;
END assert;

END MGMT_SWLIB;
/
show errors;
/