APPS.MSC_ANALYSIS_PKG SQL Statements

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

Line: 26

  select min(detail_date)-1
  from msc_bis_inv_detail
  where plan_id = p_plan
  and nvl(period_type,0) = p_period_type
  and nvl(detail_level,0) = p_detail_level
  and detail_date > p_curr_pr_date;

Line: 37

  select max(detail_date)+1
  from msc_bis_inv_detail
  where plan_id = p_plan
  and sr_instance_id = p_instance
  and organization_id = p_org
  and inventory_item_id = p_item
  and nvl(period_type,0) = p_period_type
  and nvl(detail_level,0) = p_detail_level
  and detail_date < p_curr_pr_date;

Line: 48

  select plan_start_date, curr_cutoff_date
  from msc_plans
  where plan_id = p_plan;

Line: 54

  select sr_instance_id, organization_id
  from msc_plan_organizations
  where plan_id = l_plan_id;

Line: 78

  update msc_form_query
  set number1 = l_cat_set_id,
      number2 = p_plan_type
  where query_id = l_mfq_query_id;

Line: 83

    INSERT INTO msc_form_query (QUERY_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, NUMBER1, NUMBER2) values
      (l_mfq_query_id, sysdate, -1, sysdate, -1, l_cat_set_id, p_plan_type);

Line: 91

    select plan_type
    from msc_plans
    where plan_id = p_plan_id;

Line: 155

  SELECT distinct sr_category_id
  FROM msc_item_categories
  WHERE category_set_id = l_cat_set
  and category_name in (select category_name
    from msc_item_categories
    where category_set_id = l_cat_set
    and sr_category_id = l_cat_id
    and rownum = 1);

Line: 185

  select msc_form_query_s.nextval
  from dual;

Line: 202

    SELECT sr_instance_id, organization_id
    FROM msc_plans
    WHERE plan_id = p_plan_id;

Line: 208

    SELECT count(distinct mpsd.period_start_date)
    FROM   msc_trading_partners tp,
          msc_period_start_dates mpsd,
          msc_plans mp
    WHERE  mpsd.calendar_code = tp.calendar_code
    and mpsd.sr_instance_id = tp.sr_instance_id
    and mpsd.exception_set_id = tp.calendar_exception_set_id
    and tp.sr_instance_id = p_sr_instance_id
    and tp.sr_tp_id = p_org_id
    and tp.partner_type =3
    and mp.plan_id = p_plan_id
    and mpsd.period_start_date between mp.data_start_date and mp.cutoff_date;

Line: 224

     SELECT count(distinct mbp.period_name)
     FROM   msc_bis_periods mbp,
            msc_plans mp
     WHERE  mbp.organization_id = mp.organization_id
     and    mbp.sr_instance_id = mp.sr_instance_id
     and ((mbp.start_date between nvl(mp.curr_start_date, sysdate)
                            and mp.cutoff_date
         or mbp.end_date between nvl(mp.curr_start_date,sysdate)
                            and mp.cutoff_date) or
         (mp.curr_start_date between mbp.start_date and mbp.end_date))
     and mp.plan_id = p_plan_id;

Line: 284

  l_select varchar2(300);

Line: 285

  l_insert varchar2(300);

Line: 290

  l_mfq_select varchar2(300);

Line: 291

  l_mfq_insert varchar2(600);

Line: 295

  l_plan_insert varchar2(300);

Line: 298

  l_org_insert varchar2(300);

Line: 301

  l_cate_insert varchar2(300);

Line: 304

  l_pr_insert varchar2(300);

Line: 307

  l_plan_org_cate_insert varchar2(300);

Line: 312

  l_mfq_plan_insert varchar2(300);

Line: 315

  l_mfq_org_insert varchar2(300);

Line: 318

  l_mfq_cate_insert varchar2(300);

Line: 321

  l_mfq_plan_org_cate_insert varchar2(300);

