[Home] [Help]
1: PACKAGE BODY MSC_GANTT_UTILS AS
2: /* $Header: MSCGNTUB.pls 120.67 2011/07/21 16:45:21 pabram noship $ */
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,
472: end if;
473:
474: --insert if this is a resource first
475: if ( nvl(v_res_instance_id,mbp_null_value) = mbp_null_value ) then -- {
476: l_row_found := msc_gantt_utils.isResRowInGantt(p_query_id, p_plan_id,
477: v_inst_id, v_org_id, v_dept_id, v_res_id,
478: nvl(v_res_instance_id,mbp_null_value), nvl(v_serial_number,mbp_null_value_char));
479: if (l_row_found = sys_no) then -- {
480: p_row_index := p_row_index + 1;
493: if ( nvl(v_res_instance_id,mbp_null_value) = mbp_null_value ) then --{
494: -- find res instances
495: for c_res_inst in res_inst_cur(p_plan_id, v_inst_id, v_org_id, v_dept_id, v_res_id)
496: loop -- {
497: l_row_found := msc_gantt_utils.isResRowInGantt(p_query_id, p_plan_id,
498: v_inst_id, v_org_id, c_res_inst.department_id, v_res_id,
499: nvl(c_res_inst.res_instance_id,mbp_null_value),
500: nvl(c_res_inst.serial_number,mbp_null_value_char));
501:
516: -- find res
517: for c_inst_res in inst_res_cur(p_plan_id, v_inst_id, v_org_id, v_dept_id, v_res_id,
518: v_res_instance_id, v_serial_number)
519: loop -- {
520: l_row_found := msc_gantt_utils.isResRowInGantt(p_query_id, p_plan_id,
521: v_inst_id, v_org_id, c_inst_res.department_id, v_res_id,
522: mbp_null_value, mbp_null_value_char);
523:
524: if (l_row_found = sys_no) then -- {
537: end if; -- }
538:
539: --insert if this is a res inst node last
540: if ( nvl(v_res_instance_id,mbp_null_value) <> mbp_null_value ) then --{
541: l_row_found := msc_gantt_utils.isResRowInGantt(p_query_id, p_plan_id,
542: v_inst_id, v_org_id, v_dept_id, v_res_id,
543: nvl(v_res_instance_id,mbp_null_value),
544: nvl(v_serial_number,mbp_null_value_char));
545:
839:
840: for i in 1..p_sr_instance_id.count
841: loop
842:
843: msc_gantt_utils.getBatchValues(g_plan_id, p_sr_instance_id(i), p_batch_number(i),
844: l_res_desc, l_min_capacity, l_max_capacity, l_capacity_used);
845:
846: insert into msc_gantt_dtl_query (
847: query_id, row_type, row_index, parent_id,
1973: p_segment_start_date date_arr, p_segment_end_date date_arr, p_segment_quantity number_arr,
1974: p_consumer_start_date date_arr, p_consumer_end_date date_arr, p_allocation_type number_arr,
1975: p_dependency_type number_arr, p_minimum_time_offset number_arr,
1976: p_maximum_time_offset number_arr, p_actual_time_offset number_arr,
1977: p_data_stream IN OUT NOCOPY msc_gantt_utils.maxCharTbl) is
1978:
1979: oneBigRecord maxCharTbl := maxCharTbl(0);
1980: l_data_stream maxCharTbl := maxCharTbl(0);
1981: reccount number := 0;
2061: procedure sendResReqAvailSuppStream(p_row_type number, p_row_index number,
2062: p_from_index number, p_start date_arr, p_end date_arr,
2063: p_resource_hours number_arr, p_resource_units number_arr,
2064: p_schedule_flag number_arr,
2065: p_data_stream IN OUT NOCOPY msc_gantt_utils.maxCharTbl,
2066: p_avail_qty number_arr default null, p_overload_qty number_arr default null,
2067: p_consume_qty number_arr default null, p_cum_avail_qty number_arr default null) is
2068:
2069: oneBigRecord maxCharTbl := maxCharTbl(0);
2171: p_from_setup_code char_arr, p_to_setup_code char_arr, p_std_op_code char_arr,
2172: p_changeover_time char_arr, p_changeover_penalty char_arr,
2173: p_min_capacity number_arr, p_max_capacity number_arr, p_capacity_used number_arr,
2174: p_overload_flag number_arr,
2175: p_data_stream IN OUT NOCOPY msc_gantt_utils.maxCharTbl) is
2176:
2177: oneBigRecord maxCharTbl := maxCharTbl(0);
2178: l_data_stream maxCharTbl := maxCharTbl(0);
2179: reccount number := 0;
2301: l_res_inst_batch_rows number;
2302:
2303: begin
2304:
2305: --l_res_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_no, sys_no, sys_yes);
2306: --l_res_inst_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_yes, sys_no, sys_yes);
2307: --4997096 bugfix, instead calls the below procedure
2308: msc_gantt_utils.isResRowInGantt(p_query_id, l_res_rows, l_res_inst_rows, l_res_batch_rows, l_res_inst_batch_rows);
2309:
2302:
2303: begin
2304:
2305: --l_res_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_no, sys_no, sys_yes);
2306: --l_res_inst_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_yes, sys_no, sys_yes);
2307: --4997096 bugfix, instead calls the below procedure
2308: msc_gantt_utils.isResRowInGantt(p_query_id, l_res_rows, l_res_inst_rows, l_res_batch_rows, l_res_inst_batch_rows);
2309:
2310: put_line('populateResAvailGantt: res - res inst - res batch - res inst batch : '
2304:
2305: --l_res_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_no, sys_no, sys_yes);
2306: --l_res_inst_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_yes, sys_no, sys_yes);
2307: --4997096 bugfix, instead calls the below procedure
2308: msc_gantt_utils.isResRowInGantt(p_query_id, l_res_rows, l_res_inst_rows, l_res_batch_rows, l_res_inst_batch_rows);
2309:
2310: put_line('populateResAvailGantt: res - res inst - res batch - res inst batch : '
2311: ||l_res_rows ||' - '|| l_res_inst_rows ||' - '||l_res_batch_rows ||' - '||l_res_inst_batch_rows);
2312:
2437: -- parent_id = 2 aggregated requirements
2438:
2439: cursor res_req_cur is
2440: select mgq.row_index,
2441: msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
2442: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) start_date,
2443: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
2444: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) end_date,
2445: mrr.assigned_units,
2439: cursor res_req_cur is
2440: select mgq.row_index,
2441: msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
2442: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) start_date,
2443: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
2444: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) end_date,
2445: mrr.assigned_units,
2446: mrr.resource_hours,
2447: mrr.batch_number,
2444: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) end_date,
2445: mrr.assigned_units,
2446: mrr.resource_hours,
2447: mrr.batch_number,
2448: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
2449: msc_gantt_utils.getDisplayType(p_display_type,
2450: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2451: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2452: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2445: mrr.assigned_units,
2446: mrr.resource_hours,
2447: mrr.batch_number,
2448: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
2449: msc_gantt_utils.getDisplayType(p_display_type,
2450: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2451: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2452: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2453: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2446: mrr.resource_hours,
2447: mrr.batch_number,
2448: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
2449: msc_gantt_utils.getDisplayType(p_display_type,
2450: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2451: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2452: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2453: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2454: mrr.orig_resource_seq_num, mrr.parent_seq_num),
2448: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
2449: msc_gantt_utils.getDisplayType(p_display_type,
2450: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2451: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2452: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2453: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2454: mrr.orig_resource_seq_num, mrr.parent_seq_num),
2455: mrr.firm_flag,
2456: msc_gantt_utils.isResConstraint(mrr.plan_id,
2452: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2453: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2454: mrr.orig_resource_seq_num, mrr.parent_seq_num),
2455: mrr.firm_flag,
2456: msc_gantt_utils.isResConstraint(mrr.plan_id,
2457: mrr.sr_instance_id, mrr.organization_id,
2458: ms.inventory_item_id, mrr.department_id, mrr.resource_id, mrr.supply_id),
2459: g_gantt_rh_toler_days_early,
2460: g_gantt_rh_toler_days_late) display_type
2476: and mrr.batch_number is null and mrr.end_date is not null and mrr.resource_hours > 0
2477: and nvl(mrr.parent_id, 2) = 2
2478: and nvl(mrr.status,-1) = 0
2479: and nvl(mrr.applied,-1) = 2
2480: and ( ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
2481: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
2482: between p_start_date and p_end_date
2483: )
2484: or ( msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2480: and ( ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
2481: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
2482: between p_start_date and p_end_date
2483: )
2484: or ( msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2485: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
2486: between p_start_date and p_end_date )
2487: or ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
2488: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) <= p_start_date
2483: )
2484: or ( msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2485: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
2486: between p_start_date and p_end_date )
2487: or ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
2488: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) <= p_start_date
2489: and msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2490: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) >= p_end_date )
2491: )
2485: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
2486: between p_start_date and p_end_date )
2487: or ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
2488: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) <= p_start_date
2489: and msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2490: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) >= p_end_date )
2491: )
2492: union all
2493: select mgq.row_index,
2490: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) >= p_end_date )
2491: )
2492: union all
2493: select mgq.row_index,
2494: msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
2495: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) start_date,
2496: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
2497: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) end_date,
2498: mrr.assigned_units,
2492: union all
2493: select mgq.row_index,
2494: msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
2495: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) start_date,
2496: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
2497: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) end_date,
2498: mrr.assigned_units,
2499: mrr.resource_hours,
2500: mrr.batch_number,
2497: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) end_date,
2498: mrr.assigned_units,
2499: mrr.resource_hours,
2500: mrr.batch_number,
2501: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
2502: msc_gantt_utils.getDisplayType(p_display_type,
2503: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2504: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2505: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2498: mrr.assigned_units,
2499: mrr.resource_hours,
2500: mrr.batch_number,
2501: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
2502: msc_gantt_utils.getDisplayType(p_display_type,
2503: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2504: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2505: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2506: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2499: mrr.resource_hours,
2500: mrr.batch_number,
2501: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
2502: msc_gantt_utils.getDisplayType(p_display_type,
2503: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2504: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2505: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2506: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2507: mrr.orig_resource_seq_num, mrr.parent_seq_num),
2501: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
2502: msc_gantt_utils.getDisplayType(p_display_type,
2503: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2504: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2505: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2506: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2507: mrr.orig_resource_seq_num, mrr.parent_seq_num),
2508: mrr.firm_flag,
2509: msc_gantt_utils.isResConstraint(mrr.plan_id,
2505: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2506: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2507: mrr.orig_resource_seq_num, mrr.parent_seq_num),
2508: mrr.firm_flag,
2509: msc_gantt_utils.isResConstraint(mrr.plan_id,
2510: mrr.sr_instance_id, mrr.organization_id,
2511: ms.inventory_item_id, mrr.department_id, mrr.resource_id, mrr.supply_id),
2512: g_gantt_rh_toler_days_early,
2513: g_gantt_rh_toler_days_late) display_type
2542: and nvl(mrr1.parent_id, 2) = 2
2543: and nvl(mrr1.status,-1) <> 0
2544: and nvl(mrr1.applied,-1) <> 2
2545: and nvl(mrr.parent_id,-1) = 1
2546: and ( ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
2547: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
2548: between p_start_date and p_end_date
2549: )
2550: or ( msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2546: and ( ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
2547: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
2548: between p_start_date and p_end_date
2549: )
2550: or ( msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2551: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
2552: between p_start_date and p_end_date )
2553: or ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
2554: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) <= p_start_date
2549: )
2550: or ( msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2551: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
2552: between p_start_date and p_end_date )
2553: or ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
2554: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) <= p_start_date
2555: and msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2556: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) >= p_end_date )
2557: ) ;
2551: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
2552: between p_start_date and p_end_date )
2553: or ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
2554: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) <= p_start_date
2555: and msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2556: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) >= p_end_date )
2557: ) ;
2558:
2559: cursor res_inst_req_cur is
2557: ) ;
2558:
2559: cursor res_inst_req_cur is
2560: select mgq.row_index,
2561: msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrir.start_date, mrir.end_date,
2562: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) start_date,
2563: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrir.start_date, mrir.end_date,
2564: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) end_date,
2565: 1 assigned_units,
2559: cursor res_inst_req_cur is
2560: select mgq.row_index,
2561: msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrir.start_date, mrir.end_date,
2562: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) start_date,
2563: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrir.start_date, mrir.end_date,
2564: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) end_date,
2565: 1 assigned_units,
2566: mrir.resource_instance_hours,
2567: mrr.batch_number,
2564: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) end_date,
2565: 1 assigned_units,
2566: mrir.resource_instance_hours,
2567: mrr.batch_number,
2568: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
2569: msc_gantt_utils.getDisplayType(p_display_type,
2570: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2571: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2572: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2565: 1 assigned_units,
2566: mrir.resource_instance_hours,
2567: mrr.batch_number,
2568: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
2569: msc_gantt_utils.getDisplayType(p_display_type,
2570: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2571: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2572: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2573: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2566: mrir.resource_instance_hours,
2567: mrr.batch_number,
2568: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
2569: msc_gantt_utils.getDisplayType(p_display_type,
2570: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2571: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2572: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2573: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2574: mrr.orig_resource_seq_num, mrr.parent_seq_num),
2568: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
2569: msc_gantt_utils.getDisplayType(p_display_type,
2570: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2571: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2572: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2573: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2574: mrr.orig_resource_seq_num, mrr.parent_seq_num),
2575: mrr.firm_flag,
2576: msc_gantt_utils.isResConstraint(mrr.plan_id,
2572: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2573: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2574: mrr.orig_resource_seq_num, mrr.parent_seq_num),
2575: mrr.firm_flag,
2576: msc_gantt_utils.isResConstraint(mrr.plan_id,
2577: mrr.sr_instance_id, mrr.organization_id,
2578: ms.inventory_item_id, mrr.department_id, mrr.resource_id, mrr.supply_id),
2579: g_gantt_rh_toler_days_early,
2580: g_gantt_rh_toler_days_late) display_type
2613: and mrr.batch_number is null and mrr.end_date is not null and mrr.resource_hours > 0
2614: and nvl(mrr.parent_id, 2) = 2
2615: and nvl(mrr.status,-1) = 0
2616: and nvl(mrr.applied,-1) = 2
2617: and ( ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
2618: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
2619: between p_start_date and p_end_date
2620: )
2621: or ( msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2617: and ( ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
2618: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
2619: between p_start_date and p_end_date
2620: )
2621: or ( msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2622: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
2623: between p_start_date and p_end_date )
2624: or ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
2625: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) <= p_start_date
2620: )
2621: or ( msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2622: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
2623: between p_start_date and p_end_date )
2624: or ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
2625: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) <= p_start_date
2626: and msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2627: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) >= p_end_date )
2628: )
2622: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
2623: between p_start_date and p_end_date )
2624: or ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
2625: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) <= p_start_date
2626: and msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2627: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) >= p_end_date )
2628: )
2629: union all
2630: select mgq.row_index,
2627: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) >= p_end_date )
2628: )
2629: union all
2630: select mgq.row_index,
2631: msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrir.start_date, mrir.end_date,
2632: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) start_date,
2633: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrir.start_date, mrir.end_date,
2634: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) end_date,
2635: 1 assigned_units,
2629: union all
2630: select mgq.row_index,
2631: msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrir.start_date, mrir.end_date,
2632: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) start_date,
2633: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrir.start_date, mrir.end_date,
2634: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) end_date,
2635: 1 assigned_units,
2636: mrir.resource_instance_hours,
2637: mrr.batch_number,
2634: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) end_date,
2635: 1 assigned_units,
2636: mrir.resource_instance_hours,
2637: mrr.batch_number,
2638: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
2639: msc_gantt_utils.getDisplayType(p_display_type,
2640: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2641: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2642: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2635: 1 assigned_units,
2636: mrir.resource_instance_hours,
2637: mrr.batch_number,
2638: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
2639: msc_gantt_utils.getDisplayType(p_display_type,
2640: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2641: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2642: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2643: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2636: mrir.resource_instance_hours,
2637: mrr.batch_number,
2638: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
2639: msc_gantt_utils.getDisplayType(p_display_type,
2640: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2641: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2642: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2643: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2644: mrr.orig_resource_seq_num, mrr.parent_seq_num),
2638: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
2639: msc_gantt_utils.getDisplayType(p_display_type,
2640: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2641: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2642: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2643: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2644: mrr.orig_resource_seq_num, mrr.parent_seq_num),
2645: mrr.firm_flag,
2646: msc_gantt_utils.isResConstraint(mrr.plan_id,
2642: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2643: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2644: mrr.orig_resource_seq_num, mrr.parent_seq_num),
2645: mrr.firm_flag,
2646: msc_gantt_utils.isResConstraint(mrr.plan_id,
2647: mrr.sr_instance_id, mrr.organization_id,
2648: ms.inventory_item_id, mrr.department_id, mrr.resource_id, mrr.supply_id),
2649: g_gantt_rh_toler_days_early,
2650: g_gantt_rh_toler_days_late) display_type
2696: and nvl(mrr1.parent_id, 2) = 2
2697: and nvl(mrr1.status,-1) <> 0
2698: and nvl(mrr1.applied,-1) <> 2
2699: and nvl(mrr.parent_id,-1) = 1
2700: and ( ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
2701: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
2702: between p_start_date and p_end_date
2703: )
2704: or ( msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2700: and ( ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
2701: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
2702: between p_start_date and p_end_date
2703: )
2704: or ( msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2705: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
2706: between p_start_date and p_end_date )
2707: or ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
2708: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) <= p_start_date
2703: )
2704: or ( msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2705: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
2706: between p_start_date and p_end_date )
2707: or ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
2708: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) <= p_start_date
2709: and msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2710: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) >= p_end_date )
2711: );
2705: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
2706: between p_start_date and p_end_date )
2707: or ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
2708: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) <= p_start_date
2709: and msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2710: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) >= p_end_date )
2711: );
2712:
2713: cursor res_req_btch_cur is
2711: );
2712:
2713: cursor res_req_btch_cur is
2714: select mgq.row_index,
2715: min(msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
2716: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)) start_date,
2717: max(msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrr.start_date,
2718: mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)) end_date,
2719: sum(mrr.capacity_consumed_ratio) assigned_units,
2713: cursor res_req_btch_cur is
2714: select mgq.row_index,
2715: min(msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
2716: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)) start_date,
2717: max(msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrr.start_date,
2718: mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)) end_date,
2719: sum(mrr.capacity_consumed_ratio) assigned_units,
2720: avg(mrr.resource_hours) resource_hours,
2721: mrr.batch_number,
2718: mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)) end_date,
2719: sum(mrr.capacity_consumed_ratio) assigned_units,
2720: avg(mrr.resource_hours) resource_hours,
2721: mrr.batch_number,
2722: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
2723: decode(p_display_type, DISPLAY_NONE, sys_no,
2724: msc_gantt_utils.getDisplayType(p_display_type,
2725: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2726: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2720: avg(mrr.resource_hours) resource_hours,
2721: mrr.batch_number,
2722: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
2723: decode(p_display_type, DISPLAY_NONE, sys_no,
2724: msc_gantt_utils.getDisplayType(p_display_type,
2725: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2726: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2727: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2728: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2721: mrr.batch_number,
2722: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
2723: decode(p_display_type, DISPLAY_NONE, sys_no,
2724: msc_gantt_utils.getDisplayType(p_display_type,
2725: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2726: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2727: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2728: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2729: mrr.orig_resource_seq_num, mrr.parent_seq_num),
2723: decode(p_display_type, DISPLAY_NONE, sys_no,
2724: msc_gantt_utils.getDisplayType(p_display_type,
2725: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2726: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2727: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2728: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2729: mrr.orig_resource_seq_num, mrr.parent_seq_num),
2730: mrr.firm_flag,
2731: msc_gantt_utils.isResConstraint(mrr.plan_id,
2727: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2728: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2729: mrr.orig_resource_seq_num, mrr.parent_seq_num),
2730: mrr.firm_flag,
2731: msc_gantt_utils.isResConstraint(mrr.plan_id,
2732: mrr.sr_instance_id, mrr.organization_id,
2733: ms.inventory_item_id, mrr.department_id, mrr.resource_id, mrr.supply_id),
2734: g_gantt_rh_toler_days_early,
2735: g_gantt_rh_toler_days_late)) display_type
2758: and mrr.batch_number is not null and mrr.end_date is not null and mrr.resource_hours > 0
2759: and nvl(mrr.parent_id, 2) = 2
2760: and nvl(mrr.status,-1) = 0
2761: and nvl(mrr.applied,-1) = 2
2762: and ( ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
2763: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
2764: between p_start_date and p_end_date
2765: )
2766: or ( msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2762: and ( ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
2763: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
2764: between p_start_date and p_end_date
2765: )
2766: or ( msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2767: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
2768: between p_start_date and p_end_date )
2769: or ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
2770: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) <= p_start_date
2765: )
2766: or ( msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2767: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
2768: between p_start_date and p_end_date )
2769: or ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
2770: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) <= p_start_date
2771: and msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2772: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) >= p_end_date )
2773: )
2767: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
2768: between p_start_date and p_end_date )
2769: or ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
2770: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) <= p_start_date
2771: and msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2772: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) >= p_end_date )
2773: )
2774: group by
2775: mgq.row_index,
2773: )
2774: group by
2775: mgq.row_index,
2776: mrr.batch_number,
2777: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id),
2778: msc_gantt_utils.getDisplayType(p_display_type,
2779: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2780: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2781: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2774: group by
2775: mgq.row_index,
2776: mrr.batch_number,
2777: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id),
2778: msc_gantt_utils.getDisplayType(p_display_type,
2779: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2780: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2781: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2782: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2775: mgq.row_index,
2776: mrr.batch_number,
2777: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id),
2778: msc_gantt_utils.getDisplayType(p_display_type,
2779: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2780: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2781: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2782: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2783: mrr.orig_resource_seq_num, mrr.parent_seq_num),
2777: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id),
2778: msc_gantt_utils.getDisplayType(p_display_type,
2779: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2780: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2781: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2782: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2783: mrr.orig_resource_seq_num, mrr.parent_seq_num),
2784: mrr.firm_flag,
2785: msc_gantt_utils.isResConstraint(mrr.plan_id,
2781: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2782: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2783: mrr.orig_resource_seq_num, mrr.parent_seq_num),
2784: mrr.firm_flag,
2785: msc_gantt_utils.isResConstraint(mrr.plan_id,
2786: mrr.sr_instance_id, mrr.organization_id,
2787: ms.inventory_item_id, mrr.department_id, mrr.resource_id, mrr.supply_id),
2788: g_gantt_rh_toler_days_early,
2789: g_gantt_rh_toler_days_late)
2788: g_gantt_rh_toler_days_early,
2789: g_gantt_rh_toler_days_late)
2790: union all
2791: select mgq.row_index,
2792: msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
2793: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) start_date,
2794: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrr.start_date,
2795: mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) end_date,
2796: sum(mrr.capacity_consumed_ratio) assigned_units,
2790: union all
2791: select mgq.row_index,
2792: msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
2793: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) start_date,
2794: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrr.start_date,
2795: mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) end_date,
2796: sum(mrr.capacity_consumed_ratio) assigned_units,
2797: avg(mrr.resource_hours) resource_hours,
2798: mrr.batch_number,
2795: mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) end_date,
2796: sum(mrr.capacity_consumed_ratio) assigned_units,
2797: avg(mrr.resource_hours) resource_hours,
2798: mrr.batch_number,
2799: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
2800: decode(p_display_type, DISPLAY_NONE, sys_no,
2801: msc_gantt_utils.getDisplayType(p_display_type,
2802: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2803: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2797: avg(mrr.resource_hours) resource_hours,
2798: mrr.batch_number,
2799: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
2800: decode(p_display_type, DISPLAY_NONE, sys_no,
2801: msc_gantt_utils.getDisplayType(p_display_type,
2802: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2803: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2804: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2805: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2798: mrr.batch_number,
2799: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
2800: decode(p_display_type, DISPLAY_NONE, sys_no,
2801: msc_gantt_utils.getDisplayType(p_display_type,
2802: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2803: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2804: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2805: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2806: mrr.orig_resource_seq_num, mrr.parent_seq_num),
2800: decode(p_display_type, DISPLAY_NONE, sys_no,
2801: msc_gantt_utils.getDisplayType(p_display_type,
2802: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2803: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2804: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2805: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2806: mrr.orig_resource_seq_num, mrr.parent_seq_num),
2807: mrr.firm_flag,
2808: msc_gantt_utils.isResConstraint(mrr.plan_id,
2804: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2805: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2806: mrr.orig_resource_seq_num, mrr.parent_seq_num),
2807: mrr.firm_flag,
2808: msc_gantt_utils.isResConstraint(mrr.plan_id,
2809: mrr.sr_instance_id, mrr.organization_id,
2810: ms.inventory_item_id, mrr.department_id, mrr.resource_id, mrr.supply_id),
2811: g_gantt_rh_toler_days_early,
2812: g_gantt_rh_toler_days_late)) display_type
2848: and nvl(mrr1.parent_id, 2) = 2
2849: and nvl(mrr1.status,-1) <> 0
2850: and nvl(mrr1.applied,-1) <> 2
2851: and nvl(mrr.parent_id,-1) = 1
2852: and ( ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
2853: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
2854: between p_start_date and p_end_date
2855: )
2856: or ( msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2852: and ( ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
2853: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
2854: between p_start_date and p_end_date
2855: )
2856: or ( msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2857: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
2858: between p_start_date and p_end_date )
2859: or ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
2860: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) <= p_start_date
2855: )
2856: or ( msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2857: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
2858: between p_start_date and p_end_date )
2859: or ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
2860: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) <= p_start_date
2861: and msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2862: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) >= p_end_date )
2863: )
2857: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
2858: between p_start_date and p_end_date )
2859: or ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
2860: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) <= p_start_date
2861: and msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2862: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) >= p_end_date )
2863: )
2864: group by
2865: mgq.row_index,
2862: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) >= p_end_date )
2863: )
2864: group by
2865: mgq.row_index,
2866: msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
2867: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2868: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrr.start_date,
2869: mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2870: mrr.batch_number,
2864: group by
2865: mgq.row_index,
2866: msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
2867: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2868: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrr.start_date,
2869: mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2870: mrr.batch_number,
2871: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id),
2872: msc_gantt_utils.getDisplayType(p_display_type,
2867: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2868: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrr.start_date,
2869: mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2870: mrr.batch_number,
2871: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id),
2872: msc_gantt_utils.getDisplayType(p_display_type,
2873: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2874: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2875: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2868: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrr.start_date,
2869: mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2870: mrr.batch_number,
2871: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id),
2872: msc_gantt_utils.getDisplayType(p_display_type,
2873: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2874: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2875: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2876: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2869: mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2870: mrr.batch_number,
2871: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id),
2872: msc_gantt_utils.getDisplayType(p_display_type,
2873: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2874: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2875: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2876: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2877: mrr.orig_resource_seq_num, mrr.parent_seq_num),
2871: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id),
2872: msc_gantt_utils.getDisplayType(p_display_type,
2873: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2874: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2875: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2876: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2877: mrr.orig_resource_seq_num, mrr.parent_seq_num),
2878: mrr.firm_flag,
2879: msc_gantt_utils.isResConstraint(mrr.plan_id,
2875: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2876: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2877: mrr.orig_resource_seq_num, mrr.parent_seq_num),
2878: mrr.firm_flag,
2879: msc_gantt_utils.isResConstraint(mrr.plan_id,
2880: mrr.sr_instance_id, mrr.organization_id,
2881: ms.inventory_item_id, mrr.department_id, mrr.resource_id, mrr.supply_id),
2882: g_gantt_rh_toler_days_early,
2883: g_gantt_rh_toler_days_late);
2883: g_gantt_rh_toler_days_late);
2884:
2885: cursor res_inst_req_btch_cur is
2886: select mgq.row_index,
2887: min(msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrir.start_date, mrir.end_date,
2888: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)) start_date,
2889: max(msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrir.start_date,
2890: mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)) end_date,
2891: sum(mrir.capacity_consumed_ratio) assigned_units,
2885: cursor res_inst_req_btch_cur is
2886: select mgq.row_index,
2887: min(msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrir.start_date, mrir.end_date,
2888: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)) start_date,
2889: max(msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrir.start_date,
2890: mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)) end_date,
2891: sum(mrir.capacity_consumed_ratio) assigned_units,
2892: avg(mrir.resource_instance_hours) resource_hours,
2893: mrir.batch_number,
2890: mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)) end_date,
2891: sum(mrir.capacity_consumed_ratio) assigned_units,
2892: avg(mrir.resource_instance_hours) resource_hours,
2893: mrir.batch_number,
2894: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
2895: decode(p_display_type, DISPLAY_NONE, sys_no,
2896: msc_gantt_utils.getDisplayType(p_display_type,
2897: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2898: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2892: avg(mrir.resource_instance_hours) resource_hours,
2893: mrir.batch_number,
2894: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
2895: decode(p_display_type, DISPLAY_NONE, sys_no,
2896: msc_gantt_utils.getDisplayType(p_display_type,
2897: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2898: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2899: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2900: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2893: mrir.batch_number,
2894: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
2895: decode(p_display_type, DISPLAY_NONE, sys_no,
2896: msc_gantt_utils.getDisplayType(p_display_type,
2897: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2898: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2899: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2900: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2901: mrr.orig_resource_seq_num, mrr.parent_seq_num),
2895: decode(p_display_type, DISPLAY_NONE, sys_no,
2896: msc_gantt_utils.getDisplayType(p_display_type,
2897: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2898: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2899: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2900: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2901: mrr.orig_resource_seq_num, mrr.parent_seq_num),
2902: mrr.firm_flag,
2903: msc_gantt_utils.isResConstraint(mrr.plan_id,
2899: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2900: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2901: mrr.orig_resource_seq_num, mrr.parent_seq_num),
2902: mrr.firm_flag,
2903: msc_gantt_utils.isResConstraint(mrr.plan_id,
2904: mrr.sr_instance_id, mrr.organization_id,
2905: ms.inventory_item_id, mrr.department_id, mrr.resource_id, mrr.supply_id),
2906: g_gantt_rh_toler_days_early,
2907: g_gantt_rh_toler_days_late)) display_type
2947: and mrr.batch_number is not null and mrr.end_date is not null and mrr.resource_hours > 0
2948: and nvl(mrr.parent_id, 2) = 2
2949: and nvl(mrr.status,-1) = 0
2950: and nvl(mrr.applied,-1) = 2
2951: and ( ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
2952: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
2953: between p_start_date and p_end_date
2954: )
2955: or ( msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2951: and ( ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
2952: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
2953: between p_start_date and p_end_date
2954: )
2955: or ( 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)
2957: between p_start_date and p_end_date )
2958: or ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
2959: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) <= p_start_date
2954: )
2955: or ( 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)
2957: between p_start_date and p_end_date )
2958: or ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
2959: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) <= p_start_date
2960: and msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2961: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) >= p_end_date )
2962: )
2956: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
2957: between p_start_date and p_end_date )
2958: or ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
2959: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) <= p_start_date
2960: and msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2961: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) >= p_end_date )
2962: )
2963: group by
2964: mgq.row_index,
2962: )
2963: group by
2964: mgq.row_index,
2965: mrir.batch_number,
2966: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id),
2967: msc_gantt_utils.getDisplayType(p_display_type,
2968: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2969: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2970: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2963: group by
2964: mgq.row_index,
2965: mrir.batch_number,
2966: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id),
2967: msc_gantt_utils.getDisplayType(p_display_type,
2968: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2969: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2970: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2971: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2964: mgq.row_index,
2965: mrir.batch_number,
2966: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id),
2967: msc_gantt_utils.getDisplayType(p_display_type,
2968: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2969: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2970: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2971: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2972: mrr.orig_resource_seq_num, mrr.parent_seq_num),
2966: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id),
2967: msc_gantt_utils.getDisplayType(p_display_type,
2968: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2969: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2970: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2971: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2972: mrr.orig_resource_seq_num, mrr.parent_seq_num),
2973: mrr.firm_flag,
2974: msc_gantt_utils.isResConstraint(mrr.plan_id,
2970: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2971: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2972: mrr.orig_resource_seq_num, mrr.parent_seq_num),
2973: mrr.firm_flag,
2974: msc_gantt_utils.isResConstraint(mrr.plan_id,
2975: mrr.sr_instance_id, mrr.organization_id,
2976: ms.inventory_item_id, mrr.department_id, mrr.resource_id, mrr.supply_id),
2977: g_gantt_rh_toler_days_early,
2978: g_gantt_rh_toler_days_late)
2977: g_gantt_rh_toler_days_early,
2978: g_gantt_rh_toler_days_late)
2979: union all
2980: select mgq.row_index,
2981: min(msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrir.start_date, mrir.end_date,
2982: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)) start_date,
2983: max(msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrir.start_date,
2984: mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)) end_date,
2985: sum(mrir.capacity_consumed_ratio) assigned_units,
2979: union all
2980: select mgq.row_index,
2981: min(msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrir.start_date, mrir.end_date,
2982: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)) start_date,
2983: max(msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrir.start_date,
2984: mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)) end_date,
2985: sum(mrir.capacity_consumed_ratio) assigned_units,
2986: avg(mrir.resource_instance_hours) resource_hours,
2987: mrir.batch_number,
2984: mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)) end_date,
2985: sum(mrir.capacity_consumed_ratio) assigned_units,
2986: avg(mrir.resource_instance_hours) resource_hours,
2987: mrir.batch_number,
2988: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
2989: decode(p_display_type, DISPLAY_NONE, sys_no,
2990: msc_gantt_utils.getDisplayType(p_display_type,
2991: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2992: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2986: avg(mrir.resource_instance_hours) resource_hours,
2987: mrir.batch_number,
2988: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
2989: decode(p_display_type, DISPLAY_NONE, sys_no,
2990: msc_gantt_utils.getDisplayType(p_display_type,
2991: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2992: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2993: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2994: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2987: mrir.batch_number,
2988: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
2989: decode(p_display_type, DISPLAY_NONE, sys_no,
2990: msc_gantt_utils.getDisplayType(p_display_type,
2991: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2992: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2993: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2994: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2995: mrr.orig_resource_seq_num, mrr.parent_seq_num),
2989: decode(p_display_type, DISPLAY_NONE, sys_no,
2990: msc_gantt_utils.getDisplayType(p_display_type,
2991: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
2992: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
2993: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2994: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2995: mrr.orig_resource_seq_num, mrr.parent_seq_num),
2996: mrr.firm_flag,
2997: msc_gantt_utils.isResConstraint(mrr.plan_id,
2993: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
2994: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
2995: mrr.orig_resource_seq_num, mrr.parent_seq_num),
2996: mrr.firm_flag,
2997: msc_gantt_utils.isResConstraint(mrr.plan_id,
2998: mrr.sr_instance_id, mrr.organization_id,
2999: ms.inventory_item_id, mrr.department_id, mrr.resource_id, mrr.supply_id),
3000: g_gantt_rh_toler_days_early,
3001: g_gantt_rh_toler_days_late)) display_type
3054: and nvl(mrr1.parent_id, 2) = 2
3055: and nvl(mrr1.status,-1) <> 0
3056: and nvl(mrr1.applied,-1) <> 2
3057: and nvl(mrr.parent_id,-1) = 1
3058: and ( ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
3059: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
3060: between p_start_date and p_end_date
3061: )
3062: or ( msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
3058: and ( ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
3059: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
3060: between p_start_date and p_end_date
3061: )
3062: or ( 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)
3064: between p_start_date and p_end_date )
3065: or ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
3066: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) <= p_start_date
3061: )
3062: or ( 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)
3064: between p_start_date and p_end_date )
3065: or ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
3066: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) <= p_start_date
3067: and msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
3068: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) >= p_end_date )
3069: )
3063: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
3064: between p_start_date and p_end_date )
3065: or ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
3066: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) <= p_start_date
3067: and msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
3068: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) >= p_end_date )
3069: )
3070: group by
3071: mgq.row_index,
3069: )
3070: group by
3071: mgq.row_index,
3072: mrir.batch_number,
3073: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id),
3074: msc_gantt_utils.getDisplayType(p_display_type,
3075: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
3076: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
3077: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
3070: group by
3071: mgq.row_index,
3072: mrir.batch_number,
3073: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id),
3074: msc_gantt_utils.getDisplayType(p_display_type,
3075: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
3076: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
3077: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
3078: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
3071: mgq.row_index,
3072: mrir.batch_number,
3073: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id),
3074: msc_gantt_utils.getDisplayType(p_display_type,
3075: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
3076: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
3077: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
3078: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
3079: mrr.orig_resource_seq_num, mrr.parent_seq_num),
3073: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id),
3074: msc_gantt_utils.getDisplayType(p_display_type,
3075: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
3076: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
3077: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
3078: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
3079: mrr.orig_resource_seq_num, mrr.parent_seq_num),
3080: mrr.firm_flag,
3081: msc_gantt_utils.isResConstraint(mrr.plan_id,
3077: msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
3078: mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
3079: mrr.orig_resource_seq_num, mrr.parent_seq_num),
3080: mrr.firm_flag,
3081: msc_gantt_utils.isResConstraint(mrr.plan_id,
3082: mrr.sr_instance_id, mrr.organization_id,
3083: ms.inventory_item_id, mrr.department_id, mrr.resource_id, mrr.supply_id),
3084: g_gantt_rh_toler_days_early,
3085: g_gantt_rh_toler_days_late);
3090: l_res_inst_batch_rows number;
3091:
3092: begin
3093:
3094: --l_res_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_no, sys_no, sys_yes);
3095: --l_res_inst_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_yes, sys_no, sys_yes);
3096: --l_res_batch_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_no, sys_yes);
3097: --l_res_inst_batch_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_yes, sys_yes);
3098: --4997096 bugfix, instead calls the below procedure
3091:
3092: begin
3093:
3094: --l_res_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_no, sys_no, sys_yes);
3095: --l_res_inst_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_yes, sys_no, sys_yes);
3096: --l_res_batch_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_no, sys_yes);
3097: --l_res_inst_batch_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_yes, sys_yes);
3098: --4997096 bugfix, instead calls the below procedure
3099: msc_gantt_utils.isResRowInGantt(p_query_id, l_res_rows, l_res_inst_rows, l_res_batch_rows, l_res_inst_batch_rows);
3092: begin
3093:
3094: --l_res_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_no, sys_no, sys_yes);
3095: --l_res_inst_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_yes, sys_no, sys_yes);
3096: --l_res_batch_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_no, sys_yes);
3097: --l_res_inst_batch_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_yes, sys_yes);
3098: --4997096 bugfix, instead calls the below procedure
3099: msc_gantt_utils.isResRowInGantt(p_query_id, l_res_rows, l_res_inst_rows, l_res_batch_rows, l_res_inst_batch_rows);
3100:
3093:
3094: --l_res_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_no, sys_no, sys_yes);
3095: --l_res_inst_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_yes, sys_no, sys_yes);
3096: --l_res_batch_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_no, sys_yes);
3097: --l_res_inst_batch_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_yes, sys_yes);
3098: --4997096 bugfix, instead calls the below procedure
3099: msc_gantt_utils.isResRowInGantt(p_query_id, l_res_rows, l_res_inst_rows, l_res_batch_rows, l_res_inst_batch_rows);
3100:
3101: put_line('populateResReqGanttNew: res - res inst - res batch - res inst batch: '||
3095: --l_res_inst_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_yes, sys_no, sys_yes);
3096: --l_res_batch_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_no, sys_yes);
3097: --l_res_inst_batch_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_yes, sys_yes);
3098: --4997096 bugfix, instead calls the below procedure
3099: msc_gantt_utils.isResRowInGantt(p_query_id, l_res_rows, l_res_inst_rows, l_res_batch_rows, l_res_inst_batch_rows);
3100:
3101: put_line('populateResReqGanttNew: res - res inst - res batch - res inst batch: '||
3102: l_res_rows ||' - '|| l_res_inst_rows ||' - '|| l_res_batch_rows
3103: ||' - '|| l_res_inst_batch_rows);
3201:
3202: procedure populateResActGantt(p_query_id number,
3203: p_start_date date, p_end_date date,
3204: p_batched_res_act number,
3205: p_require_data IN OUT NOCOPY msc_gantt_utils.maxCharTbl,
3206: p_display_type number) is
3207:
3208: cursor res_act_cur is
3209: select mgq.row_index,
3214: nvl(mrr.status,0) status,
3215: nvl(mrr.applied,0) applied,
3216: nvl(mrr.firm_flag,0) res_firm_flag,
3217: ms.firm_planned_type sup_firm_flag,
3218: msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
3219: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) start_date,
3220: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
3221: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) end_date,
3222: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
3216: nvl(mrr.firm_flag,0) res_firm_flag,
3217: ms.firm_planned_type sup_firm_flag,
3218: msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
3219: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) start_date,
3220: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
3221: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) end_date,
3222: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
3223: msc_gantt_utils.isResConstraint(mrr.plan_id, mrr.sr_instance_id,
3224: mrr.organization_id, ms.inventory_item_id,
3218: msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
3219: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) start_date,
3220: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
3221: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) end_date,
3222: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
3223: msc_gantt_utils.isResConstraint(mrr.plan_id, mrr.sr_instance_id,
3224: mrr.organization_id, ms.inventory_item_id,
3225: mrr.department_id, mrr.resource_id, mrr.supply_id) res_constraint,
3226: ms.new_order_quantity qty,
3219: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) start_date,
3220: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
3221: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) end_date,
3222: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
3223: msc_gantt_utils.isResConstraint(mrr.plan_id, mrr.sr_instance_id,
3224: mrr.organization_id, ms.inventory_item_id,
3225: mrr.department_id, mrr.resource_id, mrr.supply_id) res_constraint,
3226: ms.new_order_quantity qty,
3227: nvl(mrr.batch_number, mbp_null_value),
3234: mrr.ulpcd,
3235: mrr.uepsd,
3236: mrr.uepcd,
3237: mrr.eacd,
3238: msc_gantt_utils.getResActResNodeLabel(mrr.plan_id, mrr.sr_instance_id, mrr.transaction_id) bar_text,
3239: ms.inventory_item_id,
3240: msc_get_name.supply_order_number(ms.order_type, ms.order_number, ms.plan_id,
3241: ms.sr_instance_id, ms.transaction_id, ms.disposition_id) order_number,
3242: mrr.operation_seq_num,
3255: msc_get_name.setup_code(mrr.plan_id, mrr.sr_instance_id, mrr.resource_id,
3256: mrr.organization_id, mrr.setup_id)) to_setup_code,
3257: decode(mrr.from_setup_id,
3258: to_number(null), null,
3259: msc_gantt_utils.getTansitionValue(mrr.plan_id, mrr.sr_instance_id,
3260: mrr.organization_id, mrr.department_id, mrr.resource_id, mrr.from_setup_id,
3261: mrr.setup_id, 'STANDARD_OPERATION_CODE')) std_op_code,
3262: decode(mrr.from_setup_id,
3263: to_number(null), null,
3260: mrr.organization_id, mrr.department_id, mrr.resource_id, mrr.from_setup_id,
3261: mrr.setup_id, 'STANDARD_OPERATION_CODE')) std_op_code,
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_TIME')) changeover_time,
3267: decode(mrr.from_setup_id,
3268: to_number(null), null,
3265: mrr.department_id, mrr.resource_id, mrr.from_setup_id,
3266: mrr.setup_id, 'TRANSITION_TIME')) changeover_time,
3267: decode(mrr.from_setup_id,
3268: to_number(null), null,
3269: msc_gantt_utils.getTansitionValue(mrr.plan_id, mrr.sr_instance_id, mrr.organization_id,
3270: mrr.department_id, mrr.resource_id, mrr.from_setup_id,
3271: mrr.setup_id, 'TRANSITION_PENALTY')) changeover_penalty,
3272: msc_gantt_utils.isResOverload(mrr.plan_id, mrr.sr_instance_id,
3273: mrr.organization_id, ms.inventory_item_id,
3268: to_number(null), null,
3269: msc_gantt_utils.getTansitionValue(mrr.plan_id, mrr.sr_instance_id, mrr.organization_id,
3270: mrr.department_id, mrr.resource_id, mrr.from_setup_id,
3271: mrr.setup_id, 'TRANSITION_PENALTY')) changeover_penalty,
3272: msc_gantt_utils.isResOverload(mrr.plan_id, mrr.sr_instance_id,
3273: mrr.organization_id, ms.inventory_item_id,
3274: mrr.department_id, mrr.resource_id, mrr.supply_id) res_overload
3275: from msc_resource_requirements mrr,
3276: msc_department_resources mdr,
3292: and mrr.department_id = mgq.department_id
3293: and mrr.resource_id = mgq.resource_id
3294: and mrr.end_date is not null
3295: and nvl(mrr.parent_id,2) =2
3296: and ( ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
3297: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
3298: between p_start_date and p_end_date
3299: )
3300: or ( msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
3296: and ( ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
3297: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
3298: between p_start_date and p_end_date
3299: )
3300: or ( 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)
3302: between p_start_date and p_end_date )
3303: or ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
3304: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) <= p_start_date
3299: )
3300: or ( 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)
3302: between p_start_date and p_end_date )
3303: or ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
3304: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) <= p_start_date
3305: and msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
3306: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) >= p_end_date )
3307: )
3301: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
3302: between p_start_date and p_end_date )
3303: or ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
3304: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) <= p_start_date
3305: and msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
3306: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) >= p_end_date )
3307: )
3308: and mrr.plan_id = mdr.plan_id
3309: and mrr.organization_id = mdr.organization_id
3334: nvl(mrr.status,0) status,
3335: nvl(mrr.applied,0) applied,
3336: nvl(mrr.firm_flag,0) res_firm_flag,
3337: ms.firm_planned_type sup_firm_flag,
3338: msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrir.start_date, mrir.end_date,
3339: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) start_date,
3340: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrir.start_date, mrir.end_date,
3341: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) end_date,
3342: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
3336: nvl(mrr.firm_flag,0) res_firm_flag,
3337: ms.firm_planned_type sup_firm_flag,
3338: msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrir.start_date, mrir.end_date,
3339: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) start_date,
3340: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrir.start_date, mrir.end_date,
3341: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) end_date,
3342: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
3343: msc_gantt_utils.isResConstraint(mrr.plan_id, mrr.sr_instance_id,
3344: mrr.organization_id, ms.inventory_item_id,
3338: msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrir.start_date, mrir.end_date,
3339: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) start_date,
3340: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrir.start_date, mrir.end_date,
3341: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) end_date,
3342: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
3343: msc_gantt_utils.isResConstraint(mrr.plan_id, mrr.sr_instance_id,
3344: mrr.organization_id, ms.inventory_item_id,
3345: mrr.department_id, mrr.resource_id, mrr.supply_id) res_constraint,
3346: ms.new_order_quantity qty,
3339: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) start_date,
3340: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrir.start_date, mrir.end_date,
3341: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) end_date,
3342: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
3343: msc_gantt_utils.isResConstraint(mrr.plan_id, mrr.sr_instance_id,
3344: mrr.organization_id, ms.inventory_item_id,
3345: mrr.department_id, mrr.resource_id, mrr.supply_id) res_constraint,
3346: ms.new_order_quantity qty,
3347: nvl(mrir.batch_number, mbp_null_value),
3354: mrr.ulpcd,
3355: mrr.uepsd,
3356: mrr.uepcd,
3357: mrr.eacd,
3358: msc_gantt_utils.getResActResNodeLabel(mrr.plan_id, mrr.sr_instance_id, mrr.transaction_id) bar_text,
3359: ms.inventory_item_id,
3360: msc_get_name.supply_order_number(ms.order_type, ms.order_number, ms.plan_id,
3361: ms.sr_instance_id, ms.transaction_id, ms.disposition_id) order_number,
3362: mrr.operation_seq_num,
3375: msc_get_name.setup_code(mrr.plan_id, mrr.sr_instance_id, mrr.resource_id,
3376: mrr.organization_id, mrr.setup_id)) to_setup_code,
3377: decode(mrr.from_setup_id,
3378: to_number(null), null,
3379: msc_gantt_utils.getTansitionValue(mrr.plan_id, mrr.sr_instance_id,
3380: mrr.organization_id, mrr.department_id, mrr.resource_id, mrr.from_setup_id,
3381: mrr.setup_id, 'STANDARD_OPERATION_CODE')) std_op_code,
3382: decode(mrr.from_setup_id,
3383: to_number(null), null,
3380: mrr.organization_id, mrr.department_id, mrr.resource_id, mrr.from_setup_id,
3381: mrr.setup_id, 'STANDARD_OPERATION_CODE')) std_op_code,
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_TIME')) changeover_time,
3387: decode(mrr.from_setup_id,
3388: to_number(null), null,
3385: mrr.department_id, mrr.resource_id, mrr.from_setup_id,
3386: mrr.setup_id, 'TRANSITION_TIME')) changeover_time,
3387: decode(mrr.from_setup_id,
3388: to_number(null), null,
3389: msc_gantt_utils.getTansitionValue(mrr.plan_id, mrr.sr_instance_id, mrr.organization_id,
3390: mrr.department_id, mrr.resource_id, mrr.from_setup_id,
3391: mrr.setup_id, 'TRANSITION_PENALTY')) changeover_penalty,
3392: msc_gantt_utils.isResOverload(mrr.plan_id, mrr.sr_instance_id,
3393: mrr.organization_id, ms.inventory_item_id,
3388: to_number(null), null,
3389: msc_gantt_utils.getTansitionValue(mrr.plan_id, mrr.sr_instance_id, mrr.organization_id,
3390: mrr.department_id, mrr.resource_id, mrr.from_setup_id,
3391: mrr.setup_id, 'TRANSITION_PENALTY')) changeover_penalty,
3392: msc_gantt_utils.isResOverload(mrr.plan_id, mrr.sr_instance_id,
3393: mrr.organization_id, ms.inventory_item_id,
3394: mrr.department_id, mrr.resource_id, mrr.supply_id) res_overload
3395: from msc_resource_instance_reqs mrir,
3396: msc_resource_requirements mrr,
3427: and nvl(mrir.parent_id, mbp_null_value) = nvl(mrr.parent_id, mbp_null_value)
3428: and mrir.start_date = nvl(mrr.firm_start_date, mrr.start_date)
3429: and mrir.end_date = nvl(mrr.firm_end_date, mrr.end_date)
3430: and nvl(mrr.parent_id, 2) = 2
3431: and ( ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
3432: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
3433: between p_start_date and p_end_date
3434: )
3435: or ( msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
3431: and ( ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
3432: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
3433: between p_start_date and p_end_date
3434: )
3435: or ( 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)
3437: between p_start_date and p_end_date )
3438: or ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
3439: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) <= p_start_date
3434: )
3435: or ( 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)
3437: between p_start_date and p_end_date )
3438: or ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
3439: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) <= p_start_date
3440: and msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
3441: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) >= p_end_date )
3442: )
3436: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
3437: between p_start_date and p_end_date )
3438: or ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
3439: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) <= p_start_date
3440: and msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
3441: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) >= p_end_date )
3442: )
3443: and ms.plan_id = mrr.plan_id
3444: and ms.transaction_id = mrr.supply_id
3454: cursor res_act_batch_cur is
3455: select mgq.row_index,
3456: mrr.sr_instance_id,
3457: mrr.organization_id,
3458: msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
3459: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) start_date,
3460: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
3461: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) end_date,
3462: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
3456: mrr.sr_instance_id,
3457: mrr.organization_id,
3458: msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
3459: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) start_date,
3460: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
3461: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) end_date,
3462: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
3463: sum(ms.new_order_quantity) qty,
3464: mrr.batch_number,
3458: msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
3459: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) start_date,
3460: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
3461: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) end_date,
3462: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
3463: sum(ms.new_order_quantity) qty,
3464: mrr.batch_number,
3465: msc_gantt_utils.getResBatchNodeLabel(
3466: nvl(decode(mrr.setup_id,
3461: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) end_date,
3462: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
3463: sum(ms.new_order_quantity) qty,
3464: mrr.batch_number,
3465: msc_gantt_utils.getResBatchNodeLabel(
3466: nvl(decode(mrr.setup_id,
3467: to_number(null), null_space,
3468: msc_gantt_utils.getSetupCode(mrr.plan_id,
3469: mrr.sr_instance_id, mrr.resource_id, mrr.setup_id)),
3464: mrr.batch_number,
3465: msc_gantt_utils.getResBatchNodeLabel(
3466: nvl(decode(mrr.setup_id,
3467: to_number(null), null_space,
3468: msc_gantt_utils.getSetupCode(mrr.plan_id,
3469: mrr.sr_instance_id, mrr.resource_id, mrr.setup_id)),
3470: null_space),
3471: msc_get_name.org_code(mrr.organization_id, mrr.sr_instance_id),
3472: to_char(sum(ms.new_order_quantity)),
3489: and mrr.resource_id = mgq.resource_id
3490: and mrr.batch_number is not null
3491: and mrr.end_date is not null
3492: and nvl(mrr.parent_id,2) = 2
3493: and ( ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
3494: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
3495: between p_start_date and p_end_date
3496: )
3497: or ( msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
3493: and ( ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
3494: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
3495: between p_start_date and p_end_date
3496: )
3497: or ( 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)
3499: between p_start_date and p_end_date )
3500: or ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
3501: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) <= p_start_date
3496: )
3497: or ( 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)
3499: between p_start_date and p_end_date )
3500: or ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
3501: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) <= p_start_date
3502: and msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
3503: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) >= p_end_date )
3504: )
3498: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
3499: between p_start_date and p_end_date )
3500: or ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
3501: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) <= p_start_date
3502: and msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
3503: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) >= p_end_date )
3504: )
3505: and ms.plan_id = mrr.plan_id
3506: and ms.transaction_id = mrr.supply_id
3525: mrr.organization_id,
3526: msc_get_name.org_code(mrr.organization_id, mrr.sr_instance_id),
3527: mrr.resource_id,
3528: mrr.batch_number,
3529: msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
3530: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
3531: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
3532: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
3533: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id),
3527: mrr.resource_id,
3528: mrr.batch_number,
3529: msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
3530: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
3531: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
3532: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
3533: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id),
3534: mrr.setup_id;
3535:
3529: msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
3530: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
3531: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
3532: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
3533: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id),
3534: mrr.setup_id;
3535:
3536: cursor res_inst_act_batch_cur is
3537: select mgq.row_index,
3536: cursor res_inst_act_batch_cur is
3537: select mgq.row_index,
3538: mrr.sr_instance_id,
3539: mrr.organization_id,
3540: msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrir.start_date, mrir.end_date,
3541: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) start_date,
3542: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrir.start_date, mrir.end_date,
3543: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) end_date,
3544: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
3538: mrr.sr_instance_id,
3539: mrr.organization_id,
3540: msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrir.start_date, mrir.end_date,
3541: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) start_date,
3542: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrir.start_date, mrir.end_date,
3543: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) end_date,
3544: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
3545: sum(ms.new_order_quantity) qty,
3546: mrir.batch_number,
3540: msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrir.start_date, mrir.end_date,
3541: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) start_date,
3542: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrir.start_date, mrir.end_date,
3543: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) end_date,
3544: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
3545: sum(ms.new_order_quantity) qty,
3546: mrir.batch_number,
3547: msc_gantt_utils.getResBatchNodeLabel(
3548: nvl(decode(mrr.setup_id,
3543: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) end_date,
3544: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
3545: sum(ms.new_order_quantity) qty,
3546: mrir.batch_number,
3547: msc_gantt_utils.getResBatchNodeLabel(
3548: nvl(decode(mrr.setup_id,
3549: to_number(null), null_space,
3550: msc_gantt_utils.getSetupCode(mrr.plan_id,
3551: mrr.sr_instance_id, mrr.resource_id, mrr.setup_id)),
3546: mrir.batch_number,
3547: msc_gantt_utils.getResBatchNodeLabel(
3548: nvl(decode(mrr.setup_id,
3549: to_number(null), null_space,
3550: msc_gantt_utils.getSetupCode(mrr.plan_id,
3551: mrr.sr_instance_id, mrr.resource_id, mrr.setup_id)),
3552: null_space),
3553: msc_get_name.org_code(mrr.organization_id, mrr.sr_instance_id),
3554: to_char(sum(ms.new_order_quantity)),
3585: and nvl(mrir.parent_id, mbp_null_value) = nvl(mrr.parent_id, mbp_null_value)
3586: and mrir.start_date = nvl(mrr.firm_start_date, mrr.start_date)
3587: and mrir.end_date = nvl(mrr.firm_end_date, mrr.end_date)
3588: and nvl(mrr.parent_id, 2) = 2
3589: and ( ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
3590: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
3591: between p_start_date and p_end_date
3592: )
3593: or ( msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
3589: and ( ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
3590: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
3591: between p_start_date and p_end_date
3592: )
3593: or ( 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)
3595: between p_start_date and p_end_date )
3596: or ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
3597: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) <= p_start_date
3592: )
3593: or ( 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)
3595: between p_start_date and p_end_date )
3596: or ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
3597: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) <= p_start_date
3598: and msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
3599: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) >= p_end_date )
3600: )
3594: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
3595: between p_start_date and p_end_date )
3596: or ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
3597: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) <= p_start_date
3598: and msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
3599: mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) >= p_end_date )
3600: )
3601: and ms.plan_id = mrr.plan_id
3602: and ms.transaction_id = mrr.supply_id
3607: mrr.organization_id,
3608: msc_get_name.org_code(mrr.organization_id, mrr.sr_instance_id),
3609: mrr.resource_id,
3610: mrir.batch_number,
3611: msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrir.start_date, mrir.end_date,
3612: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
3613: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrir.start_date, mrir.end_date,
3614: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
3615: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id),
3609: mrr.resource_id,
3610: mrir.batch_number,
3611: msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrir.start_date, mrir.end_date,
3612: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
3613: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrir.start_date, mrir.end_date,
3614: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
3615: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id),
3616: mrr.setup_id;
3617:
3611: msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrir.start_date, mrir.end_date,
3612: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
3613: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrir.start_date, mrir.end_date,
3614: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
3615: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id),
3616: mrr.setup_id;
3617:
3618: l_op_seq number_arr;
3619: l_res_seq number_arr;
3680: else
3681: l_batch_flag := sys_no;
3682: end if; -- }
3683:
3684: --l_res_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_no, sys_no, sys_yes);
3685: --l_res_inst_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_yes, sys_no, sys_yes);
3686: --l_res_batch_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_no, sys_yes);
3687: --l_res_inst_batch_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_yes, sys_yes);
3688: --4997096 bugfix, instead calls the below procedure
3681: l_batch_flag := sys_no;
3682: end if; -- }
3683:
3684: --l_res_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_no, sys_no, sys_yes);
3685: --l_res_inst_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_yes, sys_no, sys_yes);
3686: --l_res_batch_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_no, sys_yes);
3687: --l_res_inst_batch_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_yes, sys_yes);
3688: --4997096 bugfix, instead calls the below procedure
3689: 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);
3682: end if; -- }
3683:
3684: --l_res_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_no, sys_no, sys_yes);
3685: --l_res_inst_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_yes, sys_no, sys_yes);
3686: --l_res_batch_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_no, sys_yes);
3687: --l_res_inst_batch_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_yes, sys_yes);
3688: --4997096 bugfix, instead calls the below procedure
3689: 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);
3690:
3683:
3684: --l_res_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_no, sys_no, sys_yes);
3685: --l_res_inst_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_yes, sys_no, sys_yes);
3686: --l_res_batch_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_no, sys_yes);
3687: --l_res_inst_batch_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_yes, sys_yes);
3688: --4997096 bugfix, instead calls the below procedure
3689: 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);
3690:
3691: 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);
3685: --l_res_inst_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_yes, sys_no, sys_yes);
3686: --l_res_batch_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_no, sys_yes);
3687: --l_res_inst_batch_rows := msc_gantt_utils.isResRowInGantt(p_query_id, sys_yes, sys_yes);
3688: --4997096 bugfix, instead calls the below procedure
3689: 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);
3690:
3691: 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);
3692:
3693: if ( l_res_rows = sys_yes ) then -- {
3794: nvl(mgdq.resource_units, mbp_null_value),
3795: nvl(mgdq.group_sequence_id, mbp_null_value),
3796: nvl(mgdq.group_sequence_number, mbp_null_value),
3797: nvl(mgdq.bar_label, null_space),
3798: msc_gantt_utils.getDisplayType(p_display_type, mgdq.end_date, mgdq.ulpst,
3799: mgdq.res_firm_flag, mgdq.late_flag, g_gantt_ra_toler_days_early,
3800: g_gantt_ra_toler_days_late) display_type,
3801: mgdq.cepst,
3802: mgdq.cepct,
3976: populateSuppDtlTabIntoGantt(p_query_id, SUPP_AVAIL_ROW_TYPE,
3977: v_row_index, v_start_date, v_qty, v_consumed_qty, SUMMARY_DATA);
3978:
3979: l_mfg_cal_query_id := getMFQSequence(l_mfg_cal_query_id);
3980: msc_gantt_utils.getBucketDates(p_start_date, p_end_date, v_bkt_start, v_bkt_end, l_mfg_cal_query_id);
3981:
3982: -- 2. available capacity for items with NO delivery calendar
3983: select mgq.row_index, mfq.date1, msc.capacity
3984: bulk collect into v_row_index, v_start_date, v_qty
4111: l_supp_bkt_start_date date;
4112: l_supp_bkt_end_date date;
4113:
4114: begin
4115: msc_gantt_utils.put_line('prepareSupplierGantt in ');
4116: p_start_index := mbp_null_value;
4117:
4118: msc_gantt_utils.getBucketDates(p_start_date, p_end_date, v_bkt_start, v_bkt_end, l_bkt_query_id);
4119:
4114: begin
4115: msc_gantt_utils.put_line('prepareSupplierGantt in ');
4116: p_start_index := mbp_null_value;
4117:
4118: msc_gantt_utils.getBucketDates(p_start_date, p_end_date, v_bkt_start, v_bkt_end, l_bkt_query_id);
4119:
4120: msc_gantt_utils.put_line('claendar rows ' || v_bkt_start.count);
4121:
4122: for active_row in active_res_rows_cur(p_query_id) loop -- {
4116: p_start_index := mbp_null_value;
4117:
4118: msc_gantt_utils.getBucketDates(p_start_date, p_end_date, v_bkt_start, v_bkt_end, l_bkt_query_id);
4119:
4120: msc_gantt_utils.put_line('claendar rows ' || v_bkt_start.count);
4121:
4122: for active_row in active_res_rows_cur(p_query_id) loop -- {
4123: if ( p_start_index = mbp_null_value) then
4124: p_start_index := active_row.row_index;
4191: -- 4476899 bug fix, pass zero's also
4192:
4193: end loop; -- }
4194: end loop; -- }
4195: msc_gantt_utils.put_line('prepareSupplierGantt out ');
4196:
4197: end prepareSupplierGantt;
4198:
4199: procedure sendSupplierGantt(p_query_id number,
4196:
4197: end prepareSupplierGantt;
4198:
4199: procedure sendSupplierGantt(p_query_id number,
4200: p_supp_data IN OUT NOCOPY msc_gantt_utils.maxCharTbl) is
4201:
4202: v_start_date date_arr := date_arr(sysdate);
4203: v_avail_qty number_arr:= number_arr(0);
4204: v_overload_qty number_arr:= number_arr(0);
4236:
4237: end sendSupplierGantt;
4238:
4239: procedure sendResourceGantt(p_query_id number, p_view_type number,
4240: p_isBucketed number, p_require_data IN OUT NOCOPY msc_gantt_utils.maxCharTbl,
4241: p_avail_data IN OUT NOCOPY msc_gantt_utils.maxCharTbl,
4242: p_onlyAvailData boolean, p_display_type number) is
4243:
4244: v_start date_arr:= date_arr(sysdate);
4237: end sendSupplierGantt;
4238:
4239: procedure sendResourceGantt(p_query_id number, p_view_type number,
4240: p_isBucketed number, p_require_data IN OUT NOCOPY msc_gantt_utils.maxCharTbl,
4241: p_avail_data IN OUT NOCOPY msc_gantt_utils.maxCharTbl,
4242: p_onlyAvailData boolean, p_display_type number) is
4243:
4244: v_start date_arr:= date_arr(sysdate);
4245: v_end date_arr:= date_arr(sysdate);
4388:
4389: end calculateResHours;
4390:
4391: procedure getBucketDates(p_start_date date, p_end_date date,
4392: v_bkt_start in out nocopy msc_gantt_utils.date_arr,
4393: v_bkt_end in out nocopy msc_gantt_utils.date_arr,
4394: p_query_id in out nocopy number) is
4395: p_temp_start date;
4396: i number;
4389: end calculateResHours;
4390:
4391: procedure getBucketDates(p_start_date date, p_end_date date,
4392: v_bkt_start in out nocopy msc_gantt_utils.date_arr,
4393: v_bkt_end in out nocopy msc_gantt_utils.date_arr,
4394: p_query_id in out nocopy number) is
4395: p_temp_start date;
4396: i number;
4397: begin
4493: p_start_index number;
4494: l_bkt_query_id number := -1;
4495: begin
4496:
4497: msc_gantt_utils.put_line('prepareResHoursGantt in ');
4498: p_start_index := mbp_null_value;
4499:
4500: msc_gantt_utils.getBucketDates(p_start_date, p_end_date, v_bkt_start, v_bkt_end, l_bkt_query_id);
4501:
4496:
4497: msc_gantt_utils.put_line('prepareResHoursGantt in ');
4498: p_start_index := mbp_null_value;
4499:
4500: msc_gantt_utils.getBucketDates(p_start_date, p_end_date, v_bkt_start, v_bkt_end, l_bkt_query_id);
4501:
4502: msc_gantt_utils.put_line('prepareResHoursGantt: claendar rows: ' || v_bkt_start.count);
4503:
4504: for active_row in active_res_rows_cur(p_query_id) loop -- {
4498: p_start_index := mbp_null_value;
4499:
4500: msc_gantt_utils.getBucketDates(p_start_date, p_end_date, v_bkt_start, v_bkt_end, l_bkt_query_id);
4501:
4502: msc_gantt_utils.put_line('prepareResHoursGantt: claendar rows: ' || v_bkt_start.count);
4503:
4504: for active_row in active_res_rows_cur(p_query_id) loop -- {
4505: if ( p_start_index = mbp_null_value) then
4506: p_start_index := active_row.row_index;
4515: and parent_id = SUMMARY_DATA
4516: and schedule_flag in (RES_SETUP_ROW_TYPE, RES_SETUP_FIXED_ROW_TYPE)
4517: order by start_date; --only setup time
4518:
4519: msc_gantt_utils.put_line('prepareResHoursGantt: res seup rows: '||v_setup_start.count);
4520:
4521:
4522: select start_date, end_date, resource_hours, nvl(display_flag, sys_no)
4523: bulk collect into v_req_start, v_req_end, v_req_hours, v_req_display_type
4528: and parent_id = SUMMARY_DATA
4529: and schedule_flag in (RES_REQ_ROW_TYPE, RES_REQ_SDS_ROW_TYPE)
4530: order by start_date; --only run time
4531:
4532: msc_gantt_utils.put_line('prepareResHoursGantt: res req rows: '|| v_req_start.count);
4533:
4534: select start_date, end_date, resource_hours
4535: bulk collect into v_avail_start, v_avail_end, v_avail_hours
4536: from msc_gantt_dtl_query
4539: and parent_id = SUMMARY_DATA
4540: and row_type = RES_AVAIL_ROW_TYPE
4541: order by start_date; --avail time
4542:
4543: msc_gantt_utils.put_line('prepareResHoursGantt: res avail rows: '|| v_avail_start.count);
4544:
4545: OPEN line_rate(p_query_id, active_row.row_index);
4546: FETCH line_rate INTO v_line_rate;
4547: CLOSE line_rate;
4581: v_line_rate, v_setup_start, v_setup_end, v_setup_hours, v_setup_display_type);
4582:
4583: end loop; -- }
4584: end loop; -- }
4585: msc_gantt_utils.put_line('prepareResHoursGantt out ');
4586:
4587: end prepareResHoursGantt;
4588:
4589:
4682: end if;
4683: elsif ( p_filter_type = FILTER_TYPE_FOLDER_ID ) then
4684: null; --not supported currently..
4685: elsif ( p_filter_type = FILTER_TYPE_WHERE_STMT ) then
4686: msc_gantt_utils.findRequest(p_plan_id, p_list,
4687: p_query_id, p_view_type, FILTER_TYPE_WHERE_STMT, p_folder_id);
4688: elsif ( p_filter_type = FILTER_TYPE_AMONG ) then
4689: msc_gantt_utils.findRequest(p_plan_id, p_list,
4690: p_query_id, p_view_type, FILTER_TYPE_AMONG);
4685: elsif ( p_filter_type = FILTER_TYPE_WHERE_STMT ) then
4686: msc_gantt_utils.findRequest(p_plan_id, p_list,
4687: p_query_id, p_view_type, FILTER_TYPE_WHERE_STMT, p_folder_id);
4688: elsif ( p_filter_type = FILTER_TYPE_AMONG ) then
4689: msc_gantt_utils.findRequest(p_plan_id, p_list,
4690: p_query_id, p_view_type, FILTER_TYPE_AMONG);
4691: elsif ( p_filter_type = FILTER_TYPE_QUERY_ID ) then
4692: populateResIntoGanttFromGantt(p_query_id, p_list, p_plan_id);
4693: end if; -- if ( p_filter_type = 1 ) then
5094: loop
5095: fetch c_res_batch_rows into l_inst_id, l_trx_id;
5096: exit when c_res_batch_rows%notfound;
5097:
5098: msc_gantt_utils.loadAltResource(p_plan_id,
5099: l_inst_id, l_trx_id, p_alt_resource, p_alt_resource_inst, p_serial_number, p_alt_num,
5100: p_node_type, p_to_node_type, p_return_trx_id, p_return_status, p_out);
5101: end loop;
5102: close c_res_batch_rows;
5415: end if;
5416: end if; --}
5417: -- 4569506 bugfix ends
5418:
5419: msc_gantt_utils.getAltResource(p_plan_id, p_transaction_id, p_instance_id,
5420: l_dummy_name_data, l_dummy_id_data, p_node_type, sys_yes);
5421:
5422: select count(*)
5423: into l_alt_count
5470: p_out := 'CANNOT_OFFLOAD_1_ASGN_UNITS';
5471: return;
5472: end if;
5473:
5474: msc_gantt_utils.updateReqInstFromAlt(p_plan_id, p_instance_id,
5475: ll_res_inst_trx_id, p_alt_resource, p_alt_resource_inst, p_serial_number,
5476: null, p_alt_resource, null);
5477:
5478: -- update requirement's supply
5475: ll_res_inst_trx_id, p_alt_resource, p_alt_resource_inst, p_serial_number,
5476: null, p_alt_resource, null);
5477:
5478: -- update requirement's supply
5479: msc_gantt_utils.updateSupplies(p_plan_id, l_supply_id, TOUCH_SUPPLY);
5480:
5481: p_return_status := 'OK';
5482: p_out := '';
5483: return;
5550: end if;
5551:
5552: ll_temp_res_inst_trx_id := get_child_res_trx_id(p_plan_id, p_instance_id, l_simu_res(1).transaction_id);
5553:
5554: l_dummy_id := msc_gantt_utils.insertReqFromAlt(p_plan_id, p_instance_id,
5555: l_simu_res(1).transaction_id , l_alt_res(i).resource_id, v_hours,
5556: l_alt_res(i).alternate_number, l_alt_res(i).basis_type,
5557: l_alt_res(i).orig_resource_seq_num);
5558:
5560: elsif i > l_alt_res.last then -- delete the extra res
5561:
5562: put_line(' in i > l_alt_res.last - deleting alt res into mrr ');
5563: ll_temp_res_inst_trx_id := get_child_res_trx_id(p_plan_id, p_instance_id, l_simu_res(i).transaction_id);
5564: msc_gantt_utils.DeleteReqFromAlt(p_plan_id, p_instance_id, l_simu_res(i).transaction_id);
5565: DeleteReqInstFromAlt(p_plan_id, p_instance_id, ll_temp_res_inst_trx_id);
5566:
5567: else -- update the res to alt_res
5568:
5576:
5577: ll_temp_res_inst_trx_id := get_child_res_trx_id(p_plan_id, p_instance_id, l_simu_res(i).transaction_id);
5578: put_line(' in else simu ll_temp_res_inst_trx_id '||ll_temp_res_inst_trx_id);
5579:
5580: msc_gantt_utils.updateReqFromAlt(p_plan_id, p_instance_id,
5581: l_simu_res(i).transaction_id, l_alt_res(i).resource_id,
5582: v_hours, l_alt_res(i).alternate_number, l_alt_res(i).basis_type,
5583: l_alt_res(i).orig_resource_seq_num);
5584:
5587: if (l_simu_res(i).transaction_id = ll_res_trx_id ) then -- {
5588: put_line(' in res inst update insert');
5589: if ( p_node_type = RES_NODE and p_to_node_type = RES_INST_NODE ) then
5590: put_line(' res inst insert');
5591: l_temp_trx_id := msc_gantt_utils.insertReqInstFromAlt(p_plan_id,
5592: p_instance_id, ll_res_trx_id, p_alt_resource, p_alt_resource_inst,
5593: p_serial_number, v_hours, p_alt_num, RES_NODE,
5594: l_alt_res(i).orig_resource_seq_num);
5595: p_return_trx_id := l_temp_trx_id ;
5594: l_alt_res(i).orig_resource_seq_num);
5595: p_return_trx_id := l_temp_trx_id ;
5596: elsif ( p_node_type = RES_INST_NODE and p_to_node_type = RES_INST_NODE ) then
5597: put_line(' res inst update');
5598: msc_gantt_utils.updateReqInstFromAlt(p_plan_id, p_instance_id,
5599: ll_res_inst_trx_id, p_alt_resource, p_alt_resource_inst, p_serial_number,
5600: v_hours, l_alt_res(i).alternate_number,
5601: l_alt_res(i).orig_resource_seq_num);
5602: end if;
5607: end loop; -- }
5608: end loop; -- }
5609:
5610: -- update requirement's supply
5611: msc_gantt_utils.updateSupplies(p_plan_id, l_supply_id, TOUCH_SUPPLY);
5612:
5613: p_return_status := 'OK';
5614: p_out := '';
5615: exception
5900: return;
5901: end if;
5902:
5903: -- update the simultaneous resource for a given res trans id
5904: msc_gantt_utils.updateReqSimu(p_plan_id, p_inst_id, l_trx_id,
5905: l_firm_flag, l_start_date, l_end_date, l_firm_start_date, l_firm_end_date,
5906: MOVE_MRR, p_return_status, p_out);
5907:
5908: if p_return_status = 'ERROR' then
6277: for update of mrr.firm_flag nowait;
6278:
6279: if ( p_update_mode = FIRM_MRR ) then
6280: -- update requirement
6281: msc_gantt_utils.updateReq(p_plan_id, l_instance_id, l_transaction_id,
6282: p_firm_type, p_start_date, p_end_date, p_firm_start_date, p_firm_end_date, p_update_mode);
6283: elsif ( p_update_mode = MOVE_MRR ) then
6284: moveOneResource(p_plan_id, l_transaction_id, l_instance_id,
6285: p_start_date, p_end_date, p_return_status, p_out, RES_NODE);
6404: (nvl(mrr2.end_date, mrr2.start_date+mrr2.resource_hours/24) - mrr2.start_date),
6405: FIRM_START_RES, mrr2.firm_start_date +
6406: (nvl(mrr2.end_date, mrr2.start_date+mrr2.resource_hours/24) - mrr2.start_date),
6407: nvl(nvl(mrr2.firm_end_date, mrr2.end_date), mrr2.start_date+mrr2.resource_hours/24)) end_date,
6408: msc_gantt_utils.getMTQTime(mrr2.transaction_id, p_plan_id, p_instance_id) mtq_time
6409: from msc_resource_requirements mrr1,
6410: msc_resource_requirements mrr2,
6411: msc_routings mr
6412: where mrr1.plan_id = p_plan_id
6535: (nvl(mrir2.end_date, mrir2.start_date+mrir2.resource_instance_hours/24) - mrir2.start_date),
6536: FIRM_START_RES, mrr2.firm_start_date +
6537: (nvl(mrir2.end_date, mrir2.start_date+mrir2.resource_instance_hours/24) - mrir2.start_date),
6538: nvl(nvl(mrr2.firm_end_date, mrir2.end_date), mrir2.start_date+mrir2.resource_instance_hours/24)) end_date,
6539: msc_gantt_utils.getMTQTime(mrr2.transaction_id, p_plan_id, p_instance_id) mtq_time
6540: from msc_resource_requirements mrr1,
6541: msc_resource_instance_reqs mrir1,
6542: msc_resource_requirements mrr2,
6543: msc_resource_instance_reqs mrir2,
6959: end if; -- }
6960: put_line('ll_res_trx_id ll_res_inst_trx_id '||ll_res_trx_id||' - '||ll_res_inst_trx_id);
6961:
6962: -- lock the mrr and getData
6963: msc_gantt_utils.lockReqNGetData(p_plan_id, p_instance_id, ll_res_trx_id,
6964: l_firm_type, l_supply_id, l_start, l_end, l_firm_start, l_firm_end,
6965: p_return_status, p_out);
6966:
6967: if p_return_status = 'ERROR' then
6969: end if;
6970:
6971:
6972: -- update requirement
6973: msc_gantt_utils.updateReq(p_plan_id, p_instance_id, ll_res_trx_id,
6974: p_firm_type, l_start, l_end, l_firm_start, l_firm_end, FIRM_MRR);
6975:
6976: -- update requirement
6977: msc_gantt_utils.firmReqInst(p_plan_id, p_instance_id, ll_res_trx_id, ll_res_inst_trx_id);
6973: msc_gantt_utils.updateReq(p_plan_id, p_instance_id, ll_res_trx_id,
6974: p_firm_type, l_start, l_end, l_firm_start, l_firm_end, FIRM_MRR);
6975:
6976: -- update requirement
6977: msc_gantt_utils.firmReqInst(p_plan_id, p_instance_id, ll_res_trx_id, ll_res_inst_trx_id);
6978:
6979: -- update requirement's supply
6980: msc_gantt_utils.updateSupplies(p_plan_id, l_supply_id, TOUCH_SUPPLY);
6981:
6976: -- update requirement
6977: msc_gantt_utils.firmReqInst(p_plan_id, p_instance_id, ll_res_trx_id, ll_res_inst_trx_id);
6978:
6979: -- update requirement's supply
6980: msc_gantt_utils.updateSupplies(p_plan_id, l_supply_id, TOUCH_SUPPLY);
6981:
6982: -- update the simultaneous resource for a given res trans id
6983: msc_gantt_utils.updateReqSimu(p_plan_id, p_instance_id, ll_res_trx_id,
6984: p_firm_type, l_start, l_end, l_firm_start, l_firm_end, FIRM_MRR, p_return_status, p_out);
6979: -- update requirement's supply
6980: msc_gantt_utils.updateSupplies(p_plan_id, l_supply_id, TOUCH_SUPPLY);
6981:
6982: -- update the simultaneous resource for a given res trans id
6983: msc_gantt_utils.updateReqSimu(p_plan_id, p_instance_id, ll_res_trx_id,
6984: p_firm_type, l_start, l_end, l_firm_start, l_firm_end, FIRM_MRR, p_return_status, p_out);
6985:
6986: put_line(' firmResourcePub out ');
6987: end firmResourcePub;
7041: end if;
7042: p_new_group_seq_number := p_new_group_seq_number + 10;
7043:
7044: -- lock the mrr and getNewData
7045: msc_gantt_utils.lockReqNGetData(p_plan_id, l_inst_id, ll_res_trx_id,
7046: l_firm_type, l_supply_id, l_start, l_end, l_firm_start, l_firm_end,
7047: p_return_status, p_out);
7048:
7049: update msc_resource_requirements
7100: fetch res_req_info into l_firm_type, l_firm_start, l_firm_end,
7101: l_batch_number, l_group_sequence_id, l_group_sequence_number;
7102: close res_req_info;
7103:
7104: msc_gantt_utils.updateBatchReq(p_plan_id, p_instance_id, l_batch_number,
7105: to_date(null), to_date(null), p_firm_type, FIRM_MRR, p_return_status, p_out);
7106: else
7107: open res_inst_req_info(p_plan_id, p_instance_id, p_instance_id);
7108: fetch res_inst_req_info into l_batch_number;
7107: open res_inst_req_info(p_plan_id, p_instance_id, p_instance_id);
7108: fetch res_inst_req_info into l_batch_number;
7109: close res_inst_req_info;
7110:
7111: msc_gantt_utils.updateBatchInstReq(p_plan_id, p_instance_id, l_batch_number,
7112: to_date(null), to_date(null), p_firm_type, FIRM_MRR, p_return_status, p_out);
7113: end if; -- }
7114: end firmResourceBatchPub;
7115:
7127: begin
7128: p_return_status := 'OK';
7129:
7130: -- update requirement's supply
7131: msc_gantt_utils.updateSupplies(p_plan_id, p_transaction_id, FIRM_SUPPLY, p_firm_type);
7132:
7133: --pabram ... need to verify
7134: end firmSupplyPub;
7135:
7183: and mjor.principal_flag <> 2;
7184:
7185: cursor c_alt_res_inst (p_plan number, p_inst number, p_trx number)is
7186: select distinct mor.alternate_number
7187: || COLON_SEPARATOR || msc_gantt_utils.getDeptResInstCode(mdri.plan_id,
7188: mdri.sr_instance_id, mdri.organization_id, mdri.department_id,
7189: mdri.resource_id, mdri.res_instance_id, mdri.serial_number),
7190: mdri.resource_id,
7191: mdri.res_instance_id,
7209: and mor.sr_instance_id = mdri.sr_instance_id
7210: and mor.resource_id = mdri.resource_id
7211: union all --5478582 bugfix
7212: select distinct mjor.alternate_num
7213: || COLON_SEPARATOR || msc_gantt_utils.getDeptResInstCode(mdri.plan_id,
7214: mdri.sr_instance_id, mdri.organization_id, mdri.department_id,
7215: mdri.resource_id, mdri.res_instance_id, mdri.serial_number),
7216: mdri.resource_id,
7217: mdri.res_instance_id,
7282: and mjor.principal_flag <> 2;
7283:
7284: cursor c_alt_inst_inst (p_plan number, p_inst number, p_trx number,
7285: p_res_instance_id number, p_serial_number varchar2, p_equipment_item_id number )is
7286: select distinct mor.alternate_number ||':'||msc_gantt_utils.getDeptResInstCode(mdri.plan_id,
7287: mdri.sr_instance_id, mdri.organization_id, mdri.department_id,
7288: mdri.resource_id, mdri.res_instance_id, mdri.serial_number),
7289: mdri.resource_id,
7290: mdri.res_instance_id,
7306: and mor.plan_id = mdri.plan_id
7307: and mor.sr_instance_id = mdri.sr_instance_id
7308: and mor.resource_id = mdri.resource_id
7309: union all --5513960 bugfix
7310: select distinct mrr.alternate_num ||':'||msc_gantt_utils.getDeptResInstCode(mdri.plan_id,
7311: mdri.sr_instance_id, mdri.organization_id, mdri.department_id,
7312: mdri.resource_id, mdri.res_instance_id, mdri.serial_number),
7313: mdri.resource_id,
7314: mdri.res_instance_id,
7326: and mrr.resource_id = mdri.resource_id
7327: and nvl(mdri.serial_number,mbp_null_value_char) <> nvl(p_serial_number,mbp_null_value_char)
7328: --and nvl(mdri.equipment_item_id,mbp_null_value) <> nvl(p_equipment_item_id,mbp_null_value)
7329: union all --5478582 bugfix
7330: select distinct mjor.alternate_num ||':'||msc_gantt_utils.getDeptResInstCode(mdri.plan_id,
7331: mdri.sr_instance_id, mdri.organization_id, mdri.department_id,
7332: mdri.resource_id, mdri.res_instance_id, mdri.serial_number),
7333: mdri.resource_id,
7334: mdri.res_instance_id,
7628: and mor.alternate_number = mrr.alternate_num
7629: and mor.resource_id <> mrr.resource_id;
7630:
7631: cursor c_simu_res_inst(p_plan number, p_inst number, p_trx number)is
7632: select mor.alternate_number ||':'||msc_gantt_utils.getDeptResInstCode(mdri.plan_id,
7633: mdri.sr_instance_id, mdri.organization_id, mdri.department_id,
7634: mdri.resource_id, mdri.res_instance_id, mdri.serial_number) resource_code,
7635: mdri.resource_id,
7636: mdri.res_instance_id,
7671: and mor.alternate_number = mrr.alternate_num
7672: and mor.resource_id <> mrr.resource_id;
7673:
7674: cursor c_simu_inst_inst (p_plan number, p_inst number, p_trx number)is
7675: select mor.alternate_number ||':'||msc_gantt_utils.getDeptResInstCode(mdri.plan_id,
7676: mdri.sr_instance_id, mdri.organization_id,
7677: mdri.department_id, mdri.resource_id, mdri.res_instance_id, mdri.serial_number) resource_code,
7678: mdri.resource_id,
7679: mdri.res_instance_id,
7820: return;
7821: end;
7822:
7823: -- now update
7824: msc_gantt_utils.updateSupplies(p_plan_id, p_supply_id,
7825: FIRM_ALL_SUPPLY, SYS_YES, p_end_date, l_quan);
7826:
7827: p_return_status := 'OK';
7828:
7848: if ( p_node_type = RES_NODE ) then -- {
7849: ll_res_trx_id := p_transaction_id;
7850:
7851: -- lock the mrr and getNewData
7852: msc_gantt_utils.lockReqNGetData(p_plan_id, p_instance_id, ll_res_trx_id,
7853: l_firm_flag, l_supply_id, l_start, l_end, l_firm_start, l_firm_end,
7854: p_return_status, p_out);
7855:
7856: l_start := p_start_date;
7856: l_start := p_start_date;
7857: l_end := p_end_date;
7858:
7859: -- update requirement
7860: msc_gantt_utils.updateReq(p_plan_id, p_instance_id, ll_res_trx_id,
7861: l_firm_flag, l_start, l_end, l_firm_start, l_firm_end, MOVE_MRR);
7862:
7863: -- update requirement's supply
7864: msc_gantt_utils.updateSupplies(p_plan_id, l_supply_id, TOUCH_SUPPLY);
7860: msc_gantt_utils.updateReq(p_plan_id, p_instance_id, ll_res_trx_id,
7861: l_firm_flag, l_start, l_end, l_firm_start, l_firm_end, MOVE_MRR);
7862:
7863: -- update requirement's supply
7864: msc_gantt_utils.updateSupplies(p_plan_id, l_supply_id, TOUCH_SUPPLY);
7865:
7866: else
7867: ll_res_inst_trx_id := p_transaction_id;
7868:
7866: else
7867: ll_res_inst_trx_id := p_transaction_id;
7868:
7869: -- lock the mrr and getNewData
7870: msc_gantt_utils.lockReqInstNGetData(p_plan_id, p_instance_id, ll_res_inst_trx_id,
7871: l_supply_id, l_start, l_end, p_return_status, p_out);
7872:
7873: l_start := p_start_date;
7874: l_end := p_end_date;
7873: l_start := p_start_date;
7874: l_end := p_end_date;
7875:
7876: -- update requirement
7877: msc_gantt_utils.updateReqInst(p_plan_id, p_instance_id, ll_res_inst_trx_id, l_start, l_end);
7878:
7879: -- update requirement's supply
7880: msc_gantt_utils.updateSupplies(p_plan_id, l_supply_id, TOUCH_SUPPLY);
7881:
7876: -- update requirement
7877: msc_gantt_utils.updateReqInst(p_plan_id, p_instance_id, ll_res_inst_trx_id, l_start, l_end);
7878:
7879: -- update requirement's supply
7880: msc_gantt_utils.updateSupplies(p_plan_id, l_supply_id, TOUCH_SUPPLY);
7881:
7882: end if; -- }
7883:
7884: -- when the move is made, no need to update the firm dates...
7951: fetch res_req_info into l_firm_flag, l_firm_start, l_firm_end,
7952: l_batch_number, l_group_sequence_id, l_group_sequence_number;
7953: close res_req_info;
7954:
7955: msc_gantt_utils.updateBatchReq(p_plan_id, p_instance_id, l_batch_number,
7956: p_start_date, p_end_date, l_firm_flag, MOVE_MRR, p_return_status, p_out);
7957:
7958: else
7959:
7960: open res_inst_req_info(p_plan_id, p_instance_id, p_instance_id);
7961: fetch res_inst_req_info into l_batch_number;
7962: close res_inst_req_info;
7963:
7964: msc_gantt_utils.updateBatchInstReq(p_plan_id, p_instance_id, l_batch_number,
7965: p_start_date, p_end_date, l_firm_flag, MOVE_MRR, p_return_status, p_out);
7966:
7967: end if; -- }
7968:
8010: return;
8011: end if;
8012:
8013: if (p_duration is not null) then
8014: msc_gantt_utils.updateResSeq(p_plan_id, p_instance_id,
8015: l_group_sequence_id, p_duration, p_plan_start_date, p_plan_end_date,
8016: p_return_status, p_out, p_validate_flag, p_node_type);
8017: end if;
8018:
8054:
8055: -- res/res inst firm seq move logic and batched res/res inst move logic
8056: if ( p_res_firm_seq ) then
8057: put_line(' before moveResourceSeq');
8058: msc_gantt_utils.moveResourceSeq(p_plan_id, p_transaction_id, p_instance_id,
8059: p_duration, p_plan_start_date, p_plan_end_date,
8060: p_return_status, p_out, p_validate_flag, p_node_type);
8061: return; --end this process here itself..
8062: elsif ( p_batched_res_act ) then
8060: p_return_status, p_out, p_validate_flag, p_node_type);
8061: return; --end this process here itself..
8062: elsif ( p_batched_res_act ) then
8063: put_line(' before moveResourceBatch');
8064: msc_gantt_utils.moveResourceBatch(p_plan_id, p_transaction_id, p_instance_id,
8065: p_start_date, p_end_date, p_plan_start_date, p_plan_end_date,
8066: p_return_status, p_out, p_validate_flag, p_node_type);
8067: return; --end this process here itself..
8068: end if; -- }
8105: end if;
8106:
8107: if ( p_node_type = RES_INST_NODE ) then -- {
8108: -- move res inst node
8109: msc_gantt_utils.moveOneResource(p_plan_id, ll_res_inst_trx_id, p_instance_id, p_start_date, p_end_date,
8110: p_return_status, p_out, RES_INST_NODE);
8111: if p_return_status = 'ERROR' then
8112: return;
8113: end if;
8111: if p_return_status = 'ERROR' then
8112: return;
8113: end if;
8114: -- move res node also
8115: msc_gantt_utils.moveOneResource(p_plan_id, ll_res_trx_id, p_instance_id, p_start_date, p_end_date,
8116: p_return_status, p_out, RES_NODE);
8117: if p_return_status = 'ERROR' then
8118: return;
8119: end if;
8118: return;
8119: end if;
8120: else
8121: -- move res node only
8122: msc_gantt_utils.moveOneResource(p_plan_id, ll_res_trx_id, p_instance_id, p_start_date, p_end_date,
8123: p_return_status, p_out, RES_NODE);
8124: if p_return_status = 'ERROR' then
8125: return;
8126: end if;
8124: if p_return_status = 'ERROR' then
8125: return;
8126: end if;
8127: -- move all the res instance if the resource is moved.
8128: msc_gantt_utils.moveOneResource(p_plan_id, ll_res_inst_trx_id, p_instance_id,
8129: l_start, l_end, p_return_status, p_out, RES_INST_NODE);
8130: if p_return_status = 'ERROR' then
8131: return;
8132: end if;
8132: end if;
8133: end if; -- }
8134:
8135: -- update the simultaneous resource for a given res trans id
8136: msc_gantt_utils.updateReqSimu(p_plan_id, p_instance_id, ll_res_trx_id,
8137: l_firm_flag, l_start, l_end, l_firm_start, l_firm_end, MOVE_MRR, p_return_status, p_out);
8138: -- in both the cases, res or res inst move, move the simultaneous res req ends
8139:
8140:
8172: nvl(ms.alternate_routing_designator, null_space) alternate_routing_designator,
8173: ms.organization_id org_id,
8174: nvl(to_char(msi.planning_time_fence_date, format_mask),null_space) time_fence,
8175: nvl(msc_get_name.supply_type(ms.transaction_id, p_plan_id), null_space) supply_type,
8176: decode(msc_gantt_utils.getSupplyType(ms.order_type, msi.planning_make_buy_code,
8177: ms.organization_id, ms.source_organization_id),
8178: BUY_SUPPLY, l_buy_text,
8179: TRANSFER_SUPPLY, l_transfer_text,
8180: MAKE_SUPPLY, l_make_text) item_type,
8651: rowCount number;
8652:
8653: cursor req is
8654: select
8655: to_char(msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
8656: mrr.start_date, mrr.end_date,mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied), format_mask) start_date,
8657: to_char(msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
8658: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied), format_mask) end_date,
8659: nvl(msc_get_name.job_name(mrr.supply_id, p_plan_id), to_char(mrr.supply_id)) job_name,
8653: cursor req is
8654: select
8655: to_char(msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
8656: mrr.start_date, mrr.end_date,mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied), format_mask) start_date,
8657: to_char(msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
8658: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied), format_mask) end_date,
8659: nvl(msc_get_name.job_name(mrr.supply_id, p_plan_id), to_char(mrr.supply_id)) job_name,
8660: msc_get_name.supply_type(mrr.supply_id, mrr.plan_id) supply_type,
8661: mrr.assigned_units,
8670: and nvl(mrr.firm_start_date,mrr.start_date) <= p_plan_end_date
8671: and mrr.department_id = p_dept_id
8672: and mrr.resource_id = p_res_id
8673: and to_date(p_time, format_mask)
8674: between msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
8675: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
8676: and msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
8677: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
8678: order by start_date;
8672: and mrr.resource_id = p_res_id
8673: and to_date(p_time, format_mask)
8674: between msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
8675: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
8676: and msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
8677: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
8678: order by start_date;
8679:
8680: l_req_data req%ROWTYPE;
8717: rowCount number;
8718:
8719: cursor req is
8720: select
8721: to_char(msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
8722: mrr.start_date, mrr.end_date,mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied), format_mask) start_date,
8723: to_char(msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
8724: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied), format_mask) end_date,
8725: nvl(msc_get_name.job_name(mrr.supply_id, p_plan_id), to_char(mrr.supply_id)) job_name,
8719: cursor req is
8720: select
8721: to_char(msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
8722: mrr.start_date, mrr.end_date,mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied), format_mask) start_date,
8723: to_char(msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
8724: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied), format_mask) end_date,
8725: nvl(msc_get_name.job_name(mrr.supply_id, p_plan_id), to_char(mrr.supply_id)) job_name,
8726: msc_get_name.supply_type(mrr.supply_id, mrr.plan_id) supply_type,
8727: mrr.assigned_units,
8824: end getDependencyType;
8825:
8826:
8827: procedure segmentPegging(p_query_id number, p_plan_id number,
8828: p_trx_list varchar2, p_out_data IN OUT NOCOPY msc_gantt_utils.maxCharTbl) is
8829:
8830: i number := 1;
8831:
8832: l_len number;
8873: where plan_id = p_plan
8874: and ( ( producer_sr_instance_id = p_inst and producer_trans_id = p_trx )
8875: or ( consumer_sr_instance_id = p_inst and consumer_trans_id = p_trx ) );
8876:
8877: l_producer_trans_id msc_gantt_utils.number_arr;
8878: l_producer_sr_instance_id msc_gantt_utils.number_arr;
8879: l_producer_op_seq_id msc_gantt_utils.number_arr;
8880: l_producer_op_seq_num msc_gantt_utils.number_arr;
8881: l_producer_res_seq_num msc_gantt_utils.number_arr;
8874: and ( ( producer_sr_instance_id = p_inst and producer_trans_id = p_trx )
8875: or ( consumer_sr_instance_id = p_inst and consumer_trans_id = p_trx ) );
8876:
8877: l_producer_trans_id msc_gantt_utils.number_arr;
8878: l_producer_sr_instance_id msc_gantt_utils.number_arr;
8879: l_producer_op_seq_id msc_gantt_utils.number_arr;
8880: l_producer_op_seq_num msc_gantt_utils.number_arr;
8881: l_producer_res_seq_num msc_gantt_utils.number_arr;
8882: l_consumer_trans_id msc_gantt_utils.number_arr;
8875: or ( consumer_sr_instance_id = p_inst and consumer_trans_id = p_trx ) );
8876:
8877: l_producer_trans_id msc_gantt_utils.number_arr;
8878: l_producer_sr_instance_id msc_gantt_utils.number_arr;
8879: l_producer_op_seq_id msc_gantt_utils.number_arr;
8880: l_producer_op_seq_num msc_gantt_utils.number_arr;
8881: l_producer_res_seq_num msc_gantt_utils.number_arr;
8882: l_consumer_trans_id msc_gantt_utils.number_arr;
8883: l_consumer_sr_instance_id msc_gantt_utils.number_arr;
8876:
8877: l_producer_trans_id msc_gantt_utils.number_arr;
8878: l_producer_sr_instance_id msc_gantt_utils.number_arr;
8879: l_producer_op_seq_id msc_gantt_utils.number_arr;
8880: l_producer_op_seq_num msc_gantt_utils.number_arr;
8881: l_producer_res_seq_num msc_gantt_utils.number_arr;
8882: l_consumer_trans_id msc_gantt_utils.number_arr;
8883: l_consumer_sr_instance_id msc_gantt_utils.number_arr;
8884: l_consumer_op_seq_id msc_gantt_utils.number_arr;
8877: l_producer_trans_id msc_gantt_utils.number_arr;
8878: l_producer_sr_instance_id msc_gantt_utils.number_arr;
8879: l_producer_op_seq_id msc_gantt_utils.number_arr;
8880: l_producer_op_seq_num msc_gantt_utils.number_arr;
8881: l_producer_res_seq_num msc_gantt_utils.number_arr;
8882: l_consumer_trans_id msc_gantt_utils.number_arr;
8883: l_consumer_sr_instance_id msc_gantt_utils.number_arr;
8884: l_consumer_op_seq_id msc_gantt_utils.number_arr;
8885: l_consumer_op_seq_num msc_gantt_utils.number_arr;
8878: l_producer_sr_instance_id msc_gantt_utils.number_arr;
8879: l_producer_op_seq_id msc_gantt_utils.number_arr;
8880: l_producer_op_seq_num msc_gantt_utils.number_arr;
8881: l_producer_res_seq_num msc_gantt_utils.number_arr;
8882: l_consumer_trans_id msc_gantt_utils.number_arr;
8883: l_consumer_sr_instance_id msc_gantt_utils.number_arr;
8884: l_consumer_op_seq_id msc_gantt_utils.number_arr;
8885: l_consumer_op_seq_num msc_gantt_utils.number_arr;
8886: l_consumer_res_seq_num msc_gantt_utils.number_arr;
8879: l_producer_op_seq_id msc_gantt_utils.number_arr;
8880: l_producer_op_seq_num msc_gantt_utils.number_arr;
8881: l_producer_res_seq_num msc_gantt_utils.number_arr;
8882: l_consumer_trans_id msc_gantt_utils.number_arr;
8883: l_consumer_sr_instance_id msc_gantt_utils.number_arr;
8884: l_consumer_op_seq_id msc_gantt_utils.number_arr;
8885: l_consumer_op_seq_num msc_gantt_utils.number_arr;
8886: l_consumer_res_seq_num msc_gantt_utils.number_arr;
8887: l_segment_start_date msc_gantt_utils.date_arr;
8880: l_producer_op_seq_num msc_gantt_utils.number_arr;
8881: l_producer_res_seq_num msc_gantt_utils.number_arr;
8882: l_consumer_trans_id msc_gantt_utils.number_arr;
8883: l_consumer_sr_instance_id msc_gantt_utils.number_arr;
8884: l_consumer_op_seq_id msc_gantt_utils.number_arr;
8885: l_consumer_op_seq_num msc_gantt_utils.number_arr;
8886: l_consumer_res_seq_num msc_gantt_utils.number_arr;
8887: l_segment_start_date msc_gantt_utils.date_arr;
8888: l_segment_end_date msc_gantt_utils.date_arr;
8881: l_producer_res_seq_num msc_gantt_utils.number_arr;
8882: l_consumer_trans_id msc_gantt_utils.number_arr;
8883: l_consumer_sr_instance_id msc_gantt_utils.number_arr;
8884: l_consumer_op_seq_id msc_gantt_utils.number_arr;
8885: l_consumer_op_seq_num msc_gantt_utils.number_arr;
8886: l_consumer_res_seq_num msc_gantt_utils.number_arr;
8887: l_segment_start_date msc_gantt_utils.date_arr;
8888: l_segment_end_date msc_gantt_utils.date_arr;
8889: l_segment_quantity msc_gantt_utils.number_arr;
8882: l_consumer_trans_id msc_gantt_utils.number_arr;
8883: l_consumer_sr_instance_id msc_gantt_utils.number_arr;
8884: l_consumer_op_seq_id msc_gantt_utils.number_arr;
8885: l_consumer_op_seq_num msc_gantt_utils.number_arr;
8886: l_consumer_res_seq_num msc_gantt_utils.number_arr;
8887: l_segment_start_date msc_gantt_utils.date_arr;
8888: l_segment_end_date msc_gantt_utils.date_arr;
8889: l_segment_quantity msc_gantt_utils.number_arr;
8890: l_consumer_start_date msc_gantt_utils.date_arr;
8883: l_consumer_sr_instance_id msc_gantt_utils.number_arr;
8884: l_consumer_op_seq_id msc_gantt_utils.number_arr;
8885: l_consumer_op_seq_num msc_gantt_utils.number_arr;
8886: l_consumer_res_seq_num msc_gantt_utils.number_arr;
8887: l_segment_start_date msc_gantt_utils.date_arr;
8888: l_segment_end_date msc_gantt_utils.date_arr;
8889: l_segment_quantity msc_gantt_utils.number_arr;
8890: l_consumer_start_date msc_gantt_utils.date_arr;
8891: l_consumer_end_date msc_gantt_utils.date_arr;
8884: l_consumer_op_seq_id msc_gantt_utils.number_arr;
8885: l_consumer_op_seq_num msc_gantt_utils.number_arr;
8886: l_consumer_res_seq_num msc_gantt_utils.number_arr;
8887: l_segment_start_date msc_gantt_utils.date_arr;
8888: l_segment_end_date msc_gantt_utils.date_arr;
8889: l_segment_quantity msc_gantt_utils.number_arr;
8890: l_consumer_start_date msc_gantt_utils.date_arr;
8891: l_consumer_end_date msc_gantt_utils.date_arr;
8892: l_allocation_type msc_gantt_utils.number_arr;
8885: l_consumer_op_seq_num msc_gantt_utils.number_arr;
8886: l_consumer_res_seq_num msc_gantt_utils.number_arr;
8887: l_segment_start_date msc_gantt_utils.date_arr;
8888: l_segment_end_date msc_gantt_utils.date_arr;
8889: l_segment_quantity msc_gantt_utils.number_arr;
8890: l_consumer_start_date msc_gantt_utils.date_arr;
8891: l_consumer_end_date msc_gantt_utils.date_arr;
8892: l_allocation_type msc_gantt_utils.number_arr;
8893: l_dependency_type msc_gantt_utils.number_arr;
8886: l_consumer_res_seq_num msc_gantt_utils.number_arr;
8887: l_segment_start_date msc_gantt_utils.date_arr;
8888: l_segment_end_date msc_gantt_utils.date_arr;
8889: l_segment_quantity msc_gantt_utils.number_arr;
8890: l_consumer_start_date msc_gantt_utils.date_arr;
8891: l_consumer_end_date msc_gantt_utils.date_arr;
8892: l_allocation_type msc_gantt_utils.number_arr;
8893: l_dependency_type msc_gantt_utils.number_arr;
8894: l_minimum_time_offset msc_gantt_utils.number_arr;
8887: l_segment_start_date msc_gantt_utils.date_arr;
8888: l_segment_end_date msc_gantt_utils.date_arr;
8889: l_segment_quantity msc_gantt_utils.number_arr;
8890: l_consumer_start_date msc_gantt_utils.date_arr;
8891: l_consumer_end_date msc_gantt_utils.date_arr;
8892: l_allocation_type msc_gantt_utils.number_arr;
8893: l_dependency_type msc_gantt_utils.number_arr;
8894: l_minimum_time_offset msc_gantt_utils.number_arr;
8895: l_maximum_time_offset msc_gantt_utils.number_arr;
8888: l_segment_end_date msc_gantt_utils.date_arr;
8889: l_segment_quantity msc_gantt_utils.number_arr;
8890: l_consumer_start_date msc_gantt_utils.date_arr;
8891: l_consumer_end_date msc_gantt_utils.date_arr;
8892: l_allocation_type msc_gantt_utils.number_arr;
8893: l_dependency_type msc_gantt_utils.number_arr;
8894: l_minimum_time_offset msc_gantt_utils.number_arr;
8895: l_maximum_time_offset msc_gantt_utils.number_arr;
8896: l_actual_time_offset msc_gantt_utils.number_arr;
8889: l_segment_quantity msc_gantt_utils.number_arr;
8890: l_consumer_start_date msc_gantt_utils.date_arr;
8891: l_consumer_end_date msc_gantt_utils.date_arr;
8892: l_allocation_type msc_gantt_utils.number_arr;
8893: l_dependency_type msc_gantt_utils.number_arr;
8894: l_minimum_time_offset msc_gantt_utils.number_arr;
8895: l_maximum_time_offset msc_gantt_utils.number_arr;
8896: l_actual_time_offset msc_gantt_utils.number_arr;
8897:
8890: l_consumer_start_date msc_gantt_utils.date_arr;
8891: l_consumer_end_date msc_gantt_utils.date_arr;
8892: l_allocation_type msc_gantt_utils.number_arr;
8893: l_dependency_type msc_gantt_utils.number_arr;
8894: l_minimum_time_offset msc_gantt_utils.number_arr;
8895: l_maximum_time_offset msc_gantt_utils.number_arr;
8896: l_actual_time_offset msc_gantt_utils.number_arr;
8897:
8898: begin
8891: l_consumer_end_date msc_gantt_utils.date_arr;
8892: l_allocation_type msc_gantt_utils.number_arr;
8893: l_dependency_type msc_gantt_utils.number_arr;
8894: l_minimum_time_offset msc_gantt_utils.number_arr;
8895: l_maximum_time_offset msc_gantt_utils.number_arr;
8896: l_actual_time_offset msc_gantt_utils.number_arr;
8897:
8898: begin
8899: l_len := length(p_trx_list);
8892: l_allocation_type msc_gantt_utils.number_arr;
8893: l_dependency_type msc_gantt_utils.number_arr;
8894: l_minimum_time_offset msc_gantt_utils.number_arr;
8895: l_maximum_time_offset msc_gantt_utils.number_arr;
8896: l_actual_time_offset msc_gantt_utils.number_arr;
8897:
8898: begin
8899: l_len := length(p_trx_list);
8900: while l_len > 0 loop
8934: end loop;
8935: end segmentPegging;
8936:
8937: procedure resCharges(p_query_id number, p_plan_id number,
8938: p_trx_list varchar2, p_out_data IN OUT NOCOPY msc_gantt_utils.maxCharTbl) is
8939:
8940: v_start_date msc_gantt_utils.date_arr;
8941: v_end_date msc_gantt_utils.date_arr;
8942: v_qty msc_gantt_utils.number_arr;
8936:
8937: procedure resCharges(p_query_id number, p_plan_id number,
8938: p_trx_list varchar2, p_out_data IN OUT NOCOPY msc_gantt_utils.maxCharTbl) is
8939:
8940: v_start_date msc_gantt_utils.date_arr;
8941: v_end_date msc_gantt_utils.date_arr;
8942: v_qty msc_gantt_utils.number_arr;
8943:
8944: i number := 1;
8937: procedure resCharges(p_query_id number, p_plan_id number,
8938: p_trx_list varchar2, p_out_data IN OUT NOCOPY msc_gantt_utils.maxCharTbl) is
8939:
8940: v_start_date msc_gantt_utils.date_arr;
8941: v_end_date msc_gantt_utils.date_arr;
8942: v_qty msc_gantt_utils.number_arr;
8943:
8944: i number := 1;
8945:
8938: p_trx_list varchar2, p_out_data IN OUT NOCOPY msc_gantt_utils.maxCharTbl) is
8939:
8940: v_start_date msc_gantt_utils.date_arr;
8941: v_end_date msc_gantt_utils.date_arr;
8942: v_qty msc_gantt_utils.number_arr;
8943:
8944: i number := 1;
8945:
8946: l_len number;
8986:
8987:
8988: procedure sendSupplierNames(p_query_id number,
8989: p_from_index number, p_to_index number,
8990: p_name_data IN OUT NOCOPY msc_gantt_utils.maxCharTbl,
8991: p_sort_column varchar2 default null,
8992: p_sort_order varchar2 default null) is
8993:
8994: l_node_id char_arr;
9121: end sendSupplierNames;
9122:
9123: procedure sendResourceNames(p_query_id number,
9124: p_from_index number, p_to_index number,
9125: p_name_data IN OUT NOCOPY msc_gantt_utils.maxCharTbl,
9126: p_sort_column varchar2 default null,
9127: p_sort_order varchar2 default null) is
9128:
9129: l_node_id char_arr;
9168: msc_get_name.department_code(2, res.owning_department_id,
9169: res.organization_id, res.plan_id,res.sr_instance_id)) OWN_DEPT,
9170: res.resource_code RESOURCE_CODE,
9171: msc_get_name.lookup_meaning(''BOM_RESOURCE_TYPE'',res.resource_type) RES_TYPE,
9172: msc_gantt_utils.getDeptResInstCode(res.plan_id, res.sr_instance_id, res.organization_id,
9173: res.department_id, res.resource_id, mgq.res_instance_id, mgq.serial_number) EQUIP_NUMBER
9174: from msc_department_resources res,
9175: msc_gantt_query mgq
9176: where mgq.query_id = :p_query_id
9696: end getUserPref;
9697:
9698: procedure addToOutStream(p_one_record varchar2,
9699: p_out_data_index in out nocopy number,
9700: p_out_data in out nocopy msc_gantt_utils.maxchartbl) is
9701: begin
9702: if (nvl(length(p_out_data(1)),0) = 1) then -- {
9703: p_out_data(1) := p_one_record;
9704: elsif ( nvl(length(p_out_data(p_out_data_index)),0) + length(p_one_record) < 30000 ) then
9812: end getParentLinkRec;
9813:
9814: function getResult(p_query_id number,
9815: p_from_index number, p_to_index number, p_plan_id number,
9816: p_out_data in out nocopy msc_gantt_utils.maxchartbl,
9817: p_node_level number default null,
9818: p_sort_node number default null,
9819: p_sort_column varchar2 default null,
9820: p_sort_order varchar2 default null,
9821: p_res_nodes_only varchar2 default null) return number is
9822:
9823: i number;
9824:
9825: l_row_index msc_gantt_utils.number_arr;
9826: l_item_prompt msc_gantt_utils.char_arr;
9827: l_node_type msc_gantt_utils.number_arr;
9828: l_node_path msc_gantt_utils.char_arr;
9829: l_parent_link msc_gantt_utils.char_arr;
9822:
9823: i number;
9824:
9825: l_row_index msc_gantt_utils.number_arr;
9826: l_item_prompt msc_gantt_utils.char_arr;
9827: l_node_type msc_gantt_utils.number_arr;
9828: l_node_path msc_gantt_utils.char_arr;
9829: l_parent_link msc_gantt_utils.char_arr;
9830: l_start_date msc_gantt_utils.date_arr;
9823: i number;
9824:
9825: l_row_index msc_gantt_utils.number_arr;
9826: l_item_prompt msc_gantt_utils.char_arr;
9827: l_node_type msc_gantt_utils.number_arr;
9828: l_node_path msc_gantt_utils.char_arr;
9829: l_parent_link msc_gantt_utils.char_arr;
9830: l_start_date msc_gantt_utils.date_arr;
9831: l_start_date_char msc_gantt_utils.char_arr;
9824:
9825: l_row_index msc_gantt_utils.number_arr;
9826: l_item_prompt msc_gantt_utils.char_arr;
9827: l_node_type msc_gantt_utils.number_arr;
9828: l_node_path msc_gantt_utils.char_arr;
9829: l_parent_link msc_gantt_utils.char_arr;
9830: l_start_date msc_gantt_utils.date_arr;
9831: l_start_date_char msc_gantt_utils.char_arr;
9832: l_end_date msc_gantt_utils.date_arr;
9825: l_row_index msc_gantt_utils.number_arr;
9826: l_item_prompt msc_gantt_utils.char_arr;
9827: l_node_type msc_gantt_utils.number_arr;
9828: l_node_path msc_gantt_utils.char_arr;
9829: l_parent_link msc_gantt_utils.char_arr;
9830: l_start_date msc_gantt_utils.date_arr;
9831: l_start_date_char msc_gantt_utils.char_arr;
9832: l_end_date msc_gantt_utils.date_arr;
9833: l_priority msc_gantt_utils.number_arr;
9826: l_item_prompt msc_gantt_utils.char_arr;
9827: l_node_type msc_gantt_utils.number_arr;
9828: l_node_path msc_gantt_utils.char_arr;
9829: l_parent_link msc_gantt_utils.char_arr;
9830: l_start_date msc_gantt_utils.date_arr;
9831: l_start_date_char msc_gantt_utils.char_arr;
9832: l_end_date msc_gantt_utils.date_arr;
9833: l_priority msc_gantt_utils.number_arr;
9834:
9827: l_node_type msc_gantt_utils.number_arr;
9828: l_node_path msc_gantt_utils.char_arr;
9829: l_parent_link msc_gantt_utils.char_arr;
9830: l_start_date msc_gantt_utils.date_arr;
9831: l_start_date_char msc_gantt_utils.char_arr;
9832: l_end_date msc_gantt_utils.date_arr;
9833: l_priority msc_gantt_utils.number_arr;
9834:
9835: l_from_setup_code char_arr;
9828: l_node_path msc_gantt_utils.char_arr;
9829: l_parent_link msc_gantt_utils.char_arr;
9830: l_start_date msc_gantt_utils.date_arr;
9831: l_start_date_char msc_gantt_utils.char_arr;
9832: l_end_date msc_gantt_utils.date_arr;
9833: l_priority msc_gantt_utils.number_arr;
9834:
9835: l_from_setup_code char_arr;
9836: l_to_setup_code char_arr;
9829: l_parent_link msc_gantt_utils.char_arr;
9830: l_start_date msc_gantt_utils.date_arr;
9831: l_start_date_char msc_gantt_utils.char_arr;
9832: l_end_date msc_gantt_utils.date_arr;
9833: l_priority msc_gantt_utils.number_arr;
9834:
9835: l_from_setup_code char_arr;
9836: l_to_setup_code char_arr;
9837: l_std_op_code char_arr;
9837: l_std_op_code char_arr;
9838: l_changeover_time char_arr;
9839: l_changeover_penalty char_arr;
9840:
9841: l_critical_flag msc_gantt_utils.number_arr;
9842: l_item_name msc_gantt_utils.char_arr;
9843: l_org_code msc_gantt_utils.char_arr;
9844: l_order_type msc_gantt_utils.char_arr;
9845: l_order_type_text msc_gantt_utils.char_arr;
9838: l_changeover_time char_arr;
9839: l_changeover_penalty char_arr;
9840:
9841: l_critical_flag msc_gantt_utils.number_arr;
9842: l_item_name msc_gantt_utils.char_arr;
9843: l_org_code msc_gantt_utils.char_arr;
9844: l_order_type msc_gantt_utils.char_arr;
9845: l_order_type_text msc_gantt_utils.char_arr;
9846: l_order_number msc_gantt_utils.char_arr;
9839: l_changeover_penalty char_arr;
9840:
9841: l_critical_flag msc_gantt_utils.number_arr;
9842: l_item_name msc_gantt_utils.char_arr;
9843: l_org_code msc_gantt_utils.char_arr;
9844: l_order_type msc_gantt_utils.char_arr;
9845: l_order_type_text msc_gantt_utils.char_arr;
9846: l_order_number msc_gantt_utils.char_arr;
9847: l_qty msc_gantt_utils.number_arr;
9840:
9841: l_critical_flag msc_gantt_utils.number_arr;
9842: l_item_name msc_gantt_utils.char_arr;
9843: l_org_code msc_gantt_utils.char_arr;
9844: l_order_type msc_gantt_utils.char_arr;
9845: l_order_type_text msc_gantt_utils.char_arr;
9846: l_order_number msc_gantt_utils.char_arr;
9847: l_qty msc_gantt_utils.number_arr;
9848: l_cepst msc_gantt_utils.date_arr;
9841: l_critical_flag msc_gantt_utils.number_arr;
9842: l_item_name msc_gantt_utils.char_arr;
9843: l_org_code msc_gantt_utils.char_arr;
9844: l_order_type msc_gantt_utils.char_arr;
9845: l_order_type_text msc_gantt_utils.char_arr;
9846: l_order_number msc_gantt_utils.char_arr;
9847: l_qty msc_gantt_utils.number_arr;
9848: l_cepst msc_gantt_utils.date_arr;
9849: l_cepct msc_gantt_utils.date_arr;
9842: l_item_name msc_gantt_utils.char_arr;
9843: l_org_code msc_gantt_utils.char_arr;
9844: l_order_type msc_gantt_utils.char_arr;
9845: l_order_type_text msc_gantt_utils.char_arr;
9846: l_order_number msc_gantt_utils.char_arr;
9847: l_qty msc_gantt_utils.number_arr;
9848: l_cepst msc_gantt_utils.date_arr;
9849: l_cepct msc_gantt_utils.date_arr;
9850: l_ulpst msc_gantt_utils.date_arr;
9843: l_org_code msc_gantt_utils.char_arr;
9844: l_order_type msc_gantt_utils.char_arr;
9845: l_order_type_text msc_gantt_utils.char_arr;
9846: l_order_number msc_gantt_utils.char_arr;
9847: l_qty msc_gantt_utils.number_arr;
9848: l_cepst msc_gantt_utils.date_arr;
9849: l_cepct msc_gantt_utils.date_arr;
9850: l_ulpst msc_gantt_utils.date_arr;
9851: l_ulpct msc_gantt_utils.date_arr;
9844: l_order_type msc_gantt_utils.char_arr;
9845: l_order_type_text msc_gantt_utils.char_arr;
9846: l_order_number msc_gantt_utils.char_arr;
9847: l_qty msc_gantt_utils.number_arr;
9848: l_cepst msc_gantt_utils.date_arr;
9849: l_cepct msc_gantt_utils.date_arr;
9850: l_ulpst msc_gantt_utils.date_arr;
9851: l_ulpct msc_gantt_utils.date_arr;
9852: l_uepst msc_gantt_utils.date_arr;
9845: l_order_type_text msc_gantt_utils.char_arr;
9846: l_order_number msc_gantt_utils.char_arr;
9847: l_qty msc_gantt_utils.number_arr;
9848: l_cepst msc_gantt_utils.date_arr;
9849: l_cepct msc_gantt_utils.date_arr;
9850: l_ulpst msc_gantt_utils.date_arr;
9851: l_ulpct msc_gantt_utils.date_arr;
9852: l_uepst msc_gantt_utils.date_arr;
9853: l_uepct msc_gantt_utils.date_arr;
9846: l_order_number msc_gantt_utils.char_arr;
9847: l_qty msc_gantt_utils.number_arr;
9848: l_cepst msc_gantt_utils.date_arr;
9849: l_cepct msc_gantt_utils.date_arr;
9850: l_ulpst msc_gantt_utils.date_arr;
9851: l_ulpct msc_gantt_utils.date_arr;
9852: l_uepst msc_gantt_utils.date_arr;
9853: l_uepct msc_gantt_utils.date_arr;
9854: l_min_start_time msc_gantt_utils.date_arr;
9847: l_qty msc_gantt_utils.number_arr;
9848: l_cepst msc_gantt_utils.date_arr;
9849: l_cepct msc_gantt_utils.date_arr;
9850: l_ulpst msc_gantt_utils.date_arr;
9851: l_ulpct msc_gantt_utils.date_arr;
9852: l_uepst msc_gantt_utils.date_arr;
9853: l_uepct msc_gantt_utils.date_arr;
9854: l_min_start_time msc_gantt_utils.date_arr;
9855: l_eacd msc_gantt_utils.date_arr;
9848: l_cepst msc_gantt_utils.date_arr;
9849: l_cepct msc_gantt_utils.date_arr;
9850: l_ulpst msc_gantt_utils.date_arr;
9851: l_ulpct msc_gantt_utils.date_arr;
9852: l_uepst msc_gantt_utils.date_arr;
9853: l_uepct msc_gantt_utils.date_arr;
9854: l_min_start_time msc_gantt_utils.date_arr;
9855: l_eacd msc_gantt_utils.date_arr;
9856: l_req_start_date msc_gantt_utils.date_arr;
9849: l_cepct msc_gantt_utils.date_arr;
9850: l_ulpst msc_gantt_utils.date_arr;
9851: l_ulpct msc_gantt_utils.date_arr;
9852: l_uepst msc_gantt_utils.date_arr;
9853: l_uepct msc_gantt_utils.date_arr;
9854: l_min_start_time msc_gantt_utils.date_arr;
9855: l_eacd msc_gantt_utils.date_arr;
9856: l_req_start_date msc_gantt_utils.date_arr;
9857: l_req_end_date msc_gantt_utils.date_arr;
9850: l_ulpst msc_gantt_utils.date_arr;
9851: l_ulpct msc_gantt_utils.date_arr;
9852: l_uepst msc_gantt_utils.date_arr;
9853: l_uepct msc_gantt_utils.date_arr;
9854: l_min_start_time msc_gantt_utils.date_arr;
9855: l_eacd msc_gantt_utils.date_arr;
9856: l_req_start_date msc_gantt_utils.date_arr;
9857: l_req_end_date msc_gantt_utils.date_arr;
9858: l_req_due_date msc_gantt_utils.date_arr;
9851: l_ulpct msc_gantt_utils.date_arr;
9852: l_uepst msc_gantt_utils.date_arr;
9853: l_uepct msc_gantt_utils.date_arr;
9854: l_min_start_time msc_gantt_utils.date_arr;
9855: l_eacd msc_gantt_utils.date_arr;
9856: l_req_start_date msc_gantt_utils.date_arr;
9857: l_req_end_date msc_gantt_utils.date_arr;
9858: l_req_due_date msc_gantt_utils.date_arr;
9859: l_item_type msc_gantt_utils.char_arr;
9852: l_uepst msc_gantt_utils.date_arr;
9853: l_uepct msc_gantt_utils.date_arr;
9854: l_min_start_time msc_gantt_utils.date_arr;
9855: l_eacd msc_gantt_utils.date_arr;
9856: l_req_start_date msc_gantt_utils.date_arr;
9857: l_req_end_date msc_gantt_utils.date_arr;
9858: l_req_due_date msc_gantt_utils.date_arr;
9859: l_item_type msc_gantt_utils.char_arr;
9860: l_res_req_type msc_gantt_utils.char_arr;
9853: l_uepct msc_gantt_utils.date_arr;
9854: l_min_start_time msc_gantt_utils.date_arr;
9855: l_eacd msc_gantt_utils.date_arr;
9856: l_req_start_date msc_gantt_utils.date_arr;
9857: l_req_end_date msc_gantt_utils.date_arr;
9858: l_req_due_date msc_gantt_utils.date_arr;
9859: l_item_type msc_gantt_utils.char_arr;
9860: l_res_req_type msc_gantt_utils.char_arr;
9861: l_supply_firm_type msc_gantt_utils.number_arr;
9854: l_min_start_time msc_gantt_utils.date_arr;
9855: l_eacd msc_gantt_utils.date_arr;
9856: l_req_start_date msc_gantt_utils.date_arr;
9857: l_req_end_date msc_gantt_utils.date_arr;
9858: l_req_due_date msc_gantt_utils.date_arr;
9859: l_item_type msc_gantt_utils.char_arr;
9860: l_res_req_type msc_gantt_utils.char_arr;
9861: l_supply_firm_type msc_gantt_utils.number_arr;
9862: l_res_firm_type msc_gantt_utils.number_arr;
9855: l_eacd msc_gantt_utils.date_arr;
9856: l_req_start_date msc_gantt_utils.date_arr;
9857: l_req_end_date msc_gantt_utils.date_arr;
9858: l_req_due_date msc_gantt_utils.date_arr;
9859: l_item_type msc_gantt_utils.char_arr;
9860: l_res_req_type msc_gantt_utils.char_arr;
9861: l_supply_firm_type msc_gantt_utils.number_arr;
9862: l_res_firm_type msc_gantt_utils.number_arr;
9863: l_status msc_gantt_utils.number_arr;
9856: l_req_start_date msc_gantt_utils.date_arr;
9857: l_req_end_date msc_gantt_utils.date_arr;
9858: l_req_due_date msc_gantt_utils.date_arr;
9859: l_item_type msc_gantt_utils.char_arr;
9860: l_res_req_type msc_gantt_utils.char_arr;
9861: l_supply_firm_type msc_gantt_utils.number_arr;
9862: l_res_firm_type msc_gantt_utils.number_arr;
9863: l_status msc_gantt_utils.number_arr;
9864: l_applied msc_gantt_utils.number_arr;
9857: l_req_end_date msc_gantt_utils.date_arr;
9858: l_req_due_date msc_gantt_utils.date_arr;
9859: l_item_type msc_gantt_utils.char_arr;
9860: l_res_req_type msc_gantt_utils.char_arr;
9861: l_supply_firm_type msc_gantt_utils.number_arr;
9862: l_res_firm_type msc_gantt_utils.number_arr;
9863: l_status msc_gantt_utils.number_arr;
9864: l_applied msc_gantt_utils.number_arr;
9865: l_supplier_id msc_gantt_utils.number_arr;
9858: l_req_due_date msc_gantt_utils.date_arr;
9859: l_item_type msc_gantt_utils.char_arr;
9860: l_res_req_type msc_gantt_utils.char_arr;
9861: l_supply_firm_type msc_gantt_utils.number_arr;
9862: l_res_firm_type msc_gantt_utils.number_arr;
9863: l_status msc_gantt_utils.number_arr;
9864: l_applied msc_gantt_utils.number_arr;
9865: l_supplier_id msc_gantt_utils.number_arr;
9866: l_supplier_site_id msc_gantt_utils.number_arr;
9859: l_item_type msc_gantt_utils.char_arr;
9860: l_res_req_type msc_gantt_utils.char_arr;
9861: l_supply_firm_type msc_gantt_utils.number_arr;
9862: l_res_firm_type msc_gantt_utils.number_arr;
9863: l_status msc_gantt_utils.number_arr;
9864: l_applied msc_gantt_utils.number_arr;
9865: l_supplier_id msc_gantt_utils.number_arr;
9866: l_supplier_site_id msc_gantt_utils.number_arr;
9867: l_item_desc msc_gantt_utils.char_arr;
9860: l_res_req_type msc_gantt_utils.char_arr;
9861: l_supply_firm_type msc_gantt_utils.number_arr;
9862: l_res_firm_type msc_gantt_utils.number_arr;
9863: l_status msc_gantt_utils.number_arr;
9864: l_applied msc_gantt_utils.number_arr;
9865: l_supplier_id msc_gantt_utils.number_arr;
9866: l_supplier_site_id msc_gantt_utils.number_arr;
9867: l_item_desc msc_gantt_utils.char_arr;
9868: l_assy_item_desc msc_gantt_utils.char_arr;
9861: l_supply_firm_type msc_gantt_utils.number_arr;
9862: l_res_firm_type msc_gantt_utils.number_arr;
9863: l_status msc_gantt_utils.number_arr;
9864: l_applied msc_gantt_utils.number_arr;
9865: l_supplier_id msc_gantt_utils.number_arr;
9866: l_supplier_site_id msc_gantt_utils.number_arr;
9867: l_item_desc msc_gantt_utils.char_arr;
9868: l_assy_item_desc msc_gantt_utils.char_arr;
9869: l_category_name msc_gantt_utils.char_arr;
9862: l_res_firm_type msc_gantt_utils.number_arr;
9863: l_status msc_gantt_utils.number_arr;
9864: l_applied msc_gantt_utils.number_arr;
9865: l_supplier_id msc_gantt_utils.number_arr;
9866: l_supplier_site_id msc_gantt_utils.number_arr;
9867: l_item_desc msc_gantt_utils.char_arr;
9868: l_assy_item_desc msc_gantt_utils.char_arr;
9869: l_category_name msc_gantt_utils.char_arr;
9870: l_prod_family msc_gantt_utils.char_arr;
9863: l_status msc_gantt_utils.number_arr;
9864: l_applied msc_gantt_utils.number_arr;
9865: l_supplier_id msc_gantt_utils.number_arr;
9866: l_supplier_site_id msc_gantt_utils.number_arr;
9867: l_item_desc msc_gantt_utils.char_arr;
9868: l_assy_item_desc msc_gantt_utils.char_arr;
9869: l_category_name msc_gantt_utils.char_arr;
9870: l_prod_family msc_gantt_utils.char_arr;
9871: l_planner_code msc_gantt_utils.char_arr;
9864: l_applied msc_gantt_utils.number_arr;
9865: l_supplier_id msc_gantt_utils.number_arr;
9866: l_supplier_site_id msc_gantt_utils.number_arr;
9867: l_item_desc msc_gantt_utils.char_arr;
9868: l_assy_item_desc msc_gantt_utils.char_arr;
9869: l_category_name msc_gantt_utils.char_arr;
9870: l_prod_family msc_gantt_utils.char_arr;
9871: l_planner_code msc_gantt_utils.char_arr;
9872: l_planning_group msc_gantt_utils.char_arr;
9865: l_supplier_id msc_gantt_utils.number_arr;
9866: l_supplier_site_id msc_gantt_utils.number_arr;
9867: l_item_desc msc_gantt_utils.char_arr;
9868: l_assy_item_desc msc_gantt_utils.char_arr;
9869: l_category_name msc_gantt_utils.char_arr;
9870: l_prod_family msc_gantt_utils.char_arr;
9871: l_planner_code msc_gantt_utils.char_arr;
9872: l_planning_group msc_gantt_utils.char_arr;
9873: l_project msc_gantt_utils.char_arr;
9866: l_supplier_site_id msc_gantt_utils.number_arr;
9867: l_item_desc msc_gantt_utils.char_arr;
9868: l_assy_item_desc msc_gantt_utils.char_arr;
9869: l_category_name msc_gantt_utils.char_arr;
9870: l_prod_family msc_gantt_utils.char_arr;
9871: l_planner_code msc_gantt_utils.char_arr;
9872: l_planning_group msc_gantt_utils.char_arr;
9873: l_project msc_gantt_utils.char_arr;
9874: l_task msc_gantt_utils.char_arr;
9867: l_item_desc msc_gantt_utils.char_arr;
9868: l_assy_item_desc msc_gantt_utils.char_arr;
9869: l_category_name msc_gantt_utils.char_arr;
9870: l_prod_family msc_gantt_utils.char_arr;
9871: l_planner_code msc_gantt_utils.char_arr;
9872: l_planning_group msc_gantt_utils.char_arr;
9873: l_project msc_gantt_utils.char_arr;
9874: l_task msc_gantt_utils.char_arr;
9875: l_sugg_ship_date msc_gantt_utils.date_arr;
9868: l_assy_item_desc msc_gantt_utils.char_arr;
9869: l_category_name msc_gantt_utils.char_arr;
9870: l_prod_family msc_gantt_utils.char_arr;
9871: l_planner_code msc_gantt_utils.char_arr;
9872: l_planning_group msc_gantt_utils.char_arr;
9873: l_project msc_gantt_utils.char_arr;
9874: l_task msc_gantt_utils.char_arr;
9875: l_sugg_ship_date msc_gantt_utils.date_arr;
9876: l_sugg_due_date msc_gantt_utils.date_arr;
9869: l_category_name msc_gantt_utils.char_arr;
9870: l_prod_family msc_gantt_utils.char_arr;
9871: l_planner_code msc_gantt_utils.char_arr;
9872: l_planning_group msc_gantt_utils.char_arr;
9873: l_project msc_gantt_utils.char_arr;
9874: l_task msc_gantt_utils.char_arr;
9875: l_sugg_ship_date msc_gantt_utils.date_arr;
9876: l_sugg_due_date msc_gantt_utils.date_arr;
9877: l_sugg_order_date msc_gantt_utils.date_arr;
9870: l_prod_family msc_gantt_utils.char_arr;
9871: l_planner_code msc_gantt_utils.char_arr;
9872: l_planning_group msc_gantt_utils.char_arr;
9873: l_project msc_gantt_utils.char_arr;
9874: l_task msc_gantt_utils.char_arr;
9875: l_sugg_ship_date msc_gantt_utils.date_arr;
9876: l_sugg_due_date msc_gantt_utils.date_arr;
9877: l_sugg_order_date msc_gantt_utils.date_arr;
9878: l_sugg_start_date msc_gantt_utils.date_arr;
9871: l_planner_code msc_gantt_utils.char_arr;
9872: l_planning_group msc_gantt_utils.char_arr;
9873: l_project msc_gantt_utils.char_arr;
9874: l_task msc_gantt_utils.char_arr;
9875: l_sugg_ship_date msc_gantt_utils.date_arr;
9876: l_sugg_due_date msc_gantt_utils.date_arr;
9877: l_sugg_order_date msc_gantt_utils.date_arr;
9878: l_sugg_start_date msc_gantt_utils.date_arr;
9879: l_sugg_dock_date msc_gantt_utils.date_arr;
9872: l_planning_group msc_gantt_utils.char_arr;
9873: l_project msc_gantt_utils.char_arr;
9874: l_task msc_gantt_utils.char_arr;
9875: l_sugg_ship_date msc_gantt_utils.date_arr;
9876: l_sugg_due_date msc_gantt_utils.date_arr;
9877: l_sugg_order_date msc_gantt_utils.date_arr;
9878: l_sugg_start_date msc_gantt_utils.date_arr;
9879: l_sugg_dock_date msc_gantt_utils.date_arr;
9880: l_ship_method msc_gantt_utils.char_arr;
9873: l_project msc_gantt_utils.char_arr;
9874: l_task msc_gantt_utils.char_arr;
9875: l_sugg_ship_date msc_gantt_utils.date_arr;
9876: l_sugg_due_date msc_gantt_utils.date_arr;
9877: l_sugg_order_date msc_gantt_utils.date_arr;
9878: l_sugg_start_date msc_gantt_utils.date_arr;
9879: l_sugg_dock_date msc_gantt_utils.date_arr;
9880: l_ship_method msc_gantt_utils.char_arr;
9881: l_transaction_id msc_gantt_utils.number_arr;
9874: l_task msc_gantt_utils.char_arr;
9875: l_sugg_ship_date msc_gantt_utils.date_arr;
9876: l_sugg_due_date msc_gantt_utils.date_arr;
9877: l_sugg_order_date msc_gantt_utils.date_arr;
9878: l_sugg_start_date msc_gantt_utils.date_arr;
9879: l_sugg_dock_date msc_gantt_utils.date_arr;
9880: l_ship_method msc_gantt_utils.char_arr;
9881: l_transaction_id msc_gantt_utils.number_arr;
9882: l_res_transaction_id msc_gantt_utils.number_arr;
9875: l_sugg_ship_date msc_gantt_utils.date_arr;
9876: l_sugg_due_date msc_gantt_utils.date_arr;
9877: l_sugg_order_date msc_gantt_utils.date_arr;
9878: l_sugg_start_date msc_gantt_utils.date_arr;
9879: l_sugg_dock_date msc_gantt_utils.date_arr;
9880: l_ship_method msc_gantt_utils.char_arr;
9881: l_transaction_id msc_gantt_utils.number_arr;
9882: l_res_transaction_id msc_gantt_utils.number_arr;
9883: l_customer msc_gantt_utils.char_arr;
9876: l_sugg_due_date msc_gantt_utils.date_arr;
9877: l_sugg_order_date msc_gantt_utils.date_arr;
9878: l_sugg_start_date msc_gantt_utils.date_arr;
9879: l_sugg_dock_date msc_gantt_utils.date_arr;
9880: l_ship_method msc_gantt_utils.char_arr;
9881: l_transaction_id msc_gantt_utils.number_arr;
9882: l_res_transaction_id msc_gantt_utils.number_arr;
9883: l_customer msc_gantt_utils.char_arr;
9884: l_customer_site msc_gantt_utils.char_arr;
9877: l_sugg_order_date msc_gantt_utils.date_arr;
9878: l_sugg_start_date msc_gantt_utils.date_arr;
9879: l_sugg_dock_date msc_gantt_utils.date_arr;
9880: l_ship_method msc_gantt_utils.char_arr;
9881: l_transaction_id msc_gantt_utils.number_arr;
9882: l_res_transaction_id msc_gantt_utils.number_arr;
9883: l_customer msc_gantt_utils.char_arr;
9884: l_customer_site msc_gantt_utils.char_arr;
9885: l_source_org msc_gantt_utils.char_arr;
9878: l_sugg_start_date msc_gantt_utils.date_arr;
9879: l_sugg_dock_date msc_gantt_utils.date_arr;
9880: l_ship_method msc_gantt_utils.char_arr;
9881: l_transaction_id msc_gantt_utils.number_arr;
9882: l_res_transaction_id msc_gantt_utils.number_arr;
9883: l_customer msc_gantt_utils.char_arr;
9884: l_customer_site msc_gantt_utils.char_arr;
9885: l_source_org msc_gantt_utils.char_arr;
9886: l_supplier msc_gantt_utils.char_arr;
9879: l_sugg_dock_date msc_gantt_utils.date_arr;
9880: l_ship_method msc_gantt_utils.char_arr;
9881: l_transaction_id msc_gantt_utils.number_arr;
9882: l_res_transaction_id msc_gantt_utils.number_arr;
9883: l_customer msc_gantt_utils.char_arr;
9884: l_customer_site msc_gantt_utils.char_arr;
9885: l_source_org msc_gantt_utils.char_arr;
9886: l_supplier msc_gantt_utils.char_arr;
9887: l_supplier_site msc_gantt_utils.char_arr;
9880: l_ship_method msc_gantt_utils.char_arr;
9881: l_transaction_id msc_gantt_utils.number_arr;
9882: l_res_transaction_id msc_gantt_utils.number_arr;
9883: l_customer msc_gantt_utils.char_arr;
9884: l_customer_site msc_gantt_utils.char_arr;
9885: l_source_org msc_gantt_utils.char_arr;
9886: l_supplier msc_gantt_utils.char_arr;
9887: l_supplier_site msc_gantt_utils.char_arr;
9888: l_sched_group msc_gantt_utils.char_arr;
9881: l_transaction_id msc_gantt_utils.number_arr;
9882: l_res_transaction_id msc_gantt_utils.number_arr;
9883: l_customer msc_gantt_utils.char_arr;
9884: l_customer_site msc_gantt_utils.char_arr;
9885: l_source_org msc_gantt_utils.char_arr;
9886: l_supplier msc_gantt_utils.char_arr;
9887: l_supplier_site msc_gantt_utils.char_arr;
9888: l_sched_group msc_gantt_utils.char_arr;
9889: l_actual_start_date msc_gantt_utils.date_arr;
9882: l_res_transaction_id msc_gantt_utils.number_arr;
9883: l_customer msc_gantt_utils.char_arr;
9884: l_customer_site msc_gantt_utils.char_arr;
9885: l_source_org msc_gantt_utils.char_arr;
9886: l_supplier msc_gantt_utils.char_arr;
9887: l_supplier_site msc_gantt_utils.char_arr;
9888: l_sched_group msc_gantt_utils.char_arr;
9889: l_actual_start_date msc_gantt_utils.date_arr;
9890:
9883: l_customer msc_gantt_utils.char_arr;
9884: l_customer_site msc_gantt_utils.char_arr;
9885: l_source_org msc_gantt_utils.char_arr;
9886: l_supplier msc_gantt_utils.char_arr;
9887: l_supplier_site msc_gantt_utils.char_arr;
9888: l_sched_group msc_gantt_utils.char_arr;
9889: l_actual_start_date msc_gantt_utils.date_arr;
9890:
9891: l_sr_instance_id msc_gantt_utils.number_arr;
9884: l_customer_site msc_gantt_utils.char_arr;
9885: l_source_org msc_gantt_utils.char_arr;
9886: l_supplier msc_gantt_utils.char_arr;
9887: l_supplier_site msc_gantt_utils.char_arr;
9888: l_sched_group msc_gantt_utils.char_arr;
9889: l_actual_start_date msc_gantt_utils.date_arr;
9890:
9891: l_sr_instance_id msc_gantt_utils.number_arr;
9892: l_organization_id msc_gantt_utils.number_arr;
9885: l_source_org msc_gantt_utils.char_arr;
9886: l_supplier msc_gantt_utils.char_arr;
9887: l_supplier_site msc_gantt_utils.char_arr;
9888: l_sched_group msc_gantt_utils.char_arr;
9889: l_actual_start_date msc_gantt_utils.date_arr;
9890:
9891: l_sr_instance_id msc_gantt_utils.number_arr;
9892: l_organization_id msc_gantt_utils.number_arr;
9893: l_inventory_item_id msc_gantt_utils.number_arr;
9887: l_supplier_site msc_gantt_utils.char_arr;
9888: l_sched_group msc_gantt_utils.char_arr;
9889: l_actual_start_date msc_gantt_utils.date_arr;
9890:
9891: l_sr_instance_id msc_gantt_utils.number_arr;
9892: l_organization_id msc_gantt_utils.number_arr;
9893: l_inventory_item_id msc_gantt_utils.number_arr;
9894: l_dept_id msc_gantt_utils.number_arr;
9895: l_res_id msc_gantt_utils.number_arr;
9888: l_sched_group msc_gantt_utils.char_arr;
9889: l_actual_start_date msc_gantt_utils.date_arr;
9890:
9891: l_sr_instance_id msc_gantt_utils.number_arr;
9892: l_organization_id msc_gantt_utils.number_arr;
9893: l_inventory_item_id msc_gantt_utils.number_arr;
9894: l_dept_id msc_gantt_utils.number_arr;
9895: l_res_id msc_gantt_utils.number_arr;
9896:
9889: l_actual_start_date msc_gantt_utils.date_arr;
9890:
9891: l_sr_instance_id msc_gantt_utils.number_arr;
9892: l_organization_id msc_gantt_utils.number_arr;
9893: l_inventory_item_id msc_gantt_utils.number_arr;
9894: l_dept_id msc_gantt_utils.number_arr;
9895: l_res_id msc_gantt_utils.number_arr;
9896:
9897: l_dept_code msc_gantt_utils.char_arr;
9890:
9891: l_sr_instance_id msc_gantt_utils.number_arr;
9892: l_organization_id msc_gantt_utils.number_arr;
9893: l_inventory_item_id msc_gantt_utils.number_arr;
9894: l_dept_id msc_gantt_utils.number_arr;
9895: l_res_id msc_gantt_utils.number_arr;
9896:
9897: l_dept_code msc_gantt_utils.char_arr;
9898: l_res_code msc_gantt_utils.char_arr;
9891: l_sr_instance_id msc_gantt_utils.number_arr;
9892: l_organization_id msc_gantt_utils.number_arr;
9893: l_inventory_item_id msc_gantt_utils.number_arr;
9894: l_dept_id msc_gantt_utils.number_arr;
9895: l_res_id msc_gantt_utils.number_arr;
9896:
9897: l_dept_code msc_gantt_utils.char_arr;
9898: l_res_code msc_gantt_utils.char_arr;
9899: l_res_desc msc_gantt_utils.char_arr;
9893: l_inventory_item_id msc_gantt_utils.number_arr;
9894: l_dept_id msc_gantt_utils.number_arr;
9895: l_res_id msc_gantt_utils.number_arr;
9896:
9897: l_dept_code msc_gantt_utils.char_arr;
9898: l_res_code msc_gantt_utils.char_arr;
9899: l_res_desc msc_gantt_utils.char_arr;
9900: l_op_seq msc_gantt_utils.char_arr;
9901: l_op_desc msc_gantt_utils.char_arr;
9894: l_dept_id msc_gantt_utils.number_arr;
9895: l_res_id msc_gantt_utils.number_arr;
9896:
9897: l_dept_code msc_gantt_utils.char_arr;
9898: l_res_code msc_gantt_utils.char_arr;
9899: l_res_desc msc_gantt_utils.char_arr;
9900: l_op_seq msc_gantt_utils.char_arr;
9901: l_op_desc msc_gantt_utils.char_arr;
9902: l_adjusted_res_hours msc_gantt_utils.number_arr;
9895: l_res_id msc_gantt_utils.number_arr;
9896:
9897: l_dept_code msc_gantt_utils.char_arr;
9898: l_res_code msc_gantt_utils.char_arr;
9899: l_res_desc msc_gantt_utils.char_arr;
9900: l_op_seq msc_gantt_utils.char_arr;
9901: l_op_desc msc_gantt_utils.char_arr;
9902: l_adjusted_res_hours msc_gantt_utils.number_arr;
9903: l_schedule_qty msc_gantt_utils.number_arr;
9896:
9897: l_dept_code msc_gantt_utils.char_arr;
9898: l_res_code msc_gantt_utils.char_arr;
9899: l_res_desc msc_gantt_utils.char_arr;
9900: l_op_seq msc_gantt_utils.char_arr;
9901: l_op_desc msc_gantt_utils.char_arr;
9902: l_adjusted_res_hours msc_gantt_utils.number_arr;
9903: l_schedule_qty msc_gantt_utils.number_arr;
9904: l_batch_number msc_gantt_utils.char_arr;
9897: l_dept_code msc_gantt_utils.char_arr;
9898: l_res_code msc_gantt_utils.char_arr;
9899: l_res_desc msc_gantt_utils.char_arr;
9900: l_op_seq msc_gantt_utils.char_arr;
9901: l_op_desc msc_gantt_utils.char_arr;
9902: l_adjusted_res_hours msc_gantt_utils.number_arr;
9903: l_schedule_qty msc_gantt_utils.number_arr;
9904: l_batch_number msc_gantt_utils.char_arr;
9905: l_assigned_units msc_gantt_utils.number_arr;
9898: l_res_code msc_gantt_utils.char_arr;
9899: l_res_desc msc_gantt_utils.char_arr;
9900: l_op_seq msc_gantt_utils.char_arr;
9901: l_op_desc msc_gantt_utils.char_arr;
9902: l_adjusted_res_hours msc_gantt_utils.number_arr;
9903: l_schedule_qty msc_gantt_utils.number_arr;
9904: l_batch_number msc_gantt_utils.char_arr;
9905: l_assigned_units msc_gantt_utils.number_arr;
9906: l_alternate_flag msc_gantt_utils.char_arr;
9899: l_res_desc msc_gantt_utils.char_arr;
9900: l_op_seq msc_gantt_utils.char_arr;
9901: l_op_desc msc_gantt_utils.char_arr;
9902: l_adjusted_res_hours msc_gantt_utils.number_arr;
9903: l_schedule_qty msc_gantt_utils.number_arr;
9904: l_batch_number msc_gantt_utils.char_arr;
9905: l_assigned_units msc_gantt_utils.number_arr;
9906: l_alternate_flag msc_gantt_utils.char_arr;
9907: l_res_seq msc_gantt_utils.char_arr;
9900: l_op_seq msc_gantt_utils.char_arr;
9901: l_op_desc msc_gantt_utils.char_arr;
9902: l_adjusted_res_hours msc_gantt_utils.number_arr;
9903: l_schedule_qty msc_gantt_utils.number_arr;
9904: l_batch_number msc_gantt_utils.char_arr;
9905: l_assigned_units msc_gantt_utils.number_arr;
9906: l_alternate_flag msc_gantt_utils.char_arr;
9907: l_res_seq msc_gantt_utils.char_arr;
9908: l_wip_status_text msc_gantt_utils.char_arr;
9901: l_op_desc msc_gantt_utils.char_arr;
9902: l_adjusted_res_hours msc_gantt_utils.number_arr;
9903: l_schedule_qty msc_gantt_utils.number_arr;
9904: l_batch_number msc_gantt_utils.char_arr;
9905: l_assigned_units msc_gantt_utils.number_arr;
9906: l_alternate_flag msc_gantt_utils.char_arr;
9907: l_res_seq msc_gantt_utils.char_arr;
9908: l_wip_status_text msc_gantt_utils.char_arr;
9909: l_setup_type_text msc_gantt_utils.char_arr;
9902: l_adjusted_res_hours msc_gantt_utils.number_arr;
9903: l_schedule_qty msc_gantt_utils.number_arr;
9904: l_batch_number msc_gantt_utils.char_arr;
9905: l_assigned_units msc_gantt_utils.number_arr;
9906: l_alternate_flag msc_gantt_utils.char_arr;
9907: l_res_seq msc_gantt_utils.char_arr;
9908: l_wip_status_text msc_gantt_utils.char_arr;
9909: l_setup_type_text msc_gantt_utils.char_arr;
9910: l_alternate_num msc_gantt_utils.char_arr;
9903: l_schedule_qty msc_gantt_utils.number_arr;
9904: l_batch_number msc_gantt_utils.char_arr;
9905: l_assigned_units msc_gantt_utils.number_arr;
9906: l_alternate_flag msc_gantt_utils.char_arr;
9907: l_res_seq msc_gantt_utils.char_arr;
9908: l_wip_status_text msc_gantt_utils.char_arr;
9909: l_setup_type_text msc_gantt_utils.char_arr;
9910: l_alternate_num msc_gantt_utils.char_arr;
9911:
9904: l_batch_number msc_gantt_utils.char_arr;
9905: l_assigned_units msc_gantt_utils.number_arr;
9906: l_alternate_flag msc_gantt_utils.char_arr;
9907: l_res_seq msc_gantt_utils.char_arr;
9908: l_wip_status_text msc_gantt_utils.char_arr;
9909: l_setup_type_text msc_gantt_utils.char_arr;
9910: l_alternate_num msc_gantt_utils.char_arr;
9911:
9912: l_demand_class msc_gantt_utils.char_arr;
9905: l_assigned_units msc_gantt_utils.number_arr;
9906: l_alternate_flag msc_gantt_utils.char_arr;
9907: l_res_seq msc_gantt_utils.char_arr;
9908: l_wip_status_text msc_gantt_utils.char_arr;
9909: l_setup_type_text msc_gantt_utils.char_arr;
9910: l_alternate_num msc_gantt_utils.char_arr;
9911:
9912: l_demand_class msc_gantt_utils.char_arr;
9913: l_material_avail_date msc_gantt_utils.date_arr;
9906: l_alternate_flag msc_gantt_utils.char_arr;
9907: l_res_seq msc_gantt_utils.char_arr;
9908: l_wip_status_text msc_gantt_utils.char_arr;
9909: l_setup_type_text msc_gantt_utils.char_arr;
9910: l_alternate_num msc_gantt_utils.char_arr;
9911:
9912: l_demand_class msc_gantt_utils.char_arr;
9913: l_material_avail_date msc_gantt_utils.date_arr;
9914: l_bar_text msc_gantt_utils.char_arr;
9908: l_wip_status_text msc_gantt_utils.char_arr;
9909: l_setup_type_text msc_gantt_utils.char_arr;
9910: l_alternate_num msc_gantt_utils.char_arr;
9911:
9912: l_demand_class msc_gantt_utils.char_arr;
9913: l_material_avail_date msc_gantt_utils.date_arr;
9914: l_bar_text msc_gantt_utils.char_arr;
9915:
9916: l_one_record varchar2(10000);
9909: l_setup_type_text msc_gantt_utils.char_arr;
9910: l_alternate_num msc_gantt_utils.char_arr;
9911:
9912: l_demand_class msc_gantt_utils.char_arr;
9913: l_material_avail_date msc_gantt_utils.date_arr;
9914: l_bar_text msc_gantt_utils.char_arr;
9915:
9916: l_one_record varchar2(10000);
9917: l_out_data_index number := 1;
9910: l_alternate_num msc_gantt_utils.char_arr;
9911:
9912: l_demand_class msc_gantt_utils.char_arr;
9913: l_material_avail_date msc_gantt_utils.date_arr;
9914: l_bar_text msc_gantt_utils.char_arr;
9915:
9916: l_one_record varchar2(10000);
9917: l_out_data_index number := 1;
9918:
10088: mgq.parent_link,
10089: mgq.critical_flag,
10090: msi.item_name ITEM,
10091: mtp.organization_code ORGANIZATION,
10092: to_char(least(to_date(msc_gantt_utils.getActualStartDate(ms.order_type, msi.planning_make_buy_code,
10093: ms.organization_id, ms.source_organization_id, ms.new_dock_date,
10094: ms.new_wip_start_date, ms.new_ship_date,ms.new_schedule_date, ms.source_supplier_id), :format_mask),
10095: nvl(ms.firm_date, ms.new_schedule_date)),:format_mask) start_date,
10096: nvl(ms.firm_date, ms.new_schedule_date) end_date,
10248: || FIELD_SEPERATOR || nvl(escapeSplChars(l_sched_group(i)),null_space)
10249: || FIELD_SEPERATOR || l_transaction_id(i)
10250: || FIELD_SEPERATOR || nvl(to_char(l_req_start_date(i),format_mask),null_space)
10251: || FIELD_SEPERATOR || nvl(to_char(l_req_end_date(i),format_mask),null_space)
10252: || FIELD_SEPERATOR || escapeSplChars(msc_gantt_utils.getJobNodeLabel(l_item_name(i), l_org_code(i),
10253: l_order_number(i), l_order_type_text(i), l_qty(i)))
10254: || FIELD_SEPERATOR || l_sr_instance_id(i)
10255: || FIELD_SEPERATOR || l_organization_id(i)
10256: || FIELD_SEPERATOR || l_inventory_item_id(i)
10297: || FIELD_SEPERATOR || l_node_type(i)
10298: || FIELD_SEPERATOR || l_node_path(i)
10299: || FIELD_SEPERATOR || nvl(l_parent_link(i), null_space)
10300: || FIELD_SEPERATOR || nvl(escapeSplChars(l_op_desc(i)), null_space)
10301: || FIELD_SEPERATOR || escapeSplChars(msc_gantt_utils.getOpNodeLabel(l_item_prompt(i), l_transaction_id(i),
10302: l_op_desc(i), p_plan_id));
10303:
10304: addToOutStream(l_one_record, l_out_data_index, p_out_data);
10305:
10315: mgq.node_path,
10316: mgq.parent_link,
10317: mdr.department_code,
10318: mdr.resource_code,
10319: msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
10320: mrr.start_date, mrr.end_date,mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) start_date,
10321: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
10322: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) end_date,
10323: ms.new_order_quantity QUANTITY,
10317: mdr.department_code,
10318: mdr.resource_code,
10319: msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
10320: mrr.start_date, mrr.end_date,mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) start_date,
10321: msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
10322: mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) end_date,
10323: ms.new_order_quantity QUANTITY,
10324: mrr.resource_hours adjusted_res_hours,
10325: mrr.batch_number,
10329: nvl(ms.firm_planned_type,2) supply_firm_type,
10330: nvl(mrr.firm_flag,0) res_firm_type,
10331: nvl(mrr.status, 0) status,
10332: nvl(mrr.applied, 0) applied,
10333: msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) res_req_type,
10334: mrr.supply_id transaction_id,
10335: mrr.transaction_id res_transaction_id,
10336: mrr.resource_seq_num,
10337: decode(mrr.setup_id, to_number(null), null,
10334: mrr.supply_id transaction_id,
10335: mrr.transaction_id res_transaction_id,
10336: mrr.resource_seq_num,
10337: decode(mrr.setup_id, to_number(null), null,
10338: msc_gantt_utils.getSetupCode(mrr.plan_id, mrr.sr_instance_id, mrr.resource_id, mrr.setup_id)),
10339: mrr.earliest_start_date,
10340: mrr.earliest_completion_date,
10341: mrr.ulpsd,
10342: mrr.ulpcd,
10355: mi.item_name item,
10356: mi2.description assembly_item_desc,
10357: decode(mrr.resource_hours, 0, to_number(null),
10358: nvl(mrr.cummulative_quantity,ms.new_order_quantity)) schedule_qty,
10359: msc_gantt_utils.getOrderViewResNodeLabel(mrr.plan_id, mrr.sr_instance_id,
10360: mrr.transaction_id) bar_text,
10361: mrr.alternate_num,
10362: mrr.actual_start_date,
10363: mgq.critical_flag
10494: return SYS_YES;
10495: End getResult;
10496:
10497:
10498: END MSC_GANTT_UTILS;