APPS.MSD_COMMON_UTILITIES SQL Statements

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

Line: 17

  SELECT decode( m2a_dblink,
                      null, '',
                      '@'||m2a_dblink),
              instance_code,
              apps_ver,
              gmt_difference/24.0,
              instance_type
         INTO p_dblink,
              p_icode,
              p_apps_ver,
              p_dgmt,
              p_instance_type
         FROM MSC_APPS_INSTANCES
         WHERE instance_id= p_instance_id;

Line: 51

        SELECT decode( m2a_dblink,
                      null, '',
                      '@'||m2a_dblink)
         INTO p_dblink
         FROM MSC_APPS_INSTANCES
         WHERE instance_id= p_instance_id;

Line: 81

  select level_pk
  into l_ret
  from msd_level_values
  where instance = p_instance_id
  and p_sr_key = sr_level_pk
  and level_id = nvl(p_level_id, l_def_level_id);

Line: 88

  select level_pk
  into l_ret
  from msd_level_values
  where instance = p_instance_id
  and level_value = p_val
  and level_id = nvl(p_level_id, l_def_level_id);

Line: 109

  select level_pk
  into l_ret
  from msd_level_values
  where instance = p_instance_id
  and p_sr_key = sr_level_pk
  and level_id = nvl(p_level_id, l_def_level_id);

Line: 116

  select level_pk
  into l_ret
  from msd_level_values
  where instance = p_instance_id
  and level_value = p_val
  and level_id = nvl(p_level_id, l_def_level_id);

Line: 137

  select level_pk
  from msd_level_values
  where instance = p_instance_id
  and sr_level_pk = p_sr_key
  and level_id = p_level_id;

Line: 144

  select level_pk
  from msd_level_values
  where instance = p_instance_id
  and level_value = p_val
  and level_id = p_level_id;

Line: 183

  select level_pk
  into l_ret
  from msd_level_values
  where instance = p_instance_id
  and p_sr_key = sr_level_pk
  and level_id = nvl(p_level_id, l_def_level_id);

Line: 190

  SELECT level_pk
  into l_ret
  from msd_level_values
  where instance = p_instance_id
  and level_value = p_val
  and level_id = nvl(p_level_id, l_def_level_id);

Line: 212

  select level_pk
  into l_ret
  from msd_level_values
  where instance = p_instance_id
  and p_sr_key = sr_level_pk
  and level_id = nvl(p_level_id, l_def_level_id);

Line: 219

  select level_pk
  into l_ret
  from msd_level_values
  where instance = p_instance_id
  and level_value = p_val
  and level_id = nvl(p_level_id, l_def_level_id);

Line: 241

  select level_pk
  into l_ret
  from msd_level_values
  where instance = p_instance_id
  and p_sr_key = sr_level_pk
  and level_id = nvl(p_level_id, l_def_level_id);

Line: 248

  select level_pk
  into l_ret
  from msd_level_values
  where instance = p_instance_id
  and level_value = p_val
  and level_id = nvl(p_level_id, l_def_level_id);

Line: 270

  select level_pk
  into l_ret
  from msd_level_values
  where instance = p_instance_id
  and p_sr_key = sr_level_pk
  and level_id = nvl(p_level_id, l_def_level_id);

Line: 277

  select level_pk
  into l_ret
  from msd_level_values
  where instance = p_instance_id
  and level_value = p_val
  and level_id = nvl(p_level_id, l_def_level_id);

Line: 300

  select level_pk
  into l_ret
  from msd_level_values
  where instance = p_instance_id
  and p_sr_key = sr_level_pk
  and level_id = nvl(p_level_id, l_def_level_id);

Line: 307

  select level_pk
  into l_ret
  from msd_level_values
  where instance = p_instance_id
  and level_value = p_val
  and level_id = nvl(p_level_id, l_def_level_id);

Line: 323

  select msd_level_values_s.nextval into x_temp
  from   sys.dual ;

Line: 347

  select level_name
  into l_level_name
  from msd_levels
  where level_id = p_level_id
    and plan_type is null;

Line: 373

    select DELIVERY_CALENDAR_CODE
    from msc_item_suppliers
    where plan_id = p_plan_id
    and sr_instance_id = p_sr_instance_id
    and organization_id = p_organization_id
    and inventory_item_id = p_inventory_item_id
    and supplier_id = p_supplier_id
    and supplier_site_id = p_supplier_site_id
    and using_organization_id = p_using_organization_id;

Line: 384

     select calendar_code
     from msc_trading_partners
     where partner_type = 3
     and sr_tp_id = p_organization_id
     and sr_instance_id = p_sr_instance_id;

Line: 419

    select min(period_start_date) -1 period_end_date
    from msc_safety_stocks
    where plan_id = p_plan_id
    and sr_instance_id = p_sr_instance_id
    and organization_id = p_organization_id
    and inventory_item_id = p_inventory_item_id
    and period_start_date > p_period_start_date;

