[Home] [Help]
PACKAGE BODY: APPS.FLM_KANBAN
Source
4 FUNCTION number_of_days(sdate IN DATE, edate IN DATE, org_id IN NUMBER)
1 PACKAGE BODY flm_kanban AS
2 /* $Header: FLMKBNWB.pls 120.2 2006/06/30 00:20:53 ksuleman noship $ */
3
5 RETURN NUMBER;
6
7 PROCEDURE retrieve_schedules (i_locator_option NUMBER,
8 i_template_sub VARCHAR2,
9 i_default_locator_id NUMBER DEFAULT NULL,
10 i_org_id NUMBER,
11 i_line_id NUMBER,
12 i_standard_operation_id NUMBER,
13 i_operation_type NUMBER,
14 i_assembly IN assembly_t,
15 i_item_attributes IN VARCHAR2,
16 i_item_from IN VARCHAR2,
17 i_item_to IN VARCHAR2,
18 i_backflush_sub IN VARCHAR2,
19 i_cat_from IN VARCHAR2,
20 i_cat_to IN VARCHAR2,
21 i_category_set_id IN NUMBER,
25 o_error_msg OUT NOCOPY VARCHAR2) IS
22 i_category_structure_id IN NUMBER,
23 o_result OUT NOCOPY ret_sch_t,
24 o_error_num OUT NOCOPY NUMBER,
26
27 TYPE l_item_with_type IS RECORD (
28 item_id INTEGER,
29 type INTEGER,
30 subinv VARCHAR2(10),
31 loc_id INTEGER
32 );
33
34 TYPE l_item_with_type_t IS TABLE OF l_item_with_type
35 INDEX BY BINARY_INTEGER;
36
37 TYPE l_list_t IS TABLE OF INTEGER
38 INDEX BY BINARY_INTEGER;
39
40 l_where_clause VARCHAR2(2000) := NULL;
41 l_id_where_clause VARCHAR2(32767) := NULL;
42 l_item_where_clause VARCHAR2(2000) := NULL;
43 l_cat_where_clause VARCHAR2(2000) := NULL;
44 l_err_buf VARCHAR2(2000);
45 l_return BOOLEAN;
46 l_result l_list_t;
47
48 l_traced BOOLEAN;
49 l_subinv VARCHAR2(10);
50 l_subinv_1 VARCHAR2(10);
51 l_inv_item_id INTEGER;
52 l_loc_id INTEGER;
53 l_loc_id_1 INTEGER;
54
55 l_list l_list_t;
56
57 l_item_id_temp INTEGER;
58 l_wip_supply_type INTEGER;
59 l_subinv_temp VARCHAR2(10);
60 l_loc_id_temp INTEGER;
61 l_cnt INTEGER;
62 l_items l_item_with_type_t;
63 l_line_items l_item_with_type_t;
64 l_q_item_id INTEGER;
65 l_first INTEGER;
66 l_last INTEGER;
67 l_index INTEGER;
68 l_current INTEGER;
69
70 l_cursor INTEGER;
71 l_sql_stmt VARCHAR2(32767);
72 l_item_id INTEGER;
73 l_dummy INTEGER;
74
75 CURSOR get_pos(p_item_id NUMBER,p_subinv VARCHAR2,p_loc_Id NUMBER) IS
76 SELECT
77 source_subinventory,
78 source_locator_id
79 FROM
80 mtl_kanban_pull_sequences
81 WHERE
82 organization_id = i_org_id AND
83 kanban_plan_id = -1 AND
84 source_type = 3 AND
85 inventory_item_id = p_item_id AND
86 subinventory_name = p_subinv AND
87 nvl(locator_id,-1) = nvl(p_loc_id,-1);
88
89 CURSOR find_comps_primary IS
90 SELECT DISTINCT
91 bic.component_item_id,
92 decode(bic.wip_supply_type,null,msi.wip_supply_type,bic.wip_supply_type),
93 decode(bic.supply_subinventory,null,msi.wip_supply_subinventory,bic.supply_subinventory),
94 decode(bic.supply_subinventory,null,msi.wip_supply_locator_id,bic.supply_locator_id)
95 FROM bom_bill_of_materials bbom,
96 bom_inventory_components bic,
97 mtl_system_items msi
98 WHERE bbom.organization_id = i_org_id AND
99 bbom.alternate_bom_designator is null AND
100 bbom.assembly_item_id = l_q_item_id AND
101 bbom.common_bill_sequence_id = bic.bill_sequence_id AND
102 (6=decode(bic.wip_supply_type,null,msi.wip_supply_type,bic.wip_supply_type) OR
103 i_backflush_sub IS NULL OR
104 i_backflush_sub=decode(bic.supply_subinventory,null,msi.wip_supply_subinventory,bic.supply_subinventory)) AND
105 bic.effectivity_date < SYSDATE AND
106 (bic.disable_date > sysdate-1 or bic.disable_date is null) AND
107 msi.organization_id = i_org_id AND
108 msi.inventory_item_id = bic.component_item_id
109 ORDER BY bic.component_item_id;
110
111 CURSOR find_comps IS
112 SELECT DISTINCT
113 bic.component_item_id,
114 decode(bic.wip_supply_type,null,msi.wip_supply_type,bic.wip_supply_type),
115 decode(bic.supply_subinventory,null,msi.wip_supply_subinventory,bic.supply_subinventory),
116 decode(bic.supply_subinventory,null,msi.wip_supply_locator_id,bic.supply_locator_id)
117 FROM bom_bill_of_materials bbom,
118 bom_inventory_components bic,
119 mtl_system_items msi
120 WHERE bbom.organization_id = i_org_id AND
121 -- nvl(bbom.alternate_bom_designator, 'NONE') = nvl(l_alt, 'NONE') AND
122 bbom.assembly_item_id = l_q_item_id AND
123 bbom.common_bill_sequence_id = bic.bill_sequence_id AND
124 (6=decode(bic.wip_supply_type,null,msi.wip_supply_type,bic.wip_supply_type) OR
125 i_backflush_sub IS NULL OR
126 i_backflush_sub=decode(bic.supply_subinventory,null,msi.wip_supply_subinventory,bic.supply_subinventory)) AND
127 bic.effectivity_date < SYSDATE AND
128 (bic.disable_date > sysdate-1 or bic.disable_date is null) AND
129 msi.organization_id = i_org_id AND
130 msi.inventory_item_id = bic.component_item_id
131 ORDER BY bic.component_item_id;
132
133
134 CURSOR find_all_comps IS
135 SELECT DISTINCT
136 bic.component_item_id,
137 decode(bic.wip_supply_type,null,msi.wip_supply_type,bic.wip_supply_type),
138 decode(bic.supply_subinventory,null,msi.wip_supply_subinventory,bic.supply_subinventory),
139 decode(bic.supply_subinventory,null,msi.wip_supply_locator_id,bic.supply_locator_id)
140 FROM bom_bill_of_materials bbom,
141 bom_inventory_components bic,
142 mtl_system_items msi
143 WHERE bbom.organization_id = i_org_id AND
144 -- bbom.alternate_bom_designator IS NULL AND
145 bbom.common_bill_sequence_id = bic.bill_sequence_id AND
146 (6=decode(bic.wip_supply_type,null,msi.wip_supply_type,bic.wip_supply_type) OR
147 i_backflush_sub IS NULL OR
148 i_backflush_sub=decode(bic.supply_subinventory,null,msi.wip_supply_subinventory,bic.supply_subinventory)) AND
149 bic.effectivity_date < SYSDATE AND
150 (bic.disable_date > sysdate-1 or bic.disable_date is null) AND
151 msi.organization_id = i_org_id AND
152 msi.inventory_item_id = bic.component_item_id
153 ORDER BY bic.component_item_id;
154
155 FUNCTION Id_Where_Clause (
159 ) RETURN BOOLEAN IS
156 i_items IN l_item_with_type_t,
157 i_column_name IN VARCHAR2,
158 o_clause OUT NOCOPY VARCHAR2
160
161 l_first INTEGER;
162 l_last INTEGER;
163 l_index INTEGER;
164 BEGIN
165 -- DBMS_OUTPUT.PUT_LINE('Constructing Where Clause...');
166 IF i_items IS NULL OR i_items.COUNT <= 0 THEN
167 o_clause := NULL;
168 RETURN FALSE;
169 END IF;
170 l_first := i_items.FIRST;
171 l_last := i_items.LAST;
172 l_index := l_first;
173
174 o_clause := ' '||i_column_name||' IN (';
175
176 LOOP
177 o_clause := o_clause||l_items(l_index).item_id||', ';
178 --DBMS_OUTPUT.PUT_LINE(o_clause)
179 EXIT WHEN l_index = l_last;
180 l_index := i_items.NEXT(l_index);
181 END LOOP;
182 o_clause := o_clause||' -1)';
183 --DBMS_OUTPUT.PUT_LINE(length(o_clause));
184 RETURN TRUE;
185 EXCEPTION
186 WHEN OTHERS THEN
187 o_clause := NULL;
188 RETURN FALSE;
189 END;
190
191 ----------------------------------------------------------------------------
192 -- retrieve components for a line and/or a lineop(process)
193 -- if p_restrict = 0; no restriction, return p_line_items directly
194 -- if p_restrict = 1; restrict p_items by p_line_items
195 --
196 -- p_restrict is used for case when assembly is null, backflush_subinventory
197 -- is null and line_id is not null thus we don't retrieve all components but
198 -- only components for the line and/or lineop(process)
199 ----------------------------------------------------------------------------
200 procedure restrict_items_by_line(p_restrict IN number,
201 p_org_id IN number,
202 p_line_id IN number,
203 p_standard_operation_id IN number,
204 p_operation_type IN number,
205 p_items IN OUT NOCOPY l_item_with_type_t) is
206
207 p_line_items l_item_with_type_t;
208 no_items l_item_with_type_t;
209
210 l_cnt number;
211 l_index number;
212
213 i number;
214 j number;
215 k number;
216 pou_exists boolean;
217
218 TYPE T_COMPONENT IS RECORD (
219 TOP_ASSEMBLY_ITEM_ID NUMBER,
220 ORGANIZATION_ID NUMBER,
221 ASSEMBLY_ITEM_ID NUMBER,
222 ALTERNATE_BOM_DESIGNATOR VARCHAR2(10),
223 COMPONENT_ITEM_ID NUMBER,
224 OPERATION_SEQ_NUM NUMBER,
225 WIP_SUPPLY_TYPE NUMBER,
226 SUPPLY_SUBINVENTORY VARCHAR2(10),
227 SUPPLY_LOCATOR_ID NUMBER,
228 STD_LINEOP_ID NUMBER,
229 STD_PROCESS_ID NUMBER
230 );
231
232 TYPE T_COMPONENT_TBL IS TABLE OF T_COMPONENT INDEX BY BINARY_INTEGER;
233
234 G_COMPONENTS T_COMPONENT_TBL;
235
236 empty_component_list T_COMPONENT_TBL;
237
238 L_INHERIT_PHANTOM_OP_SEQ NUMBER(1);
239
240 CURSOR ALL_COMPONENTS IS
241 select DISTINCT
242 bom.assembly_item_id top_assembly_item_id,
243 bom.organization_id,
244 bom.assembly_item_id,
245 bom.alternate_bom_designator,
246 bic.component_item_id,
247 bic.operation_seq_num,
248 decode(bic.wip_supply_type, null, msi.wip_supply_type, bic.wip_supply_type),
249 decode(bic.supply_subinventory, null, msi.wip_supply_subinventory, bic.supply_subinventory),
250 decode(bic.supply_subinventory, null, msi.wip_supply_locator_id, bic.supply_locator_id),
251 -1,
252 -1
253 from
254 bom_bill_of_materials bom,
255 bom_operational_routings bor,
256 bom_inventory_components bic,
257 mtl_system_items msi
258 where
259 bom.organization_id = p_org_id and
260 bom.organization_id = bor.organization_id and
261 bom.assembly_item_id = bor.assembly_item_id and
262 ((bom.alternate_bom_designator = bor.alternate_routing_designator) or
263 (bom.alternate_bom_designator is null and bor.alternate_routing_designator is null)) and
264 bor.line_id = P_LINE_ID and
265 bom.common_bill_sequence_id = bic.bill_sequence_id and
266 bic.effectivity_date < sysdate and
267 (bic.disable_date > sysdate - 1 or bic.disable_date is null) and
268 -- bic.wip_supply_type in (2,3,6) and
269 msi.organization_id = p_org_id and
270 msi.inventory_item_id = bic.component_item_id
271 order by
272 bic.component_item_id;
273
274 CURSOR COMPONENTS(p_top_assembly_item_id number, p_org_id number, p_assembly_item_id number, p_alt varchar2) IS
275 select DISTINCT
276 p_top_assembly_item_id,
277 bom.organization_id,
278 bom.assembly_item_id,
279 bom.alternate_bom_designator,
280 bic.component_item_id,
281 bic.operation_seq_num,
282 decode(bic.wip_supply_type, null, msi.wip_supply_type, bic.wip_supply_type),
283 decode(bic.supply_subinventory, null, msi.wip_supply_subinventory, bic.supply_subinventory),
284 decode(bic.supply_subinventory, null, msi.wip_supply_locator_id, bic.supply_locator_id),
285 -1,
286 -1
287 from
288 bom_bill_of_materials bom,
289 bom_inventory_components bic,
290 mtl_system_items msi
291 where
292 bom.organization_id = p_org_id and
293 bom.assembly_item_id = p_assembly_item_id and
294 ((bom.alternate_bom_designator = p_alt) or
295 (bom.alternate_bom_designator is null and p_alt is null)) and
296 bom.common_bill_sequence_id = bic.bill_sequence_id and
297 bic.effectivity_date < sysdate and
298 (bic.disable_date > sysdate - 1 or bic.disable_date is null) and
299 -- bic.wip_supply_type in (2,3,6) and
300 msi.organization_id = p_org_id and
301 msi.inventory_item_id = bic.component_item_id
302 order by
303 bic.component_item_id;
304
305 Cursor get_std_lineop_id(p_org_id number, p_assembly_item_id number, p_alt varchar2, p_op_seq_num number) Is
306 Select
307 bos2.standard_operation_id
308 From
312 Where
309 bom_operational_routings bor,
310 bom_operation_sequences bos1,
311 bom_operation_sequences bos2
313 bor.organization_id = p_org_id and
314 bor.assembly_item_id = p_assembly_item_id and
315 nvl(bor.alternate_routing_designator,'NONE') = nvl(p_alt,'NONE') and
316 bos1.routing_sequence_id = bor.common_routing_sequence_id and
317 bos1.line_op_seq_id = bos2.operation_sequence_id and
318 bos2.operation_type = 3 and
319 bos1.operation_type = 1 and
320 bos1.operation_seq_num = p_op_seq_num and
321 bos1.effectivity_date < sysdate and
322 (bos1.disable_date > sysdate - 1 or bos1.disable_date is null);
323
324 Cursor get_std_process_id(p_org_id number, p_assembly_item_id number, p_alt varchar2, p_op_seq_num number) Is
325 Select
326 bos2.standard_operation_id
327 From
328 bom_operational_routings bor,
329 bom_operation_sequences bos1,
330 bom_operation_sequences bos2
331 Where
332 bor.organization_id = p_org_id and
333 bor.assembly_item_id = p_assembly_item_id and
334 nvl(bor.alternate_routing_designator,'NONE') = nvl(p_alt,'NONE') and
335 bos1.routing_sequence_id = bor.common_routing_sequence_id and
336 bos1.process_op_seq_id = bos2.operation_sequence_id and
337 bos2.operation_type = 2 and
338 bos1.operation_type = 1 and
339 bos1.operation_seq_num = p_op_seq_num and
340 bos1.effectivity_date < sysdate and
341 (bos1.disable_date > sysdate - 1 or bos1.disable_date is null);
342
343
344 BEGIN
345 select INHERIT_PHANTOM_OP_SEQ
346 into L_INHERIT_PHANTOM_OP_SEQ
347 from bom_parameters
348 where organization_id = p_org_id;
349
350 if (p_items.COUNT <= 0 and p_restrict = 1) then
351 return;
352 end if;
353
354 G_COMPONENTS := empty_component_list;
355 l_cnt := 0;
356 OPEN ALL_COMPONENTS;
357
358 LOOP
359 FETCH ALL_COMPONENTS INTO G_COMPONENTS(l_cnt);
360 EXIT WHEN ALL_COMPONENTS%NOTFOUND;
361
362 Open get_std_lineop_id(G_COMPONENTS(l_cnt).organization_id,G_COMPONENTS(l_cnt).top_assembly_item_id,
363 G_COMPONENTS(l_cnt).alternate_bom_designator,G_COMPONENTS(l_cnt).operation_seq_num);
364 Fetch get_std_lineop_id into G_COMPONENTS(l_cnt).std_lineop_id;
365 If (get_std_lineop_id%NOTFOUND) Then
366 G_COMPONENTS(l_cnt).std_lineop_id := -1;
367 End If;
368 Close get_std_lineop_id;
369
370 Open get_std_process_id(G_COMPONENTS(l_cnt).organization_id,G_COMPONENTS(l_cnt).top_assembly_item_id,
371 G_COMPONENTS(l_cnt).alternate_bom_designator,G_COMPONENTS(l_cnt).operation_seq_num);
372 Fetch get_std_process_id into G_COMPONENTS(l_cnt).std_process_id;
373 If (get_std_process_id%NOTFOUND) Then
374 G_COMPONENTS(l_cnt).std_process_id := -1;
375 End If;
376 Close get_std_process_id;
377
378 l_cnt := l_cnt + 1;
379 END LOOP;
380
381 CLOSE ALL_COMPONENTS;
382
383 -- explode all phantom-subassembly
384 l_index := 0;
385 WHILE l_index < l_cnt lOOP
386 if (G_COMPONENTS(l_index).wip_supply_type = 6) then -- phantom
387 OPEN COMPONENTS(G_COMPONENTS(l_index).top_assembly_item_id,
388 G_COMPONENTS(l_index).organization_id,
389 G_COMPONENTS(l_index).component_item_id,
390 NULL);
391
392 LOOP
393 FETCH COMPONENTS INTO G_COMPONENTS(l_cnt);
394 EXIT WHEN COMPONENTS%NOTFOUND;
395
396 if (L_INHERIT_PHANTOM_OP_SEQ = 1) then
397 G_COMPONENTS(l_cnt).operation_seq_num := G_COMPONENTS(l_index).operation_seq_num;
398 end if;
399
400
401 Open get_std_lineop_id(G_COMPONENTS(l_cnt).organization_id,G_COMPONENTS(l_cnt).top_assembly_item_id,
402 G_COMPONENTS(l_cnt).alternate_bom_designator,G_COMPONENTS(l_cnt).operation_seq_num);
403 Fetch get_std_lineop_id into G_COMPONENTS(l_cnt).std_lineop_id;
404 If (get_std_lineop_id%NOTFOUND) Then
405 G_COMPONENTS(l_cnt).std_lineop_id := -1;
406 End If;
407 Close get_std_lineop_id;
408
409 Open get_std_process_id(G_COMPONENTS(l_cnt).organization_id,G_COMPONENTS(l_cnt).top_assembly_item_id,
410 G_COMPONENTS(l_cnt).alternate_bom_designator,G_COMPONENTS(l_cnt).operation_seq_num);
411 Fetch get_std_process_id into G_COMPONENTS(l_cnt).std_process_id;
412 If (get_std_process_id%NOTFOUND) Then
413 G_COMPONENTS(l_cnt).std_process_id := -1;
414 End If;
415 Close get_std_process_id;
416
417
418 l_cnt := l_cnt + 1;
419 END LOOP;
420
421 CLOSE COMPONENTS;
422 end if;
423 l_index := l_index + 1;
424 END LOOP;
425
426 -- remove phantoms
427 l_index := 0;
428 WHILE l_index < l_cnt lOOP
429 if G_COMPONENTS(l_index).wip_supply_type = 6 then
430 G_COMPONENTS.DELETE(l_index);
431 end if;
432 l_index := l_index + 1;
433 END LOOP;
434
435 -- find items attached to the std_op
436 if (G_COMPONENTS.COUNT <= 0 and p_restrict = 1) then
437 p_items := no_items;
438 return;
439 end if;
440
441 l_cnt := 0;
442 i := G_COMPONENTS.FIRST;
443 j := G_COMPONENTS.LAST;
444
445 LOOP
446 if (p_standard_operation_id is null OR
447 (G_COMPONENTS(i).std_lineop_id = p_standard_operation_id and p_operation_type = 3) OR
448 (G_COMPONENTS(i).std_process_id = p_standard_operation_id and p_operation_type = 2)) then
449
450 pou_exists := false;
451 for k in 0..l_cnt-1 loop
452 if (p_line_items(k).item_id = G_COMPONENTS(i).component_item_id) and
453 (p_line_items(k).type = G_COMPONENTS(i).wip_supply_type) and
454 (nvl(p_line_items(k).subinv,'#?') = nvl(G_COMPONENTS(i).supply_subinventory,'#?')) and
458 exit when pou_exists;
455 (nvl(p_line_items(k).loc_id,-1) = nvl(G_COMPONENTS(i).supply_locator_id,-1)) then
456 pou_exists := true;
457 end if;
459 end loop;
460
461 if (not pou_exists) then
462 p_line_items(l_cnt).item_id := G_COMPONENTS(i).component_item_id;
463 p_line_items(l_cnt).type := G_COMPONENTS(i).wip_supply_type;
464 p_line_items(l_cnt).subinv := G_COMPONENTS(i).supply_subinventory;
465 p_line_items(l_cnt).loc_id := G_COMPONENTS(i).supply_locator_id;
466 l_cnt := l_cnt + 1;
467 end if;
468 end if;
469
470 EXIT WHEN i = j;
471 i := G_COMPONENTS.NEXT(i);
472 END LOOP;
473
474 if (p_line_items.COUNT <= 0) then
475 p_items := no_items;
476 return;
477 end if;
478
479 if (p_restrict = 0) then
480 p_items := p_line_items;
481 return;
482 end if;
483
484 l_index := 0;
485 j := p_items.LAST;
486
487 WHILE l_index < l_cnt LOOP
488 pou_exists := false;
489
490 i := p_items.FIRST;
491 loop
492 if (p_line_items(l_index).item_id = p_items(i).item_id) and
493 (nvl(p_line_items(l_index).subinv,'#?') = nvl(p_items(i).subinv,'#?')) and
494 (nvl(p_line_items(l_index).loc_id,-1) = nvl(p_items(i).loc_id,-1)) then
495 pou_exists := true;
496 end if;
497 exit when pou_exists OR i = j;
498 i := p_items.NEXT(i);
499 end loop;
500
501 if (not pou_exists) then
502 p_line_items.DELETE(l_index);
503 end if;
504
505 l_index := l_index + 1;
506 END LOOP;
507
508 p_items := p_line_items;
509
510 End;
511
512
513 BEGIN
514
515 o_error_num := 0;
516
517 flm_util.init_bind;
518 --
519 -- Find out all relevant items
520 --
521 l_cnt := 0;
522 IF (i_assembly IS NULL OR i_assembly.COUNT <= 0)
523 and (i_backflush_sub is not null or i_line_id is null)
524 THEN
525 -- DBMS_OUTPUT.PUT_LINE('Nothing Selected!');
526 OPEN find_all_comps;
527 LOOP
528 FETCH find_all_comps INTO l_item_id_temp, l_wip_supply_type,
529 l_subinv_temp, l_loc_id_temp;
530 EXIT WHEN find_all_comps%NOTFOUND;
531 --DBMS_OUTPUT.PUT_LINE(l_item_id_temp||' '||l_wip_supply_type);
532 l_items(l_cnt).item_id := l_item_id_temp;
533 l_items(l_cnt).type := l_wip_supply_type;
534 l_items(l_cnt).subinv := l_subinv_temp;
535 l_items(l_cnt).loc_id := l_loc_id_temp;
536 l_cnt := l_cnt+1;
537 END LOOP;
538 CLOSE find_all_comps;
539 -- DBMS_OUTPUT.PUT_LINE('Total: '||l_cnt);
540 ELSIF (i_assembly IS NOT NULL AND i_assembly.COUNT > 0) THEN -- i_assembly IS NULL
541 -- for all assembly-items passed in, find out their components
542 -- which fufill the basic requirements
543 l_first := i_assembly.FIRST;
544 l_last := i_assembly.LAST;
545 l_index := l_first;
546
547 LOOP
548 l_q_item_id := i_assembly(l_index);
549 OPEN find_comps;
550 LOOP
551 FETCH find_comps INTO l_item_id_temp, l_wip_supply_type,
552 l_subinv_temp, l_loc_id_temp;
553 EXIT WHEN find_comps%NOTFOUND;
554 l_items(l_cnt).item_id := l_item_id_temp;
555 l_items(l_cnt).type := l_wip_supply_type;
556 l_items(l_cnt).subinv := l_subinv_temp;
557 l_items(l_cnt).loc_id := l_loc_id_temp;
558 l_cnt := l_cnt+1;
559 END LOOP;
560 CLOSE find_comps;
561 IF l_index = l_last
562 THEN
563 EXIT;
564 END IF;
565 l_index := i_assembly.NEXT(l_index);
566 END LOOP;
567 -- repetitively find out all components of phantom-components
568 l_index := 0;
569 WHILE l_index < l_cnt LOOP
570 IF l_items(l_index).type = 6 -- phantom
571 THEN
572 l_q_item_id := l_items(l_index).item_id;
573 OPEN find_comps_primary;
574 LOOP
575
576 FETCH find_comps_primary INTO l_item_id_temp, l_wip_supply_type,
577 l_subinv_temp, l_loc_id_temp;
578 EXIT WHEN find_comps_primary%NOTFOUND;
579 l_items(l_cnt).item_id := l_item_id_temp;
580 l_items(l_cnt).type := l_wip_supply_type;
581 l_items(l_cnt).subinv := l_subinv_temp;
582 l_items(l_cnt).loc_id := l_loc_id_temp;
583 l_cnt := l_cnt+1;
584 END LOOP;
585 CLOSE find_comps_primary;
586 END IF;
587 l_index := l_index+1;
588 END LOOP;
589 END IF; -- i_assembly IS NULL
590
591 l_index := 0;
592 WHILE l_index < l_cnt LOOP
593 IF l_items(l_index).type = 6 -- phantom
594 THEN
595 l_items.DELETE(l_index);
596 END IF;
597 l_index := l_index+1;
598 END LOOP;
599
600
601 if (i_line_id is not null) then
602 if (i_assembly IS NULL OR i_assembly.COUNT <= 0) and
603 (i_backflush_sub is null) then
604 -- retrieve components by a line and/or a lineop(process) into l_items
605 restrict_items_by_line(0,
606 i_org_id,
607 i_line_id,
608 i_standard_operation_id,
609 i_operation_type,
610 l_items);
611 else
612 -- restrict components in l_items by a line and/or a lineop(process)
613 restrict_items_by_line(1,
614 i_org_id,
615 i_line_id,
616 i_standard_operation_id,
617 i_operation_type,
618 l_items);
619 end if;
620 end if;
621
622 -- construct the id-where-clause
626 'inventory_item_id',
623 if ( i_assembly.COUNT > 0 or i_backflush_sub is not null
624 or i_line_id is not null ) then
625 l_return := Id_Where_Clause(l_items,
627 l_id_where_clause);
628 end if;
629
630 -- construct the 'where' clause
631 IF i_item_from IS NOT NULL AND i_item_to IS NOT NULL
632 THEN
633 l_return := flm_util.Item_Where_Clause(
634 i_item_from,
635 i_item_to,
636 'msi',
637 l_item_where_clause,
638 l_err_buf);
639 END IF;
640 -- DBMS_OUTPUT.PUT_LINE('Item where clause: '||l_item_where_clause);
641
642 IF (i_cat_from IS NOT NULL OR i_cat_to IS NOT NULL) AND
643 i_category_set_id IS NOT NULL
644 THEN
645 l_return := flm_util.Category_Where_Clause(
646 i_cat_from,
647 i_cat_to,
648 'cat',
649 i_category_structure_id,
650 l_cat_where_clause,
651 l_err_buf);
652 l_cat_where_clause := ' msi.inventory_item_id IN (select '||
653 ' inventory_item_id from mtl_item_categories mic, '||
654 ' mtl_categories cat where ' ||
655 ' cat.category_id = mic.category_id' ||
656 ' AND mic.organization_id = :org_id' ||
657 ' AND mic.category_set_id = :category_set_id' ||
658 ' AND ' || l_cat_where_clause || ')';
659 ELSIF i_category_set_id IS NOT NULL
660 THEN
661 l_cat_where_clause := ' msi.inventory_item_id IN (select '||
662 ' inventory_item_id from mtl_item_categories mic ' ||
663 ' where mic.organization_id = :org_id' ||
664 ' AND mic.category_set_id = :category_set_id' || ')';
665 END IF;
666
667 flm_util.add_bind(':org_id', i_org_id);
668 flm_util.add_bind(':category_set_id', i_category_set_id);
669
670 -- DBMS_OUTPUT.PUT_LINE('Category where clause: '||l_cat_where_clause);
671
672
673
674 --
675 -- Construct SQL statement
676 --
677 l_cnt := 1;
678 -- DBMS_OUTPUT.PUT_LINE('Where Clause GENERATED:');
679 --DBMS_OUTPUT.PUT_LINE(l_id_where_clause);
680
681 l_sql_stmt := 'SELECT DISTINCT inventory_item_id ' ||
682 'FROM mtl_system_items msi ' ||
683 'WHERE organization_id = :org_id' || ' ';
684
685 flm_util.add_bind(':org_id', i_org_id);
686
687 IF l_id_where_clause IS NOT NULL
688 THEN
689 l_sql_stmt := l_sql_stmt || ' AND ' || l_id_where_clause;
690 END IF;
691 IF l_item_where_clause IS NOT NULL
692 THEN
693 l_sql_stmt := l_sql_stmt || ' AND ' || l_item_where_clause;
694 END IF;
695 IF l_cat_where_clause IS NOT NULL
696 THEN
697 l_sql_stmt := l_sql_stmt || ' AND ' || l_cat_where_clause;
698 END IF;
699 IF i_item_attributes IS NOT NULL
700 THEN
701 l_sql_stmt := l_sql_stmt || ' AND ' || i_item_attributes;
702 END IF;
703 -- DBMS_OUTPUT.PUT_LINE('SQL STATEMENT GENERATED:');
704 -- DBMS_OUTPUT.PUT_LINE(substr(l_sql_stmt, 1, 250));
705
706 l_cursor := DBMS_SQL.OPEN_CURSOR;
707 DBMS_SQL.PARSE(l_cursor, l_sql_stmt, DBMS_SQL.NATIVE);
708 DBMS_SQL.DEFINE_COLUMN(l_cursor, 1, l_item_id);
709
710 flm_util.do_binds(l_cursor);
711
712 l_dummy := DBMS_SQL.EXECUTE(l_cursor);
713 WHILE DBMS_SQL.FETCH_ROWS(l_cursor)>0 LOOP
714 DBMS_SQL.COLUMN_VALUE(l_cursor, 1, l_item_id);
715 l_result(l_item_id) := l_item_id;
716 --l_cnt := l_cnt+1;
717 END LOOP;
718 DBMS_SQL.CLOSE_CURSOR(l_cursor);
719
720 l_cnt := 0;
721
722 /***
723 *** Retrieve POU locators for items according to i_locator_options:
724 *** 1. BOM - locators are from bom_inventory_components;
725 *** 2. Pull Sequence - Starting from BOM (item_id, subinventory, locator_id),
726 *** trace down in mtl_kanban_pull_sequences, if the last POS has the same
727 *** subinventory as template (i_template_sub), retrieve its locator;
728 *** 3. Default - use specified default locator (i_pou_default_locator_id).
729 ***
730 *** Note that for all three options, items retrieved must not be phantom,
731 *** satisfy retrieve criteria, and has the same subinventory as template.
732 ***/
733
734 if (i_locator_option = 1) then
735 -- locator from BOM
736 l_first := l_items.FIRST;
737 l_last := l_items.LAST;
738 l_index := l_first;
739
740 LOOP
741 IF l_items(l_index).type <> 6 and
742 l_result.exists(l_items(l_index).item_id) and
743 l_items(l_index).subinv = i_template_sub
744 THEN
745 o_result(l_cnt).item_id := l_items(l_index).item_id;
746 o_result(l_cnt).locator_id := l_items(l_index).loc_id;
747 l_cnt := l_cnt + 1;
748 END IF;
749 EXIT WHEN l_index = l_last;
750 l_index := l_items.next(l_index);
751 END LOOP;
752
753 elsif (i_locator_option = 2) then
754 -- locator from existing pull sequences
755 l_first := l_items.FIRST;
756 l_last := l_items.LAST;
757 l_index := l_first;
758 l_dummy := 0;
759
760 LOOP
761 l_traced := FALSE;
762 l_dummy := 0;
763
764 IF l_items(l_index).type <> 6 and
765 l_result.exists(l_items(l_index).item_id)
766 THEN
767
768 l_inv_item_id := l_items(l_index).item_id;
769 l_subinv_1 := l_items(l_index).subinv;
770 l_loc_id_1 := l_items(l_index).loc_id;
771
772 LOOP
773 l_subinv := l_subinv_1;
774 l_loc_id := l_loc_id_1;
778 CLOSE get_pos;
775 OPEN get_pos(l_inv_item_id,l_subinv,l_loc_id);
776 FETCH get_pos into l_subinv_1, l_loc_id_1;
777 if get_pos%NOTFOUND then
779 EXIT;
780 end if;
781 l_traced := TRUE;
782 CLOSE get_pos;
783 l_dummy := l_dummy + 1;
784 EXIT When l_dummy >=99;
785
786 END LOOP;
787
788
789 if (l_traced) and (l_dummy < 99) and (l_subinv = i_template_sub) then
790 o_result(l_cnt).item_id := l_inv_item_id;
791 o_result(l_cnt).locator_id := l_loc_id;
792 l_cnt := l_cnt + 1;
793 end if;
794 END IF;
795 EXIT WHEN l_index = l_last;
796 l_index := l_items.next(l_index);
797 END LOOP;
798
799 elsif (i_locator_option = 3) then
800 -- locator is specified default
801 l_first := l_result.FIRST;
802 l_last := l_result.LAST;
803 l_index := l_first;
804 LOOP
805 o_result(l_cnt).item_id := l_result(l_index);
806 o_result(l_cnt).locator_id := i_default_locator_id;
807 l_cnt := l_cnt + 1;
808 EXIT WHEN l_index = l_last;
809 l_index := l_result.next(l_index);
810 END LOOP;
811
812 end if;
813
814 -- remove duplicate tuple(item_id, subinv, loc_id) from o_result ?
815
816
817 EXCEPTION
818 WHEN OTHERS THEN
819 o_error_num := 1;
820 RETURN;
821 END;
822
823
824 PROCEDURE demand_pegging_tree (i_pull_sequence_id IN NUMBER,
825 o_result OUT NOCOPY t_sres,
826 o_numdays OUT NOCOPY NUMBER,
827 o_error_num OUT NOCOPY NUMBER,
828 o_error_msg OUT NOCOPY VARCHAR2) IS
829
830 --
831 -- Some constants
832 --
833 l_max_strlen INTEGER := 550; -- Maximum Length of a String
834
835 --
836 -- The subinventory and location we want to explore
837 --
838 l_des_sub VARCHAR2(10);
839 l_des_loc VARCHAR2(204);
840 l_des_compid INTEGER;
841 l_kp_id INTEGER;
842 l_des_locid INTEGER;
843 l_org_id NUMBER;
844
845 -- A row
846 TYPE t_arow IS RECORD (
847 -- database fields
848 item VARCHAR2(40), -- name of the item
849 item_id INTEGER, -- id of the item
850 item_sub VARCHAR2(10), -- subinventory of the item
851 item_loc VARCHAR2(204), -- location of the item
852 de_item VARCHAR2(40), -- name of the demand item
853 de_item_id INTEGER, -- id of the demand item
854 de_item_sub VARCHAR2(10), -- subinventory of the demand item
855 de_item_loc VARCHAR2(204), -- location of the demand item
856 quantity INTEGER, -- quantity of the item
857 unit_qty INTEGER, -- unit quantity of item to build demand item
858 -- tree info
859 id INTEGER, -- row id
860 parent INTEGER, -- parent row id
861 child INTEGER, -- child row id
862 mid INTEGER, -- id merged to
863 tid INTEGER, -- id of the corresponding tree node
864 total_qty INTEGER, -- total quantity
865 child_qty INTEGER -- total child quantity
866 );
867
868 -- A result row
869 TYPE t_resrow IS RECORD (
870 id INTEGER, -- id of this node in the tree
871 item_id INTEGER, -- id of the item
872 item_name VARCHAR2(40), -- name of the item
873 quantity INTEGER, -- quantity of the item
874 parent INTEGER -- id of the parent node in this tree
875 );
876
877 -- Relevant table structure
878 TYPE t_relevant IS TABLE OF t_arow
879 INDEX BY BINARY_INTEGER;
880 -- Result table structure
881 TYPE t_res IS TABLE OF t_resrow
882 INDEX BY BINARY_INTEGER;
883 TYPE t_sres IS TABLE OF VARCHAR2(32767)
884 INDEX BY BINARY_INTEGER;
885
886 --
887 -- tables
888 --
889 l_relevant t_relevant;
890 l_res t_res;
891 l_resstring t_sres; -- the result in an array of string
892 l_numstring INTEGER; -- the number of result strings
893 l_numdays NUMBER; -- the total number of days
894
895 --
896 -- Temporary variables
897 --
898 l_item VARCHAR2(40);
899 l_item_id INTEGER;
900 l_item_sub VARCHAR2(10);
901 l_item_loc VARCHAR2(204);
902 l_de_item VARCHAR2(40);
903 l_de_item_id INTEGER;
904 l_de_item_sub VARCHAR2(10);
905 l_de_item_loc VARCHAR2(204);
906 l_quantity INTEGER;
907
908 l_cnt INTEGER;
909 l_total INTEGER;
910 l_j INTEGER;
911 l_k INTEGER;
912 l_sdate DATE;
913 l_edate DATE;
914
915 --
916 -- Cursors
917 --
918 CURSOR leaves IS
919 SELECT msi1.segment1 item,
920 mkd.inventory_item_id item_id,
921 mkd.subinventory item_sub,
922 substr(mil1.concatenated_segments, 0, 5) item_loc,
923 msi2.segment1 de_item,
924 mkd.assembly_item_id de_item_id,
925 mkd.assembly_subinventory de_item_sub,
926 substr(mil2.concatenated_segments, 0, 5) de_item_loc,
927 demand_quantity quantity
928 FROM mrp_kanban_demand mkd, mtl_system_items msi1, mtl_system_items msi2,
929 mtl_item_locations_kfv mil1, mtl_item_locations_kfv mil2
930 WHERE mkd.kanban_plan_id = l_kp_id
931 AND mkd.inventory_item_id = l_des_compid
932 AND mkd.subinventory = l_des_sub
933 AND (substr(mil1.concatenated_segments, 0, 5) = l_des_loc OR
934 (l_des_loc IS NULL AND
935 substr(mil1.concatenated_segments, 0, 5) IS NULL))
936 AND mkd.organization_id = msi1.organization_id
940 AND mkd.locator_id = mil1.inventory_location_id(+)
937 AND mkd.organization_id = msi2.organization_id
938 AND mkd.inventory_item_id = msi1.inventory_item_id
939 AND mkd.assembly_item_id = msi2.inventory_item_id
941 AND mkd.assembly_locator_id = mil2.inventory_location_id(+)
942 ORDER BY msi1.segment1, mkd.subinventory, msi2.segment1, mkd.assembly_subinventory;
943
944 CURSOR findchild IS
945 SELECT msi1.segment1,
946 mkd.inventory_item_id, mkd.subinventory,
947 substr(mil1.concatenated_segments, 0, 5),
948 msi2.segment1, mkd.assembly_item_id,
949 mkd.assembly_subinventory,
950 substr(mil2.concatenated_segments, 0, 5),
951 demand_quantity
952 FROM mrp_kanban_demand mkd, mtl_system_items msi1, mtl_system_items msi2,
953 mtl_item_locations_kfv mil1, mtl_item_locations_kfv mil2
954 WHERE mkd.kanban_plan_id = l_kp_id
955 AND mkd.organization_id = msi1.organization_id
956 AND mkd.organization_id = msi2.organization_id
957 AND mkd.inventory_item_id = msi1.inventory_item_id
958 AND mkd.assembly_item_id = msi2.inventory_item_id
959 AND mkd.locator_id = mil1.inventory_location_id(+)
960 AND mkd.assembly_locator_id = mil2.inventory_location_id(+)
961 AND mkd.inventory_item_id = l_relevant(l_cnt).de_item_id -- id match
962 AND (mkd.subinventory = l_relevant(l_cnt).de_item_sub OR
963 ((mkd.subinventory IS NULL) AND
964 (l_relevant(l_cnt).de_item_sub IS NULL)))
965 AND (substr(mil1.concatenated_segments, 0, 5) = l_relevant(l_cnt).de_item_loc OR
966 ((mil1.concatenated_segments IS NULL) AND
967 (l_relevant(l_cnt).de_item_loc IS NULL)))
968 AND (demand_quantity*l_relevant(l_cnt).unit_qty)=l_relevant(l_cnt).quantity; -- quantity match
969
970 FUNCTION mergable (
971 r1 INTEGER,
972 r2 INTEGER,
973 t t_relevant
974 )RETURN BOOLEAN IS
975 BEGIN
976 -- not for same item? return false
977 IF t(r1).item_id <> t(r2).item_id OR
978 t(r1).item_sub <> t(r2).item_sub OR
979 t(r1).item_loc <> t(r2).item_loc
980 THEN
981 RETURN FALSE;
982 END IF;
983 -- both on first level? return true
984 IF (t(r1).parent<0) AND (t(r2).parent<0)
985 THEN
986 RETURN TRUE;
987 END IF;
988 -- not same level? return false
989 IF (t(r1).parent<0) OR (t(r2).parent<0)
990 THEN
991 RETURN FALSE;
992 END IF;
993 -- if parents mergable, then mergable
994 RETURN mergable(t(r1).parent, t(r2).parent, t);
995 END;
996
997 --
998 -- Put the result tree into a string/multiple strings, which
999 -- is taken as a message/multiple messages to transmit in Oracle
1000 -- Form environment.
1001 -- Return the number of strings
1002 --
1003 -- Format used:
1004 -- RES := 'COMMAND/INIT/OTHER/TOTAL/SEQN/'ROW['@'ROW]*'/'
1005 -- ROW := ID'%'ITEM_ID'%'ITEM_NAME'%'QUANTITY'%'PARENT
1006 -- TOTAL := [0-9]+
1007 -- SEQN := [0-9]+
1008 -- ID := [0-9]+
1009 -- ITEM_ID := [0-9]+
1010 -- ITEM_NAME := [A-Z|a-z|0-9|' '|'_'|'.']+
1011 -- QUANTITY := [0-9]+
1012 -- PARENT := [-][0-9]+
1013 --
1014 FUNCTION toStrings (
1015 tree IN t_res,
1016 numnode IN INTEGER,
1017 sres OUT NOCOPY t_sres
1018 ) RETURN INTEGER IS
1019 l_unit_len INTEGER := 50;
1020 l_unit_num INTEGER;
1021 l_total INTEGER := 0;
1022 l_j INTEGER;
1023 l_k INTEGER;
1024 l_up INTEGER;
1025 BEGIN
1026 -- nothing?
1027 IF numnode <= 0
1028 THEN
1029 RETURN 0;
1030 END IF;
1031 -- total number of strings
1032 l_unit_num := (l_max_strlen/l_unit_len)-2;
1033 l_total := CEIL(numnode/l_unit_num);
1034 --put strings
1035 FOR l_j IN 0..l_total-1 LOOP
1036 -- sres(l_j) := 'COMMAND/INIT/OTHER/'||l_total||'/'||l_j||'/';
1037 sres(l_j) := l_total||'/'||l_j||'/';
1038 IF l_j >= l_total-1 -- last one?
1039 THEN
1040 l_up := numnode;
1041 ELSE
1042 l_up := (l_j+1)*l_unit_num;
1043 END IF;
1044 FOR l_k IN l_j*l_unit_num..l_up-1 LOOP
1045 sres(l_j) := sres(l_j) || tree(l_k).id || '%';
1046 sres(l_j) := sres(l_j) || tree(l_k).item_id || '%';
1047 sres(l_j) := sres(l_j) || tree(l_k).item_name || '%';
1048 sres(l_j) := sres(l_j) || tree(l_k).quantity || '%';
1049 sres(l_j) := sres(l_j) || tree(l_k).parent;
1050 IF l_k >= l_up-1
1051 THEN
1052 sres(l_j) := sres(l_j) || '/';
1053 ELSE
1054 sres(l_j) := sres(l_j) || '@';
1055 END IF;
1056 END LOOP;
1057 END LOOP;
1058 RETURN l_total;
1059 END;
1060
1061 BEGIN
1062
1063 -- find out the kanban-plan-id, subinventory, locator-id, item, and organization id
1064 SELECT kanban_plan_id, subinventory_name, locator_id, inventory_item_id, organization_id
1065 INTO l_kp_id, l_des_sub, l_des_locid, l_des_compid, l_org_id
1066 FROM mtl_kanban_pull_sequences
1067 WHERE pull_sequence_id = i_pull_sequence_id;
1068
1069 IF NOT l_des_locid IS NULL
1070 THEN
1071 SELECT substr(concatenated_segments, 0, 5)
1072 INTO l_des_loc
1073 FROM mtl_item_locations_kfv
1074 WHERE inventory_location_id = l_des_locid;
1075 END IF;
1076
1077 --
1078 -- Get the total number of days
1079 --
1080 SELECT plan_start_date, plan_cutoff_date
1081 INTO l_sdate, l_edate
1082 FROM mrp_kanban_plans
1086
1083 WHERE kanban_plan_id = l_kp_id;
1084
1085 l_numdays := number_of_days(l_sdate, l_edate, l_org_id);
1087 --
1088 -- Fetch those leaves(items whose sub and loc are as indicated).
1089 --
1090 l_cnt := 0;
1091
1092 OPEN leaves;
1093
1094 LOOP
1095 -- get next row
1096 FETCH leaves INTO l_item, l_item_id, l_item_sub, l_item_loc, l_de_item, l_de_item_id, l_de_item_sub, l_de_item_loc, l_quantity;
1097 EXIT WHEN leaves%NOTFOUND;
1098 -- put it into the res
1099 l_relevant(l_cnt).item := l_item;
1100 l_relevant(l_cnt).item_id := l_item_id;
1101 l_relevant(l_cnt).item_sub := l_item_sub;
1102 l_relevant(l_cnt).item_loc := l_item_loc;
1103 l_relevant(l_cnt).de_item := l_de_item;
1104 l_relevant(l_cnt).de_item_id := l_de_item_id;
1105 l_relevant(l_cnt).de_item_sub := l_de_item_sub;
1106 l_relevant(l_cnt).de_item_loc := l_de_item_loc;
1107 l_relevant(l_cnt).quantity := l_quantity;
1108 l_relevant(l_cnt).id := l_cnt;
1109 l_relevant(l_cnt).parent := -1;
1110 l_relevant(l_cnt).child := -1;
1111 l_relevant(l_cnt).mid := l_cnt;
1112 l_cnt := l_cnt+1;
1113 END LOOP;
1114
1115 CLOSE leaves;
1116
1117 --
1118 -- Processing
1119 --
1120
1121 -- find the unit quantity
1122 l_total := l_cnt;
1123 l_cnt := 0;
1124 FOR l_j IN 0..l_total-1 LOOP
1125 IF l_relevant(l_j).item_id = l_relevant(l_j).de_item_id
1126 THEN
1127 -- same item
1128 l_relevant(l_j).unit_qty := 1;
1129 ELSE
1130 -- find it in database
1131 SELECT sum(bic.component_quantity)
1132 INTO l_relevant(l_j).unit_qty
1133 FROM bom_inventory_components bic, bom_bill_of_materials bbom
1134 WHERE l_relevant(l_j).de_item_id = bbom.assembly_item_id
1135 and bbom.organization_id = l_org_id
1136 and bbom.alternate_bom_designator is null
1137 AND bbom.bill_sequence_id = bic.bill_sequence_id
1138 AND bic.component_item_id = l_relevant(l_j).item_id
1139 AND bic.supply_subinventory = l_relevant(l_j).item_sub;
1140 --DBMS_OUTPUT.PUT_LINE('UNIT_qty: '||l_relevant(l_j).unit_qty);
1141 END IF;
1142 END LOOP;
1143
1144 -- repetitively find children
1145 LOOP
1146 EXIT WHEN l_cnt >= l_total;
1147 -- find the child
1148 OPEN findchild;
1149 FETCH findchild INTO l_relevant(l_total).item, l_relevant(l_total).item_id,
1150 l_relevant(l_total).item_sub, l_relevant(l_total).item_loc,
1151 l_relevant(l_total).de_item, l_relevant(l_total).de_item_id,
1152 l_relevant(l_total).de_item_sub, l_relevant(l_total).de_item_loc,
1153 l_relevant(l_total).quantity;
1154 IF findchild%FOUND
1155 THEN
1156 -- unit quantity
1157 IF l_relevant(l_total).item_id = l_relevant(l_total).de_item_id
1158 THEN
1159 -- same item
1160 l_relevant(l_total).unit_qty := 1;
1161 ELSE
1162 select sum(bic.COMPONENT_QUANTITY)
1163 into l_relevant(l_total).unit_qty
1164 from BOM_INVENTORY_COMPONENTS bic, BOM_BILL_OF_MATERIALS bbom
1165 where l_relevant(l_total).de_item_id = bbom.ASSEMBLY_ITEM_ID
1166 and bbom.organization_id = l_org_id
1167 and bbom.alternate_bom_designator is null
1168 AND bbom.BILL_SEQUENCE_ID = bic.BILL_SEQUENCE_ID
1169 AND bic.COMPONENT_ITEM_ID = l_relevant(l_total).item_id
1170 AND (bic.SUPPLY_SUBINVENTORY = l_relevant(l_total).item_sub OR
1171 ((bic.SUPPLY_SUBINVENTORY IS NULL) AND
1172 (l_relevant(l_total).item_sub IS NULL)));
1173 END IF;
1174 -- id, parent ...
1175 l_relevant(l_total).id := l_total;
1176 l_relevant(l_total).parent := l_relevant(l_cnt).id;
1177 l_relevant(l_total).child := -1;
1178 l_relevant(l_cnt).child := l_total;
1179 l_relevant(l_total).mid := l_relevant(l_total).id;
1180 -- increase total
1181 l_total := l_total+1;
1182 END IF;
1183 CLOSE findchild;
1184 l_cnt := l_cnt+1;
1185 END LOOP;
1186
1187 -- change the quantity to that of the corresponding leaf component
1188 FOR l_j IN 0..l_total-1 LOOP
1189 IF l_relevant(l_j).parent >= 0
1190 THEN
1191 l_relevant(l_j).quantity := l_relevant(l_relevant(l_j).parent).quantity;
1192 END IF;
1193 END LOOP;
1194 -- set total quantity and child quantity to quantity
1195 FOR l_j IN 0..l_total-1 LOOP
1196 l_relevant(l_j).total_qty := l_relevant(l_j).quantity;
1197 l_relevant(l_j).child_qty := l_relevant(l_j).quantity;
1198 END LOOP;
1199
1200 -- merging...
1201 FOR l_j IN 1..l_total-1 LOOP
1202 FOR l_k IN 0..l_j-1 LOOP
1203 IF mergable(l_j, l_k, l_relevant)
1204 THEN
1205 -- merge them
1206 l_relevant(l_k).total_qty := l_relevant(l_k).total_qty+l_relevant(l_j).quantity;
1207 l_relevant(l_j).mid := l_relevant(l_k).mid;
1208 -- leaf node?
1209 IF l_relevant(l_k).child < 0 AND
1210 l_relevant(l_k).de_item_id = l_relevant(l_j).de_item_id AND
1211 l_relevant(l_k).child_qty >= 0
1212 --l_relevant(l_k).de_item_sub = l_relevant(l_j).de_item_sub AND
1213 --l_relevant(l_k).de_item_loc = l_relevant(l_j).de_item_loc
1214 THEN
1215 l_relevant(l_k).child_qty := l_relevant(l_k).child_qty+l_relevant(l_j).child_qty;
1216 l_relevant(l_j).child_qty := -1;
1217 END IF;
1218 END IF;
1219 END LOOP;
1220 END LOOP;
1221
1222 --
1223 -- build the tree
1224 --
1225 -- root
1226 l_res(0).id := 0;
1227 l_res(0).item_id := 0;
1231
1228 l_res(0).item_name := l_des_sub||' '||l_des_loc;
1229 l_res(0).quantity := 0;
1230 l_res(0).parent := -1;
1232 l_cnt := 1;
1233
1234 FOR l_j IN 0..l_total-1 LOOP
1235 -- not a merged one?
1236 IF l_relevant(l_j).id = l_relevant(l_j).mid
1237 THEN
1238 -- add it to the result tree
1239 l_res(l_cnt).id := l_cnt;
1240 l_res(l_cnt).item_id := l_relevant(l_j).item_id;
1241 IF l_relevant(l_j).parent < 0
1242 THEN
1243 l_res(l_cnt).item_name := l_relevant(l_j).item;
1244 ELSE
1245 IF l_relevant(l_j).item_id <> l_relevant(l_relevant(l_j).parent).item_id
1246 THEN
1247 l_res(l_cnt).item_name := l_relevant(l_j).item;
1248 ELSE
1249 l_res(l_cnt).item_name := l_relevant(l_j).item_sub||' '||l_relevant(l_j).item_loc;
1250 END IF;
1251 END IF;
1252 l_res(l_cnt).quantity := l_relevant(l_j).total_qty;
1253 IF l_relevant(l_j).parent < 0
1254 THEN
1255 l_res(l_cnt).parent := 0;
1256 ELSE
1257 l_res(l_cnt).parent := l_relevant(l_relevant(l_relevant(l_j).parent).mid).tid;
1258 END IF;
1259 l_relevant(l_j).tid := l_cnt;
1260 l_cnt := l_cnt+1;
1261 END IF;
1262 --leaf item?
1263 IF l_relevant(l_j).child < 0 AND
1264 l_relevant(l_j).child_qty > 0
1265 THEN
1266 l_res(l_cnt).id := l_cnt;
1267 l_res(l_cnt).item_id := l_relevant(l_j).de_item_id;
1268 IF l_relevant(l_j).de_item_id <> l_relevant(l_j).item_id
1269 THEN
1270 l_res(l_cnt).item_name := l_relevant(l_j).de_item;
1271 ELSE
1272 l_res(l_cnt).item_name := l_relevant(l_j).de_item_sub||' '||l_relevant(l_j).de_item_loc;
1273 END IF;
1274 l_res(l_cnt).quantity := l_relevant(l_j).child_qty;
1275 l_res(l_cnt).parent := l_relevant(l_relevant(l_j).mid).tid;
1276 l_cnt := l_cnt+1;
1277 END IF;
1278 END LOOP;
1279
1280 --
1281 -- print result
1282 --
1283
1284 --output the result to a string/multiple strings
1285 -- DBMS_OUTPUT.PUT_LINE('TOTAL: '||l_cnt);
1286 l_numstring := toStrings(l_res, l_cnt, l_resstring);
1287 FOR l_j IN 0..l_numstring-1 LOOP
1288 -- DBMS_OUTPUT.PUT_LINE(l_resstring(l_j));
1289 o_result(l_j) := l_resstring(l_j);
1290 END LOOP;
1291 o_numdays := l_numdays;
1292
1293
1294 --
1295 -- Send Message
1296 --
1297
1298 -- To Be Finished: send out all strings in l_resstring, each as a message
1299
1300 EXCEPTION
1301 WHEN OTHERS THEN
1302 -- DBMS_OUTPUT.PUT_LINE(SQLERRM);
1303 -- DBMS_OUTPUT.PUT_LINE(SQLCODE);
1304 null;
1305 END;
1306
1307 PROCEDURE demand_graph (i_pull_sequence_id IN NUMBER,
1308 o_result OUT NOCOPY t_sres,
1309 o_error_num OUT NOCOPY NUMBER,
1310 o_error_msg OUT NOCOPY VARCHAR2) IS
1311 --
1312 -- Some constants
1313 --
1314 l_max_strlen INTEGER := 550; -- Maximum Length of a String
1315
1316 --
1317 -- The subinventory, location, component, and dates we want to explore
1318 --
1319 l_des_sub VARCHAR2(40);
1320 l_des_loc VARCHAR2(204);
1321 l_des_comp VARCHAR2(40);
1322 l_des_locid INTEGER;
1323 l_des_compid INTEGER;
1324 l_kp_id INTEGER;
1325 l_org_id INTEGER;
1326
1327 --
1328 -- Types
1329 --
1330 TYPE t_arow IS RECORD (
1331 item VARCHAR2(40),
1332 item_id INTEGER,
1333 item_sub VARCHAR2(10),
1334 item_loc VARCHAR2(204),
1335 de_date DATE,
1336 quantity INTEGER
1337 );
1338
1339 TYPE t_quan IS RECORD (
1340 ddate DATE,
1341 quantity INTEGER
1342 );
1343
1344 TYPE t_data IS TABLE OF t_arow
1345 INDEX BY BINARY_INTEGER;
1346
1347 TYPE t_res IS TABLE OF t_quan
1348 INDEX BY BINARY_INTEGER;
1349
1350 --
1351 -- Variables
1352 --
1353 l_res t_res;
1354 l_sres t_sres;
1355 l_numstring INTEGER;
1356
1357 l_arow t_arow;
1358 l_numrec INTEGER;
1359 l_j INTEGER;
1360 l_sdate DATE;
1361 l_edate DATE;
1362
1363 --
1364 -- Cursors
1365 --
1366 CURSOR relevant IS
1367 SELECT msi.segment1 item,
1368 mkd.inventory_item_id item_id,
1369 mkd.subinventory item_sub,
1370 substr(mil.concatenated_segments, 0, 5) item_loc,
1371 trunc(demand_date) d,
1372 demand_quantity quantity
1373 FROM mrp_kanban_demand mkd, mtl_system_items msi,
1374 mtl_item_locations_kfv mil
1375 WHERE mkd.kanban_plan_id = l_kp_id
1376 AND (mkd.subinventory = l_des_sub OR
1377 (mkd.subinventory IS NULL AND
1378 l_des_sub IS NULL))
1379 AND (substr(mil.concatenated_segments, 0, 5) = l_des_loc OR
1380 (l_des_loc IS NULL AND
1381 substr(mil.concatenated_segments, 0, 5) IS NULL))
1382 AND mkd.organization_id = msi.organization_id
1383 AND mkd.inventory_item_id = msi.inventory_item_id
1384 AND msi.segment1 = l_des_comp
1385 AND mkd.locator_id = mil.inventory_location_id(+)
1386 ORDER BY demand_date, msi.segment1, mkd.subinventory;
1387
1388
1389 --
1390 -- Put the result tree into a string/multiple strings, which
1391 -- is taken as a message/multiple messages to transmit in Oracle
1392 -- Form environment.
1393 -- Return the number of strings
1394 --
1395 -- Format used:
1396 -- RES := 'COMMAND/INIT/OTHER/TOTAL/SEQN/[SUB/LOC/COMP/SDATE/EDATE]'
1397 -- ROW['@'ROW]*'/'
1398 -- ROW := DATE'%'QUANTITY
1399 -- TOTAL := [0-9]+
1400 -- SEQN := [0-9]+
1404 -- QUANTITY := [0-9]+
1401 -- SDATE := DATE
1402 -- EDATE := DATE
1403 -- DATE := MMDDYYYY
1405 -- SUB := IDENTIFIER
1406 -- LOC := IDENTIFIER
1407 -- COMP:= IDENTIFIER
1408 -- IDENTIFIER := ['A'-'Z'|'a'-'z'|'0'-'9'|'_'|'.']+
1409 --
1410 FUNCTION toStrings (
1411 list IN t_res,
1412 numitem IN INTEGER,
1413 sres OUT NOCOPY t_sres
1414 ) RETURN INTEGER IS
1415 l_unit_len INTEGER := 30;
1416 l_unit_num INTEGER;
1417 l_total INTEGER := 0;
1418 l_j INTEGER;
1419 l_k INTEGER;
1420 l_up INTEGER;
1421 BEGIN
1422 -- nothing?
1423 IF numitem <= 0
1424 THEN
1425 RETURN 0;
1426 END IF;
1427 -- total number of strings
1428 l_unit_num := (l_max_strlen/l_unit_len)-2;
1429 l_total := CEIL(numitem/l_unit_num);
1430 --put strings
1431 FOR l_j IN 0..l_total-1 LOOP
1432 -- sres(l_j) := 'COMMAND/INIT/OTHER/'||l_total||'/'||l_j||'/';
1433 sres(l_j) := l_total||'/'||l_j||'/';
1434 IF l_j = 0 -- first one?
1435 THEN
1436 sres(l_j) := sres(l_j)||l_des_sub||'/'||l_des_loc||'/'||l_des_comp||
1437 '/'||TO_CHAR(l_sdate, 'MMDDYYYY')||'/'||
1438 TO_CHAR(l_edate, 'MMDDYYYY')||'/';
1439 END IF;
1440 IF l_j >= l_total-1 -- last one?
1441 THEN
1442 l_up := numitem;
1443 ELSE
1444 l_up := (l_j+1)*l_unit_num;
1445 END IF;
1446 FOR l_k IN l_j*l_unit_num..l_up-1 LOOP
1447 sres(l_j) := sres(l_j) || TO_CHAR(list(l_k).ddate, 'MMDDYYYY') || '%';
1448 sres(l_j) := sres(l_j) || list(l_k).quantity || '%';
1449 IF l_k >= l_up-1
1450 THEN
1451 sres(l_j) := sres(l_j) || '/';
1452 ELSE
1453 sres(l_j) := sres(l_j) || '@';
1454 END IF;
1455 END LOOP;
1456 END LOOP;
1457 RETURN l_total;
1458 END;
1459
1460 BEGIN
1461
1462 -- find out the kanban-plan-id, subinventory, locator-id, and item
1463 SELECT kanban_plan_id, subinventory_name, locator_id, inventory_item_id, organization_id
1464 INTO l_kp_id, l_des_sub, l_des_locid, l_des_compid, l_org_id
1465 FROM mtl_kanban_pull_sequences
1466 WHERE pull_sequence_id = i_pull_sequence_id;
1467
1468 -- DBMS_OUTPUT.PUT_LINE(l_kp_id || l_des_sub || l_des_locid || l_des_compid);
1469
1470 -- find out the start-date and end-date
1471 --SELECT plan_start_date, plan_cutoff_date
1472 --Added nvl for start/end dates by ks for bug 3883026
1473 SELECT nvl(plan_start_date,sysdate), nvl(plan_cutoff_date,sysdate)
1474 INTO l_sdate, l_edate
1475 FROM mrp_kanban_plans
1476 WHERE kanban_plan_id = l_kp_id;
1477
1478 SELECT DISTINCT segment1
1479 INTO l_des_comp
1480 FROM mtl_system_items
1481 WHERE inventory_item_id = l_des_compid AND
1482 organization_id = l_org_id;
1483
1484 IF NOT l_des_locid IS NULL
1485 THEN
1486 SELECT substr(concatenated_segments, 0, 5)
1487 INTO l_des_loc
1488 FROM mtl_item_locations_kfv
1489 WHERE inventory_location_id = l_des_locid;
1490 END IF;
1491
1492 -- retrieve data from database and process it
1493 OPEN relevant;
1494 l_numrec := 1;
1495 l_res(0).ddate := l_sdate;
1496 l_res(0).quantity := 0;
1497 LOOP
1498 FETCH relevant INTO l_arow;
1499 EXIT WHEN relevant%NOTFOUND;
1500 /* IF l_numrec = 0 OR
1501 l_res(l_numrec-1).ddate <> l_arow.de_date
1502 THEN
1503 -- a new one
1504 l_numrec := l_numrec+1;
1505 l_res(l_numrec-1).quantity := 0;
1506 END IF;*/
1507
1508 WHILE l_res(l_numrec-1).ddate < l_arow.de_date
1509 LOOP
1510 -- a new one
1511 l_numrec := l_numrec+1;
1512 l_res(l_numrec-1).quantity := 0;
1513 IF l_numrec <= 1
1514 THEN
1515 l_res(l_numrec-1).ddate := l_arow.de_date;
1516 ELSE
1517 l_res(l_numrec-1).ddate := mrp_calendar.next_work_day(l_org_id, 1, l_res(l_numrec-2).ddate+1);
1518 END IF;
1519 END LOOP;
1520
1521 l_res(l_numrec-1).ddate := l_arow.de_date;
1522 l_res(l_numrec-1).quantity := l_res(l_numrec-1).quantity + l_arow.quantity;
1523 --DBMS_OUTPUT.PUT_LINE(l_arow.item||' '||l_arow.item_sub||' '||l_arow.item_loc||' '||l_arow.de_date||' '||l_arow.quantity);
1524 END LOOP;
1525 WHILE l_res(l_numrec-1).ddate < l_edate
1526 LOOP
1527 l_numrec := l_numrec+1;
1528 l_res(l_numrec-1).ddate := mrp_calendar.next_work_day(l_org_id, 1, l_res(l_numrec-2).ddate+1);
1529 l_res(l_numrec-1).quantity := 0;
1530 END LOOP;
1531 CLOSE relevant;
1532
1533 --
1534 -- put result into string(s)
1535 --
1536 l_numstring := toStrings(l_res, l_numrec, l_sres);
1537 FOR l_j IN 0..l_numstring-1 LOOP
1538 -- DBMS_OUTPUT.PUT_LINE(l_sres(l_j));
1539 o_result(l_j) := l_sres(l_j);
1540 END LOOP;
1541
1542 --
1543 -- Send Message
1544 --
1545
1546 -- To Be Finished: send out all strings in l_sres, each as a message
1547
1548 END demand_graph;
1549
1550 FUNCTION Plan_Prod_Same_Pos ( p_plan_pull_seq_id NUMBER
1551 ,p_prod_pull_seq_id NUMBER)
1552 Return BOOLEAN
1553 IS
1554 l_plan_Rec INV_Kanban_Pvt.Pull_sequence_Rec_Type;
1555 l_prod_Rec INV_Kanban_Pvt.Pull_sequence_Rec_Type;
1556 l_same_pos number:=0;
1557 BEGIN
1558 l_plan_Rec := INV_PullSequence_PKG.query_row(p_plan_pull_seq_id);
1559 l_prod_Rec := INV_PullSequence_PKG.query_row(p_prod_pull_seq_id);
1560
1561 IF(l_plan_Rec.source_type = l_prod_Rec.source_type ) THEN
1562
1563 IF (l_plan_Rec.source_type = G_Source_Type_InterOrg) THEN /*Inter org*/
1564 IF (l_plan_rec.source_organization_id = l_prod_Rec.source_organization_id) THEN
1565 IF( (l_plan_rec.source_subinventory = l_prod_rec.source_subinventory)
1566 AND (nvl(l_plan_rec.source_locator_id,-1) = nvl(l_prod_rec.source_locator_id,-1))) THEN
1567 return true;
1568 END IF;
1569 END IF;
1570 ELSIF (l_plan_Rec.source_type = G_Source_Type_Supplier) THEN /*Supplier*/
1571 --IF( (l_plan_rec.supplier_id = l_prod_rec.supplier_id)
1572 IF( (nvl(l_plan_rec.supplier_id,-1) = nvl(l_prod_rec.supplier_id,-1)) --bug 5156587
1573 AND (nvl(l_plan_rec.supplier_site_id,-1) = nvl(l_prod_rec.supplier_site_id,-1))) THEN
1574 return true;
1575 end if;
1576 ELSIF (l_plan_Rec.source_type = G_Source_Type_IntraOrg) THEN /*Intra org*/
1577 IF( (l_plan_rec.source_subinventory = l_prod_rec.source_subinventory)
1578 AND (nvl(l_plan_rec.source_locator_id,-1) = nvl(l_prod_rec.source_locator_id,-1))) THEN
1579 return true;
1580 END IF;
1581 ELSE /*Production*/
1582 --IF( l_plan_rec.wip_line_id = l_prod_rec.wip_line_id) THEN
1583 IF( nvl(l_plan_rec.wip_line_id,-1) = nvl(l_prod_rec.wip_line_id,-1)) THEN --bug 5156587
1584 return true;
1585 END IF;
1586 END IF;
1587 END IF;
1588 return false;
1589
1590 END Plan_Prod_Same_Pos;
1591
1592 FUNCTION number_of_days(sdate IN DATE, edate IN DATE, org_id IN NUMBER)
1593 RETURN NUMBER IS
1594 l_result NUMBER := 1;
1595 l_d1 DATE;
1596 BEGIN
1597 l_d1 := sdate;
1598 WHILE l_d1 < edate LOOP
1599 l_d1 := mrp_calendar.next_work_day(org_id, 1, l_d1+1);
1600 l_result := l_result+1;
1601 END LOOP;
1602 RETURN l_result;
1603 EXCEPTION
1604 WHEN OTHERS THEN
1605 RETURN 1;
1606 END number_of_days;
1607
1608
1609 END flm_kanban;