Line: 352

  l_select := ' INSERT INTO msc_form_query ( '||
	' QUERY_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, '||
	' NUMBER5, NUMBER6, NUMBER7, NUMBER8, NUMBER9, NUMBER10, '||
	' NUMBER1, CHAR1, NUMBER2, NUMBER3, CHAR2, NUMBER4, CHAR3, DATE1, CHAR4, '||
	' NUMBER11, NUMBER12, CHAR5) ';

Line: 358

  l_insert := ' SELECT '||l_seq_id ||' , sysdate, -1,	sysdate, -1, '||
	' round(sum(ss_cost_no_post), '|| l_round || ' ), '||
	' round(sum(ss_cost_post), '|| l_round ||'), '||
	' round(sum(ss_cost_savings), '|| l_round ||' ), '||
	' round(sum(ss_value_no_post), '|| l_round ||' ), '||
	' round(sum(ss_value_post), '|| l_round ||' ), '||
	' round(sum(ss_value_savings), '|| l_round ||'), ';

Line: 368

  l_plan_insert := ' plan_id, plan_name, '||
	' to_number(null), to_number(null), to_char(null), '||
	' to_number(null), to_char(null), detail_date, plan_name, '||
	' to_number(null), to_number(null), to_char(null)';

Line: 373

  l_org_insert := ' plan_id, plan_name, '||
	' sr_instance_id, organization_id, org_code, '||
	' to_number(null), to_char(null), detail_date, plan_name||'' - ''||org_code ,'||
	' to_number(null), to_number(null), to_char(null)';

Line: 378

  l_cate_insert := ' plan_id, plan_name, '||
	' to_number(null), to_number(null), to_char(null), '||
	' category_id, category_name, detail_date, plan_name||'' - ''||category_name ,'||
	' to_number(null), to_number(null), to_char(null)';

Line: 383

  l_pr_insert := ' plan_id, plan_name, '||
	' to_number(null), to_number(null), to_char(null), '||
	' to_number(null), to_char(null), detail_date, plan_name||'' - ''||detail_date ,'||
	' detail_level, period_type, period_type_url ';

Line: 388

  l_plan_org_cate_insert := ' plan_id, plan_name, '||
	' sr_instance_id, organization_id, org_code, '||
	' category_id, category_name, detail_date, plan_name||''-''||org_code||''-''||category_name ,'||
	' to_number(null), to_number(null), to_char(null)';

Line: 408

  l_mfq_select := ' INSERT INTO msc_form_query ( '||
  ' QUERY_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, '||
  ' NUMBER5, NUMBER6, NUMBER7, NUMBER8, NUMBER9, NUMBER10, '||
  ' NUMBER1, CHAR1, NUMBER2, NUMBER3, CHAR2, NUMBER4, CHAR3, DATE1, CHAR4, '||
  ' NUMBER11, NUMBER12, CHAR5 ) ';

Line: 414

  l_mfq_insert := ' SELECT '||l_seq_id2 ||' , sysdate, -1,	sysdate, -1, '||
  ' round(sum(mfq1.NUMBER5), '|| l_round || ' ), '||
  ' round(sum(mfq1.NUMBER6), '|| l_round ||'), '||
  ' round(sum(mfq1.NUMBER7), '|| l_round ||' ), '||
  ' round(sum(mfq1.NUMBER8)/MSC_ANALYSIS_PKG.get_num_periods(mfq1.NUMBER1, '||l_calendar_type||'), '|| l_round ||' ), '||
  ' round(sum(mfq1.NUMBER9)/MSC_ANALYSIS_PKG.get_num_periods(mfq1.NUMBER1, '||l_calendar_type||'), '|| l_round ||' ), '||
  ' round(sum(mfq1.NUMBER10)/MSC_ANALYSIS_PKG.get_num_periods(mfq1.NUMBER1, '||l_calendar_type||'), '|| l_round ||'), ';

Line: 424

  l_mfq_plan_insert := ' mfq1.number1, mfq1.char1, '||
  ' to_number(null), to_number(null), to_char(null), '||
  ' to_number(null), to_char(null), to_date(null), mfq1.char1, '||
  ' to_number(null), to_number(null), to_char(null) ';

