APPS.FLM_KANBAN_CONFIG_PARAMS SQL Statements

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

Line: 13

	select fepd.module_id
    into l_module_id
    from FLM_EKB_PREFERENCE_definitions fepd
    where fepd.preference_id = p_pref_id;

Line: 26

    select level_id
    into l_level_id
    from
    (
      select level_id
      from FLM_EKB_preference_levels v
      where
        v.module_id = l_module_id and
        v.level_code <= l_level_code and
        ( v.organization_id is null or v.organization_id = p_org_id) and
        exists
        (
          select 1
             from flm_ekb_preference_values v1
             where v1.preference_id = p_pref_id and
               v1.level_id = v.level_id
        )
        order by v.level_code desc
       )
    where rownum = 1;

Line: 58

		select attribute_value_code
		into l_attr_val_code
		from
		(
			select attribute_value_code
			from FLM_EKB_PREFERENCE_VALUES
			where (level_id = p_level_id or level_id = 1)
				and preference_id = p_pref_id
			order by level_id desc
		)
		where rownum = 1;

Line: 70

		select attribute_value_code
		into l_attr_val_code
		from
		(
			select attribute_value_code
			from FLM_EKB_PREFERENCE_VALUES
			where (level_id = p_level_id or level_id = 1)
				and preference_id = p_pref_id
				and attribute_name = p_attr_name
			order by level_id desc
		)
		where rownum = 1;

Line: 324

		select  attribute_name, attribute_value_code
		from FLM_EKB_PREFERENCE_VALUES
		where level_id = p_level_id
		and preference_id = FLM_KANBAN_CONSTANTS.UNMOVED_CARD_LEADTIME_PER
		and attribute_value_code is NOT NULL;

Line: 366

		select  attribute_name
		from FLM_EKB_PREFERENCE_VALUES
		where level_id = p_level_id
		and preference_id = FLM_KANBAN_CONSTANTS.UNMOVED_CARD_LEADTIME_PER
		and nvl(attribute_enable_flag,1) = 1;

Line: 403

  select preference_type
  from   flm_ekb_preference_definitions
  where  preference_id = cl_pref_id;

Line: 408

  select decode( (select count(v.attribute_value_code)
                  from   flm_ekb_preference_values v,
                         flm_ekb_preference_levels l
                  where  v.PREFERENCE_ID = p_preference_id
                    and  v.LEVEL_ID = l.LEVEL_ID
                    and  nvl(l.organization_id, -99) = nvl(cl_org_id, -99)
                  ),
                  0, 'INHERIT',
                  1, (select v.attribute_value_code
                      from   flm_ekb_preference_values v,
                             flm_ekb_preference_levels l
                      where  v.PREFERENCE_ID = p_preference_id
                        and  v.LEVEL_ID = l.LEVEL_ID
                        and  nvl(l.organization_id, -99) = nvl(cl_org_id, -99)),
                  'INHERIT') AS single_value_code
  from dual;

Line: 426

  select count(v.attribute_value_code) as multi_value_count
  from   flm_ekb_preference_values v,
         flm_ekb_preference_levels l
  where  v.PREFERENCE_ID = p_preference_id
    and  v.LEVEL_ID = l.LEVEL_ID
    and  nvl(l.organization_id, -99) = nvl(cl_org_id, -99);

Line: 434

  select count(*) as multi_value_count
  from   flm_ekb_preference_values v,
         flm_ekb_preference_levels l
  where  v.PREFERENCE_ID = p_preference_id
    and  v.LEVEL_ID = l.LEVEL_ID
    and  nvl(l.organization_id, -99) = nvl(cl_org_id, -99);

Line: 496

    select ml.meaning
    from mfg_lookups ml
    where ml.lookup_code = to_number(cl_value_code) and
    ml.lookup_type = c1_lookup_type;

Line: 501

/*	(select wp.preference_value_lookup_type
                   from flm_ekb_preference_definitions wp
                   where wp.preference_id = p_preference_id);*/

Line: 513

		/*TODO: insert a fnd message and get from there*/
        /*return fnd_message.get_string('WIP', 'WIP_PREFERENCE_ENTERED');*/

Line: 517

		select wp.preference_value_lookup_type
		into l_lookup_type
        from flm_ekb_preference_definitions wp
        where wp.preference_id = p_preference_id;

Line: 587

/*function insertRowPrefValues()*/

procedure insertRow(p_preference_id number,
					p_level_id number,
					p_sequence_number number,
					p_attribute_name varchar2,
					p_attribute_value_code varchar2,
					p_enable_flag number,
					p_user_id number) is
