APPS.MSC_HUB_CALENDAR SQL Statements

Line: 85

    select plan_type into l_plan_type
    from msc_plans where plan_id=p_plan_id;

Line: 100

      select mtp.calendar_code into l_calendar_code
      from msc_trading_partners mtp,       msc_plans mp
      where mtp.sr_tp_id = mp.organization_id
      and mtp.sr_instance_id = mp.sr_instance_id
      and mp.plan_id =p_plan_id and mtp.PARTNER_TYPE =3;

Line: 117

    select mcd.calendar_date into x_date
    from msc_calendar_dates mcd,msc_calendars mc
    where mc.calendar_code = l_calendar_code
    and mc.sr_instance_id = p_sr_instance_id
    and mc.calendar_code=mcd.calendar_code
    and mc.sr_instance_id=mcd.sr_instance_id

    and mcd.calendar_date = p_bkt_start_date
    and mcd.seq_num is not null;

Line: 131

     select max(mcd.calendar_date) into x_date
     from msc_calendar_dates mcd, msc_calendars mc,msc_cal_week_start_dates mcwsd
     where mc.calendar_code = l_calendar_code
     and mc.sr_instance_id = p_sr_instance_id
     and   mc.calendar_code =  mcd.calendar_code
     and   mc.sr_instance_id = mcd.sr_instance_id

     and   mcd.seq_num is not null
     and   mcd.calendar_code = mcwsd.calendar_code
     and   mcd.sr_instance_id = mcwsd.sr_instance_id
     and   mcd.exception_set_id =mcwsd.exception_set_id
     and   mcwsd.WEEK_START_DATE >= p_bkt_start_date
     and   mcwsd.WEEK_START_DATE <= p_bkt_end_date
     and   mcd.calendar_date >=mcwsd.WEEK_START_DATE
     and   mcd.calendar_date

     select max(mcd.calendar_date) into x_date
     from msc_calendar_dates mcd, msc_calendars mc,msc_period_start_dates mpsd
     where mc.calendar_code = l_calendar_code
     and   mc.sr_instance_id = p_sr_instance_id
     and   mc.calendar_code =  mcd.calendar_code
     and   mc.sr_instance_id = mcd.sr_instance_id

     and   mcd.seq_num is not null
     and   mcd.calendar_code = mpsd.calendar_code
     and   mcd.sr_instance_id = mpsd.sr_instance_id
     and   mcd.exception_set_id =mpsd.exception_set_id
     and   mpsd.PERIOD_START_DATE >= p_bkt_start_date
     and   mpsd.PERIOD_START_DATE <= p_bkt_end_date
     and   mcd.calendar_date >=mpsd.PERIOD_START_DATE
     and   mcd.calendar_date

    select mcd.calendar_date into x_date
    from msc_calendar_dates mcd,msc_calendars mc
    where mc.calendar_code = p_calendar_code
    and mc.sr_instance_id = p_sr_instance_id
    and mc.calendar_code=mcd.calendar_code
    and mc.sr_instance_id=mcd.sr_instance_id

    and mcd.calendar_date = p_bkt_start_date
    and mcd.seq_num is not null;

     select max(mcd.calendar_date) into x_date
     from msc_calendar_dates mcd, msc_calendars mc,msc_cal_week_start_dates mcwsd
     where mc.calendar_code = p_calendar_code
     and mc.sr_instance_id = p_sr_instance_id
     and   mc.calendar_code =  mcd.calendar_code
     and   mc.sr_instance_id = mcd.sr_instance_id

     and   mcd.seq_num is not null
     and   mcd.calendar_code = mcwsd.calendar_code
     and   mcd.sr_instance_id = mcwsd.sr_instance_id
     and   mcd.exception_set_id =mcwsd.exception_set_id
     and   mcwsd.WEEK_START_DATE = p_bkt_start_date
     and   mcd.calendar_date >=mcwsd.WEEK_START_DATE
     and   mcd.calendar_date

     select max(mcd.calendar_date) into x_date
     from msc_calendar_dates mcd, msc_calendars mc,msc_period_start_dates mpsd
     where mc.calendar_code = p_calendar_code
     and   mc.sr_instance_id = p_sr_instance_id
     and   mc.calendar_code =  mcd.calendar_code
     and   mc.sr_instance_id = mcd.sr_instance_id

     and   mcd.seq_num is not null
     and   mcd.calendar_code = mpsd.calendar_code
     and   mcd.sr_instance_id = mpsd.sr_instance_id
     and   mcd.exception_set_id =mpsd.exception_set_id
     and   mpsd.PERIOD_START_DATE = p_bkt_start_date
     and   mcd.calendar_date >=mpsd.PERIOD_START_DATE
     and   mcd.calendar_date

select bkt_start_date,bkt_end_date,bucket_type
into l_bkt_start_date,l_bkt_end_date,l_bkt_type
from msc_plan_buckets
where plan_id = p_plan_id
and p_date between bkt_start_date and bkt_end_date;

select sr_instance_id into l_sr_instance_id
from msc_plans where plan_id=p_plan_id;

   select curr_start_date into x_date
   from msc_plans where plan_id = p_plan_id;

     select max(period_start_date) into x_date
     from msc_safety_Stocks
     where period_start_date <=p_bkt_end_date
     and plan_id=p_plan_id;

      select mtp.calendar_code into l_calendar_code
      from msc_trading_partners mtp,       msc_plans mp
      where mtp.sr_tp_id = mp.organization_id
      and mtp.sr_instance_id = mp.sr_instance_id
      and mp.plan_id =p_plan_id and mtp.PARTNER_TYPE =3;

    select max(mcd.calendar_date)  into x_date
    from msc_calendar_dates mcd,msc_calendars mc
    where mc.calendar_code = l_calendar_code
    and mc.sr_instance_id = p_sr_instance_id
    and mc.calendar_code=mcd.calendar_code
    and mc.sr_instance_id=mcd.sr_instance_id

    and mcd.calendar_date <= p_bkt_start_date
    and mcd.calendar_date >= p_bkt_start_date -7
    and mcd.seq_num is not null;

   select min(organization_id) into x_org_id
   from msc_system_items
   where plan_id=p_plan_id
   and inventory_item_id = p_item_id
   and sr_instance_id = p_sr_inst_id;