Line: 432

  l_mfq_org_insert := ' mfq1.number1, mfq1.char1, '||
	' mfq1.NUMBER2, mfq1.NUMBER3, mfq1.CHAR2, '||
	' to_number(null), to_char(null), to_date(null), mfq1.char1||'' - ''||mfq1.char2 ,'||
	' to_number(null), to_number(null), to_char(null)';

Line: 440

  l_mfq_cate_insert := ' mfq1.number1, mfq1.char1,  '||
	' to_number(null), to_number(null), to_char(null), '||
	' mfq1.NUMBER4, mfq1.CHAR3, to_date(null),  mfq1.char1||'' - ''||mfq1.CHAR3 ,'||
	' to_number(null), to_number(null), to_char(null)';

Line: 448

  l_mfq_plan_org_cate_insert := ' mfq1.number1, mfq1.char1, '||
	' mfq1.NUMBER2, mfq1.NUMBER3, mfq1.CHAR2, '||
	' mfq1.NUMBER4, mfq1.CHAR3, to_date(null),  mfq1.char1||''-''||mfq1.char2||''-''||mfq1.CHAR3 ,'||
	' to_number(null), to_number(null), to_char(null)';

Line: 516

    l_sql_stmt := l_select||l_insert||l_plan_insert||l_from||l_where||l_plan_groupby;

Line: 520

    l_mfq_sql_stmt := l_mfq_select||l_mfq_insert||l_mfq_plan_insert||l_mfq_from||l_mfq_where||l_mfq_plan_groupby;

Line: 527

    l_sql_stmt := l_select||l_insert||l_org_insert||l_from||l_where||l_org_groupby;

Line: 532

    l_mfq_sql_stmt := l_mfq_select||l_mfq_insert||l_mfq_org_insert||l_mfq_from||l_mfq_where||l_mfq_org_groupby;

Line: 539

    l_sql_stmt := l_select||l_insert||l_cate_insert||l_from||l_where||l_cate_groupby;

Line: 544

    l_mfq_sql_stmt := l_mfq_select||l_mfq_insert||l_mfq_cate_insert||l_mfq_from||
                                                    l_mfq_where||l_mfq_cate_groupby;

Line: 552

    l_sql_stmt := l_select||l_insert||l_pr_insert||l_from||l_where||l_pr_groupby;

Line: 558

    l_sql_stmt := l_select||l_insert||l_pr_insert||l_from||l_where||l_pr_groupby;

Line: 564

    l_sql_stmt := l_select||l_insert||l_plan_org_cate_insert||l_from||l_where||l_plan_org_cate_groupby;

Line: 569

    l_mfq_sql_stmt := l_mfq_select||l_mfq_insert||l_mfq_plan_org_cate_insert||
                                    l_mfq_from||l_mfq_where||l_mfq_plan_org_cate_groupby;

Line: 610

  l_sql_stmt := ' SELECT distinct plan_id, period_type, detail_level, detail_date '||
    ' from msc_bis_inv_detail '||
    ' where plan_id in ('|| arg_plan_list ||')';

Line: 650

      INSERT INTO msc_form_query (
        QUERY_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY,
        NUMBER1, number2, number3, date1, date2 )
      values
        ( l_mfq_query_id , sysdate, -1, sysdate, -1,
        l_cur_plan_id, nvl(l_period_type,0), nvl(l_detail_level,0), l_temp_date,
        plan_end_date);

Line: 666

      INSERT INTO msc_form_query (
        QUERY_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY,
        NUMBER1, number2, number3, date1, date2 )
      values
        ( l_mfq_query_id , sysdate, -1, sysdate, -1,
        l_plan_id, nvl(l_period_type,0), nvl(l_detail_level,0), l_start_date, l_end_date);

Line: 681

      INSERT INTO msc_form_query (
        QUERY_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY,
        NUMBER1, number2, number3, date1, date2 )
      values
        ( l_mfq_query_id , sysdate, -1, sysdate, -1,
        l_plan_id, nvl(l_period_type,0), nvl(l_detail_level,0), l_end_date, plan_end_date);

