APPS.AS_CLASSIFICATION_HOOKS SQL Statements

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

Line: 5

procedure update_selectable_flag(p_class_category in varchar2) is
begin

		update as_hz_class_code_denorm denorm
		set selectable_flag = 'N'
		where denorm.class_category = p_class_category
		      and selectable_flag = 'Y'
		      and exists (select 'x'
	                         from  hz_class_code_relations rel1,
				 hz_class_code_relations rel2,
				 fnd_lookup_values lv
				where (rel1.sub_class_code = denorm.class_code
				  or lv.lookup_code = rel1.sub_class_code)
				 and rel2.class_code = denorm.class_code
				  and lv.lookup_type = denorm.class_category
				 and denorm.class_category= rel1.class_category
				 and denorm.class_category= rel2.class_category
				 and denorm.class_category = p_class_category);

Line: 26

          FND_MSG_PUB.Add_Exc_Msg('AS_CLASSIFICATION_HOOKS', 'update_selectable_flag');

Line: 29

procedure update_class_category_post(p_class_category in varchar2,
			   p_category_meaning in varchar2,
			   p_allow_leaf_node_only_flag in varchar2) is

begin
	AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Update Category Post Start');

Line: 36

	update  as_hz_class_code_denorm
	set class_category_meaning = p_category_meaning
	where class_category = p_class_category
	and language = userenv('LANG')
	and class_category_meaning <> p_category_meaning;

Line: 44

		update_selectable_flag(p_class_category);

Line: 46

		update as_hz_class_code_denorm denorm
		set selectable_flag = 'Y'
		where denorm.class_category = p_class_category
		and selectable_flag = 'N';

Line: 52

	AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Update Category Post End');

Line: 57

          FND_MSG_PUB.Add_Exc_Msg('AS_CLASSIFICATION_HOOKS', 'update_class_category_post');

Line: 72

	INSERT INTO AS_HZ_CLASS_CODE_DENORM(
	CLASS_CATEGORY,
	CLASS_CATEGORY_MEANING,
	CLASS_CODE,
	CLASS_CODE_MEANING,
	CLASS_CODE_DESCRIPTION,
        ANCESTOR_CODE,
	ANCESTOR_MEANING,
	LANGUAGE,
	CONCAT_CLASS_CODE,
	CONCAT_CLASS_CODE_MEANING,
	CODE_LEVEL,
	START_DATE_ACTIVE,
	END_DATE_ACTIVE,
	ENABLED_FLAG,
	SELECTABLE_FLAG,
	SEGMENT1,
	SEGMENT2,
	SEGMENT3,
        SEGMENT4,
	SEGMENT5,
	SEGMENT6,
        SEGMENT7,
        SEGMENT8,
	SEGMENT9,
        SEGMENT10,
	SEGMENT1_MEANING,
	SEGMENT2_MEANING,
	SEGMENT3_MEANING,
        SEGMENT4_MEANING,
	SEGMENT5_MEANING,
	SEGMENT6_MEANING,
	SEGMENT7_MEANING,
        SEGMENT8_MEANING,
	SEGMENT9_MEANING,
	SEGMENT10_MEANING,
	CREATED_BY,
	CREATION_DATE,
	LAST_UPDATED_BY,
	LAST_UPDATE_LOGIN,
	LAST_UPDATE_DATE,
	REQUEST_ID,
	PROGRAM_ID,
	PROGRAM_APPLICATION_ID,
	PROGRAM_UPDATE_DATE
    ) select
	p_lookup_type,
	meaning,
	p_lookup_code,
	p_meaning,
	p_description,
	p_lookup_code,
	p_meaning,
	userenv('LANG'),
	p_lookup_code,
	p_meaning,
	1,
	p_start_date_active,
	p_end_date_active,
	p_enabled_flag,
	'Y',
	'not used',
	NULL,
	NULL,
	NULL,
	NULL,
	NULL,
	NULL,
	NULL,
	NULL,
	NULL,
	'not used',
	NULL,
	NULL,
	NULL,
	NULL,
	NULL,
	NULL,
	NULL,
	NULL,
	NULL,
	NVL(FND_GLOBAL.USER_ID,-1),
	SYSDATE,
	NVL(FND_GLOBAL.USER_ID,-1),
	NVL(FND_GLOBAL.LOGIN_ID,-1),
	SYSDATE,
	FND_GLOBAL.CONC_REQUEST_ID,
	FND_GLOBAL.CONC_PROGRAM_ID,
	FND_GLOBAL.PROG_APPL_ID,
	SYSDATE
	from fnd_lookup_types_vl
	where lookup_type = p_lookup_type;

