[Home] [Help]
1800: nvl(min_cutoff_bucket,0)+data_start_date hrbkt_start,
1801: decode(nvl(hour_cutoff_bucket,0),0, to_date(null),data_start_date) minbkt_start,
1802: sr_instance_id,
1803: organization_id
1804: from msc_plans
1805: where plan_id = p_plan_id;
1806:
1807: cursor daylevel_date_cur is
1808: select min(mpb.bkt_start_date), max(mpb.bkt_end_date)
1806:
1807: cursor daylevel_date_cur is
1808: select min(mpb.bkt_start_date), max(mpb.bkt_end_date)
1809: from msc_plan_buckets mpb,
1810: msc_plans mp
1811: where mp.plan_id =p_plan_id
1812: and mpb.plan_id = mp.plan_id
1813: and mpb.organization_id = mp.organization_id
1814: and mpb.sr_instance_id = mp.sr_instance_id
1825:
1826: cursor bkt_cur is
1827: select max(mpb.bkt_end_date)
1828: from msc_plan_buckets mpb,
1829: msc_plans mp
1830: where mp.plan_id =p_plan_id
1831: and mpb.plan_id = mp.plan_id
1832: and mpb.organization_id = mp.organization_id
1833: and mpb.sr_instance_id = mp.sr_instance_id
1836: cursor c_plan_cal is
1837: select mtp.calendar_code, mtp.sr_instance_id,
1838: mtp.calendar_exception_set_id, plan_type
1839: from msc_trading_partners mtp,
1840: msc_plans mp
1841: where mp.plan_id = p_plan_id
1842: and mp.sr_instance_id = mtp.sr_instance_id
1843: and mp.organization_id = mtp.sr_tp_id
1844: and mtp.partner_type = 3;
1878: select greatest(mpsd.period_start_date, mp.data_start_date)
1879: bulk collect into v_date
1880: from msc_trading_partners tp,
1881: msc_period_start_dates mpsd,
1882: msc_plans mp
1883: where mpsd.calendar_code = tp.calendar_code
1884: and mpsd.sr_instance_id = tp.sr_instance_id
1885: and mpsd.exception_set_id = tp.calendar_exception_set_id
1886: and tp.sr_instance_id = mp.sr_instance_id
1901: select nvl(min_cutoff_bucket, 0),
1902: nvl(hour_cutoff_bucket, 0),
1903: nvl(daily_cutoff_bucket, 0)
1904: into v_min_day, v_hour_day, v_date_day
1905: from msc_plans
1906: where plan_id = p_plan_id;
1907:
1908: if v_min_day <> 0 then
1909: v_buckets := to_char(p_first_date + v_min_day, format_mask);
3169: select sys_yes
3170: from msc_plan_organizations mpo,
3171: msc_resource_requirements mrr,
3172: msc_department_resources mdr,
3173: msc_plans mp
3174: where mrr.plan_id = p_plan
3175: and mrr.sr_instance_id = p_inst
3176: and mrr.organization_id = p_org
3177: and mrr.department_id = p_dept
3278: msc_items mi,
3279: msc_items mi2,
3280: msc_gantt_query mgq,
3281: msc_plan_organizations mpo,
3282: msc_plans mp
3283: where mgq.query_id = p_query_id
3284: and mgq.row_flag = SYS_YES
3285: and ( ( p_batched_res_act = RES_REQ_ROW_TYPE )
3286: or ( p_batched_res_act = RES_ACT_BATCHED_ROW_TYPE and ( nvl(mdr.batchable_flag,2) = 2 or mrr.batch_number is null) ) )
3476: msc_supplies ms,
3477: msc_gantt_query mgq,
3478: msc_plan_organizations mpo,
3479: msc_department_resources mdr,
3480: msc_plans mp
3481: where mgq.query_id = p_query_id
3482: and mgq.row_flag = SYS_YES
3483: and mgq.is_fetched = SYS_NO
3484: and mgq.res_instance_id = MBP_NULL_VALUE
3939: -- 1. available capacity for items with delivery calendar
3940: select mgq.row_index, mca.calendar_date, msc.capacity
3941: bulk collect into v_row_index, v_start_date, v_qty
3942: from msc_calendar_dates mca,
3943: msc_plans mp,
3944: msc_supplier_capacities msc,
3945: msc_item_suppliers mis,
3946: msc_gantt_query mgq
3947: where mgq.query_id = p_query_id
3982: -- 2. available capacity for items with NO delivery calendar
3983: select mgq.row_index, mfq.date1, msc.capacity
3984: bulk collect into v_row_index, v_start_date, v_qty
3985: from msc_form_query mfq,
3986: msc_plans mp,
3987: msc_supplier_capacities msc,
3988: msc_item_suppliers mis,
3989: msc_gantt_query mgq
3990: where mgq.query_id = p_query_id