Line: 710

  l_select varchar2(300);

Line: 711

  l_insert varchar2(500);

Line: 715

  l_plan_insert varchar2(300);

Line: 717

  l_plan_dflt_insert varchar2(200);

Line: 719

  l_org_insert varchar2(300);

Line: 721

  l_org_dflt_insert varchar2(200);

Line: 723

  l_cate_insert varchar2(300);

Line: 725

  l_cate_dflt_insert varchar2(200);

Line: 727

  l_item_insert varchar2(300);

Line: 729

  l_item_dflt_insert varchar2(200);

Line: 731

  l_demand_class_insert varchar2(300);

Line: 733

  l_demand_class_dflt_insert varchar2(200);

Line: 735

  l_pr_insert varchar2(300);

Line: 737

  l_pr_dflt_insert varchar2(200);

Line: 756

  l_select := ' INSERT INTO msc_form_query ( '||
	' QUERY_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, '||
	' NUMBER10, NUMBER11, NUMBER12,'||
	' NUMBER1, CHAR1, NUMBER2, NUMBER3, CHAR2, '||
	' NUMBER4, CHAR3, '||
	' NUMBER5, CHAR4, '||
	' CHAR5, DATE1, CHAR6, '||
	' number14, number15, char7 ) ';

Line: 765

  l_insert := ' SELECT '||l_seq_id ||' , sysdate, -1,	sysdate, -1, '||
        '  round(
            decode(sum(achieved_service_level_qty2), 0, 0,
            sum(achieved_service_level_qty1)/sum(achieved_service_level_qty2))
        ,'||l_round ||'),'||
        '  round(avg(target_service_level),'||l_round ||'),';

Line: 774

  l_plan_insert := ' plan_id, plan_name, '||
	' to_number(null), to_number(null), to_char(null), '||
	' to_number(null), to_char(null), '||
	' to_number(null), to_char(null), '||
	' to_char(null), to_date(null) , plan_name, '||
        ' to_number(null), to_number(null), to_char(null) ';

Line: 781

  l_org_insert := ' plan_id, plan_name, '||
	' sr_instance_id, organization_id, org_code, '||
	' to_number(null), to_char(null), '||
	' to_number(null), to_char(null), '||
	' to_char(null), to_date(null) , plan_name||'' - ''||org_code, ' ||
        ' to_number(null), to_number(null), to_char(null) ';

Line: 788

  l_cate_insert := ' plan_id, plan_name,'||
	' to_number(null), to_number(null), to_char(null), '||
	' category_id, category_name, '||
	' to_number(null), to_char(null), '||
	' to_char(null), to_date(null) ,  plan_name||'' - ''||category_name, ' ||
        ' to_number(null), to_number(null), to_char(null) ';

Line: 795

  l_item_insert := ' plan_id, plan_name,'||
	' to_number(null), to_number(null), to_char(null), '||
	' to_number(null), to_char(null), '||
	' inventory_item_id, item_name, '||
	' to_char(null), to_date(null) ,  plan_name||'' - ''||item_name, ' ||
        ' to_number(null), to_number(null), to_char(null) ';

Line: 802

  l_demand_class_insert := ' plan_id, plan_name, '||
	' to_number(null), to_number(null), to_char(null), '||
	' to_number(null), to_char(null), '||
	' to_number(null), to_char(null), '||
	' demand_class, to_date(null) , '||
	'  plan_name||'' - ''||demand_class, ' ||
        ' to_number(null), to_number(null), to_char(null) ';

Line: 810

  l_pr_insert := ' plan_id, plan_name,'||
	' to_number(null), to_number(null), to_char(null), '||
	' to_number(null), to_char(null), '||
	' to_number(null), to_char(null), '||
	' to_char(null), detail_date ,  plan_name||'' - ''||detail_date, ' ||
	' period_type, detail_level, period_type_url ';

Line: 824

  l_plan_dflt_insert := ' msc_analysis_pkg.get_dflt_value(plan_id, null, 0), ';