Line: 428

     select CURR_CUTOFF_DATE
     from msc_plans
     where plan_id = p_plan_id;

Line: 458

    select MSD_SR_LEVEL_PK_S.nextval
    into v_ret
    from sys.dual;

Line: 462

    select count(*)
    into v_count
    from msd_level_values
    where sr_level_pk = to_char(v_ret);

Line: 489

    select MSD_SR_LEVEL_PK_S.nextval
    into v_next
    from sys.dual;

Line: 493

    select count(*)
    into v_count1
    from msd_st_level_values
    where sr_instance_code = p_instance_code
    and process_flag       = G_IN_PROCESS
    and sr_level_pk        = to_char(v_next);

Line: 500

    select count(*)
    into v_count2
    from msd_level_values
    where instance = to_char(p_instance_id)
    and sr_level_pk = to_char(v_next);

Line: 526

        SELECT
              dimension_code
         INTO
              p_dimension_code
         FROM MSD_LEVELS
         WHERE level_id = p_level_id
           AND plan_type is null;

Line: 546

  select level_value
  from msd_level_values
  where level_pk = p_level_pk;

Line: 576

select  t.conversion_rate      std_to_rate,
        t.uom_class            std_to_class,
        f.conversion_rate      std_from_rate,
        f.uom_class            std_from_class
from  msc_uom_conversions t,
      msc_uom_conversions f
where t.inventory_item_id in (item_id, 0) and
      t.uom_code = to_unit and
      nvl(t.disable_date, trunc(sysdate) + 1) > trunc(sysdate) and
      f.inventory_item_id in (item_id, 0) and
      f.uom_code = from_unit and
      nvl(f.disable_date, trunc(sysdate) + 1) > trunc(sysdate)
order by t.inventory_item_id desc, f.inventory_item_id desc;

Line: 595

select decode(from_uom_class, p_from_class, 1, 2) from_flag,
       decode(to_uom_class, p_to_class, 1, 2) to_flag,
       conversion_rate rate
from   msc_uom_class_conversions
where  inventory_item_id = item_id and
       nvl(disable_date, trunc(sysdate) + 1) > trunc(sysdate) and
       ( (from_uom_class = p_from_class and to_uom_class = p_to_class) or
         (from_uom_class = p_to_class   and to_uom_class = p_from_class) );

Line: 767

      select to_number(parameter_value)
      into l_master_org
      from msd_setup_parameters
      where parameter_name = 'MSD_MASTER_ORG';

Line: 772

     select NVL(uom_code,'Ea')
     into   l_master_uom
     from msc_system_items
     where inventory_item_id = item_id
     and   organization_id = l_master_org;

Line: 853

select sr_parent_level_pk
from msd_level_associations
where
instance = p_instance_id and
level_id = p_level_id and
parent_level_id = p_parent_level_id and
sr_level_pk = p_sr_level_pk;

Line: 902

select sr_level_pk
from msd_level_associations
where
instance = p_instance_id and
level_id = p_level_id and
parent_level_id = p_parent_level_id and
sr_parent_level_pk = p_sr_level_pk and
rownum < 2;

Line: 972

select mol.level_id
from msd_dp_scenario_output_levels mol
where
    mol.demand_plan_id = p_demand_plan_id
and mol.scenario_id = p_scenario_id
and mol.level_id = 7;

Line: 1023

SELECT
1
FROM
msd_level_associations
WHERE
instance = p_instance
and level_id = 1         -- item
and sr_level_pk = p_inventory_item_id
and parent_level_id = 3   -- product family
and sr_parent_level_pk <> '-777';   -- others

Line: 1079

select /*+ CACHE */  decode(p_bucket_type, 9, day, 1, week_end_date, month_end_date) from msd_time
where
   calendar_type = p_calendar_type and
   calendar_code = p_calendar_code and
   p_date = day;

Line: 1109

select mlv.level_pk
from msc_tp_id_lid mtp, msd_level_values mlv
where
   mtp.tp_id = p_tp_id
   and mtp.sr_instance_id = p_sr_instance_id
   and mtp.partner_type = 2
   and mtp.sr_company_id = -1
   and mtp.sr_instance_id = mlv.instance
   and mtp.sr_tp_id = mlv.sr_level_pk
   and mlv.level_id = 15
   and rownum < 2;

Line: 1215

  execute immediate 'select dbms_aw.interp(:1) from dual' into v_ret using cmd;

Line: 1222

  execute immediate 'select dbms_aw.interpclob(:1) from dual' into v_ret using cmd;

Line: 1242