Line: 176

		select code_level
		from as_hz_class_code_denorm
		where class_category = p_class_category
		and class_code = p_class_code
		and ancestor_code = p_class_code
		and language = userenv('LANG');

Line: 194

		select meaning
		from fnd_lookup_values
		where lookup_type = p_type
		and language = p_language
		and lookup_code  = p_code;

Line: 219

		select a.class_code
		from (select class_code, sub_class_code from hz_class_code_relations
                      where
		      sysdate between start_date_active and nvl(end_date_active,sysdate)
		      and class_category = p_type) a
		start with sub_class_code = p_curr_code
		connect by sub_class_code =  prior class_code;

Line: 250

		select a.class_code
		from (select class_code, sub_class_code from hz_class_code_relations
                      where
		      sysdate between start_date_active and nvl(end_date_active,sysdate)
		      and class_category = p_class_category) a
		start with sub_class_code = p_curr_code
		connect by sub_class_code =  prior class_code;

Line: 278

procedure update_concate_meaning(p_class_category in varchar2, p_curr_code in varchar2) is
	cursor get_language_csr is
		select language
		from fnd_lookup_types_tl
		where lookup_type = p_class_category;

Line: 290

		update as_hz_class_code_denorm
		set concat_class_code_meaning = l_concat_meaning
		where class_category = p_class_category
		and class_code = p_curr_code
		and language = l_language;

Line: 301

procedure update_Lookup_code_post(p_lookup_type in varchar2,
			     p_lookup_code in varchar2,
			     p_meaning in varchar2,
			     p_description in varchar2,
			     p_enabled_flag in varchar2,
			     p_start_date_active in date,
			     p_end_date_active in date) is

	  cursor get_code_meaning_csr is
                select class_code_meaning
                from as_hz_class_code_denorm
                where class_category = p_lookup_type
                and class_code = p_lookup_code
                and ancestor_code = p_lookup_code
                and language = userenv('LANG');

Line: 323

	update as_hz_class_code_denorm
	set enabled_flag = p_enabled_flag,
		start_date_active = p_start_date_active,
		end_date_active = p_end_date_active
	where	class_category = p_lookup_type
	and	class_code = p_lookup_code;

Line: 330

	update as_hz_class_code_denorm
	set ancestor_meaning = p_meaning
	where	class_category = p_lookup_type
	and	ancestor_code = p_lookup_code
	and	language = userenv('LANG');

Line: 341

	update as_hz_class_code_denorm
	set
		class_code_meaning = p_meaning,
		class_code_description = p_description,
		segment1_meaning = 'not used',
		segment2_meaning = null,
		segment3_meaning = null,
		segment4_meaning = null
	where	class_category = p_lookup_type
	and     class_code = p_lookup_code
	and	language = userenv('LANG')
	and	(class_code_meaning <> p_meaning
		or nvl(class_code_description, '#@#') <> nvl(p_description, '#@#'));

Line: 355

	update as_hz_class_code_denorm
	set
         concat_class_code_meaning = replace(concat_class_code_meaning,l_old_meaning, p_meaning)
	where   class_category = p_lookup_type
	and     instr(concat_class_code_meaning,l_old_meaning)>0
        and     language = userenv('LANG');

Line: 365

          FND_MSG_PUB.Add_Exc_Msg('AS_CLASSIFICATION_HOOKS', 'update_lookup_code_post');