Line: 825

  l_org_dflt_insert := ' to_number(null), ';

Line: 826

  l_cate_dflt_insert := ' msc_analysis_pkg.get_dflt_value(plan_id, null, 6, null, '||
	' null, null, null, null, null, category_id), ';

Line: 828

  l_item_dflt_insert := ' to_number(null), ';

Line: 829

  l_demand_class_dflt_insert := ' msc_analysis_pkg.get_dflt_value(plan_id, null, 4, null, null, '||
	' null, demand_class, null, null, null), ';

Line: 831

  l_pr_dflt_insert := ' to_number(null), ';

Line: 928

    l_sql_stmt := l_select||l_insert||l_plan_dflt_insert||l_plan_insert||l_from||l_where||l_plan_groupby;

Line: 934

    l_sql_stmt := l_select||l_insert||l_org_dflt_insert||l_org_insert||l_from||l_where||l_org_groupby;

Line: 941

    l_sql_stmt := l_select||l_insert||l_cate_dflt_insert||l_cate_insert||l_from||l_where||l_cate_groupby;

Line: 947

    l_sql_stmt := l_select||l_insert||l_item_dflt_insert||l_item_insert||l_from||l_where||l_item_groupby;

Line: 953

    l_sql_stmt := l_select||l_insert||l_demand_class_dflt_insert
       ||l_demand_class_insert||l_from||l_where||l_demand_class_groupby;

Line: 960

    l_sql_stmt := l_select||l_insert||l_pr_dflt_insert||l_pr_insert||l_from||l_where||l_pr_groupby;

Line: 966

    l_sql_stmt := l_select||l_insert||l_pr_dflt_insert||l_pr_insert||l_from||l_where||l_pr_groupby;

Line: 984

  select min(detail_date)-1
  from msc_bis_inv_detail
  where plan_id = p_plan
  and sr_instance_id = arg_instance
  and organization_id = arg_org
  and nvl(period_type,0) = p_period_type
  and nvl(detail_level,0) = p_detail_level
  and detail_date > p_curr_pr_date;

Line: 1051

  l_select varchar2(500);

Line: 1052

  l_insert varchar2(1500);

Line: 1057

  l_plan_insert varchar2(300);

Line: 1060

  l_org_insert varchar2(300);

Line: 1063

  l_cate_insert varchar2(300);

Line: 1066

  l_item_insert varchar2(300);

Line: 1069

  l_pr_insert varchar2(300);

Line: 1106

  l_select := ' insert into msc_form_query ( '||
	' query_id, last_update_date, last_updated_by, creation_date, created_by, '||
	' number6, number7, number8, number9, number10, number11, number12, number13, number16,'||
  ' number17, number18, number19, number20,'||
	' number1, char1, number2, number3, char2, '||
	' number4, char3, '||
	' number5, char4, '||
	' date1, char5, number14, number15, char6 ) ';

Line: 1114

  l_insert := ' select '||l_seq_id ||' , sysdate, -1,	sysdate, -1, '||
	' round(sum(nvl(planned_production_cost,0)), '|| l_round ||' ), '||
	' round(sum(nvl(planned_carrying_cost,0)), '|| l_round ||' ), '||
  ' round(sum(nvl(planned_purchasing_cost,0)), '||l_round ||' ), '||
	' round(sum(nvl(planned_tp_cost,0)), '|| l_round ||' ), '||
	' decode(sum(planned_total_cost),0,0, '||
	' round(nvl(sum(planned_production_cost)/sum(planned_total_cost),0)* 100, '||l_round||' )), '||
	' decode(sum(planned_total_cost),0,0, '||
	' round(nvl(sum(planned_carrying_cost)/sum(planned_total_cost),0)* 100, '||l_round||' )), '||
	' decode(sum(planned_total_cost),0,0, '||
	' round(nvl(sum(planned_purchasing_cost)/sum(planned_total_cost),0)* 100, '||l_round||' )), '||
	' decode(sum(planned_total_cost),0,0, '||
	' round(nvl(sum(planned_tp_cost)/sum(planned_total_cost),0)* 100,'|| l_round ||' )), '||
	' round(sum(nvl(planned_revenue,0)), '|| l_round ||' ), '||
	' round(sum(nvl(int_repair_cost,0)), '|| l_round ||' ), '||
	' round(sum(nvl(ext_repair_cost,0)), '|| l_round ||' ), '||
	' decode(sum(planned_total_cost),0,0, '||
	' round(nvl(sum(int_repair_cost)/sum(planned_total_cost),0)* 100,'|| l_round ||' )), '||
	' decode(sum(planned_total_cost),0,0, '||
	' round(nvl(sum(ext_repair_cost)/sum(planned_total_cost),0)* 100,'|| l_round ||' )), ';

