APPS.ICX_RELATED_CATEGORIES_PUB SQL Statements

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

Line: 5

PROCEDURE Insert_Relation
( p_api_version_number 	IN	NUMBER			    		,
  p_init_msg_list	IN  	VARCHAR2 := FND_API.G_FALSE		,
  p_simulate		IN	VARCHAR2 := FND_API.G_FALSE 		,
  p_commit		IN	VARCHAR2 := FND_API.G_FALSE 		,
  p_validation_level	IN	NUMBER   := FND_API.G_VALID_LEVEL_FULL	,
  p_return_status	OUT 	VARCHAR2				,
  p_msg_count		OUT	NUMBER					,
  p_msg_data		OUT 	VARCHAR2	    			,
  p_category_set_id	IN	NUMBER	 DEFAULT NULL			,
  p_category_set	IN 	VARCHAR2 DEFAULT NULL			,
  p_category_id		IN 	NUMBER	 DEFAULT NULL			,
  p_category		IN	VARCHAR2 DEFAULT NULL			,
  p_related_category_id	IN	NUMBER 	 DEFAULT NULL			,
  p_related_category	IN	VARCHAR2 DEFAULT NULL			,
  p_relationship_type	IN 	VARCHAR2				,
  p_created_by		IN      NUMBER
) IS

cursor l_category_set_csr is
select category_set_id
from   mtl_category_sets
where  category_set_name = p_category_set;

Line: 31

select mck.category_id
from   mtl_categories_kfv mck,
       mtl_category_sets mcs
where  (mcs.validate_flag = 'Y' and
	mck.category_id in (
            select mcsv.category_id
            from   mtl_category_set_valid_cats mcsv
            where  mcsv.category_set_id = l_cat_set_id) and
	mck.concatenated_segments = l_cat_name)
or     (mcs.validate_flag <> 'Y' and
	mcs.structure_id = mck.structure_id and
	mck.concatenated_segments = l_cat_name);

Line: 59

    SAVEPOINT Insert_Relation_PUB;

Line: 66

	'Insert_Relation',
	G_PKG_NAME)
    THEN
	RAISE FND_API.G_EXC_UNEXPECTED_ERROR;

Line: 194

	    select count(*) into l_count
   	    from   mtl_category_sets
	    where  category_set_id = l_category_set_id;

Line: 212

	    select count(*) into l_count
   	    from   mtl_categories_kfv mck,
       		   mtl_category_sets mcs
	    where  (mcs.validate_flag = 'Y' and
	            mck.category_id in (
            	        select mcsv.category_id
            		from   mtl_category_set_valid_cats mcsv
            		where  mcsv.category_set_id = l_category_set_id) and
		    mck.category_id = l_category_id)
	    or     (mcs.validate_flag <> 'Y' and
	            mcs.structure_id = mck.structure_id and
	            mck.category_id = l_category_id);

Line: 239

	    select count(*) into l_count
   	    from   mtl_categories_kfv mck,
       		   mtl_category_sets mcs
	    where  (mcs.validate_flag = 'Y' and
	            mck.category_id in (
            	        select mcsv.category_id
            		from   mtl_category_set_valid_cats mcsv
            		where  mcsv.category_set_id = l_category_set_id) and
		    mck.category_id = l_related_category_id)
	    or     (mcs.validate_flag <> 'Y' and
	            mcs.structure_id = mck.structure_id and
	            mck.category_id = l_related_category_id);

Line: 279

	    select count(*) into l_count
	    from   icx_related_categories
	    where  category_set_id = l_category_set_id
	    and    category_id = l_category_id
	    and    related_category_id = l_category_id;

Line: 303

	    select count(*) into l_count
	    from   icx_related_categories
	    where  category_set_id = l_category_set_id
	    and    category_id = l_category_id
	    and    related_category_id = l_related_category_id;

Line: 321

	select count(*) into l_count
	from   fnd_lookups
	where  lookup_type = 'ICX_RELATIONS'
	and    enabled_flag = 'Y'
	and    lookup_code = p_relationship_type;

Line: 349

    insert into icx_related_categories
       (category_set_id,
	category_id,
	related_category_id,
	relationship_type,
	created_by,
	creation_date,
	last_updated_by,
	last_update_date)
    values
       (l_category_set_id,
	l_category_id,
	l_related_category_id,
	p_relationship_type,
	p_created_by,
	sysdate,
	p_created_by,
	sysdate);

Line: 376

	ROLLBACK TO Insert_Relation_PUB;

Line: 424

    	        'Insert_Relation'
	    );

Line: 436

END; -- Insert_Relation

Line: 441

PROCEDURE Delete_Relation
( p_api_version_number 	IN	NUMBER			    		,
  p_init_msg_list	IN  	VARCHAR2 := FND_API.G_FALSE		,
  p_simulate		IN	VARCHAR2 := FND_API.G_FALSE 		,
  p_commit		IN	VARCHAR2 := FND_API.G_FALSE 		,
  p_validation_level	IN	NUMBER   := FND_API.G_VALID_LEVEL_FULL	,
  p_return_status	OUT 	VARCHAR2				,
  p_msg_count		OUT	NUMBER					,
  p_msg_data		OUT 	VARCHAR2	    			,
  p_category_set_id	IN	NUMBER	 DEFAULT NULL			,
  p_category_set	IN 	VARCHAR2 DEFAULT NULL			,
  p_category_id		IN 	NUMBER	 DEFAULT NULL			,
  p_category		IN	VARCHAR2 DEFAULT NULL			,
  p_related_category_id	IN	NUMBER 	 DEFAULT NULL			,
  p_related_category	IN	VARCHAR2 DEFAULT NULL
) IS

cursor l_category_set_csr is
select category_set_id
from   mtl_category_sets
where  category_set_name = p_category_set;

Line: 465

select mck.category_id
from   mtl_categories_kfv mck,
       mtl_category_sets mcs
where  (mcs.validate_flag = 'Y' and
	mck.category_id in (
            select mcsv.category_id
            from   mtl_category_set_valid_cats mcsv
            where  mcsv.category_set_id = l_cat_set_id) and
	mck.concatenated_segments = l_cat_name)
or     (mcs.validate_flag <> 'Y' and
	mcs.structure_id = mck.structure_id and
	mck.concatenated_segments = l_cat_name);

Line: 492

    SAVEPOINT Delete_Relation_PUB;

Line: 499

	'Delete Relation',
	G_PKG_NAME)
    THEN
	RAISE FND_API.G_EXC_UNEXPECTED_ERROR;

Line: 626

    delete from icx_related_categories
    where  category_set_id = l_category_set_id
    and    category_id = l_category_id
    and    related_category_id = l_related_category_id;

Line: 639

	ROLLBACK TO Insert_Relation_PUB;

Line: 663

	    -- add message: Relation to delete does not exist
	    FND_MESSAGE.SET_NAME('ICX','ICX_CAT_DELETE');

Line: 704

    	        'Delete_Relation'
	    );

Line: 716

END; -- Delete_Relation