begin
insert into flm_ekb_preference_values
(
PREFERENCE_VALUE_ID,
PREFERENCE_ID,
LEVEL_ID,
SEQUENCE_NUMBER,
ATTRIBUTE_NAME,
ATTRIBUTE_VALUE_CODE,
ATTRIBUTE_ENABLE_FLAG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER
)
VALUES
(
flm_ekb_pref_val_seq.nextval, --get from a sequence--PREFERENCE_VALUE_ID,
p_preference_id, --PREFERENCE_ID,
p_level_id, --LEVEL_ID,
p_sequence_number, --SEQUENCE_NUMBER,
p_attribute_name, --ATTRIBUTE_NAME,
p_attribute_value_code, --ATTRIBUTE_VALUE_CODE,
p_enable_flag, --ATTRIBUTE_ENABLE_FLAG
p_user_id, --CREATED_BY,
sysdate, --CREATION_DATE,
p_user_id, --LAST_UPDATED_BY,
sysdate, --LAST_UPDATE_DATE,
p_user_id, --LAST_UPDATE_LOGIN,
1 --OBJECT_VERSION_NUMBER
);

Line: 631

function insertRowLevels(p_org_id number, p_module_id number, p_user_id number) return number is
l_level_code number;

Line: 640

insert into flm_ekb_preference_levels
(
LEVEL_ID,
LEVEL_CODE,
ORGANIZATION_ID,
MODULE_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER
)
values
(
l_level_id, --LEVEL_ID,
l_level_code, --LEVEL_CODE,
p_org_id, --ORGANIZATION_ID,
p_module_id, --MODULE_ID,
p_user_id, --CREATED_BY,
sysdate, --CREATION_DATE,
p_user_id, --LAST_UPDATED_BY,
sysdate, --LAST_UPDATE_DATE,
p_user_id, --LAST_UPDATE_LOGIN,
1 --OBJECT_VERSION_NUMBER
);

Line: 672

	select level_id into l_level_id
	from flm_ekb_preference_levels
	where organization_id = p_org_id;

Line: 681

procedure insertRowsPrefValues(p_preference_id number, p_org_id number, p_user_id number) is
l_level_id number;

Line: 697

	select ml.lookup_code
	from mfg_lookups ml
	where lookup_type = 'MTL_KANBAN_SUPPLY_STATUS'
	      and to_number(ml.lookup_code) <= 8 /*only system/custom statuses till 8 will be honored in ekanban*/
		  and ml.enabled_flag = 'Y' /*consider only enabled status*/
		  and (ml.end_date_active is null OR ml.end_date_active > sysdate) /*consider status with future end date*/
	order by lookup_code asc;

Line: 714

						  p_procName =>'flm_kanban_config_params.insertRowsPrefValues',
                          p_params => l_params,
                          x_returnStatus => l_retstatus);

Line: 719

	select fepd.module_id
    into l_module_id
    from FLM_EKB_PREFERENCE_definitions fepd
    where fepd.preference_id = p_preference_id;

Line: 725

/*get level for the org. if level_id not found, then insert into levels table*/
	if(p_org_id is null) then
		l_level_id := 1;/*setting level for Site when org_id is null*/

Line: 736

	insert the row and carry out other insertion into values table for unmove_card preference*/
		l_level_id := insertRowLevels(p_org_id, l_module_id, p_user_id);

Line: 742

	select count(*) into l_count
	from FLM_EKB_PREFERENCE_VALUES
	where level_id = l_level_id
		  and preference_id = p_preference_id;

Line: 752

			    select max(sequence_number)
				into l_seq_max
				from flm_ekb_preference_values
				where preference_id = 13;

Line: 763

						select attribute_value_code
						into l_value
						from flm_ekb_preference_values
						where preference_id = p_preference_id
							  and attribute_name = l_code
							  and sequence_number <= l_seq_max
							  and level_id = l_level_id;

Line: 771

						select attribute_enable_flag
						into l_enable_flag
						from flm_ekb_preference_values
						where preference_id = p_preference_id
							  and attribute_name = l_code
							  and sequence_number <= l_seq_max
							  and level_id = l_level_id;

Line: 785

					insertRow(p_preference_id, l_level_id, l_seq, l_code, l_value, l_enable_flag, p_user_id);

Line: 787

				/*now delete the previously existing rows*/
				flm_ekanban_logger.log(flm_ekanban_logger.FULL_LOGGING, 'deleting previously existing rows', l_retstatus);

Line: 789

				delete from flm_ekb_preference_values
				where preference_id = 13
				      and sequence_number <= l_seq_max
				      and level_id = l_level_id
				      and attribute_name in (
					select attribute_name from flm_ekb_preference_values
					where preference_id = 13
					  and sequence_number > l_seq_max
					  and level_id = l_level_id);

Line: 798

				/*TODO: if needed we can update sequence_number for remaining rows
				  but currently dont see a need for it*/
		end if;--p_preference_id=13

