[Home] [Help]
1799: nvl(min_cutoff_bucket,0)+data_start_date hrbkt_start,
1800: decode(nvl(hour_cutoff_bucket,0),0, to_date(null),data_start_date) minbkt_start,
1801: sr_instance_id,
1802: organization_id
1803: from msc_plans
1804: where plan_id = p_plan_id;
1805:
1806: cursor daylevel_date_cur is
1807: select min(mpb.bkt_start_date), max(mpb.bkt_end_date)
1805:
1806: cursor daylevel_date_cur is
1807: select min(mpb.bkt_start_date), max(mpb.bkt_end_date)
1808: from msc_plan_buckets mpb,
1809: msc_plans mp
1810: where mp.plan_id =p_plan_id
1811: and mpb.plan_id = mp.plan_id
1812: and mpb.organization_id = mp.organization_id
1813: and mpb.sr_instance_id = mp.sr_instance_id
1824:
1825: cursor bkt_cur is
1826: select max(mpb.bkt_end_date)
1827: from msc_plan_buckets mpb,
1828: msc_plans mp
1829: where mp.plan_id =p_plan_id
1830: and mpb.plan_id = mp.plan_id
1831: and mpb.organization_id = mp.organization_id
1832: and mpb.sr_instance_id = mp.sr_instance_id
1835: cursor c_plan_cal is
1836: select mtp.calendar_code, mtp.sr_instance_id,
1837: mtp.calendar_exception_set_id, plan_type
1838: from msc_trading_partners mtp,
1839: msc_plans mp
1840: where mp.plan_id = p_plan_id
1841: and mp.sr_instance_id = mtp.sr_instance_id
1842: and mp.organization_id = mtp.sr_tp_id
1843: and mtp.partner_type = 3;
1877: select greatest(mpsd.period_start_date, mp.data_start_date)
1878: bulk collect into v_date
1879: from msc_trading_partners tp,
1880: msc_period_start_dates mpsd,
1881: msc_plans mp
1882: where mpsd.calendar_code = tp.calendar_code
1883: and mpsd.sr_instance_id = tp.sr_instance_id
1884: and mpsd.exception_set_id = tp.calendar_exception_set_id
1885: and tp.sr_instance_id = mp.sr_instance_id
1900: select nvl(min_cutoff_bucket, 0),
1901: nvl(hour_cutoff_bucket, 0),
1902: nvl(daily_cutoff_bucket, 0)
1903: into v_min_day, v_hour_day, v_date_day
1904: from msc_plans
1905: where plan_id = p_plan_id;
1906:
1907: if v_min_day <> 0 then
1908: v_buckets := to_char(p_first_date + v_min_day, format_mask);
3164: select sys_yes
3165: from msc_plan_organizations mpo,
3166: msc_resource_requirements mrr,
3167: msc_department_resources mdr,
3168: msc_plans mp
3169: where mrr.plan_id = p_plan
3170: and mrr.sr_instance_id = p_inst
3171: and mrr.organization_id = p_org
3172: and mrr.department_id = p_dept
3273: msc_items mi,
3274: msc_items mi2,
3275: msc_gantt_query mgq,
3276: msc_plan_organizations mpo,
3277: msc_plans mp
3278: where mgq.query_id = p_query_id
3279: and mgq.row_flag = SYS_YES
3280: and ( ( p_batched_res_act = RES_REQ_ROW_TYPE )
3281: or ( p_batched_res_act = RES_ACT_BATCHED_ROW_TYPE and ( nvl(mdr.batchable_flag,2) = 2 or mrr.batch_number is null) ) )
3471: msc_supplies ms,
3472: msc_gantt_query mgq,
3473: msc_plan_organizations mpo,
3474: msc_department_resources mdr,
3475: msc_plans mp
3476: where mgq.query_id = p_query_id
3477: and mgq.row_flag = SYS_YES
3478: and mgq.is_fetched = SYS_NO
3479: and mgq.res_instance_id = MBP_NULL_VALUE
3934: -- 1. available capacity for items with delivery calendar
3935: select mgq.row_index, mca.calendar_date, msc.capacity
3936: bulk collect into v_row_index, v_start_date, v_qty
3937: from msc_calendar_dates mca,
3938: msc_plans mp,
3939: msc_supplier_capacities msc,
3940: msc_item_suppliers mis,
3941: msc_gantt_query mgq
3942: where mgq.query_id = p_query_id
3977: -- 2. available capacity for items with NO delivery calendar
3978: select mgq.row_index, mfq.date1, msc.capacity
3979: bulk collect into v_row_index, v_start_date, v_qty
3980: from msc_form_query mfq,
3981: msc_plans mp,
3982: msc_supplier_capacities msc,
3983: msc_item_suppliers mis,
3984: msc_gantt_query mgq
3985: where mgq.query_id = p_query_id