Line: 1137

  l_plan_insert := ' plan_id, plan_name, '||
	' to_number(null), to_number(null), to_char(null), '||
	' to_number(null), to_char(null), '||
	' to_number(null), to_char(null), '||
	' to_date(null) , plan_name, '||
	' to_number(null), to_number(null), to_char(null) ';

Line: 1144

  l_org_insert := ' plan_id, plan_name,  '||
	' sr_instance_id, organization_id, org_code, '||
	' to_number(null), to_char(null), '||
	' to_number(null), to_char(null), '||
	' to_date(null) , plan_name||'' - ''||org_code, '||
	' to_number(null), to_number(null), to_char(null) ';

Line: 1151

  l_cate_insert := ' plan_id, plan_name,  '||
	' to_number(null), to_number(null), to_char(null), '||
	' category_id, category_name, '||
	' to_number(null), to_char(null), '||
	' to_date(null) , plan_name||'' - ''||category_name, '||
	' to_number(null), to_number(null), to_char(null) ';

Line: 1158

  l_item_insert := ' plan_id, plan_name, '||
	' to_number(null), to_number(null), to_char(null), '||
	' to_number(null), to_char(null), '||
	' inventory_item_id, item_name, '||
	' to_date(null) , plan_name||'' - ''||item_name ,'||
	' to_number(null), to_number(null), to_char(null) ';

Line: 1165

  l_pr_insert := ' plan_id, plan_name, '||
	' to_number(null), to_number(null), to_char(null), '||
	' to_number(null), to_char(null), '||
	' to_number(null), to_char(null), '||
	' detail_date , plan_name||'' - ''||detail_date ,'||
	' detail_level, period_type, period_type_url ';

Line: 1259

      l_sql_stmt := l_select||l_insert||l_plan_insert||l_from||l_where||l_plan_groupby;

Line: 1262

      l_sql_stmt := l_select||l_insert||l_plan_insert||l_from_maa||l_where|| ' and record_type=4 ' ||l_plan_groupby;

Line: 1269

    l_sql_stmt := l_select||l_insert||l_org_insert||l_from||l_where||l_org_groupby;

Line: 1276

      l_sql_stmt := l_select||l_insert||l_cate_insert||l_from||l_where||l_cate_groupby;

Line: 1279

      l_sql_stmt := l_select||l_insert||l_cate_insert||l_from_maa||l_where|| ' and record_type=4 ' ||l_cate_groupby;

Line: 1286

    l_sql_stmt := l_select||l_insert||l_item_insert||l_from||l_where||l_item_groupby;

Line: 1292

    l_sql_stmt := l_select||l_insert||l_pr_insert||l_from||l_where||l_pr_groupby;

Line: 1298

    l_sql_stmt := l_select||l_insert||l_pr_insert||l_from||l_where||l_pr_groupby;

Line: 1343

     SELECT count(*)
     FROM   msc_bis_periods mbp,
            msc_plans mp
     WHERE  mbp.organization_id = mp.organization_id
     and    mbp.sr_instance_id = mp.sr_instance_id
     and ((mbp.start_date between nvl(mp.data_start_date, sysdate)
                            and mp.cutoff_date
         or mbp.end_date between nvl(mp.data_start_date,sysdate)
                            and mp.cutoff_date) or
  (mp.data_start_date between mbp.start_date and mbp.end_date))
     and mp.plan_id = p_plan_id
     and mbp.adjustment_period_flag ='N'
     order by mbp.start_date;