Line: 370

procedure insert_current_code_relation(p_class_category in varchar2,
				  p_class_code in varchar2,
				  p_sub_class_code in varchar2
				) is
	cursor get_leaf_node_flag_csr is
		select allow_leaf_node_only_Flag
		from hz_class_categories
		where class_category = p_class_category;

Line: 389

	-- update those rows inserted in register_lookup_code_post with code relations
	-- Due to language concern and func can't be called in the update,
        -- concat meaning will be handled seperately.

	update as_hz_class_code_denorm ccd
	set code_level = 2,
        concat_class_code = l_concat_code
	where class_category = p_class_category
	and class_code = p_sub_class_code;

Line: 399

	insert into as_hz_class_code_denorm (
	class_category,
	class_category_meaning,
	class_code,
	class_code_meaning,
	class_code_description,
        ancestor_code,
	ancestor_meaning,
	language,
	concat_class_code,
	concat_class_code_meaning,
	code_level,
	start_date_active,
	end_date_active,
	enabled_flag,
	selectable_flag,
	segment1,
	segment2,
	segment3,
        segment4,
	segment5,
	segment6,
	segment7,
        segment8,
	segment9,
        segment10,
	segment1_meaning,
	segment2_meaning,
	segment3_meaning,
        segment4_meaning,
	segment5_meaning,
	segment6_meaning,
	segment7_meaning,
        segment8_meaning,
	segment9_meaning,
	segment10_meaning,
	created_by,
	creation_date,
	last_updated_by,
	last_update_login,
	last_update_date,
	request_id,
	program_id,
	program_application_id,
	program_update_date
	)
	select
	class_category,
	class_category_meaning,
	p_sub_class_code,
	lv.meaning,
	lv.description,
        ancestor_code,
	ancestor_meaning,
	lv.language,
	l_concat_code,
        'temp-meaning',
	2,
	lv.start_date_active,
	lv.end_date_active,
	lv.enabled_flag,
	'Y',
	'not used',
	NULL,
	NULL,
	NULL,
	NULL,
	NULL,
	NULL,
	NULL,
	NULL,
	NULL,
	'not used',
	NULL,
	NULL,
	NULL,
	NULL,
	NULL,
	NULL,
	NULL,
	NULL,
	NULL,
	nvl(fnd_global.user_id,-1),
	sysdate,
	nvl(fnd_global.user_id,-1),
	nvl(fnd_global.login_id,-1),
	sysdate,
	fnd_global.conc_request_id,
	fnd_global.conc_program_id,
	fnd_global.prog_appl_id,
	sysdate
     from
	as_hz_class_code_denorm denorm,
	fnd_lookup_values lv
    where
	denorm.class_category = lv.lookup_type
	and 	denorm.language = lv.language
	and	denorm.class_category = p_class_category
	and	denorm.class_code = p_class_code
	and 	lv.lookup_code = p_sub_class_code
	and	denorm.code_level = 1;

Line: 504

	-- update those rows inserted in register_lookup_code_post with code relations
	update as_hz_class_code_denorm ccd
	set code_level = 3,
            concat_class_code = l_concat_code
	where class_code = p_sub_class_code
	and class_category = p_class_category;

