[Home] [Help]
PACKAGE BODY: APPS.MSC_OWB_TREE
Source
1 PACKAGE BODY MSC_OWB_TREE AS
2 /* $Header: MSCOSTRB.pls 120.7 2007/12/14 06:52:40 sbnaik ship $ */
3
4 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('MSC_ATP_DEBUG'), 'N');
5
6 /*
7
8 PROCEDURE extend( p_nodes IN OUT NoCopy owb_tree.noderec, extend_amount NUMBER );
9 PROCEDURE extend( p_nodes IN OUT NoCopy owb_tree.nodedata, extend_amount NUMBER );
10 */
11 PROCEDURE get_next_level_nodes(p_end_pegging_id NUMBER,
12 p_session_id NUMBER,
13 p_nodes OUT NoCopy owb_tree.NodeData,
14 p_expand_level NUMBER,
15 p_current_pegging_id NUMBER);
16
17 PROCEDURE get_supply_only_nodes(p_end_pegging_id NUMBER,
18 p_session_id NUMBER,
19 p_nodes OUT NoCopy owb_tree.NodeData,
20 p_expand_level NUMBER,
21 p_current_pegging_id NUMBER);
22
23 PROCEDURE extend( p_nodes IN OUT NoCopy owb_tree.noderec, extend_amount NUMBER ) IS
24 BEGIN
25 p_nodes.tree_type.extend(extend_amount);
26 p_nodes.parent_node_type.extend(extend_amount);
27 p_nodes.state.extend( extend_amount );
28 p_nodes.depth.extend( extend_amount );
29 p_nodes.label.extend( extend_amount );
30 p_nodes.icon.extend( extend_amount );
31 p_nodes.data.extend( extend_amount );
32 END extend;
33
34
35 PROCEDURE extend( p_nodes IN OUT NoCopy owb_tree.nodedata, extend_amount NUMBER ) IS
36 BEGIN
37 p_nodes.state.extend( extend_amount );
38 p_nodes.depth.extend( extend_amount );
39 p_nodes.label.extend( extend_amount );
40 p_nodes.icon.extend( extend_amount );
41 p_nodes.data.extend( extend_amount );
42 END extend;
43
44 PROCEDURE get_node
45 ( p_tree_type NUMBER,
46 p_parent NUMBER,
47 p_nodes IN OUT NoCopy owb_tree.noderec,
48 p_lookup_code NUMBER ,
49 p_state NUMBER ,
50 p_depth NUMBER,
51 p_icon VARCHAR2);
52
53 PROCEDURE getstructure
54 (p_session_id NUMBER,
55 p_mode NUMBER,
56 p_nodes OUT NoCopy owb_tree.nodeRec) IS
57 l_current_node_depth NUMBER;
58
59 total_excp NUMBER;
60
61 BEGIN
62 record_count := 1;
63
64 IF p_mode = 2 OR p_mode = 4 THEN
65 -- OE view results or SC ATP
66 extend( p_nodes, nodes_in_orders_tree);
67 ELSIF p_mode = 1 THEN
68 -- backlog mode
69 extend( p_nodes, 1+nodes_in_orders_tree+nodes_in_items_tree);
70 END IF;
71
72
73 l_current_node_depth := 1;
74 get_node( orders_tree, 0 ,p_nodes, sales_orders, 1, l_current_node_depth, ICON_FOLDER);
75
76 l_current_node_depth := 1; -- These nodes will be relative to parent node. Hence 1
77 get_node( 0, sales_orders_n ,p_nodes, indep_lines, 0, l_current_node_depth, ICON_FOLDER);
78 get_node( 0, sales_orders_n ,p_nodes, ship_sets, 1, l_current_node_depth, ICON_FOLDER);
79 get_node( 0, sales_orders_n ,p_nodes, arrival_sets, 1, l_current_node_depth, ICON_FOLDER);
80 get_node( orders_tree, ship_sets_n ,p_nodes, sources, 1, l_current_node_depth,ICON_FOLDER );
81 -- 0 implies that the hierarchy is common to all trees
82
83 IF p_mode = 1 THEN
84 l_current_node_depth := 1;
85 get_node( excp_tree, 0 , p_nodes, exceptions, 1, l_current_node_depth, ICON_FOLDER);
86
87 l_current_node_depth := 2;
88
89 total_excp := get_excp_count(p_session_id, 1);
90 IF total_excp > 0 THEN
91 extend(p_nodes,1);
92 get_node( excp_tree, exceptions, p_nodes, shortage, 1, l_current_node_depth,ICON_FOLDER );
93 p_nodes.label(record_count-1) := p_nodes.label(record_count-1)||' ('||total_excp||')';
94 END IF;
95 total_excp := get_excp_count(p_session_id, 2);
96 IF total_excp > 0 THEN
97 extend(p_nodes,1);
98 get_node( excp_tree, exceptions,p_nodes, later_than_old_schedule_date, 1, l_current_node_depth, ICON_FOLDER);
99 p_nodes.label(record_count-1) := p_nodes.label(record_count-1)||' ('||total_excp||')';
100 END IF;
101 total_excp := get_excp_count(p_session_id, 3);
102 IF total_excp > 0 THEN
103 extend(p_nodes,1);
104 get_node( excp_tree, exceptions,p_nodes, later_than_promise_date , 1, l_current_node_depth, ICON_FOLDER);
105 p_nodes.label(record_count-1) := p_nodes.label(record_count-1)||' ('||total_excp||')';
106 END IF;
107 total_excp := get_excp_count(p_session_id, 4);
108 IF total_excp > 0 THEN
109 extend(p_nodes,1);
110 get_node( excp_tree, exceptions,p_nodes, later_than_request_date , 1, l_current_node_depth,ICON_FOLDER );
111 p_nodes.label(record_count-1) := p_nodes.label(record_count-1)||' ('||total_excp||')';
112 END IF;
113 total_excp := get_excp_count(p_session_id, 5);
114 IF total_excp > 0 THEN
115 extend(p_nodes,1);
116 get_node( excp_tree, exceptions,p_nodes, insufficient_margin , 1, l_current_node_depth, ICON_FOLDER);
117 p_nodes.label(record_count-1) := p_nodes.label(record_count-1)||' ('||total_excp||')';
118 END IF;
119 total_excp := get_excp_count(p_session_id, 6);
120 IF total_excp > 0 THEN
121 extend(p_nodes,1);
122 get_node( excp_tree, exceptions,p_nodes, modified_source, 1, l_current_node_depth, ICON_FOLDER);
123 p_nodes.label(record_count-1) := p_nodes.label(record_count-1)||' ('||total_excp||')';
124 END IF;
125 total_excp := get_excp_count(p_session_id, 7);
126 IF total_excp > 0 THEN
127 extend(p_nodes,1);
128 get_node( excp_tree, exceptions,p_nodes, errors, 1, l_current_node_depth, ICON_FOLDER);
129 p_nodes.label(record_count-1) := p_nodes.label(record_count-1)||' ('||total_excp||')';
130 END IF;
131
132 l_current_node_depth := 1;
133 get_node( items_tree, 0 , p_nodes, organizations, 1, l_current_node_depth, ICON_FOLDER);
134
135 get_node( items_tree, organizations_n ,p_nodes, product_families , 1,l_current_node_depth, ICON_FOLDER);
136 get_node( items_tree, organizations_n , p_nodes, categories, 1, l_current_node_depth, ICON_FOLDER);
137 get_node( items_tree, categories_n , p_nodes, items, 1, l_current_node_depth, ICON_FOLDER);
138 get_node( items_tree, product_families_n , p_nodes, items, 1, l_current_node_depth, ICON_FOLDER);
139 get_node( items_tree, items, p_nodes, items_n, 1, l_current_node_depth, ICON_FOLDER);
140 get_node( items_tree, items_n ,p_nodes, sales_orders, 1, l_current_node_depth, ICON_FOLDER);
141 END IF;
142
143 END getstructure;
144
145 PROCEDURE get_node
146 ( p_tree_type NUMBER,
147 p_parent NUMBER,
148 p_nodes IN OUT NoCopy owb_tree.noderec,
149 p_lookup_code NUMBER ,
150 p_state NUMBER ,
151 p_depth NUMBER,
152 p_icon VARCHAR2)
153 IS
154
155 CURSOR l1(p_lookup_code NUMBER, p_state NUMBER, p_depth NUMBER, p_icon VARCHAR2 ) IS
156 SELECT
157 p_state,
158 p_depth,
159 Meaning,
160 p_icon,
161 To_char(lookup_code) -- mfg_lookups still has it as a number
162 FROM mfg_lookups
163 WHERE LOOKUP_TYPE = 'MRP_NODE_TYPE'
164 AND LOOKUP_CODE = To_char(p_lookup_code);
165
166 BEGIN
167 OPEN l1(p_lookup_code, p_state, p_depth, p_icon);
168
169 FETCH l1 INTO
170 p_nodes.state(record_count),
171 p_nodes.depth(record_count),
172 p_nodes.label(record_count),
173 p_nodes.icon(record_count),
174 p_nodes.data(record_count);
175 CLOSE l1;
176 p_nodes.tree_type(record_count) := p_tree_type;
177 p_nodes.parent_node_type(record_count) := p_parent;
178 record_count := record_count + 1;
179 END get_node;
180
181 FUNCTION get_demand_class ( p_pegging_id IN NUMBER, p_session_id IN NUMBER)
182 return VARCHAR2 IS
183
184 v_demand_class varchar2(40):= NULL;
185
186 BEGIN
187
188 select CHAR1 into v_demand_class
189 from mrp_atp_details_temp
190 where record_type = 3
191 and pegging_id = p_pegging_id
192 and session_id = p_session_id;
193
194 RETURN v_demand_class;
195 EXCEPTION
196 WHEN NO_DATA_FOUND THEN
197 RETURN NULL;
198
199 END get_demand_class;
200
201
202 FUNCTION get_excp_count( p_session_id NUMBER, col_num NUMBER ) RETURN INTEGER IS
203 excp_count NUMBER := 0;
204 stmt VARCHAR2(200);
205 BEGIN
206 stmt :=
207 ' SELECT count(1) '||
208 ' FROM mrp_atp_schedule_temp mast '||
209 ' WHERE mast.session_id = :p_session_id '||
210 ' AND exception'||col_num||' = 1';
211
212 execute immediate stmt INTO excp_count using p_session_id;
213 IF PG_DEBUG in ('Y', 'C') THEN
214 msc_sch_wb.atp_debug('get_excp_count: ' || ' Exception'||col_num||' count '||excp_count);
215 END IF;
216
217 RETURN excp_count;
218 EXCEPTION
219 WHEN no_data_found THEN
220 RETURN 0;
221 END get_excp_count;
222
223 PROCEDURE get_lookups IS
224 BEGIN
225 IF NOT owb_tree.lookups.exists(1) THEN
226 SELECT
227 Decode(lookup_code,
228 0, Substr(meaning, 1,4),
229 1, Substr(meaning, 1,13),
230 2, Substr(meaning, 1,10),
231 3, Substr(meaning, 1,4),
232 10, Substr(meaning, 1,6),
233 15, Substr(meaning, 1,11),
234 30, Substr(meaning, 1,4),
235 35, Substr(meaning, 1,5),
236 40, Substr(meaning, 1,6),
237 45, Substr(meaning, 1,3),
238 50, Substr(meaning, 1,4),
239 60, Substr(meaning, 1,3),
240 70, Substr(meaning, 1,16),
241 75, Substr(meaning, 1,16),
242 80, Substr(meaning, 1,10),
243 85, Substr(meaning, 1,10),
244 90, Substr(meaning, 1,11),
245 100,Substr(meaning, 1,6),
246 110,Substr(meaning, 1,16),
247 111,Substr(meaning, 1,24), -- Material Constraint
248 112,Substr(meaning, 1,30), -- PTF constraint
249 113, Substr(meaning, 1,40), -- Manufacturing Constraint
250 114, Substr(meaning,1,36), -- Purchasing Constraint
251 115, Substr(meaning,1,34), -- Transfer Constraint
252 116, Substr(meaning,1,24), -- Resource Constraint
253 117, Substr(meaning,1,24), -- Calendar Constraint
254 119, Substr(meaning,1,40) -- Product Family Demand Spread
255 )
256 bulk collect INTO owb_tree.lookups
257 FROM mfg_lookups
258 WHERE
259 (lookup_type = 'MRP_SOURCE_TYPE' AND lookup_code IN ( 0, 1, 2, 3))
260 OR (lookup_type = 'MRP_ATP_FORM_TYPE' AND lookup_code IN
261 (10, 15, 30, 35, 40, 45, 50, 60, 70, 75, 80, 85, 90, 100, 111,110,112,113,114,115,116, 117,119))
262 ORDER BY lookup_code;
263 -- 1. ATP 0 4
264 -- 2. Transfer 1 1
265 -- 3. Make At 2 2
266 -- 4. Buy 3 3
267 -- 5. Item 10 16
268 -- 6. Resource 15 17
269 -- 7. Res 30 5
270 -- 8. Dept 35 6
271 -- 9. From 40 7
272 -- 10. To 45 8
273 -- 11. Qty 50 9
274 -- 12. To 60 10
275 -- 13. Total supply 70 11
276 -- 14. Total demand 75 12
277 -- 15. Net ATP 80 13
278 -- 16. Cum ATP 85 14
279 -- 17. Supplier 90 15
280 -- 18. Line 100
281 -- 19. Substitute 110
282 -- 20,
283 FOR j IN 1..owb_tree.LOOKUPS_COUNT LOOP
284 msc_sch_wb.atp_debug(' lookups '||j||' '||owb_tree.lookups(j));
285 END LOOP;
286
287 END IF;
288
289 EXCEPTION
290 WHEN OTHERS THEN
291 IF PG_DEBUG in ('Y', 'C') THEN
292 msc_sch_wb.atp_debug('Excp in get_lookups '||Substr(Sqlerrm, 1,100));
293 END IF;
294 END get_lookups;
295
296 PROCEDURE get_Sourcing_Nodes(p_end_pegging_id NUMBER,
297 p_session_id NUMBER,
298 p_nodes OUT NoCopy owb_tree.NodeData,
299 p_expand_level NUMBER,
300 p_current_node_data NUMBER,
301 p_checkbox BOOLEAN DEFAULT FALSE) IS
302 BEGIN
303 get_lookups;
304
305 -- ATP Pegging
306
307
308 IF NOT p_checkbox THEN
309 IF PG_DEBUG in ('Y', 'C') THEN
310 msc_sch_wb.atp_debug('get_Sourcing_Nodes: ' || ' Checkbox is False, so calling regular tree');
311 END IF;
312 IF p_current_node_data IS NULL THEN
313 -- There is no ref node, and we have to populate the first levels
314 -- When initializing we only need the first level rows with no expansion
315 IF PG_DEBUG in ('Y', 'C') THEN
316 msc_sch_wb.atp_debug('get_Sourcing_Nodes: ' || ' p_current_node_data IS NULL ');
317 END IF;
318 get_next_level_nodes(p_end_pegging_id, p_session_id,
319 p_nodes, next_level, NULL);
320 ELSE
321 IF p_expand_level = NEXT_LEVEL THEN
322 IF PG_DEBUG in ('Y', 'C') THEN
323 msc_sch_wb.atp_debug('get_Sourcing_Nodes: ' || p_end_pegging_id||' '||p_current_node_data);
324 END IF;
325
326 get_next_level_nodes(p_end_pegging_id, p_session_id,
327 p_nodes, NEXT_LEVEL, p_current_node_data);
328 ELSIF p_expand_level = ALL_LEVELS THEN
329 get_next_level_nodes(p_end_pegging_id, p_session_id,
330 p_nodes, ALL_LEVELS, p_current_node_data);
331
332 ELSIF p_expand_level = CONSTRAINT_LEVEL THEN
333 get_next_level_nodes(p_end_pegging_id, p_session_id,
334 p_nodes, CONSTRAINT_LEVEL,p_current_node_data);
335
336
337 END IF;
338 END IF;
339
340 /* IF p_current_node_data IS NOT NULL THEN
341 p_nodes.DELETE(1);
342 END IF; */
343 -- ATP PEgging
344 ELSE
345 IF PG_DEBUG in ('Y', 'C') THEN
346 msc_sch_wb.atp_debug('get_Sourcing_Nodes: ' || ' Calling only get_supply_only_nodes ' );
347 END IF;
348 IF p_current_node_data IS NULL THEN
349 -- There is no ref node, and we have to populate the first levels
350 -- When initializing we only need the first level rows with no expansion
351 IF PG_DEBUG in ('Y', 'C') THEN
352 msc_sch_wb.atp_debug('get_Sourcing_Nodes: ' || ' p_current_node_data IS NULL ');
353 END IF;
354 get_supply_only_nodes(p_end_pegging_id, p_session_id,
355 p_nodes, next_level, NULL);
356 ELSE
357 IF p_expand_level = NEXT_LEVEL THEN
358 IF PG_DEBUG in ('Y', 'C') THEN
359 msc_sch_wb.atp_debug('get_Sourcing_Nodes: ' || p_end_pegging_id||' '||p_current_node_data);
360 END IF;
361 -- ATP Pegging
362 get_supply_only_nodes(p_end_pegging_id, p_session_id,
363 p_nodes, NEXT_LEVEL, p_current_node_data);
364 ELSIF p_expand_level = ALL_LEVELS THEN
365 get_supply_only_nodes(p_end_pegging_id, p_session_id,
366 p_nodes, ALL_LEVELS, p_current_node_data);
367
368 ELSIF p_expand_level = CONSTRAINT_LEVEL THEN
369 get_supply_only_nodes(p_end_pegging_id, p_session_id,
370 p_nodes, CONSTRAINT_LEVEL,p_current_node_data);
371
372
373 END IF;
374 END IF;
375 END IF;
376
377 IF p_nodes.state.COUNT = 0 THEN
378 extend(p_nodes,1);
379 fnd_message.set_name('MRP','MRP_ATP_NO_PEGGING');
380 p_nodes.label(1) := Substr(fnd_message.get, 1, 240);
381 p_nodes.state(1) := LEAF_NODE;
382 p_nodes.depth(1) := 1;
383 p_nodes.icon(1) := ICON_NO_PEGGING;
384 p_nodes.data(1) := 0;
385 END IF;
386 EXCEPTION
387 WHEN OTHERS THEN
388 IF PG_DEBUG in ('Y', 'C') THEN
389 msc_sch_wb.atp_debug('Excp in get_sourcing_nodes '||Substr(Sqlerrm, 1,100));
390 END IF;
391 END get_sourcing_nodes;
392
393 FUNCTION get_cust_hier_string
394 (dmd_class in VARCHAR2) return VARCHAR2 IS
395
396 pos1 number;
397 pos2 number;
398 v_class VARCHAR2(500);
399
400 v_dmd_string VARCHAR2(1000);
401
402 v_partner_id number;
403 v_partner_site_id number;
404 v_partner_name varchar2(100);
405 v_partner_site_name varchar2(240);
406 delim constant varchar2(1) := fnd_global.local_chr(13);
407
408 BEGIN
409 pos1 := instr(dmd_class,delim,1,1);
410 pos2 := instr(dmd_class,delim,1,2);
411
412 if pos1 = 0 then
413 if dmd_class = '-1' then
414 v_class := 'OTHER';
415 else
416 v_class := dmd_class;
417 end if;
418
419 v_dmd_string := v_class;
420
421 elsif pos1 <> 0 and pos2 = 0 then
422 v_class := substr(dmd_class,1,pos1-1);
423 v_partner_id := substr(dmd_class,pos1+1);
424 if v_partner_id = -1 then
425 v_partner_name := 'OTHER';
426 else
427 v_partner_name := msc_get_name.customer(v_partner_id);
428 end if;
429
430 if v_class = '-1' then
431 v_class := 'OTHER';
432 end if;
433 v_dmd_string := v_class||' - '||v_partner_name;
434
435 elsif pos1 <> 0 and pos2 <> 0 then
436 v_class := substr(dmd_class,1,pos1-1);
437 v_partner_id := substr(dmd_class,pos1+1,pos2-pos1-1);
438 v_partner_site_id := substr(dmd_class,pos2+1);
439 if v_partner_id = -1 then
440 v_partner_name := 'OTHER';
441 else
442 v_partner_name := msc_get_name.customer(v_partner_id);
443 end if;
444 if v_partner_site_id = -1 then
445 v_partner_site_name := 'OTHER';
446 else
447 v_partner_site_name := msc_get_name.customer_site(v_partner_site_id);
448 end if;
449 if v_class = '-1' then
450 v_class := 'OTHER';
451 end if;
452
453 v_dmd_string := v_class||' - '||v_partner_name||' - '||v_partner_site_name;
454
455 end if;
456
457 return v_dmd_string;
458
459 END get_cust_hier_string;
460
461
462 PROCEDURE get_next_level_nodes(p_end_pegging_id NUMBER,
463 p_session_id NUMBER,
464 p_nodes OUT NoCopy owb_tree.NodeData,
465 p_expand_level NUMBER,
466 p_current_pegging_id NUMBER)
467 IS
468 counter NUMBER;
469 BEGIN
470 IF PG_DEBUG in ('Y', 'C') THEN
471 msc_sch_wb.atp_debug('get_next_level_nodes: ' || 'Cholpon check of p_expand_level' || p_expand_level);
472 END IF;
473 SELECT
474 Decode(inventory_item_name, NULL,
475 Decode(department_code, NULL,leaf_node,
476 Decode(supply_demand_type,1,
477 Decode(p_expand_level, next_level, collapsed,
478 all_levels, expanded,
479 constraint_level, decode(constrained_path, NULL, collapsed, expanded)
480 )
481 ,leaf_node)),
482 -- Only TR, source are 1st lev
483 Decode(supply_demand_type,1,
484 Decode(p_expand_level, next_level, collapsed, all_levels, expanded,
485 constraint_level,decode(constrained_path, NULL, collapsed, expanded)),
486 Decode(source_type, NULL, leaf_node, 0, leaf_node, 3,
487 Decode(p_expand_level, next_level, collapsed, all_levels, expanded,
488 constraint_level, decode(constrained_path, NULL, collapsed, expanded)),
489 -- It will be collapsed if it is not a source_type node
490 -- (i.e. make etc)
491 -- It will be collapsed if it is a BUY from source
492 -- It will also be leaf_node for null or ATP
493 Decode(p_expand_level, next_level, collapsed,
494 all_levels, expanded,
495 constraint_level, decode(constrained_path, NULL, collapsed, expanded))))),
496 LEVEL-1,
497 Decode(inventory_item_name, NULL,
498 -- when it is null
499 Decode(department_code, NULL,
500 (ship_method||' - '||ROUND(supply_demand_quantity,6)||' '||uom_code||' - '
501 ||from_organization_code
502 ||Decode(from_location_code, NULL, '', '('||from_location_code||')')
503 ||' '||owb_tree.lookups(10)||' '
504 ||to_organization_code
505 ||Decode(to_location_code, NULL, '', '('||to_location_code||')')),
506 -- dept is not null
507 Decode(resource_code, NULL,
508 -- Line
509 owb_tree.lookups(18)||'-'||department_code,
510 -- Resource
511 owb_tree.lookups(8)||'-'||department_code||':'||owb_tree.lookups(7)||'-'||resource_code)
512 ||' '||owb_tree.lookups(11)||' '||ROUND(supply_demand_quantity,6)||' '||uom_code||' '||owb_tree.lookups(12)||' '||fnd_date.date_to_displaydate(supply_demand_date)),
513 -- when item_name is not null
514 inventory_item_name ||' -'||Decode(number1,1, owb_tree.lookups(19)||' ',' ')
515 ||Decode(source_type, 1, owb_tree.lookups(2),2, owb_tree.lookups(3), 3, owb_tree.lookups(4),
516 0, owb_tree.lookups(1))
517 ||' '||Decode(source_type, 3, supplier_name, nvl(supplier_name,organization_code)) ||
518 ' '||supplier_site_name||
519 ' '||owb_tree.lookups(11)||' '||ROUND(supply_demand_quantity,6)||' '||owb_tree.lookups(12)||' '
520 ||fnd_date.date_to_displaydate(supply_demand_date)
521 ) || ' '
522 ||decode(constraint_type,null,null,
523 1, '{'||owb_tree.lookups(20)||'}',
524 2, '{'||owb_tree.lookups(21)||' '||fnd_date.date_to_displaydate(constraint_date)||'}',
525 3, '{'||owb_tree.lookups(22)||' ' ||fnd_date.date_to_displaydate(constraint_date)||'}',
526 4, '{'||owb_tree.lookups(23)||' '||fnd_date.date_to_displaydate(constraint_date)||'}',
527 5, '{'||owb_tree.lookups(24)||' ' || fnd_date.date_to_displaydate(constraint_date)||'}',
528 6, '{'||owb_tree.lookups(25)||'}',
529 7 , '{'||owb_tree.lookups(26)||'}')
530 ||decode(char1,null, null, ' ('||get_cust_hier_string(char1)||')'),
531 decode(supply_demand_type,1, ICON_DEMAND,
532 decode(inventory_item_name, NULL,
533 decode(constraint_type,NULL,ICON_RESOURCE_CAP, -- Dept Res
534 ICON_RESOURCE_CAP_CRIT),
535 decode(source_type,0,
536 decode(constraint_type, NULL,ICON_RESOURCE_CAP,
537 ICON_RESOURCE_CAP_CRIT),
538 decode(constrained_path,NULL,ICON_RESOURCE_CAP,
539 ICON_RESOURCE_CAP_CRIT))
540 )
541 ),
542 pegging_id data
543 bulk collect INTO
544 p_nodes.state,
545 p_nodes.depth,
546 p_nodes.label,
547 p_nodes.icon,
548 p_nodes.data
549 FROM mrp_atp_details_temp
550 where nonatp_flag is NULL
551 start WITH
552 session_id = p_session_id
553 AND record_type = 3
554 AND end_pegging_id = p_end_pegging_id
555 AND nvl(p_current_pegging_id, Nvl(parent_pegging_id, -1))
556 = Decode(p_current_pegging_id,NULL, -1, pegging_id)
557 connect by
558 PRIOR session_id = session_id
559 AND PRIOR record_type = record_type
560 AND PRIOR pegging_id = parent_pegging_id
561 AND PRIOR end_pegging_id = end_pegging_id
562 AND Decode(p_expand_level, NEXT_LEVEL, Nvl(p_current_pegging_id,-1),ALL_LEVELS, 1, CONSTRAINT_LEVEL,2)
563 = Decode(p_expand_level, NEXT_LEVEL, Nvl(parent_pegging_id, -1), ALL_LEVELS, 1,CONSTRAINT_LEVEL,2)
564 ORDER BY pegging_id;
565
566 IF PG_DEBUG in ('Y', 'C') THEN
567 msc_sch_wb.atp_debug(' get_next_level_nodes EPI '||p_end_pegging_id||' exp '||p_expand_level||' PId '||p_current_pegging_id);
568 END IF;
569
570 /* for j in 1..p_nodes.state.count loop
571 IF PG_DEBUG in ('Y', 'C') THEN
572 msc_sch_wb.atp_debug('get_next_level_nodes: ' || ' s '||p_nodes.state(j) ||' d '||p_nodes.depth(j)||' l '||p_nodes.label(j)||' i '||p_nodes.icon(j)||' d '||p_nodes.data(j));
573 END IF; end loop;
574 */
575
576 EXCEPTION
577 WHEN OTHERS THEN
578 IF PG_DEBUG in ('Y', 'C') THEN
579 msc_sch_wb.atp_debug(' Exception in get_next_level_nodes '||Substr(Sqlerrm, 1, 100));
580 END IF;
581 END get_next_level_nodes;
582
583 PROCEDURE get_supply_only_nodes (p_end_pegging_id NUMBER,
584 p_session_id NUMBER,
585 p_nodes OUT NoCopy owb_tree.NodeData,
586 p_expand_level NUMBER,
587 p_current_pegging_id NUMBER)
588 IS
589 counter NUMBER;
590 BEGIN
591 IF PG_DEBUG in ('Y', 'C') THEN
592 msc_sch_wb.atp_debug('get_supply_only_nodes: ' || 'Cholpon check ' || owb_tree.lookups(21));
593 END IF;
594 SELECT
595 Decode(inventory_item_name, NULL,
596 Decode(department_code, NULL,leaf_node,
597 Decode(supply_demand_type,1,
598 Decode(p_expand_level, next_level, collapsed,
599 all_levels, expanded,
600 constraint_level,
601 decode(constrained_path, NULL, collapsed, expanded))
602 ,leaf_node)),
603 -- Only TR, source are 1st lev
604 Decode(supply_demand_type,1,
605 Decode(p_expand_level, next_level, collapsed, all_levels, expanded,constraint_level,decode(constrained_path, NULL, collapsed, expanded)),
606 Decode(source_type, NULL, leaf_node, 0, leaf_node, 3,
607 Decode(p_expand_level, next_level, collapsed, all_levels, expanded,
608 constraint_level,
609 decode(constrained_path, NULL, collapsed, expanded)),
610 -- It will be collapsed if it is not a source_type node
611 -- (i.e. make etc)
612 -- It will be collapsed if it is a BUY from source
613 -- It will also be leaf_node for null or ATP
614 Decode(p_expand_level, next_level, collapsed,
615 all_levels, expanded,
616 constraint_level,
617 decode(constrained_path,NULL,collapsed, expanded))))),
618 DECODE(LEVEL, 1, 0, LEVEL/2),
619 Decode(inventory_item_name, NULL,
620 -- when it is null
621 Decode(department_code, NULL,
622 (ship_method||' - '||ROUND(supply_demand_quantity,6)||' '||uom_code||' - '
623 ||from_organization_code
624 ||Decode(from_location_code, NULL, '', '('||from_location_code||')')
625 ||' '||owb_tree.lookups(10)||' '
626 ||to_organization_code
627 ||Decode(to_location_code, NULL, '', '('||to_location_code||')')),
628 -- dept is not null
629 Decode(resource_code, NULL,
630 -- Line
631 owb_tree.lookups(18)||'-'||department_code,
632 -- Resource
633 owb_tree.lookups(8)||'-'||department_code||':'||owb_tree.lookups(7)||'-'||resource_code)
634 ||' '||owb_tree.lookups(11)||' '||ROUND(supply_demand_quantity,6)||' '||uom_code||' '||owb_tree.lookups(12)||' '||fnd_date.date_to_displaydate(supply_demand_date)),
635 -- when item_name is not null
636 inventory_item_name ||' -'||Decode(number1,1, owb_tree.lookups(19)||' ',' ')
637 ||Decode(source_type, 1, owb_tree.lookups(2),2, owb_tree.lookups(3), 3, owb_tree.lookups(4),
638 0, owb_tree.lookups(1))
639 ||' '||Decode(source_type, 3, supplier_name, nvl(supplier_name,organization_code))||
640 ' '||supplier_site_name||
641 ' '||owb_tree.lookups(11)||' '||ROUND(supply_demand_quantity,6)||' '||owb_tree.lookups(12)||' '
642 ||fnd_date.date_to_displaydate(supply_demand_date)
643 ) ||
644 ' ' ||decode(constraint_type,null,null,
645 1 , '{'||owb_tree.lookups(20)||' '||fnd_date.date_to_displaydate(constraint_date) ||'}',
646 2, '{'||owb_tree.lookups(21)||' ' ||fnd_date.date_to_displaydate(constraint_date)||'}', 3, '{'||owb_tree.lookups(22)||' ' ||fnd_date.date_to_displaydate(constraint_date)||'}',
647 4, '{'||owb_tree.lookups(23)||' ' ||fnd_date.date_to_displaydate(constraint_date)||'}',
648 5, '{'||owb_tree.lookups(24)||' '||fnd_date.date_to_displaydate(constraint_date) ||'}',
649 6, '{'||owb_tree.lookups(25) || '}',
650 7 , '{'||owb_tree.lookups(26)||'}')
651 ||decode(char1,null, null, ' ('||get_cust_hier_string(char1)||')'),
652 decode(supply_demand_type,1, ICON_DEMAND,
653 decode(inventory_item_name, NULL,
654 decode(constraint_type,NULL,ICON_RESOURCE_CAP, -- Dept Res
655 ICON_RESOURCE_CAP_CRIT),
656 decode(source_type,0,
657 decode(constraint_type, NULL,ICON_RESOURCE_CAP,
658 ICON_RESOURCE_CAP_CRIT),
659 decode(constrained_path,NULL,ICON_RESOURCE_CAP,
660 ICON_RESOURCE_CAP_CRIT))
661 )
662 ),
663 pegging_id data
664 bulk collect INTO
665 p_nodes.state,
666 p_nodes.depth,
667 p_nodes.label,
668 p_nodes.icon,
669 p_nodes.data
670 FROM mrp_atp_details_temp
671 where supply_demand_type = Decode(parent_pegging_id, NULL, 1,2)
672 /*and decode(p_current_pegging_id, NULL, -1 , atp_level) = decode(p_current_pegging_id , NULL, -1, dummy)*/
673 start WITH
674 session_id = p_session_id
675 AND record_type = 3
676 AND end_pegging_id = p_end_pegging_id
677 AND nvl(p_current_pegging_id, Nvl(parent_pegging_id, -1)) = Decode(p_current_pegging_id,NULL, -1, pegging_id)
678 connect by
679 PRIOR session_id = session_id
680 AND PRIOR record_type = record_type
681 AND PRIOR pegging_id = parent_pegging_id
682 AND PRIOR end_pegging_id = end_pegging_id
683 /* AND Decode(p_expand_level, NEXT_LEVEL, decode(p_current_pegging_id,NULL,-1,-2),ALL_LEVELS, 1,CONSTRAINT_LEVEL,2) = Decode(p_expand_level,
684 NEXT_LEVEL, decode(parent_pegging_id,NULL, -1, -2), ALL_LEVELS, 1,CONSTRAINT_LEVEL,2)*/
685 /* AND Decode(p_expand_level, NEXT_LEVEL, Nvl(p_current_pegging_id,-1),ALL_LEVELS, 1) = Decode(p_expand_level, NEXT_LEVEL, Nvl(parent_pegging_id, -1), ALL_LEVELS, 1)*/
686 ORDER BY pegging_id;
687
688
689 IF PG_DEBUG in ('Y', 'C') THEN
690 msc_sch_wb.atp_debug('get_supply_only_nodes: ' || ' get_next_level_nodes EPI '||p_end_pegging_id||' exp '||p_expand_level||' PId '||p_current_pegging_id);
691 END IF;
692
693 /* for j in 1..p_nodes.state.count loop
694 IF PG_DEBUG in ('Y', 'C') THEN
695 msc_sch_wb.atp_debug('get_supply_only_nodes: ' || ' s '||p_nodes.state(j) ||' d '||p_nodes.depth(j)||' l '||p_nodes.label(j)||' i '||p_nodes.icon(j)||' d '||p_nodes.data(j));
696 END IF; end loop
697 ;
698 */
699
700
701 EXCEPTION
702 WHEN OTHERS THEN
703 IF PG_DEBUG in ('Y', 'C') THEN
704 msc_sch_wb.atp_debug('get_supply_only_nodes: ' || ' Exception in get_next_level_nodes '||Substr(Sqlerrm, 1, 100));
705 END IF;
706 END get_supply_only_nodes;
707
708
709
710
711 END MSC_OWB_TREE;