Line: 1358

  select round(sum(nvl(((ms.new_order_quantity * msi.unit_weight)  * mism.cost_per_weight_unit),0)),6)
  from msc_supplies ms,
    msc_system_items msi,
    msc_interorg_ship_methods mism,
    msc_plans mp
  WHERE ms.plan_id = p_plan_id
    and ms.organization_id <> ms.source_organization_id
    and ms.order_type in (l_otype1, l_otype2, l_otype3)
    and ms.plan_id = msi.plan_id
    and ms.organization_id = msi.organization_id
    and ms.sr_instance_id = msi.sr_instance_id
    and ms.inventory_item_id = msi.inventory_item_id
    and ms.plan_id = mism.plan_id
    and ms.organization_id = mism.to_organization_id
    and ms.sr_instance_id = mism.sr_instance_id
    and ms.source_organization_id = mism.from_organization_id
    and ms.source_sr_instance_id = mism.sr_instance_id2
    and ms.ship_method = mism.ship_method
    and ms.plan_id = mp.plan_id
    and trunc(ms.new_dock_date) between mp.curr_start_date and  mp.curr_cutoff_date ;

Line: 1415

  l_cursor := ' select planned_production_cost '||
	' ,planned_carrying_cost, planned_purchasing_cost '||
	' ,planned_total_cost, planned_revenue '||
	' ,planned_gross_profit, planned_gross_profit_pct '||
	' ,inventory_value, plan_id '||
	' ,ext_repair_cost, int_repair_cost '||
	' from  msc_srvlvl_profit_v '||
   ' where plan_id = :1 ';

Line: 1426

  l_cursor2 := '  select plan_id from msc_plans where plan_id in (' ||arg_plan_list ||') ';

Line: 1471

    INSERT INTO MSC_FORM_QUERY
      (
	query_id,
	last_update_date,
        last_updated_by,
        creation_date,
        created_by,
	number1,
	char1,
	number2,
	number3,
	number4,
	number5,
	number6,
	number7,
	number8,
	number9,
	number10,
	number11,
	number12,
  number13,
	number14
      )
      VALUES
      (
	 l_seq_id,
	 sysdate,
	 -1,
	 sysdate,
	 -1,
	 l_plan_id,
	 l_plan_name,
 	 round(nvl(l_attained,0), l_round),
   round(nvl(l_target,0), l_round),
	 round(nvl(l_plnd_prod_cost,0), l_round),
	 round(nvl(l_plnd_carr_cost,0), l_round),
	 round(nvl(l_plnd_purc_cost,0), l_round),
	 round(nvl(l_tp_cost,0), l_round),
	 round(nvl(l_plnd_tot_cost + l_tp_cost,0), l_round),
	 round(nvl(l_plnd_rev,0), l_round),
	 round(nvl(l_plnd_gross_profit,0), l_round),
	 round(nvl(l_calc_gross_profit_pct,0), l_round),
	 round(nvl(l_inv_value,0), l_round),
   round(nvl(l_ext_repair_cost,0), l_round),
   round(nvl(l_int_repair_cost,0), l_round)
      );

Line: 1552

  select sr_instance_id, organization_id
  from msc_plan_organizations
  where plan_id = l_plan_id;

Line: 1568

    select nvl(DAILY_RESOURCE_CONSTRAINTS,0)+
      nvl(WEEKLY_RESOURCE_CONSTRAINTS,0)+
      nvl(PERIOD_RESOURCE_CONSTRAINTS,0),
      plan_type
    into v_constraint, v_plan_type
    from msc_plans
    where plan_id = p_plan_id;

Line: 1581

    sql_stat := 'SELECT sum(nvl(md.old_demand_quantity,0)*nvl(md.probability,1)), '||
      ' sum(md.USING_REQUIREMENT_QUANTITY*nvl(md.probability,1)) ' ||
      ' FROM msc_demands md ' ||
      ' WHERE md.plan_id = :1 ' ||
      ' AND sr_instance_id = :2 ' ||
      ' AND organization_id = :3 ' ||
      ' AND md.origination_type in (6,7,8,9,11,15,22,29,30) ';