select distinct decode(P_TIME_LEVEL_ID,
1,week_end_date,
2,month_end_date,
3,month_end_date,
4,quarter_end_date,
5,year_end_date,
6,month_end_date,
7,quarter_end_date,
8,year_end_date,
10,week_end_date,
11,month_end_date,
12,quarter_end_date,
13,year_end_date)
from msd_time
where calendar_type = decode(P_TIME_LEVEL_ID,1,2,2,2,3,3,4,3,5,3,6,1,7,1,8,1,10,4,11,4,12,4,13,4)
and calendar_code = P_CALENDAR_CODE
and decode(P_TIME_LEVEL_ID,
1,week_end_date,
2,month_end_date,
3,month_end_date,
4,quarter_end_date,
5,year_end_date,
6,month_end_date,
7,quarter_end_date,
8,year_end_date,
10,week_end_date,
11,month_end_date,
12,quarter_end_date,
13,year_end_date) = day
and P_EFFECTIVE_DATE between
decode(P_TIME_LEVEL_ID,
1,week_start_date,
2,month_start_date,
3,month_start_date,
4,quarter_start_date,
5,year_start_date,
6,month_start_date,
7,quarter_start_date,
8,year_start_date,
10,week_start_date,
11,month_start_date,
12,quarter_start_date,
13,year_start_date) and day
and P_OFFSET = 1;

Line: 1288

select bucket_date from
(select distinct decode(P_TIME_LEVEL_ID,
1,week_end_date,
2,month_end_date,
3,month_end_date,
4,quarter_end_date,
5,year_end_date,
6,month_end_date,
7,quarter_end_date,
8,year_end_date,
10,week_end_date,
11,month_end_date,
12,quarter_end_date,
13,year_end_date) bucket_date
from msd_time
where calendar_type = decode(P_TIME_LEVEL_ID,1,2,2,2,3,3,4,3,5,3,6,1,7,1,8,1,10,4,11,4,12,4,13,4)
and calendar_code = P_CALENDAR_CODE
and decode(P_TIME_LEVEL_ID,
1,week_end_date,
2,month_end_date,
3,month_end_date,
4,quarter_end_date,
5,year_end_date,
6,month_end_date,
7,quarter_end_date,
8,year_end_date,
10,week_end_date,
11,month_end_date,
12,quarter_end_date,
13,year_end_date) = day
and day >= p_date
order by bucket_date)
where rownum <= p_offset;

Line: 1323

select bucket_date from
(select distinct decode(P_TIME_LEVEL_ID,
1,week_end_date,
2,month_end_date,
3,month_end_date,
4,quarter_end_date,
5,year_end_date,
6,month_end_date,
7,quarter_end_date,
8,year_end_date,
10,week_end_date,
11,month_end_date,
12,quarter_end_date,
13,year_end_date) bucket_date
from msd_time
where calendar_type = decode(P_TIME_LEVEL_ID,1,2,2,2,3,3,4,3,5,3,6,1,7,1,8,1,10,4,11,4,12,4,13,4)
and calendar_code = P_CALENDAR_CODE
and decode(P_TIME_LEVEL_ID,
1,week_end_date,
2,month_end_date,
3,month_end_date,
4,quarter_end_date,
5,year_end_date,
6,month_end_date,
7,quarter_end_date,
8,year_end_date,
10,week_end_date,
11,month_end_date,
12,quarter_end_date,
13,year_end_date) = day
and day <= p_date
order by bucket_date desc)
where rownum <= abs(p_offset);

Line: 1399

select distinct decode(P_TIME_LEVEL_ID,
1,week_start_date,
2,month_start_date,
3,month_start_date,
4,quarter_start_date,
5,year_start_date,
6,month_start_date,
7,quarter_start_date,
8,year_start_date,
10,week_start_date,
11,month_start_date,
12,quarter_start_date,
13,year_start_date)
from msd_time
where calendar_type = decode(P_TIME_LEVEL_ID,1,2,2,2,3,3,4,3,5,3,6,1,7,1,8,1,10,4,11,4,12,4,13,4)
and calendar_code = P_CALENDAR_CODE
and decode(P_TIME_LEVEL_ID,
1,week_start_date,
2,month_start_date,
3,month_start_date,
4,quarter_start_date,
5,year_start_date,
6,month_start_date,
7,quarter_start_date,
8,year_start_date,
10,week_start_date,
11,month_start_date,
12,quarter_start_date,
13,year_start_date) = day
and P_EFFECTIVE_DATE between day and
decode(P_TIME_LEVEL_ID,
1,week_end_date,
2,month_end_date,
3,month_end_date,
4,quarter_end_date,
5,year_end_date,
6,month_end_date,
7,quarter_end_date,
8,year_end_date,
10,week_end_date,
11,month_end_date,
12,quarter_end_date,
13,year_end_date)
and P_OFFSET = 1;

Line: 1445

