[Home] [Help]
1: PACKAGE BODY MSC_GANTT_UTILS AS
2: /* $Header: MSCGNTUB.pls 120.61.12010000.3 2008/07/31 12:50:55 skakani ship $ */
3:
4: g_plan_cal varchar2(50);
5: g_plan_cal_inst_id number;
167:
168: cursor c_res_node_label (p_plan_id number, p_inst_id number, p_trx_id number) is
169: select msi.item_name,
170: nvl(decode(mrr.setup_id, to_number(null), null,
171: msc_gantt_utils.getSetupCode(mrr.plan_id,
172: mrr.sr_instance_id, mrr.resource_id, mrr.setup_id)), null) setup_type,
173: mtp.organization_code org_code,
174: ms.new_order_quantity qty,
175: mrr.batch_number,
471: end if;
472:
473: --insert if this is a resource first
474: if ( nvl(v_res_instance_id,mbp_null_value) = mbp_null_value ) then -- {
475: l_row_found := msc_gantt_utils.isResRowInGantt(p_query_id, p_plan_id,
476: v_inst_id, v_org_id, v_dept_id, v_res_id,
477: nvl(v_res_instance_id,mbp_null_value), nvl(v_serial_number,mbp_null_value_char));
478: if (l_row_found = sys_no) then -- {
479: p_row_index := p_row_index + 1;
492: if ( nvl(v_res_instance_id,mbp_null_value) = mbp_null_value ) then --{
493: -- find res instances
494: for c_res_inst in res_inst_cur(p_plan_id, v_inst_id, v_org_id, v_dept_id, v_res_id)
495: loop -- {
496: l_row_found := msc_gantt_utils.isResRowInGantt(p_query_id, p_plan_id,
497: v_inst_id, v_org_id, c_res_inst.department_id, v_res_id,
498: nvl(c_res_inst.res_instance_id,mbp_null_value),
499: nvl(c_res_inst.serial_number,mbp_null_value_char));
500:
515: -- find res
516: for c_inst_res in inst_res_cur(p_plan_id, v_inst_id, v_org_id, v_dept_id, v_res_id,
517: v_res_instance_id, v_serial_number)
518: loop -- {
519: l_row_found := msc_gantt_utils.isResRowInGantt(p_query_id, p_plan_id,
520: v_inst_id, v_org_id, c_inst_res.department_id, v_res_id,
521: mbp_null_value, mbp_null_value_char);
522:
523: if (l_row_found = sys_no) then -- {
536: end if; -- }
537:
538: --insert if this is a res inst node last
539: if ( nvl(v_res_instance_id,mbp_null_value) <> mbp_null_value ) then --{
540: l_row_found := msc_gantt_utils.isResRowInGantt(p_query_id, p_plan_id,
541: v_inst_id, v_org_id, v_dept_id, v_res_id,
542: nvl(v_res_instance_id,mbp_null_value),
543: nvl(v_serial_number,mbp_null_value_char));
544:
838:
839: for i in 1..p_sr_instance_id.count
840: loop
841:
842: msc_gantt_utils.getBatchValues(g_plan_id, p_sr_instance_id(i), p_batch_number(i),
843: l_res_desc, l_min_capacity, l_max_capacity, l_capacity_used);
844:
845: insert into msc_gantt_dtl_query (
846: query_id, row_type, row_index, parent_id,
1972: p_segment_start_date date_arr, p_segment_end_date date_arr, p_segment_quantity number_arr,
1973: p_consumer_start_date date_arr, p_consumer_end_date date_arr, p_allocation_type number_arr,
1974: p_dependency_type number_arr, p_minimum_time_offset number_arr,
1975: p_maximum_time_offset number_arr, p_actual_time_offset number_arr,
1976: p_data_stream IN OUT NOCOPY msc_gantt_utils.maxCharTbl) is
1977:
1978: oneBigRecord maxCharTbl := maxCharTbl(0);
1979: l_data_stream maxCharTbl := maxCharTbl(0);
1980: reccount number := 0;
2060: procedure sendResReqAvailSuppStream(p_row_type number, p_row_index number,
2061: p_from_index number, p_start date_arr, p_end date_arr,
2062: p_resource_hours number_arr, p_resource_units number_arr,
2063: p_schedule_flag number_arr,
2064: p_data_stream IN OUT NOCOPY msc_gantt_utils.maxCharTbl,
2065: p_avail_qty number_arr default null, p_overload_qty number_arr default null,
2066: p_consume_qty number_arr default null, p_cum_avail_qty number_arr default null) is
2067:
2068: oneBigRecord maxCharTbl := maxCharTbl(0);
2170: p_from_setup_code char_arr, p_to_setup_code char_arr, p_std_op_code char_arr,
2171: p_changeover_time char_arr, p_changeover_penalty char_arr,
2172: p_min_capacity number_arr, p_max_capacity number_arr, p_capacity_used number_arr,
2173: p_overload_flag number_arr,
2174: p_data_stream IN OUT NOCOPY msc_gantt_utils.maxCharTbl) is
2175:
2176: oneBigRecord maxCharTbl := maxCharTbl(0);
2177: l_data_stream maxCharTbl := maxCharTbl(0);
2178: reccount number := 0;
2300: l_res_inst_batch_rows number;
2301:
2302: begin
2303:
2304: --l_res_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_no, sys_no, sys_yes);
2305: --l_res_inst_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_yes, sys_no, sys_yes);
2306: --4997096 bugfix, instead calls the below procedure
2307: msc_gantt_utils.isResRowInGantt(p_query_id, l_res_rows, l_res_inst_rows, l_res_batch_rows, l_res_inst_batch_rows);
2308:
2301:
2302: begin
2303:
2304: --l_res_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_no, sys_no, sys_yes);
2305: --l_res_inst_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_yes, sys_no, sys_yes);
2306: --4997096 bugfix, instead calls the below procedure
2307: msc_gantt_utils.isResRowInGantt(p_query_id, l_res_rows, l_res_inst_rows, l_res_batch_rows, l_res_inst_batch_rows);
2308:
2309: put_line('populateResAvailGantt: res - res inst - res batch - res inst batch : '
2303:
2304: --l_res_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_no, sys_no, sys_yes);
2305: --l_res_inst_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_yes, sys_no, sys_yes);
2306: --4997096 bugfix, instead calls the below procedure
2307: msc_gantt_utils.isResRowInGantt(p_query_id, l_res_rows, l_res_inst_rows, l_res_batch_rows, l_res_inst_batch_rows);
2308:
2309: put_line('populateResAvailGantt: res - res inst - res batch - res inst batch : '
2310: ||l_res_rows ||' - '|| l_res_inst_rows ||' - '||l_res_batch_rows ||' - '||l_res_inst_batch_rows);
2311:
2436: -- parent_id = 2 aggregated requirements
2437:
2438: cursor res_req_cur is
2439: select mgq.row_index,
2440: msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
2441: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) start_date,
2442: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
2443: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) end_date,
2444: mrr.assigned_units,
2438: cursor res_req_cur is
2439: select mgq.row_index,
2440: msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
2441: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) start_date,
2442: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
2443: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) end_date,
2444: mrr.assigned_units,
2445: mrr.resource_hours,
2446: mrr.batch_number,
2443: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) end_date,
2444: mrr.assigned_units,
2445: mrr.resource_hours,
2446: mrr.batch_number,
2447: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
2448: msc_gantt_utils.getDisplayType(p_display_type,
2449: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2450: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2451: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2444: mrr.assigned_units,
2445: mrr.resource_hours,
2446: mrr.batch_number,
2447: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
2448: msc_gantt_utils.getDisplayType(p_display_type,
2449: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2450: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2451: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2452: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2445: mrr.resource_hours,
2446: mrr.batch_number,
2447: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
2448: msc_gantt_utils.getDisplayType(p_display_type,
2449: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2450: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2451: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2452: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2453: mrr.orig_resource_seq_num, mrr.parent_seq_num),
2447: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
2448: msc_gantt_utils.getDisplayType(p_display_type,
2449: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2450: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2451: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2452: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2453: mrr.orig_resource_seq_num, mrr.parent_seq_num),
2454: mrr.firm_flag,
2455: msc_gantt_utils.isResConstraint(mrr.plan_id,
2451: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2452: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2453: mrr.orig_resource_seq_num, mrr.parent_seq_num),
2454: mrr.firm_flag,
2455: msc_gantt_utils.isResConstraint(mrr.plan_id,
2456: mrr.sr_instance_id, mrr.organization_id,
2457: ms.inventory_item_id, mrr.department_id, mrr.resource_id, mrr.supply_id),
2458: g_gantt_rh_toler_days_early,
2459: g_gantt_rh_toler_days_late) display_type
2475: and mrr.batch_number is null and mrr.end_date is not null and mrr.resource_hours > 0
2476: and nvl(mrr.parent_id, 2) = 2
2477: and nvl(mrr.status,-1) = 0
2478: and nvl(mrr.applied,-1) = 2
2479: and ( ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
2480: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
2481: between p_start_date and p_end_date
2482: )
2483: or ( msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2479: and ( ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
2480: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
2481: between p_start_date and p_end_date
2482: )
2483: or ( msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2484: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
2485: between p_start_date and p_end_date )
2486: or ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
2487: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) <= p_start_date
2482: )
2483: or ( msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2484: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
2485: between p_start_date and p_end_date )
2486: or ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
2487: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) <= p_start_date
2488: and msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2489: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) >= p_end_date )
2490: )
2484: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
2485: between p_start_date and p_end_date )
2486: or ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
2487: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) <= p_start_date
2488: and msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2489: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) >= p_end_date )
2490: )
2491: union all
2492: select mgq.row_index,
2489: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) >= p_end_date )
2490: )
2491: union all
2492: select mgq.row_index,
2493: msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
2494: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) start_date,
2495: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
2496: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) end_date,
2497: mrr.assigned_units,
2491: union all
2492: select mgq.row_index,
2493: msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
2494: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) start_date,
2495: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
2496: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) end_date,
2497: mrr.assigned_units,
2498: mrr.resource_hours,
2499: mrr.batch_number,
2496: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) end_date,
2497: mrr.assigned_units,
2498: mrr.resource_hours,
2499: mrr.batch_number,
2500: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
2501: msc_gantt_utils.getDisplayType(p_display_type,
2502: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2503: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2504: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2497: mrr.assigned_units,
2498: mrr.resource_hours,
2499: mrr.batch_number,
2500: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
2501: msc_gantt_utils.getDisplayType(p_display_type,
2502: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2503: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2504: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2505: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2498: mrr.resource_hours,
2499: mrr.batch_number,
2500: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
2501: msc_gantt_utils.getDisplayType(p_display_type,
2502: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2503: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2504: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2505: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2506: mrr.orig_resource_seq_num, mrr.parent_seq_num),
2500: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
2501: msc_gantt_utils.getDisplayType(p_display_type,
2502: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2503: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2504: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2505: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2506: mrr.orig_resource_seq_num, mrr.parent_seq_num),
2507: mrr.firm_flag,
2508: msc_gantt_utils.isResConstraint(mrr.plan_id,
2504: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2505: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2506: mrr.orig_resource_seq_num, mrr.parent_seq_num),
2507: mrr.firm_flag,
2508: msc_gantt_utils.isResConstraint(mrr.plan_id,
2509: mrr.sr_instance_id, mrr.organization_id,
2510: ms.inventory_item_id, mrr.department_id, mrr.resource_id, mrr.supply_id),
2511: g_gantt_rh_toler_days_early,
2512: g_gantt_rh_toler_days_late) display_type
2541: and nvl(mrr1.parent_id, 2) = 2
2542: and nvl(mrr1.status,-1) <> 0
2543: and nvl(mrr1.applied,-1) <> 2
2544: and nvl(mrr.parent_id,-1) = 1
2545: and ( ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
2546: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
2547: between p_start_date and p_end_date
2548: )
2549: or ( msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2545: and ( ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
2546: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
2547: between p_start_date and p_end_date
2548: )
2549: or ( msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2550: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
2551: between p_start_date and p_end_date )
2552: or ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
2553: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) <= p_start_date
2548: )
2549: or ( msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2550: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
2551: between p_start_date and p_end_date )
2552: or ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
2553: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) <= p_start_date
2554: and msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2555: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) >= p_end_date )
2556: ) ;
2550: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
2551: between p_start_date and p_end_date )
2552: or ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
2553: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) <= p_start_date
2554: and msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2555: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) >= p_end_date )
2556: ) ;
2557:
2558: cursor res_inst_req_cur is
2556: ) ;
2557:
2558: cursor res_inst_req_cur is
2559: select mgq.row_index,
2560: msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrir.start_date, mrir.end_date,
2561: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) start_date,
2562: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrir.start_date, mrir.end_date,
2563: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) end_date,
2564: 1 assigned_units,
2558: cursor res_inst_req_cur is
2559: select mgq.row_index,
2560: msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrir.start_date, mrir.end_date,
2561: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) start_date,
2562: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrir.start_date, mrir.end_date,
2563: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) end_date,
2564: 1 assigned_units,
2565: mrir.resource_instance_hours,
2566: mrr.batch_number,
2563: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) end_date,
2564: 1 assigned_units,
2565: mrir.resource_instance_hours,
2566: mrr.batch_number,
2567: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
2568: msc_gantt_utils.getDisplayType(p_display_type,
2569: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2570: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2571: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2564: 1 assigned_units,
2565: mrir.resource_instance_hours,
2566: mrr.batch_number,
2567: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
2568: msc_gantt_utils.getDisplayType(p_display_type,
2569: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2570: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2571: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2572: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2565: mrir.resource_instance_hours,
2566: mrr.batch_number,
2567: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
2568: msc_gantt_utils.getDisplayType(p_display_type,
2569: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2570: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2571: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2572: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2573: mrr.orig_resource_seq_num, mrr.parent_seq_num),
2567: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
2568: msc_gantt_utils.getDisplayType(p_display_type,
2569: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2570: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2571: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2572: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2573: mrr.orig_resource_seq_num, mrr.parent_seq_num),
2574: mrr.firm_flag,
2575: msc_gantt_utils.isResConstraint(mrr.plan_id,
2571: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2572: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2573: mrr.orig_resource_seq_num, mrr.parent_seq_num),
2574: mrr.firm_flag,
2575: msc_gantt_utils.isResConstraint(mrr.plan_id,
2576: mrr.sr_instance_id, mrr.organization_id,
2577: ms.inventory_item_id, mrr.department_id, mrr.resource_id, mrr.supply_id),
2578: g_gantt_rh_toler_days_early,
2579: g_gantt_rh_toler_days_late) display_type
2612: and mrr.batch_number is null and mrr.end_date is not null and mrr.resource_hours > 0
2613: and nvl(mrr.parent_id, 2) = 2
2614: and nvl(mrr.status,-1) = 0
2615: and nvl(mrr.applied,-1) = 2
2616: and ( ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
2617: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
2618: between p_start_date and p_end_date
2619: )
2620: or ( msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2616: and ( ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
2617: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
2618: between p_start_date and p_end_date
2619: )
2620: or ( msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2621: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
2622: between p_start_date and p_end_date )
2623: or ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
2624: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) <= p_start_date
2619: )
2620: or ( msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2621: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
2622: between p_start_date and p_end_date )
2623: or ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
2624: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) <= p_start_date
2625: and msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2626: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) >= p_end_date )
2627: )
2621: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
2622: between p_start_date and p_end_date )
2623: or ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
2624: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) <= p_start_date
2625: and msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2626: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) >= p_end_date )
2627: )
2628: union all
2629: select mgq.row_index,
2626: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) >= p_end_date )
2627: )
2628: union all
2629: select mgq.row_index,
2630: msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrir.start_date, mrir.end_date,
2631: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) start_date,
2632: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrir.start_date, mrir.end_date,
2633: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) end_date,
2634: 1 assigned_units,
2628: union all
2629: select mgq.row_index,
2630: msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrir.start_date, mrir.end_date,
2631: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) start_date,
2632: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrir.start_date, mrir.end_date,
2633: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) end_date,
2634: 1 assigned_units,
2635: mrir.resource_instance_hours,
2636: mrr.batch_number,
2633: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) end_date,
2634: 1 assigned_units,
2635: mrir.resource_instance_hours,
2636: mrr.batch_number,
2637: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
2638: msc_gantt_utils.getDisplayType(p_display_type,
2639: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2640: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2641: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2634: 1 assigned_units,
2635: mrir.resource_instance_hours,
2636: mrr.batch_number,
2637: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
2638: msc_gantt_utils.getDisplayType(p_display_type,
2639: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2640: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2641: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2642: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2635: mrir.resource_instance_hours,
2636: mrr.batch_number,
2637: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
2638: msc_gantt_utils.getDisplayType(p_display_type,
2639: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2640: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2641: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2642: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2643: mrr.orig_resource_seq_num, mrr.parent_seq_num),
2637: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
2638: msc_gantt_utils.getDisplayType(p_display_type,
2639: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2640: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2641: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2642: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2643: mrr.orig_resource_seq_num, mrr.parent_seq_num),
2644: mrr.firm_flag,
2645: msc_gantt_utils.isResConstraint(mrr.plan_id,
2641: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2642: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2643: mrr.orig_resource_seq_num, mrr.parent_seq_num),
2644: mrr.firm_flag,
2645: msc_gantt_utils.isResConstraint(mrr.plan_id,
2646: mrr.sr_instance_id, mrr.organization_id,
2647: ms.inventory_item_id, mrr.department_id, mrr.resource_id, mrr.supply_id),
2648: g_gantt_rh_toler_days_early,
2649: g_gantt_rh_toler_days_late) display_type
2695: and nvl(mrr1.parent_id, 2) = 2
2696: and nvl(mrr1.status,-1) <> 0
2697: and nvl(mrr1.applied,-1) <> 2
2698: and nvl(mrr.parent_id,-1) = 1
2699: and ( ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
2700: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
2701: between p_start_date and p_end_date
2702: )
2703: or ( msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2699: and ( ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
2700: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
2701: between p_start_date and p_end_date
2702: )
2703: or ( msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2704: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
2705: between p_start_date and p_end_date )
2706: or ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
2707: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) <= p_start_date
2702: )
2703: or ( msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2704: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
2705: between p_start_date and p_end_date )
2706: or ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
2707: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) <= p_start_date
2708: and msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2709: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) >= p_end_date )
2710: );
2704: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
2705: between p_start_date and p_end_date )
2706: or ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
2707: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) <= p_start_date
2708: and msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2709: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) >= p_end_date )
2710: );
2711:
2712: cursor res_req_btch_cur is
2710: );
2711:
2712: cursor res_req_btch_cur is
2713: select mgq.row_index,
2714: min(msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
2715: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)) start_date,
2716: max(msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrr.start_date,
2717: mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)) end_date,
2718: sum(mrr.capacity_consumed_ratio) assigned_units,
2712: cursor res_req_btch_cur is
2713: select mgq.row_index,
2714: min(msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
2715: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)) start_date,
2716: max(msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrr.start_date,
2717: mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)) end_date,
2718: sum(mrr.capacity_consumed_ratio) assigned_units,
2719: avg(mrr.resource_hours) resource_hours,
2720: mrr.batch_number,
2717: mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)) end_date,
2718: sum(mrr.capacity_consumed_ratio) assigned_units,
2719: avg(mrr.resource_hours) resource_hours,
2720: mrr.batch_number,
2721: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
2722: decode(p_display_type, DISPLAY_NONE, sys_no,
2723: msc_gantt_utils.getDisplayType(p_display_type,
2724: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2725: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2719: avg(mrr.resource_hours) resource_hours,
2720: mrr.batch_number,
2721: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
2722: decode(p_display_type, DISPLAY_NONE, sys_no,
2723: msc_gantt_utils.getDisplayType(p_display_type,
2724: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2725: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2726: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2727: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2720: mrr.batch_number,
2721: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
2722: decode(p_display_type, DISPLAY_NONE, sys_no,
2723: msc_gantt_utils.getDisplayType(p_display_type,
2724: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2725: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2726: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2727: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2728: mrr.orig_resource_seq_num, mrr.parent_seq_num),
2722: decode(p_display_type, DISPLAY_NONE, sys_no,
2723: msc_gantt_utils.getDisplayType(p_display_type,
2724: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2725: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2726: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2727: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2728: mrr.orig_resource_seq_num, mrr.parent_seq_num),
2729: mrr.firm_flag,
2730: msc_gantt_utils.isResConstraint(mrr.plan_id,
2726: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2727: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2728: mrr.orig_resource_seq_num, mrr.parent_seq_num),
2729: mrr.firm_flag,
2730: msc_gantt_utils.isResConstraint(mrr.plan_id,
2731: mrr.sr_instance_id, mrr.organization_id,
2732: ms.inventory_item_id, mrr.department_id, mrr.resource_id, mrr.supply_id),
2733: g_gantt_rh_toler_days_early,
2734: g_gantt_rh_toler_days_late)) display_type
2757: and mrr.batch_number is not null and mrr.end_date is not null and mrr.resource_hours > 0
2758: and nvl(mrr.parent_id, 2) = 2
2759: and nvl(mrr.status,-1) = 0
2760: and nvl(mrr.applied,-1) = 2
2761: and ( ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
2762: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
2763: between p_start_date and p_end_date
2764: )
2765: or ( msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2761: and ( ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
2762: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
2763: between p_start_date and p_end_date
2764: )
2765: or ( msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2766: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
2767: between p_start_date and p_end_date )
2768: or ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
2769: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) <= p_start_date
2764: )
2765: or ( msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2766: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
2767: between p_start_date and p_end_date )
2768: or ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
2769: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) <= p_start_date
2770: and msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2771: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) >= p_end_date )
2772: )
2766: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
2767: between p_start_date and p_end_date )
2768: or ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
2769: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) <= p_start_date
2770: and msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2771: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) >= p_end_date )
2772: )
2773: group by
2774: mgq.row_index,
2772: )
2773: group by
2774: mgq.row_index,
2775: mrr.batch_number,
2776: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id),
2777: msc_gantt_utils.getDisplayType(p_display_type,
2778: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2779: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2780: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2773: group by
2774: mgq.row_index,
2775: mrr.batch_number,
2776: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id),
2777: msc_gantt_utils.getDisplayType(p_display_type,
2778: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2779: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2780: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2781: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2774: mgq.row_index,
2775: mrr.batch_number,
2776: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id),
2777: msc_gantt_utils.getDisplayType(p_display_type,
2778: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2779: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2780: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2781: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2782: mrr.orig_resource_seq_num, mrr.parent_seq_num),
2776: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id),
2777: msc_gantt_utils.getDisplayType(p_display_type,
2778: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2779: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2780: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2781: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2782: mrr.orig_resource_seq_num, mrr.parent_seq_num),
2783: mrr.firm_flag,
2784: msc_gantt_utils.isResConstraint(mrr.plan_id,
2780: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2781: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2782: mrr.orig_resource_seq_num, mrr.parent_seq_num),
2783: mrr.firm_flag,
2784: msc_gantt_utils.isResConstraint(mrr.plan_id,
2785: mrr.sr_instance_id, mrr.organization_id,
2786: ms.inventory_item_id, mrr.department_id, mrr.resource_id, mrr.supply_id),
2787: g_gantt_rh_toler_days_early,
2788: g_gantt_rh_toler_days_late)
2787: g_gantt_rh_toler_days_early,
2788: g_gantt_rh_toler_days_late)
2789: union all
2790: select mgq.row_index,
2791: min(msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
2792: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)) start_date,
2793: max(msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrr.start_date,
2794: mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)) end_date,
2795: sum(mrr.capacity_consumed_ratio) assigned_units,
2789: union all
2790: select mgq.row_index,
2791: min(msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
2792: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)) start_date,
2793: max(msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrr.start_date,
2794: mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)) end_date,
2795: sum(mrr.capacity_consumed_ratio) assigned_units,
2796: avg(mrr.resource_hours) resource_hours,
2797: mrr.batch_number,
2794: mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)) end_date,
2795: sum(mrr.capacity_consumed_ratio) assigned_units,
2796: avg(mrr.resource_hours) resource_hours,
2797: mrr.batch_number,
2798: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
2799: decode(p_display_type, DISPLAY_NONE, sys_no,
2800: msc_gantt_utils.getDisplayType(p_display_type,
2801: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2802: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2796: avg(mrr.resource_hours) resource_hours,
2797: mrr.batch_number,
2798: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
2799: decode(p_display_type, DISPLAY_NONE, sys_no,
2800: msc_gantt_utils.getDisplayType(p_display_type,
2801: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2802: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2803: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2804: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2797: mrr.batch_number,
2798: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
2799: decode(p_display_type, DISPLAY_NONE, sys_no,
2800: msc_gantt_utils.getDisplayType(p_display_type,
2801: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2802: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2803: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2804: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2805: mrr.orig_resource_seq_num, mrr.parent_seq_num),
2799: decode(p_display_type, DISPLAY_NONE, sys_no,
2800: msc_gantt_utils.getDisplayType(p_display_type,
2801: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2802: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2803: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2804: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2805: mrr.orig_resource_seq_num, mrr.parent_seq_num),
2806: mrr.firm_flag,
2807: msc_gantt_utils.isResConstraint(mrr.plan_id,
2803: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2804: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2805: mrr.orig_resource_seq_num, mrr.parent_seq_num),
2806: mrr.firm_flag,
2807: msc_gantt_utils.isResConstraint(mrr.plan_id,
2808: mrr.sr_instance_id, mrr.organization_id,
2809: ms.inventory_item_id, mrr.department_id, mrr.resource_id, mrr.supply_id),
2810: g_gantt_rh_toler_days_early,
2811: g_gantt_rh_toler_days_late)) display_type
2847: and nvl(mrr1.parent_id, 2) = 2
2848: and nvl(mrr1.status,-1) <> 0
2849: and nvl(mrr1.applied,-1) <> 2
2850: and nvl(mrr.parent_id,-1) = 1
2851: and ( ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
2852: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
2853: between p_start_date and p_end_date
2854: )
2855: or ( msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2851: and ( ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
2852: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
2853: between p_start_date and p_end_date
2854: )
2855: or ( msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2856: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
2857: between p_start_date and p_end_date )
2858: or ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
2859: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) <= p_start_date
2854: )
2855: or ( msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2856: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
2857: between p_start_date and p_end_date )
2858: or ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
2859: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) <= p_start_date
2860: and msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2861: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) >= p_end_date )
2862: )
2856: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
2857: between p_start_date and p_end_date )
2858: or ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
2859: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) <= p_start_date
2860: and msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2861: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) >= p_end_date )
2862: )
2863: group by
2864: mgq.row_index,
2862: )
2863: group by
2864: mgq.row_index,
2865: mrr.batch_number,
2866: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id),
2867: msc_gantt_utils.getDisplayType(p_display_type,
2868: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2869: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2870: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2863: group by
2864: mgq.row_index,
2865: mrr.batch_number,
2866: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id),
2867: msc_gantt_utils.getDisplayType(p_display_type,
2868: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2869: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2870: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2871: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2864: mgq.row_index,
2865: mrr.batch_number,
2866: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id),
2867: msc_gantt_utils.getDisplayType(p_display_type,
2868: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2869: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2870: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2871: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2872: mrr.orig_resource_seq_num, mrr.parent_seq_num),
2866: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id),
2867: msc_gantt_utils.getDisplayType(p_display_type,
2868: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2869: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2870: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2871: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2872: mrr.orig_resource_seq_num, mrr.parent_seq_num),
2873: mrr.firm_flag,
2874: msc_gantt_utils.isResConstraint(mrr.plan_id,
2870: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2871: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2872: mrr.orig_resource_seq_num, mrr.parent_seq_num),
2873: mrr.firm_flag,
2874: msc_gantt_utils.isResConstraint(mrr.plan_id,
2875: mrr.sr_instance_id, mrr.organization_id,
2876: ms.inventory_item_id, mrr.department_id, mrr.resource_id, mrr.supply_id),
2877: g_gantt_rh_toler_days_early,
2878: g_gantt_rh_toler_days_late);
2878: g_gantt_rh_toler_days_late);
2879:
2880: cursor res_inst_req_btch_cur is
2881: select mgq.row_index,
2882: min(msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrir.start_date, mrir.end_date,
2883: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)) start_date,
2884: max(msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrir.start_date,
2885: mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)) end_date,
2886: sum(mrir.capacity_consumed_ratio) assigned_units,
2880: cursor res_inst_req_btch_cur is
2881: select mgq.row_index,
2882: min(msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrir.start_date, mrir.end_date,
2883: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)) start_date,
2884: max(msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrir.start_date,
2885: mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)) end_date,
2886: sum(mrir.capacity_consumed_ratio) assigned_units,
2887: avg(mrir.resource_instance_hours) resource_hours,
2888: mrir.batch_number,
2885: mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)) end_date,
2886: sum(mrir.capacity_consumed_ratio) assigned_units,
2887: avg(mrir.resource_instance_hours) resource_hours,
2888: mrir.batch_number,
2889: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
2890: decode(p_display_type, DISPLAY_NONE, sys_no,
2891: msc_gantt_utils.getDisplayType(p_display_type,
2892: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2893: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2887: avg(mrir.resource_instance_hours) resource_hours,
2888: mrir.batch_number,
2889: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
2890: decode(p_display_type, DISPLAY_NONE, sys_no,
2891: msc_gantt_utils.getDisplayType(p_display_type,
2892: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2893: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2894: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2895: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2888: mrir.batch_number,
2889: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
2890: decode(p_display_type, DISPLAY_NONE, sys_no,
2891: msc_gantt_utils.getDisplayType(p_display_type,
2892: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2893: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2894: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2895: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2896: mrr.orig_resource_seq_num, mrr.parent_seq_num),
2890: decode(p_display_type, DISPLAY_NONE, sys_no,
2891: msc_gantt_utils.getDisplayType(p_display_type,
2892: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2893: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2894: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2895: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2896: mrr.orig_resource_seq_num, mrr.parent_seq_num),
2897: mrr.firm_flag,
2898: msc_gantt_utils.isResConstraint(mrr.plan_id,
2894: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2895: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2896: mrr.orig_resource_seq_num, mrr.parent_seq_num),
2897: mrr.firm_flag,
2898: msc_gantt_utils.isResConstraint(mrr.plan_id,
2899: mrr.sr_instance_id, mrr.organization_id,
2900: ms.inventory_item_id, mrr.department_id, mrr.resource_id, mrr.supply_id),
2901: g_gantt_rh_toler_days_early,
2902: g_gantt_rh_toler_days_late)) display_type
2942: and mrr.batch_number is not null and mrr.end_date is not null and mrr.resource_hours > 0
2943: and nvl(mrr.parent_id, 2) = 2
2944: and nvl(mrr.status,-1) = 0
2945: and nvl(mrr.applied,-1) = 2
2946: and ( ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
2947: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
2948: between p_start_date and p_end_date
2949: )
2950: or ( msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2946: and ( ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
2947: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
2948: between p_start_date and p_end_date
2949: )
2950: or ( msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2951: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
2952: between p_start_date and p_end_date )
2953: or ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
2954: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) <= p_start_date
2949: )
2950: or ( msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2951: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
2952: between p_start_date and p_end_date )
2953: or ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
2954: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) <= p_start_date
2955: and msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2956: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) >= p_end_date )
2957: )
2951: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
2952: between p_start_date and p_end_date )
2953: or ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
2954: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) <= p_start_date
2955: and msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2956: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) >= p_end_date )
2957: )
2958: group by
2959: mgq.row_index,
2957: )
2958: group by
2959: mgq.row_index,
2960: mrir.batch_number,
2961: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id),
2962: msc_gantt_utils.getDisplayType(p_display_type,
2963: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2964: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2965: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2958: group by
2959: mgq.row_index,
2960: mrir.batch_number,
2961: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id),
2962: msc_gantt_utils.getDisplayType(p_display_type,
2963: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2964: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2965: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2966: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2959: mgq.row_index,
2960: mrir.batch_number,
2961: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id),
2962: msc_gantt_utils.getDisplayType(p_display_type,
2963: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2964: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2965: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2966: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2967: mrr.orig_resource_seq_num, mrr.parent_seq_num),
2961: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id),
2962: msc_gantt_utils.getDisplayType(p_display_type,
2963: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2964: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2965: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2966: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2967: mrr.orig_resource_seq_num, mrr.parent_seq_num),
2968: mrr.firm_flag,
2969: msc_gantt_utils.isResConstraint(mrr.plan_id,
2965: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2966: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2967: mrr.orig_resource_seq_num, mrr.parent_seq_num),
2968: mrr.firm_flag,
2969: msc_gantt_utils.isResConstraint(mrr.plan_id,
2970: mrr.sr_instance_id, mrr.organization_id,
2971: ms.inventory_item_id, mrr.department_id, mrr.resource_id, mrr.supply_id),
2972: g_gantt_rh_toler_days_early,
2973: g_gantt_rh_toler_days_late)
2972: g_gantt_rh_toler_days_early,
2973: g_gantt_rh_toler_days_late)
2974: union all
2975: select mgq.row_index,
2976: min(msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrir.start_date, mrir.end_date,
2977: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)) start_date,
2978: max(msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrir.start_date,
2979: mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)) end_date,
2980: sum(mrir.capacity_consumed_ratio) assigned_units,
2974: union all
2975: select mgq.row_index,
2976: min(msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrir.start_date, mrir.end_date,
2977: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)) start_date,
2978: max(msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrir.start_date,
2979: mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)) end_date,
2980: sum(mrir.capacity_consumed_ratio) assigned_units,
2981: avg(mrir.resource_instance_hours) resource_hours,
2982: mrir.batch_number,
2979: mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)) end_date,
2980: sum(mrir.capacity_consumed_ratio) assigned_units,
2981: avg(mrir.resource_instance_hours) resource_hours,
2982: mrir.batch_number,
2983: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
2984: decode(p_display_type, DISPLAY_NONE, sys_no,
2985: msc_gantt_utils.getDisplayType(p_display_type,
2986: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2987: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2981: avg(mrir.resource_instance_hours) resource_hours,
2982: mrir.batch_number,
2983: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
2984: decode(p_display_type, DISPLAY_NONE, sys_no,
2985: msc_gantt_utils.getDisplayType(p_display_type,
2986: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2987: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2988: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2989: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2982: mrir.batch_number,
2983: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
2984: decode(p_display_type, DISPLAY_NONE, sys_no,
2985: msc_gantt_utils.getDisplayType(p_display_type,
2986: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2987: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2988: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2989: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2990: mrr.orig_resource_seq_num, mrr.parent_seq_num),
2984: decode(p_display_type, DISPLAY_NONE, sys_no,
2985: msc_gantt_utils.getDisplayType(p_display_type,
2986: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2987: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2988: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2989: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2990: mrr.orig_resource_seq_num, mrr.parent_seq_num),
2991: mrr.firm_flag,
2992: msc_gantt_utils.isResConstraint(mrr.plan_id,
2988: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2989: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2990: mrr.orig_resource_seq_num, mrr.parent_seq_num),
2991: mrr.firm_flag,
2992: msc_gantt_utils.isResConstraint(mrr.plan_id,
2993: mrr.sr_instance_id, mrr.organization_id,
2994: ms.inventory_item_id, mrr.department_id, mrr.resource_id, mrr.supply_id),
2995: g_gantt_rh_toler_days_early,
2996: g_gantt_rh_toler_days_late)) display_type
3049: and nvl(mrr1.parent_id, 2) = 2
3050: and nvl(mrr1.status,-1) <> 0
3051: and nvl(mrr1.applied,-1) <> 2
3052: and nvl(mrr.parent_id,-1) = 1
3053: and ( ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
3054: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
3055: between p_start_date and p_end_date
3056: )
3057: or ( msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
3053: and ( ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
3054: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
3055: between p_start_date and p_end_date
3056: )
3057: or ( msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
3058: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
3059: between p_start_date and p_end_date )
3060: or ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
3061: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) <= p_start_date
3056: )
3057: or ( msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
3058: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
3059: between p_start_date and p_end_date )
3060: or ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
3061: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) <= p_start_date
3062: and msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
3063: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) >= p_end_date )
3064: )
3058: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
3059: between p_start_date and p_end_date )
3060: or ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
3061: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) <= p_start_date
3062: and msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
3063: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) >= p_end_date )
3064: )
3065: group by
3066: mgq.row_index,
3064: )
3065: group by
3066: mgq.row_index,
3067: mrir.batch_number,
3068: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id),
3069: msc_gantt_utils.getDisplayType(p_display_type,
3070: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
3071: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
3072: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
3065: group by
3066: mgq.row_index,
3067: mrir.batch_number,
3068: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id),
3069: msc_gantt_utils.getDisplayType(p_display_type,
3070: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
3071: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
3072: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
3073: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
3066: mgq.row_index,
3067: mrir.batch_number,
3068: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id),
3069: msc_gantt_utils.getDisplayType(p_display_type,
3070: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
3071: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
3072: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
3073: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
3074: mrr.orig_resource_seq_num, mrr.parent_seq_num),
3068: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id),
3069: msc_gantt_utils.getDisplayType(p_display_type,
3070: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
3071: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
3072: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
3073: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
3074: mrr.orig_resource_seq_num, mrr.parent_seq_num),
3075: mrr.firm_flag,
3076: msc_gantt_utils.isResConstraint(mrr.plan_id,
3072: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
3073: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
3074: mrr.orig_resource_seq_num, mrr.parent_seq_num),
3075: mrr.firm_flag,
3076: msc_gantt_utils.isResConstraint(mrr.plan_id,
3077: mrr.sr_instance_id, mrr.organization_id,
3078: ms.inventory_item_id, mrr.department_id, mrr.resource_id, mrr.supply_id),
3079: g_gantt_rh_toler_days_early,
3080: g_gantt_rh_toler_days_late);
3085: l_res_inst_batch_rows number;
3086:
3087: begin
3088:
3089: --l_res_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_no, sys_no, sys_yes);
3090: --l_res_inst_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_yes, sys_no, sys_yes);
3091: --l_res_batch_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_no, sys_yes);
3092: --l_res_inst_batch_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_yes, sys_yes);
3093: --4997096 bugfix, instead calls the below procedure
3086:
3087: begin
3088:
3089: --l_res_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_no, sys_no, sys_yes);
3090: --l_res_inst_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_yes, sys_no, sys_yes);
3091: --l_res_batch_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_no, sys_yes);
3092: --l_res_inst_batch_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_yes, sys_yes);
3093: --4997096 bugfix, instead calls the below procedure
3094: msc_gantt_utils.isResRowInGantt(p_query_id, l_res_rows, l_res_inst_rows, l_res_batch_rows, l_res_inst_batch_rows);
3087: begin
3088:
3089: --l_res_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_no, sys_no, sys_yes);
3090: --l_res_inst_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_yes, sys_no, sys_yes);
3091: --l_res_batch_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_no, sys_yes);
3092: --l_res_inst_batch_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_yes, sys_yes);
3093: --4997096 bugfix, instead calls the below procedure
3094: msc_gantt_utils.isResRowInGantt(p_query_id, l_res_rows, l_res_inst_rows, l_res_batch_rows, l_res_inst_batch_rows);
3095:
3088:
3089: --l_res_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_no, sys_no, sys_yes);
3090: --l_res_inst_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_yes, sys_no, sys_yes);
3091: --l_res_batch_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_no, sys_yes);
3092: --l_res_inst_batch_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_yes, sys_yes);
3093: --4997096 bugfix, instead calls the below procedure
3094: msc_gantt_utils.isResRowInGantt(p_query_id, l_res_rows, l_res_inst_rows, l_res_batch_rows, l_res_inst_batch_rows);
3095:
3096: put_line('populateResReqGanttNew: res - res inst - res batch - res inst batch: '||
3090: --l_res_inst_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_yes, sys_no, sys_yes);
3091: --l_res_batch_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_no, sys_yes);
3092: --l_res_inst_batch_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_yes, sys_yes);
3093: --4997096 bugfix, instead calls the below procedure
3094: msc_gantt_utils.isResRowInGantt(p_query_id, l_res_rows, l_res_inst_rows, l_res_batch_rows, l_res_inst_batch_rows);
3095:
3096: put_line('populateResReqGanttNew: res - res inst - res batch - res inst batch: '||
3097: l_res_rows ||' - '|| l_res_inst_rows ||' - '|| l_res_batch_rows
3098: ||' - '|| l_res_inst_batch_rows);
3196:
3197: procedure populateResActGantt(p_query_id number,
3198: p_start_date date, p_end_date date,
3199: p_batched_res_act number,
3200: p_require_data IN OUT NOCOPY msc_gantt_utils.maxCharTbl,
3201: p_display_type number) is
3202:
3203: cursor res_act_cur is
3204: select mgq.row_index,
3209: nvl(mrr.status,0) status,
3210: nvl(mrr.applied,0) applied,
3211: nvl(mrr.firm_flag,0) res_firm_flag,
3212: ms.firm_planned_type sup_firm_flag,
3213: msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
3214: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) start_date,
3215: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
3216: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) end_date,
3217: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
3211: nvl(mrr.firm_flag,0) res_firm_flag,
3212: ms.firm_planned_type sup_firm_flag,
3213: msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
3214: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) start_date,
3215: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
3216: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) end_date,
3217: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
3218: msc_gantt_utils.isResConstraint(mrr.plan_id, mrr.sr_instance_id,
3219: mrr.organization_id, ms.inventory_item_id,
3213: msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
3214: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) start_date,
3215: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
3216: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) end_date,
3217: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
3218: msc_gantt_utils.isResConstraint(mrr.plan_id, mrr.sr_instance_id,
3219: mrr.organization_id, ms.inventory_item_id,
3220: mrr.department_id, mrr.resource_id, mrr.supply_id) res_constraint,
3221: ms.new_order_quantity qty,
3214: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) start_date,
3215: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
3216: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) end_date,
3217: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
3218: msc_gantt_utils.isResConstraint(mrr.plan_id, mrr.sr_instance_id,
3219: mrr.organization_id, ms.inventory_item_id,
3220: mrr.department_id, mrr.resource_id, mrr.supply_id) res_constraint,
3221: ms.new_order_quantity qty,
3222: nvl(mrr.batch_number, mbp_null_value),
3229: mrr.ulpcd,
3230: mrr.uepsd,
3231: mrr.uepcd,
3232: mrr.eacd,
3233: msc_gantt_utils.getResActResNodeLabel(mrr.plan_id, mrr.sr_instance_id, mrr.transaction_id) bar_text,
3234: ms.inventory_item_id,
3235: msc_get_name.supply_order_number(ms.order_type, ms.order_number, ms.plan_id,
3236: ms.sr_instance_id, ms.transaction_id, ms.disposition_id) order_number,
3237: mrr.operation_seq_num,
3250: msc_get_name.setup_code(mrr.plan_id, mrr.sr_instance_id, mrr.resource_id,
3251: mrr.organization_id, mrr.setup_id)) to_setup_code,
3252: decode(mrr.from_setup_id,
3253: to_number(null), null,
3254: msc_gantt_utils.getTansitionValue(mrr.plan_id, mrr.sr_instance_id,
3255: mrr.organization_id, mrr.department_id, mrr.resource_id, mrr.from_setup_id,
3256: mrr.setup_id, 'STANDARD_OPERATION_CODE')) std_op_code,
3257: decode(mrr.from_setup_id,
3258: to_number(null), null,
3255: mrr.organization_id, mrr.department_id, mrr.resource_id, mrr.from_setup_id,
3256: mrr.setup_id, 'STANDARD_OPERATION_CODE')) std_op_code,
3257: decode(mrr.from_setup_id,
3258: to_number(null), null,
3259: msc_gantt_utils.getTansitionValue(mrr.plan_id, mrr.sr_instance_id, mrr.organization_id,
3260: mrr.department_id, mrr.resource_id, mrr.from_setup_id,
3261: mrr.setup_id, 'TRANSITION_TIME')) changeover_time,
3262: decode(mrr.from_setup_id,
3263: to_number(null), null,
3260: mrr.department_id, mrr.resource_id, mrr.from_setup_id,
3261: mrr.setup_id, 'TRANSITION_TIME')) changeover_time,
3262: decode(mrr.from_setup_id,
3263: to_number(null), null,
3264: msc_gantt_utils.getTansitionValue(mrr.plan_id, mrr.sr_instance_id, mrr.organization_id,
3265: mrr.department_id, mrr.resource_id, mrr.from_setup_id,
3266: mrr.setup_id, 'TRANSITION_PENALTY')) changeover_penalty,
3267: msc_gantt_utils.isResOverload(mrr.plan_id, mrr.sr_instance_id,
3268: mrr.organization_id, ms.inventory_item_id,
3263: to_number(null), null,
3264: msc_gantt_utils.getTansitionValue(mrr.plan_id, mrr.sr_instance_id, mrr.organization_id,
3265: mrr.department_id, mrr.resource_id, mrr.from_setup_id,
3266: mrr.setup_id, 'TRANSITION_PENALTY')) changeover_penalty,
3267: msc_gantt_utils.isResOverload(mrr.plan_id, mrr.sr_instance_id,
3268: mrr.organization_id, ms.inventory_item_id,
3269: mrr.department_id, mrr.resource_id, mrr.supply_id) res_overload
3270: from msc_resource_requirements mrr,
3271: msc_department_resources mdr,
3287: and mrr.department_id = mgq.department_id
3288: and mrr.resource_id = mgq.resource_id
3289: and mrr.end_date is not null
3290: and nvl(mrr.parent_id,2) =2
3291: and ( ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
3292: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
3293: between p_start_date and p_end_date
3294: )
3295: or ( msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
3291: and ( ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
3292: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
3293: between p_start_date and p_end_date
3294: )
3295: or ( msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
3296: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
3297: between p_start_date and p_end_date )
3298: or ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
3299: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) <= p_start_date
3294: )
3295: or ( msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
3296: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
3297: between p_start_date and p_end_date )
3298: or ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
3299: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) <= p_start_date
3300: and msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
3301: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) >= p_end_date )
3302: )
3296: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
3297: between p_start_date and p_end_date )
3298: or ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
3299: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) <= p_start_date
3300: and msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
3301: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) >= p_end_date )
3302: )
3303: and mrr.plan_id = mdr.plan_id
3304: and mrr.organization_id = mdr.organization_id
3329: nvl(mrr.status,0) status,
3330: nvl(mrr.applied,0) applied,
3331: nvl(mrr.firm_flag,0) res_firm_flag,
3332: ms.firm_planned_type sup_firm_flag,
3333: msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrir.start_date, mrir.end_date,
3334: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) start_date,
3335: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrir.start_date, mrir.end_date,
3336: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) end_date,
3337: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
3331: nvl(mrr.firm_flag,0) res_firm_flag,
3332: ms.firm_planned_type sup_firm_flag,
3333: msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrir.start_date, mrir.end_date,
3334: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) start_date,
3335: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrir.start_date, mrir.end_date,
3336: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) end_date,
3337: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
3338: msc_gantt_utils.isResConstraint(mrr.plan_id, mrr.sr_instance_id,
3339: mrr.organization_id, ms.inventory_item_id,
3333: msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrir.start_date, mrir.end_date,
3334: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) start_date,
3335: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrir.start_date, mrir.end_date,
3336: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) end_date,
3337: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
3338: msc_gantt_utils.isResConstraint(mrr.plan_id, mrr.sr_instance_id,
3339: mrr.organization_id, ms.inventory_item_id,
3340: mrr.department_id, mrr.resource_id, mrr.supply_id) res_constraint,
3341: ms.new_order_quantity qty,
3334: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) start_date,
3335: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrir.start_date, mrir.end_date,
3336: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) end_date,
3337: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
3338: msc_gantt_utils.isResConstraint(mrr.plan_id, mrr.sr_instance_id,
3339: mrr.organization_id, ms.inventory_item_id,
3340: mrr.department_id, mrr.resource_id, mrr.supply_id) res_constraint,
3341: ms.new_order_quantity qty,
3342: nvl(mrir.batch_number, mbp_null_value),
3349: mrr.ulpcd,
3350: mrr.uepsd,
3351: mrr.uepcd,
3352: mrr.eacd,
3353: msc_gantt_utils.getResActResNodeLabel(mrr.plan_id, mrr.sr_instance_id, mrr.transaction_id) bar_text,
3354: ms.inventory_item_id,
3355: msc_get_name.supply_order_number(ms.order_type, ms.order_number, ms.plan_id,
3356: ms.sr_instance_id, ms.transaction_id, ms.disposition_id) order_number,
3357: mrr.operation_seq_num,
3370: msc_get_name.setup_code(mrr.plan_id, mrr.sr_instance_id, mrr.resource_id,
3371: mrr.organization_id, mrr.setup_id)) to_setup_code,
3372: decode(mrr.from_setup_id,
3373: to_number(null), null,
3374: msc_gantt_utils.getTansitionValue(mrr.plan_id, mrr.sr_instance_id,
3375: mrr.organization_id, mrr.department_id, mrr.resource_id, mrr.from_setup_id,
3376: mrr.setup_id, 'STANDARD_OPERATION_CODE')) std_op_code,
3377: decode(mrr.from_setup_id,
3378: to_number(null), null,
3375: mrr.organization_id, mrr.department_id, mrr.resource_id, mrr.from_setup_id,
3376: mrr.setup_id, 'STANDARD_OPERATION_CODE')) std_op_code,
3377: decode(mrr.from_setup_id,
3378: to_number(null), null,
3379: msc_gantt_utils.getTansitionValue(mrr.plan_id, mrr.sr_instance_id, mrr.organization_id,
3380: mrr.department_id, mrr.resource_id, mrr.from_setup_id,
3381: mrr.setup_id, 'TRANSITION_TIME')) changeover_time,
3382: decode(mrr.from_setup_id,
3383: to_number(null), null,
3380: mrr.department_id, mrr.resource_id, mrr.from_setup_id,
3381: mrr.setup_id, 'TRANSITION_TIME')) changeover_time,
3382: decode(mrr.from_setup_id,
3383: to_number(null), null,
3384: msc_gantt_utils.getTansitionValue(mrr.plan_id, mrr.sr_instance_id, mrr.organization_id,
3385: mrr.department_id, mrr.resource_id, mrr.from_setup_id,
3386: mrr.setup_id, 'TRANSITION_PENALTY')) changeover_penalty,
3387: msc_gantt_utils.isResOverload(mrr.plan_id, mrr.sr_instance_id,
3388: mrr.organization_id, ms.inventory_item_id,
3383: to_number(null), null,
3384: msc_gantt_utils.getTansitionValue(mrr.plan_id, mrr.sr_instance_id, mrr.organization_id,
3385: mrr.department_id, mrr.resource_id, mrr.from_setup_id,
3386: mrr.setup_id, 'TRANSITION_PENALTY')) changeover_penalty,
3387: msc_gantt_utils.isResOverload(mrr.plan_id, mrr.sr_instance_id,
3388: mrr.organization_id, ms.inventory_item_id,
3389: mrr.department_id, mrr.resource_id, mrr.supply_id) res_overload
3390: from msc_resource_instance_reqs mrir,
3391: msc_resource_requirements mrr,
3422: and nvl(mrir.parent_id, mbp_null_value) = nvl(mrr.parent_id, mbp_null_value)
3423: and mrir.start_date = nvl(mrr.firm_start_date, mrr.start_date)
3424: and mrir.end_date = nvl(mrr.firm_end_date, mrr.end_date)
3425: and nvl(mrr.parent_id, 2) = 2
3426: and ( ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
3427: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
3428: between p_start_date and p_end_date
3429: )
3430: or ( msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
3426: and ( ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
3427: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
3428: between p_start_date and p_end_date
3429: )
3430: or ( msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
3431: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
3432: between p_start_date and p_end_date )
3433: or ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
3434: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) <= p_start_date
3429: )
3430: or ( msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
3431: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
3432: between p_start_date and p_end_date )
3433: or ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
3434: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) <= p_start_date
3435: and msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
3436: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) >= p_end_date )
3437: )
3431: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
3432: between p_start_date and p_end_date )
3433: or ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
3434: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) <= p_start_date
3435: and msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
3436: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) >= p_end_date )
3437: )
3438: and ms.plan_id = mrr.plan_id
3439: and ms.transaction_id = mrr.supply_id
3449: cursor res_act_batch_cur is
3450: select mgq.row_index,
3451: mrr.sr_instance_id,
3452: mrr.organization_id,
3453: msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
3454: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) start_date,
3455: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
3456: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) end_date,
3457: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
3451: mrr.sr_instance_id,
3452: mrr.organization_id,
3453: msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
3454: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) start_date,
3455: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
3456: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) end_date,
3457: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
3458: sum(ms.new_order_quantity) qty,
3459: mrr.batch_number,
3453: msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
3454: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) start_date,
3455: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
3456: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) end_date,
3457: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
3458: sum(ms.new_order_quantity) qty,
3459: mrr.batch_number,
3460: msc_gantt_utils.getResBatchNodeLabel(
3461: nvl(decode(mrr.setup_id,
3456: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) end_date,
3457: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
3458: sum(ms.new_order_quantity) qty,
3459: mrr.batch_number,
3460: msc_gantt_utils.getResBatchNodeLabel(
3461: nvl(decode(mrr.setup_id,
3462: to_number(null), null_space,
3463: msc_gantt_utils.getSetupCode(mrr.plan_id,
3464: mrr.sr_instance_id, mrr.resource_id, mrr.setup_id)),
3459: mrr.batch_number,
3460: msc_gantt_utils.getResBatchNodeLabel(
3461: nvl(decode(mrr.setup_id,
3462: to_number(null), null_space,
3463: msc_gantt_utils.getSetupCode(mrr.plan_id,
3464: mrr.sr_instance_id, mrr.resource_id, mrr.setup_id)),
3465: null_space),
3466: msc_get_name.org_code(mrr.organization_id, mrr.sr_instance_id),
3467: to_char(sum(ms.new_order_quantity)),
3484: and mrr.resource_id = mgq.resource_id
3485: and mrr.batch_number is not null
3486: and mrr.end_date is not null
3487: and nvl(mrr.parent_id,2) = 2
3488: and ( ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
3489: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
3490: between p_start_date and p_end_date
3491: )
3492: or ( msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
3488: and ( ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
3489: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
3490: between p_start_date and p_end_date
3491: )
3492: or ( msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
3493: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
3494: between p_start_date and p_end_date )
3495: or ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
3496: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) <= p_start_date
3491: )
3492: or ( msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
3493: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
3494: between p_start_date and p_end_date )
3495: or ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
3496: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) <= p_start_date
3497: and msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
3498: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) >= p_end_date )
3499: )
3493: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
3494: between p_start_date and p_end_date )
3495: or ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
3496: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) <= p_start_date
3497: and msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
3498: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) >= p_end_date )
3499: )
3500: and ms.plan_id = mrr.plan_id
3501: and ms.transaction_id = mrr.supply_id
3520: mrr.organization_id,
3521: msc_get_name.org_code(mrr.organization_id, mrr.sr_instance_id),
3522: mrr.resource_id,
3523: mrr.batch_number,
3524: msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
3525: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
3526: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
3527: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
3528: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id),
3522: mrr.resource_id,
3523: mrr.batch_number,
3524: msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
3525: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
3526: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
3527: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
3528: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id),
3529: mrr.setup_id;
3530:
3524: msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
3525: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
3526: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
3527: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
3528: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id),
3529: mrr.setup_id;
3530:
3531: cursor res_inst_act_batch_cur is
3532: select mgq.row_index,
3531: cursor res_inst_act_batch_cur is
3532: select mgq.row_index,
3533: mrr.sr_instance_id,
3534: mrr.organization_id,
3535: msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrir.start_date, mrir.end_date,
3536: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) start_date,
3537: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrir.start_date, mrir.end_date,
3538: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) end_date,
3539: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
3533: mrr.sr_instance_id,
3534: mrr.organization_id,
3535: msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrir.start_date, mrir.end_date,
3536: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) start_date,
3537: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrir.start_date, mrir.end_date,
3538: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) end_date,
3539: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
3540: sum(ms.new_order_quantity) qty,
3541: mrir.batch_number,
3535: msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrir.start_date, mrir.end_date,
3536: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) start_date,
3537: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrir.start_date, mrir.end_date,
3538: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) end_date,
3539: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
3540: sum(ms.new_order_quantity) qty,
3541: mrir.batch_number,
3542: msc_gantt_utils.getResBatchNodeLabel(
3543: nvl(decode(mrr.setup_id,
3538: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) end_date,
3539: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
3540: sum(ms.new_order_quantity) qty,
3541: mrir.batch_number,
3542: msc_gantt_utils.getResBatchNodeLabel(
3543: nvl(decode(mrr.setup_id,
3544: to_number(null), null_space,
3545: msc_gantt_utils.getSetupCode(mrr.plan_id,
3546: mrr.sr_instance_id, mrr.resource_id, mrr.setup_id)),
3541: mrir.batch_number,
3542: msc_gantt_utils.getResBatchNodeLabel(
3543: nvl(decode(mrr.setup_id,
3544: to_number(null), null_space,
3545: msc_gantt_utils.getSetupCode(mrr.plan_id,
3546: mrr.sr_instance_id, mrr.resource_id, mrr.setup_id)),
3547: null_space),
3548: msc_get_name.org_code(mrr.organization_id, mrr.sr_instance_id),
3549: to_char(sum(ms.new_order_quantity)),
3580: and nvl(mrir.parent_id, mbp_null_value) = nvl(mrr.parent_id, mbp_null_value)
3581: and mrir.start_date = nvl(mrr.firm_start_date, mrr.start_date)
3582: and mrir.end_date = nvl(mrr.firm_end_date, mrr.end_date)
3583: and nvl(mrr.parent_id, 2) = 2
3584: and ( ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
3585: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
3586: between p_start_date and p_end_date
3587: )
3588: or ( msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
3584: and ( ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
3585: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
3586: between p_start_date and p_end_date
3587: )
3588: or ( msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
3589: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
3590: between p_start_date and p_end_date )
3591: or ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
3592: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) <= p_start_date
3587: )
3588: or ( msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
3589: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
3590: between p_start_date and p_end_date )
3591: or ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
3592: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) <= p_start_date
3593: and msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
3594: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) >= p_end_date )
3595: )
3589: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
3590: between p_start_date and p_end_date )
3591: or ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
3592: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) <= p_start_date
3593: and msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
3594: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) >= p_end_date )
3595: )
3596: and ms.plan_id = mrr.plan_id
3597: and ms.transaction_id = mrr.supply_id
3602: mrr.organization_id,
3603: msc_get_name.org_code(mrr.organization_id, mrr.sr_instance_id),
3604: mrr.resource_id,
3605: mrir.batch_number,
3606: msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrir.start_date, mrir.end_date,
3607: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
3608: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrir.start_date, mrir.end_date,
3609: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
3610: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id),
3604: mrr.resource_id,
3605: mrir.batch_number,
3606: msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrir.start_date, mrir.end_date,
3607: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
3608: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrir.start_date, mrir.end_date,
3609: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
3610: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id),
3611: mrr.setup_id;
3612:
3606: msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrir.start_date, mrir.end_date,
3607: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
3608: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrir.start_date, mrir.end_date,
3609: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
3610: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id),
3611: mrr.setup_id;
3612:
3613: l_op_seq number_arr;
3614: l_res_seq number_arr;
3675: else
3676: l_batch_flag := sys_no;
3677: end if; -- }
3678:
3679: --l_res_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_no, sys_no, sys_yes);
3680: --l_res_inst_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_yes, sys_no, sys_yes);
3681: --l_res_batch_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_no, sys_yes);
3682: --l_res_inst_batch_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_yes, sys_yes);
3683: --4997096 bugfix, instead calls the below procedure
3676: l_batch_flag := sys_no;
3677: end if; -- }
3678:
3679: --l_res_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_no, sys_no, sys_yes);
3680: --l_res_inst_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_yes, sys_no, sys_yes);
3681: --l_res_batch_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_no, sys_yes);
3682: --l_res_inst_batch_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_yes, sys_yes);
3683: --4997096 bugfix, instead calls the below procedure
3684: msc_gantt_utils.isResRowInGantt(p_query_id, l_res_rows, l_res_inst_rows, l_res_batch_rows, l_res_inst_batch_rows, l_batch_flag);
3677: end if; -- }
3678:
3679: --l_res_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_no, sys_no, sys_yes);
3680: --l_res_inst_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_yes, sys_no, sys_yes);
3681: --l_res_batch_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_no, sys_yes);
3682: --l_res_inst_batch_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_yes, sys_yes);
3683: --4997096 bugfix, instead calls the below procedure
3684: msc_gantt_utils.isResRowInGantt(p_query_id, l_res_rows, l_res_inst_rows, l_res_batch_rows, l_res_inst_batch_rows, l_batch_flag);
3685:
3678:
3679: --l_res_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_no, sys_no, sys_yes);
3680: --l_res_inst_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_yes, sys_no, sys_yes);
3681: --l_res_batch_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_no, sys_yes);
3682: --l_res_inst_batch_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_yes, sys_yes);
3683: --4997096 bugfix, instead calls the below procedure
3684: msc_gantt_utils.isResRowInGantt(p_query_id, l_res_rows, l_res_inst_rows, l_res_batch_rows, l_res_inst_batch_rows, l_batch_flag);
3685:
3686: put_line('populateResActGantt: res - inst- res batch - inst batch: '||l_res_rows ||' - '|| l_res_inst_rows ||' - '|| l_res_batch_rows ||' - '|| l_res_inst_batch_rows);
3680: --l_res_inst_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_yes, sys_no, sys_yes);
3681: --l_res_batch_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_no, sys_yes);
3682: --l_res_inst_batch_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_yes, sys_yes);
3683: --4997096 bugfix, instead calls the below procedure
3684: msc_gantt_utils.isResRowInGantt(p_query_id, l_res_rows, l_res_inst_rows, l_res_batch_rows, l_res_inst_batch_rows, l_batch_flag);
3685:
3686: put_line('populateResActGantt: res - inst- res batch - inst batch: '||l_res_rows ||' - '|| l_res_inst_rows ||' - '|| l_res_batch_rows ||' - '|| l_res_inst_batch_rows);
3687:
3688: if ( l_res_rows = sys_yes ) then -- {
3789: nvl(mgdq.resource_units, mbp_null_value),
3790: nvl(mgdq.group_sequence_id, mbp_null_value),
3791: nvl(mgdq.group_sequence_number, mbp_null_value),
3792: nvl(mgdq.bar_label, null_space),
3793: msc_gantt_utils.getDisplayType(p_display_type, mgdq.end_date, mgdq.ulpst,
3794: mgdq.res_firm_flag, mgdq.late_flag, g_gantt_ra_toler_days_early,
3795: g_gantt_ra_toler_days_late) display_type,
3796: mgdq.cepst,
3797: mgdq.cepct,
3971: populateSuppDtlTabIntoGantt(p_query_id, SUPP_AVAIL_ROW_TYPE,
3972: v_row_index, v_start_date, v_qty, v_consumed_qty, SUMMARY_DATA);
3973:
3974: l_mfg_cal_query_id := getMFQSequence(l_mfg_cal_query_id);
3975: msc_gantt_utils.getBucketDates(p_start_date, p_end_date, v_bkt_start, v_bkt_end, l_mfg_cal_query_id);
3976:
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
4106: l_supp_bkt_start_date date;
4107: l_supp_bkt_end_date date;
4108:
4109: begin
4110: msc_gantt_utils.put_line('prepareSupplierGantt in ');
4111: p_start_index := mbp_null_value;
4112:
4113: msc_gantt_utils.getBucketDates(p_start_date, p_end_date, v_bkt_start, v_bkt_end, l_bkt_query_id);
4114:
4109: begin
4110: msc_gantt_utils.put_line('prepareSupplierGantt in ');
4111: p_start_index := mbp_null_value;
4112:
4113: msc_gantt_utils.getBucketDates(p_start_date, p_end_date, v_bkt_start, v_bkt_end, l_bkt_query_id);
4114:
4115: msc_gantt_utils.put_line('claendar rows ' || v_bkt_start.count);
4116:
4117: for active_row in active_res_rows_cur(p_query_id) loop -- {
4111: p_start_index := mbp_null_value;
4112:
4113: msc_gantt_utils.getBucketDates(p_start_date, p_end_date, v_bkt_start, v_bkt_end, l_bkt_query_id);
4114:
4115: msc_gantt_utils.put_line('claendar rows ' || v_bkt_start.count);
4116:
4117: for active_row in active_res_rows_cur(p_query_id) loop -- {
4118: if ( p_start_index = mbp_null_value) then
4119: p_start_index := active_row.row_index;
4186: -- 4476899 bug fix, pass zero's also
4187:
4188: end loop; -- }
4189: end loop; -- }
4190: msc_gantt_utils.put_line('prepareSupplierGantt out ');
4191:
4192: end prepareSupplierGantt;
4193:
4194: procedure sendSupplierGantt(p_query_id number,
4191:
4192: end prepareSupplierGantt;
4193:
4194: procedure sendSupplierGantt(p_query_id number,
4195: p_supp_data IN OUT NOCOPY msc_gantt_utils.maxCharTbl) is
4196:
4197: v_start_date date_arr := date_arr(sysdate);
4198: v_avail_qty number_arr:= number_arr(0);
4199: v_overload_qty number_arr:= number_arr(0);
4231:
4232: end sendSupplierGantt;
4233:
4234: procedure sendResourceGantt(p_query_id number, p_view_type number,
4235: p_isBucketed number, p_require_data IN OUT NOCOPY msc_gantt_utils.maxCharTbl,
4236: p_avail_data IN OUT NOCOPY msc_gantt_utils.maxCharTbl,
4237: p_onlyAvailData boolean, p_display_type number) is
4238:
4239: v_start date_arr:= date_arr(sysdate);
4232: end sendSupplierGantt;
4233:
4234: procedure sendResourceGantt(p_query_id number, p_view_type number,
4235: p_isBucketed number, p_require_data IN OUT NOCOPY msc_gantt_utils.maxCharTbl,
4236: p_avail_data IN OUT NOCOPY msc_gantt_utils.maxCharTbl,
4237: p_onlyAvailData boolean, p_display_type number) is
4238:
4239: v_start date_arr:= date_arr(sysdate);
4240: v_end date_arr:= date_arr(sysdate);
4383:
4384: end calculateResHours;
4385:
4386: procedure getBucketDates(p_start_date date, p_end_date date,
4387: v_bkt_start in out nocopy msc_gantt_utils.date_arr,
4388: v_bkt_end in out nocopy msc_gantt_utils.date_arr,
4389: p_query_id in out nocopy number) is
4390: p_temp_start date;
4391: i number;
4384: end calculateResHours;
4385:
4386: procedure getBucketDates(p_start_date date, p_end_date date,
4387: v_bkt_start in out nocopy msc_gantt_utils.date_arr,
4388: v_bkt_end in out nocopy msc_gantt_utils.date_arr,
4389: p_query_id in out nocopy number) is
4390: p_temp_start date;
4391: i number;
4392: begin
4488: p_start_index number;
4489: l_bkt_query_id number := -1;
4490: begin
4491:
4492: msc_gantt_utils.put_line('prepareResHoursGantt in ');
4493: p_start_index := mbp_null_value;
4494:
4495: msc_gantt_utils.getBucketDates(p_start_date, p_end_date, v_bkt_start, v_bkt_end, l_bkt_query_id);
4496:
4491:
4492: msc_gantt_utils.put_line('prepareResHoursGantt in ');
4493: p_start_index := mbp_null_value;
4494:
4495: msc_gantt_utils.getBucketDates(p_start_date, p_end_date, v_bkt_start, v_bkt_end, l_bkt_query_id);
4496:
4497: msc_gantt_utils.put_line('prepareResHoursGantt: claendar rows: ' || v_bkt_start.count);
4498:
4499: for active_row in active_res_rows_cur(p_query_id) loop -- {
4493: p_start_index := mbp_null_value;
4494:
4495: msc_gantt_utils.getBucketDates(p_start_date, p_end_date, v_bkt_start, v_bkt_end, l_bkt_query_id);
4496:
4497: msc_gantt_utils.put_line('prepareResHoursGantt: claendar rows: ' || v_bkt_start.count);
4498:
4499: for active_row in active_res_rows_cur(p_query_id) loop -- {
4500: if ( p_start_index = mbp_null_value) then
4501: p_start_index := active_row.row_index;
4510: and parent_id = SUMMARY_DATA
4511: and schedule_flag in (RES_SETUP_ROW_TYPE, RES_SETUP_FIXED_ROW_TYPE)
4512: order by start_date; --only setup time
4513:
4514: msc_gantt_utils.put_line('prepareResHoursGantt: res seup rows: '||v_setup_start.count);
4515:
4516:
4517: select start_date, end_date, resource_hours, nvl(display_flag, sys_no)
4518: bulk collect into v_req_start, v_req_end, v_req_hours, v_req_display_type
4523: and parent_id = SUMMARY_DATA
4524: and schedule_flag in (RES_REQ_ROW_TYPE, RES_REQ_SDS_ROW_TYPE)
4525: order by start_date; --only run time
4526:
4527: msc_gantt_utils.put_line('prepareResHoursGantt: res req rows: '|| v_req_start.count);
4528:
4529: select start_date, end_date, resource_hours
4530: bulk collect into v_avail_start, v_avail_end, v_avail_hours
4531: from msc_gantt_dtl_query
4534: and parent_id = SUMMARY_DATA
4535: and row_type = RES_AVAIL_ROW_TYPE
4536: order by start_date; --avail time
4537:
4538: msc_gantt_utils.put_line('prepareResHoursGantt: res avail rows: '|| v_avail_start.count);
4539:
4540: OPEN line_rate(p_query_id, active_row.row_index);
4541: FETCH line_rate INTO v_line_rate;
4542: CLOSE line_rate;
4576: v_line_rate, v_setup_start, v_setup_end, v_setup_hours, v_setup_display_type);
4577:
4578: end loop; -- }
4579: end loop; -- }
4580: msc_gantt_utils.put_line('prepareResHoursGantt out ');
4581:
4582: end prepareResHoursGantt;
4583:
4584:
4677: end if;
4678: elsif ( p_filter_type = FILTER_TYPE_FOLDER_ID ) then
4679: null; --not supported currently..
4680: elsif ( p_filter_type = FILTER_TYPE_WHERE_STMT ) then
4681: msc_gantt_utils.findRequest(p_plan_id, p_list,
4682: p_query_id, p_view_type, FILTER_TYPE_WHERE_STMT, p_folder_id);
4683: elsif ( p_filter_type = FILTER_TYPE_AMONG ) then
4684: msc_gantt_utils.findRequest(p_plan_id, p_list,
4685: p_query_id, p_view_type, FILTER_TYPE_AMONG);
4680: elsif ( p_filter_type = FILTER_TYPE_WHERE_STMT ) then
4681: msc_gantt_utils.findRequest(p_plan_id, p_list,
4682: p_query_id, p_view_type, FILTER_TYPE_WHERE_STMT, p_folder_id);
4683: elsif ( p_filter_type = FILTER_TYPE_AMONG ) then
4684: msc_gantt_utils.findRequest(p_plan_id, p_list,
4685: p_query_id, p_view_type, FILTER_TYPE_AMONG);
4686: elsif ( p_filter_type = FILTER_TYPE_QUERY_ID ) then
4687: populateResIntoGanttFromGantt(p_query_id, p_list, p_plan_id);
4688: end if; -- if ( p_filter_type = 1 ) then
5089: loop
5090: fetch c_res_batch_rows into l_inst_id, l_trx_id;
5091: exit when c_res_batch_rows%notfound;
5092:
5093: msc_gantt_utils.loadAltResource(p_plan_id,
5094: l_inst_id, l_trx_id, p_alt_resource, p_alt_resource_inst, p_serial_number, p_alt_num,
5095: p_node_type, p_to_node_type, p_return_trx_id, p_return_status, p_out);
5096: end loop;
5097: close c_res_batch_rows;
5410: end if;
5411: end if; --}
5412: -- 4569506 bugfix ends
5413:
5414: msc_gantt_utils.getAltResource(p_plan_id, p_transaction_id, p_instance_id,
5415: l_dummy_name_data, l_dummy_id_data, p_node_type, sys_yes);
5416:
5417: select count(*)
5418: into l_alt_count
5465: p_out := 'CANNOT_OFFLOAD_1_ASGN_UNITS';
5466: return;
5467: end if;
5468:
5469: msc_gantt_utils.updateReqInstFromAlt(p_plan_id, p_instance_id,
5470: ll_res_inst_trx_id, p_alt_resource, p_alt_resource_inst, p_serial_number,
5471: null, p_alt_resource, null);
5472:
5473: -- update requirement's supply
5470: ll_res_inst_trx_id, p_alt_resource, p_alt_resource_inst, p_serial_number,
5471: null, p_alt_resource, null);
5472:
5473: -- update requirement's supply
5474: msc_gantt_utils.updateSupplies(p_plan_id, l_supply_id, TOUCH_SUPPLY);
5475:
5476: p_return_status := 'OK';
5477: p_out := '';
5478: return;
5545: end if;
5546:
5547: ll_temp_res_inst_trx_id := get_child_res_trx_id(p_plan_id, p_instance_id, l_simu_res(1).transaction_id);
5548:
5549: l_dummy_id := msc_gantt_utils.insertReqFromAlt(p_plan_id, p_instance_id,
5550: l_simu_res(1).transaction_id , l_alt_res(i).resource_id, v_hours,
5551: l_alt_res(i).alternate_number, l_alt_res(i).basis_type,
5552: l_alt_res(i).orig_resource_seq_num);
5553:
5555: elsif i > l_alt_res.last then -- delete the extra res
5556:
5557: put_line(' in i > l_alt_res.last - deleting alt res into mrr ');
5558: ll_temp_res_inst_trx_id := get_child_res_trx_id(p_plan_id, p_instance_id, l_simu_res(i).transaction_id);
5559: msc_gantt_utils.DeleteReqFromAlt(p_plan_id, p_instance_id, l_simu_res(i).transaction_id);
5560: DeleteReqInstFromAlt(p_plan_id, p_instance_id, ll_temp_res_inst_trx_id);
5561:
5562: else -- update the res to alt_res
5563:
5571:
5572: ll_temp_res_inst_trx_id := get_child_res_trx_id(p_plan_id, p_instance_id, l_simu_res(i).transaction_id);
5573: put_line(' in else simu ll_temp_res_inst_trx_id '||ll_temp_res_inst_trx_id);
5574:
5575: msc_gantt_utils.updateReqFromAlt(p_plan_id, p_instance_id,
5576: l_simu_res(i).transaction_id, l_alt_res(i).resource_id,
5577: v_hours, l_alt_res(i).alternate_number, l_alt_res(i).basis_type,
5578: l_alt_res(i).orig_resource_seq_num);
5579:
5582: if (l_simu_res(i).transaction_id = ll_res_trx_id ) then -- {
5583: put_line(' in res inst update insert');
5584: if ( p_node_type = RES_NODE and p_to_node_type = RES_INST_NODE ) then
5585: put_line(' res inst insert');
5586: l_temp_trx_id := msc_gantt_utils.insertReqInstFromAlt(p_plan_id,
5587: p_instance_id, ll_res_trx_id, p_alt_resource, p_alt_resource_inst,
5588: p_serial_number, v_hours, p_alt_num, RES_NODE,
5589: l_alt_res(i).orig_resource_seq_num);
5590: p_return_trx_id := l_temp_trx_id ;
5589: l_alt_res(i).orig_resource_seq_num);
5590: p_return_trx_id := l_temp_trx_id ;
5591: elsif ( p_node_type = RES_INST_NODE and p_to_node_type = RES_INST_NODE ) then
5592: put_line(' res inst update');
5593: msc_gantt_utils.updateReqInstFromAlt(p_plan_id, p_instance_id,
5594: ll_res_inst_trx_id, p_alt_resource, p_alt_resource_inst, p_serial_number,
5595: v_hours, l_alt_res(i).alternate_number,
5596: l_alt_res(i).orig_resource_seq_num);
5597: end if;
5602: end loop; -- }
5603: end loop; -- }
5604:
5605: -- update requirement's supply
5606: msc_gantt_utils.updateSupplies(p_plan_id, l_supply_id, TOUCH_SUPPLY);
5607:
5608: p_return_status := 'OK';
5609: p_out := '';
5610: exception
5895: return;
5896: end if;
5897:
5898: -- update the simultaneous resource for a given res trans id
5899: msc_gantt_utils.updateReqSimu(p_plan_id, p_inst_id, l_trx_id,
5900: l_firm_flag, l_start_date, l_end_date, l_firm_start_date, l_firm_end_date,
5901: MOVE_MRR, p_return_status, p_out);
5902:
5903: if p_return_status = 'ERROR' then
6272: for update of mrr.firm_flag nowait;
6273:
6274: if ( p_update_mode = FIRM_MRR ) then
6275: -- update requirement
6276: msc_gantt_utils.updateReq(p_plan_id, l_instance_id, l_transaction_id,
6277: p_firm_type, p_start_date, p_end_date, p_firm_start_date, p_firm_end_date, p_update_mode);
6278: elsif ( p_update_mode = MOVE_MRR ) then
6279: moveOneResource(p_plan_id, l_transaction_id, l_instance_id,
6280: p_start_date, p_end_date, p_return_status, p_out, RES_NODE);
6399: (nvl(mrr2.end_date, mrr2.start_date+mrr2.resource_hours/24) - mrr2.start_date),
6400: FIRM_START_RES, mrr2.firm_start_date +
6401: (nvl(mrr2.end_date, mrr2.start_date+mrr2.resource_hours/24) - mrr2.start_date),
6402: nvl(nvl(mrr2.firm_end_date, mrr2.end_date), mrr2.start_date+mrr2.resource_hours/24)) end_date,
6403: msc_gantt_utils.getMTQTime(mrr2.transaction_id, p_plan_id, p_instance_id) mtq_time
6404: from msc_resource_requirements mrr1,
6405: msc_resource_requirements mrr2,
6406: msc_routings mr
6407: where mrr1.plan_id = p_plan_id
6530: (nvl(mrir2.end_date, mrir2.start_date+mrir2.resource_instance_hours/24) - mrir2.start_date),
6531: FIRM_START_RES, mrr2.firm_start_date +
6532: (nvl(mrir2.end_date, mrir2.start_date+mrir2.resource_instance_hours/24) - mrir2.start_date),
6533: nvl(nvl(mrr2.firm_end_date, mrir2.end_date), mrir2.start_date+mrir2.resource_instance_hours/24)) end_date,
6534: msc_gantt_utils.getMTQTime(mrr2.transaction_id, p_plan_id, p_instance_id) mtq_time
6535: from msc_resource_requirements mrr1,
6536: msc_resource_instance_reqs mrir1,
6537: msc_resource_requirements mrr2,
6538: msc_resource_instance_reqs mrir2,
6954: end if; -- }
6955: put_line('ll_res_trx_id ll_res_inst_trx_id '||ll_res_trx_id||' - '||ll_res_inst_trx_id);
6956:
6957: -- lock the mrr and getData
6958: msc_gantt_utils.lockReqNGetData(p_plan_id, p_instance_id, ll_res_trx_id,
6959: l_firm_type, l_supply_id, l_start, l_end, l_firm_start, l_firm_end,
6960: p_return_status, p_out);
6961:
6962: if p_return_status = 'ERROR' then
6964: end if;
6965:
6966:
6967: -- update requirement
6968: msc_gantt_utils.updateReq(p_plan_id, p_instance_id, ll_res_trx_id,
6969: p_firm_type, l_start, l_end, l_firm_start, l_firm_end, FIRM_MRR);
6970:
6971: -- update requirement
6972: msc_gantt_utils.firmReqInst(p_plan_id, p_instance_id, ll_res_trx_id, ll_res_inst_trx_id);
6968: msc_gantt_utils.updateReq(p_plan_id, p_instance_id, ll_res_trx_id,
6969: p_firm_type, l_start, l_end, l_firm_start, l_firm_end, FIRM_MRR);
6970:
6971: -- update requirement
6972: msc_gantt_utils.firmReqInst(p_plan_id, p_instance_id, ll_res_trx_id, ll_res_inst_trx_id);
6973:
6974: -- update requirement's supply
6975: msc_gantt_utils.updateSupplies(p_plan_id, l_supply_id, TOUCH_SUPPLY);
6976:
6971: -- update requirement
6972: msc_gantt_utils.firmReqInst(p_plan_id, p_instance_id, ll_res_trx_id, ll_res_inst_trx_id);
6973:
6974: -- update requirement's supply
6975: msc_gantt_utils.updateSupplies(p_plan_id, l_supply_id, TOUCH_SUPPLY);
6976:
6977: -- update the simultaneous resource for a given res trans id
6978: msc_gantt_utils.updateReqSimu(p_plan_id, p_instance_id, ll_res_trx_id,
6979: p_firm_type, l_start, l_end, l_firm_start, l_firm_end, FIRM_MRR, p_return_status, p_out);
6974: -- update requirement's supply
6975: msc_gantt_utils.updateSupplies(p_plan_id, l_supply_id, TOUCH_SUPPLY);
6976:
6977: -- update the simultaneous resource for a given res trans id
6978: msc_gantt_utils.updateReqSimu(p_plan_id, p_instance_id, ll_res_trx_id,
6979: p_firm_type, l_start, l_end, l_firm_start, l_firm_end, FIRM_MRR, p_return_status, p_out);
6980:
6981: put_line(' firmResourcePub out ');
6982: end firmResourcePub;
7036: end if;
7037: p_new_group_seq_number := p_new_group_seq_number + 10;
7038:
7039: -- lock the mrr and getNewData
7040: msc_gantt_utils.lockReqNGetData(p_plan_id, l_inst_id, ll_res_trx_id,
7041: l_firm_type, l_supply_id, l_start, l_end, l_firm_start, l_firm_end,
7042: p_return_status, p_out);
7043:
7044: update msc_resource_requirements
7095: fetch res_req_info into l_firm_type, l_firm_start, l_firm_end,
7096: l_batch_number, l_group_sequence_id, l_group_sequence_number;
7097: close res_req_info;
7098:
7099: msc_gantt_utils.updateBatchReq(p_plan_id, p_instance_id, l_batch_number,
7100: to_date(null), to_date(null), p_firm_type, FIRM_MRR, p_return_status, p_out);
7101: else
7102: open res_inst_req_info(p_plan_id, p_instance_id, p_instance_id);
7103: fetch res_inst_req_info into l_batch_number;
7102: open res_inst_req_info(p_plan_id, p_instance_id, p_instance_id);
7103: fetch res_inst_req_info into l_batch_number;
7104: close res_inst_req_info;
7105:
7106: msc_gantt_utils.updateBatchInstReq(p_plan_id, p_instance_id, l_batch_number,
7107: to_date(null), to_date(null), p_firm_type, FIRM_MRR, p_return_status, p_out);
7108: end if; -- }
7109: end firmResourceBatchPub;
7110:
7122: begin
7123: p_return_status := 'OK';
7124:
7125: -- update requirement's supply
7126: msc_gantt_utils.updateSupplies(p_plan_id, p_transaction_id, FIRM_SUPPLY, p_firm_type);
7127:
7128: --pabram ... need to verify
7129: end firmSupplyPub;
7130:
7178: and mjor.principal_flag <> 2;
7179:
7180: cursor c_alt_res_inst (p_plan number, p_inst number, p_trx number)is
7181: select distinct mor.alternate_number
7182: || COLON_SEPARATOR || msc_gantt_utils.getDeptResInstCode(mdri.plan_id,
7183: mdri.sr_instance_id, mdri.organization_id, mdri.department_id,
7184: mdri.resource_id, mdri.res_instance_id, mdri.serial_number),
7185: mdri.resource_id,
7186: mdri.res_instance_id,
7204: and mor.sr_instance_id = mdri.sr_instance_id
7205: and mor.resource_id = mdri.resource_id
7206: union all --5478582 bugfix
7207: select distinct mjor.alternate_num
7208: || COLON_SEPARATOR || msc_gantt_utils.getDeptResInstCode(mdri.plan_id,
7209: mdri.sr_instance_id, mdri.organization_id, mdri.department_id,
7210: mdri.resource_id, mdri.res_instance_id, mdri.serial_number),
7211: mdri.resource_id,
7212: mdri.res_instance_id,
7277: and mjor.principal_flag <> 2;
7278:
7279: cursor c_alt_inst_inst (p_plan number, p_inst number, p_trx number,
7280: p_res_instance_id number, p_serial_number varchar2, p_equipment_item_id number )is
7281: select distinct mor.alternate_number ||':'||msc_gantt_utils.getDeptResInstCode(mdri.plan_id,
7282: mdri.sr_instance_id, mdri.organization_id, mdri.department_id,
7283: mdri.resource_id, mdri.res_instance_id, mdri.serial_number),
7284: mdri.resource_id,
7285: mdri.res_instance_id,
7301: and mor.plan_id = mdri.plan_id
7302: and mor.sr_instance_id = mdri.sr_instance_id
7303: and mor.resource_id = mdri.resource_id
7304: union all --5513960 bugfix
7305: select distinct mrr.alternate_num ||':'||msc_gantt_utils.getDeptResInstCode(mdri.plan_id,
7306: mdri.sr_instance_id, mdri.organization_id, mdri.department_id,
7307: mdri.resource_id, mdri.res_instance_id, mdri.serial_number),
7308: mdri.resource_id,
7309: mdri.res_instance_id,
7321: and mrr.resource_id = mdri.resource_id
7322: and nvl(mdri.serial_number,mbp_null_value_char) <> nvl(p_serial_number,mbp_null_value_char)
7323: --and nvl(mdri.equipment_item_id,mbp_null_value) <> nvl(p_equipment_item_id,mbp_null_value)
7324: union all --5478582 bugfix
7325: select distinct mjor.alternate_num ||':'||msc_gantt_utils.getDeptResInstCode(mdri.plan_id,
7326: mdri.sr_instance_id, mdri.organization_id, mdri.department_id,
7327: mdri.resource_id, mdri.res_instance_id, mdri.serial_number),
7328: mdri.resource_id,
7329: mdri.res_instance_id,
7623: and mor.alternate_number = mrr.alternate_num
7624: and mor.resource_id <> mrr.resource_id;
7625:
7626: cursor c_simu_res_inst(p_plan number, p_inst number, p_trx number)is
7627: select mor.alternate_number ||':'||msc_gantt_utils.getDeptResInstCode(mdri.plan_id,
7628: mdri.sr_instance_id, mdri.organization_id, mdri.department_id,
7629: mdri.resource_id, mdri.res_instance_id, mdri.serial_number) resource_code,
7630: mdri.resource_id,
7631: mdri.res_instance_id,
7666: and mor.alternate_number = mrr.alternate_num
7667: and mor.resource_id <> mrr.resource_id;
7668:
7669: cursor c_simu_inst_inst (p_plan number, p_inst number, p_trx number)is
7670: select mor.alternate_number ||':'||msc_gantt_utils.getDeptResInstCode(mdri.plan_id,
7671: mdri.sr_instance_id, mdri.organization_id,
7672: mdri.department_id, mdri.resource_id, mdri.res_instance_id, mdri.serial_number) resource_code,
7673: mdri.resource_id,
7674: mdri.res_instance_id,
7815: return;
7816: end;
7817:
7818: -- now update
7819: msc_gantt_utils.updateSupplies(p_plan_id, p_supply_id,
7820: FIRM_ALL_SUPPLY, SYS_YES, p_end_date, l_quan);
7821:
7822: p_return_status := 'OK';
7823:
7843: if ( p_node_type = RES_NODE ) then -- {
7844: ll_res_trx_id := p_transaction_id;
7845:
7846: -- lock the mrr and getNewData
7847: msc_gantt_utils.lockReqNGetData(p_plan_id, p_instance_id, ll_res_trx_id,
7848: l_firm_flag, l_supply_id, l_start, l_end, l_firm_start, l_firm_end,
7849: p_return_status, p_out);
7850:
7851: l_start := p_start_date;
7851: l_start := p_start_date;
7852: l_end := p_end_date;
7853:
7854: -- update requirement
7855: msc_gantt_utils.updateReq(p_plan_id, p_instance_id, ll_res_trx_id,
7856: l_firm_flag, l_start, l_end, l_firm_start, l_firm_end, MOVE_MRR);
7857:
7858: -- update requirement's supply
7859: msc_gantt_utils.updateSupplies(p_plan_id, l_supply_id, TOUCH_SUPPLY);
7855: msc_gantt_utils.updateReq(p_plan_id, p_instance_id, ll_res_trx_id,
7856: l_firm_flag, l_start, l_end, l_firm_start, l_firm_end, MOVE_MRR);
7857:
7858: -- update requirement's supply
7859: msc_gantt_utils.updateSupplies(p_plan_id, l_supply_id, TOUCH_SUPPLY);
7860:
7861: else
7862: ll_res_inst_trx_id := p_transaction_id;
7863:
7861: else
7862: ll_res_inst_trx_id := p_transaction_id;
7863:
7864: -- lock the mrr and getNewData
7865: msc_gantt_utils.lockReqInstNGetData(p_plan_id, p_instance_id, ll_res_inst_trx_id,
7866: l_supply_id, l_start, l_end, p_return_status, p_out);
7867:
7868: l_start := p_start_date;
7869: l_end := p_end_date;
7868: l_start := p_start_date;
7869: l_end := p_end_date;
7870:
7871: -- update requirement
7872: msc_gantt_utils.updateReqInst(p_plan_id, p_instance_id, ll_res_inst_trx_id, l_start, l_end);
7873:
7874: -- update requirement's supply
7875: msc_gantt_utils.updateSupplies(p_plan_id, l_supply_id, TOUCH_SUPPLY);
7876:
7871: -- update requirement
7872: msc_gantt_utils.updateReqInst(p_plan_id, p_instance_id, ll_res_inst_trx_id, l_start, l_end);
7873:
7874: -- update requirement's supply
7875: msc_gantt_utils.updateSupplies(p_plan_id, l_supply_id, TOUCH_SUPPLY);
7876:
7877: end if; -- }
7878:
7879: -- when the move is made, no need to update the firm dates...
7946: fetch res_req_info into l_firm_flag, l_firm_start, l_firm_end,
7947: l_batch_number, l_group_sequence_id, l_group_sequence_number;
7948: close res_req_info;
7949:
7950: msc_gantt_utils.updateBatchReq(p_plan_id, p_instance_id, l_batch_number,
7951: p_start_date, p_end_date, l_firm_flag, MOVE_MRR, p_return_status, p_out);
7952:
7953: else
7954:
7955: open res_inst_req_info(p_plan_id, p_instance_id, p_instance_id);
7956: fetch res_inst_req_info into l_batch_number;
7957: close res_inst_req_info;
7958:
7959: msc_gantt_utils.updateBatchInstReq(p_plan_id, p_instance_id, l_batch_number,
7960: p_start_date, p_end_date, l_firm_flag, MOVE_MRR, p_return_status, p_out);
7961:
7962: end if; -- }
7963:
8005: return;
8006: end if;
8007:
8008: if (p_duration is not null) then
8009: msc_gantt_utils.updateResSeq(p_plan_id, p_instance_id,
8010: l_group_sequence_id, p_duration, p_plan_start_date, p_plan_end_date,
8011: p_return_status, p_out, p_validate_flag, p_node_type);
8012: end if;
8013:
8049:
8050: -- res/res inst firm seq move logic and batched res/res inst move logic
8051: if ( p_res_firm_seq ) then
8052: put_line(' before moveResourceSeq');
8053: msc_gantt_utils.moveResourceSeq(p_plan_id, p_transaction_id, p_instance_id,
8054: p_duration, p_plan_start_date, p_plan_end_date,
8055: p_return_status, p_out, p_validate_flag, p_node_type);
8056: return; --end this process here itself..
8057: elsif ( p_batched_res_act ) then
8055: p_return_status, p_out, p_validate_flag, p_node_type);
8056: return; --end this process here itself..
8057: elsif ( p_batched_res_act ) then
8058: put_line(' before moveResourceBatch');
8059: msc_gantt_utils.moveResourceBatch(p_plan_id, p_transaction_id, p_instance_id,
8060: p_start_date, p_end_date, p_plan_start_date, p_plan_end_date,
8061: p_return_status, p_out, p_validate_flag, p_node_type);
8062: return; --end this process here itself..
8063: end if; -- }
8100: end if;
8101:
8102: if ( p_node_type = RES_INST_NODE ) then -- {
8103: -- move res inst node
8104: msc_gantt_utils.moveOneResource(p_plan_id, ll_res_inst_trx_id, p_instance_id, p_start_date, p_end_date,
8105: p_return_status, p_out, RES_INST_NODE);
8106: if p_return_status = 'ERROR' then
8107: return;
8108: end if;
8106: if p_return_status = 'ERROR' then
8107: return;
8108: end if;
8109: -- move res node also
8110: msc_gantt_utils.moveOneResource(p_plan_id, ll_res_trx_id, p_instance_id, p_start_date, p_end_date,
8111: p_return_status, p_out, RES_NODE);
8112: if p_return_status = 'ERROR' then
8113: return;
8114: end if;
8113: return;
8114: end if;
8115: else
8116: -- move res node only
8117: msc_gantt_utils.moveOneResource(p_plan_id, ll_res_trx_id, p_instance_id, p_start_date, p_end_date,
8118: p_return_status, p_out, RES_NODE);
8119: if p_return_status = 'ERROR' then
8120: return;
8121: end if;
8119: if p_return_status = 'ERROR' then
8120: return;
8121: end if;
8122: -- move all the res instance if the resource is moved.
8123: msc_gantt_utils.moveOneResource(p_plan_id, ll_res_inst_trx_id, p_instance_id,
8124: l_start, l_end, p_return_status, p_out, RES_INST_NODE);
8125: if p_return_status = 'ERROR' then
8126: return;
8127: end if;
8127: end if;
8128: end if; -- }
8129:
8130: -- update the simultaneous resource for a given res trans id
8131: msc_gantt_utils.updateReqSimu(p_plan_id, p_instance_id, ll_res_trx_id,
8132: l_firm_flag, l_start, l_end, l_firm_start, l_firm_end, MOVE_MRR, p_return_status, p_out);
8133: -- in both the cases, res or res inst move, move the simultaneous res req ends
8134:
8135:
8167: nvl(ms.alternate_routing_designator, null_space) alternate_routing_designator,
8168: ms.organization_id org_id,
8169: nvl(to_char(msi.planning_time_fence_date, format_mask),null_space) time_fence,
8170: nvl(msc_get_name.supply_type(ms.transaction_id, p_plan_id), null_space) supply_type,
8171: decode(msc_gantt_utils.getSupplyType(ms.order_type, msi.planning_make_buy_code,
8172: ms.organization_id, ms.source_organization_id),
8173: BUY_SUPPLY, l_buy_text,
8174: TRANSFER_SUPPLY, l_transfer_text,
8175: MAKE_SUPPLY, l_make_text) item_type,
8646: rowCount number;
8647:
8648: cursor req is
8649: select
8650: to_char(msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
8651: mrr.start_date, mrr.end_date,mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied), format_mask) start_date,
8652: to_char(msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
8653: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied), format_mask) end_date,
8654: nvl(msc_get_name.job_name(mrr.supply_id, p_plan_id), to_char(mrr.supply_id)) job_name,
8648: cursor req is
8649: select
8650: to_char(msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
8651: mrr.start_date, mrr.end_date,mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied), format_mask) start_date,
8652: to_char(msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
8653: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied), format_mask) end_date,
8654: nvl(msc_get_name.job_name(mrr.supply_id, p_plan_id), to_char(mrr.supply_id)) job_name,
8655: msc_get_name.supply_type(mrr.supply_id, mrr.plan_id) supply_type,
8656: mrr.assigned_units,
8665: and nvl(mrr.firm_start_date,mrr.start_date) <= p_plan_end_date
8666: and mrr.department_id = p_dept_id
8667: and mrr.resource_id = p_res_id
8668: and to_date(p_time, format_mask)
8669: between msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
8670: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
8671: and msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
8672: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
8673: order by start_date;
8667: and mrr.resource_id = p_res_id
8668: and to_date(p_time, format_mask)
8669: between msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
8670: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
8671: and msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
8672: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
8673: order by start_date;
8674:
8675: l_req_data req%ROWTYPE;
8712: rowCount number;
8713:
8714: cursor req is
8715: select
8716: to_char(msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
8717: mrr.start_date, mrr.end_date,mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied), format_mask) start_date,
8718: to_char(msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
8719: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied), format_mask) end_date,
8720: nvl(msc_get_name.job_name(mrr.supply_id, p_plan_id), to_char(mrr.supply_id)) job_name,
8714: cursor req is
8715: select
8716: to_char(msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
8717: mrr.start_date, mrr.end_date,mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied), format_mask) start_date,
8718: to_char(msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
8719: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied), format_mask) end_date,
8720: nvl(msc_get_name.job_name(mrr.supply_id, p_plan_id), to_char(mrr.supply_id)) job_name,
8721: msc_get_name.supply_type(mrr.supply_id, mrr.plan_id) supply_type,
8722: mrr.assigned_units,
8819: end getDependencyType;
8820:
8821:
8822: procedure segmentPegging(p_query_id number, p_plan_id number,
8823: p_trx_list varchar2, p_out_data IN OUT NOCOPY msc_gantt_utils.maxCharTbl) is
8824:
8825: i number := 1;
8826:
8827: l_len number;
8868: where plan_id = p_plan
8869: and ( ( producer_sr_instance_id = p_inst and producer_trans_id = p_trx )
8870: or ( consumer_sr_instance_id = p_inst and consumer_trans_id = p_trx ) );
8871:
8872: l_producer_trans_id msc_gantt_utils.number_arr;
8873: l_producer_sr_instance_id msc_gantt_utils.number_arr;
8874: l_producer_op_seq_id msc_gantt_utils.number_arr;
8875: l_producer_op_seq_num msc_gantt_utils.number_arr;
8876: l_producer_res_seq_num msc_gantt_utils.number_arr;
8869: and ( ( producer_sr_instance_id = p_inst and producer_trans_id = p_trx )
8870: or ( consumer_sr_instance_id = p_inst and consumer_trans_id = p_trx ) );
8871:
8872: l_producer_trans_id msc_gantt_utils.number_arr;
8873: l_producer_sr_instance_id msc_gantt_utils.number_arr;
8874: l_producer_op_seq_id msc_gantt_utils.number_arr;
8875: l_producer_op_seq_num msc_gantt_utils.number_arr;
8876: l_producer_res_seq_num msc_gantt_utils.number_arr;
8877: l_consumer_trans_id msc_gantt_utils.number_arr;
8870: or ( consumer_sr_instance_id = p_inst and consumer_trans_id = p_trx ) );
8871:
8872: l_producer_trans_id msc_gantt_utils.number_arr;
8873: l_producer_sr_instance_id msc_gantt_utils.number_arr;
8874: l_producer_op_seq_id msc_gantt_utils.number_arr;
8875: l_producer_op_seq_num msc_gantt_utils.number_arr;
8876: l_producer_res_seq_num msc_gantt_utils.number_arr;
8877: l_consumer_trans_id msc_gantt_utils.number_arr;
8878: l_consumer_sr_instance_id msc_gantt_utils.number_arr;
8871:
8872: l_producer_trans_id msc_gantt_utils.number_arr;
8873: l_producer_sr_instance_id msc_gantt_utils.number_arr;
8874: l_producer_op_seq_id msc_gantt_utils.number_arr;
8875: l_producer_op_seq_num msc_gantt_utils.number_arr;
8876: l_producer_res_seq_num msc_gantt_utils.number_arr;
8877: l_consumer_trans_id msc_gantt_utils.number_arr;
8878: l_consumer_sr_instance_id msc_gantt_utils.number_arr;
8879: l_consumer_op_seq_id msc_gantt_utils.number_arr;
8872: l_producer_trans_id msc_gantt_utils.number_arr;
8873: l_producer_sr_instance_id msc_gantt_utils.number_arr;
8874: l_producer_op_seq_id msc_gantt_utils.number_arr;
8875: l_producer_op_seq_num msc_gantt_utils.number_arr;
8876: l_producer_res_seq_num msc_gantt_utils.number_arr;
8877: l_consumer_trans_id msc_gantt_utils.number_arr;
8878: l_consumer_sr_instance_id msc_gantt_utils.number_arr;
8879: l_consumer_op_seq_id msc_gantt_utils.number_arr;
8880: l_consumer_op_seq_num msc_gantt_utils.number_arr;
8873: l_producer_sr_instance_id msc_gantt_utils.number_arr;
8874: l_producer_op_seq_id msc_gantt_utils.number_arr;
8875: l_producer_op_seq_num msc_gantt_utils.number_arr;
8876: l_producer_res_seq_num msc_gantt_utils.number_arr;
8877: l_consumer_trans_id msc_gantt_utils.number_arr;
8878: l_consumer_sr_instance_id msc_gantt_utils.number_arr;
8879: l_consumer_op_seq_id msc_gantt_utils.number_arr;
8880: l_consumer_op_seq_num msc_gantt_utils.number_arr;
8881: l_consumer_res_seq_num msc_gantt_utils.number_arr;
8874: l_producer_op_seq_id msc_gantt_utils.number_arr;
8875: l_producer_op_seq_num msc_gantt_utils.number_arr;
8876: l_producer_res_seq_num msc_gantt_utils.number_arr;
8877: l_consumer_trans_id msc_gantt_utils.number_arr;
8878: l_consumer_sr_instance_id msc_gantt_utils.number_arr;
8879: l_consumer_op_seq_id msc_gantt_utils.number_arr;
8880: l_consumer_op_seq_num msc_gantt_utils.number_arr;
8881: l_consumer_res_seq_num msc_gantt_utils.number_arr;
8882: l_segment_start_date msc_gantt_utils.date_arr;
8875: l_producer_op_seq_num msc_gantt_utils.number_arr;
8876: l_producer_res_seq_num msc_gantt_utils.number_arr;
8877: l_consumer_trans_id msc_gantt_utils.number_arr;
8878: l_consumer_sr_instance_id msc_gantt_utils.number_arr;
8879: l_consumer_op_seq_id msc_gantt_utils.number_arr;
8880: l_consumer_op_seq_num msc_gantt_utils.number_arr;
8881: l_consumer_res_seq_num msc_gantt_utils.number_arr;
8882: l_segment_start_date msc_gantt_utils.date_arr;
8883: l_segment_end_date msc_gantt_utils.date_arr;
8876: l_producer_res_seq_num msc_gantt_utils.number_arr;
8877: l_consumer_trans_id msc_gantt_utils.number_arr;
8878: l_consumer_sr_instance_id msc_gantt_utils.number_arr;
8879: l_consumer_op_seq_id msc_gantt_utils.number_arr;
8880: l_consumer_op_seq_num msc_gantt_utils.number_arr;
8881: l_consumer_res_seq_num msc_gantt_utils.number_arr;
8882: l_segment_start_date msc_gantt_utils.date_arr;
8883: l_segment_end_date msc_gantt_utils.date_arr;
8884: l_segment_quantity msc_gantt_utils.number_arr;
8877: l_consumer_trans_id msc_gantt_utils.number_arr;
8878: l_consumer_sr_instance_id msc_gantt_utils.number_arr;
8879: l_consumer_op_seq_id msc_gantt_utils.number_arr;
8880: l_consumer_op_seq_num msc_gantt_utils.number_arr;
8881: l_consumer_res_seq_num msc_gantt_utils.number_arr;
8882: l_segment_start_date msc_gantt_utils.date_arr;
8883: l_segment_end_date msc_gantt_utils.date_arr;
8884: l_segment_quantity msc_gantt_utils.number_arr;
8885: l_consumer_start_date msc_gantt_utils.date_arr;
8878: l_consumer_sr_instance_id msc_gantt_utils.number_arr;
8879: l_consumer_op_seq_id msc_gantt_utils.number_arr;
8880: l_consumer_op_seq_num msc_gantt_utils.number_arr;
8881: l_consumer_res_seq_num msc_gantt_utils.number_arr;
8882: l_segment_start_date msc_gantt_utils.date_arr;
8883: l_segment_end_date msc_gantt_utils.date_arr;
8884: l_segment_quantity msc_gantt_utils.number_arr;
8885: l_consumer_start_date msc_gantt_utils.date_arr;
8886: l_consumer_end_date msc_gantt_utils.date_arr;
8879: l_consumer_op_seq_id msc_gantt_utils.number_arr;
8880: l_consumer_op_seq_num msc_gantt_utils.number_arr;
8881: l_consumer_res_seq_num msc_gantt_utils.number_arr;
8882: l_segment_start_date msc_gantt_utils.date_arr;
8883: l_segment_end_date msc_gantt_utils.date_arr;
8884: l_segment_quantity msc_gantt_utils.number_arr;
8885: l_consumer_start_date msc_gantt_utils.date_arr;
8886: l_consumer_end_date msc_gantt_utils.date_arr;
8887: l_allocation_type msc_gantt_utils.number_arr;
8880: l_consumer_op_seq_num msc_gantt_utils.number_arr;
8881: l_consumer_res_seq_num msc_gantt_utils.number_arr;
8882: l_segment_start_date msc_gantt_utils.date_arr;
8883: l_segment_end_date msc_gantt_utils.date_arr;
8884: l_segment_quantity msc_gantt_utils.number_arr;
8885: l_consumer_start_date msc_gantt_utils.date_arr;
8886: l_consumer_end_date msc_gantt_utils.date_arr;
8887: l_allocation_type msc_gantt_utils.number_arr;
8888: l_dependency_type msc_gantt_utils.number_arr;
8881: l_consumer_res_seq_num msc_gantt_utils.number_arr;
8882: l_segment_start_date msc_gantt_utils.date_arr;
8883: l_segment_end_date msc_gantt_utils.date_arr;
8884: l_segment_quantity msc_gantt_utils.number_arr;
8885: l_consumer_start_date msc_gantt_utils.date_arr;
8886: l_consumer_end_date msc_gantt_utils.date_arr;
8887: l_allocation_type msc_gantt_utils.number_arr;
8888: l_dependency_type msc_gantt_utils.number_arr;
8889: l_minimum_time_offset msc_gantt_utils.number_arr;
8882: l_segment_start_date msc_gantt_utils.date_arr;
8883: l_segment_end_date msc_gantt_utils.date_arr;
8884: l_segment_quantity msc_gantt_utils.number_arr;
8885: l_consumer_start_date msc_gantt_utils.date_arr;
8886: l_consumer_end_date msc_gantt_utils.date_arr;
8887: l_allocation_type msc_gantt_utils.number_arr;
8888: l_dependency_type msc_gantt_utils.number_arr;
8889: l_minimum_time_offset msc_gantt_utils.number_arr;
8890: l_maximum_time_offset msc_gantt_utils.number_arr;
8883: l_segment_end_date msc_gantt_utils.date_arr;
8884: l_segment_quantity msc_gantt_utils.number_arr;
8885: l_consumer_start_date msc_gantt_utils.date_arr;
8886: l_consumer_end_date msc_gantt_utils.date_arr;
8887: l_allocation_type msc_gantt_utils.number_arr;
8888: l_dependency_type msc_gantt_utils.number_arr;
8889: l_minimum_time_offset msc_gantt_utils.number_arr;
8890: l_maximum_time_offset msc_gantt_utils.number_arr;
8891: l_actual_time_offset msc_gantt_utils.number_arr;
8884: l_segment_quantity msc_gantt_utils.number_arr;
8885: l_consumer_start_date msc_gantt_utils.date_arr;
8886: l_consumer_end_date msc_gantt_utils.date_arr;
8887: l_allocation_type msc_gantt_utils.number_arr;
8888: l_dependency_type msc_gantt_utils.number_arr;
8889: l_minimum_time_offset msc_gantt_utils.number_arr;
8890: l_maximum_time_offset msc_gantt_utils.number_arr;
8891: l_actual_time_offset msc_gantt_utils.number_arr;
8892:
8885: l_consumer_start_date msc_gantt_utils.date_arr;
8886: l_consumer_end_date msc_gantt_utils.date_arr;
8887: l_allocation_type msc_gantt_utils.number_arr;
8888: l_dependency_type msc_gantt_utils.number_arr;
8889: l_minimum_time_offset msc_gantt_utils.number_arr;
8890: l_maximum_time_offset msc_gantt_utils.number_arr;
8891: l_actual_time_offset msc_gantt_utils.number_arr;
8892:
8893: begin
8886: l_consumer_end_date msc_gantt_utils.date_arr;
8887: l_allocation_type msc_gantt_utils.number_arr;
8888: l_dependency_type msc_gantt_utils.number_arr;
8889: l_minimum_time_offset msc_gantt_utils.number_arr;
8890: l_maximum_time_offset msc_gantt_utils.number_arr;
8891: l_actual_time_offset msc_gantt_utils.number_arr;
8892:
8893: begin
8894: l_len := length(p_trx_list);
8887: l_allocation_type msc_gantt_utils.number_arr;
8888: l_dependency_type msc_gantt_utils.number_arr;
8889: l_minimum_time_offset msc_gantt_utils.number_arr;
8890: l_maximum_time_offset msc_gantt_utils.number_arr;
8891: l_actual_time_offset msc_gantt_utils.number_arr;
8892:
8893: begin
8894: l_len := length(p_trx_list);
8895: while l_len > 0 loop
8929: end loop;
8930: end segmentPegging;
8931:
8932: procedure resCharges(p_query_id number, p_plan_id number,
8933: p_trx_list varchar2, p_out_data IN OUT NOCOPY msc_gantt_utils.maxCharTbl) is
8934:
8935: v_start_date msc_gantt_utils.date_arr;
8936: v_end_date msc_gantt_utils.date_arr;
8937: v_qty msc_gantt_utils.number_arr;
8931:
8932: procedure resCharges(p_query_id number, p_plan_id number,
8933: p_trx_list varchar2, p_out_data IN OUT NOCOPY msc_gantt_utils.maxCharTbl) is
8934:
8935: v_start_date msc_gantt_utils.date_arr;
8936: v_end_date msc_gantt_utils.date_arr;
8937: v_qty msc_gantt_utils.number_arr;
8938:
8939: i number := 1;
8932: procedure resCharges(p_query_id number, p_plan_id number,
8933: p_trx_list varchar2, p_out_data IN OUT NOCOPY msc_gantt_utils.maxCharTbl) is
8934:
8935: v_start_date msc_gantt_utils.date_arr;
8936: v_end_date msc_gantt_utils.date_arr;
8937: v_qty msc_gantt_utils.number_arr;
8938:
8939: i number := 1;
8940:
8933: p_trx_list varchar2, p_out_data IN OUT NOCOPY msc_gantt_utils.maxCharTbl) is
8934:
8935: v_start_date msc_gantt_utils.date_arr;
8936: v_end_date msc_gantt_utils.date_arr;
8937: v_qty msc_gantt_utils.number_arr;
8938:
8939: i number := 1;
8940:
8941: l_len number;
8981:
8982:
8983: procedure sendSupplierNames(p_query_id number,
8984: p_from_index number, p_to_index number,
8985: p_name_data IN OUT NOCOPY msc_gantt_utils.maxCharTbl,
8986: p_sort_column varchar2 default null,
8987: p_sort_order varchar2 default null) is
8988:
8989: l_node_id char_arr;
9116: end sendSupplierNames;
9117:
9118: procedure sendResourceNames(p_query_id number,
9119: p_from_index number, p_to_index number,
9120: p_name_data IN OUT NOCOPY msc_gantt_utils.maxCharTbl,
9121: p_sort_column varchar2 default null,
9122: p_sort_order varchar2 default null) is
9123:
9124: l_node_id char_arr;
9163: msc_get_name.department_code(2, res.owning_department_id,
9164: res.organization_id, res.plan_id,res.sr_instance_id)) OWN_DEPT,
9165: res.resource_code RESOURCE_CODE,
9166: msc_get_name.lookup_meaning(''BOM_RESOURCE_TYPE'',res.resource_type) RES_TYPE,
9167: msc_gantt_utils.getDeptResInstCode(res.plan_id, res.sr_instance_id, res.organization_id,
9168: res.department_id, res.resource_id, mgq.res_instance_id, mgq.serial_number) EQUIP_NUMBER
9169: from msc_department_resources res,
9170: msc_gantt_query mgq
9171: where mgq.query_id = :p_query_id
9691: end getUserPref;
9692:
9693: procedure addToOutStream(p_one_record varchar2,
9694: p_out_data_index in out nocopy number,
9695: p_out_data in out nocopy msc_gantt_utils.maxchartbl) is
9696: begin
9697: if (nvl(length(p_out_data(1)),0) = 1) then -- {
9698: p_out_data(1) := p_one_record;
9699: elsif ( nvl(length(p_out_data(p_out_data_index)),0) + length(p_one_record) < 31000 ) then
9807: end getParentLinkRec;
9808:
9809: function getResult(p_query_id number,
9810: p_from_index number, p_to_index number, p_plan_id number,
9811: p_out_data in out nocopy msc_gantt_utils.maxchartbl,
9812: p_node_level number default null,
9813: p_sort_node number default null,
9814: p_sort_column varchar2 default null,
9815: p_sort_order varchar2 default null,
9816: p_res_nodes_only varchar2 default null) return number is
9817:
9818: i number;
9819:
9820: l_row_index msc_gantt_utils.number_arr;
9821: l_item_prompt msc_gantt_utils.char_arr;
9822: l_node_type msc_gantt_utils.number_arr;
9823: l_node_path msc_gantt_utils.char_arr;
9824: l_parent_link msc_gantt_utils.char_arr;
9817:
9818: i number;
9819:
9820: l_row_index msc_gantt_utils.number_arr;
9821: l_item_prompt msc_gantt_utils.char_arr;
9822: l_node_type msc_gantt_utils.number_arr;
9823: l_node_path msc_gantt_utils.char_arr;
9824: l_parent_link msc_gantt_utils.char_arr;
9825: l_start_date msc_gantt_utils.date_arr;
9818: i number;
9819:
9820: l_row_index msc_gantt_utils.number_arr;
9821: l_item_prompt msc_gantt_utils.char_arr;
9822: l_node_type msc_gantt_utils.number_arr;
9823: l_node_path msc_gantt_utils.char_arr;
9824: l_parent_link msc_gantt_utils.char_arr;
9825: l_start_date msc_gantt_utils.date_arr;
9826: l_start_date_char msc_gantt_utils.char_arr;
9819:
9820: l_row_index msc_gantt_utils.number_arr;
9821: l_item_prompt msc_gantt_utils.char_arr;
9822: l_node_type msc_gantt_utils.number_arr;
9823: l_node_path msc_gantt_utils.char_arr;
9824: l_parent_link msc_gantt_utils.char_arr;
9825: l_start_date msc_gantt_utils.date_arr;
9826: l_start_date_char msc_gantt_utils.char_arr;
9827: l_end_date msc_gantt_utils.date_arr;
9820: l_row_index msc_gantt_utils.number_arr;
9821: l_item_prompt msc_gantt_utils.char_arr;
9822: l_node_type msc_gantt_utils.number_arr;
9823: l_node_path msc_gantt_utils.char_arr;
9824: l_parent_link msc_gantt_utils.char_arr;
9825: l_start_date msc_gantt_utils.date_arr;
9826: l_start_date_char msc_gantt_utils.char_arr;
9827: l_end_date msc_gantt_utils.date_arr;
9828: l_priority msc_gantt_utils.number_arr;
9821: l_item_prompt msc_gantt_utils.char_arr;
9822: l_node_type msc_gantt_utils.number_arr;
9823: l_node_path msc_gantt_utils.char_arr;
9824: l_parent_link msc_gantt_utils.char_arr;
9825: l_start_date msc_gantt_utils.date_arr;
9826: l_start_date_char msc_gantt_utils.char_arr;
9827: l_end_date msc_gantt_utils.date_arr;
9828: l_priority msc_gantt_utils.number_arr;
9829:
9822: l_node_type msc_gantt_utils.number_arr;
9823: l_node_path msc_gantt_utils.char_arr;
9824: l_parent_link msc_gantt_utils.char_arr;
9825: l_start_date msc_gantt_utils.date_arr;
9826: l_start_date_char msc_gantt_utils.char_arr;
9827: l_end_date msc_gantt_utils.date_arr;
9828: l_priority msc_gantt_utils.number_arr;
9829:
9830: l_from_setup_code char_arr;
9823: l_node_path msc_gantt_utils.char_arr;
9824: l_parent_link msc_gantt_utils.char_arr;
9825: l_start_date msc_gantt_utils.date_arr;
9826: l_start_date_char msc_gantt_utils.char_arr;
9827: l_end_date msc_gantt_utils.date_arr;
9828: l_priority msc_gantt_utils.number_arr;
9829:
9830: l_from_setup_code char_arr;
9831: l_to_setup_code char_arr;
9824: l_parent_link msc_gantt_utils.char_arr;
9825: l_start_date msc_gantt_utils.date_arr;
9826: l_start_date_char msc_gantt_utils.char_arr;
9827: l_end_date msc_gantt_utils.date_arr;
9828: l_priority msc_gantt_utils.number_arr;
9829:
9830: l_from_setup_code char_arr;
9831: l_to_setup_code char_arr;
9832: l_std_op_code char_arr;
9832: l_std_op_code char_arr;
9833: l_changeover_time char_arr;
9834: l_changeover_penalty char_arr;
9835:
9836: l_critical_flag msc_gantt_utils.number_arr;
9837: l_item_name msc_gantt_utils.char_arr;
9838: l_org_code msc_gantt_utils.char_arr;
9839: l_order_type msc_gantt_utils.char_arr;
9840: l_order_type_text msc_gantt_utils.char_arr;
9833: l_changeover_time char_arr;
9834: l_changeover_penalty char_arr;
9835:
9836: l_critical_flag msc_gantt_utils.number_arr;
9837: l_item_name msc_gantt_utils.char_arr;
9838: l_org_code msc_gantt_utils.char_arr;
9839: l_order_type msc_gantt_utils.char_arr;
9840: l_order_type_text msc_gantt_utils.char_arr;
9841: l_order_number msc_gantt_utils.char_arr;
9834: l_changeover_penalty char_arr;
9835:
9836: l_critical_flag msc_gantt_utils.number_arr;
9837: l_item_name msc_gantt_utils.char_arr;
9838: l_org_code msc_gantt_utils.char_arr;
9839: l_order_type msc_gantt_utils.char_arr;
9840: l_order_type_text msc_gantt_utils.char_arr;
9841: l_order_number msc_gantt_utils.char_arr;
9842: l_qty msc_gantt_utils.number_arr;
9835:
9836: l_critical_flag msc_gantt_utils.number_arr;
9837: l_item_name msc_gantt_utils.char_arr;
9838: l_org_code msc_gantt_utils.char_arr;
9839: l_order_type msc_gantt_utils.char_arr;
9840: l_order_type_text msc_gantt_utils.char_arr;
9841: l_order_number msc_gantt_utils.char_arr;
9842: l_qty msc_gantt_utils.number_arr;
9843: l_cepst msc_gantt_utils.date_arr;
9836: l_critical_flag msc_gantt_utils.number_arr;
9837: l_item_name msc_gantt_utils.char_arr;
9838: l_org_code msc_gantt_utils.char_arr;
9839: l_order_type msc_gantt_utils.char_arr;
9840: l_order_type_text msc_gantt_utils.char_arr;
9841: l_order_number msc_gantt_utils.char_arr;
9842: l_qty msc_gantt_utils.number_arr;
9843: l_cepst msc_gantt_utils.date_arr;
9844: l_cepct msc_gantt_utils.date_arr;
9837: l_item_name msc_gantt_utils.char_arr;
9838: l_org_code msc_gantt_utils.char_arr;
9839: l_order_type msc_gantt_utils.char_arr;
9840: l_order_type_text msc_gantt_utils.char_arr;
9841: l_order_number msc_gantt_utils.char_arr;
9842: l_qty msc_gantt_utils.number_arr;
9843: l_cepst msc_gantt_utils.date_arr;
9844: l_cepct msc_gantt_utils.date_arr;
9845: l_ulpst msc_gantt_utils.date_arr;
9838: l_org_code msc_gantt_utils.char_arr;
9839: l_order_type msc_gantt_utils.char_arr;
9840: l_order_type_text msc_gantt_utils.char_arr;
9841: l_order_number msc_gantt_utils.char_arr;
9842: l_qty msc_gantt_utils.number_arr;
9843: l_cepst msc_gantt_utils.date_arr;
9844: l_cepct msc_gantt_utils.date_arr;
9845: l_ulpst msc_gantt_utils.date_arr;
9846: l_ulpct msc_gantt_utils.date_arr;
9839: l_order_type msc_gantt_utils.char_arr;
9840: l_order_type_text msc_gantt_utils.char_arr;
9841: l_order_number msc_gantt_utils.char_arr;
9842: l_qty msc_gantt_utils.number_arr;
9843: l_cepst msc_gantt_utils.date_arr;
9844: l_cepct msc_gantt_utils.date_arr;
9845: l_ulpst msc_gantt_utils.date_arr;
9846: l_ulpct msc_gantt_utils.date_arr;
9847: l_uepst msc_gantt_utils.date_arr;
9840: l_order_type_text msc_gantt_utils.char_arr;
9841: l_order_number msc_gantt_utils.char_arr;
9842: l_qty msc_gantt_utils.number_arr;
9843: l_cepst msc_gantt_utils.date_arr;
9844: l_cepct msc_gantt_utils.date_arr;
9845: l_ulpst msc_gantt_utils.date_arr;
9846: l_ulpct msc_gantt_utils.date_arr;
9847: l_uepst msc_gantt_utils.date_arr;
9848: l_uepct msc_gantt_utils.date_arr;
9841: l_order_number msc_gantt_utils.char_arr;
9842: l_qty msc_gantt_utils.number_arr;
9843: l_cepst msc_gantt_utils.date_arr;
9844: l_cepct msc_gantt_utils.date_arr;
9845: l_ulpst msc_gantt_utils.date_arr;
9846: l_ulpct msc_gantt_utils.date_arr;
9847: l_uepst msc_gantt_utils.date_arr;
9848: l_uepct msc_gantt_utils.date_arr;
9849: l_min_start_time msc_gantt_utils.date_arr;
9842: l_qty msc_gantt_utils.number_arr;
9843: l_cepst msc_gantt_utils.date_arr;
9844: l_cepct msc_gantt_utils.date_arr;
9845: l_ulpst msc_gantt_utils.date_arr;
9846: l_ulpct msc_gantt_utils.date_arr;
9847: l_uepst msc_gantt_utils.date_arr;
9848: l_uepct msc_gantt_utils.date_arr;
9849: l_min_start_time msc_gantt_utils.date_arr;
9850: l_eacd msc_gantt_utils.date_arr;
9843: l_cepst msc_gantt_utils.date_arr;
9844: l_cepct msc_gantt_utils.date_arr;
9845: l_ulpst msc_gantt_utils.date_arr;
9846: l_ulpct msc_gantt_utils.date_arr;
9847: l_uepst msc_gantt_utils.date_arr;
9848: l_uepct msc_gantt_utils.date_arr;
9849: l_min_start_time msc_gantt_utils.date_arr;
9850: l_eacd msc_gantt_utils.date_arr;
9851: l_req_start_date msc_gantt_utils.date_arr;
9844: l_cepct msc_gantt_utils.date_arr;
9845: l_ulpst msc_gantt_utils.date_arr;
9846: l_ulpct msc_gantt_utils.date_arr;
9847: l_uepst msc_gantt_utils.date_arr;
9848: l_uepct msc_gantt_utils.date_arr;
9849: l_min_start_time msc_gantt_utils.date_arr;
9850: l_eacd msc_gantt_utils.date_arr;
9851: l_req_start_date msc_gantt_utils.date_arr;
9852: l_req_end_date msc_gantt_utils.date_arr;
9845: l_ulpst msc_gantt_utils.date_arr;
9846: l_ulpct msc_gantt_utils.date_arr;
9847: l_uepst msc_gantt_utils.date_arr;
9848: l_uepct msc_gantt_utils.date_arr;
9849: l_min_start_time msc_gantt_utils.date_arr;
9850: l_eacd msc_gantt_utils.date_arr;
9851: l_req_start_date msc_gantt_utils.date_arr;
9852: l_req_end_date msc_gantt_utils.date_arr;
9853: l_req_due_date msc_gantt_utils.date_arr;
9846: l_ulpct msc_gantt_utils.date_arr;
9847: l_uepst msc_gantt_utils.date_arr;
9848: l_uepct msc_gantt_utils.date_arr;
9849: l_min_start_time msc_gantt_utils.date_arr;
9850: l_eacd msc_gantt_utils.date_arr;
9851: l_req_start_date msc_gantt_utils.date_arr;
9852: l_req_end_date msc_gantt_utils.date_arr;
9853: l_req_due_date msc_gantt_utils.date_arr;
9854: l_item_type msc_gantt_utils.char_arr;
9847: l_uepst msc_gantt_utils.date_arr;
9848: l_uepct msc_gantt_utils.date_arr;
9849: l_min_start_time msc_gantt_utils.date_arr;
9850: l_eacd msc_gantt_utils.date_arr;
9851: l_req_start_date msc_gantt_utils.date_arr;
9852: l_req_end_date msc_gantt_utils.date_arr;
9853: l_req_due_date msc_gantt_utils.date_arr;
9854: l_item_type msc_gantt_utils.char_arr;
9855: l_res_req_type msc_gantt_utils.char_arr;
9848: l_uepct msc_gantt_utils.date_arr;
9849: l_min_start_time msc_gantt_utils.date_arr;
9850: l_eacd msc_gantt_utils.date_arr;
9851: l_req_start_date msc_gantt_utils.date_arr;
9852: l_req_end_date msc_gantt_utils.date_arr;
9853: l_req_due_date msc_gantt_utils.date_arr;
9854: l_item_type msc_gantt_utils.char_arr;
9855: l_res_req_type msc_gantt_utils.char_arr;
9856: l_supply_firm_type msc_gantt_utils.number_arr;
9849: l_min_start_time msc_gantt_utils.date_arr;
9850: l_eacd msc_gantt_utils.date_arr;
9851: l_req_start_date msc_gantt_utils.date_arr;
9852: l_req_end_date msc_gantt_utils.date_arr;
9853: l_req_due_date msc_gantt_utils.date_arr;
9854: l_item_type msc_gantt_utils.char_arr;
9855: l_res_req_type msc_gantt_utils.char_arr;
9856: l_supply_firm_type msc_gantt_utils.number_arr;
9857: l_res_firm_type msc_gantt_utils.number_arr;
9850: l_eacd msc_gantt_utils.date_arr;
9851: l_req_start_date msc_gantt_utils.date_arr;
9852: l_req_end_date msc_gantt_utils.date_arr;
9853: l_req_due_date msc_gantt_utils.date_arr;
9854: l_item_type msc_gantt_utils.char_arr;
9855: l_res_req_type msc_gantt_utils.char_arr;
9856: l_supply_firm_type msc_gantt_utils.number_arr;
9857: l_res_firm_type msc_gantt_utils.number_arr;
9858: l_status msc_gantt_utils.number_arr;
9851: l_req_start_date msc_gantt_utils.date_arr;
9852: l_req_end_date msc_gantt_utils.date_arr;
9853: l_req_due_date msc_gantt_utils.date_arr;
9854: l_item_type msc_gantt_utils.char_arr;
9855: l_res_req_type msc_gantt_utils.char_arr;
9856: l_supply_firm_type msc_gantt_utils.number_arr;
9857: l_res_firm_type msc_gantt_utils.number_arr;
9858: l_status msc_gantt_utils.number_arr;
9859: l_applied msc_gantt_utils.number_arr;
9852: l_req_end_date msc_gantt_utils.date_arr;
9853: l_req_due_date msc_gantt_utils.date_arr;
9854: l_item_type msc_gantt_utils.char_arr;
9855: l_res_req_type msc_gantt_utils.char_arr;
9856: l_supply_firm_type msc_gantt_utils.number_arr;
9857: l_res_firm_type msc_gantt_utils.number_arr;
9858: l_status msc_gantt_utils.number_arr;
9859: l_applied msc_gantt_utils.number_arr;
9860: l_supplier_id msc_gantt_utils.number_arr;
9853: l_req_due_date msc_gantt_utils.date_arr;
9854: l_item_type msc_gantt_utils.char_arr;
9855: l_res_req_type msc_gantt_utils.char_arr;
9856: l_supply_firm_type msc_gantt_utils.number_arr;
9857: l_res_firm_type msc_gantt_utils.number_arr;
9858: l_status msc_gantt_utils.number_arr;
9859: l_applied msc_gantt_utils.number_arr;
9860: l_supplier_id msc_gantt_utils.number_arr;
9861: l_supplier_site_id msc_gantt_utils.number_arr;
9854: l_item_type msc_gantt_utils.char_arr;
9855: l_res_req_type msc_gantt_utils.char_arr;
9856: l_supply_firm_type msc_gantt_utils.number_arr;
9857: l_res_firm_type msc_gantt_utils.number_arr;
9858: l_status msc_gantt_utils.number_arr;
9859: l_applied msc_gantt_utils.number_arr;
9860: l_supplier_id msc_gantt_utils.number_arr;
9861: l_supplier_site_id msc_gantt_utils.number_arr;
9862: l_item_desc msc_gantt_utils.char_arr;
9855: l_res_req_type msc_gantt_utils.char_arr;
9856: l_supply_firm_type msc_gantt_utils.number_arr;
9857: l_res_firm_type msc_gantt_utils.number_arr;
9858: l_status msc_gantt_utils.number_arr;
9859: l_applied msc_gantt_utils.number_arr;
9860: l_supplier_id msc_gantt_utils.number_arr;
9861: l_supplier_site_id msc_gantt_utils.number_arr;
9862: l_item_desc msc_gantt_utils.char_arr;
9863: l_assy_item_desc msc_gantt_utils.char_arr;
9856: l_supply_firm_type msc_gantt_utils.number_arr;
9857: l_res_firm_type msc_gantt_utils.number_arr;
9858: l_status msc_gantt_utils.number_arr;
9859: l_applied msc_gantt_utils.number_arr;
9860: l_supplier_id msc_gantt_utils.number_arr;
9861: l_supplier_site_id msc_gantt_utils.number_arr;
9862: l_item_desc msc_gantt_utils.char_arr;
9863: l_assy_item_desc msc_gantt_utils.char_arr;
9864: l_category_name msc_gantt_utils.char_arr;
9857: l_res_firm_type msc_gantt_utils.number_arr;
9858: l_status msc_gantt_utils.number_arr;
9859: l_applied msc_gantt_utils.number_arr;
9860: l_supplier_id msc_gantt_utils.number_arr;
9861: l_supplier_site_id msc_gantt_utils.number_arr;
9862: l_item_desc msc_gantt_utils.char_arr;
9863: l_assy_item_desc msc_gantt_utils.char_arr;
9864: l_category_name msc_gantt_utils.char_arr;
9865: l_prod_family msc_gantt_utils.char_arr;
9858: l_status msc_gantt_utils.number_arr;
9859: l_applied msc_gantt_utils.number_arr;
9860: l_supplier_id msc_gantt_utils.number_arr;
9861: l_supplier_site_id msc_gantt_utils.number_arr;
9862: l_item_desc msc_gantt_utils.char_arr;
9863: l_assy_item_desc msc_gantt_utils.char_arr;
9864: l_category_name msc_gantt_utils.char_arr;
9865: l_prod_family msc_gantt_utils.char_arr;
9866: l_planner_code msc_gantt_utils.char_arr;
9859: l_applied msc_gantt_utils.number_arr;
9860: l_supplier_id msc_gantt_utils.number_arr;
9861: l_supplier_site_id msc_gantt_utils.number_arr;
9862: l_item_desc msc_gantt_utils.char_arr;
9863: l_assy_item_desc msc_gantt_utils.char_arr;
9864: l_category_name msc_gantt_utils.char_arr;
9865: l_prod_family msc_gantt_utils.char_arr;
9866: l_planner_code msc_gantt_utils.char_arr;
9867: l_planning_group msc_gantt_utils.char_arr;
9860: l_supplier_id msc_gantt_utils.number_arr;
9861: l_supplier_site_id msc_gantt_utils.number_arr;
9862: l_item_desc msc_gantt_utils.char_arr;
9863: l_assy_item_desc msc_gantt_utils.char_arr;
9864: l_category_name msc_gantt_utils.char_arr;
9865: l_prod_family msc_gantt_utils.char_arr;
9866: l_planner_code msc_gantt_utils.char_arr;
9867: l_planning_group msc_gantt_utils.char_arr;
9868: l_project msc_gantt_utils.char_arr;
9861: l_supplier_site_id msc_gantt_utils.number_arr;
9862: l_item_desc msc_gantt_utils.char_arr;
9863: l_assy_item_desc msc_gantt_utils.char_arr;
9864: l_category_name msc_gantt_utils.char_arr;
9865: l_prod_family msc_gantt_utils.char_arr;
9866: l_planner_code msc_gantt_utils.char_arr;
9867: l_planning_group msc_gantt_utils.char_arr;
9868: l_project msc_gantt_utils.char_arr;
9869: l_task msc_gantt_utils.char_arr;
9862: l_item_desc msc_gantt_utils.char_arr;
9863: l_assy_item_desc msc_gantt_utils.char_arr;
9864: l_category_name msc_gantt_utils.char_arr;
9865: l_prod_family msc_gantt_utils.char_arr;
9866: l_planner_code msc_gantt_utils.char_arr;
9867: l_planning_group msc_gantt_utils.char_arr;
9868: l_project msc_gantt_utils.char_arr;
9869: l_task msc_gantt_utils.char_arr;
9870: l_sugg_ship_date msc_gantt_utils.date_arr;
9863: l_assy_item_desc msc_gantt_utils.char_arr;
9864: l_category_name msc_gantt_utils.char_arr;
9865: l_prod_family msc_gantt_utils.char_arr;
9866: l_planner_code msc_gantt_utils.char_arr;
9867: l_planning_group msc_gantt_utils.char_arr;
9868: l_project msc_gantt_utils.char_arr;
9869: l_task msc_gantt_utils.char_arr;
9870: l_sugg_ship_date msc_gantt_utils.date_arr;
9871: l_sugg_due_date msc_gantt_utils.date_arr;
9864: l_category_name msc_gantt_utils.char_arr;
9865: l_prod_family msc_gantt_utils.char_arr;
9866: l_planner_code msc_gantt_utils.char_arr;
9867: l_planning_group msc_gantt_utils.char_arr;
9868: l_project msc_gantt_utils.char_arr;
9869: l_task msc_gantt_utils.char_arr;
9870: l_sugg_ship_date msc_gantt_utils.date_arr;
9871: l_sugg_due_date msc_gantt_utils.date_arr;
9872: l_sugg_order_date msc_gantt_utils.date_arr;
9865: l_prod_family msc_gantt_utils.char_arr;
9866: l_planner_code msc_gantt_utils.char_arr;
9867: l_planning_group msc_gantt_utils.char_arr;
9868: l_project msc_gantt_utils.char_arr;
9869: l_task msc_gantt_utils.char_arr;
9870: l_sugg_ship_date msc_gantt_utils.date_arr;
9871: l_sugg_due_date msc_gantt_utils.date_arr;
9872: l_sugg_order_date msc_gantt_utils.date_arr;
9873: l_sugg_start_date msc_gantt_utils.date_arr;
9866: l_planner_code msc_gantt_utils.char_arr;
9867: l_planning_group msc_gantt_utils.char_arr;
9868: l_project msc_gantt_utils.char_arr;
9869: l_task msc_gantt_utils.char_arr;
9870: l_sugg_ship_date msc_gantt_utils.date_arr;
9871: l_sugg_due_date msc_gantt_utils.date_arr;
9872: l_sugg_order_date msc_gantt_utils.date_arr;
9873: l_sugg_start_date msc_gantt_utils.date_arr;
9874: l_sugg_dock_date msc_gantt_utils.date_arr;
9867: l_planning_group msc_gantt_utils.char_arr;
9868: l_project msc_gantt_utils.char_arr;
9869: l_task msc_gantt_utils.char_arr;
9870: l_sugg_ship_date msc_gantt_utils.date_arr;
9871: l_sugg_due_date msc_gantt_utils.date_arr;
9872: l_sugg_order_date msc_gantt_utils.date_arr;
9873: l_sugg_start_date msc_gantt_utils.date_arr;
9874: l_sugg_dock_date msc_gantt_utils.date_arr;
9875: l_ship_method msc_gantt_utils.char_arr;
9868: l_project msc_gantt_utils.char_arr;
9869: l_task msc_gantt_utils.char_arr;
9870: l_sugg_ship_date msc_gantt_utils.date_arr;
9871: l_sugg_due_date msc_gantt_utils.date_arr;
9872: l_sugg_order_date msc_gantt_utils.date_arr;
9873: l_sugg_start_date msc_gantt_utils.date_arr;
9874: l_sugg_dock_date msc_gantt_utils.date_arr;
9875: l_ship_method msc_gantt_utils.char_arr;
9876: l_transaction_id msc_gantt_utils.number_arr;
9869: l_task msc_gantt_utils.char_arr;
9870: l_sugg_ship_date msc_gantt_utils.date_arr;
9871: l_sugg_due_date msc_gantt_utils.date_arr;
9872: l_sugg_order_date msc_gantt_utils.date_arr;
9873: l_sugg_start_date msc_gantt_utils.date_arr;
9874: l_sugg_dock_date msc_gantt_utils.date_arr;
9875: l_ship_method msc_gantt_utils.char_arr;
9876: l_transaction_id msc_gantt_utils.number_arr;
9877: l_res_transaction_id msc_gantt_utils.number_arr;
9870: l_sugg_ship_date msc_gantt_utils.date_arr;
9871: l_sugg_due_date msc_gantt_utils.date_arr;
9872: l_sugg_order_date msc_gantt_utils.date_arr;
9873: l_sugg_start_date msc_gantt_utils.date_arr;
9874: l_sugg_dock_date msc_gantt_utils.date_arr;
9875: l_ship_method msc_gantt_utils.char_arr;
9876: l_transaction_id msc_gantt_utils.number_arr;
9877: l_res_transaction_id msc_gantt_utils.number_arr;
9878: l_customer msc_gantt_utils.char_arr;
9871: l_sugg_due_date msc_gantt_utils.date_arr;
9872: l_sugg_order_date msc_gantt_utils.date_arr;
9873: l_sugg_start_date msc_gantt_utils.date_arr;
9874: l_sugg_dock_date msc_gantt_utils.date_arr;
9875: l_ship_method msc_gantt_utils.char_arr;
9876: l_transaction_id msc_gantt_utils.number_arr;
9877: l_res_transaction_id msc_gantt_utils.number_arr;
9878: l_customer msc_gantt_utils.char_arr;
9879: l_customer_site msc_gantt_utils.char_arr;
9872: l_sugg_order_date msc_gantt_utils.date_arr;
9873: l_sugg_start_date msc_gantt_utils.date_arr;
9874: l_sugg_dock_date msc_gantt_utils.date_arr;
9875: l_ship_method msc_gantt_utils.char_arr;
9876: l_transaction_id msc_gantt_utils.number_arr;
9877: l_res_transaction_id msc_gantt_utils.number_arr;
9878: l_customer msc_gantt_utils.char_arr;
9879: l_customer_site msc_gantt_utils.char_arr;
9880: l_source_org msc_gantt_utils.char_arr;
9873: l_sugg_start_date msc_gantt_utils.date_arr;
9874: l_sugg_dock_date msc_gantt_utils.date_arr;
9875: l_ship_method msc_gantt_utils.char_arr;
9876: l_transaction_id msc_gantt_utils.number_arr;
9877: l_res_transaction_id msc_gantt_utils.number_arr;
9878: l_customer msc_gantt_utils.char_arr;
9879: l_customer_site msc_gantt_utils.char_arr;
9880: l_source_org msc_gantt_utils.char_arr;
9881: l_supplier msc_gantt_utils.char_arr;
9874: l_sugg_dock_date msc_gantt_utils.date_arr;
9875: l_ship_method msc_gantt_utils.char_arr;
9876: l_transaction_id msc_gantt_utils.number_arr;
9877: l_res_transaction_id msc_gantt_utils.number_arr;
9878: l_customer msc_gantt_utils.char_arr;
9879: l_customer_site msc_gantt_utils.char_arr;
9880: l_source_org msc_gantt_utils.char_arr;
9881: l_supplier msc_gantt_utils.char_arr;
9882: l_supplier_site msc_gantt_utils.char_arr;
9875: l_ship_method msc_gantt_utils.char_arr;
9876: l_transaction_id msc_gantt_utils.number_arr;
9877: l_res_transaction_id msc_gantt_utils.number_arr;
9878: l_customer msc_gantt_utils.char_arr;
9879: l_customer_site msc_gantt_utils.char_arr;
9880: l_source_org msc_gantt_utils.char_arr;
9881: l_supplier msc_gantt_utils.char_arr;
9882: l_supplier_site msc_gantt_utils.char_arr;
9883: l_sched_group msc_gantt_utils.char_arr;
9876: l_transaction_id msc_gantt_utils.number_arr;
9877: l_res_transaction_id msc_gantt_utils.number_arr;
9878: l_customer msc_gantt_utils.char_arr;
9879: l_customer_site msc_gantt_utils.char_arr;
9880: l_source_org msc_gantt_utils.char_arr;
9881: l_supplier msc_gantt_utils.char_arr;
9882: l_supplier_site msc_gantt_utils.char_arr;
9883: l_sched_group msc_gantt_utils.char_arr;
9884: l_actual_start_date msc_gantt_utils.date_arr;
9877: l_res_transaction_id msc_gantt_utils.number_arr;
9878: l_customer msc_gantt_utils.char_arr;
9879: l_customer_site msc_gantt_utils.char_arr;
9880: l_source_org msc_gantt_utils.char_arr;
9881: l_supplier msc_gantt_utils.char_arr;
9882: l_supplier_site msc_gantt_utils.char_arr;
9883: l_sched_group msc_gantt_utils.char_arr;
9884: l_actual_start_date msc_gantt_utils.date_arr;
9885:
9878: l_customer msc_gantt_utils.char_arr;
9879: l_customer_site msc_gantt_utils.char_arr;
9880: l_source_org msc_gantt_utils.char_arr;
9881: l_supplier msc_gantt_utils.char_arr;
9882: l_supplier_site msc_gantt_utils.char_arr;
9883: l_sched_group msc_gantt_utils.char_arr;
9884: l_actual_start_date msc_gantt_utils.date_arr;
9885:
9886: l_sr_instance_id msc_gantt_utils.number_arr;
9879: l_customer_site msc_gantt_utils.char_arr;
9880: l_source_org msc_gantt_utils.char_arr;
9881: l_supplier msc_gantt_utils.char_arr;
9882: l_supplier_site msc_gantt_utils.char_arr;
9883: l_sched_group msc_gantt_utils.char_arr;
9884: l_actual_start_date msc_gantt_utils.date_arr;
9885:
9886: l_sr_instance_id msc_gantt_utils.number_arr;
9887: l_organization_id msc_gantt_utils.number_arr;
9880: l_source_org msc_gantt_utils.char_arr;
9881: l_supplier msc_gantt_utils.char_arr;
9882: l_supplier_site msc_gantt_utils.char_arr;
9883: l_sched_group msc_gantt_utils.char_arr;
9884: l_actual_start_date msc_gantt_utils.date_arr;
9885:
9886: l_sr_instance_id msc_gantt_utils.number_arr;
9887: l_organization_id msc_gantt_utils.number_arr;
9888: l_inventory_item_id msc_gantt_utils.number_arr;
9882: l_supplier_site msc_gantt_utils.char_arr;
9883: l_sched_group msc_gantt_utils.char_arr;
9884: l_actual_start_date msc_gantt_utils.date_arr;
9885:
9886: l_sr_instance_id msc_gantt_utils.number_arr;
9887: l_organization_id msc_gantt_utils.number_arr;
9888: l_inventory_item_id msc_gantt_utils.number_arr;
9889: l_dept_id msc_gantt_utils.number_arr;
9890: l_res_id msc_gantt_utils.number_arr;
9883: l_sched_group msc_gantt_utils.char_arr;
9884: l_actual_start_date msc_gantt_utils.date_arr;
9885:
9886: l_sr_instance_id msc_gantt_utils.number_arr;
9887: l_organization_id msc_gantt_utils.number_arr;
9888: l_inventory_item_id msc_gantt_utils.number_arr;
9889: l_dept_id msc_gantt_utils.number_arr;
9890: l_res_id msc_gantt_utils.number_arr;
9891:
9884: l_actual_start_date msc_gantt_utils.date_arr;
9885:
9886: l_sr_instance_id msc_gantt_utils.number_arr;
9887: l_organization_id msc_gantt_utils.number_arr;
9888: l_inventory_item_id msc_gantt_utils.number_arr;
9889: l_dept_id msc_gantt_utils.number_arr;
9890: l_res_id msc_gantt_utils.number_arr;
9891:
9892: l_dept_code msc_gantt_utils.char_arr;
9885:
9886: l_sr_instance_id msc_gantt_utils.number_arr;
9887: l_organization_id msc_gantt_utils.number_arr;
9888: l_inventory_item_id msc_gantt_utils.number_arr;
9889: l_dept_id msc_gantt_utils.number_arr;
9890: l_res_id msc_gantt_utils.number_arr;
9891:
9892: l_dept_code msc_gantt_utils.char_arr;
9893: l_res_code msc_gantt_utils.char_arr;
9886: l_sr_instance_id msc_gantt_utils.number_arr;
9887: l_organization_id msc_gantt_utils.number_arr;
9888: l_inventory_item_id msc_gantt_utils.number_arr;
9889: l_dept_id msc_gantt_utils.number_arr;
9890: l_res_id msc_gantt_utils.number_arr;
9891:
9892: l_dept_code msc_gantt_utils.char_arr;
9893: l_res_code msc_gantt_utils.char_arr;
9894: l_res_desc msc_gantt_utils.char_arr;
9888: l_inventory_item_id msc_gantt_utils.number_arr;
9889: l_dept_id msc_gantt_utils.number_arr;
9890: l_res_id msc_gantt_utils.number_arr;
9891:
9892: l_dept_code msc_gantt_utils.char_arr;
9893: l_res_code msc_gantt_utils.char_arr;
9894: l_res_desc msc_gantt_utils.char_arr;
9895: l_op_seq msc_gantt_utils.char_arr;
9896: l_op_desc msc_gantt_utils.char_arr;
9889: l_dept_id msc_gantt_utils.number_arr;
9890: l_res_id msc_gantt_utils.number_arr;
9891:
9892: l_dept_code msc_gantt_utils.char_arr;
9893: l_res_code msc_gantt_utils.char_arr;
9894: l_res_desc msc_gantt_utils.char_arr;
9895: l_op_seq msc_gantt_utils.char_arr;
9896: l_op_desc msc_gantt_utils.char_arr;
9897: l_adjusted_res_hours msc_gantt_utils.number_arr;
9890: l_res_id msc_gantt_utils.number_arr;
9891:
9892: l_dept_code msc_gantt_utils.char_arr;
9893: l_res_code msc_gantt_utils.char_arr;
9894: l_res_desc msc_gantt_utils.char_arr;
9895: l_op_seq msc_gantt_utils.char_arr;
9896: l_op_desc msc_gantt_utils.char_arr;
9897: l_adjusted_res_hours msc_gantt_utils.number_arr;
9898: l_schedule_qty msc_gantt_utils.number_arr;
9891:
9892: l_dept_code msc_gantt_utils.char_arr;
9893: l_res_code msc_gantt_utils.char_arr;
9894: l_res_desc msc_gantt_utils.char_arr;
9895: l_op_seq msc_gantt_utils.char_arr;
9896: l_op_desc msc_gantt_utils.char_arr;
9897: l_adjusted_res_hours msc_gantt_utils.number_arr;
9898: l_schedule_qty msc_gantt_utils.number_arr;
9899: l_batch_number msc_gantt_utils.char_arr;
9892: l_dept_code msc_gantt_utils.char_arr;
9893: l_res_code msc_gantt_utils.char_arr;
9894: l_res_desc msc_gantt_utils.char_arr;
9895: l_op_seq msc_gantt_utils.char_arr;
9896: l_op_desc msc_gantt_utils.char_arr;
9897: l_adjusted_res_hours msc_gantt_utils.number_arr;
9898: l_schedule_qty msc_gantt_utils.number_arr;
9899: l_batch_number msc_gantt_utils.char_arr;
9900: l_assigned_units msc_gantt_utils.number_arr;
9893: l_res_code msc_gantt_utils.char_arr;
9894: l_res_desc msc_gantt_utils.char_arr;
9895: l_op_seq msc_gantt_utils.char_arr;
9896: l_op_desc msc_gantt_utils.char_arr;
9897: l_adjusted_res_hours msc_gantt_utils.number_arr;
9898: l_schedule_qty msc_gantt_utils.number_arr;
9899: l_batch_number msc_gantt_utils.char_arr;
9900: l_assigned_units msc_gantt_utils.number_arr;
9901: l_alternate_flag msc_gantt_utils.char_arr;
9894: l_res_desc msc_gantt_utils.char_arr;
9895: l_op_seq msc_gantt_utils.char_arr;
9896: l_op_desc msc_gantt_utils.char_arr;
9897: l_adjusted_res_hours msc_gantt_utils.number_arr;
9898: l_schedule_qty msc_gantt_utils.number_arr;
9899: l_batch_number msc_gantt_utils.char_arr;
9900: l_assigned_units msc_gantt_utils.number_arr;
9901: l_alternate_flag msc_gantt_utils.char_arr;
9902: l_res_seq msc_gantt_utils.char_arr;
9895: l_op_seq msc_gantt_utils.char_arr;
9896: l_op_desc msc_gantt_utils.char_arr;
9897: l_adjusted_res_hours msc_gantt_utils.number_arr;
9898: l_schedule_qty msc_gantt_utils.number_arr;
9899: l_batch_number msc_gantt_utils.char_arr;
9900: l_assigned_units msc_gantt_utils.number_arr;
9901: l_alternate_flag msc_gantt_utils.char_arr;
9902: l_res_seq msc_gantt_utils.char_arr;
9903: l_wip_status_text msc_gantt_utils.char_arr;
9896: l_op_desc msc_gantt_utils.char_arr;
9897: l_adjusted_res_hours msc_gantt_utils.number_arr;
9898: l_schedule_qty msc_gantt_utils.number_arr;
9899: l_batch_number msc_gantt_utils.char_arr;
9900: l_assigned_units msc_gantt_utils.number_arr;
9901: l_alternate_flag msc_gantt_utils.char_arr;
9902: l_res_seq msc_gantt_utils.char_arr;
9903: l_wip_status_text msc_gantt_utils.char_arr;
9904: l_setup_type_text msc_gantt_utils.char_arr;
9897: l_adjusted_res_hours msc_gantt_utils.number_arr;
9898: l_schedule_qty msc_gantt_utils.number_arr;
9899: l_batch_number msc_gantt_utils.char_arr;
9900: l_assigned_units msc_gantt_utils.number_arr;
9901: l_alternate_flag msc_gantt_utils.char_arr;
9902: l_res_seq msc_gantt_utils.char_arr;
9903: l_wip_status_text msc_gantt_utils.char_arr;
9904: l_setup_type_text msc_gantt_utils.char_arr;
9905: l_alternate_num msc_gantt_utils.char_arr;
9898: l_schedule_qty msc_gantt_utils.number_arr;
9899: l_batch_number msc_gantt_utils.char_arr;
9900: l_assigned_units msc_gantt_utils.number_arr;
9901: l_alternate_flag msc_gantt_utils.char_arr;
9902: l_res_seq msc_gantt_utils.char_arr;
9903: l_wip_status_text msc_gantt_utils.char_arr;
9904: l_setup_type_text msc_gantt_utils.char_arr;
9905: l_alternate_num msc_gantt_utils.char_arr;
9906:
9899: l_batch_number msc_gantt_utils.char_arr;
9900: l_assigned_units msc_gantt_utils.number_arr;
9901: l_alternate_flag msc_gantt_utils.char_arr;
9902: l_res_seq msc_gantt_utils.char_arr;
9903: l_wip_status_text msc_gantt_utils.char_arr;
9904: l_setup_type_text msc_gantt_utils.char_arr;
9905: l_alternate_num msc_gantt_utils.char_arr;
9906:
9907: l_demand_class msc_gantt_utils.char_arr;
9900: l_assigned_units msc_gantt_utils.number_arr;
9901: l_alternate_flag msc_gantt_utils.char_arr;
9902: l_res_seq msc_gantt_utils.char_arr;
9903: l_wip_status_text msc_gantt_utils.char_arr;
9904: l_setup_type_text msc_gantt_utils.char_arr;
9905: l_alternate_num msc_gantt_utils.char_arr;
9906:
9907: l_demand_class msc_gantt_utils.char_arr;
9908: l_material_avail_date msc_gantt_utils.date_arr;
9901: l_alternate_flag msc_gantt_utils.char_arr;
9902: l_res_seq msc_gantt_utils.char_arr;
9903: l_wip_status_text msc_gantt_utils.char_arr;
9904: l_setup_type_text msc_gantt_utils.char_arr;
9905: l_alternate_num msc_gantt_utils.char_arr;
9906:
9907: l_demand_class msc_gantt_utils.char_arr;
9908: l_material_avail_date msc_gantt_utils.date_arr;
9909: l_bar_text msc_gantt_utils.char_arr;
9903: l_wip_status_text msc_gantt_utils.char_arr;
9904: l_setup_type_text msc_gantt_utils.char_arr;
9905: l_alternate_num msc_gantt_utils.char_arr;
9906:
9907: l_demand_class msc_gantt_utils.char_arr;
9908: l_material_avail_date msc_gantt_utils.date_arr;
9909: l_bar_text msc_gantt_utils.char_arr;
9910:
9911: l_one_record varchar2(4000);
9904: l_setup_type_text msc_gantt_utils.char_arr;
9905: l_alternate_num msc_gantt_utils.char_arr;
9906:
9907: l_demand_class msc_gantt_utils.char_arr;
9908: l_material_avail_date msc_gantt_utils.date_arr;
9909: l_bar_text msc_gantt_utils.char_arr;
9910:
9911: l_one_record varchar2(4000);
9912: l_out_data_index number := 1;
9905: l_alternate_num msc_gantt_utils.char_arr;
9906:
9907: l_demand_class msc_gantt_utils.char_arr;
9908: l_material_avail_date msc_gantt_utils.date_arr;
9909: l_bar_text msc_gantt_utils.char_arr;
9910:
9911: l_one_record varchar2(4000);
9912: l_out_data_index number := 1;
9913:
10083: mgq.parent_link,
10084: mgq.critical_flag,
10085: msi.item_name ITEM,
10086: mtp.organization_code ORGANIZATION,
10087: to_char(least(to_date(msc_gantt_utils.getActualStartDate(ms.order_type, msi.planning_make_buy_code,
10088: ms.organization_id, ms.source_organization_id, ms.new_dock_date,
10089: ms.new_wip_start_date, ms.new_ship_date,ms.new_schedule_date, ms.source_supplier_id), :format_mask),
10090: nvl(ms.firm_date, ms.new_schedule_date)),:format_mask) start_date,
10091: nvl(ms.firm_date, ms.new_schedule_date) end_date,
10243: || FIELD_SEPERATOR || nvl(escapeSplChars(l_sched_group(i)),null_space)
10244: || FIELD_SEPERATOR || l_transaction_id(i)
10245: || FIELD_SEPERATOR || nvl(to_char(l_req_start_date(i),format_mask),null_space)
10246: || FIELD_SEPERATOR || nvl(to_char(l_req_end_date(i),format_mask),null_space)
10247: || FIELD_SEPERATOR || escapeSplChars(msc_gantt_utils.getJobNodeLabel(l_item_name(i), l_org_code(i),
10248: l_order_number(i), l_order_type_text(i), l_qty(i)))
10249: || FIELD_SEPERATOR || l_sr_instance_id(i)
10250: || FIELD_SEPERATOR || l_organization_id(i)
10251: || FIELD_SEPERATOR || l_inventory_item_id(i)
10292: || FIELD_SEPERATOR || l_node_type(i)
10293: || FIELD_SEPERATOR || l_node_path(i)
10294: || FIELD_SEPERATOR || nvl(l_parent_link(i), null_space)
10295: || FIELD_SEPERATOR || nvl(escapeSplChars(l_op_desc(i)), null_space)
10296: || FIELD_SEPERATOR || escapeSplChars(msc_gantt_utils.getOpNodeLabel(l_item_prompt(i), l_transaction_id(i),
10297: l_op_desc(i), p_plan_id));
10298:
10299: addToOutStream(l_one_record, l_out_data_index, p_out_data);
10300:
10310: mgq.node_path,
10311: mgq.parent_link,
10312: mdr.department_code,
10313: mdr.resource_code,
10314: msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
10315: mrr.start_date, mrr.end_date,mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) start_date,
10316: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
10317: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) end_date,
10318: ms.new_order_quantity QUANTITY,
10312: mdr.department_code,
10313: mdr.resource_code,
10314: msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
10315: mrr.start_date, mrr.end_date,mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) start_date,
10316: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
10317: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) end_date,
10318: ms.new_order_quantity QUANTITY,
10319: mrr.resource_hours adjusted_res_hours,
10320: mrr.batch_number,
10324: nvl(ms.firm_planned_type,2) supply_firm_type,
10325: nvl(mrr.firm_flag,0) res_firm_type,
10326: nvl(mrr.status, 0) status,
10327: nvl(mrr.applied, 0) applied,
10328: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) res_req_type,
10329: mrr.supply_id transaction_id,
10330: mrr.transaction_id res_transaction_id,
10331: mrr.resource_seq_num,
10332: decode(mrr.setup_id, to_number(null), null,
10329: mrr.supply_id transaction_id,
10330: mrr.transaction_id res_transaction_id,
10331: mrr.resource_seq_num,
10332: decode(mrr.setup_id, to_number(null), null,
10333: msc_gantt_utils.getSetupCode(mrr.plan_id, mrr.sr_instance_id, mrr.resource_id, mrr.setup_id)),
10334: mrr.earliest_start_date,
10335: mrr.earliest_completion_date,
10336: mrr.ulpsd,
10337: mrr.ulpcd,
10350: mi.item_name item,
10351: mi2.description assembly_item_desc,
10352: decode(mrr.resource_hours, 0, to_number(null),
10353: nvl(mrr.cummulative_quantity,ms.new_order_quantity)) schedule_qty,
10354: msc_gantt_utils.getOrderViewResNodeLabel(mrr.plan_id, mrr.sr_instance_id,
10355: mrr.transaction_id) bar_text,
10356: mrr.alternate_num,
10357: mrr.actual_start_date,
10358: mgq.critical_flag
10489: return SYS_YES;
10490: End getResult;
10491:
10492:
10493: END MSC_GANTT_UTILS;