[Home] [Help]
PACKAGE BODY: APPS.MSC_GANTT_PKG
Source
1 PACKAGE BODY MSC_GANTT_PKG AS
2 /* $Header: MSCGNTPB.pls 120.30 2006/09/20 00:28:24 pabram noship $ */
3
4 -- Constants
5 --separators
6 FIELD_SEPERATOR constant varchar2(5) := msc_gantt_utils.FIELD_SEPERATOR;
7 RECORD_SEPERATOR constant varchar2(5) := msc_gantt_utils.RECORD_SEPERATOR;
8 RESOURCE_SEPERATOR constant varchar2(5) := msc_gantt_utils.RESOURCE_SEPERATOR;
9 FORMAT_MASK constant varchar2(20) := msc_gantt_utils.FORMAT_MASK;
10 COMMA_SEPARATOR constant varchar2(20) := msc_gantt_utils.COMMA_SEPARATOR;
11 COLON_SEPARATOR constant varchar2(20) := msc_gantt_utils.COLON_SEPARATOR;
12
13 --view types
14 DEMAND_VIEW CONSTANT INTEGER := msc_gantt_utils.DEMAND_VIEW;
15 ORDER_VIEW CONSTANT INTEGER := msc_gantt_utils.ORDER_VIEW;
16 RES_ACTIVITIES_VIEW CONSTANT INTEGER := msc_gantt_utils.RES_ACTIVITIES_VIEW;
17 RES_UNITS_VIEW CONSTANT INTEGER := msc_gantt_utils.RES_UNITS_VIEW;
18 RES_HOURS_VIEW CONSTANT INTEGER := msc_gantt_utils.RES_HOURS_VIEW;
19 SUPPLIER_VIEW CONSTANT INTEGER := msc_gantt_utils.SUPPLIER_VIEW;
20
21 --node types
22 END_DEMAND_NODE constant INTEGER := msc_gantt_utils.END_DEMAND_NODE;
23 JOB_NODE constant INTEGER := msc_gantt_utils.JOB_NODE;
24 OP_NODE constant INTEGER := msc_gantt_utils.OP_NODE;
25 RES_NODE constant INTEGER := msc_gantt_utils.RES_NODE;
26 -- END_JOB_NODE CONSTANT INTEGER := msc_gantt_utils.END_JOB_NODE;
27 COPROD_NODE constant INTEGER := msc_gantt_utils.COPROD_NODE;
28 RES_INST_NODE CONSTANT INTEGER := msc_gantt_utils.RES_INST_NODE;
29
30 --orders view types
31 PEG_ORDERS constant integer := msc_gantt_utils.PEG_ORDERS;
32 PEG_DOWN constant integer := msc_gantt_utils.PEG_DOWN;
33 PEG_UP constant integer := msc_gantt_utils.PEG_UP;
34 PEG_UP_AND_DOWN constant integer := msc_gantt_utils.PEG_UP_AND_DOWN;
35 SHOW_CHILDREN constant integer := msc_gantt_utils.SHOW_CHILDREN;
36 SHOW_PARENT constant integer := msc_gantt_utils.SHOW_PARENT;
37 SHOW_PARENT_CHILDREN constant integer := msc_gantt_utils.SHOW_PARENT_CHILDREN;
38
39 --diff peg vs same peg orders view
40 SAME_PEG constant INTEGER := msc_gantt_utils.SAME_PEG;
41 ALL_PEG constant INTEGER := msc_gantt_utils.ALL_PEG;
42
43 --parent id
44 SUMMARY_DATA constant integer := msc_gantt_utils.SUMMARY_DATA;
45 DETAIL_DATA constant integer := msc_gantt_utils.DETAIL_DATA;
46
47 -- res firm types
48 NO_FIRM CONSTANT INTEGER := msc_gantt_utils.NO_FIRM;
49 FIRM_START CONSTANT INTEGER := msc_gantt_utils.FIRM_START;
50 FIRM_END CONSTANT INTEGER := msc_gantt_utils.FIRM_END;
51 FIRM_RESOURCE CONSTANT INTEGER := msc_gantt_utils.FIRM_RESOURCE;
52 FIRM_START_END CONSTANT INTEGER := msc_gantt_utils.FIRM_START_END;
53 FIRM_START_RES CONSTANT INTEGER := msc_gantt_utils.FIRM_START_RES;
54 FIRM_END_RES CONSTANT INTEGER := msc_gantt_utils.FIRM_END_RES;
55 FIRM_ALL CONSTANT INTEGER := msc_gantt_utils.FIRM_ALL;
56
57 --display types for res activities /hours view
58 DISPLAY_NONE constant integer := msc_gantt_utils.DISPLAY_NONE;
59 DISPLAY_LATE constant integer := msc_gantt_utils.DISPLAY_LATE;
60 DISPLAY_EARLY constant integer := msc_gantt_utils.DISPLAY_EARLY;
61 DISPLAY_FIRM constant integer := msc_gantt_utils.DISPLAY_FIRM;
62 DISPLAY_OVERLOAD constant integer := msc_gantt_utils.DISPLAY_OVERLOAD;
63
64 --res row types
65 RES_REQ_ROW_TYPE constant INTEGER := msc_gantt_utils.RES_REQ_ROW_TYPE;
66 RES_ACT_BATCHED_ROW_TYPE constant integer := msc_gantt_utils.RES_ACT_BATCHED_ROW_TYPE;
67
68 --critical vs non critical orders view
69 NON_CRITICAL_PATH constant integer := msc_gantt_utils.NON_CRITICAL_PATH;
70 CRITICAL_PATH constant integer := msc_gantt_utils.CRITICAL_PATH;
71
72 --filter types
73 FILTER_TYPE_LIST constant integer := msc_gantt_utils.FILTER_TYPE_LIST;
74 FILTER_TYPE_MFQ constant integer := msc_gantt_utils.FILTER_TYPE_MFQ;
75 FILTER_TYPE_FOLDER_ID constant integer := msc_gantt_utils.FILTER_TYPE_FOLDER_ID;
76 FILTER_TYPE_WHERE_STMT constant integer := msc_gantt_utils.FILTER_TYPE_WHERE_STMT;
77 FILTER_TYPE_AMONG constant integer := msc_gantt_utils.FILTER_TYPE_AMONG;
78
79 --misc
80 MBP_NULL_VALUE constant integer := msc_gantt_utils.MBP_NULL_VALUE;
81 MBP_NULL_VALUE_CHAR constant varchar2(30) := msc_gantt_utils.MBP_NULL_VALUE_CHAR;
82 NULL_SPACE constant varchar2(1) := msc_gantt_utils.NULL_SPACE;
83
84 SYS_YES constant INTEGER := msc_gantt_utils.SYS_YES;
85 SYS_NO constant INTEGER := msc_gantt_utils.SYS_NO;
86
87
88 TRANSFER_JOB_NODE constant integer := 1;
89 OP_JOB_NODE constant integer := 2;
90
91 -- Constants ends
92
93 g_pref_id number;
94 g_plan_id number;
95 g_plan_type number;
96 g_plan_info varchar2(5000);
97 g_first_date date; --day level first date
98 g_last_date date; --day level last date
99 g_hour_bkt_start_date date; --hour bucket start date
100 g_day_bkt_start_date date; --day bucket start after hour and min
101 g_plan_start_date date; --plan start date
102 g_cutoff_date date; -- plan cutoff date
103
104 g_end_demand_id number;
105
106 g_node_index number;
107 g_node_level number;
108
109 g_op_query_id number;
110 g_dem_op_query_id number;
111 g_end_peg_query_id number;
112 g_order_query_id number;
113
114 g_peg_up_and_down number;
115 g_peg_type number;
116 g_same_peg number;
117
118 -- cursors +
119
120 -- demand node ids
121 cursor demand_cur (x_plan_id number, x_demand_id number) is
122 select md.sr_instance_id,
123 md.organization_id,
124 md.demand_id
125 from msc_demands md
126 where md.demand_id = x_demand_id
127 and md.plan_id = x_plan_id
128 and md.origination_type in (6,7,8,9,11,15,22,29,30);
129
130 -- end demand node ids at same level for a given supply
131 cursor end_demands_cur (x_plan_id number,
132 x_instance_id number, x_supply_id number) is
133 select md.sr_instance_id,
134 md.organization_id,
135 md.demand_id,
136 md.using_assembly_demand_date start_date
137 from msc_demands md,
138 msc_full_pegging mfp1
139 where mfp1.plan_id = x_plan_id
140 and mfp1.transaction_id = x_supply_id
141 and mfp1.sr_instance_id = x_instance_id
142 and mfp1.plan_id = md.plan_id
143 and mfp1.demand_id = md.demand_id
144 and mfp1.sr_instance_id = md.sr_instance_id
145 and md.origination_type in (6,7,8,9,11,15,22,29,30)
146 order by md.using_assembly_demand_date;
147
148 -- supplies pegged to this demand
149 cursor end_pegs_cur (x_plan_id number, x_demand_id number) is
150 select distinct ms.sr_instance_id,
151 ms.organization_id,
152 ms.transaction_id,
153 msc_gantt_utils.isCriticalSupply(x_plan_id, g_end_demand_id,
154 ms.transaction_id, ms.sr_instance_id) critical_supply
155 from msc_full_pegging mfp,
156 msc_supplies ms
157 where mfp.demand_id = x_demand_id
158 and mfp.plan_id = x_plan_id
159 and ms.plan_id = mfp.plan_id
160 and ms.transaction_id = mfp.transaction_id
161 and ms.sr_instance_id = mfp.sr_instance_id;
162
163 -- op node ids
164 cursor ops_mfq_cur (x_op_seq_query_id number, x_supply_id number,
165 x_plan_id number, x_inst_id number) is
166 select distinct
167 to_char(number2),
168 number2,
169 number3,
170 char9,
171 date1
172 from msc_form_query
173 where query_id = x_op_seq_query_id
174 and number1 = x_supply_id
175 /*
176 and number2 not in
177 (select mon.to_op_seq_num
178 from msc_operation_networks mon,
179 msc_resource_requirements mrr
180 where mrr.plan_id = x_plan_id
181 and mrr.sr_instance_id = x_inst_id
182 and mrr.supply_id = x_supply_id
183 and mrr.end_date is not null
184 and mrr.department_id <> -1
185 and nvl(mrr.parent_id,2) = 2
186 and mrr.plan_id = mon.plan_id
187 and mrr.sr_instance_id = mon.sr_instance_id
188 and mrr.routing_sequence_id = mon.routing_sequence_id
189 and mrr.operation_seq_num = mon.from_op_seq_num
190 and mon.transition_type = 1 --primary
191 and mon.to_op_seq_num is not null
192 )
193 order by number2;
194 */
195 order by date1;
196
197 -- op node ids from msc_operation_networks for intra routing
198 cursor ops_intra_routing_cur (x_supply_id number, x_plan_id number, x_inst_id number) is
199 select
200 mon.from_op_seq_num,
201 mgq1.row_index from_index,
202 mon.to_op_seq_num,
203 mgq2.row_index to_index,
204 mon.dependency_type
205 from msc_operation_networks mon,
206 msc_resource_requirements mrr,
207 msc_gantt_query mgq1,
208 msc_gantt_query mgq2
209 where mrr.plan_id = x_plan_id
210 and mrr.sr_instance_id = x_inst_id
211 and mrr.supply_id = x_supply_id
212 and mrr.end_date is not null
213 and mrr.department_id <> -1
214 and nvl(mrr.parent_id,2) = 2
215 and mrr.plan_id = mon.plan_id
216 and mrr.sr_instance_id = mon.sr_instance_id
217 --and mrr.organization_id = mon.organization_id --org in mon is null
218 and mrr.routing_sequence_id = mon.routing_sequence_id
219 and mon.transition_type = 1 --primary
220 and mon.from_op_seq_num = mrr.operation_seq_num
221 and mgq1.query_id = g_order_query_id
222 and mgq1.sr_instance_id = mon.sr_instance_id
223 and mgq1.transaction_id = mrr.supply_id
224 and mgq1.op_seq_num = mon.from_op_seq_num
225 and mgq2.query_id = g_order_query_id
226 and mgq2.sr_instance_id = mon.sr_instance_id
227 and mgq2.transaction_id = mrr.supply_id
228 and mgq2.op_seq_num = mon.to_op_seq_num
229 order by
230 mon.from_op_seq_num,
231 mon.to_op_seq_num;
232
233 -- resource node ids
234 cursor resources_cur (x_plan_id number, x_instance_id number,
235 x_org_id number, x_supply_id number, x_op_seq number) is
236 select
237 nvl(mrr.department_id, 0) department_id,
238 nvl(mrr.resource_id, 0) resource_id,
239 mrr.transaction_id,
240 decode(g_end_demand_id, null, 0,
241 msc_gantt_utils.isCriticalRes(x_plan_id,g_end_demand_id,
242 ms.transaction_id, ms.sr_instance_id,
243 mrr.operation_seq_num, mrr.routing_sequence_id)) critical_flag,
244 mrr.operation_seq_num,
245 mrr.resource_seq_num
246 from msc_resource_requirements mrr,
247 msc_supplies ms
248 where mrr.plan_id = x_plan_id
249 and mrr.sr_instance_id = x_instance_id
250 and mrr.organization_id = x_org_id
251 and mrr.supply_id = x_supply_id
252 and mrr.operation_seq_num = x_op_seq
253 and mrr.end_date is not null
254 and mrr.parent_id =2
255 and mrr.department_id <> -1
256 and ms.plan_id = mrr.plan_id
257 and ms.transaction_id = mrr.supply_id
258 and ms.sr_instance_id = mrr.sr_instance_id
259 order by
260 mrr.operation_seq_num,
261 mrr.resource_seq_num;
262
263 -- supply node from mfq
264 cursor supplies_mfq_cur(x_dem_op_query_id number,
265 x_plan_id number, x_inst_id number,
266 x_supply_id number) is
267 select distinct
268 mfq.number4,
269 mfq.number5,
270 mfq.number3,
271 decode(g_end_demand_id, null, 0,
272 msc_gantt_utils.isCriticalSupply(ms.plan_id, g_end_demand_id,
273 ms.transaction_id, ms.sr_instance_id)) critical_supply,
274 msc_gantt_utils.getActualStartDate(ms.order_type, msi.planning_make_buy_code,
275 ms.organization_id, ms.source_organization_id, ms.new_dock_date,
276 ms.new_wip_start_date, ms.new_ship_date,ms.new_schedule_date,
277 ms.source_supplier_id) start_date,
278 ms.order_type
279 from msc_form_query mfq,
280 msc_supplies ms,
281 msc_system_items msi
282 where mfq.query_id = x_dem_op_query_id
283 and mfq.number1 = x_supply_id
284 and ms.plan_id = x_plan_id
285 and ms.sr_instance_id = x_inst_id
286 and ms.transaction_id = mfq.number3
287 and ms.plan_id = msi.plan_id
288 and ms.organization_id = msi.organization_id
289 and ms.sr_instance_id = msi.sr_instance_id
290 and ms.inventory_item_id = msi.inventory_item_id
291 order by msc_gantt_utils.getActualStartDate(ms.order_type, msi.planning_make_buy_code,
292 ms.organization_id, ms.source_organization_id, ms.new_dock_date,
293 ms.new_wip_start_date, ms.new_ship_date,ms.new_schedule_date,
294 ms.source_supplier_id) desc;
295
296 -- supply node <same peg - peg down>
297 cursor supplies_same_down_cur (x_plan_id number,
298 x_instance_id number, x_supply_id number,
299 x_end_peg_query_id number, x_dem_op_query_id number) is
300 select distinct ms.sr_instance_id,
301 ms.organization_id,
302 ms.transaction_id,
303 decode(g_end_demand_id, null, 0,
304 msc_gantt_utils.isCriticalSupply(ms.plan_id, g_end_demand_id,
305 ms.transaction_id, ms.sr_instance_id)) critical_supply,
306 msc_gantt_utils.getActualStartDate(ms.order_type, msi.planning_make_buy_code,
307 ms.organization_id, ms.source_organization_id, ms.new_dock_date,
308 ms.new_wip_start_date, ms.new_ship_date,ms.new_schedule_date,
309 ms.source_supplier_id) start_date,
310 ms.order_type
311 from msc_full_pegging mfp1,
312 msc_full_pegging mfp2,
313 msc_form_query mfq,
314 msc_supplies ms,
315 msc_system_items msi
316 where mfp1.plan_id = x_plan_id
317 and mfp1.sr_instance_id = x_instance_id
318 and mfp1.transaction_id = x_supply_id
319 and mfq.query_id = x_end_peg_query_id
320 and mfq.number1 = mfp1.end_pegging_id
321 and mfp2.plan_id = mfp1.plan_id
322 and mfp2.prev_pegging_id = mfp1.pegging_id
323 and ms.plan_id = mfp2.plan_id
324 and ms.sr_instance_id = mfp2.sr_instance_id
325 and ms.transaction_id = mfp2.transaction_id
326 and ms.transaction_id not in (
327 select mfq.number3
328 from msc_form_query mfq
329 where mfq.query_id = x_dem_op_query_id
330 and mfq.number1 = x_supply_id
331 and mfq.number2 is not null
332 )
333 and ms.plan_id = msi.plan_id
334 and ms.organization_id = msi.organization_id
335 and ms.sr_instance_id = msi.sr_instance_id
336 and ms.inventory_item_id = msi.inventory_item_id
337 order by msc_gantt_utils.getActualStartDate(ms.order_type, msi.planning_make_buy_code,
338 ms.organization_id, ms.source_organization_id, ms.new_dock_date,
339 ms.new_wip_start_date, ms.new_ship_date,ms.new_schedule_date,
340 ms.source_supplier_id) desc;
341
342 -- supply node <diff peg - peg up>
343 cursor supplies_diff_up_cur (x_plan_id number,
344 x_instance_id number, x_supply_id number,
345 x_end_peg_query_id number, x_dem_op_query_id number) is
346 select distinct ms.sr_instance_id,
347 ms.organization_id,
348 ms.transaction_id,
349 decode(g_end_demand_id, null, 0,
350 msc_gantt_utils.isCriticalSupply(ms.plan_id, g_end_demand_id,
351 ms.transaction_id, ms.sr_instance_id)) critical_supply,
352 msc_gantt_utils.getActualStartDate(ms.order_type, msi.planning_make_buy_code,
353 ms.organization_id, ms.source_organization_id, ms.new_dock_date,
354 ms.new_wip_start_date, ms.new_ship_date,ms.new_schedule_date,
355 ms.source_supplier_id) start_date,
356 ms.order_type
357 from msc_full_pegging mfp1,
358 msc_full_pegging mfp2,
359 msc_supplies ms,
360 msc_system_items msi
361 where mfp1.plan_id = x_plan_id
362 and mfp1.sr_instance_id = x_instance_id
363 and mfp1.transaction_id = x_supply_id
364 and mfp2.plan_id = mfp1.plan_id
365 and mfp2.pegging_id = mfp1.prev_pegging_id
366 and ms.plan_id = mfp2.plan_id
367 and ms.sr_instance_id = mfp2.sr_instance_id
368 and ms.transaction_id = mfp2.transaction_id
369 and ms.plan_id = msi.plan_id
370 and ms.organization_id = msi.organization_id
371 and ms.sr_instance_id = msi.sr_instance_id
372 and ms.inventory_item_id = msi.inventory_item_id
373 order by msc_gantt_utils.getActualStartDate(ms.order_type, msi.planning_make_buy_code,
374 ms.organization_id, ms.source_organization_id, ms.new_dock_date,
375 ms.new_wip_start_date, ms.new_ship_date,ms.new_schedule_date,
376 ms.source_supplier_id) desc;
377
378 -- supply node <diff peg - peg down>
379 cursor supplies_diff_down_cur (x_plan_id number,
380 x_instance_id number, x_supply_id number,
381 x_end_peg_query_id number, x_dem_op_query_id number) is
382 select distinct ms.sr_instance_id,
383 ms.organization_id,
384 ms.transaction_id,
385 decode(g_end_demand_id, null, 0,
386 msc_gantt_utils.isCriticalSupply(ms.plan_id, g_end_demand_id,
387 ms.transaction_id, ms.sr_instance_id)) critical_supply,
388 msc_gantt_utils.getActualStartDate(ms.order_type, msi.planning_make_buy_code,
389 ms.organization_id, ms.source_organization_id, ms.new_dock_date,
390 ms.new_wip_start_date, ms.new_ship_date,ms.new_schedule_date,
391 ms.source_supplier_id) start_date,
392 ms.order_type
393 from msc_full_pegging mfp1,
394 msc_full_pegging mfp2,
395 msc_supplies ms,
396 msc_system_items msi
397 where mfp1.plan_id = x_plan_id
398 and mfp1.sr_instance_id = x_instance_id
399 and mfp1.transaction_id = x_supply_id
400 and mfp2.plan_id = mfp1.plan_id
401 and mfp2.prev_pegging_id = mfp1.pegging_id
402 and ms.plan_id = mfp2.plan_id
403 and ms.sr_instance_id = mfp2.sr_instance_id
404 and ms.transaction_id = mfp2.transaction_id
405 and ms.transaction_id not in (
406 select mfq.number3
407 from msc_form_query mfq
408 where mfq.query_id = x_dem_op_query_id
409 and mfq.number1 = x_supply_id
410 and mfq.number2 is not null
411 )
412 and ms.plan_id = msi.plan_id
413 and ms.organization_id = msi.organization_id
414 and ms.sr_instance_id = msi.sr_instance_id
415 and ms.inventory_item_id = msi.inventory_item_id
416 order by msc_gantt_utils.getActualStartDate(ms.order_type, msi.planning_make_buy_code,
417 ms.organization_id, ms.source_organization_id, ms.new_dock_date,
418 ms.new_wip_start_date, ms.new_ship_date,ms.new_schedule_date,
419 ms.source_supplier_id) desc;
420
421 -- co-prod supply node <stickers>
422 cursor coprod_supplies_cur (x_plan_id number,
423 x_instance_id number, x_supply_id number) is
424 select distinct ms.sr_instance_id,
425 ms.organization_id,
426 ms.transaction_id,
427 decode(g_end_demand_id, null, 0,
428 msc_gantt_utils.isCriticalSupply(x_plan_id,g_end_demand_id,
429 ms.transaction_id, ms.sr_instance_id)) critical_supply,
430 ms.new_wip_start_date start_date,
431 ms.order_type
432 from msc_supplies ms
433 where ms.plan_id = x_plan_id
434 and ms.sr_instance_id = x_instance_id
435 and ms.disposition_id = x_supply_id
436 -- and ms.order_type in (14,15,16,17,28)
437 and ms.transaction_id not in (select mgq.transaction_id
438 from msc_gantt_query mgq
439 where mgq.query_id = g_order_query_id
440 and mgq.transaction_id = ms.transaction_id)
441 order by ms.new_wip_start_date;
442
443 -- get op/supply from msc_demands <same peg - peg down>
444 cursor op_supp_same_down_cur (x_plan_id number,
445 x_instance_id number, x_supply_id number,
446 x_end_peg_query_id number, x_first_op number) is
447 select distinct
448 decode(md.op_seq_num, 1, x_first_op, md.op_seq_num),
449 ms.transaction_id,
450 ms.sr_instance_id,
451 ms.organization_id,
452 msc_gantt_utils.isCriticalSupply(ms.plan_id, g_end_demand_id,
453 ms.transaction_id, ms.sr_instance_id) critical_supply
454 from msc_full_pegging mfp1,
455 msc_full_pegging mfp2,
456 msc_supplies ms,
457 msc_demands md,
458 msc_form_query mfq
459 where mfp1.plan_id = x_plan_id
460 and mfp1.transaction_id = x_supply_id
461 and mfp1.sr_instance_id = x_instance_id
462 and mfq.query_id = x_end_peg_query_id
463 and mfp1.end_pegging_id = mfq.number1
464 and md.plan_id = mfp1.plan_id
465 and md.disposition_id = mfp1.transaction_id
466 and md.sr_instance_id = mfp1.sr_instance_id
467 and nvl(md.op_seq_num,0) <> 0
468 and mfp2.plan_id = mfp1.plan_id
469 and mfp2.prev_pegging_id = mfp1.pegging_id
470 and mfp2.demand_id = md.demand_id
471 and ms.plan_id = mfp2.plan_id
472 and ms.transaction_id = mfp2.transaction_id
473 and ms.sr_instance_id = mfp2.sr_instance_id;
474
475 -- get op/supply from msc_demands <diff peg - peg down>
476 cursor op_supp_diff_down_cur (x_plan_id number,
477 x_instance_id number, x_supply_id number,
478 x_end_peg_query_id number, x_first_op number) is
479 select distinct
480 decode(md.op_seq_num, 1, x_first_op, md.op_seq_num),
481 ms.transaction_id,
482 ms.sr_instance_id,
483 ms.organization_id,
484 msc_gantt_utils.isCriticalSupply(ms.plan_id, g_end_demand_id,
485 ms.transaction_id, ms.sr_instance_id) critical_supply
486 from msc_full_pegging mfp1,
487 msc_full_pegging mfp2,
488 msc_supplies ms,
489 msc_demands md
490 where mfp1.plan_id = x_plan_id
491 and mfp1.transaction_id = x_supply_id
492 and mfp1.sr_instance_id = x_instance_id
493 and md.plan_id = mfp1.plan_id
494 and md.disposition_id = mfp1.transaction_id
495 and md.sr_instance_id = mfp1.sr_instance_id
496 and nvl(md.op_seq_num,0) <> 0
497 and mfp2.plan_id = mfp1.plan_id
498 and mfp2.prev_pegging_id = mfp1.pegging_id
499 and mfp2.demand_id = md.demand_id
500 and ms.plan_id = mfp2.plan_id
501 and ms.transaction_id = mfp2.transaction_id
502 and ms.sr_instance_id = mfp2.sr_instance_id;
503
504 --misc
505 cursor c_op_seq_num_cur (p_dem_op_query_id number,
506 p_supply_id number, p_trans_id number) is
507 select number2 --op_seq_num
508 from msc_form_query mfq
509 where mfq.query_id = p_dem_op_query_id
510 and mfq.number1 = p_supply_id
511 and mfq.number3 = p_trans_id;
512
513 cursor c_op_row_index_cur (p_qid number,
514 p_supply_id number, p_op_seq_num number)is
515 select mgq.row_index
516 from msc_gantt_query mgq
517 where mgq.query_id = p_qid
518 and mgq.transaction_id = p_supply_id
519 and mgq.op_seq_num = p_op_seq_num;
520
521 cursor check_job_row_cur (p_query number,
522 p_plan number, p_inst number, p_trx number) is
523 select count(*)
524 from msc_gantt_query mgq
525 where mgq.query_id = p_query
526 --and mgq.plan_id = p_plan
527 and mgq.sr_instance_id = p_inst
528 and mgq.transaction_id = p_trx
529 and rownum = 1;
530
531 cursor c_dmd_row_index (p_query number, p_inst number, p_trx number) is
532 select row_index
533 from msc_gantt_query mgq
534 where mgq.query_id = p_query
535 and mgq.sr_instance_id = p_inst
536 and mgq.transaction_id = p_trx;
537
538 cursor check_supp_row_cur (p_query number,
539 p_plan number, p_inst number, p_org number, p_item number,
540 p_supp number, p_supp_site number) is
541 select count(*)
542 from msc_gantt_query mgq
543 where mgq.query_id = p_query
544 and mgq.plan_id = p_plan
545 and mgq.sr_instance_id = p_inst
546 and mgq.organization_id = p_org
547 and mgq.inventory_item_id = p_item
548 and mgq.supplier_id = p_supp
549 and nvl(mgq.supplier_site_id, mbp_null_value) = nvl(p_supp_site, mbp_null_value)
550 and rownum = 1;
551
552 cursor c_check_row_type (p_query number, p_index number) is
553 select node_type
554 from msc_gantt_query
555 where query_id = p_query
556 and row_index = p_index;
557
558 cursor c_parent_link (p_query number, p_index number) is
559 select parent_link
560 from msc_gantt_query
561 where query_id = p_query
562 and row_index = p_index;
563
564 -- cursors -
565
566 --Private Apis
567 procedure put_line(p_string varchar2) is
568 begin
569 null;
570 --dbms_output.put_line(p_string);
571 end put_line;
572
573 procedure linkQueries(p_parent_query_id number,
574 p_child_query_id number, p_view_type number, p_list varchar2) is
575
576 l_trx_id number;
577 l_inst_id number;
578 l_one_record varchar2(100);
579
580 i number:=1;
581 begin
582 if ( p_parent_query_id is null ) then -- {
583 return;
584 end if; -- }
585
586 if ( p_view_type = DEMAND_VIEW ) then -- {
587 l_trx_id := to_number(p_list);
588 else
589 l_one_record := substr(p_list,
590 instr(p_list,'(',1,i)+1,instr(p_list,')',1,i)-instr(p_list,'(',1,i)-1);
591 l_trx_id := to_number(substr(l_one_record,instr(l_one_record,',')+1));
592 end if; -- }
593
594 update msc_gantt_query
595 set child_query_id = p_child_query_id
596 where query_id = p_parent_query_id
597 and transaction_id = l_trx_id ;
598
599 end linkQueries;
600
601 procedure updateParentLinkforCoprod(p_query_id number, p_row_index number,
602 p_parent_link varchar2, p_dependency_type number default null) is
603 begin
604 if ( p_parent_link is null ) then -- {
605 return;
606 end if; -- }
607
608 put_line(' updateParentLinkforCoprod '
609 ||' p_row_index '|| p_row_index
610 ||' p_parent_link '|| p_parent_link);
611
612 update msc_gantt_query
613 set parent_link = decode(parent_link, null,p_parent_link,
614 parent_link|| COMMA_SEPARATOR ||p_parent_link),
615 dependency_type = p_dependency_type
616 where query_id = p_query_id
617 and row_index = p_row_index;
618
619 end updateParentLinkforCoprod;
620
621 procedure updateOpParentLink(p_query_id number, p_row_index number,
622 p_parent_link varchar2, p_dependency_type number default null) is
623 begin
624 if ( p_parent_link is null ) then -- {
625 return;
626 end if; -- }
627 put_line(' updateOpParentLink '
628 ||' p_row_index '|| p_row_index
629 ||' p_parent_link '|| p_parent_link);
630
631 update msc_gantt_query
632 set parent_link = decode(parent_link,
633 null, p_parent_link || FIELD_SEPERATOR || nvl(to_char(p_dependency_type), null_space),
634 parent_link || FIELD_SEPERATOR || p_parent_link || FIELD_SEPERATOR || nvl(to_char(p_dependency_type), null_space)),
635 dependency_type = nvl(dependency_type,0)+1
636 where query_id = p_query_id
637 and row_index = p_row_index;
638
639 end updateOpParentLink;
640
641 procedure updateParentLink(p_query_id number, p_row_index number,
642 p_parent_link varchar2, p_dependency_type number default null) is
643
644 l_parent_row_type number;
645 l_child_row_type number;
646 l_child_parent_link msc_gantt_query.parent_link%type;
647 begin
648 if ( p_parent_link is null ) then -- {
649 return;
650 end if; -- }
651 put_line(' updateParentLink '
652 ||' p_row_index '|| p_row_index
653 ||' p_parent_link '|| p_parent_link);
654
655 open c_check_row_type(p_query_id, p_parent_link);
656 fetch c_check_row_type into l_parent_row_type;
657 close c_check_row_type;
658
659 open c_check_row_type(p_query_id, p_row_index);
660 fetch c_check_row_type into l_child_row_type;
661 close c_check_row_type;
662
663 open c_parent_link(p_query_id, p_row_index);
664 fetch c_parent_link into l_child_parent_link;
665 close c_parent_link;
666
667 if (l_parent_row_type = COPROD_NODE or l_child_row_type = COPROD_NODE ) then
668 return;
669 end if;
670
671 if ( instr(l_child_parent_link,p_parent_link) <> 0 ) then
672 return;
673 end if;
674
675 update msc_gantt_query
676 set parent_link = decode(parent_link, null,p_parent_link,
677 parent_link|| COMMA_SEPARATOR ||p_parent_link),
678 dependency_type = p_dependency_type
679 where query_id = p_query_id
680 and row_index = p_row_index;
681
682 end updateParentLink;
683
684 procedure addEndDemandNodes(p_instance_id number, p_supply_id number, p_parent_index number) is
685
686 l_child_count number := 0;
687
688 l_inst_id number;
689 l_org_id number;
690 l_trans_id number;
691 l_start_date date;
692
693 l_row_index number;
694 begin
695 open end_demands_cur(g_plan_id, p_instance_id, p_supply_id);
696 loop -- {
697 fetch end_demands_cur into l_inst_id, l_org_id, l_trans_id, l_start_date;
698 exit when end_demands_cur%notfound;
699
700 open c_dmd_row_index(g_order_query_id, l_inst_id, l_trans_id);
701 fetch c_dmd_row_index into l_row_index;
702 close c_dmd_row_index;
703
704 if ( nvl(l_row_index, mbp_null_value) = mbp_null_value) then
705 g_node_index := g_node_index + g_peg_type;
706
707 msc_gantt_utils.populateRowKeysIntoGantt(
708 p_query_id => g_order_query_id,
709 p_index => g_node_index,
710 p_node_path => g_node_index,
711 p_node_type => END_DEMAND_NODE,
712 p_inst_id => l_inst_id,
713 p_org_id => l_org_id,
714 p_transaction_id => l_trans_id,
715 p_critical_flag => NON_CRITICAL_PATH,
716 p_node_level => g_node_level
717 );
718 updateParentLink(g_order_query_id, p_parent_index, g_node_index, null);
719 l_child_count := l_child_count + 1;
720 else
721 updateParentLink(g_order_query_id, p_parent_index, l_row_index, null);
722 end if;
723 end loop; -- }
724 close end_demands_cur;
725 end addEndDemandNodes;
726
727 procedure addResources(p_nodepath varchar2,
728 p_instance_id number, p_org_id number,
729 p_supply_id number, p_op_seq_id number) is
730
731 l_trans_id msc_gantt_utils.number_arr;
732 l_dept_id msc_gantt_utils.number_arr;
733 l_res_id msc_gantt_utils.number_arr;
734 l_critical_flag msc_gantt_utils.number_arr;
735 l_op_seq_num msc_gantt_utils.number_arr;
736 l_res_seq_num msc_gantt_utils.number_arr;
737
738 l_child_count number := 0;
739 begin
740 open resources_cur (g_plan_id, p_instance_id, p_org_id, p_supply_id, p_op_seq_id);
741 fetch resources_cur bulk collect into l_dept_id, l_res_id, l_trans_id, l_critical_flag,
742 l_op_seq_num, l_res_seq_num;
743 close resources_cur;
744
745 for i in 1..l_dept_id.count
746 loop -- {
747 if ( g_peg_type = PEG_ORDERS ) then
748 g_node_index := g_node_index + 1;
749 else
750 g_node_index := g_node_index + g_peg_type;
751 end if;
752
753 msc_gantt_utils.populateRowKeysIntoGantt(
754 p_query_id => g_order_query_id,
755 p_index => g_node_index,
756 p_node_path => p_nodepath||COLON_SEPARATOR||to_char(l_child_count),
757 p_node_type => RES_NODE,
758 p_inst_id => p_instance_id,
759 p_org_id => p_org_id,
760 p_transaction_id => l_trans_id(i),
761 p_dept_id => l_dept_id(i),
762 p_res_id => l_res_id(i),
763 p_node_level => g_node_level,
764 p_critical_flag => l_critical_flag(i)
765 );
766 l_child_count := l_child_count + 1;
767 end loop; -- }
768 end addResources;
769
770 procedure addOpResNodes(p_nodepath varchar2,
771 p_instance_id number, p_org_id number, p_supply_id number,
772 p_first_op out nocopy number) is
773
774 l_critical_flag number;
775 l_op_seq_num varchar2(50);
776 l_op_seq_id number;
777 l_op_desc varchar2(300);
778 l_date1 date;
779 l_from_row_index number;
780
781 l_to_critical_flag number;
782 l_to_op_seq_num varchar2(50);
783 l_to_op_seq_id number;
784 l_to_op_desc varchar2(300);
785 l_dependency_type number;
786 l_to_row_index number;
787
788 isFirstOp boolean;
789 l_op_node_path varchar2(50);
790 l_child_count number := 0;
791
792 l_dummy_date date;
793 l_check_op_row number;
794
795 l_from_op_seq number;
796 l_from_dependency number;
797
798 l_ops_intra_routing_cur ops_intra_routing_cur%rowtype;
799 begin
800 put_line('addOpResNodes in');
801 isFirstOp := true;
802 p_first_op :=1;
803
804 open ops_mfq_cur (g_op_query_id, p_supply_id, g_plan_id, p_instance_id);
805 loop -- {
806 fetch ops_mfq_cur into l_op_seq_num, l_op_seq_id, l_critical_flag, l_op_desc, l_date1;
807 exit when ops_mfq_cur%notfound;
808
809 l_from_row_index := mbp_null_value;
810 l_check_op_row := mbp_null_value;
811 open c_op_row_index_cur(g_order_query_id, p_supply_id, l_op_seq_num);
812 fetch c_op_row_index_cur into l_check_op_row;
813 close c_op_row_index_cur;
814
815 put_line('regular oper '||l_op_seq_num||' '|| l_check_op_row);
816
817 if ( nvl(l_check_op_row,mbp_null_value) = mbp_null_value) then --{
818 if isFirstOp then
819 p_first_op := l_op_seq_id;
820 isFirstOp := false;
821 end if;
822
823 put_line('adding regular oper'||l_op_seq_num);
824
825 if ( g_peg_type = PEG_ORDERS ) then
826 g_node_index := g_node_index + 1;
827 else
828 g_node_index := g_node_index + g_peg_type;
829 end if;
830
831 msc_gantt_utils.populateRowKeysIntoGantt(
832 p_query_id => g_order_query_id,
833 p_index => g_node_index,
834 p_node_path => p_nodepath||COLON_SEPARATOR||to_char(l_child_count),
835 p_node_type => OP_NODE,
836 p_transaction_id => p_supply_id,
837 p_inst_id => p_instance_id,
838 p_org_id => p_org_id,
839 p_op_seq_num => l_op_seq_num,
840 p_op_seq_id => l_op_seq_id,
841 p_op_desc => l_op_desc,
842 p_node_level => g_node_level,
843 p_critical_flag => l_critical_flag
844 );
845
846 l_op_node_path := p_nodepath||COLON_SEPARATOR||to_char(l_child_count);
847 l_child_count := l_child_count + 1;
848 l_from_row_index := g_node_index;
849
850 addResources(l_op_node_path, p_instance_id, p_org_id, p_supply_id, l_op_seq_num);
851 end if; --}
852 end loop; -- }
853 close ops_mfq_cur;
854
855 --intra-routing
856 open ops_intra_routing_cur (p_supply_id, g_plan_id, p_instance_id);
857 loop -- {
858 fetch ops_intra_routing_cur into l_ops_intra_routing_cur;
859 exit when ops_intra_routing_cur%notfound;
860 updateOpParentLink(g_order_query_id, l_ops_intra_routing_cur.from_index,
861 l_ops_intra_routing_cur.to_index, l_ops_intra_routing_cur.dependency_type);
862 end loop; -- }
863 close ops_intra_routing_cur;
864
865 put_line('addOpResNodes out');
866 end addOpResNodes;
867
868 procedure insertOpIntoMFQ(p_instance_id number, p_org_id number, p_supply_id number) is
869 begin
870
871 insert into msc_form_query
872 (query_id,
873 last_update_date, last_updated_by, creation_date, created_by, last_update_login,
874 number1, number2, number3, char9, date1)
875 select distinct
876 g_op_query_id,
877 trunc(sysdate), -1, trunc(sysdate), -1, -1,
878 p_supply_id, mrr.operation_seq_num,
879 msc_gantt_utils.isCriticalRes(g_plan_id, g_end_demand_id,
880 mrr.supply_id, mrr.sr_instance_id,
881 mrr.operation_seq_num, mrr.routing_sequence_id) critical_flag,
882 mro.operation_description op_desc,
883 mrr.start_date
884 from msc_resource_requirements mrr,
885 msc_routing_operations mro
886 where mrr.plan_id = g_plan_id
887 and mrr.sr_instance_id = p_instance_id
888 and mrr.organization_id = p_org_id
889 and mrr.supply_id = p_supply_id
890 and mrr.end_date is not null
891 and mrr.department_id <> -1
892 and nvl(mrr.parent_id,2) = 2
893 and mrr.plan_id = mro.plan_id (+)
894 and mrr.sr_instance_id = mro.sr_instance_id (+)
895 and mrr.routing_sequence_id = mro.routing_sequence_id (+)
896 and mrr.operation_sequence_id = mro.operation_sequence_id (+);
897
898 end insertOpIntoMFQ;
899
900 procedure insertOpJobFromMDIntoMFQ (p_first_op number,
901 p_instance_id number, p_org_id number, p_supply_id number) is
902
903 l_inst_id msc_gantt_utils.number_arr;
904 l_org_id msc_gantt_utils.number_arr;
905 l_trans_id msc_gantt_utils.number_arr;
906 l_op_seq_num msc_gantt_utils.number_arr;
907 l_critical_flag msc_gantt_utils.number_arr;
908 begin
909 if ( g_same_peg = SAME_PEG ) then -- {
910 open op_supp_same_down_cur (g_plan_id, p_instance_id, p_supply_id,
911 g_end_peg_query_id, p_first_op);
912 fetch op_supp_same_down_cur bulk collect into l_op_seq_num, l_trans_id,
913 l_inst_id, l_org_id, l_critical_flag;
914 close op_supp_same_down_cur;
915 elsif ( g_same_peg = ALL_PEG ) then
916 open op_supp_diff_down_cur (g_plan_id, p_instance_id, p_supply_id,
917 g_end_peg_query_id, p_first_op);
918 fetch op_supp_diff_down_cur bulk collect into l_op_seq_num, l_trans_id,
919 l_inst_id, l_org_id, l_critical_flag;
920 close op_supp_diff_down_cur;
921 end if; -- }
922
923 -- supply_id, op_seq, tran_id, inst_id, org_id, critical
924 for i in 1..l_op_seq_num.count
925 loop -- {
926 insert into msc_form_query
927 (query_id,
928 last_update_date, last_updated_by, creation_date, created_by, last_update_login,
929 number1, number2, number3, number4, number5, number6)
930 values
931 (g_dem_op_query_id,
932 trunc(sysdate), -1, trunc(sysdate), -1, -1,
933 p_supply_id, l_op_seq_num(i), l_trans_id(i),
934 l_inst_id(i), l_org_id(i), l_critical_flag(i));
935 end loop; -- }
936 end insertOpJobFromMDIntoMFQ;
937
938 -- if op exists in msc_demand but not in msc_resource_requirements,
939 -- show the op in the closest next op or prev op
940 procedure moveDmdOp(p_supply_id number) is
941
942 v_op msc_gantt_utils.number_arr;
943 v_new_op msc_gantt_utils.number_arr;
944 v_dummy number;
945
946 begin
947 select distinct mfq.number2, mfq.number2
948 bulk collect into v_op, v_new_op
949 from msc_form_query mfq
950 where mfq.query_id = g_dem_op_query_id
951 and mfq.number1 = p_supply_id
952 and mfq.number2 not in (
953 select mfq_mrr.number2
954 from msc_form_query mfq_mrr
955 where mfq_mrr.query_id = g_op_query_id
956 and mfq_mrr.number1 = p_supply_id);
957 for a in 1 .. v_op.count
958 loop -- {
959 -- find the closest next op
960 select min(number2)
961 into v_dummy
962 from msc_form_query
963 where query_id = g_op_query_id
964 and number1 = p_supply_id
965 and number2 > v_op(a);
966
967 if v_dummy is null then
968 -- if not found, find the closest prev op
969 select max(number2)
970 into v_dummy
971 from msc_form_query
972 where query_id = g_op_query_id
973 and number1 = p_supply_id
974 and number2 < v_op(a);
975 end if;
976
977 if (v_dummy is not null) then
978 v_new_op(a) := v_dummy;
979 else
980 v_new_op(a) := v_op(a);
981 end if;
982 end loop; -- }
983
984 forall a in 1.. v_op.count
985 update msc_form_query
986 set number2= v_new_op(a)
987 where query_id = g_dem_op_query_id
988 and number1 = p_supply_id
989 and number2 = v_op(a);
990 exception
991 when no_data_found then
992 null;
993 end moveDmdOp;
994
995 function getParentOpLink(p_supply_id1 number, p_supply_id2 number) return varchar2 is
996
997 l_op_seq_num number;
998 l_parent_link msc_gantt_query.parent_link%type;
999 begin
1000 if ( g_peg_type = PEG_DOWN ) then -- {
1001 open c_op_seq_num_cur(g_dem_op_query_id, p_supply_id1, p_supply_id2);
1002 fetch c_op_seq_num_cur into l_op_seq_num;
1003 close c_op_seq_num_cur;
1004
1005 open c_op_row_index_cur(g_order_query_id, p_supply_id1, l_op_seq_num);
1006 fetch c_op_row_index_cur into l_parent_link;
1007 close c_op_row_index_cur;
1008 else -- PEG_UP
1009 open c_op_seq_num_cur(g_dem_op_query_id, p_supply_id2, p_supply_id1);
1010 fetch c_op_seq_num_cur into l_op_seq_num;
1011 close c_op_seq_num_cur;
1012
1013 open c_op_row_index_cur(g_order_query_id, p_supply_id2, l_op_seq_num);
1014 fetch c_op_row_index_cur into l_parent_link;
1015 close c_op_row_index_cur;
1016 end if; -- }
1017 return l_parent_link;
1018 end getParentOpLink;
1019
1020 procedure addCoProdNodes(p_parent_index number,
1021 p_instance_id number, p_org_id number, p_supply_id number) is
1022
1023 l_inst_id msc_gantt_utils.number_arr;
1024 l_org_id msc_gantt_utils.number_arr;
1025 l_trans_id msc_gantt_utils.number_arr;
1026 l_op_seq_num msc_gantt_utils.number_arr;
1027 l_critical_flag msc_gantt_utils.number_arr;
1028 l_start_date msc_gantt_utils.char_arr;
1029 l_ordertype msc_gantt_utils.number_arr;
1030
1031 cursor c_order_type (p_plan number, p_inst number, p_trx number) is
1032 select ms.order_type, ms.disposition_id
1033 from msc_supplies ms
1034 where ms.plan_id = p_plan
1035 and ms.sr_instance_id = p_inst
1036 and ms.transaction_id = p_trx;
1037
1038 l_order_type number;
1039 l_disp_id number;
1040 l_node_type number;
1041 begin
1042 open c_order_type (g_plan_id, p_instance_id, p_supply_id);
1043 fetch c_order_type into l_order_type, l_disp_id;
1044 close c_order_type;
1045
1046 --put_line(' order type '||l_order_type||' disp id '||l_disp_id);
1047
1048 if l_order_type in (14,15,16,17,28) then
1049 open coprod_supplies_cur (g_plan_id, p_instance_id, l_disp_id);
1050 fetch coprod_supplies_cur bulk collect into l_inst_id, l_org_id,
1051 l_trans_id, l_critical_flag, l_start_date, l_ordertype;
1052 close coprod_supplies_cur;
1053
1054 else
1055 open coprod_supplies_cur (g_plan_id, p_instance_id, p_supply_id);
1056 fetch coprod_supplies_cur bulk collect into l_inst_id, l_org_id,
1057 l_trans_id, l_critical_flag, l_start_date, l_ordertype;
1058 close coprod_supplies_cur;
1059 end if;
1060
1061 for i in 1..l_inst_id.count
1062 loop -- {
1063 g_node_index := g_node_index + g_peg_type;
1064
1065 if (l_ordertype(i) in (14,15,16,17,28)) then
1066 l_node_type := COPROD_NODE;
1067 else
1068 l_node_type := JOB_NODE;
1069 end if;
1070
1071 put_line('addcoprodnodes p_supply_id '|| p_supply_id ||' l_trans_id '||l_trans_id(i) );
1072
1073 msc_gantt_utils.populateRowKeysIntoGantt(
1074 p_query_id => g_order_query_id,
1075 p_index => g_node_index,
1076 p_node_path => g_node_index,
1077 p_node_type => l_node_type,
1078 p_inst_id => l_inst_id(i),
1079 p_org_id => l_org_id(i),
1080 p_transaction_id => l_trans_id(i),
1081 p_critical_flag => l_critical_flag(i),
1082 p_node_level => g_node_level
1083 );
1084
1085 end loop; -- }
1086
1087 end addCoProdNodes;
1088
1089 procedure addPegUpNodes(p_parent_index number,
1090 p_instance_id number, p_org_id number, p_supply_id number) is
1091
1092 l_parent_link msc_gantt_query.parent_link%type;
1093 l_first_op number;
1094
1095 l_inst_id msc_gantt_utils.number_arr;
1096 l_org_id msc_gantt_utils.number_arr;
1097 l_trans_id msc_gantt_utils.number_arr;
1098 l_critical_flag msc_gantt_utils.number_arr;
1099 l_start_date msc_gantt_utils.char_arr;
1100 l_order_type msc_gantt_utils.number_arr;
1101
1102 l_row_type number;
1103 l_row_index number;
1104 begin
1105 open supplies_diff_up_cur (g_plan_id, p_instance_id, p_supply_id,
1106 g_end_peg_query_id, g_dem_op_query_id);
1107 fetch supplies_diff_up_cur bulk collect into l_inst_id, l_org_id,
1108 l_trans_id, l_critical_flag, l_start_date, l_order_type;
1109 close supplies_diff_up_cur;
1110
1111 open c_check_row_type(g_order_query_id, p_parent_index);
1112 fetch c_check_row_type into l_row_type;
1113 close c_check_row_type;
1114
1115 for i in 1..l_inst_id.count
1116 loop -- {
1117
1118 put_line('addPegUpNodes in '||' p_supply_id ' || p_supply_id ||' l_trans_id '|| l_trans_id(i) );
1119
1120 open c_dmd_row_index (g_order_query_id, l_inst_id(i), l_trans_id(i));
1121 fetch c_dmd_row_index into l_row_index;
1122 close c_dmd_row_index ;
1123
1124 if (nvl(l_row_index, mbp_null_value) = mbp_null_value) then
1125
1126 g_node_index := g_node_index + g_peg_type;
1127
1128 msc_gantt_utils.populateRowKeysIntoGantt(
1129 p_query_id => g_order_query_id,
1130 p_index => g_node_index,
1131 p_node_path => g_node_index,
1132 p_node_type => JOB_NODE,
1133 p_inst_id => l_inst_id(i),
1134 p_org_id => l_org_id(i),
1135 p_transaction_id => l_trans_id(i),
1136 p_critical_flag => l_critical_flag(i),
1137 p_node_level => g_node_level
1138 );
1139
1140 if (l_row_type = JOB_NODE) then
1141 l_parent_link := getParentOpLink(p_supply_id, l_trans_id(i));
1142 updateParentLink(g_order_query_id, p_parent_index, nvl(l_parent_link, g_node_index) );
1143 elsif (l_row_type = COPROD_NODE) then
1144 updateParentLinkforCoprod(g_order_query_id, p_parent_index, g_node_index);
1145 end if;
1146
1147 insertOpIntoMFQ(l_inst_id(i), l_org_id(i), l_trans_id(i));
1148 addOpResNodes(g_node_index, l_inst_id(i), l_org_id(i), l_trans_id(i), l_first_op);
1149 insertOpJobFromMDIntoMFQ(l_first_op, l_inst_id(i), l_org_id(i), l_trans_id(i));
1150 moveDmdOp(p_supply_id);
1151 addCoProdNodes(g_node_index, l_inst_id(i), l_org_id(i), l_trans_id(i));
1152 else
1153 -- row is already there, so just update the parent_link of this node to point to l_row_index
1154 updateParentLinkforCoprod(g_order_query_id, p_parent_index, l_row_index);
1155 end if;
1156
1157
1158 end loop; -- }
1159 end addPegUpNodes;
1160
1161 function isDupInMGQ(p_query number, p_inst number, p_trx number) return number is
1162
1163 cursor c_dup is
1164 select row_index
1165 from msc_gantt_query
1166 where query_id = p_query
1167 and sr_instance_id = p_inst
1168 and transaction_id = p_trx;
1169
1170 l_temp number;
1171 begin
1172 put_line('isDupInMGQ in p_query '|| p_query || ' p_inst ' || p_inst ||' p_trx '|| p_trx );
1173 open c_dup;
1174 fetch c_dup into l_temp;
1175 close c_dup;
1176
1177 l_temp := nvl(l_temp, mbp_null_value);
1178 put_line('isDupInMGQ out '||l_temp);
1179 return l_temp;
1180 end isDupInMGQ;
1181
1182 procedure addPegDownJobNodes(p_parent_index number,
1183 p_instance_id number, p_org_id number,
1184 p_supply_id number, p_fetch_type number) is
1185
1186 l_parent_link msc_gantt_query.parent_link%type;
1187
1188 l_inst_id msc_gantt_utils.number_arr;
1189 l_org_id msc_gantt_utils.number_arr;
1190 l_trans_id msc_gantt_utils.number_arr;
1191 l_op_seq_num msc_gantt_utils.number_arr;
1192 l_critical_flag msc_gantt_utils.number_arr;
1193 l_start_date msc_gantt_utils.char_arr;
1194 l_order_type msc_gantt_utils.number_arr;
1195
1196 l_node_type number;
1197
1198 l_dup_row_index number;
1199
1200 cursor c_check_op_in_mrr (p_plan number, p_supply number) is
1201 select 1
1202 from msc_resource_requirements
1203 where plan_id = p_plan
1204 and supply_id = p_supply
1205 and nvl(parent_id,2) = 2;
1206
1207 l_parent_op_found number;
1208 l_child_op_found number;
1209
1210 begin
1211 put_line('addPegDownJobNodes in');
1212 if ( p_fetch_type = OP_JOB_NODE ) then -- {
1213 put_line('addPegDownJobNodes op_job_node ');
1214 open supplies_mfq_cur(g_dem_op_query_id, g_plan_id, p_instance_id, p_supply_id);
1215 fetch supplies_mfq_cur bulk collect into l_inst_id, l_org_id,
1216 l_trans_id, l_critical_flag, l_start_date, l_order_type;
1217 close supplies_mfq_cur;
1218 elsif ( g_same_peg = SAME_PEG ) then
1219 put_line('addPegDownJobNodes same_peg ');
1220 open supplies_same_down_cur (g_plan_id, p_instance_id, p_supply_id,
1221 g_end_peg_query_id, g_dem_op_query_id);
1222 fetch supplies_same_down_cur bulk collect into l_inst_id, l_org_id,
1223 l_trans_id, l_critical_flag, l_start_date, l_order_type;
1224 close supplies_same_down_cur;
1225 elsif ( g_same_peg = ALL_PEG ) then
1226 put_line('addPegDownJobNodes all_peg ');
1227 open supplies_diff_down_cur (g_plan_id, p_instance_id, p_supply_id,
1228 g_end_peg_query_id, g_dem_op_query_id);
1229 fetch supplies_diff_down_cur bulk collect into l_inst_id, l_org_id,
1230 l_trans_id, l_critical_flag, l_start_date, l_order_type;
1231 close supplies_diff_down_cur;
1232 end if; -- }
1233
1234 for i in 1..l_inst_id.count
1235 loop -- {
1236
1237 if ( p_fetch_type = OP_JOB_NODE ) then
1238 l_parent_link := getParentOpLink(p_supply_id, l_trans_id(i));
1239 elsif ( p_fetch_type = TRANSFER_JOB_NODE ) then
1240 l_parent_link := p_parent_index;
1241 end if;
1242
1243 l_dup_row_index := isDupInMGQ(g_order_query_id, l_inst_id(i), l_trans_id(i));
1244 put_line('l_dup_row_index '|| l_dup_row_index);
1245 if ( l_dup_row_index <> mbp_null_value ) then -- {
1246 updateParentLink(g_order_query_id, l_dup_row_index, l_parent_link);
1247 else
1248 g_node_index := g_node_index + g_peg_type;
1249 if ( l_order_type(i) in (14,15,16,17,28) ) then
1250 l_node_type := COPROD_NODE;
1251 else
1252 l_node_type := JOB_NODE;
1253 end if;
1254
1255 msc_gantt_utils.populateRowKeysIntoGantt(
1256 p_query_id => g_order_query_id,
1257 p_index => g_node_index,
1258 p_node_path => g_node_index,
1259 p_node_type => l_node_type,
1260 p_inst_id => l_inst_id(i),
1261 p_org_id => l_org_id(i),
1262 p_transaction_id => l_trans_id(i),
1263 p_parent_link => l_parent_link,
1264 p_critical_flag => l_critical_flag(i),
1265 p_node_level => g_node_level
1266 );
1267
1268
1269 /*
1270 if (l_parent_link is null) then
1271 open c_check_op_in_mrr (g_plan_id, p_supply_id);
1272 fetch c_check_op_in_mrr into l_parent_op_found;
1273 close c_check_op_in_mrr;
1274 end if;
1275 */
1276 updateParentLink(g_order_query_id, g_node_index, p_parent_index);
1277
1278 addCoProdNodes(g_node_index, l_inst_id(i), l_org_id(i), l_trans_id(i));
1279 put_line('addPegDownJobNodes '||l_trans_id(i) );
1280 end if;
1281 end loop; -- }
1282 put_line('addPegDownJobNodes out');
1283 end addPegDownJobNodes;
1284
1285 procedure ordersPegging(p_start_index number default null) is
1286
1287 p_supply_id number;
1288 p_instance_id number;
1289 p_org_id number;
1290 p_op_seq_id number;
1291 p_first_supply_id number;
1292
1293 p_nodetype number;
1294 p_nodepath varchar2(200);
1295
1296 current_index number := 0;
1297 hasMore boolean := true;
1298 l_dummy_sort varchar2(10);
1299
1300 l_first_op number;
1301
1302 cursor c_next_row (p_query_id number, p_row_index number) is
1303 select transaction_id, organization_id, sr_instance_id, op_seq_id,
1304 node_type, node_path
1305 from msc_gantt_query
1306 where query_id = p_query_id
1307 and row_index = p_row_index;
1308
1309 cursor c_next_row_index (p_query_id number,
1310 p_row_index number, p_peg_dir number) is
1311 select row_index, '1' dummy_sort
1312 from msc_gantt_query
1313 where query_id = p_query_id
1314 and (node_type = JOB_NODE
1315 or (node_type = COPROD_NODE and p_peg_dir = PEG_UP)
1316 or (node_type = COPROD_NODE and p_peg_dir = PEG_DOWN)
1317 or (node_type = COPROD_NODE and p_peg_dir = PEG_ORDERS))
1318 and ((p_peg_dir = PEG_DOWN and row_index > p_row_index)
1319 or (p_peg_dir = PEG_ORDERS and row_index > p_row_index)
1320 or (p_peg_dir = PEG_UP and row_index < p_row_index ))
1321 order by
1322 decode(p_peg_dir, PEG_UP, row_index, dummy_sort) desc,
1323 row_index asc;
1324
1325 cursor c_next_node_level(p_query_id number,
1326 p_peg_dir number, p_row_index number) is
1327 select decode(p_peg_dir,
1328 PEG_UP, node_level + PEG_UP,
1329 PEG_DOWN, node_level + PEG_DOWN,
1330 PEG_ORDERS, node_level + 1 )
1331 from msc_gantt_query
1332 where query_id = p_query_id
1333 and row_index = p_row_index;
1334
1335 BEGIN
1336 put_line('ordersPegging in');
1337 put_line('query_ids g_op_query_id '||g_op_query_id
1338 ||' g_end_peg_query_id '||g_end_peg_query_id
1339 ||' g_dem_op_query_id '||g_dem_op_query_id );
1340
1341 --5530776 bugfix, for demand view, start with 1 instead of 0
1342 if (p_start_index is not null) then
1343 current_index := p_start_index;
1344 end if;
1345
1346 while ( hasMore )
1347 loop -- {
1348
1349 open c_next_row(g_order_query_id, current_index);
1350 fetch c_next_row into p_supply_id, p_org_id, p_instance_id, p_op_seq_id,
1351 p_nodetype, p_nodepath;
1352 close c_next_row;
1353
1354 if ( p_first_supply_id is null) then
1355 p_first_supply_id := p_supply_id;
1356 end if;
1357
1358 put_line('node '
1359 || ' p_supply_id '|| p_supply_id
1360 || ' p_op_seq_id '|| p_op_seq_id
1361 || ' p_nodetype '|| p_nodetype
1362 || ' p_nodepath '|| p_nodepath
1363 || ' current_index '|| current_index
1364 || ' g_node_index '||g_node_index);
1365
1366 if ( p_nodetype = JOB_NODE
1367 or ( p_nodetype = COPROD_NODE and g_peg_type in ( PEG_UP, PEG_DOWN, PEG_ORDERS ))) then -- {
1368
1369 open c_next_node_level(g_order_query_id, g_peg_type, current_index);
1370 fetch c_next_node_level into g_node_level;
1371 close c_next_node_level;
1372
1373 if ( g_peg_type in ( PEG_DOWN, PEG_ORDERS ) ) then -- {
1374 insertOpIntoMFQ(p_instance_id, p_org_id, p_supply_id);
1375 addOpResNodes(p_nodepath, p_instance_id, p_org_id, p_supply_id, l_first_op);
1376 if ( g_peg_type = PEG_DOWN ) then
1377 insertOpJobFromMDIntoMFQ( l_first_op, p_instance_id, p_org_id, p_supply_id);
1378 moveDmdOp(p_supply_id);
1379 if ( p_first_supply_id = p_supply_id ) then
1380 addCoProdNodes(current_index, p_instance_id, p_org_id, p_supply_id);
1381 end if;
1382 addPegDownJobNodes(current_index, p_instance_id, p_org_id, p_supply_id, OP_JOB_NODE);
1383 addPegDownJobNodes(current_index, p_instance_id, p_org_id, p_supply_id, TRANSFER_JOB_NODE);
1384 end if;
1385 else --PEG_UP
1386 if ( g_peg_up_and_down <> PEG_UP_AND_DOWN and current_index = 0) then
1387 insertOpIntoMFQ(p_instance_id, p_org_id, p_supply_id);
1388 addOpResNodes(p_nodepath, p_instance_id, p_org_id, p_supply_id, l_first_op);
1389 end if;
1390 if ( p_first_supply_id = p_supply_id ) then
1391 addCoProdNodes(current_index, p_instance_id, p_org_id, p_supply_id);
1392 end if;
1393 addPegUpNodes(current_index, p_instance_id, p_org_id, p_supply_id);
1394 addEndDemandNodes(p_instance_id, p_supply_id, current_index);
1395 end if; -- }
1396 end if; -- }
1397
1398 open c_next_row_index(g_order_query_id, current_index, g_peg_type);
1399 fetch c_next_row_index into current_index, l_dummy_sort;
1400 if ( c_next_row_index%notfound) then
1401 hasMore := false; -- no more data
1402 end if;
1403 close c_next_row_index;
1404 put_line('next row index '||current_index);
1405 end loop; -- }
1406
1407 put_line('ordersPegging out');
1408 END ordersPegging;
1409
1410 procedure demandView (p_list varchar2) is
1411
1412 l_inst_id msc_gantt_utils.number_arr;
1413 l_org_id msc_gantt_utils.number_arr;
1414 l_trans_id msc_gantt_utils.number_arr;
1415 l_op_seq_num msc_gantt_utils.number_arr;
1416 l_critical_flag msc_gantt_utils.number_arr;
1417
1418 s_inst_id number;
1419 s_org_id number;
1420 s_trans_id number;
1421
1422 i number ;
1423 l_one_record varchar2(100);
1424 l_parent_index number;
1425
1426 BEGIN
1427 put_line('demandView in');
1428
1429 i := 1;
1430 l_one_record := substr(p_list,instr(p_list,'(',1,i)+1,
1431 instr(p_list,')',1,i)-instr(p_list,'(',1,i)-1);
1432 s_inst_id := to_number(substr(l_one_record,1,instr(l_one_record,',')-1));
1433 s_trans_id := to_number(substr(l_one_record,instr(l_one_record,',')+1));
1434
1435 g_end_demand_id := s_trans_id;
1436
1437 open demand_cur(g_plan_id, g_end_demand_id);
1438 fetch demand_cur into s_inst_id, s_org_id, s_trans_id ;
1439 close demand_cur;
1440
1441 msc_gantt_utils.populateRowKeysIntoGantt(
1442 p_query_id => g_order_query_id,
1443 p_index => g_node_index,
1444 p_node_path => g_node_index,
1445 p_node_type => END_DEMAND_NODE,
1446 p_transaction_id => s_trans_id,
1447 p_inst_id => s_inst_id,
1448 p_org_id => s_org_id,
1449 p_critical_flag => 1,
1450 p_node_level => g_node_level
1451 );
1452
1453 g_node_level := g_node_level + PEG_DOWN;
1454 l_parent_index := g_node_index;
1455
1456 open end_pegs_cur(g_plan_id, g_end_demand_id);
1457 fetch end_pegs_cur bulk collect into l_inst_id, l_org_id, l_trans_id, l_critical_flag;
1458 close end_pegs_cur;
1459
1460 for a in 1.. l_org_id.count
1461 loop -- {
1462 g_node_index := g_node_index + PEG_DOWN;
1463
1464 msc_gantt_utils.populateRowKeysIntoGantt(
1465 p_query_id => g_order_query_id,
1466 p_index => g_node_index,
1467 p_node_path => g_node_index,
1468 p_node_type => JOB_NODE,
1469 p_transaction_id => l_trans_id(a),
1470 p_inst_id => l_inst_id(a),
1471 p_org_id => l_org_id(a),
1472 p_critical_flag => l_critical_flag(a),
1473 p_node_level => g_node_level
1474 );
1475
1476 updateParentLink(g_order_query_id, g_node_index, l_parent_index, null);
1477
1478 end loop; -- }
1479
1480 g_node_level := g_node_level + PEG_DOWN;
1481
1482 g_end_peg_query_id := msc_gantt_utils.getMFQSequence(g_end_peg_query_id);
1483 msc_gantt_utils.populateEndPegsMFQ(g_plan_id, g_end_demand_id, g_end_peg_query_id);
1484
1485 ordersPegging(1);
1486
1487 put_line('demandView out');
1488 END demandView;
1489
1490 procedure updateNodeLevels(p_query_id number) is
1491 l_min_node_level number;
1492
1493 cursor c_min_level is
1494 select abs(min(node_level))
1495 from msc_gantt_query
1496 where query_id = p_query_id;
1497
1498 begin
1499 open c_min_level;
1500 fetch c_min_level into l_min_node_level;
1501 close c_min_level;
1502
1503 update msc_gantt_query
1504 set node_level = l_min_node_level + node_level + 1
1505 where query_id = p_query_id;
1506 end updateNodeLevels;
1507
1508 function getNodesCount(p_query_id number) return varchar2 is
1509 cursor c_node_count_cur is
1510 select node_type, count(*)
1511 from msc_gantt_query
1512 where query_id = p_query_id
1513 group by node_type;
1514
1515 cursor c_first_row_cur is
1516 select min(row_index)
1517 from msc_gantt_query
1518 where query_id = p_query_id;
1519
1520 l_node_type number;
1521 l_count number;
1522 l_first_index number;
1523 l_total_count number := 0;
1524 l_node_count varchar2(50);
1525 begin
1526 open c_node_count_cur;
1527 loop -- {
1528 fetch c_node_count_cur into l_node_type, l_count;
1529 exit when c_node_count_cur%notfound;
1530 l_total_count := l_total_count + l_count;
1531 if ( l_node_count is null ) then
1532 l_node_count := l_node_type || COLON_SEPARATOR || l_count;
1533 else
1534 l_node_count := l_node_count || COLON_SEPARATOR ||
1535 l_node_type || COLON_SEPARATOR || l_count;
1536 end if;
1537 end loop; -- }
1538 close c_node_count_cur;
1539
1540 open c_first_row_cur;
1541 fetch c_first_row_cur into l_first_index;
1542 close c_first_row_cur;
1543
1544 l_node_count := l_total_count || COLON_SEPARATOR || l_first_index
1545 || COLON_SEPARATOR || l_node_count;
1546 return l_node_count;
1547 end getNodesCount;
1548
1549 --
1550 -- Public Apis
1551 --
1552 function orderView(p_query_id in number,
1553 p_plan_id number, p_list varchar2, p_filter_type number,
1554 p_view_type number, p_peg_type number,
1555 p_node_count out nocopy varchar2,
1556 p_refresh boolean default false) return number is
1557
1558 cursor c_max_index_cur (l_query number) is
1559 select max(row_index)
1560 from msc_gantt_query
1561 where query_id = l_query ;
1562 BEGIN
1563 put_line('orderView in');
1564 g_node_index := 0;
1565 g_node_level := 1;
1566 g_peg_type := p_peg_type;
1567 g_same_peg := ALL_PEG;
1568 g_peg_up_and_down := p_peg_type;
1569
1570 g_plan_info := getPlanInfo(p_plan_id);
1571 if (p_query_id is null) then
1572 g_order_query_id := msc_gantt_utils.getGanttSequence();
1573 else
1574 g_order_query_id := p_query_id;
1575 end if;
1576
1577 if ( p_view_type = DEMAND_VIEW ) then -- {
1578 g_node_level := 1;
1579 g_peg_type := PEG_DOWN;
1580 -- g_same_peg := SAME_PEG;
1581 g_same_peg := ALL_PEG; -- 3863300 bug fix...
1582
1583 g_op_query_id := msc_gantt_utils.getMFQSequence(g_op_query_id);
1584 g_dem_op_query_id := msc_gantt_utils.getMFQSequence(g_dem_op_query_id);
1585
1586 demandView(p_list);
1587
1588 linkQueries(p_query_id, g_order_query_id, p_view_type, p_list);
1589 --updateNodeLevels(g_order_query_id);
1590 p_node_count := getNodesCount(g_order_query_id);
1591 return g_order_query_id;
1592 end if; -- }
1593
1594 if (p_query_id is null) then
1595 msc_gantt_utils.populateListIntoGantt(g_order_query_id, p_plan_id,
1596 p_list, p_filter_type, ORDER_VIEW);
1597 end if;
1598
1599 if ( g_peg_type = PEG_UP_AND_DOWN ) then -- {
1600 g_peg_type := PEG_DOWN;
1601 g_op_query_id := msc_gantt_utils.getMFQSequence(g_op_query_id);
1602 g_dem_op_query_id := msc_gantt_utils.getMFQSequence(g_dem_op_query_id);
1603 ordersPegging();
1604
1605 g_peg_type := PEG_UP;
1606 g_node_index := 0;
1607 g_op_query_id := msc_gantt_utils.getMFQSequence(g_op_query_id);
1608 g_dem_op_query_id := msc_gantt_utils.getMFQSequence(g_dem_op_query_id);
1609 ordersPegging();
1610 elsif ( g_peg_type in (PEG_UP, PEG_DOWN) ) then
1611 g_op_query_id := msc_gantt_utils.getMFQSequence(g_op_query_id);
1612 g_dem_op_query_id := msc_gantt_utils.getMFQSequence(g_dem_op_query_id);
1613 ordersPegging();
1614 elsif ( g_peg_type = PEG_ORDERS ) then
1615 open c_max_index_cur(g_order_query_id);
1616 fetch c_max_index_cur into g_node_index;
1617 close c_max_index_cur;
1618 g_op_query_id := msc_gantt_utils.getMFQSequence(g_op_query_id);
1619 g_dem_op_query_id := msc_gantt_utils.getMFQSequence(g_dem_op_query_id);
1620 ordersPegging();
1621 end if; -- }
1622
1623 if ( g_peg_type in (PEG_UP, PEG_UP_AND_DOWN) ) then
1624 --updateNodeLevels(g_order_query_id);
1625 null;
1626 end if;
1627
1628 linkQueries(p_query_id, g_order_query_id, p_view_type, p_list);
1629 p_node_count := getNodesCount(g_order_query_id);
1630
1631 put_line('orderView out');
1632 return g_order_query_id;
1633 END orderView;
1634
1635 function getResult(p_query_id number,
1636 p_from_index number, p_to_index number,
1637 p_out_data IN OUT NOCOPY msc_gantt_utils.maxCharTbl,
1638 p_node_level number default null,
1639 p_sort_node number default null,
1640 p_sort_column varchar2 default null,
1641 p_sort_order varchar2 default null,
1642 p_res_nodes_only varchar2 default null) return number is
1643 begin
1644 return msc_gantt_utils.getResult(p_query_id, p_from_index, p_to_index,
1645 g_plan_id, p_out_data, p_node_level, p_sort_node, p_sort_column, p_sort_order,
1646 p_res_nodes_only);
1647 end getResult;
1648
1649 --p_trx_list is (inst_id, trx id),(inst_id, trx_id)
1650 procedure resCharges(p_query_id number,
1651 p_trx_list varchar2, p_out_data IN OUT NOCOPY msc_gantt_utils.maxCharTbl) is
1652 begin
1653 msc_gantt_utils.resCharges(p_query_id, g_plan_id, p_trx_list, p_out_data);
1654 end resCharges;
1655
1656 --p_trx_list is (inst_id, node_type, trx id),(inst_id, node_type, trx_id)
1657 procedure segmentPegging(p_query_id number,
1658 p_trx_list varchar2, p_out_data IN OUT NOCOPY msc_gantt_utils.maxCharTbl) is
1659 begin
1660 msc_gantt_utils.segmentPegging(p_query_id, g_plan_id, p_trx_list, p_out_data);
1661 end segmentPegging;
1662
1663 function addSimuAltResToView(p_query_id number, p_view_type number,
1664 p_node_type number, p_node_list varchar2, p_out out nocopy varchar2) return number is
1665
1666 l_row_index number;
1667
1668 l_inst_id number;
1669 l_org_id number;
1670 l_dept_id number;
1671 l_res_id number;
1672 l_res_instance_id number;
1673 l_serial_number varchar2(30);
1674 l_one_record varchar2(250);
1675
1676 cursor c_res_inst is
1677 select res_instance_id,
1678 nvl(serial_number, MBP_NULL_VALUE_CHAR) serial_number
1679 from msc_dept_res_instances
1680 where plan_id = g_plan_id
1681 and sr_instance_id = l_inst_id
1682 and organization_id = l_org_id
1683 and department_id = l_dept_id
1684 and resource_id = l_res_id;
1685
1686 l_row_count number;
1687 begin
1688 select nvl(max(row_index),0)
1689 into l_row_index
1690 from msc_gantt_query
1691 where query_id = p_query_id;
1692
1693 l_one_record := substr(p_node_list,instr(p_node_list,'(',1,1)+1,
1694 instr(p_node_list,')',1,1)-instr(p_node_list,'(',1,1)-1);
1695
1696 msc_gantt_utils.parseResString(l_one_record, l_inst_id, l_org_id, l_dept_id, l_res_id,
1697 l_res_instance_id, l_serial_number);
1698 l_one_record := null;
1699
1700 l_row_count := msc_gantt_utils.isResRowInGantt(p_query_id, g_plan_id, l_inst_id, l_org_id, l_dept_id,
1701 l_res_id, mbp_null_value, mbp_null_value_char);
1702
1703 if ( l_row_count = sys_no ) then
1704 msc_gantt_utils.populateResIntoGantt(p_query_id, l_row_index, l_one_record,
1705 g_plan_id, l_inst_id, l_org_id, l_dept_id, l_res_id, MBP_NULL_VALUE, MBP_NULL_VALUE_CHAR, sys_no);
1706 end if;
1707
1708 for c_res_inst_row in c_res_inst
1709 loop -- {
1710 l_row_count := msc_gantt_utils.isResRowInGantt(p_query_id, g_plan_id, l_inst_id, l_org_id,
1711 l_dept_id, l_res_id, c_res_inst_row.res_instance_id, c_res_inst_row.serial_number);
1712
1713 if (l_row_count = sys_no ) then
1714 --l_row_index := l_row_index + 1;
1715 msc_gantt_utils.populateResIntoGantt(p_query_id, l_row_index, l_one_record,
1716 g_plan_id, l_inst_id, l_org_id, l_dept_id, l_res_id,
1717 c_res_inst_row.res_instance_id, c_res_inst_row.serial_number, sys_no);
1718 end if;
1719 end loop; -- }
1720
1721 return l_row_index-1;
1722 end addSimuAltResToView;
1723
1724 function addResToResView(p_from_query_id number, p_to_query_id in out nocopy number,
1725 p_from_index number, p_critical number) return number is
1726
1727 cursor c_node_type_cur is
1728 select node_type, node_path
1729 from msc_gantt_query
1730 where query_id = p_from_query_id
1731 and row_index = p_from_index;
1732
1733 l_node_type number;
1734 l_node_path varchar2(250);
1735 l_row_index number;
1736 l_one_record varchar2(250);
1737
1738 v_inst_id msc_gantt_utils.number_arr;
1739 v_org_id msc_gantt_utils.number_arr;
1740 v_dept_id msc_gantt_utils.number_arr;
1741 v_res_id msc_gantt_utils.number_arr;
1742
1743 l_row_found number := sys_no;
1744 checkRow boolean := false;
1745
1746 begin
1747 open c_node_type_cur;
1748 fetch c_node_type_cur into l_node_type, l_node_path;
1749 close c_node_type_cur;
1750
1751 if ( p_to_query_id is null ) then -- {
1752 p_to_query_id := msc_gantt_utils.getGanttSequence();
1753 l_row_index := 1;
1754 else
1755 select nvl(max(row_index),0)
1756 into l_row_index
1757 from msc_gantt_query
1758 where query_id = p_to_query_id;
1759 checkRow := true;
1760 end if; -- }
1761
1762 if (p_critical = CRITICAL_PATH ) then -- {
1763 select distinct sr_instance_id, organization_id, department_id, resource_id
1764 bulk collect into v_inst_id, v_org_id, v_dept_id, v_res_id
1765 from msc_gantt_query mgq
1766 where query_id = p_from_query_id
1767 and node_type = RES_NODE
1768 and ((p_from_index = 0) or ( (l_node_type in (JOB_NODE, OP_NODE) and node_path like l_node_path||':%')
1769 or (l_node_type = RES_NODE and row_index = p_from_index) ))
1770 and nvl(critical_flag, mbp_null_value) > 0;
1771 else
1772 select distinct sr_instance_id, organization_id, department_id, resource_id
1773 bulk collect into v_inst_id, v_org_id, v_dept_id, v_res_id
1774 from msc_gantt_query mgq
1775 where query_id = p_from_query_id
1776 and node_type = RES_NODE
1777 and ((p_from_index = 0) or ( (l_node_type in (JOB_NODE, OP_NODE) and node_path like l_node_path||':%')
1778 or (l_node_type = RES_NODE and row_index = p_from_index) ));
1779 end if; -- }
1780
1781 for i in 1..v_inst_id.count
1782 loop -- {
1783 if ( checkRow ) then
1784 l_row_found := msc_gantt_utils.isResRowInGantt(p_to_query_id, g_plan_id,
1785 v_inst_id(i), v_org_id(i), v_dept_id(i), v_res_id(i), MBP_NULL_VALUE, MBP_NULL_VALUE_CHAR);
1786 end if;
1787
1788 if ( l_row_found = sys_no ) then
1789 msc_gantt_utils.populateResIntoGantt(p_to_query_id, l_row_index, l_one_record,
1790 g_plan_id, v_inst_id(i), v_org_id(i), v_dept_id(i), v_res_id(i),
1791 mbp_null_value, MBP_NULL_VALUE_CHAR, sys_yes); --5521235 will add res instances too
1792 l_row_index := l_row_index + 1;
1793 end if;
1794 end loop; -- }
1795
1796 return l_row_index - 1;
1797 end addResToResView;
1798
1799 function addSuppToSuppView(p_from_query_id number, p_to_query_id in out nocopy number,
1800 p_from_index number) return number is
1801
1802 cursor c_node_type_cur is
1803 select node_type, node_path
1804 from msc_gantt_query
1805 where query_id = p_from_query_id
1806 and row_index = p_from_index;
1807
1808 l_node_type number;
1809 l_node_path varchar2(250);
1810 l_row_index number;
1811 l_one_record varchar2(250);
1812
1813 v_inst_id msc_gantt_utils.number_arr;
1814 v_org_id msc_gantt_utils.number_arr;
1815 v_item_id msc_gantt_utils.number_arr;
1816 v_supp_id msc_gantt_utils.number_arr;
1817 v_supp_site_id msc_gantt_utils.number_arr;
1818
1819 l_row_found number := 0;
1820 checkRow boolean := false;
1821 begin
1822 open c_node_type_cur;
1823 fetch c_node_type_cur into l_node_type, l_node_path;
1824 close c_node_type_cur;
1825
1826 if ( l_node_type not in (JOB_NODE, COPROD_NODE) ) then
1827 return 0;
1828 end if;
1829
1830 if ( p_to_query_id is null ) then -- {
1831 p_to_query_id := msc_gantt_utils.getGanttSequence();
1832 l_row_index := 1;
1833 else
1834 select nvl(max(row_index),0) + 1
1835 into l_row_index
1836 from msc_gantt_query
1837 where query_id = p_to_query_id;
1838 checkRow := true;
1839 end if; -- }
1840
1841 select
1842 ms.sr_instance_id,
1843 ms.organization_id,
1844 ms.inventory_item_id,
1845 nvl(ms.supplier_id, mbp_null_value),
1846 nvl(ms.supplier_site_id, mbp_null_value)
1847 bulk collect into v_inst_id, v_org_id, v_item_id, v_supp_id, v_supp_site_id
1848 from msc_gantt_query mgq,
1849 msc_supplies ms
1850 where mgq.query_id = p_from_query_id
1851 and mgq.row_index = p_from_index
1852 and ms.plan_id = g_plan_id
1853 and ms.sr_instance_id = mgq.sr_instance_id
1854 and ms.transaction_id = mgq.transaction_id;
1855
1856 for i in 1..v_inst_id.count
1857 loop -- {
1858 if ( checkRow ) then
1859 open check_supp_row_cur (p_to_query_id, g_plan_id,
1860 v_inst_id(i), v_org_id(i), v_item_id(i), v_supp_id(i), v_supp_site_id(i));
1861 fetch check_supp_row_cur into l_row_found;
1862 close check_supp_row_cur;
1863 end if;
1864
1865 if ( l_row_found = 0 ) then
1866 msc_gantt_utils.populateSuppIntoGantt(p_to_query_id, l_row_index, l_one_record,
1867 g_plan_id, v_inst_id(i), v_org_id(i),v_item_id(i), v_supp_id(i), v_supp_site_id(i));
1868 l_row_index := l_row_index + 1;
1869 end if;
1870 end loop; -- }
1871
1872 -- put_line('addSuppToSuppView: rows added: '||l_row_index - 1);
1873 return l_row_index - 1;
1874 end addSuppToSuppView;
1875
1876 function AddToOrdersView(p_from_query_id number, p_to_query_id in out nocopy number,
1877 p_from_index number, p_from_view_type number,
1878 p_context_value varchar2, p_context_value2 varchar2 default null) return number is
1879 l_row_index number;
1880
1881 v_inst_id msc_gantt_utils.number_arr;
1882 v_org_id msc_gantt_utils.number_arr;
1883 v_trx_id msc_gantt_utils.number_arr;
1884
1885 l_row_found number := 0;
1886 checkRow boolean := false;
1887 l_temp_query_id number;
1888 l_node_count varchar2(100);
1889
1890 l_row_type number;
1891
1892 cursor c_rowtype (ll_query number, ll_index number)is
1893 select mgq.res_instance_id
1894 from msc_gantt_query mgq
1895 where mgq.query_id = ll_query
1896 and mgq.row_index = ll_index;
1897
1898 begin
1899 if ( p_to_query_id is null ) then -- {
1900 p_to_query_id := msc_gantt_utils.getGanttSequence();
1901 g_order_query_id := p_to_query_id;
1902 g_node_level := 1;
1903 l_row_index := 1;
1904 else
1905 select nvl(max(row_index),0) + 1
1906 into l_row_index
1907 from msc_gantt_query
1908 where query_id = p_to_query_id;
1909 checkRow := true;
1910 g_order_query_id := p_to_query_id;
1911 end if; -- }
1912
1913 open c_rowtype (p_from_query_id, p_from_index);
1914 fetch c_rowtype into l_row_type;
1915 close c_rowtype;
1916 if ( p_from_view_type = RES_ACTIVITIES_VIEW ) then -- {
1917 if (l_row_type = mbp_null_value) then
1918 select distinct mrr.sr_instance_id, mrr.organization_id, mrr.supply_id
1919 bulk collect into v_inst_id, v_org_id, v_trx_id
1920 from msc_gantt_query mgq,
1921 msc_gantt_dtl_query mgdq,
1922 msc_resource_requirements mrr
1923 where mgq.query_id = p_from_query_id
1924 and mgq.row_index = p_from_index
1925 and mgq.query_id = mgdq.query_id
1926 and mgq.row_index = mgdq.row_index
1927 and mgdq.transaction_id = to_number(p_context_value)
1928 and mrr.plan_id = g_plan_id
1929 and mrr.sr_instance_id = mgq.sr_instance_id
1930 and mrr.organization_id = mgq.organization_id
1931 and mrr.department_id = mgq.department_id
1932 and mrr.resource_id = mgq.resource_id
1933 and mrr.transaction_id = mgdq.transaction_id
1934 and mrr.parent_id = 2;
1935 else
1936 select distinct mrir.sr_instance_id, mrir.organization_id, mrir.supply_id
1937 bulk collect into v_inst_id, v_org_id, v_trx_id
1938 from msc_gantt_query mgq,
1939 msc_gantt_dtl_query mgdq,
1940 msc_resource_instance_reqs mrir
1941 where mgq.query_id = p_from_query_id
1942 and mgq.row_index = p_from_index
1943 and mgq.query_id = mgdq.query_id
1944 and mgq.row_index = mgdq.row_index
1945 and mgdq.transaction_id = to_number(p_context_value)
1946 and mrir.plan_id = g_plan_id
1947 and mrir.sr_instance_id = mgq.sr_instance_id
1948 and mrir.organization_id = mgq.organization_id
1949 and mrir.department_id = mgq.department_id
1950 and mrir.resource_id = mgq.resource_id
1951 and mrir.res_instance_id = mgq.res_instance_id
1952 and mrir.serial_number = mgq.serial_number
1953 and mrir.res_inst_transaction_id = mgdq.transaction_id
1954 and mrir.parent_id = 2;
1955 end if;
1956
1957 elsif ( p_from_view_type = RES_HOURS_VIEW ) then
1958
1959 select distinct mrr.sr_instance_id, mrr.organization_id, mrr.supply_id
1960 bulk collect into v_inst_id, v_org_id, v_trx_id
1961 from msc_gantt_query mgq,
1962 msc_gantt_dtl_query mgdq,
1963 msc_resource_requirements mrr
1964 where mgq.query_id = p_from_query_id
1965 and mgq.row_index = p_from_index
1966 and mgq.query_id = mgdq.query_id
1967 and mgq.row_index = mgdq.row_index
1968 and mrr.plan_id = g_plan_id
1969 and mrr.sr_instance_id = mgq.sr_instance_id
1970 and mrr.organization_id = mgq.organization_id
1971 and mrr.department_id = mgq.department_id
1972 and mrr.resource_id = mgq.resource_id
1973 and ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrr.start_date,
1974 mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
1975 between to_date(p_context_value,FORMAT_MASK) and to_date(p_context_value2,FORMAT_MASK)
1976 or msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrr.start_date,
1977 mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
1978 between to_date(p_context_value,FORMAT_MASK) and to_date(p_context_value2,FORMAT_MASK))
1979 and mrr.parent_id = 2;
1980
1981 elsif ( p_from_view_type = RES_UNITS_VIEW ) then
1982 return 0; -- not supported for this view..
1983 elsif ( p_from_view_type = SUPPLIER_VIEW ) then
1984 select distinct msr.sr_instance_id, msr.organization_id, msr.supply_id
1985 bulk collect into v_inst_id, v_org_id, v_trx_id
1986 from msc_gantt_query mgq,
1987 msc_supplier_requirements msr
1988 where mgq.query_id = p_from_query_id
1989 and mgq.row_index = p_from_index
1990 and msr.plan_id = g_plan_id
1991 --and msr.sr_instance_id = mgq.sr_instance_id
1992 --and msr.organization_id = mgq.organization_id
1993 and msr.inventory_item_id = mgq.inventory_item_id
1994 and msr.supplier_id = mgq.supplier_id
1995 and msr.supplier_site_id = mgq.supplier_site_id
1996 and trunc(msr.consumption_date) between to_date(p_context_value,FORMAT_MASK)
1997 and to_date(p_context_value2,FORMAT_MASK);
1998
1999 end if; -- }
2000
2001 for i in 1..v_inst_id.count
2002 loop -- {
2003 if ( checkRow ) then
2004 open check_job_row_cur (g_order_query_id, g_plan_id, v_inst_id(i), v_trx_id(i));
2005 fetch check_job_row_cur into l_row_found;
2006 close check_job_row_cur;
2007 end if;
2008
2009 if ( l_row_found = 0) then
2010 msc_gantt_utils.populateRowKeysIntoGantt(
2011 p_query_id => g_order_query_id,
2012 p_index => l_row_index,
2013 p_node_path => l_row_index,
2014 p_node_type => JOB_NODE,
2015 p_inst_id => v_inst_id(i),
2016 p_org_id => v_org_id(i),
2017 p_transaction_id => v_trx_id(i),
2018 p_critical_flag => 0,
2019 p_node_level => 1
2020 );
2021
2022 l_row_index := l_row_index + 1;
2023 end if;
2024 end loop; -- }
2025
2026 l_temp_query_id := msc_gantt_pkg.orderview(g_order_query_id, g_plan_id,
2027 null, null, ORDER_VIEW,
2028 PEG_ORDERS, l_node_count);
2029
2030 return l_row_index - 1;
2031 end AddToOrdersView;
2032
2033 --
2034 --
2035 function getGanttRowCount(p_query_id number) return number is
2036 cursor c_row is
2037 select count(*)
2038 from msc_gantt_query
2039 where query_id = p_query_id;
2040 l_count number;
2041 begin
2042 open c_row;
2043 fetch c_row into l_count;
2044 close c_row;
2045 return l_count;
2046 end getGanttRowCount;
2047
2048 function resourceView (p_query_id in out nocopy number,
2049 p_plan_id number, p_list varchar2,
2050 p_filter_type number, p_view_type number,
2051 p_from_index number, p_to_index number,
2052 p_name_data IN OUT NOCOPY msc_gantt_utils.maxCharTbl,
2053 p_require_data IN OUT NOCOPY msc_gantt_utils.maxCharTbl,
2054 p_avail_data IN OUT NOCOPY msc_gantt_utils.maxCharTbl,
2055 p_batched_res_act number default null,
2056 p_display_type number default null,
2057 p_refresh boolean default false,
2058 p_sort_column varchar2 default null,
2059 p_sort_order varchar2 default null,
2060 p_change_view boolean default false,
2061 p_folder_id number default null) return number is
2062 begin
2063 put_line('resourceView in');
2064
2065 if ( p_query_id is null ) then -- {
2066 g_plan_info := getPlanInfo(p_plan_id);
2067 p_query_id := msc_gantt_utils.getGanttSequence();
2068
2069 msc_gantt_utils.populateListIntoGantt(p_query_id, p_plan_id, p_list,
2070 p_filter_type, p_view_type, p_folder_id);
2071
2072 elsif ( p_refresh ) then
2073
2074 update msc_gantt_query
2075 set is_fetched = SYS_NO,
2076 row_flag = SYS_NO
2077 where query_id = p_query_id;
2078
2079 delete from msc_gantt_dtl_query
2080 where query_id = p_query_id;
2081
2082 else
2083 update msc_gantt_query
2084 set is_fetched = SYS_NO,
2085 row_flag = SYS_NO
2086 where query_id = p_query_id;
2087
2088 delete from msc_gantt_dtl_query
2089 where query_id = p_query_id;
2090
2091 end if; -- }
2092
2093 msc_gantt_utils.sendResourceNames(p_query_id, p_from_index, p_to_index,
2094 p_name_data, p_sort_column, p_sort_order);
2095
2096 if ( p_view_type = RES_HOURS_VIEW ) then -- {
2097
2098 msc_gantt_utils.populateResReqGanttNew(p_query_id,
2099 g_plan_start_date, g_cutoff_date, nvl(p_display_type, display_none));
2100
2101 msc_gantt_utils.populateResAvailGantt(p_query_id, g_plan_start_date, g_cutoff_date);
2102 msc_gantt_utils.prepareResHoursGantt(p_query_id, p_plan_id, g_plan_start_date,
2103 g_cutoff_date, nvl(p_display_type, display_none));
2104
2105 msc_gantt_utils.sendResourceGantt(p_query_id, p_view_type, SYS_YES,
2106 p_require_data, p_avail_data, false, nvl(p_display_type, display_none));
2107
2108 elsif ( p_view_type = RES_UNITS_VIEW ) then
2109
2110 msc_gantt_utils.populateResReqGanttNew(p_query_id, g_plan_start_date, g_day_bkt_start_date);
2111 msc_gantt_utils.populateResAvailGantt(p_query_id, g_plan_start_date, g_day_bkt_start_date);
2112
2113 msc_gantt_utils.sendResourceGantt(p_query_id, p_view_type, SYS_NO, p_require_data,
2114 p_avail_data, false, DISPLAY_NONE);
2115
2116 elsif (p_view_type = RES_ACTIVITIES_VIEW ) then
2117
2118 msc_gantt_utils.populateResActGantt(p_query_id, g_plan_start_date, g_day_bkt_start_date,
2119 nvl(p_batched_res_act, RES_REQ_ROW_TYPE), p_require_data, nvl(p_display_type, display_none));
2120
2121 msc_gantt_utils.populateResAvailGantt(p_query_id, g_plan_start_date, g_day_bkt_start_date);
2122 msc_gantt_utils.sendResourceGantt(p_query_id, p_view_type, SYS_NO, p_require_data,
2123 p_avail_data, true, nvl(p_display_type, display_none));
2124
2125 end if; -- }
2126
2127 -- set the is_fetched to SYS_YES
2128 update msc_gantt_query
2129 set is_fetched = SYS_YES
2130 where query_id = p_query_id
2131 and row_flag = SYS_YES ;
2132
2133 put_line('resourceView out');
2134
2135 return getGanttRowCount(p_query_id);
2136 end resourceView ;
2137
2138 function supplierView(p_query_id in out nocopy number,
2139 p_plan_id number, p_list varchar2,
2140 p_filter_type number, p_view_type number,
2141 p_from_index number, p_to_index number,
2142 p_name_data IN OUT NOCOPY msc_gantt_utils.maxCharTbl,
2143 p_supp_data IN OUT NOCOPY msc_gantt_utils.maxCharTbl,
2144 p_refresh boolean default false,
2145 p_sort_column varchar2 default null,
2146 p_sort_order varchar2 default null) return number is
2147 begin
2148 put_line('supplierView in');
2149
2150 if ( p_query_id is null ) then -- {
2151 g_plan_info := getPlanInfo(p_plan_id);
2152 p_query_id := msc_gantt_utils.getGanttSequence();
2153
2154 msc_gantt_utils.populateListIntoGantt(p_query_id,
2155 p_plan_id, p_list, p_filter_type, p_view_type);
2156
2157 elsif ( p_refresh ) then
2158 update msc_gantt_query
2159 set is_fetched = SYS_NO,
2160 row_flag = SYS_NO
2161 where query_id = p_query_id;
2162
2163 delete from msc_gantt_dtl_query
2164 where query_id = p_query_id;
2165 else
2166 update msc_gantt_query
2167 set is_fetched = SYS_NO,
2168 row_flag = SYS_NO
2169 where query_id = p_query_id;
2170
2171 delete from msc_gantt_dtl_query
2172 where query_id = p_query_id;
2173 end if; -- }
2174
2175 msc_gantt_utils.sendSupplierNames(p_query_id, p_from_index, p_to_index, p_name_data,
2176 p_sort_column, p_sort_order);
2177
2178 msc_gantt_utils.populateSupplierGantt(p_query_id, g_plan_id, g_plan_start_date, g_cutoff_date);
2179 msc_gantt_utils.prepareSupplierGantt(p_query_id, p_plan_id, g_plan_start_date, g_cutoff_date);
2180 msc_gantt_utils.sendSupplierGantt(p_query_id, p_supp_data);
2181
2182 -- set the is_fetched to SYS_YES
2183 update msc_gantt_query
2184 set is_fetched = SYS_YES
2185 where query_id = p_query_id
2186 and row_flag = SYS_YES ;
2187
2188 return getGanttRowCount(p_query_id);
2189 put_line('supplierView out');
2190 end supplierView;
2191
2192 procedure sortResSuppView(p_query_id number,
2193 p_view_type number, p_from_index number, p_to_index number,
2194 p_name_data IN OUT NOCOPY msc_gantt_utils.maxCharTbl,
2195 p_sort_column varchar2 default null,
2196 p_sort_order varchar2 default null) is
2197 begin
2198 if ( p_view_type = SUPPLIER_VIEW ) then
2199 msc_gantt_utils.sendSupplierNames(p_query_id, p_from_index, p_to_index,
2200 p_name_data, p_sort_column, p_sort_order);
2201 elsif (p_view_type in (RES_ACTIVITIES_VIEW, RES_UNITS_VIEW, RES_HOURS_VIEW) ) then
2202 msc_gantt_utils.sendResourceNames(p_query_id, p_from_index, p_to_index,
2203 p_name_data, p_sort_column, p_sort_order);
2204 end if;
2205 end sortResSuppView;
2206
2207 procedure firmUnfirm(p_query_id number,
2208 p_view_type number, p_node_type number,
2209 p_firm_type number,
2210 p_start_date date, p_end_date date,
2211 p_trx_list varchar2,
2212 p_return_status OUT NOCOPY varchar2,
2213 p_out OUT NOCOPY varchar2,
2214 p_out_data IN OUT NOCOPY msc_gantt_utils.maxCharTbl,
2215 p_validate_flag boolean default true,
2216 p_res_firm_seq boolean default false,
2217 p_batched_res_act boolean default false) is
2218
2219 l_inst_id number;
2220 l_trx_id number;
2221 l_one_record varchar2(100);
2222
2223 i number;
2224 v_len number;
2225 begin
2226 if ( p_view_type not in (RES_ACTIVITIES_VIEW, DEMAND_VIEW, ORDER_VIEW)
2227 or p_node_type not in (RES_NODE, RES_INST_NODE, JOB_NODE, COPROD_NODE) ) then
2228 p_return_status := 'ERROR';
2229 p_out := 'INVALID_VIEW_OR_NODE';
2230 return;
2231 end if;
2232
2233 i :=1;
2234
2235 if ( p_node_type in (RES_NODE, RES_INST_NODE) ) then -- {
2236 if (p_res_firm_seq) then -- {
2237 msc_gantt_utils.firmResourceSeqPub(g_plan_id, p_trx_list, p_firm_type,
2238 p_return_status, p_out, p_node_type);
2239 elsif (p_batched_res_act) then
2240 l_one_record := substr(p_trx_list,
2241 instr(p_trx_list,'(',1,i)+1,instr(p_trx_list,')',1,i) - instr(p_trx_list,'(',1,i)-1);
2242 l_inst_id := to_number(substr(l_one_record,1,instr(l_one_record,',')-1));
2243 l_trx_id := to_number(substr(l_one_record,instr(l_one_record,',')+1));
2244 -- no firming of a batch...client should not call this..
2245 -- msc_gantt_utils.firmResourceBatchPub(g_plan_id, l_trx_id, l_inst_id, p_firm_type,
2246 -- p_return_status, p_out, p_node_type);
2247 else
2248 v_len := length(p_trx_list);
2249 i :=1;
2250 while v_len > 1 loop
2251 l_one_record := substr(p_trx_list,
2252 instr(p_trx_list,'(',1,i)+1,instr(p_trx_list,')',1,i) - instr(p_trx_list,'(',1,i)-1);
2253 l_inst_id := to_number(substr(l_one_record,1,instr(l_one_record,',')-1));
2254 l_trx_id := to_number(substr(l_one_record,instr(l_one_record,',')+1));
2255
2256 msc_gantt_utils.firmResourcePub(g_plan_id, l_trx_id, l_inst_id, p_firm_type,
2257 p_return_status, p_out, p_node_type);
2258 i := i+1;
2259 v_len := v_len - length(l_one_record)-3;
2260 end loop;
2261 end if; -- }
2262 elsif (p_node_type in (JOB_NODE, COPROD_NODE) ) then
2263 v_len := length(p_trx_list);
2264 i :=1;
2265 while v_len > 1 loop
2266 l_one_record := substr(p_trx_list,
2267 instr(p_trx_list,'(',1,i)+1,instr(p_trx_list,')',1,i) - instr(p_trx_list,'(',1,i)-1);
2268 l_inst_id := to_number(substr(l_one_record,1,instr(l_one_record,',')-1));
2269 l_trx_id := to_number(substr(l_one_record,instr(l_one_record,',')+1));
2270
2271 msc_gantt_utils.firmSupplyPub(g_plan_id, l_trx_id, l_inst_id,
2272 p_firm_type, p_start_date, p_end_date, g_plan_start_date, g_cutoff_date,
2273 p_return_status, p_out, p_validate_flag, p_node_type);
2274
2275 i := i+1;
2276 v_len := v_len - length(l_one_record)-3;
2277 end loop;
2278 end if; -- }
2279 end firmUnfirm;
2280
2281 procedure moveNode(p_query_id number, p_view_type number,
2282 p_node_type number, p_to_node_type number,
2283 p_trx_list varchar2, p_to_trx_list varchar2,
2284 p_start_date date, p_end_date date, p_duration varchar2,
2285 p_return_status OUT NOCOPY varchar2,
2286 p_out OUT NOCOPY varchar2,
2287 p_out_data IN OUT NOCOPY msc_gantt_utils.maxCharTbl,
2288 p_validate_flag boolean default true,
2289 p_res_firm_seq boolean default false,
2290 p_batched_res_act boolean default false) is
2291
2292 l_inst_id number;
2293 l_trx_id number;
2294
2295 l_to_res_id number;
2296 l_to_res_instance_id number;
2297 l_to_serial_number varchar2(30);
2298 l_to_alt_num number;
2299
2300 l_one_record varchar2(100);
2301
2302 i number;
2303 v_len number;
2304
2305 p_return_trx_id number;
2306 begin
2307 put_line('moveNode in');
2308
2309 savepoint start_of_submission; --save point
2310
2311 if ( p_view_type not in (RES_ACTIVITIES_VIEW, DEMAND_VIEW, ORDER_VIEW)
2312 or p_node_type not in (RES_NODE, RES_INST_NODE, JOB_NODE, COPROD_NODE) ) then
2313 p_return_status := 'ERROR';
2314 p_out := 'INVALID_VIEW_OR_NODE';
2315 return;
2316 end if;
2317
2318 if ( p_node_type in (RES_NODE, RES_INST_NODE) ) then -- {
2319 if ( p_to_node_type is not null ) then -- {
2320 i:= 1;
2321 l_one_record := substr(p_to_trx_list,
2322 instr(p_to_trx_list,'(',1,i)+1,instr(p_to_trx_list,')',1,i)
2323 - instr(p_to_trx_list,'(',1,i)-1);
2324 l_to_res_id := to_number(substr(l_one_record,1,instr(l_one_record,',')-1));
2325 l_to_res_instance_id := to_number(substr(l_one_record,instr(l_one_record,',',1,1)+1,
2326 instr(l_one_record,',',1,2)-instr(l_one_record,',',1,1)-1));
2327 l_to_serial_number := substr(l_one_record,instr(l_one_record,',',1,2)+1,
2328 instr(l_one_record,',',1,3)-instr(l_one_record,',',1,2)-1);
2329 l_to_alt_num := to_number(substr(l_one_record,instr(l_one_record,',',1,3)+1));
2330
2331 put_line(' to_node '||l_to_res_id||' '||l_to_res_instance_id||' '||l_to_serial_number||' '||l_to_alt_num);
2332
2333 l_one_record := substr(p_trx_list,
2334 instr(p_trx_list,'(',1,i)+1,instr(p_trx_list,')',1,i) - instr(p_trx_list,'(',1,i)-1);
2335 l_inst_id := to_number(substr(l_one_record,1,instr(l_one_record,',')-1));
2336 l_trx_id := to_number(substr(l_one_record,instr(l_one_record,',')+1));
2337
2338 put_line(' from_node '|| l_inst_id ||' '||l_trx_id);
2339
2340 if ( p_batched_res_act ) then -- {
2341 msc_gantt_utils.loadAltResourceBatch(g_plan_id,
2342 l_trx_id, l_inst_id, l_to_res_id, l_to_res_instance_id, l_to_serial_number,
2343 l_to_alt_num, p_node_type, p_to_node_type, p_return_trx_id,
2344 p_return_status, p_out);
2345 else
2346 msc_gantt_utils.loadAltResource(g_plan_id,
2347 l_trx_id, l_inst_id, l_to_res_id, l_to_res_instance_id, l_to_serial_number,
2348 l_to_alt_num, p_node_type, p_to_node_type, p_return_trx_id,
2349 p_return_status, p_out);
2350 end if; -- }
2351
2352 if ( nvl(p_return_status, 'ERROR') <> 'OK' ) then -- {
2353 put_line('error while offloading - rolling back ');
2354 ROLLBACK TO start_of_submission;
2355 return;
2356 end if; -- }
2357
2358 put_line(' offloading done .now move the trx '||p_return_trx_id);
2359
2360 msc_gantt_utils.moveResourcePub(g_plan_id, p_return_trx_id, l_inst_id,
2361 p_start_date, p_end_date, p_duration, g_plan_start_date, g_cutoff_date,
2362 p_return_status, p_out, p_validate_flag, p_res_firm_seq, p_batched_res_act, p_node_type);
2363
2364 else
2365
2366 v_len := length(p_trx_list);
2367 i :=1;
2368 while v_len > 1 loop
2369 -- from node
2370 l_one_record := substr(p_trx_list,
2371 instr(p_trx_list,'(',1,i)+1,instr(p_trx_list,')',1,i) - instr(p_trx_list,'(',1,i)-1);
2372 l_inst_id := to_number(substr(l_one_record,1,instr(l_one_record,',')-1));
2373 l_trx_id := to_number(substr(l_one_record,instr(l_one_record,',')+1));
2374
2375 put_line(' move inst trx '||l_inst_id||' - '||l_trx_id);
2376
2377 msc_gantt_utils.moveResourcePub(g_plan_id, l_trx_id, l_inst_id, p_start_date, p_end_date,
2378 p_duration, g_plan_start_date, g_cutoff_date, p_return_status, p_out,
2379 p_validate_flag, p_res_firm_seq, p_batched_res_act, p_node_type);
2380 i := i+1;
2381 v_len := v_len - length(l_one_record)-3;
2382 end loop;
2383
2384 end if; -- }
2385 elsif (p_node_type in (JOB_NODE, COPROD_NODE) ) then
2386
2387 v_len := length(p_trx_list);
2388 i :=1;
2389 while v_len > 1 loop
2390 -- from node
2391 l_one_record := substr(p_trx_list,
2392 instr(p_trx_list,'(',1,i)+1,instr(p_trx_list,')',1,i) - instr(p_trx_list,'(',1,i)-1);
2393 l_inst_id := to_number(substr(l_one_record,1,instr(l_one_record,',')-1));
2394 l_trx_id := to_number(substr(l_one_record,instr(l_one_record,',')+1));
2395
2396 put_line(' move inst trx '||l_inst_id||' - '||l_trx_id);
2397
2398 msc_gantt_utils.moveSupplyPub (g_plan_id, l_trx_id, p_start_date, p_end_date,
2399 g_plan_start_date, g_cutoff_date, p_return_status, p_out);
2400
2401 i := i+1;
2402 v_len := v_len - length(l_one_record)-3;
2403 end loop;
2404
2405 end if; -- }
2406 put_line('moveNode out');
2407 end moveNode;
2408
2409 procedure moveAndFirm(p_node_type number, p_to_node_type number,
2410 p_firm_type number, p_trx_list varchar2, p_to_trx_list varchar2,
2411 p_start_date date, p_end_date date, p_duration varchar2,
2412 p_return_status OUT NOCOPY varchar2, p_out OUT NOCOPY varchar2,
2413 p_out_data IN OUT NOCOPY msc_gantt_utils.maxCharTbl,
2414 p_validate_flag boolean default true,
2415 p_res_firm_seq boolean default false) is
2416 begin
2417
2418 if p_firm_type=0 then -- If user wants to unfirm, then do it first
2419 msc_gantt_pkg.firmUnfirm(null, RES_ACTIVITIES_VIEW, p_node_type,
2420 p_firm_type, p_start_date, p_end_date, p_trx_list,
2421 p_return_status, p_out, p_out_data,
2422 p_validate_flag, p_res_firm_seq, false);
2423 return;
2424 end if;
2425
2426 msc_gantt_pkg.moveNode(null, RES_ACTIVITIES_VIEW, p_node_type, p_to_node_type,
2427 p_trx_list, p_to_trx_list, p_start_date, p_end_date, p_duration,
2428 p_return_status, p_out, p_out_data,
2429 p_validate_flag, p_res_firm_seq, false);
2430
2431 if ( nvl(p_return_status, 'ERROR') <> 'OK' ) then -- {
2432 -- p_out := p_return_status; -- Bug4552734
2433 return;
2434 end if; -- }
2435 if p_firm_type <> 0 then
2436 msc_gantt_pkg.firmUnfirm(null, RES_ACTIVITIES_VIEW, p_node_type,
2437 p_firm_type, p_start_date, p_end_date, p_trx_list,
2438 p_return_status, p_out, p_out_data,
2439 p_validate_flag, p_res_firm_seq, false);
2440 end if;
2441 end moveAndFirm;
2442
2443 procedure updateResUnitsDirectly(p_query_id number,
2444 p_node_type number, p_inst_id number, p_trx_id number,
2445 p_assigned_units_hours number,
2446 p_return_status OUT NOCOPY varchar2,
2447 p_out OUT NOCOPY varchar2) is
2448
2449 l_res_hours number;
2450 l_child_count number;
2451 begin
2452 if ( p_node_type = RES_NODE ) then
2453 select count(*)
2454 into l_child_count
2455 from msc_resource_requirements mrr,
2456 msc_resource_instance_reqs mrir
2457 where mrr.plan_id = g_plan_id
2458 and mrr.sr_instance_id = p_inst_id
2459 and mrr.transaction_id = p_trx_id
2460 and mrr.plan_id = mrir.plan_id
2461 and mrr.sr_instance_id = mrir.sr_instance_id
2462 and mrr.organization_id = mrir.organization_id
2463 and mrr.department_id = mrir.department_id
2464 and mrr.resource_id = mrir.resource_id
2465 and nvl(mrr.parent_id,2) = 2;
2466
2467 if (l_child_count > 0) then
2468 p_return_status := 'ERROR';
2469 p_out := 'RES_SCHEDULED_TO_INSTANCE';
2470 return;
2471 end if;
2472
2473 select mrr.resource_hours
2474 into l_res_hours
2475 from msc_resource_requirements mrr
2476 where mrr.plan_id = g_plan_id
2477 and mrr.sr_instance_id = p_inst_id
2478 and mrr.transaction_id = p_trx_id
2479 for update of mrr.resource_hours nowait;
2480
2481 update msc_resource_requirements
2482 set status =0,
2483 applied=2,
2484 assigned_units = p_assigned_units_hours
2485 where plan_id = g_plan_id
2486 and transaction_id = p_trx_id
2487 and sr_instance_id = p_inst_id;
2488
2489 end if;
2490
2491 p_return_status := 'OK';
2492 end updateResUnitsDirectly;
2493
2494 procedure updateResHoursDirectly(p_query_id number,
2495 p_node_type number, p_inst_id number, p_trx_id number,
2496 p_resource_hours number, p_return_status OUT NOCOPY varchar2,
2497 p_out OUT NOCOPY varchar2) is
2498
2499 l_res_hours number;
2500
2501 begin
2502
2503 if ( p_node_type = RES_NODE ) then
2504 select mrr.resource_hours
2505 into l_res_hours
2506 from msc_resource_requirements mrr
2507 where mrr.plan_id = g_plan_id
2508 and mrr.sr_instance_id = p_inst_id
2509 and mrr.transaction_id = p_trx_id
2510 for update of mrr.resource_hours nowait;
2511
2512 update msc_resource_requirements
2513 set status =0,
2514 applied=2,
2515 resource_hours = p_resource_hours
2516 where plan_id = g_plan_id
2517 and transaction_id = p_trx_id
2518 and sr_instance_id = p_inst_id;
2519
2520 elsif ( p_node_type = RES_INST_NODE ) then
2521
2522 select mrir.resource_instance_hours
2523 into l_res_hours
2524 from msc_resource_instance_reqs mrir
2525 where mrir.plan_id = g_plan_id
2526 and mrir.sr_instance_id = p_inst_id
2527 and mrir.res_inst_transaction_id = p_trx_id
2528 for update of mrir.resource_instance_hours nowait;
2529
2530 update msc_resource_instance_reqs
2531 set status =0,
2532 applied=2,
2533 resource_instance_hours = p_resource_hours
2534 where plan_id = g_plan_id
2535 and res_inst_transaction_id = p_trx_id
2536 and sr_instance_id = p_inst_id;
2537 end if;
2538
2539 p_return_status := 'OK';
2540 end updateResHoursDirectly;
2541
2542 --
2543 --
2544 function getPlanInfo(p_plan_id number) return varchar2 is
2545 begin
2546 if (g_plan_id = p_plan_id) then
2547 return g_plan_info ;
2548 end if ;
2549
2550 g_plan_info := msc_gantt_utils.getPlanInfo(p_plan_id,
2551 g_first_date, g_last_date,
2552 g_hour_bkt_start_date, g_day_bkt_start_date,
2553 g_plan_start_date, g_cutoff_date, g_plan_type);
2554
2555 g_plan_id := p_plan_id;
2556 return g_plan_info ;
2557 end getPlanInfo;
2558
2559 procedure getUserPref(p_pref_id number) is
2560 begin
2561 msc_gantt_utils.getUserPref(p_pref_id);
2562 end getUserPref;
2563
2564 procedure getSimuResource(p_plan_id number,
2565 p_transaction_id number, p_instance_id number,
2566 p_name OUT NOCOPY varchar2, p_id OUT NOCOPY varchar2,
2567 p_node_type number default null) is
2568 begin
2569 msc_gantt_utils.getSimuResource(p_plan_id,
2570 p_transaction_id, p_instance_id, p_name, p_id, p_node_type);
2571 end getSimuResource;
2572
2573 procedure getAltResource(p_plan_id number,
2574 p_transaction_id number, p_instance_id number,
2575 p_name OUT NOCOPY varchar2, p_id OUT NOCOPY varchar2,
2576 p_node_type number default null,
2577 p_from_form number default null) is
2578 begin
2579 msc_gantt_utils.getAltResource(p_plan_id,
2580 p_transaction_id, p_instance_id, p_name, p_id, p_node_type, p_from_form);
2581 end getAltResource;
2582
2583 function isPlanGanttEnabled(p_plan_id number) return boolean is
2584 cursor isconstrained is
2585 select daily_resource_constraints,
2586 weekly_resource_constraints,
2587 period_resource_constraints, plan_type
2588 from msc_plans
2589 where plan_id = p_plan_id;
2590
2591 l_daily number;
2592 l_weekly number;
2593 l_monthly number;
2594 l_plantype number;
2595 begin
2596 open isconstrained;
2597 fetch isconstrained into l_daily, l_weekly, l_monthly, l_plantype;
2598 close isconstrained;
2599 if ( l_plantype in (4,5) or p_plan_id = -1 ) then
2600 return false;
2601 elsif ( l_daily = 1 or l_weekly = 1 or l_monthly = 1 ) then
2602 return true;
2603 end if;
2604 return false;
2605 end isPlanGanttEnabled;
2606
2607 function isPlanDSEnabled(p_plan_id number) return boolean is
2608 cursor c_dsplan is
2609 select nvl(nvl(curr_ds_enabled_flag,ds_enabled_flag), sys_no) ds_enabled_flag
2610 from msc_plan_organizations
2611 where plan_id = p_plan_id;
2612 l_ds_enabled number;
2613 begin
2614 open c_dsplan;
2615 loop
2616 fetch c_dsplan into l_ds_enabled;
2617 exit when c_dsplan%notfound;
2618 if ( l_ds_enabled = 1 ) then
2619 close c_dsplan;
2620 return true;
2621 end if;
2622 end loop;
2623 close c_dsplan;
2624 return false;
2625 end isPlanDSEnabled;
2626
2627 function getEndDemandIds(p_plan_id number, p_view_type number, p_node_type number,
2628 p_trx_list varchar2, p_date1 date default null, p_date2 date default null) return number is
2629 v_mfq_to_query_id number;
2630 v_mfq_from_query_id number;
2631 v_query_id number;
2632 v_node_count varchar2(100);
2633 v_len number;
2634 v_one_record varchar2(50);
2635 i number := 1;
2636
2637 l_inst_id number;
2638 l_org_id number;
2639 l_dept_id number;
2640 l_res_id number;
2641 l_res_instance_id number;
2642 l_serial_number varchar2(30);
2643
2644 begin
2645 v_mfq_from_query_id := msc_gantt_utils.getMFQSequence();
2646 v_mfq_to_query_id := msc_gantt_utils.getMFQSequence();
2647
2648 if ( p_view_type in (RES_ACTIVITIES_VIEW, DEMAND_VIEW, ORDER_VIEW) ) then -- {
2649 v_len := length(p_trx_list);
2650 while v_len > 1 loop
2651 v_one_record := substr(p_trx_list,instr(p_trx_list,
2652 '(',1,i)+1, instr(p_trx_list,')',1,i)-instr(p_trx_list,'(',1,i)-1);
2653
2654 msc_gantt_utils.populateOrdersIntoMFQ(null, null, v_mfq_from_query_id, v_one_record);
2655
2656 i := i+1;
2657 v_len := v_len - length(v_one_record)-3;
2658 end loop;
2659 end if; -- }
2660
2661 if ( p_view_type in (RES_ACTIVITIES_VIEW) ) then -- {
2662
2663 if ( p_node_type = RES_NODE ) then -- {
2664
2665 insert into msc_form_query
2666 (query_id, last_update_date, last_updated_by, creation_date, created_by,
2667 last_update_login, number1, number2)
2668 select distinct v_mfq_to_query_id, sysdate, -1, sysdate, -1, -1,
2669 mfp2.demand_id, mfp2.sr_instance_id
2670 from msc_full_pegging mfp2,
2671 msc_full_pegging mfp,
2672 msc_resource_requirements mrr,
2673 msc_form_query mfq
2674 where mfq.query_id = v_mfq_from_query_id
2675 and mrr.plan_id = p_plan_id
2676 and mrr.sr_instance_id = mfq.number2
2677 and mrr.transaction_id = mfq.number1
2678 and mfp.plan_id = mrr.plan_id
2679 and mfp.transaction_id = mrr.supply_id
2680 and mfp2.plan_id = mfp.plan_id
2681 and mfp2.pegging_id = mfp.end_pegging_id;
2682
2683 else
2684
2685 insert into msc_form_query
2686 (query_id, last_update_date, last_updated_by, creation_date, created_by,
2687 last_update_login, number1, number2)
2688 select distinct v_mfq_to_query_id, sysdate, -1, sysdate, -1, -1,
2689 mfp2.demand_id, mfp2.sr_instance_id
2690 from msc_full_pegging mfp2,
2691 msc_full_pegging mfp,
2692 msc_resource_instance_reqs mrir,
2693 msc_form_query mfq
2694 where mfq.query_id = v_mfq_from_query_id
2695 and mrir.plan_id = p_plan_id
2696 and mrir.sr_instance_id = mfq.number2
2697 and mrir.res_inst_transaction_id = mfq.number1
2698 and mfp.plan_id = mrir.plan_id
2699 and mfp.transaction_id = mrir.supply_id
2700 and mfp2.plan_id = mfp.plan_id
2701 and mfp2.pegging_id = mfp.end_pegging_id;
2702
2703 end if; -- }
2704
2705 elsif ( p_view_type in (RES_HOURS_VIEW) ) then
2706
2707 v_one_record := substr(p_trx_list,instr(p_trx_list,
2708 '(',1,i)+1, instr(p_trx_list,')',1,i)-instr(p_trx_list,'(',1,i)-1);
2709
2710 msc_gantt_utils.parseResString(v_one_record, l_inst_id, l_org_id,
2711 l_dept_id, l_res_id, l_res_instance_id, l_serial_number);
2712
2713 if ( p_node_type = RES_NODE ) then -- {
2714
2715 insert into msc_form_query
2716 (query_id, last_update_date, last_updated_by, creation_date, created_by,
2717 last_update_login, number1, number2)
2718 select distinct v_mfq_to_query_id, sysdate, -1, sysdate, -1, -1,
2719 mfp2.demand_id, mfp2.sr_instance_id
2720 from msc_full_pegging mfp2,
2721 msc_full_pegging mfp,
2722 msc_resource_requirements mrr
2723 where mrr.plan_id = p_plan_id
2724 and mrr.sr_instance_id = l_inst_id
2725 and mrr.organization_id = l_org_id
2726 and mrr.department_id = l_dept_id
2727 and mrr.resource_id = l_res_id
2728 and ( nvl(mrr.firm_start_date, mrr.start_date) between p_date1 and p_date2
2729 or nvl(mrr.firm_end_date, mrr.end_date) between p_date1 and p_date2
2730 or ( nvl(mrr.firm_start_date, mrr.start_date) <= p_date1
2731 and nvl(mrr.firm_end_date, mrr.end_date) >= p_date2) ) --5456033 bugfix
2732 and mfp.plan_id = mrr.plan_id
2733 and mfp.transaction_id = mrr.supply_id
2734 and mfp2.plan_id = mfp.plan_id
2735 and mfp2.pegging_id = mfp.end_pegging_id;
2736 else
2737
2738 insert into msc_form_query
2739 (query_id, last_update_date, last_updated_by, creation_date, created_by,
2740 last_update_login, number1, number2)
2741 select distinct v_mfq_to_query_id, sysdate, -1, sysdate, -1, -1,
2742 mfp2.demand_id, mfp2.sr_instance_id
2743 from msc_full_pegging mfp2,
2744 msc_full_pegging mfp,
2745 msc_resource_instance_reqs mrir
2746 where mrir.plan_id = p_plan_id
2747 and mrir.sr_instance_id = l_inst_id
2748 and mrir.organization_id = l_org_id
2749 and mrir.department_id = l_dept_id
2750 and mrir.resource_id = l_res_id
2751 and mrir.res_instance_id = l_res_instance_id
2752 and mrir.serial_number = l_serial_number
2753 and ( nvl(mrir.start_date, mrir.start_date) between p_date1 and p_date2
2754 or nvl(mrir.end_date, mrir.end_date) between p_date1 and p_date2
2755 or ( nvl(mrir.start_date, mrir.start_date) <= p_date1
2756 and nvl(mrir.end_date, mrir.end_date) >= p_date2) )
2757 and mfp.plan_id = mrir.plan_id
2758 and mfp.transaction_id = mrir.supply_id
2759 and mfp2.plan_id = mfp.plan_id
2760 and mfp2.pegging_id = mfp.end_pegging_id;
2761
2762 end if; -- }
2763
2764 elsif ( p_view_type in (DEMAND_VIEW, ORDER_VIEW) ) then
2765
2766 if ( p_node_type = RES_NODE ) then -- {
2767
2768 insert into msc_form_query
2769 (query_id, last_update_date, last_updated_by, creation_date, created_by,
2770 last_update_login, number1, number2)
2771 select distinct v_mfq_to_query_id, sysdate, -1, sysdate, -1, -1,
2772 mfp2.demand_id, mfp2.sr_instance_id
2773 from msc_full_pegging mfp2,
2774 msc_full_pegging mfp,
2775 msc_resource_requirements mrr,
2776 msc_form_query mfq
2777 where mfq.query_id = v_mfq_from_query_id
2778 and mrr.plan_id = p_plan_id
2779 and mrr.sr_instance_id = mfq.number2
2780 and mrr.transaction_id = mfq.number1
2781 and mfp.plan_id = mrr.plan_id
2782 and mfp.transaction_id = mrr.supply_id
2783 and mfp2.plan_id = mfp.plan_id
2784 and mfp2.pegging_id = mfp.end_pegging_id;
2785
2786 else
2787
2788 insert into msc_form_query
2789 (query_id, last_update_date, last_updated_by, creation_date, created_by,
2790 last_update_login, number1, number2)
2791 select distinct v_mfq_to_query_id, sysdate, -1, sysdate, -1, -1,
2792 mfp2.demand_id, mfp2.sr_instance_id
2793 from msc_full_pegging mfp2,
2794 msc_full_pegging mfp,
2795 msc_form_query mfq
2796 where mfq.query_id = v_mfq_from_query_id
2797 and mfp.plan_id = p_plan_id
2798 and mfp.sr_instance_id = mfq.number2
2799 and mfp.transaction_id = mfq.number1
2800 and mfp2.plan_id = mfp.plan_id
2801 and mfp2.pegging_id = mfp.end_pegging_id;
2802 end if; --}
2803 else
2804 return -1;
2805 end if; -- }
2806
2807 return v_mfq_to_query_id;
2808 end getEndDemandIds;
2809
2810 --5516790 bugfix
2811 function getNewViewStartDate(p_node_type number, p_trx_id number, p_to_view_type number) return date is
2812 cursor c_supp_view_date1 is
2813 select min(msr.consumption_date) start_date
2814 from msc_supplier_requirements msr
2815 where msr.plan_id = g_plan_id
2816 and msr.supply_id = p_trx_id;
2817
2818 cursor c_supp_view_date2 is
2819 select min(msr.consumption_date) start_date
2820 from msc_supplier_requirements msr,
2821 msc_resource_requirements mrr
2822 where mrr.plan_id = g_plan_id
2823 and mrr.transaction_id = p_trx_id
2824 and msr.plan_id = mrr.plan_id
2825 and msr.sr_instance_id = mrr.sr_instance_id
2826 and msr.supply_id = mrr.supply_id;
2827
2828 cursor c_res_view_date1 is
2829 select min(nvl(mrr.firm_start_date,mrr.start_date)) start_date
2830 from msc_resource_requirements mrr
2831 where mrr.plan_id = g_plan_id
2832 and mrr.supply_id = p_trx_id;
2833
2834 cursor c_res_view_date2 is
2835 select min(nvl(mrr.firm_start_date,mrr.start_date)) start_date
2836 from msc_resource_requirements mrr
2837 where mrr.plan_id = g_plan_id
2838 and mrr.transaction_id = p_trx_id;
2839
2840 l_date date;
2841 begin
2842 if (p_to_view_type in (RES_HOURS_VIEW,RES_UNITS_VIEW) ) then
2843 if (p_node_type = RES_NODE) then
2844 open c_res_view_date2;
2845 fetch c_res_view_date2 into l_date;
2846 close c_res_view_date2;
2847 return l_date;
2848 elsif (p_node_type in (JOB_NODE,COPROD_NODE) ) then
2849 open c_res_view_date1;
2850 fetch c_res_view_date1 into l_date;
2851 close c_res_view_date1;
2852 return l_date;
2853 end if;
2854 elsif (p_to_view_type = SUPPLIER_VIEW) then
2855 if (p_node_type = RES_NODE ) then
2856 open c_supp_view_date2;
2857 fetch c_supp_view_date2 into l_date;
2858 close c_supp_view_date2;
2859 return l_date;
2860 elsif (p_node_type in (JOB_NODE,COPROD_NODE) ) then
2861 open c_supp_view_date1;
2862 fetch c_supp_view_date1 into l_date;
2863 close c_supp_view_date1;
2864 return l_date;
2865 end if;
2866 end if;
2867 return l_date;
2868 end getNewViewStartDate;
2869
2870 procedure getProperty(p_plan_id number, p_instance_id number,
2871 p_transaction_id number, p_type number, p_view_type number,
2872 v_pro out NOCOPY varchar2, v_demand out NOCOPY varchar2) is
2873 l_end_demand_id number;
2874 begin
2875 msc_gantt_utils.getProperty(p_plan_id, p_instance_id, p_transaction_id,
2876 p_type, p_view_type, l_end_demand_id, v_pro, v_demand);
2877 end getProperty;
2878
2879 function getDebugProfile return varchar2 is
2880 begin
2881 return fnd_profile.value('MSC_JAVA_DEBUG');
2882 end;
2883
2884 End MSC_GANTT_PKG;