Line: 1591

    sql_stat := 'SELECT avg(md.service_level), count(*) '||
      ' FROM msc_demands md ' ||
      ' WHERE md.plan_id = :1 ' ||
      ' AND md.sr_instance_id = :2 ' ||
      ' AND md.organization_id = :3 ' ||
      ' AND md.origination_type in (6,7,8,9,11,15,22,29,30) ';

Line: 1608

    sql_stat := ' select round(sum(nvl(((ms.new_order_quantity * '||
      ' msi.unit_weight)  * mism.cost_per_weight_unit),0)),6), 0'||
      ' from msc_supplies ms,  '||
      ' msc_system_items msi,  '||
      ' msc_interorg_ship_methods mism '||
      ' WHERE ms.plan_id = :1 ' ||
      ' and ms.organization_id != ms.source_organization_id '||
      ' and ms.order_type in (' || l_order_type || ') '||
      --' and ms.order_type in (5,11) '||
      ' and ms.plan_id = msi.plan_id '||
      ' and ms.organization_id = msi.organization_id '||
      ' and ms.sr_instance_id = msi.sr_instance_id '||
      ' and ms.inventory_item_id = msi.inventory_item_id '||
      ' and ms.plan_id = mism.plan_id '||
      ' and ms.organization_id = mism.to_organization_id '||
      ' and ms.sr_instance_id = mism.sr_instance_id '||
      ' and ms.source_organization_id = mism.from_organization_id '||
      ' and ms.source_sr_instance_id = mism.sr_instance_id2'||
      ' and ms.ship_method = mism.ship_method ' ||
      ' AND ms.organization_id = :2 '||
      ' AND ms.sr_instance_id = :3 ' ||
      ' AND ms.inventory_item_id = :4 ' ||
      ' AND trunc(ms.new_dock_date) BETWEEN :5 AND :6 ';

Line: 1694

  select nvl(demand_fulfillment_lt,0)
  from msc_plans
  where plan_id = p_plan_id;

Line: 1722

  select nvl(demand_fulfillment_lead_time,0)
  from msc_service_levels
  where plan_id = p_plan_id
    --and category_set_id = l_cate_set_id
    and definition_level = l_dflt_level
    and ( ( sr_instance_id is null and organization_id is null
            and p_inst_id is null and p_org_id is null)
	   or (sr_instance_id = p_inst_id and organization_id = p_org_id))
    and ( ( inventory_item_id is null and p_item_id is null)
           or (inventory_item_id = p_item_id) )
    and ( ( demand_class is null and p_demand_class is null)
           or (demand_class = p_demand_class) )
    and ( ( customer_id is null and p_customer_id is null)
           or (customer_id = p_customer_id) )
    and ( ( customer_site_id is null and p_customer_site_id is null)
           or (customer_site_id = p_customer_site_id) )
    and ( ( sr_category_id is null and p_cate_id is null)
           or (sr_category_id = p_cate_id) );

Line: 1742

  select nvl(demand_fulfillment_lead_time,0)
  from msc_service_levels
  where plan_id = p_plan_id
    --and category_set_id = l_cate_set_id
    and definition_level = nvl(p_definition_level, definition_level)
    --and nvl(sr_instance_id, -1) = nvl(p_inst_id, -1) --not req as they populate for every row
    and nvl(organization_id,-1) = nvl(p_org_id,-1)
    and nvl(inventory_item_id,-1) = nvl(p_item_id,-1)
    and nvl(demand_class,'-1') = nvl(p_demand_class,'-1')
    and nvl(customer_id,-1) = nvl(p_customer_id,-1)
    and nvl(customer_site_id,-1) = nvl(p_customer_site_id,-1)
    and nvl(sr_category_id,-1) = nvl(p_cate_id,-1)
  order by definition_level desc;