Line: 511

	insert into as_hz_class_code_denorm (
	class_category,
	class_category_meaning,
	class_code,
	class_code_meaning,
	class_code_description,
        ancestor_code,
	ancestor_meaning,
	language,
	concat_class_code,
	concat_class_code_meaning,
	code_level,
	start_date_active,
	end_date_active,
	enabled_flag,
	selectable_flag,
	segment1,
	segment2,
	segment3,
        segment4,
	segment5,
	segment6,
	segment7,
        segment8,
	segment9,
        segment10,
	segment1_meaning,
	segment2_meaning,
	segment3_meaning,
        segment4_meaning,
	segment5_meaning,
	segment6_meaning,
	segment7_meaning,
        segment8_meaning,
	segment9_meaning,
	segment10_meaning,
	created_by,
	creation_date,
	last_updated_by,
	last_update_login,
	last_update_date,
	request_id,
	program_id,
	program_application_id,
	program_update_date
	)
	select
	class_category,
	class_category_meaning,
	p_sub_class_code,
	lv.meaning,
	lv.description,
        ancestor_code,
	ancestor_meaning,
	lv.language,
	l_concat_code,
	'temp-meaning',
	3,
	lv.start_date_active,
	lv.end_date_active,
	lv.enabled_flag,
	'Y',
	'not used',
	NULL,
	NULL,
	NULL,
	NULL,
	NULL,
	NULL,
	NULL,
	NULL,
	NULL,
	'not used',
	NULL,
	NULL,
	NULL,
	NULL,
	NULL,
	NULL,
	NULL,
	NULL,
	NULL,
	nvl(fnd_global.user_id,-1),
	sysdate,
	nvl(fnd_global.user_id,-1),
	nvl(fnd_global.login_id,-1),
	sysdate,
	fnd_global.conc_request_id,
	fnd_global.conc_program_id,
	fnd_global.prog_appl_id,
	sysdate
     from
	as_hz_class_code_denorm denorm,
	fnd_lookup_values lv
    where
	denorm.class_category = lv.lookup_type
	and 	denorm.language = lv.language
	and	denorm.class_category = p_class_category
	and	denorm.class_code = p_class_code
	and 	lv.lookup_code = p_sub_class_code
	and	denorm.code_level = 2;

Line: 616

	-- update those rows inserted in register_lookup_code_post with code relations
	update as_hz_class_code_denorm ccd
	set code_level = 4,
	    concat_class_code = l_concat_code
	where class_code = p_sub_class_code
	and class_category = p_class_category;

Line: 623

	insert into as_hz_class_code_denorm (
	class_category,
	class_category_meaning,
	class_code,
	class_code_meaning,
	class_code_description,
        ancestor_code,
	ancestor_meaning,
	language,
	concat_class_code,
	concat_class_code_meaning,
	code_level,
	start_date_active,
	end_date_active,
	enabled_flag,
	selectable_flag,
	segment1,
	segment2,
	segment3,
        segment4,
	segment5,
	segment6,
	segment7,
        segment8,
	segment9,
        segment10,
	segment1_meaning,
	segment2_meaning,
	segment3_meaning,
        segment4_meaning,
	segment5_meaning,
	segment6_meaning,
	segment7_meaning,
        segment8_meaning,
	segment9_meaning,
	segment10_meaning,
	created_by,
	creation_date,
	last_updated_by,
	last_update_login,
	last_update_date,
	request_id,
	program_id,
	program_application_id,
	program_update_date
	)
	select
	class_category,
	class_category_meaning,
	p_sub_class_code,
	lv.meaning,
	lv.description,
        ancestor_code,
	ancestor_meaning,
	lv.language,
	l_concat_code,
	'temp-meaning',
	4,
	lv.start_date_active,
	lv.end_date_active,
	lv.enabled_flag,
	'Y',
	segment1,
	NULL,
	NULL,
	NULL,
	NULL,
	NULL,
	NULL,
	NULL,
	NULL,
	NULL,
	segment1_meaning,
	  NULL,
        NULL,
        NULL,
	NULL,
	NULL,
	NULL,
	NULL,
	NULL,
	NULL,
	nvl(fnd_global.user_id,-1),
	sysdate,
	nvl(fnd_global.user_id,-1),
	nvl(fnd_global.login_id,-1),
	sysdate,
	fnd_global.conc_request_id,
	fnd_global.conc_program_id,
	fnd_global.prog_appl_id,
	sysdate
     from
	as_hz_class_code_denorm denorm,
	fnd_lookup_values lv
    where
	denorm.class_category = lv.lookup_type
	and 	denorm.language = lv.language
	and	denorm.class_category = p_class_category
	and	denorm.class_code = p_class_code
	and 	lv.lookup_code = p_sub_class_code
	and	denorm.code_level = 3;