Line: 802

	/*for Unmoved Cards, insert rows from mfg_lookup (current statuses)
	  for other preferences, insert from Site level*/
		if(p_preference_id=13) then
			/*insert rows for all system/custom statuses*/
			l_seq := 0;

Line: 812

					select attribute_value_code
					into l_value
					from flm_ekb_preference_values
					where preference_id = p_preference_id
						  and attribute_name = l_code
						  and level_id = 1;

Line: 824

				insertRow(13, l_level_id, l_seq, l_code, l_value, l_enable_flag, p_user_id);

Line: 827

			flm_ekanban_logger.log(flm_ekanban_logger.FULL_LOGGING, 'inserting into flm_ekb_preference_values at Org from Site', l_retstatus);

Line: 828

			insert into flm_ekb_preference_values
			(
			PREFERENCE_VALUE_ID,
			PREFERENCE_ID,
			LEVEL_ID,
			SEQUENCE_NUMBER,
			ATTRIBUTE_NAME,
			ATTRIBUTE_VALUE_CODE,
			CREATED_BY,
			CREATION_DATE,
			LAST_UPDATED_BY,
			LAST_UPDATE_DATE,
			LAST_UPDATE_LOGIN,
			OBJECT_VERSION_NUMBER
			)
			select
			flm_ekb_pref_val_seq.nextval,
			fepv.preference_id,
			l_level_id,
			fepv.sequence_number,
			fepv.attribute_name,
			fepv.attribute_value_code,
			p_user_id,
			sysdate, --CREATION_DATE,
			p_user_id, --LAST_UPDATED_BY,
			sysdate, --LAST_UPDATE_DATE,
			p_user_id, --LAST_UPDATE_LOGIN,
			1 --OBJECT_VERSION_NUMBER
			from flm_ekb_preference_values fepv
			where preference_id = p_preference_id
			and level_id = 1;

Line: 862

						  p_procName =>'flm_kanban_config_params.insertRowsPrefValues',
						  p_procReturnStatus => l_retstatus,
                          p_msg => 'success',
                          x_returnStatus => l_retstatus);

Line: 868

procedure delete_disabled_card_status(p_org_id number) is
l_level_id number;

Line: 873

delete from flm_ekb_preference_values
where preference_id = 13
	and level_id = l_level_id
	and attribute_name not in (
	select ml.lookup_code
	from mfg_lookups ml
	where lookup_type = 'MTL_KANBAN_SUPPLY_STATUS'
	      and to_number(ml.lookup_code) <= 8 /*only system/custom statuses till 8 will be honored in ekanban*/
	      and ml.enabled_flag = 'Y' /*consider only enabled status*/
	      and (ml.end_date_active is null OR ml.end_date_active > sysdate));

Line: 886

procedure delete_preference_values(p_preference_id number, p_org_id number) is
l_level_id number;

Line: 891

delete from flm_ekb_preference_values
where preference_id = p_preference_id
	and level_id = l_level_id;

Line: 901

	select ml.meaning into l_label_meaning
	from mfg_lookups ml
	where lookup_code = p_label_code and
  lookup_type in
	(
		select preference_name_lookup_type from flm_ekb_preference_definitions
    where preference_id = p_preference_id
	);

Line: 914

    select ml.meaning
    from mfg_lookups ml
    where ml.lookup_code = to_number(p_lookup_code)
		  and ml.lookup_type = p_lookup_type;

Line: 932

    select fepd.PREFERENCE_NAME_LOOKUP_TYPE
    from flm_ekb_preference_definitions fepd
    where fepd.preference_id = p_preference_id;

Line: 966

  select ml.meaning from mfg_lookups ml
  where ml.lookup_type = 'MTL_KANBAN_SUPPLY_STATUS'
	  and ml.lookup_code not in
	  (
		select  attribute_name
		from flm_ekb_preference_values v
		where preference_id = 13
		  and level_id = p_level_id
	  )
	  and ml.enabled_flag = 'Y'
	  and (ml.end_date_active is null OR ml.end_date_active > sysdate)
	  and ml.lookup_code <= '8';

Line: 1004

	select v.attribute_name
	from flm_ekb_preference_values v
	where v.attribute_name in
	(
		--list of disabled codes
		select to_char(ml.lookup_code)
		from mfg_lookups ml
		where lookup_type = 'MTL_KANBAN_SUPPLY_STATUS'
		  and (enabled_flag = 'N' or ml.end_date_active <= sysdate)
	)
	and v.level_id = p_level_id;

Line: 1028

	  select ml.meaning into l_meaning
	  from mfg_lookups ml
	  where ml.lookup_type = 'MTL_KANBAN_SUPPLY_STATUS'
		and ml.lookup_code = c_new_disabled_status.attribute_name;

Line: 1037

		l_ret_val := 'Following statuses will be deleted on clicking "Apply": ' || l_ret_val;