select bucket_date from
(select distinct decode(P_TIME_LEVEL_ID,
1,week_start_date,
2,month_start_date,
3,month_start_date,
4,quarter_start_date,
5,year_start_date,
6,month_start_date,
7,quarter_start_date,
8,year_start_date,
10,week_start_date,
11,month_start_date,
12,quarter_start_date,
13,year_start_date) bucket_date
from msd_time
where calendar_type = decode(P_TIME_LEVEL_ID,1,2,2,2,3,3,4,3,5,3,6,1,7,1,8,1,10,4,11,4,12,4,13,4)
and calendar_code = P_CALENDAR_CODE
and decode(P_TIME_LEVEL_ID,
1,week_start_date,
2,month_start_date,
3,month_start_date,
4,quarter_start_date,
5,year_start_date,
6,month_start_date,
7,quarter_start_date,
8,year_start_date,
10,week_start_date,
11,month_start_date,
12,quarter_start_date,
13,year_start_date) = day
and day >= p_date
order by bucket_date)
where rownum <= p_offset;

Line: 1480

select bucket_date from
(select distinct decode(P_TIME_LEVEL_ID,
1,week_start_date,
2,month_start_date,
3,month_start_date,
4,quarter_start_date,
5,year_start_date,
6,month_start_date,
7,quarter_start_date,
8,year_start_date,
10,week_start_date,
11,month_start_date,
12,quarter_start_date,
13,year_start_date) bucket_date
from msd_time
where calendar_type = decode(P_TIME_LEVEL_ID,1,2,2,2,3,3,4,3,5,3,6,1,7,1,8,1,10,4,11,4,12,4,13,4)
and calendar_code = P_CALENDAR_CODE
and decode(P_TIME_LEVEL_ID,
1,week_start_date,
2,month_start_date,
3,month_start_date,
4,quarter_start_date,
5,year_start_date,
6,month_start_date,
7,quarter_start_date,
8,year_start_date,
10,week_start_date,
11,month_start_date,
12,quarter_start_date,
13,year_start_date) = day
and day <= p_date
order by bucket_date desc)
where rownum <= abs(p_offset);

Line: 1558

select count(distinct decode(P_TIME_LEVEL_ID,
1,week_start_date,
2,month_start_date,
3,month_start_date,
4,quarter_start_date,
5,year_start_date,
6,month_start_date,
7,quarter_start_date,
8,year_start_date,
10,week_start_date,
11,month_start_date,
12,quarter_start_date,
13,year_start_date))
from msd_time
where calendar_type = decode(P_TIME_LEVEL_ID,1,2,2,2,3,3,4,3,5,3,6,1,7,1,8,1,10,4,11,4,12,4,13,4)
and calendar_code = P_CALENDAR_CODE
and decode(P_TIME_LEVEL_ID,
1,week_start_date,
2,month_start_date,
3,month_start_date,
4,quarter_start_date,
5,year_start_date,
6,month_start_date,
7,quarter_start_date,
8,year_start_date,
10,week_start_date,
11,month_start_date,
12,quarter_start_date,
13,year_start_date) = day
and day between P_START_DATE and P_END_DATE;

Line: 1608

select sr_level_pk, level_value
from msd_level_values
where instance = P_INSTANCE_ID
and level_id = P_LEVEL_ID
and level_pk = P_LEVEL_PK;

Line: 1638

  select dp_enabled_flag
  into l_ret
  from msd_level_values
  where instance = p_instance_id
  and p_sr_key = sr_level_pk
  and level_id = nvl(p_level_id, l_def_level_id);

Line: 1645

  select dp_enabled_flag
  into l_ret
  from msd_level_values
  where instance = p_instance_id
  and level_value = p_val
  and level_id = nvl(p_level_id, l_def_level_id);

Line: 1676

	select 	meaning into l_system_attribute1
	from 	fnd_lookup_values_vl
	where
		LOOKUP_TYPE='MSD_LEVEL_VALUE_DESC' and
		LOOKUP_CODE= p_lookup_code;

Line: 1696

      select sum(mad.PERCENTAGE_PURCHASE_PRICE)/((p_period_end_date - p_period_start_date +1 )*100) into l_auth_percent
      from MSC_ASL_AUTH_DETAILS mad,
           msd_time mt,
           msd_demand_plans mdp
      where       p_supplier_id = mad.supplier_id and
                  p_sr_instance_id = mad.sr_instance_id and
                  p_organization_id = mad.organization_id and
                  p_inventory_item_id = mad.inventory_item_id and
                  p_supplier_site_id = mad.supplier_site_id and
                  mdp.demand_plan_id = p_demand_plan_id and
                  mt.day between trunc(mdp.plan_start_date+mad.start_days) and trunc(mdp.plan_START_date+mad.end_days) and
                  mt.calendar_code='GREGORIAN'and
                  mt.calendar_type=1 and
                  mt.day between p_period_start_date and p_period_end_date ;