Line: 727

    update_concate_meaning(p_class_category,p_sub_class_code);

Line: 734

	update_selectable_flag(p_class_category);

Line: 750

		insert_current_code_relation(p_class_category,p_class_code,p_sub_class_code);

Line: 756

		-- delete extra data entered from register_lookup_code_post where level > 4 for sub code.
		delete
		from as_hz_class_code_denorm
		where class_category = p_class_category
		and class_code = p_sub_class_code
		and code_level = 1;

Line: 771

procedure update_class_code_rel_post(p_class_category in varchar2,
				  p_class_code in varchar2,
				  p_sub_class_code in varchar2,
				  p_end_date_active in date) is
	cursor get_sub_sub_class_code is
		select sub_class_code
		from hz_class_code_relations
		where class_category = p_class_category
		and p_class_code = p_sub_class_code
		and sysdate between start_date_active and nvl(end_date_active,sysdate);

Line: 783

		select lookup_code,language,as_classification_hooks.get_concat_meaning(lookup_type,lookup_code,language)
		from fnd_lookup_values
		where lookup_type = p_class_category;

Line: 788

                select lookup_code,as_classification_hooks.get_concat_code(lookup_type,lookup_code)
                from fnd_lookup_values
                where lookup_type = p_class_category;

Line: 793

		select 'x'
		from as_hz_class_code_denorm
		where class_category = p_class_category
		and class_code = p_sub_class_code
		and ancestor_code = p_class_code
		and language = userenv('LANG');

Line: 813

                        update as_hz_class_code_denorm
                        set concat_class_code = l_concat_code
                        where class_category = p_class_category
                        and class_code = l_class_code;

Line: 824

			update as_hz_class_code_denorm
			set concat_class_code_meaning = l_concat_meaning
			where class_category = p_class_category
			and class_code = l_class_code
			and language = l_language;

Line: 842

		-- code relation 1->2, 1->3, 1->4 need to be deleted

			delete
			from as_hz_class_code_denorm
			where class_category = p_class_category
			and ancestor_code = p_class_code
			and class_code <> p_class_code;

Line: 850

			update as_hz_class_code_denorm
                        set code_level = code_level-1
                        where class_category = p_class_category
                        and code_level <> 1;

Line: 856

		-- code relation 1->3,1->4, 2->3,2->4 need to be deleted.
		-- since maximum level = 4, we only need to set 3 to level 1 and 4 to level 2
		elsif	l_code_level = 3
		then
			open get_sub_sub_class_code;

Line: 864

			delete
			from as_hz_class_code_denorm
			where class_category = p_class_category
			and ancestor_code <> p_sub_class_code
			and class_code = p_sub_class_code
			  or (class_code = l_sub_sub_code and ancestor_code not in (l_sub_sub_code,p_sub_class_code));

Line: 871

			update as_hz_class_code_denorm
                        set code_level = 1
                        where class_category = p_class_category
                        and class_code = p_sub_class_code;

Line: 876

                        update as_hz_class_code_denorm
                        set code_level = 2
                        where class_category = p_class_category
                        and class_code = l_sub_sub_code;

Line: 882

		-- relation 1->4,2->4,3->4 need to be deleted

		elsif	l_code_level = 4
		then
			delete
			from as_hz_class_code_denorm
			where class_category = p_class_category
			and class_code = p_sub_class_code
			and ancestor_code<>p_sub_class_code;

Line: 892

			 update as_hz_class_code_denorm
                        set code_level = 1
                        where class_category = p_class_category
                        and class_code = p_sub_class_code
                        and ancestor_code = p_sub_class_code;

Line: 908

			insert_current_code_relation(p_class_category,p_class_code,p_sub_class_code);

Line: 915

          FND_MSG_PUB.Add_Exc_Msg('AS_CLASSIFICATION_HOOKS', 'update_class_code_rel_post');