1 PACKAGE BODY inv_object_genealogy AS
2 /* $Header: INVOGENB.pls 120.10 2008/02/06 12:10:55 rkatoori ship $ */
3 FUNCTION getobjecttype(p_object_id IN NUMBER)
4 RETURN NUMBER IS
5 l_retval NUMBER;
6 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
7 BEGIN
8 BEGIN
9 SELECT 1
10 INTO l_retval
11 FROM mtl_lot_numbers
12 WHERE gen_object_id = p_object_id;
13 EXCEPTION
14 WHEN NO_DATA_FOUND THEN
15 BEGIN
16 SELECT 2
17 INTO l_retval
18 FROM mtl_serial_numbers
19 WHERE gen_object_id = p_object_id;
20 EXCEPTION
21 /*osfmint*/
22 WHEN NO_DATA_FOUND THEN
23 BEGIN
24 SELECT 5
25 INTO l_retval
26 FROM wip_entities
27 WHERE gen_object_id = p_object_id;
28 EXCEPTION
29 WHEN OTHERS THEN
30 l_retval := NULL;
31 END;
32 /*osfmint*/
33 WHEN OTHERS THEN
34 l_retval := NULL;
35 END;
36 WHEN OTHERS THEN
37 l_retval := NULL;
38 END;
39
40 -- return 2; -- Serial
41 RETURN l_retval;
42 END getobjecttype;
43
44
45 PROCEDURE getobjectinfo(
46 p_object_id IN NUMBER
47 , p_object_type IN NUMBER
48 , p_object_name OUT NOCOPY VARCHAR2
49 , p_object_description OUT NOCOPY VARCHAR2
50 , p_object_type_name OUT NOCOPY VARCHAR2
51 , p_expiration_date OUT NOCOPY DATE
52 , p_primary_uom OUT NOCOPY VARCHAR2
53 , p_inventory_item_id OUT NOCOPY NUMBER
54 , p_object_number OUT NOCOPY VARCHAR2
55 , p_material_status OUT NOCOPY VARCHAR2
56 , p_unit_number OUT NOCOPY VARCHAR2
57 ) IS
58 l_status_id NUMBER;
59 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
60 BEGIN
61 IF (l_debug = 1) THEN
62 inv_trx_util_pub.TRACE('in the procedure getObjectInfo', 'INV_OBJECT_GENEALOGY', 9);
63 inv_trx_util_pub.TRACE('input param values to this proc are:', 'INV_OBJECT_GENEALOGY', 9);
64 inv_trx_util_pub.TRACE('p_object_id is ' || p_object_id || 'p_object_type ' || p_object_type, 'INV_OBJECT_GENEALOGY', 9);
65 END IF;
66
67 IF p_object_type = 1 THEN
68 IF (l_debug = 1) THEN
69 inv_trx_util_pub.TRACE('object_type is 1', 'INV_OBJECT_GENEALOGY', 9);
70 END IF;
71
72 BEGIN
73 SELECT concatenated_segments
74 , msivl.description
75 , mln.expiration_date
76 , msivl.primary_uom_code
77 , mln.inventory_item_id
78 , mln.lot_number
79 , mln.status_id
80 INTO p_object_name
81 , p_object_description
82 , p_expiration_date
83 , p_primary_uom
84 , p_inventory_item_id
85 , p_object_number
86 , l_status_id
87 FROM mtl_system_items_vl msivl, mtl_lot_numbers mln
88 WHERE mln.gen_object_id = p_object_id
89 AND mln.inventory_item_id = msivl.inventory_item_id
90 AND mln.organization_id = msivl.organization_id;
91
92 IF (l_debug = 1) THEN
93 inv_trx_util_pub.TRACE(
94 'p_object_name: '
95 || p_object_name
96 || ' p_object_description: '
97 || p_object_description
98 || ' p_expiration_date :'
99 || p_expiration_date
100 , 'INV_OBJECT_GENEALOGY'
101 , 9
102 );
103 inv_trx_util_pub.TRACE(
104 'p_primary_uom : '
105 || p_primary_uom
106 || ' p_inventory_item_id : '
107 || p_inventory_item_id
108 || ' p_object_number :'
109 || p_object_number
110 || ' l_status_id :'
111 || l_status_id
112 , 'INV_OBJECT_GENEALOGY'
113 , 9
114 );
115 END IF;
116 EXCEPTION
117 WHEN OTHERS THEN
118 NULL;
119 END;
120
121 IF l_status_id IS NOT NULL THEN
122 IF (l_debug = 1) THEN
123 inv_trx_util_pub.TRACE('object type is 1 and status id is not null ', 'INV_OBJECT_GENEALOGY', 9);
124 END IF;
125
126 BEGIN
127 SELECT status_code
128 INTO p_material_status
129 FROM mtl_material_statuses_vl
130 WHERE status_id = l_status_id;
131
132 IF (l_debug = 1) THEN
133 inv_trx_util_pub.TRACE('object type is 1 and status code is ' || p_material_status, 'INV_OBJECT_GENEALOGY', 9);
134 END IF;
135 EXCEPTION
136 WHEN OTHERS THEN
137 NULL;
138 END;
139 END IF;
140 ELSIF p_object_type = 2 THEN
141 IF (l_debug = 1) THEN
142 inv_trx_util_pub.TRACE('object type is 2 ', 'INV_OBJECT_GENEALOGY', 9);
143 END IF;
144
145 SELECT concatenated_segments
146 , msivl.description
147 , msn.end_item_unit_number
148 , msn.serial_number
149 , msn.inventory_item_id
150 , msn.status_id
151 INTO p_object_name
152 , p_object_description
153 , p_unit_number
154 , p_object_number
155 , p_inventory_item_id
156 , l_status_id
157 FROM mtl_system_items_vl msivl, mtl_serial_numbers msn
158 WHERE msn.gen_object_id = p_object_id
159 AND msn.inventory_item_id = msivl.inventory_item_id
160 AND msn.current_organization_id = msivl.organization_id;
161
162 IF (l_debug = 1) THEN
163 inv_trx_util_pub.TRACE('object type is 2 and values retrieved from MSN, MSIK are ', 'INV_OBJECT_GENEALOGY', 9);
164 inv_trx_util_pub.TRACE(
165 'p_object_name : ' || p_object_name || ' p_object_description : ' || p_object_description || ' p_unit_number ' || p_unit_number
166 , 'INV_OBJECT_GENEALOGY'
167 , 9
168 );
169 inv_trx_util_pub.TRACE('p_object_number : ' || p_object_number || ' l_status_id ' || l_status_id, 'INV_OBJECT_GENEALOGY', 9);
170 END IF;
171
172 IF l_status_id IS NOT NULL THEN
173 BEGIN
174 SELECT status_code
175 INTO p_material_status
176 FROM mtl_material_statuses_vl
177 WHERE status_id = l_status_id;
178
179 IF (l_debug = 1) THEN
180 inv_trx_util_pub.TRACE(
181 'object type is 2 and status code from mtl_material_statuses_vl for status_id ' || l_status_id || ' : ' || p_material_status
182 , 'INV_OBJECT_GENEALOGY'
183 , 9
184 );
185 END IF;
186 EXCEPTION
187 WHEN OTHERS THEN
188 NULL;
189 END;
190 END IF;
191
192 SELECT meaning
193 INTO p_object_type_name
194 FROM mfg_lookups
195 WHERE lookup_code = p_object_type
196 AND lookup_type = 'INV_GENEALOGY_OBJECT_TYPE';
197
198 IF (l_debug = 1) THEN
199 inv_trx_util_pub.TRACE(
200 'meaning from mfg_lookups for lookup_type = inv_genealogy_object_type and lookup_code = '
201 || p_object_type
202 || ' : '
203 || p_object_type_name
204 );
205 END IF;
206 END IF;
207 END getobjectinfo;
208
209 PROCEDURE getobjectinfo(
210 p_object_id IN NUMBER
211 , p_object_type IN NUMBER
212 , p_object_name OUT NOCOPY VARCHAR2
213 , p_object_description OUT NOCOPY VARCHAR2
214 , p_object_type_name OUT NOCOPY VARCHAR2
215 , p_expiration_date OUT NOCOPY DATE
216 , p_primary_uom OUT NOCOPY VARCHAR2
217 , p_inventory_item_id OUT NOCOPY NUMBER
218 , p_object_number OUT NOCOPY VARCHAR2
219 , p_material_status OUT NOCOPY VARCHAR2
220 , p_unit_number OUT NOCOPY VARCHAR2
221 , /*Serial Tracking in WIP project. Return
222 wip_entity_id, operation_seq_num AND intraoperation_step_type also*/x_wip_entity_id OUT NOCOPY NUMBER
223 , x_operation_seq_num OUT NOCOPY NUMBER
224 , x_intraoperation_step_type OUT NOCOPY NUMBER
225 , x_current_lot_number OUT NOCOPY VARCHAR2 -- R12 Lot serial Genealogy Project : added new parameter x_current_lot_number
226 ) IS
227 l_status_id NUMBER;
228 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
229 BEGIN
230 IF (l_debug = 1) THEN
231 inv_trx_util_pub.TRACE('in the procedure getObjectInfo', 'INV_OBJECT_GENEALOGY', 9);
232 inv_trx_util_pub.TRACE('input param values to this proc are:', 'INV_OBJECT_GENEALOGY', 9);
233 inv_trx_util_pub.TRACE('p_object_id is ' || p_object_id || 'p_object_type ' || p_object_type, 'INV_OBJECT_GENEALOGY', 9);
234 END IF;
235
236 x_wip_entity_id := NULL;
237 x_operation_seq_num := NULL;
238 x_intraoperation_step_type := NULL;
239
240 IF p_object_type = 1 THEN
241 IF (l_debug = 1) THEN
242 inv_trx_util_pub.TRACE('object_type is 1', 'INV_OBJECT_GENEALOGY', 9);
243 END IF;
244
245 BEGIN
246 SELECT concatenated_segments
247 , msivl.description
248 , mln.expiration_date
249 , msivl.primary_uom_code
250 , mln.inventory_item_id
251 , mln.lot_number
252 , mln.status_id
253 INTO p_object_name
254 , p_object_description
255 , p_expiration_date
256 , p_primary_uom
257 , p_inventory_item_id
258 , p_object_number
259 , l_status_id
260 FROM mtl_system_items_vl msivl, mtl_lot_numbers mln
261 WHERE mln.gen_object_id = p_object_id
262 AND mln.inventory_item_id = msivl.inventory_item_id
263 AND mln.organization_id = msivl.organization_id;
264
265 IF (l_debug = 1) THEN
266 inv_trx_util_pub.TRACE(
267 'p_object_name: '
268 || p_object_name
269 || ' p_object_description: '
270 || p_object_description
271 || ' p_expiration_date :'
272 || p_expiration_date
273 , 'INV_OBJECT_GENEALOGY'
274 , 9
275 );
276 inv_trx_util_pub.TRACE(
277 'p_primary_uom : '
278 || p_primary_uom
279 || ' p_inventory_item_id : '
280 || p_inventory_item_id
281 || ' p_object_number :'
282 || p_object_number
283 || ' l_status_id :'
284 || l_status_id
285 , 'INV_OBJECT_GENEALOGY'
286 , 9
287 );
288 END IF;
289 EXCEPTION
290 WHEN OTHERS THEN
291 NULL;
292 END;
293
294 IF l_status_id IS NOT NULL THEN
295 IF (l_debug = 1) THEN
296 inv_trx_util_pub.TRACE('object type is 1 and status id is not null ', 'INV_OBJECT_GENEALOGY', 9);
297 END IF;
298
299 BEGIN
300 SELECT status_code
301 INTO p_material_status
302 FROM mtl_material_statuses_vl
303 WHERE status_id = l_status_id;
304
305 IF (l_debug = 1) THEN
306 inv_trx_util_pub.TRACE('object type is 1 and status code is ' || p_material_status, 'INV_OBJECT_GENEALOGY', 9);
307 END IF;
308 EXCEPTION
309 WHEN OTHERS THEN
310 NULL;
311 END;
312 END IF;
313 ELSIF p_object_type = 2 THEN
314 IF (l_debug = 1) THEN
315 inv_trx_util_pub.TRACE('object type is 2 ', 'INV_OBJECT_GENEALOGY', 9);
316 END IF;
317
318 SELECT concatenated_segments
319 , msivl.description
320 , msn.end_item_unit_number
321 , msn.serial_number
322 , msn.inventory_item_id
323 , msn.status_id
324 , msn.wip_entity_id
325 , msn.operation_seq_num
326 , msn.intraoperation_step_type
327 , msn.lot_number
328 --Serial Tracking in WIP project. Retrieve wip_entity_id, operation_seq_num and
329 -- intraoperation_step_type also.
330 INTO p_object_name
331 , p_object_description
332 , p_unit_number
333 , p_object_number
334 , p_inventory_item_id
335 , l_status_id
336 , x_wip_entity_id
337 , x_operation_seq_num
338 , x_intraoperation_step_type
339 , x_current_lot_number
340 FROM mtl_system_items_vl msivl, mtl_serial_numbers msn
341 WHERE msn.gen_object_id = p_object_id
342 AND msn.inventory_item_id = msivl.inventory_item_id
343 AND msn.current_organization_id = msivl.organization_id;
344
345 IF (l_debug = 1) THEN
346 inv_trx_util_pub.TRACE('object type is 2 and values retrieved from MSN, MSIK are ', 'INV_OBJECT_GENEALOGY', 9);
347 inv_trx_util_pub.TRACE(
348 'p_object_name : ' || p_object_name || ' p_object_description : ' || p_object_description || ' p_unit_number ' || p_unit_number
349 , 'INV_OBJECT_GENEALOGY'
350 , 9
351 );
352 inv_trx_util_pub.TRACE('p_object_number : ' || p_object_number || ' l_status_id ' || l_status_id, 'INV_OBJECT_GENEALOGY', 9);
353 inv_trx_util_pub.TRACE(
354 'x_wip_entity_id : '
355 || x_wip_entity_id
356 || ' x_operation_seq_num : '
357 || x_operation_seq_num
358 || ' x_intraoperation_step_type : '
359 || x_intraoperation_step_type
360 , 'INV_OBJECT_GENEALOGY'
361 , 9
362 );
363 END IF;
364
365 IF l_status_id IS NOT NULL THEN
366 BEGIN
367 SELECT status_code
368 INTO p_material_status
369 FROM mtl_material_statuses_vl
370 WHERE status_id = l_status_id;
371
372 IF (l_debug = 1) THEN
373 inv_trx_util_pub.TRACE(
374 'object type is 2 and status code from mtl_material_statuses_vl for status_id ' || l_status_id || ' : ' || p_material_status
375 , 'INV_OBJECT_GENEALOGY'
376 , 9
377 );
378 END IF;
379 EXCEPTION
380 WHEN OTHERS THEN
381 NULL;
382 END;
383 END IF;
384
385 SELECT meaning
386 INTO p_object_type_name
387 FROM mfg_lookups
388 WHERE lookup_code = p_object_type
389 AND lookup_type = 'INV_GENEALOGY_OBJECT_TYPE';
390
391 IF (l_debug = 1) THEN
392 inv_trx_util_pub.TRACE(
396 || p_object_type_name
393 'meaning from mfg_lookups for lookup_type = inv_genealogy_object_type and lookup_code = '
394 || p_object_type
395 || ' : '
397 );
398 END IF;
399 END IF;
400 END getobjectinfo;
401
402 /*Bug :4939794
403 Function getjData returns 1 if the object_id passed is of 11510 data,
404 returns 0 if the object_id passed is of R12 data*/
405
406 FUNCTION getjData(
407 p_object_id IN NUMBER
408 , p_object_type IN NUMBER
409 , p_object_id2 IN NUMBER DEFAULT NULL
410 , p_object_type2 IN NUMBER DEFAULT NULL
411 )
412 RETURN NUMBER IS
413 CURSOR jRecord IS
414 SELECT 1 --when queried by asembly serial
415 FROM DUAL
416 WHERE EXISTS (
417 SELECT 1
418 FROM mtl_object_genealogy
419 WHERE genealogy_origin = 1
420 AND object_type = 2
421 AND parent_object_type = 5
422 AND (end_date_active IS NULL OR end_date_active > SYSDATE)
423 AND (object_id = NVL (p_object_id, p_object_id2))
424 OR --when quereid by component serial
425 EXISTS (
426 SELECT 1
427 FROM DUAL
428 WHERE EXISTS (
429 SELECT mog.parent_object_id
430 FROM mtl_object_genealogy mog
431 WHERE object_type = 2
432 AND parent_object_type = 2
433 AND object_id =
434 NVL (p_object_id, p_object_id2)
435 AND EXISTS (
436 SELECT 1
437 --if queried by comp serial then check if it's parent aser has job as parent
438 FROM mtl_object_genealogy
439 WHERE genealogy_origin = 1
440 AND object_type = 2
441 AND parent_object_type = 5
442 AND ( end_date_active IS NULL
443 OR end_date_active > SYSDATE
444 )
445 AND object_id =
446 mog.parent_object_id))))
447 OR
448 -- if queried by component lot, then check if it's parent has aser as child
449 EXISTS (
450 SELECT 1
451 FROM DUAL
452 WHERE EXISTS (
453 SELECT mog.parent_object_id
454 FROM mtl_object_genealogy mog
455 WHERE object_type = 1
456 AND parent_object_type = 5
457 AND object_id = NVL (p_object_id, p_object_id2)
458 AND EXISTS (
459 SELECT 1
460 FROM mtl_object_genealogy
461 WHERE genealogy_origin = 1
462 AND object_type = 2
463 AND parent_object_type = 5
464 AND ( end_date_active IS NULL
465 OR end_date_active > SYSDATE
466 )
467 AND parent_object_id = mog.parent_object_id)))
468 OR
469 --if queried by assembly lot of lot serial, then check if it's parent has aser as child
470 EXISTS (
471 SELECT 1
472 FROM DUAL
473 WHERE EXISTS (
474 SELECT mog.object_id
475 FROM mtl_object_genealogy mog
476 WHERE parent_object_type = 1
477 AND object_type = 5
478 AND parent_object_id = NVL (p_object_id, p_object_id2)
479 AND EXISTS (
480 SELECT 1
481 FROM mtl_object_genealogy
482 WHERE genealogy_origin = 1
483 AND object_type = 2
484 AND parent_object_type = 5
485 AND ( end_date_active IS NULL
486 OR end_date_active > SYSDATE
487 )
488 AND parent_object_id = mog.object_id)))
489 OR
490 --if queried by job and if it has aser as child
491 EXISTS (
492 SELECT 1
493 FROM DUAL
494 WHERE EXISTS (
495 SELECT 1
496 FROM mtl_object_genealogy mog
500 AND parent_object_id=p_object_id)) ;
497 WHERE parent_object_type = 5
498 AND object_type = 2
499 AND genealogy_origin=1
501 jRec jRecord%ROWTYPE;
502
503
504 BEGIN
505 inv_trx_util_pub.TRACE('gjData'||g_jData, 'INV_OBJECT_GENEALOGY', 9);
506 OPEN jRecord;
507 FETCH jRecord INTO jRec;
508 IF jRecord%found THEN
509 g_jData:=1;
510 ELSE
511 g_jData:=0;
512 END IF;
513 RETURN (g_jData);
514 END getjData;
515
516
517 FUNCTION getobjectnumber(
518 p_object_id IN NUMBER
519 , p_object_type IN NUMBER
520 , p_object_id2 IN NUMBER DEFAULT NULL
521 , p_object_type2 IN NUMBER DEFAULT NULL
522 )
523 RETURN VARCHAR2 IS
524 l_object_number VARCHAR2(1200) := 'NULL';
525 l_orgn_id NUMBER;
526 l_item_id NUMBER;
527 l_job_name VARCHAR2(240);
528 l_con_seg VARCHAR2(240);
529 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
530 l_genealogy_prefix_suffix NUMBER := fnd_profile.VALUE('GENEALOGY_PREFIX_SUFFIX');
531 l_genealogy_delimitter VARCHAR2(1) := NVL(fnd_profile.VALUE('GENEALOGY_DELIMITER'), '.');
532 l_lot_number VARCHAR2(80) := NULL;
533 BEGIN
534 IF p_object_type = 1 THEN /* object_type = 1 means lot control */
535 -- Lot Genealogy is available even if WMS is not installed
536 IF l_genealogy_prefix_suffix = 1 THEN --Prefix
537 SELECT lot_number || l_genealogy_delimitter || concatenated_segments
538 INTO l_object_number
539 FROM mtl_lot_numbers mln, mtl_system_items_kfv msikfv
540 WHERE mln.gen_object_id = p_object_id
541 AND mln.inventory_item_id = msikfv.inventory_item_id
542 AND mln.organization_id = msikfv.organization_id;
543 ELSIF l_genealogy_prefix_suffix = 2 THEN --Suffix
544 SELECT concatenated_segments || l_genealogy_delimitter || lot_number
545 INTO l_object_number
546 FROM mtl_lot_numbers mln, mtl_system_items_kfv msikfv
547 WHERE mln.gen_object_id = p_object_id
548 AND mln.inventory_item_id = msikfv.inventory_item_id
549 AND mln.organization_id = msikfv.organization_id;
550 ELSIF l_genealogy_prefix_suffix = 3 THEN --None
551 SELECT lot_number
552 INTO l_object_number
553 FROM mtl_lot_numbers mln, mtl_system_items_kfv msikfv
554 WHERE mln.gen_object_id = p_object_id
555 AND mln.inventory_item_id = msikfv.inventory_item_id
556 AND mln.organization_id = msikfv.organization_id;
557 END IF;
558 ELSIF p_object_type = 2 THEN
559 IF p_object_id2 IS NOT NULL
560 AND p_object_type2 = 1 THEN -- Lot Serial controlled
561 BEGIN
562 SELECT lot_number
563 INTO l_lot_number
564 FROM mtl_lot_numbers mln, mtl_system_items_kfv msikfv
565 WHERE mln.gen_object_id = p_object_id2
566 AND mln.inventory_item_id = msikfv.inventory_item_id
567 AND mln.organization_id = msikfv.organization_id;
568 EXCEPTION
569 WHEN NO_DATA_FOUND THEN
570 NULL;
571 END;
572 END IF;
573
574 IF inv_install.adv_inv_installed(p_organization_id => NULL) THEN
575 IF l_genealogy_prefix_suffix = 1 THEN -- prefix
576 SELECT l_lot_number
577 || DECODE(l_lot_number, NULL, '', l_genealogy_delimitter)
578 || serial_number
579 || l_genealogy_delimitter
580 || concatenated_segments
581 INTO l_object_number
582 FROM mtl_serial_numbers msn, mtl_system_items_kfv msikfv
583 WHERE msn.gen_object_id = p_object_id
584 AND msn.inventory_item_id = msikfv.inventory_item_id
585 AND msn.current_organization_id = msikfv.organization_id;
586 ELSIF l_genealogy_prefix_suffix = 2 THEN -- suffix
587 SELECT concatenated_segments
588 || DECODE(l_lot_number, NULL, '', l_genealogy_delimitter)
589 || l_lot_number
590 || l_genealogy_delimitter
591 || serial_number
592 INTO l_object_number
593 FROM mtl_serial_numbers msn, mtl_system_items_kfv msikfv
594 WHERE msn.gen_object_id = p_object_id
595 AND msn.inventory_item_id = msikfv.inventory_item_id
596 AND msn.current_organization_id = msikfv.organization_id;
597 ELSIF l_genealogy_prefix_suffix = 3 THEN --None
598 SELECT l_lot_number || DECODE(l_lot_number, NULL, '', l_genealogy_delimitter) || serial_number
599 INTO l_object_number
600 FROM mtl_serial_numbers msn, mtl_system_items_kfv msikfv
601 WHERE msn.gen_object_id = p_object_id
602 AND msn.inventory_item_id = msikfv.inventory_item_id
603 AND msn.current_organization_id = msikfv.organization_id;
604 END IF;
605 ELSE
606 SELECT l_lot_number || DECODE(l_lot_number, NULL, '', l_genealogy_delimitter) || serial_number
607 INTO l_object_number
611 ELSIF p_object_type = 5 THEN
608 FROM mtl_serial_numbers
609 WHERE gen_object_id = p_object_id;
610 END IF;
612 SELECT we.primary_item_id
613 , we.organization_id
614 , we.wip_entity_name
615 INTO l_item_id
616 , l_orgn_id
617 , l_job_name
618 FROM wip_entities we
619 WHERE we.gen_object_id = p_object_id;
620
621 IF (l_item_id IS NOT NULL) THEN
622 SELECT concatenated_segments
623 INTO l_con_seg
624 FROM mtl_system_items_kfv msikfv
625 WHERE msikfv.inventory_item_id = l_item_id
626 AND msikfv.organization_id = l_orgn_id;
627 ELSE
628 l_con_seg := '';
629 END IF;
630
631 IF l_genealogy_prefix_suffix = 1 THEN
632 l_object_number := l_job_name || l_genealogy_delimitter || l_con_seg;
633 ELSIF l_genealogy_prefix_suffix = 2 THEN
634 l_object_number := l_con_seg || l_genealogy_delimitter || l_job_name;
635 ELSIF l_genealogy_prefix_suffix = 3 THEN
636 l_object_number := l_con_seg;
637 END IF;
638 END IF;
639
640 RETURN(l_object_number);
641 END getobjectnumber;
642
643
644 FUNCTION getsource(p_org_id IN NUMBER, p_trx_src_type IN NUMBER, p_trx_src_id IN NUMBER)
645 RETURN VARCHAR2 IS
646 l_trx_src VARCHAR2(30);
647 row_count NUMBER := 0;
648 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
649 BEGIN
650 IF p_trx_src_type = 1 THEN -- PO
651 SELECT segment1
652 INTO l_trx_src
653 FROM po_headers_all
654 WHERE po_header_id = p_trx_src_id;
655 ELSIF p_trx_src_type IN(2, 8, 12) THEN -- SO,Internal Order,RMA
656 SELECT SUBSTR(concatenated_segments, 1, 30)
657 INTO l_trx_src
658 FROM mtl_sales_orders_kfv
659 WHERE sales_order_id = p_trx_src_id;
660 /* Removed p_trx-src_type = 3 for 2 reasons. one, that is function is not
661 used at all. Second that mtl_object_genealogy table never has data from this
662 source. Bug 4237802 */
663 ELSIF p_trx_src_type = 4 THEN -- Move Orders
664 SELECT request_number
665 INTO l_trx_src
666 FROM mtl_txn_request_headers
667 WHERE header_id = p_trx_src_id;
668 ELSIF p_trx_src_type = 5 THEN -- WIP
669 SELECT wip_entity_name
670 INTO l_trx_src
671 FROM wip_entities
672 WHERE wip_entity_id = p_trx_src_id
673 AND organization_id = p_org_id;
674 ELSIF p_trx_src_type = 6 THEN -- Account Alias
675 SELECT SUBSTR(concatenated_segments, 1, 30)
676 INTO l_trx_src
677 FROM mtl_generic_dispositions_kfv
678 WHERE disposition_id = p_trx_src_id
679 AND organization_id = p_org_id;
680 ELSIF p_trx_src_type = 7 THEN -- Internal Requisition
681 SELECT segment1
682 INTO l_trx_src
683 FROM po_requisition_headers_all
684 WHERE requisition_header_id = p_trx_src_id;
685 ELSIF p_trx_src_type = 9 THEN -- Cycle Count
686 SELECT cycle_count_header_name
687 INTO l_trx_src
688 FROM mtl_cycle_count_headers
689 WHERE cycle_count_header_id = p_trx_src_id
690 AND organization_id = p_org_id;
691 ELSIF p_trx_src_type = 10 THEN -- Physical Inventory
692 SELECT physical_inventory_name
693 INTO l_trx_src
694 FROM mtl_physical_inventories
695 WHERE physical_inventory_id = p_trx_src_id
696 AND organization_id = p_org_id;
697 ELSIF p_trx_src_type = 11 THEN -- Standard Cost Update
698 SELECT description
699 INTO l_trx_src
700 FROM cst_cost_updates
701 WHERE cost_update_id = p_trx_src_id
702 AND organization_id = p_org_id;
703 ELSIF p_trx_src_type = 13 THEN -- Inventory
704 -- Bug 2666620: BackFlush MO Type Removed. Hence checking for TxnSourceType also.
705 SELECT COUNT(*)
706 INTO row_count
707 FROM mtl_txn_request_lines mol
708 WHERE txn_source_id = p_trx_src_id
709 AND organization_id = p_org_id
710 AND EXISTS(SELECT NULL
711 FROM mtl_txn_request_headers
712 WHERE header_id = mol.header_id
713 AND move_order_type = 5
714 AND mol.transaction_source_type_id = 13);
715
716 IF row_count > 0 THEN
717 SELECT wip_entity_name
718 INTO l_trx_src
719 FROM wip_entities
720 WHERE wip_entity_id = p_trx_src_id
721 AND organization_id = p_org_id;
722 END IF;
723 END IF;
724
725 RETURN l_trx_src;
726 EXCEPTION
727 WHEN OTHERS THEN
728 RETURN NULL;
729 END getsource;
730
731 FUNCTION gettradingpartner(
732 p_org_id IN NUMBER
733 , p_trx_src_type IN NUMBER
734 , p_trx_src_id IN NUMBER
735 , p_trx_src_line_id IN NUMBER
736 , p_transfer_org_id IN NUMBER
737 )
738 RETURN VARCHAR2 IS
739 l_trading_partner VARCHAR2(240) := NULL;
740 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
741 BEGIN
745 SELECT vendor_name
742 /* For Bug#3420761: Changed the code p_trx_src_type = 2 to
743 p_trx_src_type in (2,12) to handle RMA transactions */
744 IF p_trx_src_type = 1 THEN -- PO
746 INTO l_trading_partner
747 FROM po_vendors pov, po_headers_all poh
748 WHERE poh.po_header_id = p_trx_src_id
749 AND poh.vendor_id = pov.vendor_id;
750 ELSIF p_trx_src_type IN(2, 12) THEN -- SO,RMA
751 SELECT party_name
752 INTO l_trading_partner
753 FROM hz_parties hp, hz_cust_accounts hca ,
754 -- R12 TCA Mandate to replace RA_CUSTOMERS with the above 2
755 oe_order_headers_all sha, oe_order_lines_all sla
756 WHERE sla.line_id = p_trx_src_line_id
757 AND sha.header_id = sla.header_id
758 --AND sha.sold_to_org_id = rac.customer_id; As part of R12 TCA changes
759 AND sha.sold_to_org_id = hca.cust_account_id
760 AND hca.party_id = hp.party_id;
761 ELSIF p_trx_src_type IN(7, 8) THEN
762 SELECT organization_code
763 INTO l_trading_partner
764 FROM mtl_parameters mp
765 WHERE mp.organization_id = p_transfer_org_id;
766 END IF;
767
768 RETURN l_trading_partner;
769 EXCEPTION
770 WHEN OTHERS THEN
771 RETURN '';
772 END gettradingpartner;
773
774 -- Added this pacakage as part of Bug 4018721
775 PROCEDURE init IS
776 BEGIN
777 g_ind := 0;
778 g_treeno := 1;
779 g_depth := 1;
780
781 DELETE FROM mtl_gen_temp;
782 END init;
783
784 PROCEDURE inv_populate_child(
785 p_object_id IN NUMBER
786 , p_related_object_id IN NUMBER
787 , p_object_type IN NUMBER DEFAULT NULL
788 , p_object_id2 IN NUMBER DEFAULT NULL
789 , p_object_type2 IN NUMBER DEFAULT NULL
790 ) IS
791 l_count NUMBER := 0;
792 l_previous_parent NUMBER := 0;
793 get_hierc VARCHAR2(1) := 'Y';
794
795 -- get_hierc is used to decide whether to select the next level of nodes for the selected node
796 -- do not get the next level, if the node has already been processed once.
797
798 CURSOR search_cur IS
799 SELECT *
800 FROM mtl_gen_temp
801 WHERE treeno < g_treeno
802 AND DEPTH < g_depth
803 ORDER BY ind DESC;
804
805 /* R12 Lot Serial Genealogy Project : Modified cursor */
806
807 /*11510 record group*/
808 CURSOR child_cur1 IS
809 SELECT object_id
810 , parent_object_id
811 , object_type
812 , NULL object_id2
813 , NULL object_type2
814 , NULL parent_object_id2
815 FROM mtl_object_genealogy
816 WHERE parent_object_id = p_object_id
817 AND object_type<>2
818 AND (end_date_active is null or end_date_active > SYSDATE);
819 --AND ((object_type = 2 AND parent_object_type = object_type) OR (object_type <> 2))
820
821
822 /* R12 RECORD GROUP*/
823 CURSOR child_cur2 IS
824 SELECT object_id
825 , parent_object_id
826 , object_type
827 , object_id2
828 , object_type2
829 , parent_object_id2
830 FROM mtl_object_genealogy
831 WHERE parent_object_id = p_object_id
832 AND (p_object_id2 IS NULL OR parent_object_id2 = p_object_id2)
833 AND (end_date_active IS NULL OR end_date_active > SYSDATE);
834
835 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
836
837
838 BEGIN
839 -- First condition
840 -- ===============
841 -- Check if the p_object_id is the first node of the tree
842 -- if yes, then no need to check other trees or nodes
843 IF (l_debug = 1) THEN
844 inv_trx_util_pub.TRACE('in the procedure inv_populate_child', 'INV_OBJECT_GENEALOGY', 9);
845 inv_trx_util_pub.TRACE('input param values to this proc are:', 'INV_OBJECT_GENEALOGY', 9);
846 inv_trx_util_pub.TRACE('p_object_id is ' || p_object_id || 'p_object_type ' || p_object_type, 'INV_OBJECT_GENEALOGY', 9);
847 inv_trx_util_pub.TRACE('p_object_id2 is ' || p_object_id2 || 'p_object_type2 ' || p_object_type2, 'INV_OBJECT_GENEALOGY', 9);
848 inv_trx_util_pub.TRACE('p_related_object_id is ' || p_related_object_id, 'INV_OBJECT_GENEALOGY', 9);
849 END IF;
850
851 IF g_ind > 1 THEN
852 BEGIN
853 SELECT 1
854 INTO l_count
855 FROM mtl_gen_temp
856 WHERE label = p_object_id
857 AND related_label = 0
858 AND (p_object_id2 is null or label2=p_object_id2)
859 AND ROWNUM < 2;
860
861 IF l_count > 0 THEN
862 get_hierc := 'N';
863 g_ind := g_ind + 1;
864
865 IF l_debug =1 THEN
866 inv_trx_util_pub.TRACE('insert1, g_ind '||g_ind, 'INV_OBJECT_GENEALOGY', 9);
867 END IF;
868
869 INSERT INTO mtl_gen_temp
870 (
871 ind
872 , treeno
873 , DEPTH
874 , label
875 , related_label
876 , child_object_type
880 VALUES (
877 , label2
878 , child_object_type2
879 )
881 g_ind
882 , g_treeno
883 , g_depth
884 , p_object_id
885 , p_related_object_id
886 , p_object_type
887 , p_object_id2
888 , p_object_type2
889 );
890 END IF;
891 EXCEPTION
892 WHEN NO_DATA_FOUND THEN
893 l_count := 0;
894 IF l_debug = 1 THEN
895 inv_trx_util_pub.TRACE('exception 1', 'INV_OBJECT_GENEALOGY', 9);
896 END IF;
897
898 END;
899 END IF;
900
901 -- Second Condition
902 -- ================
903 IF g_ind > 1 and get_hierc='Y' THEN
904 BEGIN
905 SELECT 1
906 INTO l_count
907 FROM mtl_gen_temp
908 WHERE treeno = g_treeno
909 AND label = p_object_id
910 AND (p_object_id2 is null or label2=p_object_id2)
911 AND ROWNUM < 2;
912
913 -- If the node already exists in the present tree , that means
914 -- it need not be exploded further.
915 IF l_count > 0 THEN
916 get_hierc := 'N';
917 g_ind := g_ind + 1;
918 IF l_debug=1 THEN
919 inv_trx_util_pub.TRACE('insert2 g_ind '||g_ind, 'INV_OBJECT_GENEALOGY', 9);
920 END IF;
921
922 INSERT INTO mtl_gen_temp
923 (
924 ind
925 , treeno
926 , DEPTH
927 , label
928 , related_label
929 , child_object_type
930 , label2
931 , child_object_type2
932 )
933 VALUES (
934 g_ind
935 , g_treeno
936 , g_depth
937 , p_object_id
938 , p_related_object_id
939 , p_object_type
940 , p_object_id2
941 , p_object_type2
942 );
943 END IF;
944 EXCEPTION
945 WHEN NO_DATA_FOUND THEN
946 l_count := 0;
947 IF l_debug=1 THEN
948 inv_trx_util_pub.TRACE('exception 2', 'INV_OBJECT_GENEALOGY', 9);
949 END IF;
950
951 END;
952 END IF;
953
954
955 -- Third condition
956 -- ===============
957 IF get_hierc = 'Y'
958 AND g_treeno > 1 THEN
959 l_previous_parent := 0;
960
961 FOR cur_var IN search_cur LOOP
962 IF cur_var.label = p_object_id
963 AND cur_var.label = l_previous_parent THEN
964 get_hierc := 'N';
965 g_ind := g_ind + 1;
966 IF l_debug=1 THEN
967 inv_trx_util_pub.TRACE('insert3 g_ind '||g_ind, 'INV_OBJECT_GENEALOGY', 9);
968 END IF;
969
970 INSERT INTO mtl_gen_temp
971 (
972 ind
973 , treeno
974 , DEPTH
975 , label
976 , related_label
977 , child_object_type
978 , label2
979 , child_object_type2
980 )
981 VALUES (
982 g_ind
983 , g_treeno
984 , g_depth
985 , p_object_id
986 , p_related_object_id
987 , p_object_type
988 , p_object_id2
989 , p_object_type2
990 );
991
992 EXIT;
993 END IF;
994
995 l_previous_parent := cur_var.related_label;
996 END LOOP;
997 END IF;
998
999 IF (get_hierc = 'Y') THEN
1000 g_ind := g_ind + 1;
1001 IF l_debug=1 THEN
1002 inv_trx_util_pub.TRACE('insert4 g_ind '||g_ind, 'INV_OBJECT_GENEALOGY', 9);
1003 END IF;
1004
1005 INSERT INTO mtl_gen_temp
1006 (
1007 ind
1008 , treeno
1009 , DEPTH
1010 , label
1011 , related_label
1012 , child_object_type
1013 , label2
1014 , child_object_type2
1015 )
1016 VALUES (
1017 g_ind
1018 , g_treeno
1019 , g_depth
1020 , p_object_id
1021 , p_related_object_id
1022 , p_object_type
1023 , p_object_id2
1024 , p_object_type2
1025 );
1026 IF p_related_object_id <> p_object_id THEN
1027 IF g_jData=1 THEN
1028 FOR child_rec IN child_cur1 LOOP
1029 g_depth := g_depth + 1;
1030
1031 -- added the following condition, so that if for some reason there is an end-less
1035 -- We do not expect anyone to have such a deep hierarchy
1032 -- loop, it will atleast stop after the depth of 45 and will not 'disconnect the server'
1033 -- Please note that if the user really has a hieracrchy that is more than 45 levels deep,
1034 -- The users may report a bug that the complete information is not diaplyes
1036 IF g_depth > 45 THEN
1037 EXIT;
1038 END IF;
1039
1040 inv_populate_child(child_rec.object_id, child_rec.parent_object_id, child_rec.object_type, child_rec.object_id2
1041 , child_rec.object_type2);
1042 g_depth := g_depth - 1;
1043 g_treeno := g_treeno + 1;
1044 END LOOP;
1045 ELSE
1046 FOR child_rec IN child_cur2 LOOP
1047 g_depth := g_depth + 1;
1048
1049 -- added the following condition, so that if for some reason there is an end-less
1050 -- loop, it will atleast stop after the depth of 45 and will not 'disconnect the server'
1051 -- Please note that if the user really has a hieracrchy that is more than 45 levels deep,
1052 -- The users may report a bug that the complete information is not diaplyes
1053 -- We do not expect anyone to have such a deep hierarchy
1054 IF g_depth > 45 THEN
1055 EXIT;
1056 END IF;
1057
1058 inv_populate_child(child_rec.object_id, child_rec.parent_object_id, child_rec.object_type, child_rec.object_id2
1059 , child_rec.object_type2);
1060 g_depth := g_depth - 1;
1061 g_treeno := g_treeno + 1;
1062 END LOOP;
1063 END IF;
1064 END IF;
1065 END IF;
1066 END inv_populate_child;
1067
1068 PROCEDURE inv_populate_child_tree(
1069 p_object_id IN NUMBER
1070 , p_related_object_id IN NUMBER
1071 , p_object_type IN NUMBER DEFAULT NULL
1072 , p_object_id2 IN NUMBER DEFAULT NULL
1073 , p_object_type2 IN NUMBER DEFAULT NULL
1074 ) IS
1075 l_count NUMBER;
1076 l_prev_parent NUMBER := 1;
1077 l_min_level NUMBER := 1;
1078
1079 /* R12 Lot Serial Genealogy Project : Modified cursor */
1080
1081 /*11510 record group*/
1082
1083 --Bug 6600064, Added end_date_active condition in the START WITH clause in child_cur1 and child_cur1
1084 --Bug 6643575, removed the where clause logic and moved it to the to connect by clause in child_cur1 and child_cur1
1085
1086 CURSOR child_cur1 IS
1087 SELECT level
1088 , mog.object_id
1089 , mog.parent_object_id
1090 , mog.object_type
1091 , NULL object_id2
1092 , NULL object_type2
1093 , NULL parent_object_id2
1094 FROM mtl_object_genealogy mog
1095 -- WHERE object_type<>2--((object_type = 2 AND parent_object_type = object_type) OR (object_type <> 2))
1096 -- AND (end_date_active is null or end_date_active > SYSDATE)
1097 START WITH (parent_object_id=p_object_id and (end_date_active is null or end_date_active > SYSDATE))
1098 CONNECT BY prior object_id = parent_object_id
1099 AND object_type<>2 --((object_type = 2 AND parent_object_type = object_type) OR (object_type <> 2))
1100 AND (end_date_active is null or end_date_active > SYSDATE);
1101
1102
1103 /*R12 record group*/
1104 CURSOR child_cur2 IS
1105 SELECT LEVEL
1106 , object_id
1107 , parent_object_id
1108 , object_type
1109 , object_id2
1110 , object_type2
1111 , parent_object_id2
1112 FROM mtl_object_genealogy
1113 -- WHERE end_date_active IS NULL
1114 -- OR end_date_active > SYSDATE
1115 START WITH (parent_object_id=p_object_id and (end_date_active is null or end_date_active > SYSDATE))
1116 CONNECT BY PRIOR object_id = parent_object_id
1117 AND (end_date_active IS NULL OR end_date_active > SYSDATE);
1118
1119 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
1120 /*End of R12 Genealogy Lot serial Project*/
1121
1122 BEGIN
1123 IF (l_debug = 1) THEN
1124 inv_trx_util_pub.TRACE('in the procedure inv_populate_child_tree', 'INV_OBJECT_GENEALOGY', 9);
1125 inv_trx_util_pub.TRACE('input param values to this proc are:', 'INV_OBJECT_GENEALOGY', 9);
1126 inv_trx_util_pub.TRACE('p_object_id is ' || p_object_id || 'p_object_type ' || p_object_type, 'INV_OBJECT_GENEALOGY', 9);
1127 inv_trx_util_pub.TRACE('p_object_id2 is ' || p_object_id2 || 'p_object_type2 ' || p_object_type2, 'INV_OBJECT_GENEALOGY', 9);
1128 inv_trx_util_pub.TRACE('p_related_object_id is ' || p_related_object_id, 'INV_OBJECT_GENEALOGY', 9);
1129 END IF;
1130
1131 inv_object_genealogy.init;
1132 g_ind := g_ind + 1;
1133 l_prev_parent := p_related_object_id;
1134
1135
1136
1137 INSERT INTO mtl_gen_temp
1138 (
1139 ind
1140 , treeno
1141 , DEPTH
1142 , label
1143 , related_label
1144 , child_object_type
1145 , label2
1146 , child_object_type2
1147 )
1148 VALUES (
1149 g_ind
1150 , g_treeno
1151 , g_depth
1152 , p_object_id
1153 , p_related_object_id
1157 );
1154 , p_object_type
1155 , p_object_id2
1156 , p_object_type2
1158
1159 IF g_jData=1 THEN
1160 --open 11510 cursor
1161 FOR child_rec IN child_cur1
1162 LOOP
1163 IF child_rec.parent_object_id = l_prev_parent THEN
1164 g_treeno := g_treeno + 1;
1165 END IF;
1166
1167 IF l_min_level = child_rec.LEVEL THEN
1168 g_treeno := g_ind;
1169 END IF;
1170
1171 IF g_depth < child_rec.LEVEL + 1 THEN
1172 g_depth := child_rec.LEVEL + 1;
1173 END IF;
1174
1175 g_ind := g_ind + 1;
1176
1177 INSERT INTO mtl_gen_temp
1178 (
1179 ind
1180 , treeno
1181 , DEPTH
1182 , label
1183 , related_label
1184 , child_object_type
1185 , label2
1186 , child_object_type2
1187 )
1188 VALUES
1189 ( g_ind,
1190 g_treeno,
1191 child_rec.level+1,
1192 child_rec.object_id,
1193 child_rec.parent_object_id,
1194 child_rec.object_type,
1195 child_rec.object_id2,
1196 child_rec.object_type2);
1197
1198 l_prev_parent := child_rec.parent_object_id;
1199 END LOOP;
1200
1201 ELSE
1202 --open r12 cursor
1203 FOR child_rec IN child_cur2
1204 LOOP
1205 IF child_rec.parent_object_id = l_prev_parent THEN
1206 g_treeno := g_treeno + 1;
1207 END IF;
1208
1209 IF l_min_level = child_rec.LEVEL THEN
1210 g_treeno := g_ind;
1211 END IF;
1212
1213 IF g_depth < child_rec.LEVEL + 1 THEN
1214 g_depth := child_rec.LEVEL + 1;
1215 END IF;
1216
1217 g_ind := g_ind + 1;
1218
1219 INSERT INTO mtl_gen_temp
1220 (
1221 ind
1222 , treeno
1223 , DEPTH
1224 , label
1225 , related_label
1226 , child_object_type
1227 , label2
1228 , child_object_type2
1229 )
1230 VALUES
1231 ( g_ind,
1232 g_treeno,
1233 child_rec.level+1,
1234 child_rec.object_id,
1235 child_rec.parent_object_id,
1236 child_rec.object_type,
1237 child_rec.object_id2,
1238 child_rec.object_type2);
1239
1240 l_prev_parent := child_rec.parent_object_id;
1241 END LOOP;
1242
1243
1244 END IF;
1245
1246
1247 EXCEPTION
1248 WHEN OTHERS THEN
1249 inv_object_genealogy.init;
1250 /* R12 Lot Serial Genealogy Project : Modified call to procedure to include 2 new parameters p_object_id2, p_object_type2 */
1251 inv_populate_child(p_object_id, p_related_object_id, p_object_type, p_object_id2, p_object_type2);
1252 END inv_populate_child_tree;
1253
1254 PROCEDURE inv_populate_parent(
1255 p_object_id IN NUMBER
1256 , p_related_object_id IN NUMBER
1257 , p_object_type IN NUMBER DEFAULT NULL
1258 , p_object_id2 IN NUMBER DEFAULT NULL
1259 , p_object_type2 IN NUMBER DEFAULT NULL
1260 ) IS
1261 l_count NUMBER := 0;
1262 l_previous_parent NUMBER := 0;
1263 get_hierc VARCHAR2(1) := 'Y';
1264
1265 -- get_hierc is used to decide whether to select the next level of nodes for the selected node
1266 -- do not get the next level, if the node has already been processed once.
1267
1268 /* R12 Lot Serial Genealogy Project : Added cursors to job, direct genealogy types */
1269 CURSOR parent_cur IS
1270 SELECT parent_object_id
1271 , object_id
1272 , parent_object_type
1273 , parent_object_id2
1274 , parent_object_type2, object_id2,object_type2,object_type
1275 FROM mtl_object_genealogy
1276 WHERE object_id = p_object_id
1277 AND (p_object_id2 IS NULL OR object_id2 = p_object_id2)
1278 AND(end_date_active IS NULL
1279 OR end_date_active > SYSDATE);
1280
1281 CURSOR search_cur IS
1282 SELECT *
1283 FROM mtl_gen_temp
1284 WHERE treeno < g_treeno
1285 AND DEPTH < g_depth
1286 ORDER BY ind DESC;
1287
1288
1289 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
1290 BEGIN
1291 -- First condition
1292 -- ===============
1293 -- Check if the p_object_id is the first node of the tree
1294 -- if yes, then no need to check other trees or nodes
1295 IF (l_debug = 1) THEN
1296 inv_trx_util_pub.TRACE('in the procedure inv_populate_parent', 'INV_OBJECT_GENEALOGY', 9);
1300 inv_trx_util_pub.TRACE('p_related_object_id is ' || p_related_object_id, 'INV_OBJECT_GENEALOGY', 9);
1297 inv_trx_util_pub.TRACE('input param values to this proc are:', 'INV_OBJECT_GENEALOGY', 9);
1298 inv_trx_util_pub.TRACE('p_object_id is ' || p_object_id || 'p_object_type ' || p_object_type, 'INV_OBJECT_GENEALOGY', 9);
1299 inv_trx_util_pub.TRACE('p_object_id2 is ' || p_object_id2 || 'p_object_type2 ' || p_object_type2, 'INV_OBJECT_GENEALOGY', 9);
1301 END IF;
1302 IF g_ind > 0 THEN
1303 BEGIN
1304 SELECT 1
1305 INTO l_count
1306 FROM mtl_gen_temp
1307 WHERE label = p_object_id
1308 AND related_label = 0
1309 AND (p_object_id2 is null or label2=p_object_id2)
1310 AND ROWNUM < 2;
1311
1312 IF l_count > 0 THEN
1313 get_hierc := 'N';
1314 g_ind := g_ind + 1;
1315
1316 IF l_debug =1 THEN
1317 inv_trx_util_pub.TRACE('insert1, g_ind '||g_ind, 'INV_OBJECT_GENEALOGY', 9);
1318 END IF;
1319
1320
1321 INSERT INTO mtl_gen_temp
1322 (
1323 ind
1324 , treeno
1325 , DEPTH
1326 , label
1327 , related_label
1328 , child_object_type
1329 , label2
1330 , child_object_type2
1331 )
1332 VALUES (
1333 g_ind
1334 , g_treeno
1335 , g_depth
1336 , p_object_id
1337 , p_related_object_id
1338 , p_object_type
1339 , p_object_id2
1340 , p_object_type2
1341 );
1342 END IF;
1343 EXCEPTION
1344 WHEN NO_DATA_FOUND THEN
1345 l_count := 0;
1346 IF l_debug = 1 THEN
1347 inv_trx_util_pub.TRACE('exception 1', 'INV_OBJECT_GENEALOGY', 9);
1348 END IF;
1349
1350 END;
1351 END IF;
1352
1353 -- Second Condition
1354 -- ================
1355 IF g_ind > 1 and get_hierc='Y' THEN
1356 BEGIN
1357 SELECT 1
1358 INTO l_count
1359 FROM mtl_gen_temp
1360 WHERE treeno = g_treeno
1361 AND label = p_object_id
1362 AND (p_object_id2 is null or label2=p_object_id2)
1363 AND ROWNUM < 2;
1364
1365 -- If the node already exists in the present tree , that means
1366 -- it need not be exploded further.
1367 IF l_count > 0 THEN
1368 get_hierc := 'N';
1369 g_ind := g_ind + 1;
1370
1371 IF l_debug = 2 THEN
1372 inv_trx_util_pub.TRACE('insert2, g_ind '||g_ind, 'INV_OBJECT_GENEALOGY', 9);
1373 END IF;
1374
1375
1376 INSERT INTO mtl_gen_temp
1377 (
1378 ind
1379 , treeno
1380 , DEPTH
1381 , label
1382 , related_label
1383 , child_object_type
1384 , label2
1385 , child_object_type2
1386 )
1387 VALUES (
1388 g_ind
1389 , g_treeno
1390 , g_depth
1391 , p_object_id
1392 , p_related_object_id
1393 , p_object_type
1394 , p_object_id2
1395 , p_object_type2
1396 );
1397 END IF;
1398 EXCEPTION
1399 WHEN NO_DATA_FOUND THEN
1400 l_count := 0;
1401 IF l_debug = 1 THEN
1402 inv_trx_util_pub.TRACE('exception 2', 'INV_OBJECT_GENEALOGY', 9);
1403 END IF;
1404
1405 END;
1406 END IF;
1407 -- Third condition
1408 -- ===============
1409 IF get_hierc = 'Y'
1410 AND g_treeno > 1 THEN
1411 l_previous_parent := 0;
1412
1413 FOR cur_var IN search_cur LOOP
1414 IF cur_var.label = p_object_id
1415 AND cur_var.label = l_previous_parent THEN
1416 get_hierc := 'N';
1417 g_ind := g_ind + 1;
1418
1419 IF l_debug = 3 THEN
1420 inv_trx_util_pub.TRACE('insert3, g_ind '||g_ind, 'INV_OBJECT_GENEALOGY', 9);
1421 END IF;
1422
1423
1424 INSERT INTO mtl_gen_temp
1425 (
1426 ind
1427 , treeno
1428 , DEPTH
1429 , label
1430 , related_label
1431 , child_object_type
1432 , label2
1433 , child_object_type2
1434 )
1435 VALUES (
1436 g_ind
1437 , g_treeno
1438 , g_depth
1439 , p_object_id
1440 , p_related_object_id
1441 , p_object_type
1442 , p_object_id2
1446 EXIT;
1443 , p_object_type2
1444 );
1445
1447 END IF;
1448
1449 l_previous_parent := cur_var.related_label;
1450 END LOOP;
1451 END IF;
1452
1453 IF (get_hierc = 'Y') THEN
1454 g_ind := g_ind + 1;
1455
1456 IF l_debug =1 THEN
1457 inv_trx_util_pub.TRACE('insert4, g_ind '||g_ind, 'INV_OBJECT_GENEALOGY', 9);
1458 END IF;
1459
1460
1461 INSERT INTO mtl_gen_temp
1462 (
1463 ind
1464 , treeno
1465 , DEPTH
1466 , label
1467 , related_label
1468 , child_object_type
1469 , label2
1470 , child_object_type2
1471 )
1472 VALUES (
1473 g_ind
1474 , g_treeno
1475 , g_depth
1476 , p_object_id
1477 , p_related_object_id
1478 , p_object_type
1479 , p_object_id2
1480 , p_object_type2
1481 );
1482
1483 IF p_object_id <> p_related_object_id THEN
1484 FOR parent_rec IN parent_cur LOOP
1485 g_depth := g_depth + 1;
1486
1487 -- added the following condition, so that if for some reason there is an end-less
1488 -- loop, it will atleast stop after the depth of 45 and will not 'disconnect the server'
1489 -- Please note that if the user really has a hieracrchy that is more than 45 levels deep,
1490 -- The users may report a bug that the complete information is not diaplyes
1491 -- We do not expect anyone to have such a deep hierarchy
1492 IF g_depth > 45 THEN
1493 EXIT;
1494 END IF;
1495
1496 inv_populate_parent(
1497 parent_rec.parent_object_id
1498 , parent_rec.object_id
1499 , parent_rec.parent_object_type
1500 , parent_rec.parent_object_id2
1501 , parent_rec.parent_object_type2
1502 );
1503 g_depth := g_depth - 1;
1504 g_treeno := g_treeno + 1;
1505 END LOOP;
1506 END IF;
1507 END IF;
1508 END inv_populate_parent;
1509
1510 PROCEDURE inv_populate_parent_tree(
1511 p_object_id IN NUMBER
1512 , p_related_object_id IN NUMBER
1513 , p_object_type IN NUMBER DEFAULT NULL
1514 , p_object_id2 IN NUMBER DEFAULT NULL
1515 , p_object_type2 IN NUMBER DEFAULT NULL
1516 ) IS
1517 l_count NUMBER;
1518 l_prev_parent NUMBER := 1;
1519 l_min_level NUMBER := 1;
1520
1521 /* R12 Lot Serial Genealogy Project : Modified cursor */
1522 --Bug 6600064, Added end_date_active condition in the START WITH clause in parent_cur
1523 --Bug 6643575, removed the where clause logic and moved it to the to connect by clause in parent_cur
1524
1525 CURSOR parent_cur IS
1526 SELECT LEVEL
1527 , mog.parent_object_id
1528 , mog.object_id
1529 , mog.parent_object_type
1530 , mog.parent_object_id2
1531 , mog.parent_object_type2
1532 FROM mtl_object_genealogy mog
1533 -- WHERE end_date_active IS NULL
1534 -- OR end_date_active > SYSDATE
1535 START WITH (object_id = p_object_id and (end_date_active is null or end_date_active > SYSDATE))
1536 CONNECT BY PRIOR parent_object_id = object_id
1537 AND (end_date_active IS NULL OR end_date_active > SYSDATE);
1538
1539
1540 /* End of R12 Lot Serial Genealogy Project */
1541 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
1542
1543
1544
1545 BEGIN
1546 IF (l_debug = 1) THEN
1547 inv_trx_util_pub.TRACE('in the procedure inv_populate_parent_tree', 'INV_OBJECT_GENEALOGY', 9);
1548 inv_trx_util_pub.TRACE('input param values to this proc are:', 'INV_OBJECT_GENEALOGY', 9);
1549 inv_trx_util_pub.TRACE('p_object_id is ' || p_object_id || 'p_object_type ' || p_object_type, 'INV_OBJECT_GENEALOGY', 9);
1550 inv_trx_util_pub.TRACE('p_object_id2 is ' || p_object_id2 || 'p_object_type2 ' || p_object_type2, 'INV_OBJECT_GENEALOGY', 9);
1551 inv_trx_util_pub.TRACE('p_related_object_id is ' || p_related_object_id, 'INV_OBJECT_GENEALOGY', 9);
1552 END IF;
1553
1554 inv_object_genealogy.init;
1555 g_ind := g_ind + 1;
1556 l_prev_parent := p_related_object_id;
1557
1558 INSERT INTO mtl_gen_temp
1559 (
1560 ind
1561 , treeno
1562 , DEPTH
1563 , label
1564 , related_label
1565 , child_object_type
1566 , label2
1567 , child_object_type2
1568 )
1569 VALUES (
1570 g_ind
1571 , g_treeno
1572 , g_depth
1573 , p_object_id
1574 , p_related_object_id
1575 , p_object_type
1576 , p_object_id2
1577 , p_object_type2
1578 );
1579
1580 --IF g_jData=1 THEN
1581 FOR parent_rec IN parent_cur LOOP
1582 IF parent_rec.parent_object_id = l_prev_parent THEN
1583 g_treeno := g_treeno + 1;
1584 END IF;
1585
1586 IF l_min_level = parent_rec.LEVEL THEN
1587 g_treeno := g_ind;
1588 END IF;
1589
1590 IF g_depth < parent_rec.LEVEL + 1 THEN
1591 g_depth := parent_rec.LEVEL + 1;
1592 END IF;
1593
1594 g_ind := g_ind + 1;
1595
1596 INSERT INTO mtl_gen_temp
1597 (
1598 ind
1599 , treeno
1600 , DEPTH
1601 , label
1602 , related_label
1603 , child_object_type
1604 , label2
1605 , child_object_type2
1606 )
1607 VALUES (
1608 g_ind
1609 ,g_treeno
1610 ,parent_rec.level+1
1611 ,parent_rec.parent_object_id
1612 ,parent_rec.object_id
1613 ,parent_rec.parent_object_type
1614 ,parent_rec.parent_object_id2
1615 ,parent_rec.parent_object_type2);
1616
1617 l_prev_parent := parent_rec.parent_object_id;
1618 END LOOP;
1619 --END IF;
1620 EXCEPTION
1621 WHEN OTHERS THEN
1622 inv_object_genealogy.init;
1623 inv_populate_parent(p_object_id, p_related_object_id, p_object_type, p_object_id2, p_object_type2);
1624 END inv_populate_parent_tree;
1625
1626
1627 END inv_object_genealogy;
[Home] [Help]