APPS.MSC_PHUB_PKG SQL Statements

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

Line: 21

    select
      compile_designator,
      sr_instance_id,
      organization_id,
      decode(plan_id, -1, sysdate, trunc(curr_start_date)) curr_start_date,
      decode(plan_id, -1, sysdate+365, trunc(curr_cutoff_date)) curr_cutoff_date,
      curr_plan_type,
      nvl(plan_completion_date, sysdate),
      nvl(display_kpi,1)
    from msc_plans
    where plan_id = g_plan_id;

Line: 35

   select plan_run_id
   from msc_plan_runs
   where plan_id = g_plan_id
     and nvl(archive_flag,sys_no) = sys_yes
   order by plan_run_id desc;

Line: 50

        select ltrim(rtrim(value))
          into g_log_file_dir
        from (select value from v$parameter2 where name='utl_file_dir'
             order by rownum desc)
        where rownum <2;

Line: 140

      insert into msc_calendar_dtl (year_start_date, year_end_date, year_name,
        month_start_date, month_end_date, month_name, month_seq, detail_date,
        created_by, creation_date, last_update_date, last_updated_by, last_update_login)
      values (l_year_start_date, l_year_end_date, l_year_name,
        l_month_start_date, l_month_end_date, l_month_name, l_month_seq, l_detail_date,
        fnd_global.user_id, sysdate, sysdate, fnd_global.user_id, fnd_global.user_id);

Line: 154

    select min(detail_date) min_date,
      max(detail_date) max_date
    from msc_calendar_dtl;

Line: 161

    select min(calendar_date) mfg_min_date, max(calendar_date) mfg_max_date
    from msc_calendars mc,
      msc_calendar_dates mcd
    where mc.calendar_code = fnd_profile.value('MSC_HUB_CAL_CODE')
      and mc.calendar_code = mcd.calendar_code
      and mc.sr_instance_id = mcd.sr_instance_id;

Line: 171

    select min(mbp.start_date) bis_min_date, max(mbp.end_date) bis_max_date
    from msc_bis_periods mbp,
      msc_trading_partners mtp
    where mbp.sr_instance_id = mtp.sr_instance_id
      and mbp.organization_id = mtp.sr_tp_id
      and mtp.partner_type = 3
      and mtp.organization_code||':'||period_set_name = fnd_profile.value('MSC_HUB_PERIOD_SET_NAME');

Line: 561

    select msc_plan_runs_s.nextval
    from dual;

Line: 565

    select count(*)
    from msc_plan_runs
    where plan_id = p_plan_id;

Line: 588

        insert into msc_plan_runs
          (plan_id, plan_run_id, plan_run_name, plan_completion_date, last_run_flag,
           created_by, creation_date, last_update_date, last_updated_by, last_update_login,
           start_date, end_date, archive_flag, planning_hub_flag)
        values (p_plan_id, l_plan_run_id,
           p_plan_name||' '||to_char(p_plan_comp_date,'mm/dd')||'('||l_plan_version||')'||l_scenario_suffix,
           p_plan_comp_date, 2,
           fnd_global.user_id, sysdate, sysdate, fnd_global.user_id, fnd_global.user_id,
           sysdate, null, sys_no, sys_no);

Line: 599

        insert into msc_plan_runs
          (plan_id, plan_run_id, plan_run_name, plan_completion_date, last_run_flag,
           created_by, creation_date, last_update_date, last_updated_by, last_update_login,
           start_date, end_date, archive_flag, planning_hub_flag)
            select
              das.scenario_id plan_id,
              l_plan_run_id plan_run_id,
              das.scenario_name||' '||to_char(sysdate,'mm/dd')||'('||l_plan_version||')'||l_scenario_suffix plan_run_name,
              sysdate plan_completion_date,
              sys_yes last_run_flag,
              fnd_global.user_id created_by,
              sysdate creation_date,
              sysdate last_update_date,
              fnd_global.user_id last_updated_by,
              fnd_global.user_id last_update_login,
              tq.from_date start_date,
              tq.until_date end_date,
              sys_yes archive_flag,
              sys_no planning_hub_flag
            from msd_dp_ascp_scenarios_v das,
              msd_dem_transfer_query tq
            where das.demand_plan_id=5555555
              and das.demand_plan_name = substr(tq.query_name, 1, 30)
              and das.scenario_id = p_plan_id;

Line: 627

    select plan_run_name into l_plan_run_name from msc_plan_runs
    where plan_id=p_plan_id and plan_run_id=l_plan_run_id;

Line: 748

    update msc_plan_runs
      set last_run_flag = decode(p_return_status, sys_yes, sys_no, sys_yes)
    where plan_id = g_plan_id
      and plan_run_id < l_plan_run_id;

Line: 753

    update msc_plan_runs
      set last_run_flag = decode(p_return_status, sys_yes, sys_yes, sys_no),
        end_date = sysdate,
        planning_hub_flag = decode(p_return_status, sys_yes, sys_yes, sys_no),
        archive_flag = l_display_kpi
    where plan_id = g_plan_id
      and plan_run_id = l_plan_run_id;

Line: 777

   select plan_run_id
   from msc_plan_runs
   where plan_id = g_plan_id
     and plan_run_id = nvl(p_plan_run_id, plan_run_id)
     and nvl(archive_flag,sys_no) = sys_yes;

Line: 794

      update msc_plan_runs
      set
        planning_hub_flag =  sys_no,
    last_run_flag = sys_no,
        archive_flag = sys_no
      where plan_run_id = r_plan_runs.plan_run_id;

Line: 909

        select msc_plan_runs_s.nextval
        from dual;

Line: 913

        select count(*)
        from msc_plan_runs
        where plan_id = p_scenario_id;

Line: 976

        update msc_plan_runs
            set last_run_flag = sys_no
        where plan_id = p_scenario_id
            and plan_run_id < l_plan_run_id;

Line: 981

        update msc_plan_runs
            set last_run_flag = sys_yes,
                end_date = sysdate,
                planning_hub_flag = sys_yes
        where plan_id = p_scenario_id
            and plan_run_id = l_plan_run_id;