[Home] [Help]
PACKAGE BODY: APPS.MSC_GET_GANTT_DATA
Source
1 PACKAGE BODY Msc_Get_GANTT_DATA AS
2 /* $Header: MSCGNTDB.pls 120.1 2005/06/17 15:35:48 appldev $ */
3
4 field_seperator varchar2(5) := '|';
5 record_seperator varchar2(5) := '&';
6 resource_seperator varchar2(5) := '~';
7 format_mask varchar2(20) :='MM/DD/YYYY HH24:MI';
8 g_plan_id number;
9 g_last_date date;
10 g_first_date date;
11 g_cutoff_date date;
12 g_current_block number;
13 g_supply_rec_count number :=0;
14 g_supply_parentIndex number;
15 g_supply_childIndex number :=0;
16 g_supply_limit number := 10;
17 g_resource_limit number := 15;
18 g_has_more_supply boolean;
19 g_has_prev_supply boolean;
20 g_supply_query_id number;
21 g_res_query_id number;
22 g_supplier_query_id number;
23 g_find_query_id number;
24 g_end_demand_id number;
25 g_dmd_priority number;
26 TYPE number_arr IS TABLE OF number;
27 g_block_start_item number_arr := number_arr(0);
28 g_block_start_row number_arr := number_arr(0);
29 g_buy_text varchar2(2000) := fnd_message.get_string('MSC','BUY_TEXT');
30 g_make_text varchar2(2000) := fnd_message.get_string('MSC','MAKE_TEXT');
31 g_transfer_text varchar2(2000) := fnd_message.get_string('MSC','TRANSFER_TEXT');
32 NO_FIRM CONSTANT INTEGER :=0;
33 FIRM_START CONSTANT INTEGER :=1;
34 FIRM_END CONSTANT INTEGER :=2;
35 FIRM_RESOURCE CONSTANT INTEGER :=3;
36 FIRM_START_END CONSTANT INTEGER :=4;
37 FIRM_START_RES CONSTANT INTEGER :=5;
38 FIRM_END_RES CONSTANT INTEGER :=6;
39 FIRM_ALL CONSTANT INTEGER :=7;
40
41 ON_HAND CONSTANT INTEGER :=1;
42 BUY_SUPPLY CONSTANT INTEGER :=2;
43 MAKE_SUPPLY CONSTANT INTEGER :=3;
44 TRANSFER_SUPPLY CONSTANT INTEGER :=4;
45
46 peg_data peg_rec_type;
47 the_index number :=0;
48
49 FUNCTION get_debug_mode RETURN VARCHAR2 IS
50 BEGIN
51 return FND_PROFILE.Value('MSC_JAVA_DEBUG');
52 END;
53
54 FUNCTION replace_seperator(old_string varchar2) return varchar2 IS
55 new_string varchar2(30000);
56 BEGIN
57 new_string := old_string;
58 new_string := replace(new_string,record_Seperator,'*');
59 new_string := replace(new_string,resource_Seperator,'^');
60 new_string := replace(new_string,field_Seperator,':');
61 return new_string;
62 END replace_seperator;
63
64 Function fetchDeptResCode(p_plan_id number,
65 v_instance_id number,
66 v_org_id number,
67 v_dept_id number,
68 v_res_id number) RETURN varchar2 IS
69
70 CURSOR name IS
71 select mtp.organization_code
72 ||':'||mdr.department_code || ':' || mdr.resource_code
73 from msc_department_resources mdr,
74 msc_trading_partners mtp
75 where mdr.department_id = v_dept_id
76 and mdr.resource_id = v_res_id
77 and mdr.plan_id = p_plan_id
78 and mdr.organization_id = v_org_id
79 and mdr.sr_instance_id = v_instance_id
80 and mtp.partner_type =3
81 and mtp.sr_tp_id = mdr.organization_id
82 and mtp.sr_instance_id = mdr.sr_instance_id;
83
84 v_name varchar2(30);
85 BEGIN
86
87 OPEN name;
88 FETCH name INTO v_name;
89 CLOSE name;
90 return v_name;
91
92 END fetchDeptResCode;
93
94 Procedure setFetchRow(p_supply_limit number,
95 p_resource_limit number) IS
96 BEGIN
97 g_supply_limit := p_supply_limit;
98 g_resource_limit := p_resource_limit;
99
100 END setFetchRow;
101
102 Procedure fetchResourceData(p_plan_id number,
103 p_res_list varchar2,
104 p_fetch_type varchar2 default null,
105 v_require_data OUT NOCOPY Child_Rec_Type,
106 v_name OUT NOCOPY varchar2) IS
107 v_org_id number;
108 v_instance_id number;
109 v_dept_id number;
110 v_res_id number;
111 v_len number;
112 one_record varchar2(100);
113 i number:=1;
114 j number := 1;
115 a number:=0;
116 nameCount number:=0;
117 recordCount number:=0;
118 l_inventory_item_id number := -1;
119 l_resource_constraint VARCHAR2(20);
120
121 -------------------------------------------------------
122 -- bug 2116260: logic for late_flag changed
123 -- old logic:
124 -- late if need_by_date < new_schedule_date
125 -- new logic:
126 -- late if HLS generated resource constraint
127 -- (exception type = 36) while scheduling this
128 -- supply for this transaction (even if there is
129 -- one exception in any date-range)
130 --------------------------------------------------------
131 CURSOR resource_constraint_cur ( p_instance_id IN number
132 , p_plan_id IN number
133 , p_organization_id IN number
134 , p_inventory_item_id IN number
135 , p_department_id IN number
136 , p_resource_id IN number
137 , p_transaction_id IN number
138 ) IS
139 SELECT 'EXISTS'
140 FROM msc_exception_details
141 WHERE number1 = p_transaction_id
142 AND sr_instance_id = p_instance_id
143 AND plan_id = p_plan_id
144 and exception_type =36
145 AND organization_id = p_organization_id
146 AND inventory_item_id = p_inventory_item_id
147 AND department_id = p_department_id
148 AND resource_id = p_resource_id;
149
150 CURSOR req IS
151 select to_char(
152 decode(nvl(mrr.firm_flag,0),
153 NO_FIRM, mrr.start_date,
154 FIRM_RESOURCE, mrr.start_date,
155 FIRM_END,
156 mrr.firm_end_date - (mrr.end_date - mrr.start_date),
157 FIRM_END_RES,
158 mrr.firm_end_date - (mrr.end_date - mrr.start_date),
159 nvl(mrr.firm_start_date, mrr.start_date)),
160 format_mask) start_date,
161 to_char(least(g_cutoff_date,
162 nvl(
163 decode(nvl(mrr.firm_flag,0),
164 NO_FIRM, mrr.end_date,
165 FIRM_RESOURCE, mrr.end_date,
166 FIRM_START,
167 mrr.firm_start_date + (mrr.end_date - mrr.start_date),
168 FIRM_START_RES,
169 mrr.firm_start_date + (mrr.end_date - mrr.start_date),
170 nvl(mrr.firm_end_date, mrr.end_date)),mrr.start_date)),
171 format_mask) end_date,
172 msc_get_gantt_data.order_number(ms.order_type,ms.order_number,
173 ms.plan_id, ms.sr_instance_id,
174 ms.transaction_id, ms.disposition_id)
175 ||'/'||
176 msi.item_name
177 ||'('||mrr.operation_seq_num||':'||mrr.resource_seq_num
178 ||')' job_name,
179 mrr.transaction_id,
180 nvl(mrr.status,0) status,
181 nvl(mrr.applied,0) applied,
182 mfg.meaning supply_type,
183 mrr.sr_instance_id,
184 nvl(mrr.firm_flag,0) res_firm_flag,
185 ms.firm_planned_type sup_firm_flag,
186 decode(sign(ms.new_schedule_date - (ms.need_by_date+1)), 1,
187 1,0) late_flag,
188 mrr.supply_id
189 from msc_resource_requirements mrr,
190 msc_supplies ms,
191 msc_items msi,
192 mfg_lookups mfg
193 where mrr.organization_id =v_org_id
194 and mrr.sr_instance_id = v_instance_id
195 and mrr.department_id = v_dept_id
196 and mrr.resource_id = v_res_id
197 and mrr.plan_id = p_plan_id
198 and mrr.end_date is not null
199 and ms.inventory_item_id = msi.inventory_item_id
200 and mfg.lookup_type = 'MRP_ORDER_TYPE'
201 and mfg.lookup_code = ms.order_type
202 and nvl(mrr.parent_id,2) =2
203 and nvl(mrr.firm_start_date,mrr.start_date) <= g_cutoff_date
204 and ms.plan_id = mrr.plan_id
205 and ms.transaction_id = mrr.supply_id
206 and ms.sr_instance_id = mrr.sr_instance_id
207 order by mrr.batch_number, nvl(mrr.firm_start_date, mrr.start_date);
208
209 CURSOR req_find IS
210 select to_char(
211 decode(nvl(mrr.firm_flag,0),
212 NO_FIRM, mrr.start_date,
216 FIRM_END_RES,
213 FIRM_RESOURCE, mrr.start_date,
214 FIRM_END,
215 mrr.firm_end_date - (mrr.end_date - mrr.start_date),
217 mrr.firm_end_date - (mrr.end_date - mrr.start_date),
218 nvl(mrr.firm_start_date, mrr.start_date)),
219 format_mask) start_date,
220 to_char(least(g_cutoff_date,
221 decode(nvl(mrr.firm_flag,0),
222 NO_FIRM, mrr.end_date,
223 FIRM_RESOURCE, mrr.end_date,
224 FIRM_START,
225 mrr.firm_start_date + (mrr.end_date - mrr.start_date),
226 FIRM_START_RES,
227 mrr.firm_start_date + (mrr.end_date - mrr.start_date),
228 nvl(mrr.firm_end_date, mrr.end_date))),
229 format_mask) end_date,
230 msc_get_gantt_data.order_number(ms.order_type,ms.order_number,
231 ms.plan_id, ms.sr_instance_id,
232 ms.transaction_id, ms.disposition_id)
233 ||'/'||
234 msi.item_name
235 ||'('||mrr.operation_seq_num||':'||mrr.resource_seq_num
236 ||')' job_name,
237 mrr.transaction_id,
238 nvl(mrr.status,0) status,
239 nvl(mrr.applied,0) applied,
240 mfg.meaning supply_type,
241 mrr.sr_instance_id,
242 nvl(mrr.firm_flag,0) res_firm_flag,
243 ms.firm_planned_type sup_firm_flag,
244 decode(sign(ms.new_schedule_date - (ms.need_by_date+1)), 1,
245 1,0) late_flag,
246 mrr.supply_id
247 from msc_resource_requirements mrr,
248 msc_supplies ms,
249 msc_items msi,
250 mfg_lookups mfg,
251 msc_form_query mfq
252 where mrr.organization_id =v_org_id
253 and mrr.sr_instance_id = v_instance_id
254 and mrr.department_id = v_dept_id
255 and mrr.resource_id = v_res_id
256 and mrr.plan_id = p_plan_id
257 and mrr.end_date is not null
258 and ms.inventory_item_id = msi.inventory_item_id
259 and mfg.lookup_type = 'MRP_ORDER_TYPE'
260 and mfg.lookup_code = ms.order_type
261 and nvl(mrr.parent_id,2) =2
262 and nvl(mrr.firm_start_date,mrr.start_date) <= g_cutoff_date
263 and ms.plan_id = mrr.plan_id
264 and ms.transaction_id = mrr.supply_id
265 and ms.sr_instance_id = mrr.sr_instance_id
266 and mfq.number6 = mrr.transaction_id
267 and mfq.query_id = g_find_query_id
268 order by mrr.batch_number, nvl(mrr.firm_start_date, mrr.start_date);
269
270 req_rec req%ROWTYPE;
271
272 one_name varchar2(100);
273 rowCount number;
274 currentItem number;
275 startRow number;
276 p_has_previous number :=0;
277 BEGIN
278
279 -- parse the resource_list
280 -- the format of res_list is
281 -- (instance_id, org_id, dept_id, res_id),(ins_id, org_id, dept_id, res_id)
282
283 if p_fetch_type is null then -- start from beginning
284 g_current_block := 1;
285 g_block_start_item.delete;
286 g_block_start_row.delete;
287 g_block_start_item.extend;
288 g_block_start_row.extend;
289 g_block_start_item(1) := 1;
290 g_block_start_row(1) :=0;
291 elsif p_fetch_type = 'PREV' then
292 g_current_block := g_current_block -1;
293 elsif p_fetch_type = 'NEXT' then
294 g_current_block := g_current_block +1;
295 elsif p_fetch_type = 'CURRENT' then
296 g_current_block := nvl(g_current_block, 1);
297 end if;
298
299 v_len := length(p_res_list);
300 recordCount := 0;
301 currentItem := g_block_start_item(g_current_block);
302
303 while v_len > 0 and j < g_resource_limit+2 LOOP
304 one_record :=
305 substr(p_res_list,instr(p_res_list,'(',1,i)+1,
306 instr(p_res_list,')',1,i)-instr(p_res_list,'(',1,i)-1);
307 v_instance_id := to_number(substr(one_record,1,instr(one_record,',')-1));
308 v_org_id := to_number(substr(one_record,instr(one_record,',',1,1)+1,
309 instr(one_record,',',1,2)-instr(one_record,',',1,1)-1));
310 v_dept_id := to_number(substr(one_record,instr(one_record,',',1,2)+1
311 ,instr(one_record,',',1,3)-instr(one_record,',',1,2)-1));
312 v_res_id := to_number(substr(one_record,instr(one_record,',',1,3)+1));
313 recordCount := recordCount +1;
314
315 if recordCount >= currentItem then
316 rowCount :=0;
317 a :=0;
318 if recordCount = currentItem then
319 startRow :=g_block_start_row(g_current_block);
320 else
321 startRow :=0;
322 end if;
323
324 if g_find_query_id is null then
325 OPEN req;
326 else
327 OPEN req_find;
328 end if;
329 LOOP
330 if g_find_query_id is null then
331 FETCH req INTO req_rec;
332 EXIT WHEN req%NOTFOUND or j > g_resource_limit +1 + p_has_previous or
333 a > startRow + g_resource_limit;
334 else
335 FETCH req_find INTO req_rec;
336 EXIT WHEN req_find%NOTFOUND or j > g_resource_limit +1 + p_has_previous or
337 a > startRow + g_resource_limit;
338 end if;
339 a := a+1;
340
341 if a > startRow then
342 if g_current_block <> 1 and j = 1 then -- add prev node
346 v_require_data.transaction_id(j):= -1;
343 v_require_data.start_date(j) := req_rec.start_date;
344 v_require_data.end_date(j) := req_rec.start_date;
345 v_require_data.name(j) := 'Previous '||g_resource_limit;
347 v_require_data.status(j):= req_rec.status;
348 v_require_data.applied(j):= req_rec.applied;
349 v_require_data.supply_type(j):= req_rec.supply_type;
350 v_require_data.instance_id(j):= req_rec.sr_instance_id;
351 v_require_data.res_firm_flag(j):= req_rec.res_firm_flag;
352 v_require_data.sup_firm_flag(j):= req_rec.sup_firm_flag;
353 v_require_data.late_flag(j):= 0;
354 j := j+1;
355 rowCount := rowCount+1;
356 p_has_previous :=1;
357
358 end if;
359
360 if j = g_resource_limit +p_has_previous +1 then -- add next node
361 v_require_data.start_date(j) := req_rec.start_date;
362 v_require_data.end_date(j) := req_rec.start_date;
363 v_require_data.name(j) := 'Next '||g_resource_limit;
364 v_require_data.transaction_id(j):= -2;
365 v_require_data.status(j):= req_rec.status;
366 v_require_data.applied(j):= req_rec.applied;
367 v_require_data.supply_type(j):= req_rec.supply_type;
368 v_require_data.instance_id(j):= req_rec.sr_instance_id;
369 v_require_data.res_firm_flag(j):= req_rec.res_firm_flag;
370 v_require_data.sup_firm_flag(j):= req_rec.sup_firm_flag;
371 v_require_data.late_flag(j):= 0;
372 j := j+1;
373 rowCount := rowCount+1;
374
375 elsif j <= g_resource_limit + p_has_previous then
376 v_require_data.start_date(j) := req_rec.start_date;
377 v_require_data.end_date(j) := req_rec.end_date;
378 v_require_data.name(j) := replace_seperator(req_rec.job_name);
379 v_require_data.transaction_id(j):= req_rec.transaction_id;
380 v_require_data.status(j):= req_rec.status;
381 v_require_data.applied(j):= req_rec.applied;
382 v_require_data.supply_type(j):= req_rec.supply_type;
383 v_require_data.instance_id(j):= req_rec.sr_instance_id;
384 v_require_data.res_firm_flag(j):= req_rec.res_firm_flag;
385 v_require_data.sup_firm_flag(j):= req_rec.sup_firm_flag;
386 v_require_data.late_flag(j):= req_rec.late_flag;
387
388 if v_require_data.res_firm_flag(j) >= 8 then
389 v_require_data.res_firm_flag(j) := 0;
390 end if;
391 OPEN resource_constraint_cur ( v_instance_id
392 , p_plan_id
393 , v_org_id
394 , l_inventory_item_id
395 , v_dept_id
396 , v_res_id
397 , req_rec.supply_id
398 );
399 FETCH resource_constraint_cur INTO l_resource_constraint;
400 -- check for resource constraint exceptions
401 IF resource_constraint_cur%FOUND THEN
402 -- late_flag is true(1) if exception exists
403 v_require_data.late_flag(j) := 1;
404 ELSE
405 -- and false(0) if it doesn't
406 v_require_data.late_flag(j) := 0;
407 END IF;
408 CLOSE resource_constraint_cur;
409
410 j := j+1;
411 rowCount := rowCount+1;
412 end if; -- if j = g_resource_limit +1
413
414 end if; -- if a > startRow
415 END LOOP;
416
417 if g_find_query_id is null then
418 CLOSE req;
419 else
420 CLOSE req_find;
421 end if;
422
423 one_name :=null;
424 one_name := fetchDeptResCode(p_plan_id, v_instance_id, v_org_id,
425 v_dept_id, v_res_id);
426 IF one_name is not null THEN
427 nameCount := nameCount+1;
428 one_name := replace_seperator(one_name) || field_seperator ||
429 v_instance_id || field_seperator ||
430 v_org_id || field_seperator ||
431 v_dept_id || field_seperator ||
432 v_res_id ;
433 v_require_data.record_count(nameCount) := rowCount;
434
435 IF v_name IS NULL THEN
436 v_name := one_name;
437 ELSE
438 v_name := v_name || resource_seperator || one_name;
439 END IF;
440 END IF;
441
442 end if; -- end of if recordCount >= g_block_start_item(g_current_block)
443 i := i+1;
444 v_len := v_len - length(one_record)-3;
445
446 END LOOP; -- while v_len > 0
447 v_name := nameCount || resource_seperator || v_name;
448
449 if p_fetch_type = 'NEXT' or p_fetch_type is null then
450 g_block_start_item.extend;
451 g_block_start_row.extend;
452 g_block_start_item(g_current_block+1) := recordCount;
453 g_block_start_row(g_current_block+1) := a-1;
454 end if;
455
456 END;
457
458 Procedure fetchLoadData(p_plan_id number,
459 p_res_list varchar2,
460 p_start varchar2 default null,
461 p_end varchar2 default null,
462 v_require_data IN OUT NOCOPY maxCharTbl,
463 v_avail_data OUT NOCOPY varchar2) IS
464 v_org_id number;
465 v_instance_id number;
466 v_dept_id number;
467 v_res_id number;
468 v_len number;
469 one_record varchar2(100);
470 i number:=1;
471 j number:=1;
472 k number:=0;
473 a number;
474 b number;
475 oneAvailRecord varchar2(32000);
476 oneAssignRecord maxCharTbl := maxCharTbl(0);
477 availCount number;
478 reqCount number;
479 p_day_bkt_start_date date;
480 p_hour_bkt_start_date date;
481 new_hour number;
482 time_change boolean :=false;
483 v_total_avail number;
484 CURSOR line_rate IS
485 select max_rate
486 from msc_department_resources
487 where organization_id =v_org_id
488 and sr_instance_id = v_instance_id
489 and department_id = v_dept_id
490 and resource_id = v_res_id
491 and plan_id = -1;
492
493 CURSOR finite_avail IS
494 select 1
495 from msc_net_resource_avail
496 where organization_id =v_org_id
497 and sr_instance_id = v_instance_id
498 and department_id = v_dept_id
499 and resource_id = v_res_id
500 and plan_id = p_plan_id
501 and nvl(parent_id, 0) <> -1;
502
503 v_finite_avail number;
504
505 TYPE date_arr IS TABLE OF date;
506 v_req_start date_arr;
507 v_req_end date_arr;
508 v_avail_start date_arr;
509 v_avail_end date_arr;
510 v_bkt_start date_arr;
511 v_bkt_end date_arr;
512
513 v_req_qty number_arr;
514 v_avail_qty number_arr;
515 v_over_cap number_arr;
516 v_batch number_arr;
517
518 v_qty number :=0;
519 new_start date;
520 new_end date;
521 bkt_qty number :=0;
522 max_cap number;
523 eff_rate number;
524
525 v_line_rate number;
526 v_dummy number;
527 v_max_len number;
528 v_one_record varchar2(200);
529
530 BEGIN
531 select nvl(MIN_CUTOFF_BUCKET,0)+nvl(HOUR_CUTOFF_BUCKET,0)+data_start_date,
532 nvl(MIN_CUTOFF_BUCKET,0)+data_start_date
533 into p_day_bkt_start_date,
534 p_hour_bkt_start_date
535 from msc_plans
536 where plan_id = p_plan_id;
537
538 -- parse the resource_list
539 -- the format of res_list is
540 -- (instance_id, org_id, dept_id, res_id),(ins_id, org_id, dept_id, res_id)
541
542 v_len := length(p_res_list);
543 while v_len > 0 LOOP
544
545 one_record :=
546 substr(p_res_list,instr(p_res_list,'(',1,i)+1,
547 instr(p_res_list,')',1,i)-instr(p_res_list,'(',1,i)-1);
548
549 v_instance_id := to_number(substr(one_record,1,instr(one_record,',')-1));
550
551 v_org_id := to_number(substr(one_record,instr(one_record,',',1,1)+1,
552 instr(one_record,',',1,2)-instr(one_record,',',1,1)-1));
553
554 v_dept_id := to_number(substr(one_record,instr(one_record,',',1,2)+1
555 ,instr(one_record,',',1,3)-instr(one_record,',',1,2)-1));
556
557 v_res_id := to_number(substr(one_record,instr(one_record,',',1,3)+1));
558
559 oneAvailRecord := null;
560 availCount :=0;
561 j := 1;
562 oneAssignRecord.delete;
563 oneAssignRecord.extend;
564 reqCount :=0;
565
566 if v_res_id =-1 then
567 OPEN line_rate;
568 FETCH line_rate INTO v_line_rate;
569 CLOSE line_rate;
570 else
571 v_line_rate :=1;
572 end if;
573
574 v_finite_avail := null;
575
576 OPEN finite_avail;
577 FETCH finite_avail INTO v_finite_avail;
578 CLOSE finite_avail;
579
580 v_line_rate := nvl(v_line_rate, 1);
581
582 select start_date, end_date, assigned_units, over_cap, batch_number
583 BULK COLLECT INTO v_req_start, v_req_end, v_req_qty, v_over_cap, v_batch
584 FROM (
585 select -- req has been moved will use parent_id =2
586 decode(nvl(firm_flag,0),
587 NO_FIRM, start_date,
588 FIRM_RESOURCE, start_date,
589 FIRM_END,
590 firm_end_date - (end_date - start_date),
591 FIRM_END_RES,
592 firm_end_date - (end_date - start_date),
593 nvl(firm_start_date, start_date)) start_date,
594 least(g_cutoff_date,
595 decode(nvl(firm_flag,0),
596 NO_FIRM, end_date,
597 FIRM_RESOURCE, end_date,
598 FIRM_START,
599 firm_start_date + (end_date - start_date),
600 FIRM_START_RES,
601 firm_start_date + (end_date - start_date),
602 nvl(firm_end_date, end_date))) end_date,
603 assigned_units,
604 nvl(overloaded_capacity,0) over_cap,
605 batch_number
606 from msc_resource_requirements
607 where organization_id =v_org_id
608 and sr_instance_id = v_instance_id
609 and department_id = v_dept_id
610 and resource_id = v_res_id
611 and plan_id = p_plan_id
612 and end_date is not null
613 and batch_number is null
614 and nvl(parent_id,2) =2
615 and status = 0
616 and applied = 2
617 and nvl(firm_start_date,start_date) <= g_cutoff_date
618 and ( decode(nvl(firm_flag,0),
619 NO_FIRM, start_date,
620 FIRM_RESOURCE, start_date,
621 FIRM_END,
622 firm_end_date - (end_date - start_date),
623 FIRM_END_RES,
624 firm_end_date - (end_date - start_date),
625 nvl(firm_start_date, start_date)) <=
626 to_date(p_end,format_mask)
627 and decode(nvl(firm_flag,0),
628 NO_FIRM, end_date,
629 FIRM_RESOURCE, end_date,
630 FIRM_START,
631 firm_start_date + (end_date - start_date),
632 FIRM_START_RES,
633 firm_start_date + (end_date - start_date),
634 nvl(firm_end_date, end_date)) >=
635 to_date(p_start,format_mask) )
636 UNION ALL
637 select -- req has not been moved will use parent_id =1
638 mrr2.start_date,
639 least(g_cutoff_date,
640 decode(sign(mrr2.end_date-mrr2.start_date), 1,
641 mrr2.end_date, trunc(mrr2.start_date)+1
642 )
643 ) end_date,
644 mrr2.resource_hours assigned_units,
645 -1 over_cap,
646 mrr2.batch_number
647 from msc_resource_requirements mrr,
648 msc_resource_requirements mrr2
649 where mrr.organization_id =v_org_id
650 and mrr.sr_instance_id = v_instance_id
651 and mrr.department_id = v_dept_id
652 and mrr.resource_id = v_res_id
653 and mrr.plan_id = p_plan_id
654 and mrr.batch_number is null
655 and mrr.end_date is not null
656 and nvl(mrr.parent_id,2) =2
657 and (nvl(mrr.status,1) <> 0 or nvl(mrr.applied,1) <> 2)
658 and nvl(mrr.firm_start_date,mrr.start_date) <= g_cutoff_date
659 and ( decode(nvl(mrr.firm_flag,0),
660 NO_FIRM, mrr.start_date,
661 FIRM_RESOURCE, mrr.start_date,
662 FIRM_END,
663 mrr.firm_end_date - (mrr.end_date - mrr.start_date),
664 FIRM_END_RES,
665 mrr.firm_end_date - (mrr.end_date - mrr.start_date),
666 nvl(mrr.firm_start_date, mrr.start_date)) <=
667 to_date(p_end,format_mask)
668 and decode(nvl(mrr.firm_flag,0),
669 NO_FIRM, mrr.end_date,
670 FIRM_RESOURCE, mrr.end_date,
671 FIRM_START,
672 mrr.firm_start_date + (mrr.end_date - mrr.start_date),
673 FIRM_START_RES,
674 mrr.firm_start_date + (mrr.end_date - mrr.start_date),
675 nvl(mrr.firm_end_date, mrr.end_date)) >=
676 to_date(p_start,format_mask) )
677 and mrr2.parent_id =1
678 and mrr2.organization_id =mrr.organization_id
679 and mrr2.sr_instance_id = mrr.sr_instance_id
680 and mrr2.department_id = mrr.department_id
681 and mrr2.resource_id = mrr.resource_id
682 and mrr2.plan_id = mrr.plan_id
683 and mrr2.supply_id = mrr.supply_id
684 and mrr2.resource_hours > 0
685 and mrr2.operation_seq_num = mrr.operation_seq_num
686 and mrr2.resource_seq_num = mrr.resource_seq_num
687 and mrr2.end_date is not null
688 UNION ALL
689 select -- batch resource from parent_id = 1
690 min(mrr2.start_date) start_date,
691 max(least(g_cutoff_date,
692 decode(sign(mrr2.end_date-mrr2.start_date), 1,
693 mrr2.end_date, trunc(mrr2.start_date)+1
694 )
695 )) end_date,
696 max(mrr2.resource_hours) assigned_units,
697 -1 over_cap,
698 mrr2.batch_number
699 from msc_resource_requirements mrr,
700 msc_resource_requirements mrr2
701 where mrr.organization_id =v_org_id
702 and mrr.sr_instance_id = v_instance_id
703 and mrr.department_id = v_dept_id
704 and mrr.resource_id = v_res_id
705 and mrr.plan_id = p_plan_id
706 and mrr.batch_number is not null
707 and mrr.end_date is not null
708 and nvl(mrr.parent_id,2) =2
709 and (nvl(mrr.status,1) <> 0 or nvl(mrr.applied,1) <> 2)
710 and nvl(mrr.firm_start_date,mrr.start_date) <= g_cutoff_date
711 and ( decode(nvl(mrr.firm_flag,0),
712 NO_FIRM, mrr.start_date,
713 FIRM_RESOURCE, mrr.start_date,
714 FIRM_END,
715 mrr.firm_end_date - (mrr.end_date - mrr.start_date),
716 FIRM_END_RES,
717 mrr.firm_end_date - (mrr.end_date - mrr.start_date),
718 nvl(mrr.firm_start_date, mrr.start_date)) <=
719 to_date(p_end,format_mask)
720 and decode(nvl(mrr.firm_flag,0),
721 NO_FIRM, mrr.end_date,
722 FIRM_RESOURCE, mrr.end_date,
723 FIRM_START,
724 mrr.firm_start_date + (mrr.end_date - mrr.start_date),
725 FIRM_START_RES,
726 mrr.firm_start_date + (mrr.end_date - mrr.start_date),
727 nvl(mrr.firm_end_date, mrr.end_date)) >=
728 to_date(p_start,format_mask) )
729 and mrr2.parent_id =1
730 and mrr2.organization_id =mrr.organization_id
731 and mrr2.sr_instance_id = mrr.sr_instance_id
732 and mrr2.department_id = mrr.department_id
733 and mrr2.resource_id = mrr.resource_id
734 and mrr2.plan_id = mrr.plan_id
735 and mrr2.supply_id = mrr.supply_id
736 and mrr2.resource_hours > 0
737 and mrr2.operation_seq_num = mrr.operation_seq_num
738 and mrr2.resource_seq_num = mrr.resource_seq_num
739 and mrr2.end_date is not null
740 group by mrr2.batch_number
741 UNION ALL
742 select -- batch resource from parent_id = 2
743 min(
744 decode(nvl(firm_flag,0),
745 NO_FIRM, start_date,
746 FIRM_RESOURCE, start_date,
747 FIRM_END,
748 firm_end_date - (end_date - start_date),
749 FIRM_END_RES,
750 firm_end_date - (end_date - start_date),
751 nvl(firm_start_date, start_date))) start_date,
752 max(least(g_cutoff_date,
753 decode(nvl(firm_flag,0),
754 NO_FIRM, end_date,
755 FIRM_RESOURCE, end_date,
756 FIRM_START,
757 firm_start_date + (end_date - start_date),
758 FIRM_START_RES,
759 firm_start_date + (end_date - start_date),
760 nvl(firm_end_date, end_date)))) end_date,
761 max(assigned_units) assigned_units,
762 max(nvl(overloaded_capacity,0)) over_cap
763 , batch_number
764 from msc_resource_requirements
765 where organization_id =v_org_id
766 and sr_instance_id = v_instance_id
767 and department_id = v_dept_id
768 and resource_id = v_res_id
769 and plan_id = p_plan_id
770 and batch_number is not null
771 and end_date is not null
772 and nvl(parent_id,2) =2
773 and status =0
774 and applied =2
775 and nvl(firm_start_date,start_date) <= g_cutoff_date
776 and ( decode(nvl(firm_flag,0),
777 NO_FIRM, start_date,
778 FIRM_RESOURCE, start_date,
779 FIRM_END,
780 firm_end_date - (end_date - start_date),
781 FIRM_END_RES,
782 firm_end_date - (end_date - start_date),
783 nvl(firm_start_date, start_date)) <=
784 to_date(p_end,format_mask)
785 and decode(nvl(firm_flag,0),
786 NO_FIRM, end_date,
787 FIRM_RESOURCE, end_date,
788 FIRM_START,
789 firm_start_date + (end_date - start_date),
790 FIRM_START_RES,
791 firm_start_date + (end_date - start_date),
792 nvl(firm_end_date, end_date)) >=
793 to_date(p_start,format_mask))
794 group by batch_number)
795 order by start_date
796 ;
797
798 select
799 shift_date+from_time/86400,
800 decode(sign(to_time-from_time), 1,
801 shift_date+to_time/86400,
802 shift_date+1+to_time/86400),
803 capacity_units
804 bulk collect into v_avail_start, v_avail_end, v_avail_qty
805 from msc_net_resource_avail mrr
806 where organization_id =v_org_id
807 and sr_instance_id = v_instance_id
808 and department_id = v_dept_id
809 and resource_id = v_res_id
810 and plan_id = p_plan_id
811 and nvl(parent_id,0) <> -1
812 and capacity_units > 0
813 and (shift_date+from_time/86400) <=
814 to_date(p_end,format_mask)
815 and decode(sign(to_time-from_time), 1,
816 shift_date+to_time/86400,
817 shift_date+1+to_time/86400) >=
818 to_date(p_start,format_mask)
819 and shift_date <= g_cutoff_date
820 order by shift_date, from_time, to_time;
821
822 select mpb.bkt_start_date,mpb.bkt_end_date
823 BULK COLLECT INTO v_bkt_start, v_bkt_end
824 from msc_plan_buckets mpb,
825 msc_plans mp
826 where mp.plan_id =p_plan_id
827 and mpb.plan_id = mp.plan_id
828 and mpb.organization_id = mp.organization_id
829 and mpb.sr_instance_id = mp.sr_instance_id
830 and ( mpb.bkt_start_date between to_date(p_start,format_mask) and
831 to_date(p_end,format_mask)
832 or
833 mpb.bkt_end_date between to_date(p_start,format_mask) and
834 to_date(p_end,format_mask) )
835 and mpb.bkt_start_date >= p_day_bkt_start_date
836 order by 1;
837
838 -- for hourly bucket, round down start_time/round up end_time to a whole hour
839
840 for b in 1 .. v_avail_start.count loop
841 time_change := false;
842 if v_avail_start(b) >= p_hour_bkt_start_date and
846 if to_char(v_avail_start(b),'MI') <> '00' then
843 v_avail_start(b) < p_day_bkt_start_date then
844 v_total_avail := (v_avail_end(b)-v_avail_start(b))*24*
845 v_avail_qty(b);
847 v_avail_start(b) := to_date(to_char(
848 v_avail_start(b),'MM/DD/RR, HH24'),
849 'MM/DD/RR HH24');
850 time_change := true;
851 end if;
852 if to_char(v_avail_end(b),'MI') <> '00' then
853 if to_char(v_avail_end(b),'HH24') = '23' then
854 v_avail_end(b) := to_date(to_char(
855 v_avail_start(b),'MM/DD/RR')||
856 ' 23:59',
857 'MM/DD/RR HH24:MI');
858 else
859 new_hour := to_number(to_char(v_avail_end(b),'HH24'))+1;
860 v_avail_end(b) := to_date(to_char(
861 v_avail_start(b),'MM/DD/RR')||' '||
862 new_hour,
863 'MM/DD/RR HH24');
864 end if;
865 time_change := true;
866 end if;
867 if time_change then
868 v_avail_qty(b) := round(v_total_avail/
869 ((v_avail_end(b)-v_avail_start(b))*24),6);
870 end if;
871
872 end if;
873
874 end loop;
875
876 -- for minute and hourly bucket, don't group it
877 for b in 1 .. v_avail_start.count loop
878 if v_avail_start(b) < p_day_bkt_start_date then
879 new_end := least(v_avail_end(b),p_day_bkt_start_date);
880 if oneAvailRecord is not null then
881 oneAvailRecord :=
882 oneAvailRecord || field_seperator ||
883 to_char(v_avail_start(b),format_mask) ||
884 field_seperator ||
885 to_char(new_end,format_mask) ||
886 field_seperator ||
887 fnd_number.number_to_canonical(v_avail_qty(b))||
888 field_seperator ||
889 0;
890 else
891 oneAvailRecord :=
892 to_char(v_avail_start(b),format_mask) ||
893 field_seperator ||
894 to_char(new_end,format_mask) ||
895 field_seperator ||
896 fnd_number.number_to_canonical(v_avail_qty(b))||
897 field_seperator ||
898 0;
899 end if;
900 availCount := availCount+1;
901 end if;
902 end loop;
903
904 for b in 1 .. v_req_start.count loop
905 if v_req_start(b) < p_day_bkt_start_date then
906 v_qty := v_req_qty(b)* v_line_rate;
907 new_end := least(v_req_end(b),p_day_bkt_start_date);
908 if v_over_cap(b) = v_qty or -- req is overloaded during the break
909 v_over_cap(b) = -1 then -- req from parent_id =1
910
911 if v_over_cap(b) = -1 then -- from parent_id = 1
912 v_qty := round(v_req_qty(b)/((v_req_end(b)-v_req_start(b))*24),6);
913 v_over_cap(b) := v_qty;
914 end if;
915 v_one_record :=
916 to_char(v_req_start(b),format_mask) ||
917 field_seperator ||
918 to_char(new_end,format_mask) ||
919 field_seperator ||
920 fnd_number.number_to_canonical(v_qty)||
921 field_seperator ||
922 fnd_number.number_to_canonical(v_over_cap(b));
923 v_max_len := nvl(length(oneAssignRecord(j)),0) +
924 nvl(length(v_one_record),0);
925 if v_max_len > 30000 then
926 j := j+1;
927 oneAssignRecord.extend;
928 end if;
929
930 oneAssignRecord(j) := oneAssignRecord(j) || field_seperator ||
931 v_one_record;
932 reqCount := reqCount+1;
933 else -- only pass the req which has avail
934 for a in 1 .. v_avail_start.count loop
935 if (v_avail_start(a) >= v_req_start(b) and
936 v_avail_start(a) <= v_req_end(b)) or
937 (v_avail_end(a) >= v_req_start(b) and
938 v_avail_end(a) <= v_req_end(b)) or
939 (v_req_start(b) >= v_avail_start(a) and
940 v_req_end(b) <= v_avail_end(a))then
941 new_start := greatest(v_req_start(b), v_avail_start(a));
942 new_end := least(v_req_end(b), v_avail_end(a));
943 v_one_record :=
944 to_char(new_start,format_mask) ||
945 field_seperator ||
946 to_char(new_end,format_mask) ||
947 field_seperator ||
948 fnd_number.number_to_canonical(v_qty)||
949 field_seperator ||
950 fnd_number.number_to_canonical(v_qty);
951 v_max_len := nvl(length(oneAssignRecord(j)),0) +
952 nvl(length(v_one_record),0);
953 if v_max_len > 30000 then
954 j := j+1;
955 oneAssignRecord.extend;
956 end if;
957
958 oneAssignRecord(j) := oneAssignRecord(j) || field_seperator ||
959 v_one_record;
960 reqCount := reqCount+1;
961 end if;
962 end loop;
963
964 end if;
965 end if;
966 end loop;
967
968
969 -- for daily, weekly, period buckets
970 for a in 1 .. v_bkt_start.count loop
971
972 bkt_qty := 0;
973 v_qty := 0;
974 eff_rate := 0;
975 max_cap := 0;
976 -- found all res avail for one bucket
977 for b in 1 .. v_avail_start.count loop
978 if (v_avail_start(b) > v_bkt_end(a) or
979 v_avail_end(b) < v_bkt_start(a) ) then
980 v_qty := 0;
981 else
982 new_start := greatest(v_avail_start(b), v_bkt_start(a));
983 new_end := least(v_avail_end(b), v_bkt_end(a));
984 v_qty := v_avail_qty(b);
985 end if;
986 if v_qty <> 0 then
987 bkt_qty := bkt_qty +
988 (new_end - new_start) * v_qty/(v_bkt_end(a)- v_bkt_start(a));
989 max_cap := greatest(max_cap, v_qty);
990 end if;
991 end loop;
992
993 if (bkt_qty <> 0) then
994 eff_rate := bkt_qty / max_cap;
995 bkt_qty := round(bkt_qty,6);
996 if oneAvailRecord is not null then
997 oneAvailRecord :=
998 oneAvailRecord || field_seperator ||
999 to_char(v_bkt_start(a),format_mask) || field_seperator ||
1000 to_char(v_bkt_end(a),format_mask) || field_seperator ||
1001 fnd_number.number_to_canonical(bkt_qty)||
1002 field_seperator ||
1003 0;
1004 else
1005 oneAvailRecord :=
1006 to_char(v_bkt_start(a),format_mask) || field_seperator ||
1007 to_char(v_bkt_end(a),format_mask) || field_seperator ||
1008 fnd_number.number_to_canonical(bkt_qty)||
1009 field_seperator ||
1010 0;
1011 end if;
1012 availCount := availCount+1;
1013 end if;
1014 bkt_qty := 0;
1015 v_qty := 0;
1016
1017 -- found all req in one bucket
1018 for b in 1 .. v_req_start.count loop
1019 if (v_req_start(b) > v_bkt_end(a) or
1020 v_req_end(b) < v_bkt_start(a) ) then
1021 v_qty := 0;
1022 elsif v_over_cap(b) <> -1 and -- not from parent_id = 1
1023 eff_rate = 0 and v_over_cap(b) <> v_req_qty(b) then
1024 v_qty := 0;
1025 else
1026 new_start := greatest(v_req_start(b), v_bkt_start(a));
1027 new_end := least(v_req_end(b), v_bkt_end(a));
1028 if v_over_cap(b) = -1 then -- from parent_id = 1
1029 v_qty := v_req_qty(b)/((v_req_end(b)-v_req_start(b))*24);
1030 else
1031 v_qty := v_req_qty(b)* v_line_rate;
1032 end if;
1033 end if;
1034 if v_qty <> 0 then
1035 if (v_over_cap(b) = v_req_qty(b) or
1036 v_over_cap(b) = -1 ) then
1037 bkt_qty := bkt_qty +
1038 (new_end - new_start)
1039 * v_qty/(v_bkt_end(a)- v_bkt_start(a));
1040 else
1041 bkt_qty := bkt_qty +
1042 (new_end - new_start)
1043 * v_qty * eff_rate/(v_bkt_end(a)- v_bkt_start(a));
1044 end if;
1045 end if;
1046 end loop;
1047
1048 if bkt_qty <> 0 then
1049 bkt_qty := round(bkt_qty, 6);
1050 v_one_record :=
1051 to_char(v_bkt_start(a),format_mask) || field_seperator ||
1052 to_char(v_bkt_end(a),format_mask) || field_seperator ||
1053 fnd_number.number_to_canonical(bkt_qty)||
1054 field_seperator ||
1055 fnd_number.number_to_canonical(bkt_qty);
1056 v_max_len := nvl(length(oneAssignRecord(j)),0) +
1057 nvl(length(v_one_record),0);
1058 if v_max_len > 30000 then
1059 j := j+1;
1060 oneAssignRecord.extend;
1061 end if;
1062 oneAssignRecord(j) := oneAssignRecord(j) || field_seperator ||
1063 v_one_record;
1064 reqCount := reqCount+1;
1065 end if;
1066 end loop;
1067
1068 v_require_data.extend;
1069 k := k+1;
1070 if i = 1 then -- not the first record
1071 v_require_data(k) := to_char(i-1) || field_seperator ||
1072 reqCount;
1073 else
1074 v_require_data(k) := record_seperator ||
1075 to_char(i-1) || field_seperator ||
1076 reqCount;
1077 end if;
1078
1079 for j in 1 .. oneAssignRecord.count loop
1080 if j = 1 then
1081 v_require_data(k) := v_require_data(k) || oneAssignRecord(j);
1082 else
1083 v_require_data.extend;
1084 k := k+1;
1085 v_require_data(k) := oneAssignRecord(j);
1086 end if;
1087 end loop;
1088
1089 if v_finite_avail is null and oneAvailRecord is null then
1090 oneAvailRecord := 1;
1091 end if;
1092
1093 if v_avail_data is not null then
1094 v_avail_data := v_avail_data || record_seperator ||
1095 to_char(i-1) || field_seperator ||
1096 availCount || field_seperator ||
1097 oneAvailRecord;
1098 else
1099 v_avail_data :=
1100 to_char(i-1) || field_seperator ||
1101 availCount || field_seperator ||
1102 oneAvailRecord;
1103 end if;
1104
1105
1106
1107 i := i+1;
1108 v_len := v_len - length(one_record)-3;
1109 END LOOP;
1110
1111 END;
1112
1113
1114 Function loadAltResource(p_plan_id number,
1115 p_transaction_id number,
1116 p_instance_id number,
1117 p_alt_resource number,
1118 p_alt_num number) return Varchar2 IS
1119 l_firm_flag number;
1120 l_basis_type number;
1121 l_rout_seq number;
1122 l_op_seq number;
1123 l_res_seq number;
1124 l_supply_id number;
1125 l_act_group number;
1126 l_avail_res_seq number;
1127
1128 CURSOR res_seq IS
1129 Select distinct mors.resource_seq_num
1130 from msc_operation_resource_seqs mors
1131 where mors.plan_id = p_plan_id
1132 and mors.routing_sequence_id = l_rout_seq
1133 and mors.operation_sequence_id = l_op_seq
1134 and mors.sr_instance_id = p_instance_id
1135 and mors.activity_group_id = l_act_group
1136 ;
1137
1138 CURSOR res_group IS
1139 SELECT distinct mrr.transaction_id,mor.principal_flag
1140 FROM msc_resource_requirements mrr,
1141 msc_operation_resources mor
1142 WHERE mrr.plan_id = p_plan_id
1143 AND mrr.sr_instance_id = p_instance_id
1144 and mrr.routing_sequence_id = l_rout_seq
1145 AND mrr.operation_sequence_id = l_op_seq
1146 AND mrr.resource_seq_num = l_res_seq
1147 and mor.plan_id = p_plan_id
1148 and mor.sr_instance_id = p_instance_id
1149 and mor.routing_sequence_id = mrr.routing_sequence_id
1150 and mor.operation_sequence_id = mrr.operation_sequence_id
1151 and mor.resource_seq_num = mrr.resource_seq_num
1152 and mor.resource_id = mrr.resource_id
1153 AND mor.alternate_number <> p_alt_num
1154 AND mrr.parent_id =2
1155 and mrr.supply_id = l_supply_id
1156 order by mor.principal_flag;
1157
1158 CURSOR alt_res_group IS
1159 SELECT mor.resource_usage,
1160 mor.resource_units,
1161 mor.resource_id,
1162 mor.alternate_number,
1163 mor.principal_flag
1164 FROM msc_operation_resources mor
1165 WHERE mor.plan_id = p_plan_id
1166 AND mor.routing_sequence_id = l_rout_seq
1167 AND mor.sr_instance_id = p_instance_id
1168 AND mor.operation_sequence_id = l_op_seq
1169 AND mor.resource_seq_num = l_res_seq
1170 AND mor.alternate_number = p_alt_num
1171 order by mor.principal_flag;
1172
1173 TYPE ResRecTyp IS RECORD (
1174 resource_usage number,
1175 resource_units number,
1176 resource_id number,
1177 alternate_number number,
1178 principal_flag number);
1179 TYPE SimRecTyp IS RECORD (
1180 transaction_id number,
1181 principal_flag number);
1182
1183 TYPE numTabTyp IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1184 TYPE FromTabTyp IS TABLE OF SimRecTyp INDEX BY BINARY_INTEGER;
1185 TYPE ToTabTyp IS TABLE OF ResRecTyp INDEX BY BINARY_INTEGER;
1186
1187 l_simu_res FromTabTyp;
1188 l_alt_res ToTabTyp;
1189 l_all_seq numTabTyp;
1190 i BINARY_INTEGER := 0;
1191 j BINARY_INTEGER := 0;
1192 v_usage number;
1193 v_qty number;
1194 v_hours number;
1195 BEGIN
1196 --get corresponding info about this tranx
1197 select
1198 mrr.routing_sequence_id, mrr.operation_sequence_id,
1199 mrr.resource_seq_num,mrr.supply_id,mrr.basis_type
1200 into
1201 l_rout_seq, l_op_seq, l_res_seq, l_supply_id,l_basis_type
1202 FROM msc_resource_requirements mrr
1203 WHERE mrr.plan_id = p_plan_id
1204 and mrr.transaction_id = p_transaction_id
1205 and mrr.sr_instance_id = p_instance_id;
1206
1207 IF l_basis_type = 1 THEN
1208 select new_order_quantity
1209 into v_qty
1210 from msc_supplies
1211 where plan_id = p_plan_id
1212 and transaction_id = l_supply_id;
1213 ELSE
1214 v_qty := 1;
1215 END IF;
1216
1217 -- find the activity_group_id
1218 select activity_group_id
1219 into l_act_group
1220 from msc_operation_resource_seqs
1221 where plan_id = p_plan_id
1222 and routing_sequence_id = l_rout_seq
1223 and operation_sequence_id = l_op_seq
1224 and resource_seq_num = l_res_seq
1225 and sr_instance_id = p_instance_id;
1226
1227 if l_act_group is null then
1228 l_all_seq(1) := l_res_seq;
1229 else
1230 i :=1;
1231 OPEN res_seq;
1232 LOOP
1233 FETCH res_seq INTO l_all_seq(i);
1234 EXIT WHEN res_seq%NOTFOUND;
1235 i:= i+1;
1236 END LOOP;
1237 CLOSE res_seq;
1238
1239 select count(*)
1240 into l_avail_res_seq
1241 from (
1242 select distinct mors.resource_seq_num
1243 from msc_operation_resource_seqs mors,
1244 msc_operation_resources mor
1245 where mors.plan_id = p_plan_id
1246 and mors.sr_instance_id = p_instance_id
1247 and mors.operation_sequence_id = l_op_seq
1248 and mors.routing_sequence_id = l_rout_seq
1249 and mors.activity_group_id = l_act_group
1250 and mor.plan_id = p_plan_id
1251 and mor.routing_sequence_id = mors.routing_sequence_id
1252 and mor.operation_sequence_id = mors.operation_sequence_id
1253 and mor.sr_instance_id = p_instance_id
1254 and mor.resource_seq_num = mors.resource_seq_num
1255 and mor.alternate_number = p_alt_num
1256 );
1257 if l_avail_res_seq is null or l_avail_res_seq < l_all_seq.LAST then
1258 return 'NO_ALT';
1259 end if;
1260
1261 end if;
1262
1263 for j in 1..l_all_seq.LAST LOOP
1264 -- fetch the resources in the same resource group
1265 l_res_seq := l_all_seq(j);
1266 i :=1;
1267 OPEN res_group;
1268 LOOP
1269 FETCH res_group INTO l_simu_res(i);
1270 EXIT WHEN res_group%NOTFOUND;
1271 i:= i+1;
1272 END LOOP;
1273 CLOSE res_group;
1274
1275 -- fetch the resources in the alternate resource group
1276 i:=1;
1277 OPEN alt_res_group;
1278 LOOP
1279 FETCH alt_res_group INTO l_alt_res(i);
1280 EXIT WHEN alt_res_group%NOTFOUND;
1281 i:= i+1;
1282 END LOOP;
1283 CLOSE alt_res_group;
1284
1285 i:=1;
1286 while (l_simu_res.LAST >= i or l_alt_res.LAST >= i)
1287 loop
1288 if i > l_simu_res.LAST THEN -- add the res from alt res group
1289
1290 v_hours := v_qty * l_alt_res(i).resource_usage;
1291
1292 insert into msc_resource_requirements(
1293 TRANSACTION_ID ,
1294 PLAN_ID ,
1295 SUPPLY_ID ,
1296 ORGANIZATION_ID ,
1297 SR_INSTANCE_ID ,
1298 ROUTING_SEQUENCE_ID ,
1299 OPERATION_SEQUENCE_ID ,
1300 RESOURCE_SEQ_NUM ,
1301 RESOURCE_ID ,
1302 DEPARTMENT_ID ,
1303 ALTERNATE_NUM ,
1304 START_DATE ,
1305 END_DATE ,
1306 BKT_START_DATE ,
1307 RESOURCE_HOURS ,
1308 SET_UP ,
1309 BKT_END_DATE ,
1310 TEAR_DOWN ,
1314 STD_OP_CODE ,
1311 AGGREGATE_RESOURCE_ID ,
1312 SCHEDULE_FLAG ,
1313 PARENT_ID ,
1315 WIP_ENTITY_ID ,
1316 ASSIGNED_UNITS ,
1317 BASIS_TYPE ,
1318 OPERATION_SEQ_NUM ,
1319 LOAD_RATE ,
1320 DAILY_RESOURCE_HOURS ,
1321 STATUS ,
1322 APPLIED ,
1323 UPDATED ,
1324 SUBST_RES_FLAG ,
1325 REFRESH_NUMBER ,
1326 LAST_UPDATED_BY ,
1327 LAST_UPDATE_DATE ,
1328 CREATED_BY ,
1329 CREATION_DATE ,
1330 LAST_UPDATE_LOGIN ,
1331 SOURCE_ITEM_ID ,
1332 ASSEMBLY_ITEM_ID ,
1333 SUPPLY_TYPE ,
1334 FIRM_START_DATE ,
1335 FIRM_END_DATE ,
1336 FIRM_FLAG )
1337 select msc_resource_requirements_s.nextval,
1338 PLAN_ID ,
1339 SUPPLY_ID ,
1340 ORGANIZATION_ID ,
1341 SR_INSTANCE_ID ,
1342 ROUTING_SEQUENCE_ID ,
1343 OPERATION_SEQUENCE_ID ,
1344 RESOURCE_SEQ_NUM ,
1345 l_alt_res(i).resource_id ,
1346 DEPARTMENT_ID ,
1347 l_alt_res(i).alternate_number ,
1348 START_DATE ,
1349 END_DATE ,
1350 BKT_START_DATE ,
1351 v_hours ,
1352 SET_UP ,
1353 BKT_END_DATE ,
1354 TEAR_DOWN ,
1355 AGGREGATE_RESOURCE_ID ,
1356 SCHEDULE_FLAG ,
1357 PARENT_ID ,
1358 STD_OP_CODE ,
1359 WIP_ENTITY_ID ,
1360 ASSIGNED_UNITS ,
1361 BASIS_TYPE ,
1362 OPERATION_SEQ_NUM ,
1363 LOAD_RATE ,
1364 DAILY_RESOURCE_HOURS ,
1365 0 ,
1366 2 ,
1367 UPDATED ,
1368 SUBST_RES_FLAG ,
1369 REFRESH_NUMBER ,
1370 LAST_UPDATED_BY ,
1371 LAST_UPDATE_DATE ,
1372 CREATED_BY ,
1373 CREATION_DATE ,
1374 LAST_UPDATE_LOGIN ,
1375 SOURCE_ITEM_ID ,
1376 ASSEMBLY_ITEM_ID ,
1377 SUPPLY_TYPE ,
1378 FIRM_START_DATE ,
1379 FIRM_END_DATE ,
1380 FIRM_RESOURCE
1381 from msc_resource_requirements mrr
1382 WHERE mrr.plan_id = p_plan_id
1383 and mrr.transaction_id = l_simu_res(1).transaction_id
1384 and mrr.sr_instance_id = p_instance_id;
1385
1386 ELSIF i > l_alt_res.LAST THEN -- delete the extra res
1387 select mrr.firm_flag
1388 into l_firm_flag
1389 FROM msc_resource_requirements mrr
1390 WHERE mrr.plan_id = p_plan_id
1391 and mrr.transaction_id = l_simu_res(i).transaction_id
1392 and mrr.sr_instance_id = p_instance_id
1393 for update of mrr.firm_flag nowait;
1394 delete msc_resource_requirements mrr
1395 where mrr.plan_id = p_plan_id
1396 and mrr.transaction_id = l_simu_res(i).transaction_id
1397 and mrr.sr_instance_id = p_instance_id;
1398
1399 ELSE -- update the res to alt_res
1400
1401 select mrr.firm_flag
1402 into l_firm_flag
1403 FROM msc_resource_requirements mrr
1404 WHERE mrr.plan_id = p_plan_id
1405 and mrr.transaction_id = l_simu_res(i).transaction_id
1406 and mrr.sr_instance_id = p_instance_id
1407 for update of mrr.firm_flag nowait;
1408
1409 if l_firm_flag in (NO_FIRM, FIRM_RESOURCE) or l_firm_flag IS null THEN
1410 l_firm_flag := FIRM_RESOURCE;
1411 elsif l_firm_flag in (FIRM_START, FIRM_START_RES) THEN
1412 l_firm_flag := FIRM_START_RES;
1416 l_firm_flag := FIRM_ALL;
1413 elsif l_firm_flag in (FIRM_END, FIRM_END_RES) THEN
1414 l_firm_flag := FIRM_END_RES;
1415 elsif l_firm_flag in (FIRM_ALL, FIRM_START_END) THEN
1417 else
1418 l_firm_flag := FIRM_RESOURCE;
1419 end if;
1420
1421 --undo_change(p_plan_id, l_rest_res(i), p_instance_id,
1422 -- l_firm_flag, l_firm_start, l_firm_end,
1423 -- l_alt_res(i).resource_id,
1424 -- l_alt_res(i).alternate_number,
1425 -- v_hours);
1426 v_hours := v_qty * l_alt_res(i).resource_usage;
1427 update msc_resource_requirements
1428 set status =0,
1429 applied=2,
1430 resource_id = l_alt_res(i).resource_id,
1431 alternate_num = l_alt_res(i).alternate_number,
1432 firm_flag = l_firm_flag,
1433 resource_hours = v_hours
1434 where plan_id = p_plan_id
1435 and transaction_id = l_simu_res(i).transaction_id
1436 and sr_instance_id = p_instance_id;
1437
1438 END IF;
1439 i := i+1;
1440 end loop;
1441 end LOOP;
1442
1443 update msc_supplies
1444 set status = 0, applied = 2
1445 where plan_id = p_plan_id
1446 and transaction_id = l_supply_id;
1447
1448 --end LOOP;
1449 return 'OK';
1450 exception
1451 when app_exception.record_lock_exception then
1452 return 'RECORD_LOCK';
1453 END;
1454
1455 Function firmResource(p_plan_id number,
1456 p_transaction_id number,
1457 p_instance_id number,
1458 p_firm_type number,
1459 p_start varchar2,
1460 p_end varchar2) return varchar2 IS
1461 v_start date;
1462 v_end date;
1463 v_return_status varchar2(10):= 'OK';
1464 v_out varchar2(100);
1465 l_firm_flag number;
1466 l_firm_start date;
1467 l_firm_end date;
1468 l_res_id number;
1469 l_alt_num number;
1470 l_supply_id number;
1471 v_transaction_id number;
1472 v_instance_id number;
1473 v_count number;
1474 l_start date;
1475 l_end date;
1476
1477 CURSOR simu IS
1478 SELECT mrr2.transaction_id, mrr2.sr_instance_id
1479 FROM msc_resource_requirements mrr1,
1480 msc_resource_requirements mrr2
1481 WHERE mrr1.plan_id = p_plan_id
1482 and mrr1.transaction_id = p_transaction_id
1483 and mrr1.sr_instance_id = p_instance_id
1484 and mrr2.plan_id = mrr1.plan_id
1485 and mrr2.sr_instance_id = mrr1.sr_instance_id
1486 and mrr2.supply_id = mrr1.supply_id
1487 and mrr2.operation_seq_num = mrr1.operation_seq_num
1488 and mrr2.resource_seq_num = mrr1.resource_seq_num
1489 and mrr2.alternate_num = mrr1.alternate_num
1490 and mrr2.transaction_id <> mrr1.transaction_id
1491 and mrr2.parent_id = 2;
1492
1493 BEGIN
1494
1495 v_start := to_date(p_start, format_mask);
1496 v_end := to_date(p_end, format_mask);
1497 if (v_end <= v_start) then
1498 return 'END_BEFORE_START';
1499 end if;
1500 if p_firm_type not in (NO_FIRM,FIRM_RESOURCE) then
1501 -- validate if the time is OK
1502
1503 validateTime(p_plan_id, p_transaction_id,
1504 p_instance_id, p_start,
1505 p_end,
1506 v_return_status, v_out);
1507 end if;
1508
1509 if v_return_status = 'ERROR' then
1510 return v_out;
1511 else
1512 -- lock the record first
1513
1514 select mrr.firm_flag,mrr.firm_start_date, mrr.firm_end_date,
1515 mrr.resource_id, mrr.alternate_num, mrr.supply_id,
1516 mrr.start_date, mrr.end_date
1517 into l_firm_flag, l_firm_start, l_firm_end,
1518 l_res_id, l_alt_num, l_supply_id,
1519 l_start, l_end
1520 FROM msc_resource_requirements mrr
1521 WHERE mrr.plan_id = p_plan_id
1522 and mrr.transaction_id = p_transaction_id
1523 and mrr.sr_instance_id = p_instance_id
1524 for update of mrr.firm_flag nowait;
1525
1526 if p_firm_type in (NO_FIRM, FIRM_RESOURCE) THEN
1527 l_firm_start := to_date(null);
1528 l_firm_end := to_date(null);
1529 l_start := v_start;
1530 l_end := v_end;
1531 elsif p_firm_type in (FIRM_END, FIRM_END_RES) THEN
1532 l_firm_start := to_date(null);
1533 l_firm_end := v_end;
1534 l_start := v_start;
1535 elsif p_firm_type in (FIRM_START, FIRM_START_RES) THEN
1536 l_firm_start := v_start;
1537 l_firm_end := to_date(null);
1538 l_end := v_end;
1539 elsif p_firm_type in (FIRM_START_END,FIRM_ALL) THEN
1540 l_firm_start := v_start;
1541 l_firm_end := v_end;
1542 end if;
1543
1544 --undo_change(p_plan_id, p_transaction_id, p_instance_id,
1545 -- p_firm_type, l_firm_start, l_firm_end,
1546 -- l_res_id, l_alt_num);
1547
1548 update msc_resource_requirements
1549 set status =0,
1550 applied=2,
1551 firm_flag = p_firm_type,
1552 firm_start_date = l_firm_start,
1553 firm_end_date = l_firm_end,
1557 and transaction_id = p_transaction_id
1554 start_date = l_start,
1555 end_date = l_end
1556 where plan_id = p_plan_id
1558 and sr_instance_id = p_instance_id;
1559
1560 update msc_supplies
1561 set status =0,
1562 applied=2
1563 where plan_id = p_plan_id
1564 and transaction_id = l_supply_id;
1565
1566 -- update the simultaneous resource also
1567 v_count :=0;
1568 OPEN simu;
1569 LOOP FETCH simu INTO v_transaction_id, v_instance_id;
1570 EXIT WHEN simu%NOTFOUND;
1571
1572 select mrr.resource_id, mrr.alternate_num
1573 into l_res_id, l_alt_num
1574 FROM msc_resource_requirements mrr
1575 WHERE mrr.plan_id = p_plan_id
1576 and mrr.transaction_id = v_transaction_id
1577 and mrr.sr_instance_id = v_instance_id
1578 for update of mrr.firm_flag nowait;
1579
1580 --undo_change(p_plan_id, v_transaction_id, v_instance_id,
1581 -- p_firm_type, l_firm_start, l_firm_end,
1582 -- l_res_id, l_alt_num);
1583
1584 update msc_resource_requirements
1585 set status =0,
1586 applied=2,
1587 firm_flag = p_firm_type,
1588 firm_start_date = l_firm_start,
1589 firm_end_date = l_firm_end,
1590 start_date = l_start,
1591 end_date = l_end
1592 where plan_id = p_plan_id
1593 and transaction_id = v_transaction_id
1594 and sr_instance_id = v_instance_id;
1595 v_count := v_count+1;
1596 END LOOP;
1597
1598 if v_count > 0 then
1599 return 'OK_WITH_ST_RES';
1600 else
1601 return 'OK';
1602 end if;
1603 end if;
1604 exception when app_exception.record_lock_exception then
1605 return 'RECORD_LOCK';
1606 END;
1607
1608 PROCEDURE fetchAltResource(p_plan_id number,
1609 p_transaction_id number,
1610 p_instance_id number,
1611 v_name OUT NOCOPY varchar2,
1612 v_id OUT NOCOPY varchar2) IS
1613
1614 CURSOR alt_res IS
1615 SELECT mor.alternate_number ||':'||
1616 msc_get_name.resource_code(mor.resource_id, mrr.department_id,
1617 mrr.organization_id, mrr.plan_id,
1618 mrr.sr_instance_id),
1619 mor.resource_id,
1620 mor.alternate_number
1621 FROM msc_operation_resources mor,
1622 msc_resource_requirements mrr
1623 WHERE mrr.plan_id = p_plan_id
1624 AND mrr.transaction_id = p_transaction_id
1625 AND mrr.sr_instance_id = p_instance_id
1626 AND mor.plan_id = mrr.plan_id
1627 AND mor.routing_sequence_id = mrr.routing_sequence_id
1628 AND mor.sr_instance_id = mrr.sr_instance_id
1629 AND mor.operation_sequence_id = mrr.operation_sequence_id
1630 AND mor.resource_seq_num = mrr.resource_seq_num
1631 AND mor.alternate_number <> mrr.alternate_num;
1632
1633 CURSOR flag IS
1634 SELECT nvl(mrr.firm_flag,NO_FIRM)
1635 FROM msc_resource_requirements mrr
1636 WHERE mrr.plan_id = p_plan_id
1637 AND mrr.transaction_id = p_transaction_id
1638 AND mrr.sr_instance_id = p_instance_id;
1639
1640 CURSOR activity_c IS
1641 select mors.activity_group_id,
1642 mrr.routing_sequence_id, mrr.operation_sequence_id,
1643 mrr.resource_seq_num
1644 from msc_operation_resource_seqs mors,
1645 msc_resource_requirements mrr
1646 where mrr.plan_id = p_plan_id
1647 and mrr.transaction_id = p_transaction_id
1648 and mrr.sr_instance_id = p_instance_id
1649 and mors.plan_id = mrr.plan_id
1650 and mors.routing_sequence_id = mrr.routing_sequence_id
1651 and mors.operation_sequence_id = mrr.operation_sequence_id
1652 and mors.resource_seq_num = mrr.resource_seq_num
1653 and mors.sr_instance_id = mrr.sr_instance_id;
1654
1655 temp_name varchar2(30);
1656 temp_id number;
1657 temp_flag number;
1658 alt_number number;
1659 rowcount number;
1660 l_rout_seq number;
1661 l_op_seq number;
1662 l_res_seq number;
1663 l_total_seqs number;
1664 l_avail_seqs number;
1665 l_act_group number;
1666 l_flag varchar2(5);
1667 BEGIN
1668 --get corresponding info about this tranx
1669 OPEN activity_c;
1670 FETCH activity_c INTO l_act_group,l_rout_seq, l_op_seq, l_res_seq;
1671 CLOSE activity_c;
1672
1673 if l_act_group is not null then
1674 select count(*)
1675 into l_total_seqs
1676 from (Select distinct mors.resource_seq_num
1677 from msc_operation_resource_seqs mors
1678 where mors.plan_id = p_plan_id
1679 and mors.routing_sequence_id = l_rout_seq
1680 and mors.operation_sequence_id = l_op_seq
1681 and mors.sr_instance_id = p_instance_id
1682 and mors.activity_group_id = l_act_group
1683 ) ;
1684 end if;
1685
1686 rowcount :=0;
1687 OPEN alt_res;
1688 LOOP
1689 FETCH alt_res INTO temp_name, temp_id, alt_number;
1690 EXIT WHEN alt_res%NOTFOUND;
1691 rowcount := rowcount +1;
1692 if l_act_group is null then
1693 l_flag := 'Y';
1694 else
1695 select count(*)
1696 into l_avail_seqs
1697 from (
1698 select distinct mors.resource_seq_num
1699 from msc_operation_resource_seqs mors,
1703 and mors.operation_sequence_id = l_op_seq
1700 msc_operation_resources mor
1701 where mors.plan_id = p_plan_id
1702 and mors.sr_instance_id = p_instance_id
1704 and mors.routing_sequence_id = l_rout_seq
1705 and mors.activity_group_id = l_act_group
1706 and mor.plan_id = p_plan_id
1707 and mor.routing_sequence_id = mors.routing_sequence_id
1708 and mor.operation_sequence_id = mors.operation_sequence_id
1709 and mor.sr_instance_id = p_instance_id
1710 and mor.resource_seq_num = mors.resource_seq_num
1711 and mor.alternate_number = alt_number
1712 );
1713 if l_avail_seqs = l_total_seqs then
1714 l_flag := 'Y';
1715 else
1716 l_flag := 'N';
1717 end if;
1718 end if;
1719
1720 if v_name is not null then
1721 v_name := v_name || field_seperator || temp_name;
1722 v_id := v_id || field_seperator || temp_id
1723 || field_seperator || alt_number
1724 || field_seperator || l_flag;
1725 else
1726 v_name := temp_name;
1727 v_id := temp_id || field_seperator || alt_number
1728 || field_seperator || l_flag;
1729 end if;
1730
1731 END LOOP;
1732 CLOSE alt_res;
1733
1734 OPEN flag;
1735 FETCH flag INTO temp_flag;
1736 CLOSE flag;
1737 if temp_flag >= 8 then
1738 temp_flag := 0;
1739 end if;
1740
1741 v_name := temp_flag || field_seperator ||
1742 to_char(rowcount) || field_seperator || v_name;
1743 v_id := to_char(rowcount) || field_seperator || v_id;
1744 END;
1745
1746 PROCEDURE fetchSimultaneousRes(p_plan_id number,
1747 p_transaction_id number,
1748 p_instance_id number,
1749 v_name OUT NOCOPY varchar2,
1750 v_id OUT NOCOPY varchar2) IS
1751
1752 CURSOR smu_res IS
1753 SELECT msc_get_name.resource_code(mor.resource_id, mrr.department_id,
1754 mrr.organization_id, mrr.plan_id,
1755 mrr.sr_instance_id),
1756 mor.resource_id
1757 FROM msc_operation_resources mor,
1758 msc_resource_requirements mrr
1759 WHERE mrr.plan_id = p_plan_id
1760 AND mrr.transaction_id = p_transaction_id
1761 AND mrr.sr_instance_id = p_instance_id
1762 AND mor.plan_id = mrr.plan_id
1763 AND mor.routing_sequence_id = mrr.routing_sequence_id
1764 AND mor.sr_instance_id = mrr.sr_instance_id
1765 AND mor.operation_sequence_id = mrr.operation_sequence_id
1766 AND mor.resource_seq_num = mrr.resource_seq_num
1767 AND mor.alternate_number = mrr.alternate_num
1768 AND mor.resource_id <> mrr.resource_id;
1769
1770 temp_name varchar2(30);
1771 temp_id number;
1772 rowcount number;
1773 BEGIN
1774 rowcount :=0;
1775 OPEN smu_res;
1776 LOOP
1777 FETCH smu_res INTO temp_name, temp_id;
1778 EXIT WHEN smu_res%NOTFOUND;
1779 rowcount := rowcount +1;
1780 if v_name is not null then
1781 v_name := v_name || field_seperator || temp_name;
1782 v_id := v_id || field_seperator || temp_id;
1783 else
1784 v_name := temp_name;
1785 v_id := temp_id;
1786 end if;
1787
1788 END LOOP;
1789 CLOSE smu_res;
1790 v_name := to_char(rowcount) || field_seperator || v_name;
1791 v_id := to_char(rowcount) || field_seperator || v_id;
1792
1793 END;
1794
1795 PROCEDURE fetchPropertyData(p_plan_id number,
1796 p_transaction_id number,
1797 p_instance_id number,
1798 v_job OUT NOCOPY varchar2,
1799 v_demand OUT NOCOPY varchar2) IS
1800
1801 CURSOR property IS
1802 SELECT msc_get_name.item_name(ms.inventory_item_id,null,null,null) item,
1803 mrr.operation_seq_num,
1804 ms.new_order_quantity qty,
1805 nvl(to_char(ms.firm_date,format_mask),' ') firm_date,
1806 to_char(ms.new_schedule_date,format_mask) sugg_due_date,
1807 nvl(to_char(ms.need_by_date,format_mask),' ') needby,
1808 nvl(ms.unit_number,'null') unit_number,
1809 nvl(msc_get_name.project(ms.project_id,
1810 ms.organization_id,
1811 ms.plan_id,
1812 ms.sr_instance_id), 'null') project,
1813 nvl(msc_get_name.task( ms.task_id,
1814 ms.project_id,
1815 ms.organization_id,
1816 ms.plan_id,
1817 ms.sr_instance_id),'null') task,
1818 ms.transaction_id,
1819 ms.organization_id,
1820 msc_get_name.org_code(mdr.organization_id, mdr.sr_instance_id) org,
1821 mdr.department_code,
1822 msc_get_gantt_data.order_number(ms.order_type,ms.order_number,
1823 ms.plan_id, ms.sr_instance_id,
1824 ms.transaction_id, ms.disposition_id) job_name,
1825 mrr.assigned_units,
1826 nvl(msc_get_name.lookup_meaning('RESOURCE_FIRM_TYPE',
1827 nvl(mrr.firm_flag,NO_FIRM)),
1828 msc_get_name.lookup_meaning('RESOURCE_FIRM_TYPE',0))
1829 firm_flag,
1830 ms.firm_planned_type,
1831 nvl(mrr.alternate_num,0) alternate_num,
1832 mrr.resource_seq_num,
1836 'null')
1833 nvl(mdr.resource_code, 'null') resource_code,
1834 mrr.resource_hours,
1835 nvl(msc_get_name.alternate_bom(pe.plan_id, pe.sr_instance_id,pe.bill_sequence_id),
1837 alternate_bom_designator,
1838 nvl(msc_get_name.alternate_bom(pe.plan_id, pe.sr_instance_id,pe.bill_sequence_id),
1839 'null')
1840 alternate_routing_designator,
1841 nvl(to_char(msi.planning_time_fence_date, format_mask),' ') time_fence,
1842 0 mtq_time, --get_MTQ_time(p_transaction_id, p_plan_id, p_instance_id) mtq_time,
1843 nvl(mdr.batchable_flag, 2) batchable,
1844 nvl(mrr.batch_number, -1) batch_number,
1845 nvl(mdr.unit_of_measure,'-1') uom,
1846 nvl(decode(mrr.basis_type, null, '-1',
1847 msc_get_name.lookup_meaning(
1848 'MSC_RES_BASIS_TYPE',mrr.basis_type)),'-1') basis_type,
1849 nvl(decode(mrr.schedule_flag, null, '-1',
1850 msc_get_name.lookup_meaning(
1851 'BOM_RESOURCE_SCHEDULE_TYPE',mrr.schedule_flag)),'-1') schedule_flag,
1852 nvl(to_char(mrr.EARLIEST_START_DATE,format_mask),'null') EPSD,
1853 nvl(to_char(mrr.EARLIEST_COMPLETION_DATE,format_mask),'null') EPCD,
1854 nvl(to_char(mrr.UEPSD,format_mask),'null') UEPSD,
1855 nvl(to_char(mrr.UEPCD,format_mask),'null') UEPCD,
1856 nvl(to_char(mrr.ULPSD,format_mask),'null') ULPSD,
1857 nvl(to_char(mrr.ULPCD,format_mask),'null') ULPCD
1858 FROM msc_supplies ms,
1859 msc_resource_requirements mrr,
1860 msc_department_resources mdr,
1861 msc_system_items msi,
1862 msc_process_effectivity pe
1863 WHERE pe.plan_id(+) = ms.plan_id
1864 AND pe.sr_instance_id(+) = ms.sr_instance_id
1865 AND pe.process_sequence_id(+) = ms.process_seq_id
1866 AND mrr.plan_id = p_plan_id
1867 AND mrr.transaction_id = p_transaction_id
1868 AND mrr.sr_instance_id = p_instance_id
1869 AND ms.plan_id = mrr.plan_id
1870 AND ms.transaction_id = mrr.supply_id
1871 AND ms.sr_instance_id = mrr.sr_instance_id
1872 AND mdr.plan_id = mrr.plan_id
1873 AND mdr.organization_id = mrr.organization_id
1874 AND mdr.sr_instance_id = mrr.sr_instance_id
1875 AND mdr.department_id = mrr.department_id
1876 AND mdr.resource_id = mrr.resource_id
1877 and msi.plan_id = ms.plan_id
1878 and msi.organization_id = ms.organization_id
1879 and msi.sr_instance_id = ms.sr_instance_id
1880 and msi.inventory_item_id = ms.inventory_item_id;
1881
1882 pro_record property%ROWTYPE;
1883
1884 BEGIN
1885 OPEN property;
1886 FETCH property INTO pro_record;
1887 CLOSE property;
1888
1889 v_job := replace_seperator(pro_record.item) || field_seperator ||
1890 pro_record.operation_seq_num || field_seperator ||
1891 pro_record.qty || field_seperator ||
1892 pro_record.firm_date || field_seperator ||
1893 pro_record.sugg_due_date || field_seperator ||
1894 pro_record.needby || field_seperator ||
1895 pro_record.unit_number || field_seperator ||
1896 pro_record.project || field_seperator ||
1897 pro_record.task || field_seperator ||
1898 replace_seperator(pro_record.department_code)|| field_seperator ||
1899 replace_seperator(pro_record.job_name) || field_seperator ||
1900 replace_seperator(pro_record.org) || field_seperator ||
1901 pro_record.assigned_units ||field_seperator ||
1902 pro_record.firm_flag || field_seperator ||
1903 pro_record.firm_planned_type || field_seperator ||
1904 pro_record.alternate_num || field_seperator ||
1905 pro_record.resource_seq_num || field_seperator ||
1906 replace_seperator(pro_record.resource_code) || field_seperator ||
1907 pro_record.resource_hours || field_seperator ||
1908 pro_record.alternate_bom_designator || field_seperator ||
1909 pro_record.alternate_routing_designator || field_seperator ||
1910 pro_record.time_fence || field_seperator ||
1911 pro_record.mtq_time || field_seperator ||
1912 pro_record.batchable || field_seperator ||
1913 pro_record.batch_number || field_seperator ||
1914 pro_record.uom || field_seperator ||
1915 pro_record.basis_type || field_seperator ||
1916 pro_record.schedule_flag || field_seperator ||
1917 pro_record.EPSD || field_seperator ||
1918 pro_record.EPCD || field_seperator ||
1919 pro_record.UEPSD || field_seperator ||
1920 pro_record.UEPCD || field_seperator ||
1921 pro_record.ULPSD || field_seperator ||
1922 pro_record.ULPCD;
1923
1924 if pro_record.transaction_id is not null then
1925 fetchDemandData(p_plan_id, p_instance_id, pro_record.transaction_id,
1926 pro_record.organization_id, v_demand);
1927 end if;
1928 END;
1929
1930 Procedure fetchDemandData( p_plan_id number,
1931 p_instance_id number,
1932 v_transaction_id number,
1933 v_org_id number,
1934 v_demand out NOCOPY varchar2) IS
1935 v_instance_id number;
1936 v_demand_id number;
1937 v_pegging_id number;
1938 v_pegged_qty number;
1939 v_days_late varchar2(3000);
1940 v_demand_quantity number;
1941 v_item_id number;
1942 v_demand_date date;
1943
1944
1945 CURSOR pegging IS
1946 SELECT mfp2.demand_id, mfp2.sr_instance_id,
1947 sum(nvl(mfp1.allocated_quantity,0)),
1948 mfp2.demand_quantity,
1949 mfp2.demand_date,
1950 mfp2.inventory_item_id
1951 FROM msc_full_pegging mfp1,
1952 msc_full_pegging mfp2
1953 WHERE mfp1.plan_id = p_plan_id
1954 AND mfp1.organization_id = v_org_id
1955 AND mfp1.sr_instance_id = p_instance_id
1956 AND mfp1.transaction_id = v_transaction_id
1957 AND mfp2.plan_id = mfp1.plan_id
1958 AND mfp2.sr_instance_id = mfp1.sr_instance_id
1959 AND mfp2.pegging_id = mfp1.end_pegging_id
1960 group by mfp2.demand_id, mfp2.sr_instance_id,
1961 mfp2.demand_quantity, mfp2.demand_date,
1962 mfp2.inventory_item_id;
1963
1964 CURSOR other_demand IS
1965 SELECT nvl(v_demand_quantity,0) qty,
1966 nvl(to_char(v_demand_date,
1967 format_mask), 'null') demand_date,
1968 msc_get_name.lookup_meaning('MRP_FLP_SUPPLY_DEMAND_TYPE',
1969 v_demand_id) type,
1970 item_name item
1971 FROM msc_items
1972 WHERE inventory_item_id = v_item_id;
1973
1974 CURSOR demand IS
1975 SELECT md.using_requirement_quantity qty,
1976 to_char(md.using_assembly_demand_date,
1977 format_mask) demand_date,
1978 nvl(decode(md.schedule_designator_id, null, md.order_number,
1979 msc_get_name.designator(md.schedule_designator_id)),
1980 'null') name,
1981 msc_get_name.lookup_meaning('MRP_DEMAND_ORIGINATION',
1982 md.origination_type) type,
1983 msc_get_name.item_name(md.inventory_item_id, null,null,null) item,
1984 nvl(md.demand_priority,0) priority,
1985 nvl(msc_get_name.customer(md.customer_id),
1986 'null') customer,
1987 nvl(msc_get_name.customer_site(md.customer_site_id),
1988 'null') customer_site,
1989 nvl(to_char(md.dmd_satisfied_date,format_mask),
1990 'null') satisfied_date,
1991 decode(sign(md.dmd_satisfied_date - md.using_assembly_demand_date),
1992 1, GREATEST(round(md.dmd_satisfied_date -
1993 md.using_assembly_demand_date,2), 0.01), 0) days_late,
1994 nvl(to_char(md.quantity_by_due_date),'null') qty_by_due_date,
1995 msc_get_name.org_code(md.organization_id, md.sr_instance_id) org,
1996 nvl(md.demand_class,'null') demand_class
1997 FROM msc_demands md
1998 WHERE md.plan_id = p_plan_id
1999 AND md.demand_id = v_demand_id
2000 AND md.sr_instance_id =v_instance_id
2001 ;
2002
2003 demand_rec demand%ROWTYPE;
2004 other_demand_rec other_demand%ROWTYPE;
2005 rowcount number;
2006 BEGIN
2007 rowcount :=0;
2008
2009 OPEN pegging;
2010 LOOP
2011 FETCH pegging INTO v_demand_id, v_instance_id, v_pegged_qty,
2012 v_demand_quantity, v_demand_date, v_item_id;
2013 EXIT WHEN pegging%NOTFOUND or nvl(length(v_demand),0) > 31000;
2014 rowcount := rowcount +1;
2015 IF v_demand_id not in (-1,-2,-3,18) THEN
2016 OPEN demand;
2017 FETCH demand INTO demand_rec;
2018 CLOSE demand;
2019 v_days_late := demand_rec.days_late;
2020 if v_days_late = 0 then
2021 v_days_late := ' ';
2022 end if;
2023 if v_demand is not null then
2024 if v_demand_id = g_end_demand_id then
2025 v_demand :=
2026 demand_rec.qty || field_seperator ||
2027 demand_rec.demand_date || field_seperator ||
2028 replace_seperator(demand_rec.name) || field_seperator ||
2029 demand_rec.type || field_seperator ||
2030 replace_seperator(demand_rec.item) || field_seperator ||
2031 demand_rec.priority || field_seperator ||
2032 replace_seperator(demand_rec.customer) || field_seperator ||
2033 replace_seperator(demand_rec.customer_site)|| field_seperator ||
2034 demand_rec.satisfied_date || field_seperator ||
2035 v_pegged_qty || field_seperator ||
2036 v_days_late || field_seperator ||
2037 demand_rec.qty_by_due_date || field_seperator ||
2038 demand_rec.org || field_seperator ||
2039 demand_rec.demand_class ||
2040 record_seperator ||v_demand;
2041 else
2042 v_demand := v_demand || record_seperator ||
2043 demand_rec.qty || field_seperator ||
2044 demand_rec.demand_date || field_seperator ||
2045 replace_seperator(demand_rec.name) || field_seperator ||
2046 demand_rec.type || field_seperator ||
2047 replace_seperator(demand_rec.item) || field_seperator ||
2048 demand_rec.priority || field_seperator ||
2049 replace_seperator(demand_rec.customer) || field_seperator ||
2050 replace_seperator(demand_rec.customer_site) || field_seperator ||
2051 demand_rec.satisfied_date || field_seperator ||
2052 v_pegged_qty || field_seperator ||
2053 v_days_late || field_seperator ||
2054 demand_rec.qty_by_due_date || field_seperator ||
2055 demand_rec.org || field_seperator ||
2056 demand_rec.demand_class;
2057 end if;
2058 else
2059 v_demand :=
2060 demand_rec.qty || field_seperator ||
2061 demand_rec.demand_date || field_seperator ||
2062 replace_seperator(demand_rec.name) || field_seperator ||
2063 demand_rec.type || field_seperator ||
2064 replace_seperator(demand_rec.item) || field_seperator ||
2065 demand_rec.priority || field_seperator ||
2066 replace_seperator(demand_rec.customer) || field_seperator ||
2067 replace_seperator(demand_rec.customer_site) || field_seperator ||
2068 demand_rec.satisfied_date || field_seperator ||
2069 v_pegged_qty || field_seperator ||
2070 v_days_late || field_seperator ||
2071 demand_rec.qty_by_due_date || field_seperator ||
2072 demand_rec.org || field_seperator ||
2073 demand_rec.demand_class ;
2074 end if;
2075 ELSE
2076 OPEN other_demand;
2077 FETCH other_demand INTO other_demand_rec;
2078 CLOSE other_demand;
2079 if v_demand is not null then
2080 v_demand := v_demand || record_seperator ||
2081 other_demand_rec.qty || field_seperator ||
2082 other_demand_rec.demand_date || field_seperator ||
2083 'null' || field_seperator ||
2084 other_demand_rec.type || field_seperator ||
2085 replace_seperator(other_demand_rec.item)|| field_seperator ||
2086 '0' || field_seperator ||
2087 'null' || field_seperator ||
2088 'null' || field_seperator ||
2089 'null' || field_seperator ||
2090 v_pegged_qty || field_seperator ||
2091 'null' || field_seperator ||
2092 'null' || field_seperator ||
2093 'null' || field_seperator ||
2094 'null' ;
2095 else
2096 v_demand :=
2097 other_demand_rec.qty || field_seperator ||
2098 other_demand_rec.demand_date || field_seperator ||
2099 'null' || field_seperator ||
2100 other_demand_rec.type || field_seperator ||
2101 replace_seperator(other_demand_rec.item)|| field_seperator ||
2102 '0' || field_seperator ||
2103 'null' || field_seperator ||
2104 'null' || field_seperator ||
2105 'null' || field_seperator ||
2106 v_pegged_qty || field_seperator ||
2107 'null' || field_seperator ||
2108 'null' || field_seperator ||
2109 'null' || field_seperator ||
2110 'null';
2111 end if;
2112 END IF;
2113 END LOOP;
2114 CLOSE pegging;
2115 v_demand :=
2116 to_char(rowcount) || record_seperator || v_demand;
2117
2118 END fetchDemandData;
2119
2120 Procedure fetchRescheduleData(p_plan_id number,
2121 p_instance_id number,
2122 p_org_id number,
2123 p_dept_id number,
2124 p_res_id number,
2125 p_time varchar2,
2126 v_require_data OUT NOCOPY varchar2) IS
2127
2128 oneRecord varchar2(32000);
2129 rowCount number;
2130
2131 CURSOR req IS
2132 select to_char(
2133 decode(nvl(mrr.firm_flag,0),
2134 NO_FIRM, mrr.start_date,
2135 FIRM_RESOURCE, mrr.start_date,
2136 FIRM_END,
2137 mrr.firm_end_date - (mrr.end_date - mrr.start_date),
2138 FIRM_END_RES,
2139 mrr.firm_end_date - (mrr.end_date - mrr.start_date),
2140 nvl(mrr.firm_start_date, mrr.start_date)),
2141 format_mask) start_date,
2142 to_char(least(g_cutoff_date,
2143 decode(nvl(mrr.firm_flag,0),
2144 NO_FIRM, mrr.end_date,
2145 FIRM_RESOURCE, mrr.end_date,
2146 FIRM_START,
2147 mrr.firm_start_date + (mrr.end_date - mrr.start_date),
2148 FIRM_START_RES,
2149 mrr.firm_start_date + (mrr.end_date - mrr.start_date),
2150 nvl(mrr.firm_end_date, mrr.end_date))),
2151 format_mask) end_date,
2152 nvl(msc_get_name.job_name(mrr.supply_id, p_plan_id),
2153 to_char(mrr.supply_id)) job_name,
2154 msc_get_name.supply_type(mrr.supply_id, mrr.plan_id) supply_type,
2155 mrr.assigned_units,
2156 mrr.transaction_id,
2157 mrr.sr_instance_id
2158 from msc_resource_requirements mrr
2159 where mrr.sr_instance_id = p_instance_id
2160 and mrr.plan_id = p_plan_id
2161 and mrr.organization_id = p_org_id
2162 and mrr.end_date is not null
2163 and nvl(mrr.parent_id,2) =2
2164 and nvl(mrr.firm_start_date,mrr.start_date) <= g_cutoff_date
2165 and mrr.department_id = p_dept_id
2166 and mrr.resource_id = p_res_id
2167 and to_date(p_time, format_mask)
2168 BETWEEN decode(nvl(mrr.firm_flag,0),
2169 NO_FIRM, mrr.start_date,
2170 FIRM_RESOURCE, mrr.start_date,
2171 FIRM_END,
2172 mrr.firm_end_date - (mrr.end_date - mrr.start_date),
2173 FIRM_END_RES,
2174 mrr.firm_end_date - (mrr.end_date - mrr.start_date),
2175 nvl(mrr.firm_start_date, mrr.start_date)) AND
2176 decode(nvl(mrr.firm_flag,0),
2177 NO_FIRM, mrr.end_date,
2178 FIRM_RESOURCE, mrr.end_date,
2179 FIRM_START,
2180 mrr.firm_start_date + (mrr.end_date - mrr.start_date),
2181 FIRM_START_RES,
2182 mrr.firm_start_date + (mrr.end_date - mrr.start_date),
2183 nvl(mrr.firm_end_date, mrr.end_date))
2184 order by start_date;
2185
2186 l_req_data req%ROWTYPE;
2187 BEGIN
2188 rowCount :=0;
2189 OPEN req;
2190 LOOP
2191 FETCH req INTO l_req_data;
2192 EXIT WHEN req%NOTFOUND;
2193 if oneRecord is not null then
2194 oneRecord := oneRecord || record_seperator ||
2195 replace_seperator(l_req_data.job_name) || field_seperator ||
2196 l_req_data.start_date || field_seperator ||
2197 l_req_data.end_date || field_seperator ||
2198 l_req_data.supply_type || field_seperator ||
2199 l_req_data.assigned_units || field_seperator ||
2200 l_req_data.transaction_id || field_seperator ||
2201 l_req_data.sr_instance_id ;
2202 else
2203 oneRecord :=
2204 replace_seperator(l_req_data.job_name) || field_seperator ||
2205 l_req_data.start_date || field_seperator ||
2206 l_req_data.end_date || field_seperator ||
2207 l_req_data.supply_type || field_seperator ||
2208 l_req_data.assigned_units || field_seperator ||
2209 l_req_data.transaction_id || field_seperator ||
2210 l_req_data.sr_instance_id ;
2211 end if;
2212 rowCount := rowCount+1;
2213 END LOOP;
2214 CLOSE req;
2215
2216 v_require_data := rowCount || record_seperator ||
2217 oneRecord;
2218
2219 END;
2220
2221 Procedure fetchRescheduleData(p_plan_id number,
2222 p_instance_id number,
2223 p_transaction_id number,
2224 v_require_data OUT NOCOPY varchar2) IS
2225
2226 oneRecord varchar2(32000);
2227 rowCount number;
2228
2229 CURSOR req IS
2230 select to_char(
2231 decode(nvl(mrr.firm_flag,0),
2232 NO_FIRM, mrr.start_date,
2233 FIRM_RESOURCE, mrr.start_date,
2234 FIRM_END,
2235 mrr.firm_end_date - (mrr.end_date - mrr.start_date),
2236 FIRM_END_RES,
2237 mrr.firm_end_date - (mrr.end_date - mrr.start_date),
2238 nvl(mrr.firm_start_date, mrr.start_date)),
2239 format_mask) start_date,
2240 to_char(least(g_cutoff_date,
2241 decode(nvl(mrr.firm_flag,0),
2242 NO_FIRM, mrr.end_date,
2243 FIRM_RESOURCE, mrr.end_date,
2244 FIRM_START,
2245 mrr.firm_start_date + (mrr.end_date - mrr.start_date),
2246 FIRM_START_RES,
2247 mrr.firm_start_date + (mrr.end_date - mrr.start_date),
2248 nvl(mrr.firm_end_date, mrr.end_date))),
2249 format_mask) end_date,
2250 nvl(msc_get_name.job_name(mrr.supply_id, p_plan_id),
2251 to_char(mrr.supply_id)) job_name,
2252 msc_get_name.supply_type(mrr.supply_id, mrr.plan_id) supply_type,
2253 mrr.assigned_units,
2254 mrr.transaction_id,
2255 mrr.sr_instance_id
2256 from msc_resource_requirements mrr
2257 where mrr.sr_instance_id = p_instance_id
2258 and mrr.plan_id = p_plan_id
2259 and mrr.transaction_id = p_transaction_id
2260 and mrr.end_date is not null
2261 and mrr.department_id <> -1
2262 and nvl(mrr.parent_id,2) =2
2263 and nvl(mrr.firm_start_date,mrr.start_date) <= g_cutoff_date
2264 order by start_date;
2265
2266 l_req_data req%ROWTYPE;
2267
2268 BEGIN
2269 rowCount :=0;
2270 OPEN req;
2271 LOOP
2272 FETCH req INTO l_req_data;
2273 EXIT WHEN req%NOTFOUND;
2274 if oneRecord is not null then
2275 oneRecord := oneRecord || record_seperator ||
2276 replace_seperator(l_req_data.job_name) || field_seperator ||
2277 l_req_data.start_date || field_seperator ||
2278 l_req_data.end_date || field_seperator ||
2279 l_req_data.supply_type || field_seperator ||
2280 l_req_data.assigned_units || field_seperator ||
2281 l_req_data.transaction_id || field_seperator ||
2282 l_req_data.sr_instance_id ;
2283 else
2284 oneRecord :=
2285 replace_seperator(l_req_data.job_name) || field_seperator ||
2286 l_req_data.start_date || field_seperator ||
2287 l_req_data.end_date || field_seperator ||
2288 l_req_data.supply_type || field_seperator ||
2289 l_req_data.assigned_units || field_seperator ||
2290 l_req_data.transaction_id || field_seperator ||
2291 l_req_data.sr_instance_id ;
2292 end if;
2293 rowCount := rowCount+1;
2294 END LOOP;
2295 CLOSE req;
2296
2297 v_require_data := rowCount || record_seperator ||
2298 oneRecord;
2299
2300 END;
2301
2302 Procedure fetchAllResource(p_plan_id number,
2303 p_where varchar2,
2304 v_name OUT NOCOPY varchar2) IS
2305 oneRecord varchar2(32000);
2306 rowCount number:=0;
2307
2308 TYPE char_arr IS TABLE OF varchar2(100);
2309 v_dept_code char_arr;
2310 v_org number_arr;
2311 v_instance number_arr;
2312 v_dept number_arr;
2313 v_res number_arr;
2314
2315 BEGIN
2316 oneRecord := null;
2317 rowCount := 0;
2318 select distinct
2319 mtp.organization_code||':'||
2320 mdr.department_code||':'||
2321 mdr.resource_code,
2322 mdr.organization_id,
2323 mdr.sr_instance_id,
2324 mdr.department_id,
2325 mdr.resource_id
2326 bulk collect into
2327 v_dept_code,
2328 v_org, v_instance, v_dept, v_res
2329 FROM msc_department_resources mdr,
2330 msc_trading_partners mtp,
2331 msc_form_query mfq
2332 WHERE mdr.plan_id = p_plan_id
2333 AND mdr.organization_id = mfq.number2
2334 AND mdr.sr_instance_id = mfq.number1
2335 AND mdr.department_id = mfq.number3
2336 AND mdr.resource_id = mfq.number4
2337 AND mfq.query_id = g_res_query_id
2338 AND mtp.partner_type = 3
2339 AND mdr.organization_id = mtp.sr_tp_id
2340 AND mdr.sr_instance_id = mtp.sr_instance_id
2341 AND mdr.aggregate_resource_flag =2
2342 ORDER BY 1,2,3 ;
2343 for a in 1..v_dept_code.count loop
2344 v_dept_code(a) := replace_seperator(v_dept_code(a));
2345 oneRecord := oneRecord || record_seperator ||
2346 v_dept_code(a) || field_seperator ||
2347 v_org(a) || field_seperator ||
2348 v_instance(a) || field_seperator ||
2349 v_dept(a) || field_seperator ||
2350 v_res(a);
2351 end loop;
2352
2353 rowCount := v_dept_code.count;
2354
2355 v_name := rowCount || oneRecord;
2356 END;
2357
2358 Function get_MTQ_time(p_transaction_id number,
2359 p_plan_id number,
2360 p_instance_id number) return number IS
2361 l_mtq number;
2362 l_cumm_quan number;
2363 l_order_quan number;
2364 Begin
2365 select mro.MINIMUM_TRANSFER_QUANTITY,
2366 mrr.cummulative_quantity, ms.new_order_quantity
2367 into l_mtq,l_cumm_quan, l_order_quan
2368 from msc_routing_operations mro,
2369 msc_resource_requirements mrr,
2370 msc_supplies ms
2371 where mrr.plan_id = p_plan_id
2372 and mrr.sr_instance_id = p_instance_id
2373 and mrr.transaction_id = p_transaction_id
2374 and mro.routing_sequence_id = mrr.routing_sequence_id
2375 and mro.operation_sequence_id = mrr.operation_sequence_id
2376 and mro.plan_id = p_plan_id
2377 and mro.sr_instance_id = p_instance_id
2378 and ms.plan_id = p_plan_id
2379 and ms.sr_instance_id = p_instance_id
2380 and ms.transaction_id = mrr.supply_id;
2381 if l_mtq is null then
2382 return 1;
2383 end if;
2384 if l_cumm_quan is null then
2385 if l_order_quan is null then
2386 return 1;
2387 end if;
2388 l_cumm_quan := l_order_quan;
2389 end if;
2390 if l_mtq >= l_cumm_quan or l_mtq < 0 then
2391 return 1;
2392 else
2393 return l_mtq/l_cumm_quan;
2394 end if;
2395 exception when no_data_found then
2396 return 1;
2397 End;
2398
2399 Procedure ValidateTime(p_plan_id number,
2400 p_transaction_id number,
2401 p_instance_id number,
2402 p_start varchar2,
2403 p_end varchar2,
2404 p_return_status OUT NOCOPY varchar2,
2405 p_out OUT NOCOPY varchar2) IS
2406
2407 Cursor curr_res IS
2408 SELECT mrr.operation_seq_num,
2409 nvl(mrr.schedule_flag, 0) schedule_flag,
2410 decode(ms.order_type, 27,
2411 1, ms.firm_planned_type) firm_planned_type,
2412 sysdate theDate,
2413 get_MTQ_time(p_transaction_id, p_plan_id, p_instance_id) mtq_time
2414 FROM msc_resource_requirements mrr,
2415 msc_supplies ms
2416 WHERE mrr.plan_id = p_plan_id
2417 and mrr.transaction_id = p_transaction_id
2418 and mrr.sr_instance_id = p_instance_id
2419 and ms.plan_id = mrr.plan_id
2420 and ms.transaction_id = mrr.supply_id
2421 and ms.sr_instance_id = mrr.sr_instance_id;
2422
2423 Cursor lower_bound IS
2424 SELECT mrr2.operation_seq_num, mrr2.resource_seq_num, mrr2.transaction_id,
2425 nvl(mrr2.schedule_flag, 0) schedule_flag,
2426 decode(nvl(mrr2.firm_flag,0),
2427 NO_FIRM, mrr2.start_date,
2428 FIRM_RESOURCE, mrr2.start_date,
2429 FIRM_END,
2430 mrr2.firm_end_date - (nvl(mrr2.end_date, mrr2.start_date+mrr2.resource_hours/24)
2431 - mrr2.start_date),
2432 FIRM_END_RES,
2433 mrr2.firm_end_date - (nvl(mrr2.end_date, mrr2.start_date+mrr2.resource_hours/24)
2434 - mrr2.start_date),
2435 nvl(mrr2.firm_start_date, mrr2.start_date)) start_date,
2436 decode(nvl(mrr2.firm_flag,0),
2437 NO_FIRM, nvl(mrr2.end_date, mrr2.start_date+mrr2.resource_hours/24),
2438 FIRM_RESOURCE, nvl(mrr2.end_date, mrr2.start_date+mrr2.resource_hours/24),
2439 FIRM_START,
2440 mrr2.firm_start_date + (nvl(mrr2.end_date, mrr2.start_date+mrr2.resource_hours/24)
2441 - mrr2.start_date),
2442 FIRM_START_RES,
2443 mrr2.firm_start_date + (nvl(mrr2.end_date, mrr2.start_date+mrr2.resource_hours/24)
2444 - mrr2.start_date),
2445 nvl(nvl(mrr2.firm_end_date, mrr2.end_date), mrr2.start_date+mrr2.resource_hours/24)) end_date,
2446 get_MTQ_time(mrr2.transaction_id, p_plan_id, p_instance_id) mtq_time
2447 from msc_resource_requirements mrr1,
2448 msc_resource_requirements mrr2,
2449 msc_routings mr
2450 where mrr1.plan_id = p_plan_id
2451 and mrr1.transaction_id = p_transaction_id
2452 and mrr1.sr_instance_id = p_instance_id
2453 and mrr2.plan_id = mrr1.plan_id
2454 and mrr2.supply_id = mrr1.supply_id
2455 and mrr2.sr_instance_id = mrr1.sr_instance_id
2456 and nvl(mrr2.parent_id,2) =2
2457 and mr.plan_id = mrr1.plan_id
2458 and mr.sr_instance_id = mrr1.sr_instance_id
2459 and mr.routing_sequence_id = mrr1.routing_sequence_id
2460 and (((nvl(mr.cfm_routing_flag,2) <> 3 and mrr2.operation_seq_num < mrr1.operation_seq_num)
2461 or ( nvl(mr.cfm_routing_flag,2) = 3 and mrr2.operation_sequence_id in (
2462 select mon.from_op_seq_id from msc_operation_networks mon
2463 where mon.plan_id = mrr1.plan_id
2464 and mon.sr_instance_id = mrr1.sr_instance_id
2465 and mon.routing_sequence_id = mrr1.routing_sequence_id
2466 and mon.to_op_seq_id = mrr1.operation_sequence_id
2467 ))) or
2468 (mrr2.operation_seq_num = mrr1.operation_seq_num and
2469 mrr2.resource_seq_num < mrr1.resource_seq_num))
2470 and (mrr2.firm_start_date is not null or
2471 mrr2.firm_end_date is not null )
2472 and mrr2.firm_flag in (FIRM_START,FIRM_END,FIRM_START_END,FIRM_START_RES,
2473 FIRM_END_RES,FIRM_ALL)
2474 order by mrr2.operation_seq_num desc, mrr2.resource_seq_num desc;
2475
2476 Cursor upper_bound IS
2477 SELECT mrr2.operation_seq_num, mrr2.resource_seq_num,mrr2.transaction_id,
2478 nvl(mrr2.schedule_flag, 0) schedule_flag,
2479 decode(nvl(mrr2.firm_flag,0),
2480 NO_FIRM, mrr2.start_date,
2481 FIRM_RESOURCE, mrr2.start_date,
2482 FIRM_END,
2483 mrr2.firm_end_date - (nvl(mrr2.end_date, mrr2.start_date+mrr2.resource_hours/24)
2484 - mrr2.start_date),
2485 FIRM_END_RES,
2486 mrr2.firm_end_date - (nvl(mrr2.end_date, mrr2.start_date+mrr2.resource_hours/24)
2487 - mrr2.start_date),
2488 nvl(mrr2.firm_start_date, mrr2.start_date)) start_date,
2489 decode(nvl(mrr2.firm_flag,0),
2490 NO_FIRM, nvl(mrr2.end_date, mrr2.start_date+mrr2.resource_hours/24),
2491 FIRM_RESOURCE, nvl(mrr2.end_date, mrr2.start_date+mrr2.resource_hours/24),
2492 FIRM_START,
2493 mrr2.firm_start_date + (nvl(mrr2.end_date, mrr2.start_date+mrr2.resource_hours/24)
2494 - mrr2.start_date),
2495 FIRM_START_RES,
2496 mrr2.firm_start_date + (nvl(mrr2.end_date, mrr2.start_date+mrr2.resource_hours/24)
2497 - mrr2.start_date),
2498 nvl(nvl(mrr2.firm_end_date, mrr2.end_date), mrr2.start_date+mrr2.resource_hours/24)) end_date
2499 from msc_resource_requirements mrr1,
2500 msc_resource_requirements mrr2,
2501 msc_routings mr
2502 where mrr1.plan_id = p_plan_id
2503 and mrr1.transaction_id = p_transaction_id
2504 and mrr1.sr_instance_id = p_instance_id
2505 and mrr2.plan_id = mrr1.plan_id
2506 and mrr2.supply_id = mrr1.supply_id
2507 and mrr2.sr_instance_id = mrr1.sr_instance_id
2508 and nvl(mrr2.parent_id,2) =2
2509 and mr.plan_id = mrr1.plan_id
2510 and mr.sr_instance_id = mrr1.sr_instance_id
2514 select mon.to_op_seq_id from msc_operation_networks mon
2511 and mr.routing_sequence_id = mrr1.routing_sequence_id
2512 and (((nvl(mr.cfm_routing_flag,2) <> 3 and mrr2.operation_seq_num > mrr1.operation_seq_num)
2513 or ( nvl(mr.cfm_routing_flag,2) = 3 and mrr2.operation_sequence_id in (
2515 where mon.plan_id = mrr1.plan_id
2516 and mon.sr_instance_id = mrr1.sr_instance_id
2517 and mon.routing_sequence_id = mrr1.routing_sequence_id
2518 and mon.from_op_seq_id = mrr1.operation_sequence_id
2519 ))) or
2520 (mrr2.operation_seq_num = mrr1.operation_seq_num and
2521 mrr2.resource_seq_num > mrr1.resource_seq_num))
2522 and (mrr2.firm_start_date is not null or
2523 mrr2.firm_end_date is not null )
2524 and mrr2.firm_flag in (FIRM_START,FIRM_END,FIRM_START_END,FIRM_START_RES,
2525 FIRM_END_RES,FIRM_ALL)
2526 order by mrr2.operation_seq_num, mrr2.resource_seq_num;
2527
2528 current_rec curr_res%ROWTYPE;
2529 lower_rec lower_bound%ROWTYPE;
2530 upper_rec upper_bound%ROWTYPE;
2531 v_lower_start date;
2532 v_upper_start Date;
2533 v_lower_start_id number;
2534 v_upper_start_id number;
2535 v_lower_end date;
2536 v_upper_end date;
2537 v_lower_end_id number;
2538 v_upper_end_id number;
2539 v_prev_op number;
2540 v_next_op number;
2541 v_lower_mtq_id number;
2542 v_upper_mtq_id number;
2543 Begin
2544
2545 IF to_date(p_end, format_mask) > g_last_date THEN
2546 p_return_status := 'ERROR';
2547 p_out := 'null'||field_seperator ||
2548 to_char(g_last_date,format_mask)||field_seperator||
2549 'null';
2550 return;
2551 END IF;
2552
2553 OPEN curr_res;
2554 FETCH curr_res INTO current_rec;
2555 CLOSE curr_res;
2556
2557 IF current_rec.firm_planned_type = 1 THEN
2558 p_return_status := 'ERROR';
2559 p_out := 'FIRM_SUPPLY';
2560 return;
2561 END IF;
2562
2563 IF to_date(p_start, format_mask) < current_rec.theDate THEN
2564 p_return_status := 'ERROR';
2565 p_out := to_char(current_rec.theDate,format_mask)
2566 || field_seperator ||'null'||field_seperator||'null' ;
2567 return;
2568 END IF;
2569
2570 OPEN lower_bound;
2571 LOOP
2572 FETCH lower_bound INTO lower_rec;
2573 EXIT WHEN lower_bound%NOTFOUND;
2574 IF v_lower_start is not null and v_lower_end is not null THEN
2575 EXIT;
2576 ELSE
2577 IF v_prev_op is null and
2578 lower_rec.operation_seq_num < current_rec.operation_seq_num THEN
2579 v_prev_op := lower_rec.operation_seq_num;
2580 END IF;
2581 IF v_lower_start is null then
2582 v_lower_start := lower_rec.start_date;
2583 v_lower_start_id := lower_rec.transaction_id;
2584 END IF;
2585 IF v_lower_end is null then
2586 v_lower_end := lower_rec.end_date;
2587 v_lower_end_id := lower_rec.transaction_id;
2588 END IF;
2589 IF v_lower_MTQ_id is null then
2590 IF not (v_prev_op is not null and
2591 lower_rec.operation_seq_num = v_prev_op and
2592 current_rec.schedule_flag = 3 and -- prior
2593 lower_rec.schedule_flag = 4) then
2594 IF to_date(p_start,format_mask) < lower_rec.start_date + lower_rec.mtq_time*(
2595 lower_rec.end_date - lower_rec.start_date) then
2596 v_lower_mtq_id := lower_rec.transaction_id;
2597 END IF;
2598 END IF;
2599 END IF;
2600 END IF;
2601 END LOOP;
2602 CLOSE lower_bound;
2603
2604 OPEN upper_bound;
2605 LOOP
2606 FETCH upper_bound INTO upper_rec;
2607 EXIT WHEN upper_bound%NOTFOUND;
2608 IF v_upper_start is not null and v_upper_end is not null THEN
2609 EXIT;
2610 ELSE
2611 IF v_next_op is null and
2612 upper_rec.operation_seq_num > current_rec.operation_seq_num THEN
2613 v_next_op := upper_rec.operation_seq_num;
2614 END IF;
2615 IF v_upper_start is null then
2616 v_upper_start := upper_rec.start_date;
2617 v_upper_start_id := upper_rec.transaction_id;
2618 END IF;
2619 IF v_upper_end is null then
2620 v_upper_end := upper_rec.end_date;
2621 v_upper_end_id := upper_rec.transaction_id;
2622 END IF;
2623 IF v_upper_MTQ_id is null then
2624 IF not (v_next_op is not null and
2625 upper_rec.operation_seq_num = v_next_op and
2626 current_rec.schedule_flag = 4 and --next
2627 upper_rec.schedule_flag = 3) then
2628 IF upper_rec.start_date < to_date(p_start, format_mask) + current_rec.mtq_time*
2629 (to_date(p_end, format_mask) - to_date(p_start, format_mask)) then
2630 v_upper_mtq_id := upper_rec.transaction_id;
2631 END IF;
2632 END IF;
2633 END IF;
2634 END IF;
2635 END LOOP;
2636 CLOSE upper_bound;
2637
2638 p_return_status := 'OK';
2639 if v_lower_start is not null and to_date(p_start, format_mask) < v_lower_start then
2640 p_out := to_char(v_lower_start,format_mask) || field_seperator
2641 || to_char(v_lower_start_id);
2642 p_return_status := 'ERROR';
2643 else
2647 if v_upper_start is not null and to_date(p_start, format_mask) > v_upper_start then
2644 p_out := 'null' || field_seperator || 'null';
2645 end if;
2646
2648 p_out := p_out || field_seperator
2649 || to_char(v_upper_start,format_mask) || field_seperator
2650 || to_char(v_upper_start_id);
2651 p_return_status := 'ERROR';
2652 else
2653 p_out := p_out || field_seperator || 'null' || field_seperator || 'null';
2654 end if;
2655
2656 if p_return_status = 'ERROR' then
2657 p_out := p_out || field_seperator || 'null' || field_seperator || 'null';
2658 p_out := p_out || field_seperator || 'null' || field_seperator || 'null';
2659 return;
2660 end if;
2661
2662 if v_lower_end is not null and to_date(p_end, format_mask) < v_lower_end then
2663 p_out := p_out || field_seperator
2664 || to_char(v_lower_end,format_mask)|| field_seperator
2665 || to_char(v_lower_end_id);
2666 p_return_status := 'ERROR';
2667 else
2668 p_out := p_out || field_seperator || 'null' || field_seperator || 'null';
2669 end if;
2670
2671 if v_upper_end is not null and to_date(p_end, Format_mask) > v_upper_end then
2672 p_out := p_out || field_seperator
2673 || to_char(v_upper_end,format_mask)|| field_seperator
2674 || to_char(v_upper_end_id);
2675 p_return_status := 'ERROR';
2676 else
2677 p_out := p_out || field_seperator|| 'null' || field_seperator || 'null';
2678 end if;
2679
2680 if p_return_status = 'ERROR' then
2681 return;
2682 end if;
2683
2684 if v_lower_mtq_id is not null or v_upper_mtq_id is not null then
2685 p_return_status := 'WARNING';
2686 p_out := nvl(to_char(v_lower_mtq_id), 'null') ||field_seperator
2687 || nvl(to_char(v_upper_mtq_id), 'null');
2688 return;
2689 end if;
2690 END;
2691
2692 FUNCTION IsTimeFenceCrossed(p_plan_id number,
2693 p_transaction_id number,
2694 p_instance_id number,
2695 p_start varchar2)
2696 RETURN varchar2 IS
2697 l_timefence_date DATE;
2698 l_prev_start_date DATE;
2699 BEGIN
2700 select decode(nvl(mrr.firm_flag,0),
2701 NO_FIRM, mrr.start_date,
2702 FIRM_RESOURCE, mrr.start_date,
2703 FIRM_END,
2704 mrr.firm_end_date - (mrr.end_date - mrr.start_date),
2705 FIRM_END_RES,
2706 mrr.firm_end_date - (mrr.end_date - mrr.start_date),
2707 nvl(mrr.firm_start_date, mrr.start_date)),
2708 msi.PLANNING_TIME_FENCE_DATE
2709 into l_prev_start_date, l_timefence_date
2710 from msc_system_items msi,
2711 msc_resource_requirements mrr,
2712 msc_supplies ms
2713 where mrr.plan_id = p_plan_id
2714 and mrr.transaction_id = p_transaction_id
2715 and mrr.sr_instance_id = p_instance_id
2716 AND ms.plan_id = mrr.plan_id
2717 AND ms.transaction_id = mrr.supply_id
2718 AND ms.sr_instance_id = mrr.sr_instance_id
2719 and msi.plan_id = ms.plan_id
2720 and msi.organization_id = ms.organization_id
2721 and msi.sr_instance_id = ms.sr_instance_id
2722 and msi.inventory_item_id = ms.inventory_item_id;
2723
2724 if (l_timefence_date < l_prev_start_date and
2725 to_date(p_start, format_mask) < l_timefence_date) or
2726 (to_date(p_start, format_mask) > l_timefence_date and
2727 l_timefence_date > l_prev_start_date) then
2728 return 'Y';
2729 else
2730 return 'N';
2731 end if;
2732 END IsTimeFenceCrossed;
2733
2734 Procedure ValidateAndMove(p_plan_id number,
2735 p_transaction_id number,
2736 p_instance_id number,
2737 p_start varchar2,
2738 p_end varchar2,
2739 p_return_status OUT NOCOPY varchar2,
2740 p_out OUT NOCOPY varchar2,
2741 p_out2 OUT NOCOPY boolean) IS
2742 CURSOR simu IS
2743 SELECT mrr2.transaction_id, mrr2.sr_instance_id
2744 FROM msc_resource_requirements mrr1,
2745 msc_resource_requirements mrr2
2746 WHERE mrr1.plan_id = p_plan_id
2747 and mrr1.transaction_id = p_transaction_id
2748 and mrr1.sr_instance_id = p_instance_id
2749 and mrr2.plan_id = mrr1.plan_id
2750 and mrr2.sr_instance_id = mrr1.sr_instance_id
2751 and mrr2.supply_id = mrr1.supply_id
2752 and mrr2.operation_seq_num = mrr1.operation_seq_num
2753 and mrr2.resource_seq_num = mrr1.resource_seq_num
2754 and mrr2.alternate_num = mrr1.alternate_num
2755 and mrr2.transaction_id <> mrr1.transaction_id
2756 and mrr2.parent_id = 2;
2757 row_count number;
2758 v_transaction_id number;
2759 v_instance_id number;
2760 p_saved_status varchar(20);
2761 p_saved_out varchar(400);
2762 Begin
2763 p_out2 :=false;
2764 if (to_date(p_end,format_mask) <= to_date(p_start, format_mask)) then
2765 p_return_status := 'ERROR';
2766 p_out := 'END_BEFORE_START';
2767 return;
2768 end if;
2769 validateTime(p_plan_id, p_transaction_id,
2770 p_instance_id, p_start, p_end,
2771 p_return_status, p_out);
2772 if p_return_status = 'ERROR' then
2773 return;
2774 else
2775 if p_return_status = 'WARNING' then
2776 p_saved_status := p_return_status;
2777 p_saved_out := p_out;
2778 end if;
2779 moveResource(p_plan_id, p_transaction_id, p_instance_id,
2780 p_start, p_end, p_return_status, p_out);
2781 if p_return_status = 'ERROR' then
2782 return;
2786 p_out2 := true;
2783 else
2784 if not p_out2 then
2785 if usingBatchableRes(p_plan_id, p_transaction_id, p_instance_id) then
2787 end if;
2788 end if;
2789 --update the simultaneous resources
2790 row_count :=0;
2791 OPEN simu;
2792 LOOP
2793 FETCH simu INTO v_transaction_id, v_instance_id;
2794 EXIT WHEN simu%NOTFOUND;
2795 row_count := row_count+1;
2796 moveResource(p_plan_id, v_transaction_id, v_instance_id,
2797 p_start, p_end, p_return_status, p_out);
2798 if p_return_status = 'ERROR' then
2799 CLOSE simu;
2800 return;
2801 END IF;
2802 if not p_out2 then
2803 if usingBatchableRes(p_plan_id, v_transaction_id, v_instance_id) then
2804 p_out2 := true;
2805 end if;
2806 end if;
2807 END LOOP;
2808 CLOSE simu;
2809
2810 if row_count > 0 then
2811 if p_saved_status = 'WARNING' then
2812 p_return_status := 'WITH_ST_RES_WITH_WARN';
2813 p_out := p_saved_out;
2814 else
2815 p_return_status := 'WITH_ST_RES';
2816 end if;
2817 else
2818 if p_saved_status = 'WARNING' then
2819 p_return_status := 'NO_ST_RES_WITH_WARN';
2820 p_out := p_saved_out;
2821 else
2822 p_return_status := 'NO_ST_RES';
2823 end if;
2824 end if;
2825 end if;
2826 end if;
2827 END;
2828
2829 Function usingBatchableRes(p_plan_id number,
2830 p_transaction_id number,
2831 p_instance_id number) return boolean is
2832 v_flag number :=2;
2833 Begin
2834 select nvl(mdr.batchable_flag, 2)
2835 into v_flag
2836 from msc_resource_requirements mrr,
2837 msc_department_resources mdr
2838 where mrr.plan_id = p_plan_id
2839 and mrr.transaction_id = p_transaction_id
2840 and mrr.sr_instance_id = p_instance_id
2841 AND mdr.plan_id = mrr.plan_id
2842 AND mdr.organization_id = mrr.organization_id
2843 AND mdr.sr_instance_id = mrr.sr_instance_id
2844 AND mdr.department_id = mrr.department_id
2845 AND mdr.resource_id = mrr.resource_id
2846 ;
2847 if v_flag = 2 then
2848 return false;
2849 else
2850 return true;
2851 end if;
2852 End;
2853
2854 Procedure MoveResource(p_plan_id number,
2855 p_transaction_id number,
2856 p_instance_id number,
2857 p_start varchar2,
2858 p_end varchar2,
2859 p_return_status OUT NOCOPY varchar2,
2860 p_out OUT NOCOPY varchar2) IS
2861 l_firm_flag number;
2862 l_firm_start date;
2863 l_firm_end date;
2864 l_start date;
2865 l_end date;
2866 l_res_id number;
2867 l_alt_num number;
2868 l_supply_id number;
2869 BEGIN
2870 begin
2871
2872 select mrr.firm_flag,mrr.firm_start_date, mrr.firm_end_date,
2873 mrr.resource_id, mrr.alternate_num,
2874 mrr.start_date, mrr.end_date, mrr.supply_id
2875 into l_firm_flag, l_firm_start, l_firm_end,
2876 l_res_id, l_alt_num,
2877 l_start, l_end, l_supply_id
2878 FROM msc_resource_requirements mrr
2879 WHERE mrr.plan_id = p_plan_id
2880 and mrr.transaction_id = p_transaction_id
2881 and mrr.sr_instance_id = p_instance_id
2882 for update of mrr.firm_flag nowait;
2883 exception when app_exception.record_lock_exception then
2884 p_return_status := 'ERROR';
2885 p_out := 'RECORD_LOCK';
2886 return;
2887 end;
2888 if (l_end-l_start) =
2889 (to_date(p_end, format_mask)-to_date(p_start,format_mask)) then
2890 if l_firm_flag in (NO_FIRM, FIRM_START) or l_firm_flag is null THEN
2891 l_firm_flag := FIRM_START;
2892 elsif l_firm_flag in (FIRM_END, FIRM_START_END) THEN
2893 l_firm_flag := FIRM_START_END;
2894 elsif l_firm_flag in (FIRM_RESOURCE, FIRM_START_RES) THEN
2895 l_firm_flag := FIRM_START_RES;
2896 elsif l_firm_flag in (FIRM_END_RES,FIRM_ALL) THEN
2897 l_firm_flag := FIRM_ALL;
2898 else
2899 l_firm_flag := FIRM_START;
2900 end if;
2901 else
2902 if l_firm_flag in
2903 (FIRM_RESOURCE,FIRM_START_RES,FIRM_END_RES,FIRM_ALL) THEN
2904 l_firm_flag := FIRM_ALL;
2905 else
2906 l_firm_flag := FIRM_START_END;
2907 end if;
2908 end if;
2909
2910 if l_firm_flag in (NO_FIRM, FIRM_START,
2911 FIRM_RESOURCE,FIRM_START_RES) THEN
2912 l_firm_end := to_date(null);
2913 else
2914 l_firm_end := to_date(p_end, format_mask);
2915 end if;
2916 l_firm_start := to_date(p_start, format_mask);
2917
2918 --undo_change(p_plan_id, p_transaction_id, p_instance_id,
2919 -- l_firm_flag, l_firm_start, l_firm_end,
2920 -- l_res_id, l_alt_num);
2921 -- update data now
2922 update msc_resource_requirements
2923 set status =0,
2924 applied=2,
2925 firm_flag = l_firm_flag,
2926 firm_start_date =l_firm_start,
2927 firm_end_date =l_firm_end
2931
2928 where plan_id = p_plan_id
2929 and transaction_id = p_transaction_id
2930 and sr_instance_id = p_instance_id;
2932 update msc_supplies
2933 set status =0,
2934 applied=2
2935 where plan_id = p_plan_id
2936 and transaction_id = l_supply_id;
2937
2938 p_return_status := 'OK';
2939 END;
2940
2941 Function get_start_date(p_plan_id number,
2942 p_transaction_id number,
2943 p_instance_id number)
2944 return date IS
2945 Cursor activity_cur IS
2946 select decode(nvl( firm_flag,0),
2947 NO_FIRM, start_date,
2948 FIRM_RESOURCE, start_date,
2949 FIRM_END,
2950 firm_end_date - ( end_date - start_date),
2951 FIRM_END_RES,
2952 firm_end_date - ( end_date - start_date),
2953 nvl(firm_start_date, start_date))
2954 from msc_resource_requirements
2955 where plan_id = p_plan_id
2956 and transaction_id = p_transaction_id
2957 and sr_instance_id = p_instance_id;
2958 v_temp date;
2959 Begin
2960 OPEN activity_cur;
2961 FETCH activity_cur INTO v_temp;
2962 CLOSE activity_cur;
2963 return v_temp;
2964 End get_start_date;
2965
2966 Function get_end_date(p_plan_id number,
2967 p_transaction_id number,
2968 p_instance_id number)
2969 return date IS
2970 Cursor activity_cur IS
2971 select decode(nvl( firm_flag,0),
2972 NO_FIRM, end_date,
2973 FIRM_RESOURCE, end_date,
2974 FIRM_START,
2975 firm_start_date + ( end_date - start_date),
2976 FIRM_START_RES,
2977 firm_start_date + ( end_date - start_date),
2978 nvl(firm_end_date, end_date))
2979 from msc_resource_requirements
2980 where plan_id = p_plan_id
2981 and transaction_id = p_transaction_id
2982 and sr_instance_id = p_instance_id;
2983 v_temp date;
2984 Begin
2985 OPEN activity_cur;
2986 FETCH activity_cur INTO v_temp;
2987 CLOSE activity_cur;
2988 return v_temp;
2989 End get_end_date;
2990
2991 Procedure findRequest(p_plan_id number,
2992 p_where varchar2,
2993 v_resource_list OUT NOCOPY varchar2,
2994 v_supply_list OUT NOCOPY varchar2) IS
2995 TYPE GanttCurTyp IS REF CURSOR;
2996 resource_cursor GanttCurTyp;
2997 sql_statement varchar2(32000);
2998 l_dept number;
2999 l_res number;
3000 l_org number;
3001 l_instance number;
3002 l_supply number;
3003 l_transaction number;
3004 exc_where_stat varchar2(32000);
3005 where_stat varchar2(32000);
3006
3007 CURSOR dept IS
3008 select distinct number1, number2, number3, number4
3009 from msc_form_query
3010 where query_id =g_find_query_id;
3011
3012 CURSOR supply IS
3013 select distinct number1, number5
3014 from msc_form_query
3015 where query_id =g_find_query_id;
3016
3017 v_one_record varchar2(200);
3018 v_len number;
3019
3020 BEGIN
3021 where_stat := ' SELECT sr_instance_id, ' ||
3022 ' organization_id, '||
3023 ' department_id, '||
3024 ' resource_id, '||
3025 ' transaction_id, ' ||
3026 ' r_transaction_id ' ||
3027 ' FROM (select mrr.sr_instance_id, '||
3028 ' mrr.organization_id, ' ||
3029 ' mtp.partner_id, ' ||
3030 ' mrr.department_id, '||
3031 ' mrr.resource_id, '||
3032 ' mrr.transaction_id r_transaction_id, ' ||
3033 ' mrr.supply_id transaction_id, ' ||
3034 ' ms.inventory_item_id, ' ||
3035 ' decode(sign(ms.new_schedule_date '||
3036 '- (ms.need_by_date+1)),1,1,2) late_order, '||
3037 ' msc_get_gantt_data.get_start_date( ' ||
3038 'mrr.plan_id, mrr.transaction_id, ' ||
3039 ' mrr.sr_instance_id) start_date, '||
3040 ' msc_get_gantt_data.get_end_date( ' ||
3041 'mrr.plan_id, mrr.transaction_id, ' ||
3042 ' mrr.sr_instance_id) end_date '||
3043 ' FROM msc_resource_requirements mrr, ' ||
3044 ' msc_supplies ms, ' ||
3045 ' msc_trading_partners mtp ' ||
3046 ' WHERE ms.plan_id = :1 '||
3047 ' and mrr.plan_id = ms.plan_id ' ||
3048 ' and mrr.supply_id = ms.transaction_id ' ||
3049 ' and mrr.sr_instance_id = ms.sr_instance_id ' ||
3050 ' and mrr.organization_id = ms.organization_id ' ||
3051 ' and mrr.organization_id = mtp.sr_tp_id ' ||
3052 ' and mrr.sr_instance_id = mtp.sr_instance_id ' ||
3053 ' and mrr.end_date is not null '||
3054 ' and mtp.partner_type = 3 ' ||
3055 ' and nvl(mrr.parent_id,2) =2) ';
3056
3057 exc_where_stat := ' SELECT sr_instance_id, ' ||
3058 ' organization_id, '||
3059 ' department_id, '||
3060 ' resource_id, '||
3064 ' mrr.organization_id, ' ||
3061 ' transaction_id, ' ||
3062 ' r_transaction_id ' ||
3063 ' FROM (select mrr.sr_instance_id, '||
3065 ' mtp.partner_id, ' ||
3066 ' med.exception_type, ' ||
3067 ' mrr.department_id, '||
3068 ' mrr.resource_id, '||
3069 ' mrr.transaction_id r_transaction_id, ' ||
3070 ' mrr.supply_id transaction_id, ' ||
3071 ' ms.inventory_item_id, ' ||
3072 ' msc_get_gantt_data.get_start_date( ' ||
3073 'mrr.plan_id, mrr.transaction_id, ' ||
3074 ' mrr.sr_instance_id) start_date, '||
3075 ' msc_get_gantt_data.get_end_date( ' ||
3076 'mrr.plan_id, mrr.transaction_id, ' ||
3077 ' mrr.sr_instance_id) end_date '||
3078 ' FROM msc_resource_requirements mrr, ' ||
3079 ' msc_supplies ms, ' ||
3080 ' msc_trading_partners mtp, ' ||
3081 ' msc_exception_details med ' ||
3082 ' WHERE ms.plan_id = :1 '||
3083 ' and mrr.plan_id = ms.plan_id ' ||
3084 ' and mrr.supply_id = ms.transaction_id ' ||
3085 ' and mrr.sr_instance_id = ms.sr_instance_id ' ||
3086 ' and mrr.organization_id = ms.organization_id ' ||
3087 ' and mrr.organization_id = mtp.sr_tp_id ' ||
3088 ' and mrr.sr_instance_id = mtp.sr_instance_id ' ||
3089 ' and mtp.partner_type = 3 ' ||
3090 ' AND med.plan_id = mrr.plan_id ' ||
3091 ' AND med.organization_id = mrr.organization_id ' ||
3092 ' AND med.sr_instance_id = mrr.sr_instance_id ' ||
3093 ' AND decode(med.department_id, -1, mrr.department_id,'||
3094 ' med.department_id) = mrr.department_id ' ||
3095 ' AND decode(med.resource_id, -1, mrr.resource_id, '||
3096 ' med.resource_id) = mrr.resource_id '||
3097 ' AND decode(med.inventory_item_id, -1, '||
3098 ' mrr.assembly_item_id, '||
3099 ' med.inventory_item_id) = mrr.assembly_item_id '||
3100 ' and mrr.end_date is not null '||
3101 ' and nvl(mrr.parent_id,2) =2 )';
3102
3103 if p_where is not null then
3104 if inStr(p_where, 'EXCEPTION_TYPE') <> 0 then
3105 sql_statement := exc_where_stat || ' where 1=1 '||p_where;
3106 else
3107 sql_statement := where_stat || ' where 1=1 '||p_where;
3108 end if;
3109 else
3110 sql_statement := where_stat;
3111 end if;
3112
3113 if g_find_query_id is not null then
3114 delete msc_form_query
3115 where query_id = g_find_query_id;
3116 else
3117 select msc_form_query_s.nextval
3118 into g_find_query_id
3119 from dual;
3120 end if;
3121 OPEN resource_cursor FOR sql_statement
3122 USING p_plan_id;
3123
3124 LOOP
3125 FETCH resource_cursor INTO l_instance, l_org, l_dept, l_res,
3126 l_supply, l_transaction;
3127 EXIT WHEN resource_cursor%NOTFOUND;
3128
3129 insert into msc_form_query
3130 (QUERY_ID,
3131 LAST_UPDATE_DATE,
3132 LAST_UPDATED_BY,
3133 CREATION_DATE,
3134 CREATED_BY,
3135 LAST_UPDATE_LOGIN,
3136 NUMBER1,
3137 NUMBER2,
3138 NUMBER3,
3139 NUMBER4,
3140 NUMBER5,
3141 NUMBER6)
3142 values (
3143 g_find_query_id,
3144 sysdate,
3145 -1,
3146 sysdate,
3147 -1,
3148 -1,
3149 l_instance,
3150 l_org,
3151 l_dept,
3152 l_res,
3153 l_supply,
3154 l_transaction);
3155
3156 END LOOP;
3157 CLOSE resource_cursor;
3158
3159 OPEN dept;
3160 LOOP
3161 FETCH dept INTO l_instance, l_org, l_dept, l_res;
3162 EXIT WHEN dept%NOTFOUND;
3163 v_one_record :=
3164 '('||l_instance ||','||l_org ||','||l_dept||','||l_res||')';
3165 v_len := nvl(length(v_resource_list),0) + nvl(length(v_one_record),0);
3166 if v_resource_list is null then
3167 v_resource_list := v_one_record;
3168 else
3169 if v_len < 31000 then
3170 v_resource_list := v_resource_list ||','||v_one_record;
3171 else
3172 exit;
3173 end if;
3174 end if;
3175 END LOOP;
3176 CLOSE dept;
3177
3178 OPEN supply;
3179 LOOP
3180 FETCH supply INTO l_instance, l_supply;
3181 EXIT WHEN supply%NOTFOUND;
3182 v_one_record := '('||l_instance ||','||l_supply ||')';
3183 v_len := nvl(length(v_supply_list),0) + nvl(length(v_one_record),0);
3184 if v_supply_list is null then
3185 v_supply_list := v_one_record;
3186 else
3187 if v_len < 31000 then
3188 v_supply_list := v_supply_list ||','||v_one_record;
3189 else
3193 END LOOP;
3190 exit;
3191 end if;
3192 end if;
3194 CLOSE supply;
3195
3196 END findRequest;
3197
3198 FUNCTION constructSupplyRequest(p_from_block varchar2,
3199 p_plan_id number,
3200 p_where varchar2)
3201 RETURN varchar2 IS
3202 TYPE GanttCurTyp IS REF CURSOR;
3203 the_cursor GanttCurTyp;
3204 l_instance number;
3205 l_supply number;
3206 l_exp_id number;
3207 sql_stat varchar2(32000);
3208 p_request varchar2(32000);
3209 l_char varchar2(32000);
3210 v_one_record varchar2(200);
3211 v_len number;
3212 BEGIN
3213 if g_plan_id is null OR p_plan_id <> g_plan_id then
3214 l_char := get_plan_time(p_plan_id);
3215 end if;
3216
3217 if p_from_block = 'RESOURCE' then
3218 sql_stat := ' SELECT distinct mrr.sr_instance_id, ' ||
3219 ' mrr.supply_id ' ||
3220 ' FROM msc_resource_requirements mrr, ' ||
3221 ' msc_department_resources mdr ' ||
3222 ' WHERE mrr.plan_id = '||p_plan_id ||
3223 ' AND mdr.plan_id = mrr.plan_id '||
3224 ' AND mdr.organization_id = mrr.organization_id ' ||
3225 ' AND mdr.sr_instance_id = mrr.sr_instance_id'||
3226 ' AND mdr.department_id = mrr.department_id'||
3227 ' AND mdr.resource_id = mrr.resource_id '||
3228 ' AND mdr.aggregate_resource_flag =2 '||
3229 ' and mrr.end_date is not null '||
3230 ' and nvl(mrr.parent_id,2) =2 '||
3231 p_where;
3232 elsif p_from_block = 'EXCEPTION' then
3233
3234 sql_stat :=
3235 'SELECT mrr.sr_instance_id, '||
3236 ' mrr.transaction_id '||
3237 -- ' med.exception_detail_id '||
3238 ' FROM msc_supplies mrr, '||
3239 ' msc_exception_details med '||
3240 ' WHERE mrr.plan_id = '||p_plan_id ||
3241 ' and mrr.plan_id = med.plan_id '||
3242 ' and ( (mrr.transaction_id = med.number1 and '||
3243 ' med.exception_type in (6,7,8,9,10,32,34,53,54,58))'||
3244 ' or (mrr.transaction_id = med.number2 and '||
3245 ' med.exception_type = 37)) '|| p_where ||
3246 ' union select mrr.sr_instance_id, '||
3247 ' mrr.supply_id transaction_id '||
3248 -- ' med.exception_detail_id '||
3249 ' FROM msc_resource_requirements mrr, '||
3250 ' msc_exception_details med '||
3251 ' where med.exception_type in (21,22,36,45,46) '||
3252 ' AND med.plan_id = mrr.plan_id ' ||
3253 ' AND med.organization_id = mrr.organization_id ' ||
3254 ' AND med.sr_instance_id = mrr.sr_instance_id ' ||
3255 ' AND med.department_id = mrr.department_id '||
3256 ' AND med.plan_id = '||p_plan_id ||
3257 ' AND med.resource_id = mrr.resource_id '||
3258 ' and mrr.end_date is not null '||
3259 p_where;
3260
3261 else
3262 sql_stat := 'SELECT mrr.sr_instance_id, '||
3263 ' mrr.transaction_id '||
3264 'FROM msc_supplies mrr '||
3265 ' WHERE mrr.plan_id = '||p_plan_id ||p_where;
3266 end if;
3267
3268 OPEN the_cursor FOR sql_stat;
3269 LOOP
3270 FETCH the_cursor INTO l_instance, l_supply;
3271 EXIT WHEN the_cursor%NOTFOUND;
3272 v_one_record := '('||l_instance ||','||l_supply ||')';
3273 v_len := nvl(length(p_request),0) + nvl(length(v_one_record),0);
3274 if p_request is null then
3275 p_request := v_one_record;
3276 else
3277 if v_len < 31000 then
3278 p_request := p_request ||','|| v_one_record;
3279 else
3280 exit;
3281 end if;
3282 end if;
3283 END LOOP;
3284 CLOSE the_cursor;
3285 return p_request;
3286
3287 END constructSupplyRequest;
3288
3289 FUNCTION constructResourceRequest(p_from_block varchar2,
3290 p_plan_id number,
3291 p_where varchar2) RETURN varchar2 IS
3292 TYPE GanttCurTyp IS REF CURSOR;
3293 the_cursor GanttCurTyp;
3294 l_dept number;
3295 l_res number;
3296 l_org number;
3297 l_instance number;
3298 sql_stat varchar2(32000);
3299 p_request varchar2(32000);
3300 l_char varchar2(2000);
3301 v_one_record varchar2(200);
3302 v_len number;
3303 BEGIN
3304 if g_plan_id is null OR p_plan_id <> g_plan_id then
3305 l_char := get_plan_time(p_plan_id);
3306 end if;
3307
3308 if p_from_block = 'EXCEPTION' then
3309 sql_stat := ' SELECT distinct mrr.sr_instance_id, ' ||
3310 ' mrr.organization_id, '||
3311 ' mrr.department_id, '||
3312 ' mrr.resource_id '||
3313 ' FROM msc_resource_requirements mrr, ' ||
3314 ' msc_department_resources mdr, ' ||
3315 ' msc_exception_details med ' ||
3316 ' WHERE mrr.plan_id = :1 '||
3317 ' AND mdr.plan_id = mrr.plan_id '||
3318 ' AND mdr.organization_id = mrr.organization_id ' ||
3319 ' AND mdr.sr_instance_id = mrr.sr_instance_id'||
3320 ' AND mdr.department_id = mrr.department_id'||
3321 ' AND mdr.resource_id = mrr.resource_id '||
3322 ' AND mdr.aggregate_resource_flag =2 '||
3323 ' AND med.plan_id = mrr.plan_id ' ||
3324 ' AND med.organization_id = mrr.organization_id ' ||
3325 ' AND med.sr_instance_id = mrr.sr_instance_id ' ||
3326 ' AND decode(med.department_id, -1, mrr.department_id,'||
3327 ' med.department_id) = mrr.department_id ' ||
3328 ' AND decode(med.resource_id, -1, mrr.resource_id, '||
3329 ' med.resource_id) = mrr.resource_id '||
3330 ' AND decode(med.inventory_item_id, -1, '||
3331 ' mrr.assembly_item_id, '||
3332 ' med.inventory_item_id) = mrr.assembly_item_id '||
3333 ' and ( (mrr.supply_id = med.number1 and '||
3334 ' med.exception_type in (6,7,8,9,10,32,34,53,54,58))'||
3335 ' or (mrr.supply_id = med.number2 and '||
3336 ' med.exception_type = 37) '||
3337 ' or (med.exception_type in (21,22,36,45,46)))'||
3338 ' and nvl(mrr.parent_id,2) =2 ' ||
3339 ' and mrr.end_date is not null '||
3340 ' and nvl(mrr.firm_end_date,mrr.end_date) <= :2 '||
3341 p_where;
3342
3343 else
3344 sql_stat := ' SELECT distinct mrr.sr_instance_id, ' ||
3345 ' mrr.organization_id, '||
3346 ' mrr.department_id, '||
3347 ' mrr.resource_id '||
3348 ' FROM msc_resource_requirements mrr, ' ||
3349 ' msc_department_resources mdr ' ||
3350 ' WHERE mrr.plan_id = :1 '||
3351 ' AND mdr.plan_id = mrr.plan_id '||
3352 ' AND mdr.organization_id = mrr.organization_id ' ||
3353 ' AND mdr.sr_instance_id = mrr.sr_instance_id'||
3354 ' AND mdr.department_id = mrr.department_id'||
3355 ' AND mdr.resource_id = mrr.resource_id '||
3356 ' AND mdr.aggregate_resource_flag =2 '||
3357 ' and mrr.end_date is not null '||
3358 ' and nvl(mrr.parent_id,2) =2 ' ||
3359 ' and nvl(mrr.firm_end_date,mrr.end_date) <= :2 '||
3360 p_where;
3361
3362 end if;
3363
3364 OPEN the_cursor FOR sql_stat USING p_plan_id, g_cutoff_date;
3365 LOOP
3366 FETCH the_cursor INTO l_instance, l_org, l_dept, l_res;
3367 EXIT WHEN the_cursor%NOTFOUND;
3368 v_one_record :=
3369 '('||l_instance ||','||l_org ||','||l_dept||','||l_res||')';
3370 v_len := nvl(length(p_request),0) + nvl(length(v_one_record),0);
3371 if p_request is null then
3372 p_request := v_one_record;
3373 else
3374 if v_len < 31000 then
3375 p_request := p_request ||','|| v_one_record;
3376 else
3377 exit;
3378 end if;
3379 end if;
3380 END LOOP;
3381 CLOSE the_cursor;
3382 return p_request;
3383
3384 END constructResourceRequest;
3385
3386 FUNCTION constructRequest(p_type varchar2,
3387 p_plan_id number,
3388 p_where varchar2,
3389 p_from_block varchar2) RETURN varchar2 IS
3390 p_request varchar2(32000);
3391
3392 cursor supply_rec is
3393 select number2, number1
3394 from msc_form_query
3395 where query_id = g_supply_query_id;
3396
3397 cursor res_rec is
3398 select distinct number1, number2,number3, number4
3399 from msc_form_query
3400 where query_id = g_res_query_id;
3401
3402 l_dept number;
3403 l_res number;
3404 l_org number;
3405 l_instance number;
3406 l_supply number;
3407 v_one_record varchar2(200);
3408 v_len number;
3409
3410 BEGIN
3411
3412 if p_from_block in ('LATE_DEMAND','ORDER') then
3413 if p_type = 'RESOURCE' then
3414 OPEN res_rec;
3415 LOOP
3416 FETCH res_rec into l_instance, l_org, l_dept, l_res;
3417 EXIT WHEN res_rec%NOTFOUND;
3418 v_one_record :=
3419 '('||l_instance ||','||l_org ||','||l_dept||','||l_res||')';
3420 v_len := nvl(length(p_request),0) + nvl(length(v_one_record),0);
3421 if p_request is null then
3422 p_request := v_one_record;
3423 else
3424 if v_len < 31000 then
3425 p_request := p_request ||','|| v_one_record;
3426 else
3427 exit;
3428 end if;
3429 end if;
3430 END LOOP;
3431 CLOSE res_rec;
3432 else -- order centric view
3433 OPEN supply_rec;
3434 LOOP
3435 FETCH supply_rec into l_instance, l_supply;
3436 EXIT WHEN supply_rec%NOTFOUND;
3437 v_one_record := '('||l_instance ||','||l_supply ||')';
3438 v_len := nvl(length(p_request),0) + nvl(length(v_one_record),0);
3439 if p_request is null then
3440 p_request := v_one_record;
3441 else
3442 if v_len < 31000 then
3443 p_request := p_request ||','|| v_one_record;
3444 else
3445 exit;
3446 end if;
3447 end if;
3448 END LOOP;
3449 CLOSE supply_rec;
3450 end if;
3451 else -- not from late demand and order view
3452 if p_type = 'RESOURCE' then
3453 p_request := constructResourceRequest(p_from_block,p_plan_id, p_where);
3454 else
3455 p_request := constructSupplyRequest(p_from_block,p_plan_id, p_where);
3456 end if;
3457 end if;
3458 return p_request;
3459 END constructRequest;
3460
3461 Function print_one_record(i number) Return varchar2 IS
3462 temp varchar2(2000);
3463 v_critical_flag number;
3464 Begin
3465 peg_data.name(i) := replace_seperator(peg_data.name(i));
3466 temp := peg_data.path(i)|| field_seperator ||
3467 peg_data.type(i) || field_seperator ||
3468 peg_data.transaction_id(i) ||field_seperator ||
3469 peg_data.name(i) ||field_seperator ||
3470 peg_data.instance_id(i) ||field_seperator ||
3471 peg_data.org_id(i);
3472 if peg_data.type(i) in (RES_NODE, END_JOB_NODE) then
3473 temp := temp ||field_seperator||peg_data.start_date(i)||
3474 field_seperator||peg_data.end_date(i);
3475 if peg_data.type(i) = RES_NODE then
3476 temp := temp || field_seperator || peg_data.department_id(i)
3477 || field_seperator || peg_data.status(i)
3478 || field_seperator || peg_data.applied(i)
3479 || field_seperator || peg_data.res_firm_flag(i)
3480 || field_seperator || peg_data.late_flag(i);
3481 else
3482 temp := temp || field_seperator || peg_data.firm_flag(i);
3483 end if;
3484 elsif peg_data.type(i) in (JOB_NODE, PREV_NODE) then
3485 temp := temp ||field_seperator||peg_data.late_flag(i);
3486 end if;
3487
3488
3489 if peg_data.type(i) in (END_DEMAND_NODE,JOB_NODE) then
3490 temp := temp ||field_seperator||peg_data.start_date(i)||
3491 field_seperator||peg_data.end_date(i);
3492 end if;
3493
3494 if g_end_demand_id is not null then
3495 if peg_data.type(i) in (RES_NODE, JOB_NODE) then
3496 if peg_data.critical_flag(i) >=0 then
3497 v_critical_flag := 1;
3498 else
3499 v_critical_flag := 0;
3500 end if;
3501 temp := temp ||field_seperator||peg_data.u_early_start_date(i)
3502 ||field_seperator||peg_data.u_early_end_date(i)
3503 ||field_seperator||peg_data.latest_start_date(i)
3504 ||field_seperator||peg_data.latest_end_date(i)
3505 ||field_seperator||peg_data.min_start_date(i)
3506 ||field_seperator||v_critical_flag
3507 ||field_seperator||peg_data.early_start_date(i)
3508 ||field_seperator||peg_data.early_end_date(i);
3509 end if;
3510 end if;
3511
3512 if peg_data.type(i) = JOB_NODE then
3513 temp := temp ||field_seperator||peg_data.supply_type(i);
3514 end if;
3515
3516 return temp;
3517 End print_one_record;
3518
3519 Function get_plan_time (p_plan_id number) return varchar2 IS
3520 Cursor cutoff_date_cur IS
3521 select curr_cutoff_date +1, curr_cutoff_date +2
3522 from msc_plans
3523 where plan_id = p_plan_id;
3524
3525 CURSOR daylevel_date_cur IS
3526 select min(mpb.bkt_start_date), max(mpb.bkt_end_date)
3527 from msc_plan_buckets mpb,
3528 msc_plans mp
3529 where mp.plan_id =p_plan_id
3530 and mpb.plan_id = mp.plan_id
3531 and mpb.organization_id = mp.organization_id
3532 and mpb.sr_instance_id = mp.sr_instance_id
3533 and mpb.bucket_type =1;
3534
3535 TYPE date_arr IS TABLE OF date;
3536 v_date date_arr;
3537 v_period varchar2(32000);
3538 p_bkt_type number;
3539 v_buckets varchar2(3200);
3540 v_min_day number;
3541 v_hour_day number;
3542 v_date_day number;
3543 v_bkt_date date;
3544 p_gantt_end_date date;
3545
3546 cursor bkt_cur is
3547 select max(mpb.bkt_end_date)
3548 from msc_plan_buckets mpb,
3549 msc_plans mp
3550 where mp.plan_id =p_plan_id
3551 and mpb.plan_id = mp.plan_id
3552 and mpb.organization_id = mp.organization_id
3553 and mpb.sr_instance_id = mp.sr_instance_id
3554 and mpb.bucket_type =p_bkt_type
3555 ;
3556
3557 Begin
3558
3559 -- reset find query id
3560 g_find_query_id := null;
3561
3562 g_plan_id := p_plan_id;
3563
3564 OPEN daylevel_date_cur;
3565 FETCH daylevel_date_cur INTO g_first_date, g_last_date;
3566 CLOSE daylevel_date_cur;
3567
3568 OPEN cutoff_date_cur;
3569 FETCH cutoff_date_cur INTO g_cutoff_date, p_gantt_end_date;
3570 CLOSE cutoff_date_cur;
3571
3572 -- fetch period start date
3573 SELECT greatest(mpsd.period_start_date, mp.data_start_date)
3574 BULK COLLECT INTO v_date
3575 FROM msc_trading_partners tp,
3576 msc_period_start_dates mpsd,
3577 msc_plans mp
3578 WHERE mpsd.calendar_code = tp.calendar_code
3579 and mpsd.sr_instance_id = tp.sr_instance_id
3580 and mpsd.exception_set_id = tp.calendar_exception_set_id
3581 and tp.sr_instance_id = mp.sr_instance_id
3582 and tp.sr_tp_id = mp.organization_id
3583 and tp.partner_type =3
3584 and mp.plan_id = p_plan_id
3585 and (mpsd.period_start_date between mp.data_start_date
3586 and mp.curr_cutoff_date
3587 or mpsd.next_date between mp.data_start_date and
3588 mp.curr_cutoff_date)
3589 order by mpsd.period_start_date;
3590
3591 v_period := to_char(v_date.count);
3592 for a in 1 .. v_date.count loop
3593 v_period := v_period || field_seperator||
3594 to_char(v_date(a), format_mask);
3595 end loop;
3596
3597 -- fetch bucket days
3598
3599 select nvl(MIN_CUTOFF_BUCKET,0),
3600 nvl(HOUR_CUTOFF_BUCKET,0),
3601 DAILY_CUTOFF_BUCKET
3602 into v_min_day, v_hour_day, v_date_day
3603 from msc_plans
3604 where plan_id = p_plan_id;
3605
3606 if v_min_day <> 0 then
3607 v_buckets :=
3608 to_char(g_first_date + v_min_day, format_mask);
3609 else
3610 v_buckets := v_buckets || field_seperator|| '0';
3611 end if;
3612
3613 if v_hour_day <> 0 then
3614 v_buckets := v_buckets || field_seperator||
3615 to_char(g_first_date + v_min_day+v_hour_day, format_mask);
3616 else
3617 v_buckets := v_buckets || field_seperator|| '0';
3618 end if;
3619
3620 if v_min_day+v_hour_day <> v_date_day then
3621 v_buckets := v_buckets || field_seperator||
3622 to_char(g_last_date, format_mask);
3623 else
3624 v_buckets := v_buckets || field_seperator|| '0';
3625 end if;
3626 p_bkt_type := 1;
3627 for a in 1..2 loop
3628 v_bkt_date := null;
3629 p_bkt_type := p_bkt_type +1;
3630 OPEN bkt_cur;
3631 FETCH bkt_cur into v_bkt_date;
3632 CLOSE bkt_cur;
3633 if v_bkt_date is not null then
3634 v_buckets := v_buckets || field_seperator||
3635 to_char(v_bkt_date, format_mask);
3636 else
3637 v_buckets := v_buckets || field_seperator|| '0';
3638 end if;
3639 end loop;
3640 return record_seperator || to_char(g_first_date, format_mask)
3641 || record_seperator || to_char(p_gantt_end_date, format_mask)
3642 || record_seperator || v_period
3643 || record_seperator || v_buckets ;
3644 END get_plan_time;
3645
3646 PROCEDURE validate_and_move_end_job (p_plan_id number,
3647 p_supply_id number,
3648 p_end varchar2,
3649 p_return_status OUT NOCOPY varchar2,
3650 p_out out NOCOPY varchar2 ) IS
3651 l_quan Number;
3652
3653 BEGIN
3654 if to_date(p_end,format_mask) < g_first_date then
3655 p_return_status := 'ERROR';
3656 p_out := 'START';
3657 return;
3658 elsif to_date(p_end,format_mask) > g_cutoff_date then
3659 p_return_status := 'ERROR';
3660 p_out := 'END';
3661 return;
3662 end if;
3663
3664 BEGIN
3665 SELECT nvl(new_order_quantity,0)
3666 INTO l_quan
3667 FROM msc_supplies
3668 WHERE plan_id = p_plan_id
3669 AND transaction_id = p_supply_id
3670 FOR UPDATE OF firm_date NOWAIT;
3671 EXCEPTION WHEN app_exception.record_lock_exception THEN
3672 p_return_status := 'ERROR';
3673 return;
3674 END;
3675 -- now update
3676 UPDATE msc_supplies
3677 SET firm_date = to_date(p_end, format_mask), firm_quantity = l_quan,
3678 applied = 2, status = 0, firm_planned_type = 1
3679 WHERE plan_id = p_plan_id
3680 AND transaction_id = p_supply_id;
3681
3682 p_return_status := 'OK';
3683
3684 END validate_and_move_end_job;
3685
3686 Function get_result(start_index IN number,
3687 v_return_data OUT NOCOPY varchar2,
3688 next_index OUT NOCOPY number)
3689 return boolean IS
3690 v_one_record varchar2(2000);
3691 v_len number :=0;
3692 i number;
3693 Begin
3694 i := start_index;
3695 if peg_data.parent_index.count > 0 and
3696 i < peg_data.parent_index.count then
3697 while i is not null loop
3698 v_one_record := print_one_record(i);
3699 v_len := nvl(length(v_return_data),0) + nvl(length(v_one_record),0);
3700
3701 if v_len < 1000 then
3702 v_return_data := v_return_data || record_seperator || v_one_record;
3703 next_index := i+1;
3704 i := peg_data.parent_index.next(i);
3705 else
3706 exit;
3707 end if;
3708 end loop;
3709 end if;
3710
3711 if next_index = peg_data.parent_index.count then
3712 if g_has_more_supply and g_end_demand_id is null then
3713 -- add next code
3714 v_one_record := next_index+1|| field_seperator ||
3715 NEXT_NODE || field_seperator ||
3716 -1 ||field_seperator ||
3717 'Next '||g_supply_limit||field_seperator ||
3718 -1 ||field_seperator ||
3719 -1 ||field_seperator||0;
3720 v_return_data :=v_return_data || record_seperator ||
3721 v_one_record;
3722 end if;
3723 return false;
3724 elsif v_return_data is null then
3725 return false;
3726 else
3727 return true;
3728 end if;
3729
3730 End get_result;
3731
3732 Procedure explode_children(p_plan_id number,
3733 p_critical number default -1) IS
3734
3735 p_supply_id number;
3736 p_instance_id number;
3737 p_org_id number;
3738 p_op_seq number;
3739 p_query_id number;
3740 p_op_seq_query_id number;
3741 p_end_peg_query_id number;
3742
3743 CURSOR ops_seq_cur IS
3744 select distinct to_char(number2),
3745 number2,
3746 OP_NODE,
3747 0,
3748 0,
3749 0
3750 from msc_form_query
3751 where query_id = p_op_seq_query_id
3752 and number1 = p_supply_id
3753 order by number2;
3754
3755 -- get children which are not components
3756
3757 Cursor peg_data_cur IS
3758 select distinct ms.organization_id,
3762 ms.plan_id, ms.sr_instance_id,
3759 ms.transaction_id,
3760 ms.sr_instance_id,
3761 msc_get_gantt_data.order_number(ms.order_type,ms.order_number,
3763 ms.transaction_id, ms.disposition_id) ||' for '||
3764 msi.item_name ||
3765 ' in ' ||mtp.organization_code ||'('||
3766 ms.new_order_quantity||')',
3767 nvl(ms.firm_planned_type,2),
3768 nvl(ms.status, 0),
3769 nvl(ms.applied,0),
3770 msc_get_gantt_data.isSupplyLate(ms.plan_id,ms.sr_instance_id,
3771 ms.organization_id,ms.inventory_item_id,ms.transaction_id),
3772 msc_get_gantt_data.actualStartDate(ms.order_type,
3773 msi.planning_make_buy_code,
3774 ms.organization_id,
3775 ms.source_organization_id,
3776 ms.new_dock_date,
3777 ms.new_wip_start_date,
3778 ms.new_ship_date,
3779 ms.new_schedule_date),
3780 nvl(to_char(ms.new_schedule_date,format_mask),'null'),
3781 decode(g_end_demand_id, null, 'null',
3782 nvl(to_char(ms.EARLIEST_START_DATE,format_mask),'null')),
3783 decode(g_end_demand_id, null, 'null',
3784 nvl(to_char(ms.EARLIEST_COMPLETION_DATE,format_mask),'null')),
3785 decode(g_end_demand_id, null, 'null',
3786 nvl(to_char(ms.ULPSD,format_mask),'null')),
3787 decode(g_end_demand_id, null, 'null',
3788 nvl(to_char(ms.ULPCD,format_mask),'null')),
3789 decode(g_end_demand_id, null, 'null',
3790 nvl(to_char(ms.UEPSD,format_mask),'null')),
3791 decode(g_end_demand_id, null, 'null',
3792 nvl(to_char(ms.UEPCD,format_mask),'null')),
3793 decode(g_end_demand_id, null, 'null',
3794 nvl(to_char(ms.MIN_START_DATE,format_mask), 'null')),
3795 decode(g_end_demand_id, null, 0,
3796 msc_get_gantt_data.isCriticalSupply(p_plan_id,g_end_demand_id,
3797 ms.transaction_id, ms.sr_instance_id)),
3798 msc_get_gantt_data.supplyType(ms.order_type,
3799 msi.planning_make_buy_code,
3800 ms.organization_id,
3801 ms.source_organization_id),
3802 mtp.organization_code||':'||msi.item_name,
3803 ms.inventory_item_id,
3804 nvl(ms.supplier_id,-1)
3805 from msc_full_pegging mfp1,
3806 msc_full_pegging mfp2,
3807 msc_supplies ms,
3808 msc_system_items msi,
3809 msc_trading_partners mtp,
3810 msc_form_query mfq
3811 where mfp1.plan_id = p_plan_id
3812 and mfp1.transaction_id = p_supply_id
3813 and mfp1.sr_instance_id = p_instance_id
3814 and mfp1.end_pegging_id = mfq.number1
3815 and mfq.query_id = p_end_peg_query_id
3816 and mfp2.plan_id = mfp1.plan_id
3817 and mfp2.prev_pegging_id = mfp1.pegging_id
3818 and ms.plan_id = mfp2.plan_id
3819 and ms.transaction_id = mfp2.transaction_id
3820 and ms.sr_instance_id = mfp2.sr_instance_id
3821 and msi.plan_id = ms.plan_id
3822 and msi.organization_id = ms.organization_id
3823 and msi.sr_instance_id = ms.sr_instance_id
3824 and msi.inventory_item_id = ms.inventory_item_id
3825 and mtp.partner_type=3
3826 and mtp.sr_tp_id=ms.organization_id
3827 and mtp.sr_instance_id = ms.sr_instance_id
3828 and ms.transaction_id not in (
3829 select mfq.number3
3830 from msc_form_query mfq
3831 where mfq.query_id = p_query_id
3832 and mfq.number1 = p_supply_id
3833 and mfq.number2 is not null -- op_seq_num is not null
3834 );
3835
3836 -- get the children which have operation in msc_resource_requirements
3837 Cursor job_data_cur(p_op_seq_num number) IS
3838 select distinct
3839 mfq.number3, -- ms.transaction_id,
3840 mfq.number4, -- ms.sr_instance_id,
3841 mfq.number5, -- ms.organization_id,
3842 mfq.char10 || ' for '|| -- ms.order_number
3843 mi.item_name ||
3844 ' in ' ||mtp.organization_code ||'('||
3845 mfq.number11||')',
3846 mfq.number7, -- nvl(ms.firm_planned_type,2),
3847 mfq.number8, -- nvl(ms.status, 0),
3848 mfq.number9, -- nvl(ms.applied,0),
3849 mfq.number10, -- late flag
3850 mfq.char1,
3851 mfq.char2,
3852 mfq.char3,
3853 mfq.char4,
3854 mfq.char5,
3855 mfq.char6,
3856 mfq.char7,
3857 mfq.char8,
3858 mfq.char9,
3859 mfq.number12,
3860 mfq.number13,
3861 mtp.organization_code||':'||mi.item_name,
3862 mfq.number14,
3863 mfq.number15
3864 from msc_form_query mfq,
3865 msc_items mi,
3866 msc_trading_partners mtp
3867 where mfq.query_id = p_query_id
3868 and mfq.number1 = p_supply_id
3869 and mfq.number2 =p_op_seq_num
3870 and mi.inventory_item_id = mfq.number6
3871 and mtp.partner_type=3
3872 and mtp.sr_tp_id=mfq.number5
3873 and mtp.sr_instance_id = mfq.number4;
3874
3875 CURSOR ops_data_cursor IS
3876 select to_char(mrr.operation_seq_num)||'/'
3877 ||to_char(mrr.resource_seq_num)||
3878 '('||msc_get_name.department_resource_code(mrr.resource_id,
3879 mrr.department_id, mrr.organization_id,
3880 mrr.plan_id, mrr.sr_instance_id)||')',
3881 to_char(msc_get_gantt_data.get_start_date(
3882 mrr.plan_id, mrr.transaction_id, mrr.sr_instance_id),
3883 format_mask),
3884 to_char(nvl(msc_get_gantt_data.get_end_date(
3885 mrr.plan_id, mrr.transaction_id, mrr.sr_instance_id),
3886 mrr.start_date),
3887 format_mask),
3888 mrr.transaction_id,
3889 nvl(mrr.department_id, 0),
3890 nvl(mrr.resource_id, 0),
3891 nvl(mrr.status, 0),
3892 nvl(mrr.applied, 0),
3893 nvl(mrr.firm_flag, 0),
3894 msc_get_gantt_data.isSupplyLate(ms.plan_id,ms.sr_instance_id,
3895 ms.organization_id,ms.inventory_item_id,ms.transaction_id),
3896 decode(g_end_demand_id, null, 'null',
3897 nvl(to_char(mrr.EARLIEST_START_DATE,format_mask),'null')),
3898 decode(g_end_demand_id, null, 'null',
3899 nvl(to_char(mrr.EARLIEST_COMPLETION_DATE,format_mask),'null')),
3900 decode(g_end_demand_id, null, 'null',
3901 nvl(to_char(mrr.ULPSD,format_mask),'null')),
3902 decode(g_end_demand_id, null, 'null',
3903 nvl(to_char(mrr.ULPCD,format_mask),'null')),
3904 decode(g_end_demand_id, null, 'null',
3905 nvl(to_char(mrr.UEPSD,format_mask),'null')),
3906 decode(g_end_demand_id, null, 'null',
3907 nvl(to_char(mrr.UEPCD,format_mask),'null')),
3908 decode(g_end_demand_id, null, 'null',
3909 nvl(to_char(ms.MIN_START_DATE,format_mask), 'null')),
3910 decode(g_end_demand_id, null, 0,
3911 msc_get_gantt_data.isCriticalRes(p_plan_id,g_end_demand_id,
3912 ms.transaction_id, ms.sr_instance_id,
3913 mrr.operation_seq_num, mrr.routing_sequence_id))
3914 from msc_resource_requirements mrr,
3915 msc_supplies ms
3916 where mrr.plan_id = p_plan_id
3917 and mrr.supply_id = p_supply_id
3918 and mrr.parent_id =2
3919 and mrr.operation_seq_num = p_op_seq
3920 and mrr.sr_instance_id = p_instance_id
3921 and mrr.organization_id = p_org_id
3922 and mrr.end_date is not null
3923 and mrr.department_id <> -1
3924 and ms.plan_id = mrr.plan_id
3925 and ms.transaction_id = mrr.supply_id
3926 and ms.sr_instance_id = mrr.sr_instance_id
3927 order by 2,3,1;
3928
3929 CURSOR date_cur IS
3930 select to_char(nvl(decode(nvl(firm_planned_type,2),2,
3931 new_dock_date,
3932 new_dock_date+(firm_date-new_schedule_date)),
3933 new_schedule_date),
3934 format_mask),
3935 to_char(decode(nvl(firm_planned_type,2),2,
3936 new_schedule_date,nvl(firm_date,new_schedule_date)),
3937 format_mask),
3938 nvl(firm_planned_type,2)
3939 from msc_supplies
3940 where plan_id = p_plan_id
3941 and transaction_id = p_supply_id;
3942
3943 i number;
3944
3945 current_index number;
3946 parent_index number;
3947 child_index number;
3948 hasMore boolean;
3949 moreParent boolean;
3950 next_row number;
3951 p_count number;
3952 firstOp boolean;
3953 p_first_op number;
3954 v_op number_arr;
3955 v_new_op number_arr;
3956 v_dummy number;
3957 v_org_id number;
3958 v_transaction_id number;
3959 v_instance_id number;
3960 v_dept_id number;
3961 v_res_id number;
3962 v_name varchar2(200);
3963 v_firm_flag number;
3964 v_status number;
3965 v_applied number;
3966 v_late_flag number;
3967 v_start_date varchar2(20);
3968 v_end_date varchar2(20);
3969 v_early_start_date varchar2(20);
3970 v_early_end_date varchar2(20);
3971 v_u_early_start_date varchar2(20);
3972 v_u_early_end_date varchar2(20);
3973 v_latest_start_date varchar2(20);
3974 v_latest_end_date varchar2(20);
3975 v_min_start_date varchar2(20);
3976 v_critical_flag number;
3977 v_supply_type number;
3978 v_supplier_id number;
3979 v_item_id number;
3980 v_org_code varchar2(300);
3981
3982 p_end_supply_id number;
3983
3984 BEGIN
3985
3986 -- find the end_pegging_id
3987
3988 if g_end_demand_id is not null then
3989
3990 select msc_form_query_s.nextval
3991 into p_end_peg_query_id
3992 from dual;
3993
3994 insert into msc_form_query
3995 (QUERY_ID,
3996 LAST_UPDATE_DATE,
3997 LAST_UPDATED_BY,
3998 CREATION_DATE,
3999 CREATED_BY,
4000 LAST_UPDATE_LOGIN,
4001 NUMBER1)
4002 select
4003 p_end_peg_query_id,
4004 sysdate,
4005 -1,
4006 sysdate,
4007 -1,
4008 -1,
4009 mfp.end_pegging_id
4010 from msc_full_pegging mfp
4011 where mfp.plan_id = p_plan_id
4012 and mfp.demand_id = g_end_demand_id;
4013
4014 end if;
4015 i := the_index;
4016 the_index := 0;
4017 if i = 0 then
4018 hasMore := false;
4019 else
4020 hasMore := true;
4021 end if;
4022 if g_has_prev_supply and g_end_demand_id is null then
4023 current_index :=1;
4024 else
4025 current_index :=0;
4026 end if;
4027 parent_index :=0;
4028 while (hasMore) loop
4029
4030 -- fetch the children
4031 next_row := -1;
4032 child_index :=0;
4033 p_supply_id := peg_data.transaction_id(current_index);
4034 p_instance_id := peg_data.instance_id(current_index);
4035 p_org_id := peg_data.org_id(current_index);
4036 if peg_data.type(current_index) = END_DEMAND_NODE then
4037 next_row := peg_data.next_record(current_index); -- move to the next record
4038 elsif peg_data.type(current_index) = JOB_NODE then
4039 if g_end_demand_id is null then
4040 p_end_supply_id :=
4041 peg_data.res_firm_flag(current_index); -- end supply tran id
4042 else
4043 p_end_supply_id := null;
4044 end if;
4045 -- populate op seq num from msc_resource_requirements to msc_form_query
4046
4047 if p_op_seq_query_id is null then
4048 select msc_form_query_s.nextval
4049 into p_op_seq_query_id
4050 from dual;
4051 end if;
4052 if p_critical <> -1 then
4053 insert into msc_form_query
4054 (QUERY_ID,
4055 LAST_UPDATE_DATE,
4056 LAST_UPDATED_BY,
4057 CREATION_DATE,
4058 CREATED_BY,
4059 LAST_UPDATE_LOGIN,
4060 NUMBER1, -- supply_id
4061 NUMBER2) -- op_seq
4062 select distinct
4063 p_op_seq_query_id,
4064 trunc(sysdate),
4065 -1,
4066 trunc(sysdate),
4067 -1,
4068 -1,
4069 p_supply_id,
4070 mrr.operation_seq_num
4071 from msc_resource_requirements mrr,
4072 msc_critical_paths mcp
4073 where mrr.plan_id = p_plan_id
4074 and mrr.supply_id = p_supply_id
4075 and mrr.sr_instance_id = p_instance_id
4076 and mrr.end_date is not null
4077 and nvl(mrr.parent_id,2) =2
4078 and mrr.department_id <> -1
4079 and mrr.organization_id = p_org_id
4080 and mrr.plan_id = mcp.plan_id
4084 nvl(mcp.routing_sequence_id,-1)
4081 and mrr.sr_instance_id = mcp.sr_instance_id
4082 and mrr.supply_id = mcp.supply_id
4083 and nvl(mrr.routing_sequence_id,-1) =
4085 and mrr.operation_seq_num = mcp.operation_sequence_id
4086 and mcp.demand_id = g_end_demand_id
4087 and nvl(mcp.path_number,1) =
4088 decode(p_critical,0,0,nvl(mcp.path_number,1));
4089 else -- not critical only
4090 insert into msc_form_query
4091 (QUERY_ID,
4092 LAST_UPDATE_DATE,
4093 LAST_UPDATED_BY,
4094 CREATION_DATE,
4095 CREATED_BY,
4096 LAST_UPDATE_LOGIN,
4097 NUMBER1, -- supply_id
4098 NUMBER2) -- op_seq
4099 select distinct
4100 p_op_seq_query_id,
4101 trunc(sysdate),
4102 -1,
4103 trunc(sysdate),
4104 -1,
4105 -1,
4106 p_supply_id,
4107 operation_seq_num
4108 from msc_resource_requirements
4109 where plan_id = p_plan_id
4110 and supply_id = p_supply_id
4111 and sr_instance_id = p_instance_id
4112 and end_date is not null
4113 and department_id <> -1
4114 and nvl(parent_id,2) =2
4115 and organization_id = p_org_id;
4116 end if;
4117 firstOp := true;
4118 p_first_op :=1;
4119 -- get it's operations
4120 OPEN ops_seq_cur;
4121 LOOP
4122 FETCH ops_seq_cur INTO peg_data.name(i),
4123 peg_data.op_seq(i),
4124 peg_data.type(i),
4125 peg_data.status(i),
4126 peg_data.applied(i),
4127 peg_data.res_firm_flag(i);
4128 EXIT WHEN ops_seq_cur%NOTFOUND;
4129 if firstOp then
4130 peg_data.status(i) :=1;
4131 p_first_op :=peg_data.op_seq(i);
4132 firstOp := false;
4133 else
4134 peg_data.status(i) :=0;
4135 end if;
4136 peg_data.res_firm_flag(i) := p_end_supply_id; -- end supply trans id
4137 peg_data.late_flag(i) := 0;
4138 peg_data.parent_index(i) := current_index;
4139 peg_data.next_record(i) := -1;
4140 peg_data.transaction_id(i) := p_supply_id;
4141 peg_data.instance_id(i) := p_instance_id;
4142 peg_data.org_id(i) := p_org_id;
4143 peg_data.path(i) := peg_data.path(current_index)||
4144 '-'||to_char(child_index);
4145 peg_data.new_path(i) := peg_data.path(i);
4146 if next_row > 0 then
4147 peg_data.next_record(i-1) := i;
4148 end if;
4149 if next_row=-1 then
4150 next_row :=i;
4151 end if;
4152
4153 i := i+1;
4154 child_index := child_index +1;
4155
4156 END LOOP;
4157 CLOSE ops_seq_cur;
4158
4159 -- populate the children to msc_form_query
4160
4161 if p_query_id is null then
4162 select msc_form_query_s.nextval
4163 into p_query_id
4164 from dual;
4165 end if;
4166
4167 -- get the end_pegging_id
4168 if g_end_demand_id is null then
4169
4170 select msc_form_query_s.nextval
4171 into p_end_peg_query_id
4172 from dual;
4173
4174 insert into msc_form_query
4175 (QUERY_ID,
4176 LAST_UPDATE_DATE,
4177 LAST_UPDATED_BY,
4178 CREATION_DATE,
4179 CREATED_BY,
4180 LAST_UPDATE_LOGIN,
4181 NUMBER1)
4182 select
4183 p_end_peg_query_id,
4184 sysdate,
4185 -1,
4186 sysdate,
4187 -1,
4188 -1,
4189 mfp.end_pegging_id
4190 from msc_full_pegging mfp
4191 where mfp.plan_id = p_plan_id
4192 and mfp.transaction_id = p_end_supply_id;
4193 end if;
4194
4195 -- only get the children which are in the same pegging tree
4196
4197 if (p_critical <> -1) then
4198
4199 insert into msc_form_query
4200 (QUERY_ID,
4201 LAST_UPDATE_DATE,
4202 LAST_UPDATED_BY,
4203 CREATION_DATE,
4204 CREATED_BY,
4205 LAST_UPDATE_LOGIN,
4206 NUMBER1, -- supply_id
4207 NUMBER2, -- op_seq
4208 NUMBER3, -- tran_id
4209 NUMBER4, -- inst_id
4210 NUMBER5, -- org_id
4211 NUMBER6, -- item_id
4212 NUMBER7, --firm_type
4213 NUMBER8, -- status
4214 NUMBER9, -- applied
4215 NUMBER10, -- late_flag
4216 NUMBER11, -- qty
4217 CHAR10, -- order_number
4218 CHAR1, -- start date
4219 CHAR2, -- end date
4220 CHAR3, -- early start date
4221 CHAR4, -- early end date
4222 CHAR5, -- latest start date
4223 CHAR6, -- latest end date
4224 CHAR7, -- min start
4225 CHAR8, -- u early start date
4226 CHAR9, -- u early end date
4227 NUMBER12, -- critical_flag
4228 NUMBER13, -- supply type
4229 NUMBER14, -- item_id
4230 NUMBER15) -- supplier_id
4231 select distinct
4232 p_query_id,
4233 trunc(sysdate),
4234 -1,
4235 trunc(sysdate),
4236 -1,
4237 -1,
4238 p_supply_id,
4239 decode(md.op_seq_num,1,p_first_op,md.op_seq_num),
4240 ms.transaction_id,
4241 ms.sr_instance_id,
4242 ms.organization_id,
4243 ms.inventory_item_id,
4244 nvl(ms.firm_planned_type,2),
4245 nvl(ms.status, 0),
4246 nvl(ms.applied,0),
4247 msc_get_gantt_data.isSupplyLate(ms.plan_id,ms.sr_instance_id,
4248 ms.organization_id,ms.inventory_item_id,ms.transaction_id),
4249 ms.new_order_quantity,
4250 msc_get_gantt_data.order_number(ms.order_type,ms.order_number,
4251 ms.plan_id, ms.sr_instance_id,
4252 ms.transaction_id, ms.disposition_id),
4253 msc_get_gantt_data.actualStartDate(ms.order_type,
4254 msi.planning_make_buy_code,
4255 ms.organization_id,
4256 ms.source_organization_id,
4257 ms.new_dock_date,
4258 ms.new_wip_start_date,
4259 ms.new_ship_date,
4260 ms.new_schedule_date),
4261 nvl(to_char(ms.new_schedule_date,format_mask),'null'),
4262 nvl(to_char(ms.EARLIEST_START_DATE,format_mask),'null'),
4263 nvl(to_char(ms.EARLIEST_COMPLETION_DATE,format_mask),'null'),
4264 nvl(to_char(ms.ULPSD,format_mask),'null'),
4265 nvl(to_char(ms.ULPCD,format_mask),'null'),
4266 nvl(to_char(ms.MIN_START_DATE,format_mask), 'null'),
4267 nvl(to_char(ms.UEPSD,format_mask),'null'),
4268 nvl(to_char(ms.UEPCD,format_mask),'null'),
4269 1,
4270 msc_get_gantt_data.supplyType(ms.order_type,
4271 msi.planning_make_buy_code,
4272 ms.organization_id,
4273 ms.source_organization_id),
4274 ms.inventory_item_id,
4275 nvl(ms.supplier_id,-1)
4276 from msc_full_pegging mfp1,
4277 msc_full_pegging mfp2,
4278 msc_supplies ms,
4279 msc_demands md,
4280 msc_system_items msi,
4281 msc_critical_paths mcp,
4282 msc_form_query mfq
4283 where mfp1.plan_id = p_plan_id
4284 and mfp1.transaction_id = p_supply_id
4285 and mfp1.end_pegging_id = mfq.number1
4286 and mfq.query_id = p_end_peg_query_id
4287 and md.plan_id = mfp1.plan_id
4288 and md.disposition_id = mfp1.transaction_id
4289 and md.sr_instance_id = mfp1.sr_instance_id
4290 and nvl(md.op_seq_num,0) <> 0
4291 and mfp2.plan_id = mfp1.plan_id
4292 and mfp2.prev_pegging_id = mfp1.pegging_id
4293 and mfp2.demand_id = md.demand_id
4294 and ms.plan_id = mfp2.plan_id
4295 and ms.transaction_id = mfp2.transaction_id
4296 and mcp.plan_id = ms.plan_id
4297 and mcp.supply_id = ms.transaction_id
4298 and mcp.sr_instance_id = ms.sr_instance_id
4299 and mcp.demand_id = g_end_demand_id
4300 -- and mcp.routing_sequence_id is null
4301 and msi.plan_id = ms.plan_id
4302 and msi.organization_id = ms.organization_id
4303 and msi.sr_instance_id = ms.sr_instance_id
4304 and msi.inventory_item_id = ms.inventory_item_id
4305 and nvl(mcp.path_number,1) =
4306 decode(p_critical,0,0,nvl(mcp.path_number,1))
4307 ;
4308
4309 else -- not critical_only
4310 insert into msc_form_query
4311 (QUERY_ID,
4312 LAST_UPDATE_DATE,
4313 LAST_UPDATED_BY,
4314 CREATION_DATE,
4315 CREATED_BY,
4316 LAST_UPDATE_LOGIN,
4317 NUMBER1, -- supply_id
4318 NUMBER2, -- op_seq
4319 NUMBER3, -- tran_id
4320 NUMBER4, -- inst_id
4321 NUMBER5, -- org_id
4322 NUMBER6, -- item_id
4323 NUMBER7, --firm_type
4324 NUMBER8, -- status
4325 NUMBER9, -- applied
4326 NUMBER10, -- late_flag
4327 NUMBER11, -- qty
4328 CHAR10, -- order_number
4329 CHAR1, -- start date
4330 CHAR2, -- end date
4331 CHAR3, -- early start date
4332 CHAR4, -- early end date
4333 CHAR5, -- latest start date
4334 CHAR6, -- latest end date
4335 CHAR7, -- min start
4336 CHAR8, -- u early start date
4337 CHAR9, -- u early end date
4338 NUMBER12, -- critical_flag
4339 NUMBER13, -- supply type
4340 NUMBER14, -- item_id
4341 NUMBER15) -- supplier_id
4342 select distinct
4343 p_query_id,
4344 trunc(sysdate),
4345 -1,
4346 trunc(sysdate),
4347 -1,
4348 -1,
4349 p_supply_id,
4350 decode(md.op_seq_num,1,p_first_op,md.op_seq_num),
4351 ms.transaction_id,
4352 ms.sr_instance_id,
4353 ms.organization_id,
4354 ms.inventory_item_id,
4355 nvl(ms.firm_planned_type,2),
4356 nvl(ms.status, 0),
4357 nvl(ms.applied,0),
4358 msc_get_gantt_data.isSupplyLate(ms.plan_id,ms.sr_instance_id,
4359 ms.organization_id,ms.inventory_item_id,ms.transaction_id),
4360 ms.new_order_quantity,
4361 msc_get_gantt_data.order_number(ms.order_type,ms.order_number,
4362 ms.plan_id, ms.sr_instance_id,
4363 ms.transaction_id, ms.disposition_id),
4364 msc_get_gantt_data.actualStartDate(ms.order_type,
4365 msi.planning_make_buy_code,
4366 ms.organization_id,
4367 ms.source_organization_id,
4368 ms.new_dock_date,
4369 ms.new_wip_start_date,
4370 ms.new_ship_date,
4371 ms.new_schedule_date),
4372 nvl(to_char(ms.new_schedule_date,format_mask),'null'),
4373 decode(g_end_demand_id, null, 'null',
4374 nvl(to_char(ms.EARLIEST_START_DATE,format_mask),'null')),
4375 decode(g_end_demand_id, null, 'null',
4376 nvl(to_char(ms.EARLIEST_COMPLETION_DATE,format_mask),'null')),
4377 decode(g_end_demand_id, null, 'null',
4378 nvl(to_char(ms.ULPSD,format_mask),'null')),
4379 decode(g_end_demand_id, null, 'null',
4380 nvl(to_char(ms.ULPCD,format_mask),'null')),
4381 decode(g_end_demand_id, null, 'null',
4382 nvl(to_char(ms.MIN_START_DATE,format_mask), 'null')),
4383 decode(g_end_demand_id, null, 'null',
4384 nvl(to_char(ms.UEPSD,format_mask),'null')),
4385 decode(g_end_demand_id, null, 'null',
4386 nvl(to_char(ms.UEPCD,format_mask),'null')),
4387 decode(g_end_demand_id, null, 0,
4388 msc_get_gantt_data.isCriticalSupply(p_plan_id,g_end_demand_id,
4389 ms.transaction_id, ms.sr_instance_id)),
4390 msc_get_gantt_data.supplyType(ms.order_type,
4391 msi.planning_make_buy_code,
4392 ms.organization_id,
4393 ms.source_organization_id),
4394 ms.inventory_item_id,
4395 nvl(ms.supplier_id,-1)
4396 from msc_full_pegging mfp1,
4397 msc_full_pegging mfp2,
4398 msc_system_items msi,
4399 msc_supplies ms,
4400 msc_demands md,
4401 msc_form_query mfq
4402 where mfp1.plan_id = p_plan_id
4403 and mfp1.transaction_id = p_supply_id
4404 and mfp1.end_pegging_id = mfq.number1
4405 and mfq.query_id = p_end_peg_query_id
4406 and md.plan_id = mfp1.plan_id
4407 and md.disposition_id = mfp1.transaction_id
4408 and md.sr_instance_id = mfp1.sr_instance_id
4409 and nvl(md.op_seq_num,0) <> 0
4410 and mfp2.plan_id = mfp1.plan_id
4411 and mfp2.prev_pegging_id = mfp1.pegging_id
4412 and mfp2.demand_id = md.demand_id
4413 and ms.plan_id = mfp2.plan_id
4414 and ms.transaction_id = mfp2.transaction_id
4415 and msi.plan_id = ms.plan_id
4416 and msi.organization_id = ms.organization_id
4417 and msi.sr_instance_id = ms.sr_instance_id
4418 and msi.inventory_item_id = ms.inventory_item_id;
4419
4420 end if;
4421
4422 -- if op exists in msc_demand but not in msc_resource_requirements,
4423 -- show the op in the closest next op or prev op
4424
4425 begin
4426 select distinct mfq.number2, mfq.number2
4427 bulk collect into v_op, v_new_op
4428 from msc_form_query mfq
4429 where mfq.query_id = p_query_id
4430 and mfq.number1 = p_supply_id
4431 and mfq.number2 not in (
4432 select mfq_mrr.number2
4433 from msc_form_query mfq_mrr
4434 where mfq_mrr.query_id = p_op_seq_query_id
4435 and mfq_mrr.number1 = p_supply_id);
4436 for a in 1 .. v_op.count loop
4437 -- find the closest next op
4438 select min(number2)
4439 into v_dummy
4440 from msc_form_query
4441 where query_id = p_op_seq_query_id
4442 and number1 = p_supply_id
4443 and number2 > v_op(a);
4444 if v_dummy is null then
4445 -- if not found, find the closest prev op
4446 select max(number2)
4447 into v_dummy
4448 from msc_form_query
4449 where query_id = p_op_seq_query_id
4450 and number1 = p_supply_id
4451 and number2 < v_op(a);
4452 end if;
4453 v_new_op(a) := v_dummy;
4454 end loop;
4455
4456 forall a in 1.. v_op.count
4457 update msc_form_query
4458 set number2= v_new_op(a)
4459 where query_id = p_query_id
4460 and number1 = p_supply_id
4461 and number2 = v_op(a);
4462
4463 exception when no_data_found then
4464 null;
4465 end;
4466
4467 -- get its children which is not its component
4468 -- only get the children which are in the same pegging tree
4469
4470 OPEN peg_data_cur;
4471 LOOP
4472 FETCH peg_data_cur INTO v_org_id,
4473 v_transaction_id,
4474 v_instance_id,
4475 v_name,
4476 v_firm_flag,
4477 v_status,
4478 v_applied,
4479 v_late_flag,
4480 v_start_date,
4481 v_end_date,
4482 v_early_start_date,
4483 v_early_end_date,
4484 v_latest_start_date,
4485 v_latest_end_date,
4486 v_u_early_start_date,
4487 v_u_early_end_date,
4488 v_min_start_date,
4489 v_critical_flag,
4490 v_supply_type,
4491 v_org_code,
4492 v_item_id,
4493 v_supplier_id;
4494 EXIT WHEN peg_data_cur%NOTFOUND;
4495 if (p_critical =0 and v_critical_flag = 0) or -- critical path 0
4496 (p_critical =1 and v_critical_flag >= 0) or -- all critical path
4497 (p_critical = -1) then -- all path
4498 peg_data.org_id(i) := v_org_id;
4499 peg_data.transaction_id(i) := v_transaction_id;
4500 peg_data.instance_id(i) := v_instance_id;
4501 peg_data.name(i) := v_name;
4502 peg_data.firm_flag(i) := v_firm_flag;
4503 peg_data.status(i) := v_status;
4504 peg_data.applied(i) := v_applied;
4505 peg_data.late_flag(i) := v_late_flag;
4506 peg_data.start_date(i) := v_start_date;
4507 peg_data.end_date(i) := v_end_date;
4508 peg_data.early_start_date(i) := v_early_start_date;
4509 peg_data.early_end_date(i) := v_early_end_date;
4510 peg_data.latest_start_date(i) := v_latest_start_date;
4511 peg_data.latest_end_date(i) := v_latest_end_date;
4512 peg_data.u_early_start_date(i) := v_u_early_start_date;
4513 peg_data.u_early_end_date(i) := v_u_early_end_date;
4514 peg_data.min_start_date(i) := v_min_start_date;
4515 peg_data.critical_flag(i) := v_critical_flag;
4516 peg_data.supply_type(i) := v_supply_type;
4517 peg_data.res_firm_flag(i) := p_end_supply_id; -- end supply id
4518 peg_data.parent_index(i) := current_index;
4519 peg_data.next_record(i) := -1;
4520 peg_data.type(i) := JOB_NODE;
4521 peg_data.path(i) := peg_data.path(current_index)||
4522 '-'||to_char(child_index);
4523 peg_data.new_path(i) := peg_data.path(i);
4524 if next_row > 0 then
4525 peg_data.next_record(i-1) := i;
4526 end if;
4527 if next_row=-1 then
4528 next_row :=i;
4529 end if;
4530 i := i+1;
4531 child_index := child_index +1;
4532
4533 if v_supplier_id <> -1 and
4534 g_supplier_query_id is not null then
4535 -- for supplier list
4536 insert into msc_form_query
4537 (QUERY_ID,
4538 LAST_UPDATE_DATE,
4539 LAST_UPDATED_BY,
4540 CREATION_DATE,
4541 CREATED_BY,
4542 LAST_UPDATE_LOGIN,
4543 NUMBER1,
4544 NUMBER2,
4545 NUMBER3,
4546 NUMBER4,
4547 char1)
4548 values (
4549 g_supplier_query_id,
4550 sysdate,
4551 -1,
4552 sysdate,
4553 -1,
4554 -1,
4555 v_supplier_id,
4556 v_org_id,
4557 v_instance_id,
4558 v_item_id,
4559 v_org_code);
4560 end if;
4561
4562 end if;
4563 END LOOP;
4564 CLOSE peg_data_cur;
4565
4566 -- if no child and no operations, should be buy part
4567 if next_row = -1 and
4568 g_end_demand_id is null then
4569 OPEN date_cur;
4570 FETCH date_cur INTO peg_data.start_date(current_index),
4571 peg_data.end_date(current_index),
4572 peg_data.firm_flag(current_index);
4573 CLOSE date_cur;
4574 peg_data.type(current_index) := END_JOB_NODE;
4575 end if;
4576
4577
4578 elsif peg_data.type(current_index) = OP_NODE then
4579 p_op_seq := peg_data.op_seq(current_index);
4580 if g_end_demand_id is null then
4581 p_end_supply_id :=
4582 peg_data.res_firm_flag(current_index); -- end supply tran id
4583 else
4584 p_end_supply_id := null;
4585 end if;
4586 -- get the children
4587 OPEN job_data_cur(p_op_seq);
4588 LOOP
4589 FETCH job_data_cur INTO peg_data.transaction_id(i),
4590 peg_data.instance_id(i),
4591 peg_data.org_id(i),
4592 peg_data.name(i),
4593 peg_data.firm_flag(i),
4594 peg_data.status(i),
4595 peg_data.applied(i),
4596 peg_data.late_flag(i),
4597 peg_data.start_date(i),
4598 peg_data.end_date(i),
4599 peg_data.early_start_date(i),
4600 peg_data.early_end_date(i),
4601 peg_data.latest_start_date(i),
4602 peg_data.latest_end_date(i),
4603 peg_data.min_start_date(i),
4604 peg_data.u_early_start_date(i),
4605 peg_data.u_early_end_date(i),
4606 peg_data.critical_flag(i),
4607 peg_data.supply_type(i),
4608 v_org_code,
4609 v_item_id,
4610 v_supplier_id;
4611 EXIT WHEN job_data_cur%NOTFOUND;
4612
4613 peg_data.res_firm_flag(i) := p_end_supply_id; -- store end supply id
4614 peg_data.parent_index(i) := current_index;
4615 peg_data.next_record(i) := -1;
4616 peg_data.type(i) := JOB_NODE;
4617 peg_data.path(i) := peg_data.path(current_index)||
4618 '-'||to_char(child_index);
4619 peg_data.new_path(i) := peg_data.path(i);
4620 if next_row > 0 then
4621 peg_data.next_record(i-1) := i;
4622 end if;
4623 if next_row=-1 then
4624 next_row :=i;
4625 end if;
4626
4627 if v_supplier_id <> -1 and
4628 g_supplier_query_id is not null then
4629 -- for supplier list
4630 insert into msc_form_query
4631 (QUERY_ID,
4632 LAST_UPDATE_DATE,
4633 LAST_UPDATED_BY,
4634 CREATION_DATE,
4635 CREATED_BY,
4636 LAST_UPDATE_LOGIN,
4637 NUMBER1,
4638 NUMBER2,
4639 NUMBER3,
4640 NUMBER4,
4641 char1)
4642 values (
4643 g_supplier_query_id,
4644 sysdate,
4645 -1,
4646 sysdate,
4647 -1,
4648 -1,
4649 v_supplier_id,
4650 peg_data.org_id(i),
4651 peg_data.instance_id(i),
4652 v_item_id,
4653 v_org_code);
4654 end if;
4655
4656 i := i+1;
4657 child_index := child_index +1;
4658
4659 END LOOP;
4660 CLOSE job_data_cur;
4661
4662 OPEN ops_data_cursor;
4663 LOOP
4664 FETCH ops_data_cursor INTO
4665 v_name,
4666 v_start_date,
4667 v_end_date,
4668 v_transaction_id,
4669 v_dept_id,
4670 v_res_id,
4671 v_status,
4672 v_applied,
4673 v_firm_flag,
4674 v_late_flag,
4678 v_latest_end_date,
4675 v_early_start_date,
4676 v_early_end_date,
4677 v_latest_start_date,
4679 v_u_early_start_date,
4680 v_u_early_end_date,
4681 v_min_start_date,
4682 v_critical_flag;
4683 EXIT WHEN ops_data_cursor%NOTFOUND;
4684
4685 if (p_critical = 0 and v_critical_flag = 0) or
4686 (p_critical = 1 and v_critical_flag >= 0) or
4687 (p_critical = -1 ) then
4688 peg_data.transaction_id(i) := v_transaction_id;
4689 peg_data.department_id(i) := v_dept_id;
4690 peg_data.name(i) := v_name;
4691 peg_data.res_firm_flag(i) := v_firm_flag;
4692 if peg_data.res_firm_flag(i) >= 8 then
4693 peg_data.res_firm_flag(i) := 0;
4694 end if;
4695 peg_data.status(i) := v_status;
4696 peg_data.applied(i) := v_applied;
4697 peg_data.late_flag(i) := v_late_flag;
4698 peg_data.start_date(i) := v_start_date;
4699 peg_data.end_date(i) := v_end_date;
4700 peg_data.early_start_date(i) := v_early_start_date;
4701 peg_data.early_end_date(i) := v_early_end_date;
4702 peg_data.latest_start_date(i) := v_latest_start_date;
4703 peg_data.latest_end_date(i) := v_latest_end_date;
4704 peg_data.u_early_start_date(i) := v_u_early_start_date;
4705 peg_data.u_early_end_date(i) := v_u_early_end_date;
4706 peg_data.min_start_date(i) := v_min_start_date;
4707 peg_data.critical_flag(i) := v_critical_flag;
4708 peg_data.org_id(i) := p_org_id;
4709 peg_data.instance_id(i) := p_instance_id;
4710 peg_data.type(i) := RES_NODE;
4711 peg_data.parent_index(i) := current_index;
4712 peg_data.next_record(i) := -1;
4713 peg_data.path(i) := peg_data.path(current_index)||
4714 '-'||to_char(child_index);
4715 peg_data.new_path(i) := peg_data.path(i);
4716 i := i+1;
4717 child_index := child_index +1;
4718
4719 -- for resource centric view
4720 insert into msc_form_query
4721 (QUERY_ID,
4722 LAST_UPDATE_DATE,
4723 LAST_UPDATED_BY,
4724 CREATION_DATE,
4725 CREATED_BY,
4726 LAST_UPDATE_LOGIN,
4727 NUMBER1,
4728 NUMBER2,
4729 NUMBER3,
4730 NUMBER4)
4731 values (
4732 g_res_query_id,
4733 sysdate,
4734 -1,
4735 sysdate,
4736 -1,
4737 -1,
4738 p_instance_id,
4739 p_org_id,
4740 v_dept_id,
4741 v_res_id);
4742 end if;
4743 END LOOP;
4744 CLOSE ops_data_cursor;
4745 end if;
4746 if next_row =-1 then
4747 if peg_data.next_record(current_index) <> -1 then
4748 -- move to next record
4749 next_row := peg_data.next_record(current_index);
4750 elsif peg_data.next_record(current_index) = -1 and
4751 peg_data.parent_index(current_index) <> -1 then
4752 -- move to next parent
4753 parent_index := peg_data.parent_index(current_index);
4754 moreParent := true;
4755 while (moreParent) loop
4756 if peg_data.next_record(parent_index) <> -1 then
4757 next_row := peg_data.next_record(parent_index);
4758 moreParent := false;
4759 elsif peg_data.parent_index(parent_index) <> -1 then
4760 parent_index := peg_data.parent_index(parent_index);
4761 elsif peg_data.next_record(parent_index) = -1 and
4762 peg_data.parent_index(parent_index) = -1 then
4763 moreParent := false;
4764 hasMore := false;
4765 end if;
4766
4767 end loop;
4768 elsif peg_data.next_record(current_index) = -1 and
4769 peg_data.parent_index(current_index) = -1 then
4770 -- no more data
4771 hasMore := false;
4772 end if;
4773 end if;
4774 current_index := next_row;
4775 end loop;
4776
4777 END explode_children;
4778
4779 Procedure get_end_pegging(p_plan_id number) IS
4780 i number;
4781 TYPE char_arr IS TABLE OF varchar2(300);
4782 curr_org_id number_arr;
4783 curr_trans_id number_arr;
4784 curr_inst_id number_arr;
4785 curr_name char_arr;
4786 curr_end_pegging_id number_arr;
4787 curr_late_flag number_arr;
4788 curr_start_date char_arr;
4789 curr_end_date char_arr;
4790 curr_supply_type number_arr;
4791 v_current_block number;
4792
4793 CURSOR end_peg_cur IS
4794 select distinct ms.organization_id,
4795 ms.transaction_id, ms.sr_instance_id,
4796 msc_get_gantt_data.order_number(ms.order_type,ms.order_number,
4797 ms.plan_id, ms.sr_instance_id,
4798 ms.transaction_id, ms.disposition_id) ||' for '||
4799 msi.item_name ||
4800 ' in ' || mtp.organization_code,
4801 1, -- mfp1.pegging_id,
4802 msc_get_gantt_data.isSupplyLate(ms.plan_id,ms.sr_instance_id,
4803 ms.organization_id,ms.inventory_item_id,ms.transaction_id),
4804 msc_get_gantt_data.actualStartDate(ms.order_type,
4805 msi.planning_make_buy_code,
4806 ms.organization_id,
4807 ms.source_organization_id,
4808 ms.new_dock_date,
4809 ms.new_wip_start_date,
4810 ms.new_ship_date,
4811 ms.new_schedule_date),
4812 nvl(to_char(ms.new_schedule_date,format_mask),'null'),
4813 msc_get_gantt_data.supplyType(ms.order_type,
4814 msi.planning_make_buy_code,
4815 ms.organization_id,
4816 ms.source_organization_id)
4817 from msc_full_pegging mfp1,
4818 msc_full_pegging mfp2,
4819 msc_form_query mfq,
4820 msc_supplies ms,
4821 msc_system_items msi,
4822 msc_trading_partners mtp
4823 where mfp1.pegging_id = mfp2.end_pegging_id
4824 and mfp1.plan_id = mfp2.plan_id
4825 and mfp1.sr_instance_id = mfp2.sr_instance_id
4826 and mfp2.plan_id = p_plan_id
4827 and mfp2.transaction_id = mfq.number1
4828 and mfp2.sr_instance_id = mfq.number2
4829 and mfq.query_id = g_supply_query_id
4830 and msi.plan_id = ms.plan_id
4831 and msi.organization_id = ms.organization_id
4832 and msi.sr_instance_id = ms.sr_instance_id
4833 and msi.inventory_item_id = ms.inventory_item_id
4834 and mtp.partner_type =3
4835 and mtp.sr_tp_id = ms.organization_id
4836 and mtp.sr_instance_id = ms.sr_instance_id
4837 and ms.plan_id = mfp1.plan_id
4838 and ms.transaction_id = mfp1.transaction_id
4839 and ms.sr_instance_id = mfp1.sr_instance_id
4840 order by ms.transaction_id;
4841
4842 CURSOR peg_cur IS
4843 select distinct ms.organization_id,
4844 ms.transaction_id, ms.sr_instance_id,
4845 msc_get_gantt_data.order_number(ms.order_type,ms.order_number,
4846 ms.plan_id, ms.sr_instance_id,
4847 ms.transaction_id, ms.disposition_id)||' for '||
4848 msi.item_name ||
4849 ' in ' || mtp.organization_code,
4850 1, -- mfp1.pegging_id,
4851 msc_get_gantt_data.isSupplyLate(ms.plan_id,ms.sr_instance_id,
4852 ms.organization_id,ms.inventory_item_id,ms.transaction_id),
4853 msc_get_gantt_data.actualStartDate(ms.order_type,
4854 msi.planning_make_buy_code,
4855 ms.organization_id,
4856 ms.source_organization_id,
4857 ms.new_dock_date,
4858 ms.new_wip_start_date,
4859 ms.new_ship_date,
4860 ms.new_schedule_date),
4861 nvl(to_char(ms.new_schedule_date,format_mask),'null'),
4862 msc_get_gantt_data.supplyType(ms.order_type,
4863 msi.planning_make_buy_code,
4864 ms.organization_id,
4865 ms.source_organization_id)
4866 from msc_full_pegging mfp2,
4867 msc_form_query mfq,
4868 msc_supplies ms,
4869 msc_system_items msi,
4870 msc_trading_partners mtp
4871 where mfp2.plan_id = p_plan_id
4872 and mfp2.transaction_id = mfq.number1
4873 and mfp2.sr_instance_id = mfq.number2
4874 and mfq.query_id = g_supply_query_id
4875 and msi.plan_id = ms.plan_id
4876 and msi.organization_id = ms.organization_id
4877 and msi.sr_instance_id = ms.sr_instance_id
4878 and msi.inventory_item_id = ms.inventory_item_id
4879 and mtp.partner_type =3
4880 and mtp.sr_tp_id = ms.organization_id
4881 and mtp.sr_instance_id = ms.sr_instance_id
4882 and ms.plan_id = mfp2.plan_id
4883 and ms.transaction_id = mfp2.transaction_id
4884 and ms.sr_instance_id = mfp2.sr_instance_id
4885 order by ms.transaction_id;
4886
4887 p_found boolean;
4888
4889 --Limit Gantt Chart Orders View Hierarchy". Default = No [2]
4890 p_gantt_hier_prf varchar2(10) := nvl(fnd_profile.value('MSC_GANTT_ORDER_HIERARCHY'), 'N');
4891
4892 BEGIN
4893
4894 i :=the_index;
4895 g_has_prev_supply := false;
4896
4897 if i = 0 and g_current_block >= 2 then -- add prev node
4898 peg_data.org_id(i) := -1;
4899 peg_data.transaction_id(i) := -1;
4900 peg_data.instance_id(i) := -1;
4901 peg_data.name(i) := 'Previous '||g_supply_limit;
4902 peg_data.late_flag(i) := 0;
4903 peg_data.type(i) := PREV_NODE;
4904 peg_data.parent_index(i) := -1;
4905 peg_data.next_record(i) := -1;
4906 peg_data.path(i) := to_char(i);
4907 g_has_prev_supply := true;
4908 i := i +1;
4909 end if;
4910 v_current_block := 1;
4911 g_has_more_supply := false;
4912 p_found := false;
4913
4914 if ( p_gantt_hier_prf = 'Y' ) then
4915 OPEN peg_cur;
4916 else
4917 OPEN end_peg_cur;
4918 end if;
4919
4920 LOOP
4921 if ( p_gantt_hier_prf = 'Y' ) then
4922 FETCH peg_cur bulk collect into
4923 curr_org_id, curr_trans_id,
4924 curr_inst_id, curr_name,
4925 curr_end_pegging_id,
4926 curr_late_flag,
4927 curr_start_date,
4928 curr_end_date,
4929 curr_supply_type LIMIT g_supply_limit;
4930 EXIT WHEN peg_cur%NOTFOUND or v_current_block > g_current_block +1;
4931 else
4932 FETCH end_peg_cur bulk collect into
4933 curr_org_id, curr_trans_id,
4934 curr_inst_id, curr_name,
4935 curr_end_pegging_id,
4936 curr_late_flag,
4937 curr_start_date,
4938 curr_end_date,
4939 curr_supply_type LIMIT g_supply_limit;
4940 EXIT WHEN end_peg_cur%NOTFOUND or v_current_block > g_current_block +1;
4941 end if;
4942
4943 if v_current_block = g_current_block then
4944 For a in 1.. curr_org_id.count loop
4945 peg_data.org_id(i) := curr_org_id(a);
4946 peg_data.transaction_id(i) := curr_trans_id(a);
4947 peg_data.instance_id(i) := curr_inst_id(a);
4948 peg_data.name(i) := curr_name(a);
4949 peg_data.late_flag(i) := curr_late_flag(a);
4950 peg_data.start_date(i) := curr_start_date(a);
4951 peg_data.end_date(i) := curr_end_date(a);
4952 peg_data.supply_type(i) := curr_supply_type(a);
4953 peg_data.type(i) := JOB_NODE;
4954 peg_data.res_firm_flag(i) := curr_trans_id(a); -- end supply trans id
4955 peg_data.parent_index(i) := -1;
4956 peg_data.next_record(i) := -1;
4957 peg_data.path(i) := to_char(i);
4958 peg_data.new_path(i) := peg_data.path(i);
4959 if i>0 then
4960 peg_data.next_record(i-1) := i;
4961 end if;
4962 i := i+1;
4963 p_found := true;
4964 END LOOP;
4965 the_index := i;
4966 end if;
4967 v_current_block := v_current_block +1;
4968 END LOOP;
4969
4970 if ( p_gantt_hier_prf = 'Y' ) then
4971 CLOSE peg_cur;
4972 else
4973 CLOSE end_peg_cur;
4974 end if;
4975
4979 peg_data.transaction_id(i) := curr_trans_id(a);
4976 if not(p_found) then -- last block
4977 For a in 1.. curr_org_id.count loop
4978 peg_data.org_id(i) := curr_org_id(a);
4980 peg_data.instance_id(i) := curr_inst_id(a);
4981 peg_data.name(i) := curr_name(a);
4982 peg_data.late_flag(i) := curr_late_flag(a);
4983 peg_data.start_date(i) := curr_start_date(a);
4984 peg_data.end_date(i) := curr_end_date(a);
4985 peg_data.supply_type(i) := curr_supply_type(a);
4986 peg_data.type(i) := JOB_NODE;
4987 peg_data.res_firm_flag(i) := curr_trans_id(a); -- end supply trans id
4988 peg_data.parent_index(i) := -1;
4989 peg_data.next_record(i) := -1;
4990 peg_data.path(i) := to_char(i);
4991 peg_data.new_path(i) := peg_data.path(i);
4992 if i>0 then
4993 peg_data.next_record(i-1) := i;
4994 end if;
4995 i := i+1;
4996 END LOOP;
4997 the_index := i;
4998 elsif curr_org_id.count > 0 then
4999 g_has_more_supply := true;
5000 end if;
5001 END get_end_pegging;
5002
5003 Procedure fetchSupplyData(p_plan_id number, p_supply_list varchar2,
5004 p_fetch_type varchar2 default null) IS
5005 v_transaction_id number;
5006 v_instance_id number;
5007 v_len number;
5008 one_record varchar2(100);
5009 i number:=1;
5010
5011
5012 BEGIN
5013
5014 -- the format of supply_list is
5015 -- (instance_id, transaction_id),(ins_id, transaction_id)
5016
5017 msc_get_gantt_data.init;
5018
5019 if g_current_block is null then
5020 g_current_block := 1;
5021 end if;
5022
5023 if p_fetch_type is null then
5024 g_current_block := 1;
5025 elsif p_fetch_type = 'PREV' then
5026 g_current_block := g_current_block-1;
5027 elsif p_fetch_type = 'NEXT' then
5028 g_current_block := g_current_block+1;
5029 end if;
5030
5031 if p_fetch_type is null then
5032 if g_supply_query_id is not null then
5033 delete msc_form_query
5034 where query_id = g_supply_query_id;
5035 else
5036 select msc_form_query_s.nextval
5037 into g_supply_query_id
5038 from dual;
5039 end if;
5040
5041 if g_res_query_id is not null then
5042 delete msc_form_query
5043 where query_id = g_res_query_id;
5044 else
5045 select msc_form_query_s.nextval
5046 into g_res_query_id
5047 from dual;
5048 end if;
5049
5050 g_supplier_query_id := null;
5051
5052 v_len := length(p_supply_list);
5053 while v_len > 0 LOOP
5054 one_record :=
5055 substr(p_supply_list,instr(p_supply_list,'(',1,i)+1,
5056 instr(p_supply_list,')',1,i)-instr(p_supply_list,'(',1,i)-1);
5057 v_instance_id := to_number(substr(one_record,1,instr(one_record,',')-1));
5058 v_transaction_id := to_number(substr(one_record,instr(one_record,',')+1));
5059
5060 insert into msc_form_query
5061 (QUERY_ID,
5062 LAST_UPDATE_DATE,
5063 LAST_UPDATED_BY,
5064 CREATION_DATE,
5065 CREATED_BY,
5066 LAST_UPDATE_LOGIN,
5067 NUMBER1,
5068 NUMBER2)
5069 values (
5070 g_supply_query_id,
5071 sysdate,
5072 -1,
5073 sysdate,
5074 -1,
5075 -1,
5076 v_transaction_id,
5077 v_instance_id);
5078 i := i+1;
5079 v_len := v_len - length(one_record)-3;
5080
5081 END LOOP;
5082 end if; -- if p_patch_type is null
5083
5084 get_end_pegging(p_plan_id);
5085 msc_get_gantt_data.explode_children(p_plan_id);
5086
5087 END fetchSupplyData;
5088
5089 Procedure get_property(p_plan_id number, p_instance_id number,
5090 p_transaction_id number, p_type number,
5091 v_pro out NOCOPY varchar2, v_demand out NOCOPY varchar2)
5092 IS
5093
5094 CURSOR job_cur IS
5095 SELECT msc_get_name.item_name(ms.inventory_item_id,null,null,null) item,
5096 ms.new_order_quantity qty,
5097 nvl(to_char(ms.firm_date,format_mask), ' ') firm_date,
5098 to_char(ms.new_schedule_date,format_mask) sugg_due_date,
5099 nvl(to_char(ms.need_by_date,format_mask), ' ') needby,
5100 nvl(ms.unit_number,'null') unit_number,
5101 nvl(msc_get_name.project(ms.project_id,
5102 ms.organization_id,
5103 ms.plan_id,
5104 ms.sr_instance_id), 'null') project,
5105 nvl(msc_get_name.task( ms.task_id,
5106 ms.project_id,
5107 ms.organization_id,
5108 ms.plan_id,
5109 ms.sr_instance_id),'null') task,
5110 msc_get_name.org_code(ms.organization_id, ms.sr_instance_id) org,
5111 msc_get_gantt_data.order_number(ms.order_type,ms.order_number,
5112 ms.plan_id, ms.sr_instance_id,
5113 ms.transaction_id, ms.disposition_id) job_name,
5114 ms.firm_planned_type,
5115 nvl(ms.alternate_bom_designator, 'null') alternate_bom_designator,
5116 nvl(ms.alternate_routing_designator, 'null')
5117 alternate_routing_designator,
5118 ms.organization_id org_id,
5122 msi.planning_make_buy_code,
5119 nvl(to_char(msi.planning_time_fence_date, format_mask),' ') time_fence,
5120 msc_get_name.supply_type(ms.transaction_id, p_plan_id) supply_type,
5121 decode(msc_get_gantt_data.supplyType(ms.order_type,
5123 ms.organization_id,
5124 ms.source_organization_id),
5125 BUY_SUPPLY, g_buy_text,
5126 TRANSFER_SUPPLY, g_transfer_text,
5127 MAKE_SUPPLY, g_make_text) item_type,
5128 msi.description,
5129 nvl(msc_get_name.supplier(
5130 nvl(ms.source_supplier_id, ms.supplier_id)),'-1') supplier,
5131 nvl(msc_get_name.org_code(ms.source_organization_id,
5132 ms.source_sr_instance_id),'-1') source_org,
5133 nvl(ms.ship_method, '-1') ship_method,
5134 msc_get_name.lookup_meaning('SYS_YES_NO',
5135 decode(ms.supply_is_shared,1,1,2)) share_supply,
5136 nvl(to_char(ms.EARLIEST_START_DATE,format_mask),'null') EPSD,
5137 nvl(to_char(ms.EARLIEST_COMPLETION_DATE,format_mask),'null') EPCD,
5138 nvl(to_char(ms.UEPSD,format_mask),'null') UEPSD,
5139 nvl(to_char(ms.UEPCD,format_mask),'null') UEPCD,
5140 nvl(to_char(ms.ULPSD,format_mask),'null') ULPSD,
5141 nvl(to_char(ms.ULPCD,format_mask),'null') ULPCD
5142 FROM msc_supplies ms,
5143 msc_system_items msi
5144 WHERE ms.plan_id = p_plan_id
5145 AND ms.transaction_id = p_transaction_id
5146 and ms.sr_instance_id = p_instance_id
5147 and msi.plan_id = ms.plan_id
5148 and msi.organization_id = ms.organization_id
5149 and msi.sr_instance_id = ms.sr_instance_id
5150 and msi.inventory_item_id = ms.inventory_item_id;
5151
5152 CURSOR res_cur IS
5153 SELECT msc_get_name.item_name(mrr.assembly_item_id,null,null,null) item,
5154 nvl(mrr.operation_seq_num,0) op_seq,
5155 msc_get_name.org_code(mrr.organization_id, mrr.sr_instance_id) org,
5156 msc_get_name.department_code(decode(mrr.resource_id, -1, 1,2),
5157 mrr.department_id, mrr.organization_id,
5158 mrr.plan_id, mrr.sr_instance_id) dept_code,
5159 nvl(msc_get_name.job_name(mrr.supply_id, p_plan_id),
5160 to_char(mrr.supply_id)) job_name,
5161 nvl(mrr.assigned_units,0) assigned_units,
5162 msc_get_name.lookup_meaning('RESOURCE_FIRM_TYPE',
5163 nvl(mrr.firm_flag,0)) firm_flag,
5164 nvl(mrr.alternate_num,0) alternate_num,
5165 nvl(mrr.resource_seq_num,0) res_seq_num,
5166 nvl(msc_get_name.resource_code(mrr.resource_id,
5167 mrr.department_id, mrr.organization_id,
5168 mrr.plan_id, mrr.sr_instance_id)
5169 , 'null') res_code,
5170 nvl(mrr.resource_hours,0) resource_hours,
5171 ms.organization_id org_id,
5172 ms.transaction_id trans_id,
5173 0 mtq_time, -- get_MTQ_time(p_transaction_id, p_plan_id, p_instance_id) mtq_time,
5174 nvl(mdr.batchable_flag,2) batchable,
5175 nvl(mrr.batch_number, -1) batch_number,
5176 nvl(mdr.unit_of_measure,'-1') uom,
5177 nvl(decode(mrr.basis_type, null, '-1',
5178 msc_get_name.lookup_meaning(
5179 'MSC_RES_BASIS_TYPE',mrr.basis_type)),'-1') basis_type,
5180 nvl(decode(mrr.schedule_flag, null, '-1',
5181 msc_get_name.lookup_meaning(
5182 'BOM_RESOURCE_SCHEDULE_TYPE',mrr.schedule_flag)),'-1') schedule_flag,
5183 nvl(to_char(mrr.EARLIEST_START_DATE,format_mask),'null') EPSD,
5184 nvl(to_char(mrr.EARLIEST_COMPLETION_DATE,format_mask),'null') EPCD,
5185 nvl(to_char(mrr.UEPSD,format_mask),'null') UEPSD,
5186 nvl(to_char(mrr.UEPCD,format_mask),'null') UEPCD,
5187 nvl(to_char(mrr.ULPSD,format_mask),'null') ULPSD,
5188 nvl(to_char(mrr.ULPCD,format_mask),'null') ULPCD
5189 FROM msc_resource_requirements mrr,
5190 msc_supplies ms,
5191 msc_department_resources mdr
5192 WHERE mrr.plan_id = p_plan_id
5193 AND mrr.transaction_id = p_transaction_id
5194 and mrr.sr_instance_id = p_instance_id
5195 and ms.sr_instance_id = mrr.sr_instance_id
5196 and ms.plan_id = p_plan_id
5197 and ms.transaction_id = mrr.supply_id
5198 AND mdr.plan_id = mrr.plan_id
5199 AND mdr.organization_id = mrr.organization_id
5200 AND mdr.sr_instance_id = mrr.sr_instance_id
5201 AND mdr.department_id = mrr.department_id
5202 AND mdr.resource_id = mrr.resource_id
5203 ;
5204
5205 job_cur_rec job_cur%ROWTYPE;
5206 res_cur_rec res_cur%ROWTYPE;
5207
5208 p_end_peg_id number;
5209
5210 cursor alloc_cur is
5211 select sum(decode(mfp.demand_id, -1, mfp.allocated_quantity, 0)),
5212 sum(mfp.allocated_quantity)
5213 from msc_full_pegging mfp
5214 where mfp.plan_id = p_plan_id
5215 AND mfp.transaction_id = p_transaction_id
5216 and mfp.end_pegging_id = p_end_peg_id;
5217
5218 v_end_peg_id number_arr;
5219 a number;
5220 v_qty1 number;
5221 v_qty2 number;
5222 v_excess_qty number :=0;
5223 v_alloc_qty number :=0;
5224 BEGIN
5225 if p_type in (JOB_NODE, END_JOB_NODE) then
5226 -- calculate alloc_qty, excess_qty, and short_qty
5227
5228 select mfp.end_pegging_id
5229 bulk collect into v_end_peg_id
5230 from msc_full_pegging mfp
5231 where mfp.plan_id = p_plan_id
5232 and mfp.demand_id = g_end_demand_id
5233 and mfp.pegging_id = mfp.end_pegging_id;
5234 for a in 1..v_end_peg_id.count loop
5235 p_end_peg_id := v_end_peg_id(a);
5236 v_qty1 :=0;
5237 v_qty2 :=0;
5238 open alloc_cur;
5239 fetch alloc_cur into v_qty1,v_qty2;
5240 close alloc_cur;
5241 v_excess_qty := v_excess_qty + nvl(v_qty1,0);
5242 v_alloc_qty := v_alloc_qty + nvl(v_qty2,0);
5243 end loop;
5244
5245 OPEN job_cur;
5246 FETCH job_cur INTO job_cur_rec;
5247 CLOSE job_cur;
5248 v_pro := replace_seperator(job_cur_rec.item) || field_seperator ||
5249 job_cur_rec.qty || field_seperator ||
5250 job_cur_rec.firm_date || field_seperator ||
5251 job_cur_rec.sugg_due_date || field_seperator ||
5252 job_cur_rec.needby || field_seperator ||
5253 job_cur_rec.unit_number || field_seperator ||
5254 job_cur_rec.project || field_seperator ||
5255 job_cur_rec.task || field_seperator ||
5256 replace_seperator(job_cur_rec.org) || field_seperator ||
5257 replace_seperator(job_cur_rec.job_name) || field_seperator ||
5258 job_cur_rec.firm_planned_type || field_seperator ||
5259 job_cur_rec.alternate_bom_designator || field_seperator ||
5260 job_cur_rec.alternate_routing_designator || field_seperator ||
5261 job_cur_rec.time_fence || field_seperator ||
5262 nvl(job_cur_rec.supply_type, ' ')||field_seperator ||
5263 job_cur_rec.item_type || field_seperator ||
5264 replace_seperator(job_cur_rec.description)|| field_seperator ||
5265 nvl(v_alloc_qty,'-1') || field_seperator ||
5266 nvl(v_excess_qty,'-1')|| field_seperator ||
5267 replace_seperator(job_cur_rec.supplier) || field_seperator ||
5268 replace_seperator(job_cur_rec.source_org) || field_seperator ||
5269 replace_seperator(job_cur_rec.ship_method)|| field_seperator ||
5270 job_cur_rec.share_supply || field_seperator ||
5271 job_cur_rec.EPSD || field_seperator ||
5272 job_cur_rec.EPCD || field_seperator ||
5273 job_cur_rec.UEPSD || field_seperator ||
5274 job_cur_rec.UEPCD || field_seperator ||
5275 job_cur_rec.ULPSD || field_seperator ||
5276 job_cur_rec.ULPCD ;
5277
5278 fetchDemandData(p_plan_id, p_instance_id, p_transaction_id,
5279 job_cur_rec.org_id, v_demand);
5280 elsif p_type = RES_NODE then
5281 OPEN res_cur;
5282 FETCH res_cur INTO res_cur_rec;
5283 CLOSE res_cur;
5284 v_pro := replace_seperator(res_cur_rec.item) || field_seperator ||
5285 res_cur_rec.op_seq || field_seperator ||
5286 replace_seperator(res_cur_rec.org) || field_seperator ||
5287 replace_seperator(res_cur_rec.dept_code) || field_seperator ||
5288 replace_seperator(res_cur_rec.job_name) || field_seperator ||
5289 res_cur_rec.assigned_units || field_seperator ||
5290 res_cur_rec.firm_flag || field_seperator ||
5291 res_cur_rec.alternate_num || field_seperator ||
5292 res_cur_rec.res_seq_num || field_seperator ||
5293 replace_seperator(res_cur_rec.res_code) || field_seperator ||
5294 res_cur_rec.resource_hours || field_seperator ||
5295 res_cur_rec.mtq_time|| field_seperator ||
5296 res_cur_rec.batchable || field_seperator ||
5297 res_cur_rec.batch_number|| field_seperator ||
5298 res_cur_rec.uom|| field_seperator ||
5299 res_cur_rec.basis_type || field_seperator ||
5300 res_cur_rec.schedule_flag || field_seperator ||
5301 res_cur_rec.EPSD || field_seperator ||
5302 res_cur_rec.EPCD || field_seperator ||
5303 res_cur_rec.UEPSD || field_seperator ||
5304 res_cur_rec.UEPCD || field_seperator ||
5305 res_cur_rec.ULPSD || field_seperator ||
5306 res_cur_rec.ULPCD;
5307
5308 fetchDemandData(p_plan_id, p_instance_id, res_cur_rec.trans_id,
5309 res_cur_rec.org_id, v_demand);
5310 end if;
5311
5312 END get_property;
5313
5314 Procedure init IS
5315 BEGIN
5316
5317 peg_data.parent_index.delete;
5318 peg_data.next_record.delete;
5319 peg_data.org_id.delete;
5320 peg_data.transaction_id.delete;
5321 peg_data.instance_id.delete;
5322 peg_data.department_id.delete;
5323 peg_data.op_seq.delete;
5324 peg_data.type.delete;
5325 peg_data.path.delete;
5326 peg_data.name.delete;
5327 peg_data.firm_flag.delete;
5328 peg_data.start_date.delete;
5329 peg_data.end_date.delete;
5330 peg_data.status.delete;
5331 peg_data.applied.delete;
5332 peg_data.res_firm_flag.delete;
5333 peg_data.late_flag.delete;
5334 peg_data.early_start_date.delete;
5335 peg_data.early_end_date.delete;
5336 peg_data.u_early_start_date.delete;
5337 peg_data.u_early_end_date.delete;
5338 peg_data.latest_start_date.delete;
5339 peg_data.latest_end_date.delete;
5340 peg_data.min_start_date.delete;
5341 peg_data.critical_flag.delete;
5342 peg_data.supply_type.delete;
5343 peg_data.new_path.delete;
5344 the_index :=0;
5345 g_end_demand_id := null;
5346
5347 END init;
5348
5349
5350 Procedure fetchSupplierLoadData(p_plan_id number,
5351 p_supplier_list varchar2,
5352 p_start varchar2 default null,
5353 p_end varchar2 default null,
5354 v_require_data IN OUT NOCOPY maxCharTbl,
5355 v_avail_data IN OUT NOCOPY maxCharTbl) IS
5356 v_org_id number;
5357 v_instance_id number;
5358 v_item_id number;
5359 v_supplier_id number;
5360 v_len number;
5361 one_record varchar2(100);
5362 i number:=1;
5363 j number:=1;
5364 k number:=0;
5365 n number:=0;
5366 a number;
5367 b number;
5368 c number;
5369 oneBigRecord maxCharTbl := maxCharTbl(0);
5370 recCount number :=0;
5371 TYPE date_arr IS TABLE OF date;
5372 v_req_start date_arr := date_arr(sysdate);
5373 v_req_end date_arr:= date_arr(sysdate);
5374 v_req_qty number_arr:= number_arr(0);
5375 v_req_qty_unmet number_arr:= number_arr(0);
5376 v_avail_start date_arr:= date_arr(sysdate);
5377 v_avail_end date_arr:= date_arr(sysdate);
5378 v_avail_qty number_arr:= number_arr(0);
5379 v_max_len number;
5380 v_one_record varchar2(200);
5381 p_start_date date;
5382 p_end_date date;
5383 v_cum_qty number;
5384 v_bkt_start date_arr;
5385 v_bkt_end date_arr;
5386 v_start date_arr;
5387 v_end date_arr;
5388 v_qty number_arr;
5389 v_bkt_qty number;
5390 cursor start_date_cur is
5391 select nvl(trunc(mis.SUPPLIER_LEAD_TIME_DATE +1),
5392 trunc(mp.plan_start_date+2))
5393 from msc_item_suppliers mis,
5394 msc_plans mp
5395 where mis.plan_id = mp.plan_id
5396 and mis.inventory_item_id = v_item_id
5397 and mis.sr_instance_id = v_instance_id
5398 and mis.supplier_id = v_supplier_id
5399 and mis.organization_id = v_org_id
5400 and mp.plan_id = p_plan_id;
5401
5402 p_promise_date_profile number :=
5403 nvl(FND_PROFILE.Value('MSC_PO_DOCK_DATE_CALC_PREF'),1);
5404 v_lead_time_date date;
5405 BEGIN
5406
5407 p_start_date := to_date(p_start,format_mask);
5408 p_end_date := to_date(p_end,format_mask);
5409
5410 select mpb.bkt_start_date,mpb.bkt_end_date
5411 BULK COLLECT INTO v_bkt_start, v_bkt_end
5412 from msc_plan_buckets mpb,
5413 msc_plans mp
5414 where mp.plan_id =p_plan_id
5415 and mpb.plan_id = mp.plan_id
5416 and mpb.organization_id = mp.organization_id
5417 and mpb.sr_instance_id = mp.sr_instance_id
5418 and ( mpb.bkt_start_date between p_start_date and p_end_date
5419 or
5420 mpb.bkt_end_date between p_start_date and p_end_date )
5421 and mpb.bucket_type <> 1
5422 order by 1;
5423
5424 -- parse the supplier_list
5425 -- the format of supplier_list is
5426 -- (instance_id, org_id,item_id, supplier_id)
5427 v_len := length(p_supplier_list);
5428 while v_len > 0 LOOP
5429 one_record :=
5430 substr(p_supplier_list,instr(p_supplier_list,'(',1,i)+1,
5431 instr(p_supplier_list,')',1,i)-instr(p_supplier_list,'(',1,i)-1);
5432
5433 v_instance_id := to_number(substr(one_record,1,instr(one_record,',')-1));
5434
5435 v_org_id := to_number(substr(one_record,instr(one_record,',',1,1)+1,
5436 instr(one_record,',',1,2)-instr(one_record,',',1,1)-1));
5437
5438 v_item_id := to_number(substr(one_record,instr(one_record,',',1,2)+1
5439 ,instr(one_record,',',1,3)-instr(one_record,',',1,2)-1));
5440
5441 v_supplier_id := to_number(substr(one_record,instr(one_record,',',1,3)+1));
5442
5443 OPEN start_date_cur;
5444 FETCH start_date_cur INTO v_lead_time_date;
5445 CLOSE start_date_cur;
5446 select
5447 mca.calendar_date, mca.calendar_date+1, msc.capacity
5448 bulk collect into v_start, v_end, v_qty
5449 from msc_calendar_dates mca,
5450 msc_plans mp,
5451 msc_trading_partners mtp,
5452 msc_supplier_capacities msc
5453 where msc.plan_id = p_plan_id
5454 and msc.inventory_item_id = v_item_id
5455 and msc.sr_instance_id = v_instance_id
5456 and msc.supplier_id = v_supplier_id
5457 and msc.organization_id = v_org_id
5458 and msc.capacity > 0
5459 and msc.from_date <=p_end_date
5460 and msc.to_date >=v_lead_time_date
5461 and mp.plan_id = msc.plan_id
5462 and mtp.sr_tp_id = mp.organization_id
5463 and mtp.sr_instance_id = mp.sr_instance_id
5464 and mtp.partner_type =3
5465 and mca.sr_instance_id= mtp.sr_instance_id
5466 and mca.calendar_code = mtp.calendar_code
5467 and mca.exception_set_id = mtp.calendar_exception_set_id
5468 and mca.calendar_date between msc.from_date and msc.to_date
5469 and mca.seq_num is not null
5470 order by msc.transaction_id,msc.from_date, msc.to_date;
5471
5472 c :=1;
5473 -- daily bkt
5474 for a in 1 .. v_start.count loop
5475 if (v_bkt_start.count = 0 or
5476 v_end(a) <= v_bkt_start(1)) and
5477 v_start(a) >= v_lead_time_date then
5478 v_avail_start.extend;
5479 v_avail_end.extend;
5480 v_avail_qty.extend;
5481 v_avail_start(c) := v_start(a);
5482 v_avail_end(c) := v_end(a);
5483 v_avail_qty(c) := v_qty(a);
5484 c := c+1;
5485 end if;
5486 end loop;
5487
5488 -- weekly and period bkt
5489 for b in 1..v_bkt_start.count loop
5490 v_bkt_qty :=0;
5491 for a in 1 .. v_start.count loop
5492 if v_start(a) >= v_lead_time_date and
5493 v_start(a) >= v_bkt_start(b) and
5494 v_end(a) <= v_bkt_end(b) then
5495 v_bkt_qty := v_bkt_qty + v_qty(a);
5496 end if;
5497 end loop;
5498 if v_bkt_qty > 0 then
5499 v_avail_start.extend;
5500 v_avail_end.extend;
5501 v_avail_qty.extend;
5502 v_avail_start(c) := v_bkt_start(b);
5503 v_avail_end(c) := v_bkt_end(b);
5504 v_avail_qty(c) := v_bkt_qty;
5505 c := c+1;
5506 end if;
5507 end loop;
5508
5509 select trunc(ms.new_dock_date), trunc(ms.new_dock_date)+1,
5510 ms.new_order_quantity
5511 bulk collect into v_start, v_end, v_qty
5512 from msc_supplies ms
5513 where ms.plan_id = p_plan_id
5514 and ms.inventory_item_id = v_item_id
5515 and ms.sr_instance_id = v_instance_id
5516 and ms.supplier_id = v_supplier_id
5517 and ms.organization_id = v_org_id
5518 and ms.new_dock_date <= p_end_date
5519 and (ms.order_type <> 1 -- not for PO
5520 or
5521 (ms.order_type = 1 and
5522 ms.promised_date is null and
5523 p_promise_date_profile = 1)) -- promised_date
5524 order by ms.new_dock_date;
5525
5526 oneBigRecord.delete;
5527 oneBigRecord.extend;
5528 recCount :=0;
5529 j :=1;
5530 c :=0;
5531 -- daily bkt
5532 for a in 1 .. v_start.count loop
5533 if v_bkt_start.count = 0 or
5534 v_end(a) <= v_bkt_start(1) then
5535 v_req_start.extend;
5536 v_req_end.extend;
5537 v_req_qty.extend;
5538 v_req_qty_unmet.extend;
5539 v_req_start(a) := v_start(a);
5540 v_req_end(a) := v_end(a);
5541 v_req_qty(a) := v_qty(a);
5542 v_req_qty_unmet(a) := v_qty(a);
5543 c := a;
5544 end if;
5545 end loop;
5546 -- weekly and period bkt
5547 for b in 1..v_bkt_start.count loop
5548 v_bkt_qty :=0;
5549 for a in 1 .. v_start.count loop
5550
5551 if v_start(a) >= v_bkt_start(b) and
5552 v_end(a) <= v_bkt_end(b) then
5553 v_bkt_qty := v_bkt_qty + v_qty(a);
5554 end if;
5555 end loop;
5556 if v_bkt_qty > 0 then
5557 c := c+1;
5558 v_req_start.extend;
5559 v_req_end.extend;
5560 v_req_qty.extend;
5561 v_req_qty_unmet.extend;
5562 v_req_start(c) := v_bkt_start(b);
5563 v_req_end(c) := v_bkt_end(b);
5564 v_req_qty(c) := v_bkt_qty; --v_bkt_qty/(v_bkt_end(b) - v_bkt_start(b));
5565 v_req_qty_unmet(c) := v_req_qty(c);
5566 end if;
5567 end loop;
5568
5569 -- get the actual req
5570 for b in 1 .. v_req_start.count-1 loop
5571 if v_req_end(b) >= p_start_date then
5572 v_one_record :=
5573 to_char(v_req_start(b),format_mask) ||
5574 field_seperator ||
5575 to_char(v_req_end(b),format_mask) ||
5576 field_seperator ||
5577 v_req_qty(b);
5578 v_max_len := nvl(length(oneBigRecord(j)),0) +
5579 nvl(length(v_one_record),0);
5580 if v_max_len > 30000 then
5581 j := j+1;
5582 oneBigRecord.extend;
5583 end if;
5584
5585 oneBigRecord(j) := oneBigRecord(j) || field_seperator ||
5586 v_one_record;
5587 recCount := recCount+1;
5588 end if;
5589 end loop;
5590
5591
5592
5593 v_require_data.extend;
5594 k := k+1;
5595 if i = 1 then -- not the first record
5596 v_require_data(k) := to_char(i-1) || field_seperator ||
5597 recCount;
5598 else
5599 v_require_data(k) := record_seperator ||
5600 to_char(i-1) || field_seperator ||
5601 recCount;
5602 end if;
5603
5604 for j in 1 .. oneBigRecord.count loop
5605 if j = 1 then
5606 v_require_data(k) := v_require_data(k) || oneBigRecord(j);
5607 else
5608 v_require_data.extend;
5609 k := k+1;
5610 v_require_data(k) := oneBigRecord(j);
5611 end if;
5612 end loop;
5613
5614 j := 1;
5615 oneBigRecord.delete;
5616 oneBigRecord.extend;
5617 recCount :=0;
5618 v_cum_qty :=0;
5619
5620 -- calculate the net accumulative supplier capacity
5621 -- req will use the capacity which is before the req date, but not after
5622
5623 -- found the net accumulative avail qty
5624 for b in 1 .. v_avail_start.count loop
5625 v_cum_qty := v_cum_qty + v_avail_qty(b);
5626 for a in 1.. v_req_start.count-1 loop
5627 if v_avail_start(b) <= v_req_start(a) and
5628 v_req_qty_unmet(a) > 0 and
5629 v_cum_qty > 0 then
5630 if v_cum_qty >= v_req_qty_unmet(a) then
5631 v_cum_qty := v_cum_qty -v_req_qty_unmet(a);
5632 v_req_qty_unmet(a) := 0;
5633 else
5634 v_req_qty_unmet(a) := v_req_qty_unmet(a) - v_cum_qty;
5635 v_cum_qty := 0;
5636 end if;
5637
5638 end if;
5639 end loop; -- end of v_req_start loop
5640 v_avail_qty(b):= v_cum_qty;
5641 end loop; -- end of v_avail loop
5642
5643 -- pad up net avail qty with req met qty so it won't be overload in chart
5644 for b in 1 .. v_req_start.count loop
5645 if v_req_end(b) >= p_start_date and
5646 v_req_qty_unmet(b) < v_req_qty(b) then
5647 v_bkt_qty := v_req_qty(b)-v_req_qty_unmet(b);
5648 v_one_record :=
5649 to_char(v_req_start(b),format_mask) ||
5650 field_seperator ||
5651 to_char(v_req_end(b),format_mask) ||
5652 field_seperator ||
5653 v_bkt_qty;
5654
5655 v_max_len := nvl(length(oneBigRecord(j)),0) +
5656 nvl(length(v_one_record),0);
5657 if v_max_len > 30000 then
5658 j := j+1;
5659 oneBigRecord.extend;
5660 end if;
5661
5662 oneBigRecord(j) := oneBigRecord(j) || field_seperator ||
5663 v_one_record;
5664 recCount := recCount +1;
5665 end if;
5666 end loop;
5667
5668 for b in 1 .. v_avail_start.count loop
5669 if v_avail_end(b) >= p_start_date then
5670 v_one_record :=
5671 to_char(v_avail_start(b),format_mask) ||
5672 field_seperator ||
5673 to_char(v_avail_end(b),format_mask) ||
5674 field_seperator ||
5675 v_avail_qty(b);
5676
5677 v_max_len := nvl(length(oneBigRecord(j)),0) +
5678 nvl(length(v_one_record),0);
5679 if v_max_len > 30000 then
5680 j := j+1;
5681 oneBigRecord.extend;
5682 end if;
5683
5684 oneBigRecord(j) := oneBigRecord(j) || field_seperator ||
5685 v_one_record;
5686 recCount := recCount +1;
5687 end if;
5688 end loop;
5689 v_avail_data.extend;
5690 n := n+1;
5691 if i = 1 then -- not the first record
5692 v_avail_data(n) := to_char(i-1) || field_seperator ||
5693 recCount;
5694 else
5695 v_avail_data(n) := record_seperator ||
5696 to_char(i-1) || field_seperator ||
5697 recCount;
5698 end if;
5699
5700 for j in 1 .. oneBigRecord.count loop
5701 if j = 1 then
5702 v_avail_data(n) := v_avail_data(n) || oneBigRecord(j);
5703 else
5704 v_avail_data.extend;
5705 n := n+1;
5706 v_avail_data(n) := oneBigRecord(j);
5707 end if;
5708 end loop;
5709
5710
5711 v_req_start.delete;
5712 v_req_end.delete;
5713 v_req_qty.delete;
5714 v_req_qty_unmet.delete;
5715
5716 i := i+1;
5717 v_len := v_len - length(one_record)-3;
5718 END LOOP;
5719
5720 END fetchSupplierLoadData;
5721
5722 Procedure fetchLateDemandData(p_plan_id number, p_demand_id number,
5723 p_critical number default -1) IS
5724 CURSOR end_demand_cur IS
5725 select md.organization_id,
5726 md.demand_id, md.sr_instance_id,
5727 nvl(md.order_number,
5728 nvl(msc_get_name.designator(md.schedule_designator_id),
5729 md.demand_id)) ||' for '||
5730 mi.item_name ||
5731 ' in ' || mtp.organization_code ||'('||
5732 md.using_requirement_quantity||')',
5733 to_char(md.using_assembly_demand_date,format_mask),
5734 to_char(nvl(md.dmd_satisfied_date,md.using_assembly_demand_date),format_mask),
5735 demand_priority
5736 from msc_demands md,
5737 msc_items mi,
5738 msc_trading_partners mtp
5739 where md.demand_id = p_demand_id
5740 and md.plan_id = p_plan_id
5741 and mi.inventory_item_id = md.inventory_item_id
5742 and mtp.partner_type =3
5743 and mtp.sr_tp_id = md.organization_id
5744 and mtp.sr_instance_id = md.sr_instance_id;
5745
5746 CURSOR end_peg_cur IS
5747 select distinct ms.organization_id,
5748 ms.transaction_id, ms.sr_instance_id,
5749 msc_get_gantt_data.order_number(ms.order_type,ms.order_number,
5750 ms.plan_id, ms.sr_instance_id,
5751 ms.transaction_id, ms.disposition_id)||' for '||
5752 msi.item_name ||
5753 ' in ' || mtp.organization_code ||'('||
5754 ms.new_order_quantity||')',
5755 msc_get_gantt_data.isSupplyLate(ms.plan_id,ms.sr_instance_id,
5756 ms.organization_id,ms.inventory_item_id,ms.transaction_id),
5757 msc_get_gantt_data.actualStartDate(ms.order_type,
5758 msi.planning_make_buy_code,
5759 ms.organization_id,
5760 ms.source_organization_id,
5761 ms.new_dock_date,
5762 ms.new_wip_start_date,
5763 ms.new_ship_date,
5764 ms.new_schedule_date),
5765 nvl(to_char(ms.new_schedule_date,format_mask),'null'),
5766 nvl(to_char(ms.EARLIEST_START_DATE,format_mask),'null'),
5767 nvl(to_char(ms.EARLIEST_COMPLETION_DATE,format_mask),'null'),
5768 nvl(to_char(ms.ULPSD,format_mask),'null'),
5769 nvl(to_char(ms.ULPCD,format_mask),'null'),
5770 nvl(to_char(ms.UEPSD,format_mask),'null'),
5771 nvl(to_char(ms.UEPCD,format_mask),'null'),
5772 nvl(to_char(ms.MIN_START_DATE,format_mask), 'null'),
5773 msc_get_gantt_data.isCriticalSupply(p_plan_id,g_end_demand_id,
5774 ms.transaction_id, ms.sr_instance_id),
5775 msc_get_gantt_data.supplyType(ms.order_type,
5776 msi.planning_make_buy_code,
5777 ms.organization_id,
5778 ms.source_organization_id),
5779 mtp.organization_code ||':'||msi.item_name,
5780 ms.inventory_item_id,
5781 nvl(ms.supplier_id,-1)
5782 from msc_full_pegging mfp,
5783 msc_supplies ms,
5784 msc_system_items msi,
5785 msc_trading_partners mtp
5786 where mfp.demand_id = p_demand_id
5787 and mfp.plan_id = p_plan_id
5788 and msi.plan_id = ms.plan_id
5789 and msi.organization_id = ms.organization_id
5790 and msi.sr_instance_id = ms.sr_instance_id
5791 and msi.inventory_item_id = ms.inventory_item_id
5792 and mtp.partner_type =3
5793 and mtp.sr_tp_id = ms.organization_id
5794 and mtp.sr_instance_id = ms.sr_instance_id
5795 and ms.plan_id = mfp.plan_id
5796 and ms.transaction_id = mfp.transaction_id
5797 and ms.sr_instance_id = mfp.sr_instance_id
5798 order by ms.transaction_id;
5799
5800 i number;
5801 TYPE char_arr IS TABLE OF varchar2(300);
5802 curr_org_id number_arr;
5803 curr_trans_id number_arr;
5804 curr_inst_id number_arr;
5805 curr_name char_arr;
5806 curr_late_flag number_arr;
5807 curr_start_date char_arr;
5808 curr_end_date char_arr;
5809 curr_early_start_date char_arr;
5810 curr_early_end_date char_arr;
5811 curr_latest_start_date char_arr;
5812 curr_latest_end_date char_arr;
5813 curr_u_early_start_date char_arr;
5814 curr_u_early_end_date char_arr;
5815 curr_min_start_date char_arr;
5816 curr_critical_flag number_arr;
5817 curr_supply_type number_arr;
5818 curr_supplier_id number_arr;
5819 curr_item_id number_arr;
5820 curr_org_code char_arr;
5821 BEGIN
5822
5823 msc_get_gantt_data.init;
5824 g_end_demand_id := p_demand_id;
5825
5826 -- for order centric view
5827 if g_supply_query_id is not null then
5828 delete msc_form_query
5829 where query_id = g_supply_query_id;
5830 else
5831 select msc_form_query_s.nextval
5832 into g_supply_query_id
5833 from dual;
5834 end if;
5835 -- for resource centric view
5836 if g_res_query_id is not null then
5837 delete msc_form_query
5838 where query_id = g_res_query_id;
5839 else
5840 select msc_form_query_s.nextval
5841 into g_res_query_id
5842 from dual;
5843 end if;
5844 -- for supplier list
5845 if g_supplier_query_id is not null then
5846 delete msc_form_query
5847 where query_id = g_supplier_query_id;
5848 else
5849 select msc_form_query_s.nextval
5850 into g_supplier_query_id
5851 from dual;
5852 end if;
5853
5854 i :=the_index;
5855 OPEN end_demand_cur;
5856 FETCH end_demand_cur into peg_data.org_id(i),
5857 peg_data.transaction_id(i),
5858 peg_data.instance_id(i),
5859 peg_data.name(i),
5860 peg_data.start_date(i),
5861 peg_data.end_date(i),
5862 g_dmd_priority;
5863 CLOSE end_demand_cur;
5864 peg_data.type(i) := END_DEMAND_NODE;
5865 peg_data.parent_index(i) := -1;
5866 peg_data.next_record(i) := -1;
5867 peg_data.path(i) := to_char(i);
5868 peg_data.new_path(i) := peg_data.path(i);
5869 i := i+1;
5870 OPEN end_peg_cur;
5871 FETCH end_peg_cur bulk collect into
5872 curr_org_id, curr_trans_id,
5873 curr_inst_id, curr_name,
5874 curr_late_flag, curr_start_date,
5875 curr_end_date, curr_early_start_date,
5876 curr_early_end_date, curr_latest_start_date,
5877 curr_latest_end_date, curr_u_early_start_date,
5878 curr_u_early_end_date, curr_min_start_date,
5879 curr_critical_flag, curr_supply_type,
5880 curr_org_code, curr_item_id, curr_supplier_id;
5881 CLOSE end_peg_cur;
5882 For a in 1.. curr_org_id.count loop
5883 if (p_critical = 0 and curr_critical_flag(a)=0) or
5884 (p_critical = 1 and curr_critical_flag(a) >= 0) or
5885 (p_critical = -1) then
5886 peg_data.org_id(i) := curr_org_id(a);
5887 peg_data.transaction_id(i) := curr_trans_id(a);
5888 peg_data.instance_id(i) := curr_inst_id(a);
5889 peg_data.name(i) := curr_name(a);
5890 peg_data.late_flag(i) := curr_late_flag(a);
5891 peg_data.start_date(i) := curr_start_date(a);
5892 peg_data.end_date(i) := curr_end_date(a);
5893 peg_data.early_start_date(i) := curr_early_start_date(a);
5894 peg_data.early_end_date(i) := curr_early_end_date(a);
5895 peg_data.u_early_start_date(i) := curr_u_early_start_date(a);
5896 peg_data.u_early_end_date(i) := curr_u_early_end_date(a);
5897 peg_data.latest_start_date(i) := curr_latest_start_date(a);
5898 peg_data.latest_end_date(i) := curr_latest_end_date(a);
5899 peg_data.min_start_date(i) := curr_min_start_date(a);
5900 peg_data.critical_flag(i) := curr_critical_flag(a);
5901 peg_data.supply_type(i) := curr_supply_type(a);
5902 peg_data.type(i) := JOB_NODE;
5903 peg_data.parent_index(i) := -1;
5904 peg_data.next_record(i) := -1;
5905 peg_data.path(i) := '0-'||to_char(i-1);
5906 peg_data.new_path(i) := peg_data.path(i);
5907 if i>0 then
5908 peg_data.next_record(i-1) := i;
5909 end if;
5910 i := i+1;
5911
5912 -- for order centric view
5913
5914 insert into msc_form_query
5915 (QUERY_ID,
5916 LAST_UPDATE_DATE,
5917 LAST_UPDATED_BY,
5918 CREATION_DATE,
5919 CREATED_BY,
5920 LAST_UPDATE_LOGIN,
5921 NUMBER1,
5922 NUMBER2)
5923 values (
5924 g_supply_query_id,
5925 sysdate,
5926 -1,
5927 sysdate,
5928 -1,
5929 -1,
5930 curr_trans_id(a),
5931 curr_inst_id(a));
5932
5933 if curr_supplier_id(a) <> -1 then
5934 -- for supplier list
5935 insert into msc_form_query
5936 (QUERY_ID,
5937 LAST_UPDATE_DATE,
5938 LAST_UPDATED_BY,
5939 CREATION_DATE,
5940 CREATED_BY,
5941 LAST_UPDATE_LOGIN,
5942 NUMBER1,
5943 NUMBER2,
5944 NUMBER3,
5945 NUMBER4,
5946 char1)
5947 values (
5948 g_supplier_query_id,
5949 sysdate,
5950 -1,
5951 sysdate,
5952 -1,
5953 -1,
5954 curr_supplier_id(a),
5955 curr_org_id(a),
5956 curr_inst_id(a),
5957 curr_item_id(a),
5958 curr_org_code(a));
5959 end if;
5960
5961 end if;
5962 END LOOP;
5963 the_index := i;
5964 msc_get_gantt_data.explode_children(p_plan_id, p_critical);
5965
5966 END fetchLateDemandData;
5967
5968
5969 Procedure fetchAllSupplier(p_plan_id number,
5970 v_name OUT NOCOPY varchar2) IS
5971 oneRecord varchar2(32000);
5972 rowCount number:=0;
5973 TYPE char_arr IS TABLE OF varchar2(255);
5974 v_org_code char_arr;
5975 v_supplier_name char_arr;
5976 v_org number_arr;
5977 v_instance number_arr;
5978 v_item number_arr;
5979 v_supplier number_arr;
5980
5981 BEGIN
5982 oneRecord := null;
5983 rowCount := 0;
5984 select distinct
5985 mfq.char1,
5986 mtp.partner_name,
5987 mfq.number2,
5988 mfq.number3,
5989 mfq.number4,
5990 mfq.number1
5991 bulk collect into
5992 v_org_code, v_supplier_name,
5993 v_org, v_instance, v_item, v_supplier
5994 FROM msc_trading_partners mtp,
5995 msc_form_query mfq
5996 where mfq.query_id = g_supplier_query_id
5997 AND mtp.partner_type = 1
5998 AND mtp.partner_id = mfq.number1
5999 ORDER BY 1,2 ;
6000 for a in 1..v_org_code.count loop
6001 v_supplier_name(a) := replace_seperator(v_supplier_name(a));
6002 oneRecord := oneRecord || record_seperator ||
6003 replace_seperator(v_org_code(a)) ||':'||
6004 v_supplier_name(a) || field_seperator ||
6005 v_org(a) || field_seperator ||
6006 v_instance(a) || field_seperator ||
6007 v_item(a) || field_seperator ||
6008 v_supplier(a);
6009 end loop;
6010
6011 rowCount := v_org_code.count;
6012
6013 v_name := rowCount || oneRecord;
6014 END fetchAllSupplier;
6015
6016 Procedure fetchAllLateDemand(p_plan_id number,
6017 p_demand_id number,
6018 v_name OUT NOCOPY varchar2) IS
6019 oneRecord varchar2(32000);
6020 rowCount number:=0;
6021 TYPE char_arr IS TABLE OF varchar2(600);
6022 TYPE dummy_date_arr IS TABLE OF date;
6023 v_order_name char_arr;
6024 v_demand_id number_arr;
6025 v_dummy_date dummy_date_arr;
6026
6027 BEGIN
6028 oneRecord := null;
6029 rowCount := 0;
6030 select distinct
6031 md.demand_id,
6032 nvl(md.order_number,
6033 msc_get_name.designator(md.schedule_designator_id))
6034 ||'('||md.using_assembly_demand_date||','||
6035 md.USING_REQUIREMENT_QUANTITY||')' ,
6036 md.using_assembly_demand_date
6037 bulk collect into v_demand_id, v_order_name , v_dummy_date
6038 FROM
6039 msc_exception_details med,
6040 msc_demands md,
6041 msc_demands md2
6042 where md2.plan_id = p_plan_id
6043 and md2.demand_id = p_demand_id
6044 and med.plan_id = md2.plan_id
6045 and med.organization_id = md2.organization_id
6046 and med.sr_instance_id = md2.sr_instance_id
6047 and med.inventory_item_id = md2.inventory_item_id
6048 and med.exception_type in (24,26)
6049 and md.plan_id = med.plan_id
6050 and md.demand_id = med.number1
6051 order by md.using_assembly_demand_date ;
6052
6053
6054 for a in 1..v_order_name.count loop
6055 oneRecord := oneRecord || record_seperator ||
6056 replace_seperator(v_order_name(a))|| field_seperator ||
6057 v_demand_id(a) ;
6058 end loop;
6059
6060 rowCount := v_order_name.count;
6061
6062 v_name := rowCount || oneRecord;
6063 END fetchAllLateDemand;
6064
6065 Function isCriticalSupply(p_plan_id number,
6066 p_end_demand_id number,
6067 p_transaction_id number,
6068 p_inst_id number) Return number IS
6069 isCritical number :=-1;
6070
6071 CURSOR critical_cur is
6072 select nvl(path_number,1)
6073 from msc_critical_paths
6074 where plan_id = p_plan_id
6075 and supply_id = p_transaction_id
6076 and sr_instance_id = p_inst_id
6077 and demand_id = p_end_demand_id
6078 -- and routing_sequence_id is null
6079 ;
6080
6081 Begin
6082 OPEN critical_cur;
6083 FETCH critical_cur into isCritical;
6084 CLOSE critical_cur;
6085
6086 return isCritical;
6087 END isCriticalSupply;
6088
6089 Function isCriticalRes(p_plan_id number,
6090 p_end_demand_id number,
6091 p_transaction_id number,
6092 p_inst_id number,
6093 p_operation_seq_id number,
6094 p_routing_seq_id number) Return number IS
6095 isCritical number :=-1;
6096
6097 CURSOR critical_cur is
6098 select nvl(path_number,1)
6099 from msc_critical_paths
6100 where plan_id = p_plan_id
6101 and supply_id = p_transaction_id
6102 and sr_instance_id = p_inst_id
6103 and demand_id = p_end_demand_id
6104 and nvl(routing_sequence_id,-1) = nvl(p_routing_seq_id,-1)
6105 and operation_sequence_id = p_operation_seq_id;
6106
6107 Begin
6108 OPEN critical_cur;
6109 FETCH critical_cur into isCritical;
6110 CLOSE critical_cur;
6111
6112 return isCritical;
6113 END isCriticalRes;
6114
6115 Function supplyType(p_order_type number, p_make_buy_code number,
6116 p_org_id number,p_source_org_id number) return number is
6117 p_supply_type number;
6118 BEGIN
6119 if p_org_id <> p_source_org_id then
6120 p_supply_type := TRANSFER_SUPPLY;
6121 elsif p_order_type in (1,2,8,11,12) then
6122 p_supply_type := BUY_SUPPLY;
6123 elsif p_order_type = 5 and
6124 p_make_buy_code = 2 then
6125 p_supply_type := BUY_SUPPLY;
6126 else
6127 p_supply_type := MAKE_SUPPLY;
6128 end if;
6129
6130 return p_supply_type;
6131
6132 END supplyType;
6133
6134 Function actualStartDate(p_order_type number, p_make_buy_code number,
6135 p_org_id number,p_source_org_id number,
6136 p_dock_date date, p_wip_start_date date,
6137 p_ship_date date, p_schedule_date date)
6138 return varchar2 is
6139 p_actual_start_date date;
6140 p_date varchar2(20);
6141 p_supply_type number;
6142 BEGIN
6143 if p_org_id <> p_source_org_id then
6144 p_supply_type := TRANSFER_SUPPLY;
6145 elsif p_order_type in (1,2,8,11,12) then
6146 p_supply_type := BUY_SUPPLY;
6147 elsif p_order_type = 5 and
6148 p_make_buy_code = 2 then
6149 p_supply_type := BUY_SUPPLY;
6150 else
6151 p_supply_type := MAKE_SUPPLY;
6152 end if;
6153
6154 if p_supply_type = BUY_SUPPLY then
6155 p_actual_start_date := p_dock_date;
6156 elsif p_supply_type = MAKE_SUPPLY then
6157 p_actual_start_date := p_wip_start_date;
6158 elsif p_supply_type = TRANSFER_SUPPLY then
6159 p_actual_start_date := p_ship_date;
6160 else
6161 p_actual_start_date := p_schedule_date;
6162 end if;
6163 return to_char(nvl(p_actual_start_date,p_schedule_date),format_mask);
6164
6165 END actualStartDate;
6166
6167 Function fetchSupplierPriority(p_plan_id number,
6168 p_instance_id number,
6169 p_org_id number,
6170 p_item_id number,
6171 p_supplier_id number,
6172 p_start varchar2,
6173 p_end varchar2) return varchar2 is
6174 p_start_date date:= to_date(p_start,format_mask);
6175 p_end_date date:= to_date(p_end,format_mask);
6176 v_qty number_arr;
6177 v_id number_arr;
6178 v_firm_qty number :=0;
6179 v_lower_qty number :=0;
6180 v_same_qty number :=0;
6181 v_higher_qty number :=0;
6182 b number;
6183 p_promise_date_profile number :=
6184 nvl(FND_PROFILE.Value('MSC_PO_DOCK_DATE_CALC_PREF'),1);
6185 BEGIN
6186
6187 if g_dmd_priority is null then
6188 g_dmd_priority :=0;
6189 end if;
6190
6191 if trunc(p_start_date) = trunc(p_end_date) then
6192 p_end_date := p_end_date +1;
6193 end if;
6194 -- supplier requirements
6195 select decode(ms.firm_planned_type, 1, -1,
6196 msc_get_gantt_data.get_dmd_priority(
6197 ms.plan_id, ms.sr_instance_id, ms.transaction_id)),
6198 ms.new_order_quantity
6199 bulk collect into v_id,v_qty
6200 from msc_supplies ms
6201 where ms.plan_id = p_plan_id
6202 and ms.inventory_item_id = p_item_id
6203 and ms.sr_instance_id = p_instance_id
6204 and ms.supplier_id = p_supplier_id
6205 and ms.organization_id = p_org_id
6206 and trunc(ms.new_dock_date) >= trunc(p_start_date)
6207 and trunc(ms.new_dock_date) < trunc(p_end_date)
6208 and (ms.order_type <> 1 -- not for PO
6209 or
6210 (ms.order_type = 1 and
6211 ms.promised_date is null and
6212 p_promise_date_profile = 1)); -- promised_date
6213 for b in 1 .. v_id.count loop
6214 if v_id(b) = -1 then -- firm
6215 v_firm_qty := v_firm_qty + v_qty(b);
6216 elsif v_id(b) > g_dmd_priority then
6217 v_lower_qty := v_lower_qty + v_qty(b);
6218 elsif v_id(b) < g_dmd_priority then
6219 v_higher_qty := v_higher_qty + v_qty(b);
6220 else
6221 v_same_qty := v_same_qty + v_qty(b);
6222 end if;
6223 end loop;
6224
6225 return
6226 v_lower_qty || field_seperator ||
6227 v_same_qty || field_seperator ||
6228 v_higher_qty|| field_seperator ||
6229 v_firm_qty ;
6230
6231 END fetchSupplierPriority;
6232
6233 Function fetchResourcePriority(p_plan_id number,
6234 p_instance_id number,
6235 p_org_id number,
6236 p_dept_id number,
6237 p_resource_id number,
6238 p_start varchar2,
6239 p_end varchar2) return varchar2 is
6240 p_start_date date:= trunc(to_date(p_start,format_mask));
6241 p_end_date date:= trunc(to_date(p_end,format_mask));
6242 v_qty number_arr;
6243 v_id number_arr;
6244 v_lower_qty number :=0;
6245 v_same_qty number :=0;
6246 v_higher_qty number :=0;
6247 v_firm_qty number :=0;
6248 b number;
6249 v_bkt_size number;
6250 BEGIN
6251
6252 if g_dmd_priority is null then
6253 g_dmd_priority :=0;
6254 end if;
6255
6256 if p_start_date = p_end_date then
6257 p_end_date := p_start_date +1;
6258 v_bkt_size := 1;
6259 else
6260 v_bkt_size := p_end_date - p_start_date;
6261 end if;
6262 -- requirements
6263 select decode(ms.firm_planned_type, 1, -1,
6264 msc_get_gantt_data.get_dmd_priority(
6265 mrr.plan_id, mrr.sr_instance_id, mrr.supply_id)),
6266 mrr.resource_hours/24/v_bkt_size
6267 bulk collect into v_id,v_qty
6268 from msc_resource_requirements mrr,
6269 msc_supplies ms
6270 where mrr.plan_id = p_plan_id
6271 and mrr.department_id = p_dept_id
6272 and mrr.sr_instance_id = p_instance_id
6273 and mrr.resource_id = p_resource_id
6274 and mrr.organization_id = p_org_id
6275 and mrr.parent_id =1
6276 and mrr.resource_hours > 0
6277 and mrr.end_date is not null
6278 and mrr.start_date <= g_cutoff_date
6279 and mrr.start_date < trunc(p_end_date)
6280 and mrr.start_date >= trunc(p_start_date)
6281 and mrr.plan_id = ms.plan_id
6282 and mrr.supply_id = ms.transaction_id
6283 and mrr.sr_instance_id = ms.sr_instance_id;
6284
6285 for b in 1 .. v_id.count loop
6286 if v_id(b) = -1 then -- firm
6287 v_firm_qty := v_firm_qty + v_qty(b);
6288 elsif v_id(b) > g_dmd_priority then
6289 v_lower_qty := v_lower_qty + v_qty(b);
6290 elsif v_id(b) < g_dmd_priority then
6291 v_higher_qty := v_higher_qty + v_qty(b);
6292 else
6293 v_same_qty := v_same_qty + v_qty(b);
6294 end if;
6295 end loop;
6296
6297 if v_lower_qty <> 0 then
6298 v_lower_qty := greatest(round(v_lower_qty,2),0.01);
6299 end if;
6300
6301 if v_same_qty <> 0 then
6302 v_same_qty := greatest(round(v_same_qty,2),0.01);
6303 end if;
6304
6305 if v_higher_qty <> 0 then
6306 v_higher_qty := greatest(round(v_higher_qty,2),0.01);
6307 end if;
6308
6309 if v_firm_qty <> 0 then
6310 v_firm_qty := greatest(round(v_firm_qty,2),0.01);
6311 end if;
6312
6313 return
6314 v_lower_qty || field_seperator ||
6315 v_same_qty || field_seperator ||
6316 v_higher_qty || field_seperator ||
6317 v_firm_qty;
6318
6319 END fetchResourcePriority;
6320
6321 Function get_dmd_priority(p_plan_id number,
6322 p_instance_id number,
6323 p_transaction_id number) return number is
6324 CURSOR dmd_cur IS
6325 SELECT min(md.demand_priority)
6326 FROM msc_demands md,
6327 msc_full_pegging mfp2,
6328 msc_full_pegging mfp1
6329 WHERE mfp1.plan_id = p_plan_id
6330 and mfp1.transaction_id = p_transaction_id
6331 and mfp1.sr_instance_id = p_instance_id
6332 and mfp2.pegging_id = mfp1.end_pegging_id
6333 and mfp2.plan_id = mfp1.plan_id
6334 and mfp2.sr_instance_id = mfp1.sr_instance_id
6335 and md.plan_id = mfp2.plan_id
6336 and md.sr_instance_id = mfp2.sr_instance_id
6337 and md.demand_id = mfp2.demand_id
6338 and md.demand_priority is not null;
6339 l_priority number;
6340
6341 BEGIN
6342 if p_transaction_id is null or
6343 p_plan_id is null or
6344 p_instance_id is null then
6345 return null;
6346 end if;
6347 Open dmd_cur;
6348 Fetch dmd_cur Into l_priority;
6349 Close dmd_cur;
6350
6351 return(l_priority);
6352
6353 END get_dmd_priority;
6354
6355 Procedure start_fetch(p_fetch_type IN varchar2,
6356 v_return_data OUT NOCOPY varchar2,
6357 start_index OUT NOCOPY number) is
6358 startParentIndex number;
6359 i number :=0;
6360 v_one_record varchar2(30000);
6361 begin
6362 if p_fetch_type is null then -- start from beginning
6363 g_current_block := 1;
6364 g_block_start_row.delete;
6365 g_block_start_row.extend;
6366 g_block_start_row(1) :=0;
6367 elsif p_fetch_type = 'PREV' then
6368 g_current_block := g_current_block -1;
6369 elsif p_fetch_type = 'NEXT' then
6370 g_current_block := g_current_block +1;
6371 elsif p_fetch_type = 'CURRENT' then
6372 g_current_block := nvl(g_current_block, 1);
6373 end if;
6374
6375 if p_fetch_type = 'NEXT' or p_fetch_type is null then
6376 g_block_start_row.extend;
6377 g_block_start_row(g_current_block+1) := null;
6378 end if;
6379 g_supply_rec_count :=0;
6380
6381 -- find the parent node of the start node
6382 start_index := g_block_start_row(g_current_block);
6383
6384 startParentIndex := peg_data.parent_index(start_index);
6385
6386 -- find all the parent nodes of the start node
6387 if startParentIndex is not null and
6388 startParentIndex > 0 then
6389 i := startParentIndex;
6390 while i is not null and i > 0 loop
6391
6392 v_one_record := i||field_seperator ||
6393 peg_data.parent_index(i) ||field_seperator ||
6394 msc_get_gantt_data.modify_parent_path(i) ||field_seperator ||
6395 print_one_record(i);
6396 if v_return_data is null then
6397 v_return_data := v_one_record;
6398 else
6399 v_return_data := v_one_record || record_seperator || v_return_data;
6400 end if;
6401 g_supply_rec_count := g_supply_rec_count +1;
6402 i := peg_data.parent_index(i);
6403 end loop;
6404
6405 v_one_record := print_one_record(0);
6406 v_return_data := record_seperator || v_one_record ||
6407 record_seperator || v_return_data;
6408 g_supply_rec_count := g_supply_rec_count +1;
6409
6410 if g_current_block >1 then -- add prev node
6411 v_one_record := msc_get_gantt_data.modify_parent_path(
6412 start_index)
6413 || field_seperator ||
6414 PREV_NODE || field_seperator ||
6415 -1 ||field_seperator ||
6416 'Previous '||g_supply_limit||field_seperator ||
6417 -1 ||field_seperator ||
6418 -1 ||field_seperator||0;
6419 v_return_data := v_return_data||record_seperator || v_one_record;
6420 end if;
6421 end if;
6422
6423 end start_fetch;
6424
6425 Function get_new_result(start_index IN number,
6426 v_return_data OUT NOCOPY varchar2,
6427 next_index OUT NOCOPY number)
6428 return boolean IS
6429 v_one_record varchar2(2000);
6430 v_len number :=0;
6431 i number;
6432 Begin
6433
6434 i := start_index;
6435
6436 if peg_data.parent_index.count > 0 and
6437 i < peg_data.parent_index.count and
6438 g_supply_rec_count < g_supply_limit then
6439 g_supply_parentIndex := peg_data.parent_index(i);
6440 while i is not null and g_supply_rec_count < g_supply_limit loop
6441 if g_supply_parentIndex <> peg_data.parent_index(i) then
6442 g_supply_childIndex :=0;
6443 g_supply_parentIndex := peg_data.parent_index(i);
6444 end if;
6445
6446 if g_supply_parentIndex >0 then
6447 peg_data.new_path(i) :=
6448 peg_data.new_path(g_supply_parentIndex) ||'-'||
6449 g_supply_childIndex;
6450 end if;
6451
6452 v_one_record := i||field_seperator ||
6453 peg_data.parent_index(i) ||field_seperator ||
6454 peg_data.new_path(i) ||field_seperator ||
6455 print_one_record(i);
6456 v_len := nvl(length(v_return_data),0) + nvl(length(v_one_record),0);
6457
6458 if v_len < 1000 then
6459 v_return_data := v_return_data || record_seperator || v_one_record;
6460 g_supply_rec_count := g_supply_rec_count +1;
6461 next_index := i+1;
6462 i := peg_data.parent_index.next(i);
6463 g_supply_childIndex := g_supply_childIndex +1;
6464 else
6465 exit;
6466 end if;
6467 end loop;
6468 end if;
6469
6470 if g_supply_rec_count >= g_supply_limit and
6471 next_index < peg_data.parent_index.count then
6472 -- add next code
6473 v_one_record := next_index+1|| field_seperator ||
6474 NEXT_NODE || field_seperator ||
6475 -1 ||field_seperator ||
6476 'Next '||g_supply_limit||field_seperator ||
6477 -1 ||field_seperator ||
6478 -1 ||field_seperator||0;
6479 v_return_data :=v_return_data || record_seperator ||
6480 v_one_record;
6481 g_block_start_row(g_current_block+1) := next_index;
6482 return false;
6483 end if;
6484
6485 if next_index = peg_data.parent_index.count then
6486 g_block_start_row(g_current_block+1) := next_index;
6487 return false;
6488 elsif v_return_data is null then
6489 return false;
6490 else
6491 return true;
6492 end if;
6493
6494 End get_new_result;
6495
6496 Function modify_parent_path(i number) return varchar2 is
6497 new_path varchar2(200);
6498 level number :=1;
6499 a number;
6500 Begin
6501 a := instr(peg_data.path(i), '-',1, level);
6502
6503 while a >0 loop
6504 if new_path is null then
6505 new_path := '0-0';
6506 else
6507 new_path := new_path ||'-0';
6508 end if;
6509 level := level+1;
6510 a := instr(peg_data.path(i), '-',1, level);
6511 end loop;
6512 peg_data.new_path(i) := new_path;
6513
6514 return new_path;
6515 END modify_parent_path;
6516
6517 FUNCTION isSupplyLate(p_plan_id number,
6518 p_instance_id number,
6519 p_organization_id number,
6520 p_inventory_item_id number,
6521 p_transaction_id number) RETURN NUMBER IS
6522 CURSOR C IS
6523 select 1
6524 from msc_exception_details
6525 WHERE number1 = p_transaction_id
6526 AND sr_instance_id = p_instance_id
6527 AND plan_id = p_plan_id
6528 and exception_type =36
6529 AND organization_id = p_organization_id
6530 AND inventory_item_id = p_inventory_item_id;
6531 v_isLate number :=0;
6532 BEGIN
6533 OPEN C;
6534 FETCH C INTO v_isLate;
6535 CLOSE C;
6536 return v_isLate;
6537 END isSupplyLate;
6538
6539 Function order_number(p_order_type number, p_order_number varchar2,
6540 p_plan_id number, p_inst_id number,
6541 p_transaction_id number, p_disposition_id number)
6542 return varchar2 IS
6543 v_text varchar2(300);
6544 cursor order_c is
6545 select order_number
6546 from msc_supplies
6547 where plan_id = p_plan_id
6548 and transaction_id = p_disposition_id
6549 and sr_instance_id = p_inst_id;
6550 BEGIN
6551
6552
6553 if p_order_type = 5 then
6554 if p_order_number is null then
6555 return to_char(p_transaction_id);
6556 else
6557 return p_order_number||' '||to_char(p_transaction_id);
6558 end if;
6559 end if;
6560
6561 if p_order_type in (14,17) then
6562 open order_c;
6563 fetch order_c into v_text;
6564 close order_c;
6565
6566 if v_text is null then
6567 return to_char(p_disposition_id);
6568 else
6569 return v_text ||' '||to_char(p_disposition_id);
6570 end if;
6571
6572 end if;
6573
6574 if p_order_number is not null then
6575 return p_order_number;
6576 end if;
6577
6578 --return null;
6579 return ' ';
6580 End order_number;
6581
6582
6583 END;