1 PACKAGE BODY inv_object_genealogy AS
2 /* $Header: INVOGENB.pls 120.18.12020000.3 2013/03/21 06:06:57 gausriva 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(
393 'meaning from mfg_lookups for lookup_type = inv_genealogy_object_type and lookup_code = '
394 || p_object_type
395 || ' : '
396 || p_object_type_name
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 --bug 13866934
415 SELECT 1
416 FROM DUAL
417 WHERE EXISTS (
418 SELECT 1 FROM MTL_OBJECT_GENEALOGY
419 WHERE GENEALOGY_ORIGIN = 1 AND OBJECT_TYPE = 2 AND PARENT_OBJECT_TYPE = 5
420 AND ( END_DATE_ACTIVE IS NULL OR END_DATE_ACTIVE > SYSDATE )
421 AND OBJECT_ID = nvl(p_object_id, p_object_id2)
422
423 union ALL
424
425 SELECT MOG.PARENT_OBJECT_ID
426 FROM MTL_OBJECT_GENEALOGY MOG
427 WHERE OBJECT_TYPE = 2 AND PARENT_OBJECT_TYPE = 2
428 AND OBJECT_ID = nvl(p_object_id, p_object_id2)
429 AND EXISTS (
430 SELECT 1 FROM MTL_OBJECT_GENEALOGY
431 WHERE GENEALOGY_ORIGIN = 1 AND OBJECT_TYPE = 2 AND PARENT_OBJECT_TYPE = 5
432 AND ( END_DATE_ACTIVE IS NULL OR END_DATE_ACTIVE > SYSDATE )
433 AND OBJECT_ID = MOG.PARENT_OBJECT_ID )
434
435 union ALL
436
437 SELECT MOG.PARENT_OBJECT_ID
438 FROM MTL_OBJECT_GENEALOGY MOG
439 WHERE OBJECT_TYPE = 1 AND PARENT_OBJECT_TYPE = 5
440 AND OBJECT_ID = nvl(p_object_id, p_object_id2)
441 AND EXISTS (
442 SELECT 1 FROM MTL_OBJECT_GENEALOGY
443 WHERE GENEALOGY_ORIGIN = 1 AND OBJECT_TYPE = 2 AND PARENT_OBJECT_TYPE = 5
444 AND ( END_DATE_ACTIVE IS NULL OR END_DATE_ACTIVE > SYSDATE )
445 AND PARENT_OBJECT_ID = MOG.PARENT_OBJECT_ID)
446
447 union ALL
448
449 SELECT MOG.OBJECT_ID
450 FROM MTL_OBJECT_GENEALOGY MOG
451 WHERE PARENT_OBJECT_TYPE = 1 AND OBJECT_TYPE = 5
452 AND PARENT_OBJECT_ID = nvl(p_object_id, p_object_id2)
453 AND EXISTS (
454 SELECT 1 FROM MTL_OBJECT_GENEALOGY
455 WHERE GENEALOGY_ORIGIN = 1 AND OBJECT_TYPE = 2 AND PARENT_OBJECT_TYPE = 5
456 AND ( END_DATE_ACTIVE IS NULL OR END_DATE_ACTIVE > SYSDATE )
457 AND PARENT_OBJECT_ID = MOG.OBJECT_ID )
458
459 union ALL
460
461 SELECT 1 FROM MTL_OBJECT_GENEALOGY MOG
462 WHERE PARENT_OBJECT_TYPE = 5 AND OBJECT_TYPE = 2 AND GENEALOGY_ORIGIN=1
463 AND PARENT_OBJECT_ID=p_object_id
464 ) ; --bug 13866934
465
466 /* Bug 13866934 commenting the cusror for adding the new one
467 SELECT 1 --when queried by asembly serial
468 FROM DUAL
469 WHERE EXISTS (
470 SELECT 1
471 FROM mtl_object_genealogy
472 WHERE genealogy_origin = 1
473 AND object_type = 2
474 AND parent_object_type = 5
475 AND (end_date_active IS NULL OR end_date_active > SYSDATE)
476 AND (object_id = NVL (p_object_id, p_object_id2))
477 OR --when quereid by component serial
478 EXISTS (
479 SELECT 1
480 FROM DUAL
481 WHERE EXISTS (
482 SELECT mog.parent_object_id
483 FROM mtl_object_genealogy mog
484 WHERE object_type = 2
485 AND parent_object_type = 2
486 AND object_id =
487 NVL (p_object_id, p_object_id2)
488 AND EXISTS (
489 SELECT 1
490 --if queried by comp serial then check if it's parent aser has job as parent
491 FROM mtl_object_genealogy
492 WHERE genealogy_origin = 1
493 AND object_type = 2
494 AND parent_object_type = 5
495 AND ( end_date_active IS NULL
496 OR end_date_active > SYSDATE
497 )
498 AND object_id =
499 mog.parent_object_id))))
500 OR
501 -- if queried by component lot, then check if it's parent has aser as child
502 EXISTS (
503 SELECT 1
504 FROM DUAL
505 WHERE EXISTS (
506 SELECT mog.parent_object_id
507 FROM mtl_object_genealogy mog
508 WHERE object_type = 1
509 AND parent_object_type = 5
510 AND object_id = NVL (p_object_id, p_object_id2)
511 AND EXISTS (
512 SELECT 1
513 FROM mtl_object_genealogy
514 WHERE genealogy_origin = 1
515 AND object_type = 2
516 AND parent_object_type = 5
517 AND ( end_date_active IS NULL
518 OR end_date_active > SYSDATE
519 )
520 AND parent_object_id = mog.parent_object_id)))
521 OR
522 --if queried by assembly lot of lot serial, then check if it's parent has aser as child
523 EXISTS (
524 SELECT 1
525 FROM DUAL
526 WHERE EXISTS (
527 SELECT mog.object_id
528 FROM mtl_object_genealogy mog
529 WHERE parent_object_type = 1
530 AND object_type = 5
531 AND parent_object_id = NVL (p_object_id, p_object_id2)
532 AND EXISTS (
533 SELECT 1
534 FROM mtl_object_genealogy
535 WHERE genealogy_origin = 1
536 AND object_type = 2
537 AND parent_object_type = 5
538 AND ( end_date_active IS NULL
539 OR end_date_active > SYSDATE
540 )
541 AND parent_object_id = mog.object_id)))
542 OR
543 --if queried by job and if it has aser as child
544 EXISTS (
545 SELECT 1
546 FROM DUAL
547 WHERE EXISTS (
548 SELECT 1
549 FROM mtl_object_genealogy mog
550 WHERE parent_object_type = 5
551 AND object_type = 2
552 AND genealogy_origin=1
553 AND parent_object_id=p_object_id)) ;*/
554 --Bug 13866934 commenting the cusror for adding the new one
555 jRec jRecord%ROWTYPE;
556
557
558 BEGIN
559 inv_trx_util_pub.TRACE('gjData'||g_jData, 'INV_OBJECT_GENEALOGY', 9);
560 OPEN jRecord;
561 FETCH jRecord INTO jRec;
562 IF jRecord%found THEN
563 g_jData:=1;
564 ELSE
565 g_jData:=0;
566 END IF;
567 RETURN (g_jData);
568 END getjData;
569
570
571 FUNCTION getobjectnumber(
572 p_object_id IN NUMBER
573 , p_object_type IN NUMBER
574 , p_object_id2 IN NUMBER DEFAULT NULL
575 , p_object_type2 IN NUMBER DEFAULT NULL
576 )
577 RETURN VARCHAR2 IS
578 l_object_number VARCHAR2(1200) := 'NULL';
579 l_orgn_id NUMBER;
580 l_item_id NUMBER;
581 l_job_name VARCHAR2(240);
582 l_con_seg VARCHAR2(240);
583 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
584 l_genealogy_prefix_suffix NUMBER := fnd_profile.VALUE('GENEALOGY_PREFIX_SUFFIX');
585 l_genealogy_delimitter VARCHAR2(1) := NVL(fnd_profile.VALUE('GENEALOGY_DELIMITER'), '.');
586 l_lot_number VARCHAR2(80) := NULL;
587 BEGIN
588 IF p_object_type = 1 THEN /* object_type = 1 means lot control */
589 -- Lot Genealogy is available even if WMS is not installed
590 IF l_genealogy_prefix_suffix = 1 THEN --Prefix
591 SELECT lot_number || l_genealogy_delimitter || concatenated_segments
592 INTO l_object_number
593 FROM mtl_lot_numbers mln, mtl_system_items_kfv msikfv
594 WHERE mln.gen_object_id = p_object_id
595 AND mln.inventory_item_id = msikfv.inventory_item_id
596 AND mln.organization_id = msikfv.organization_id;
597 ELSIF l_genealogy_prefix_suffix = 2 THEN --Suffix
598 SELECT concatenated_segments || l_genealogy_delimitter || lot_number
599 INTO l_object_number
600 FROM mtl_lot_numbers mln, mtl_system_items_kfv msikfv
601 WHERE mln.gen_object_id = p_object_id
602 AND mln.inventory_item_id = msikfv.inventory_item_id
603 AND mln.organization_id = msikfv.organization_id;
604 ELSIF l_genealogy_prefix_suffix = 3 THEN --None
605 SELECT lot_number
606 INTO l_object_number
607 FROM mtl_lot_numbers mln, mtl_system_items_kfv msikfv
608 WHERE mln.gen_object_id = p_object_id
609 AND mln.inventory_item_id = msikfv.inventory_item_id
610 AND mln.organization_id = msikfv.organization_id;
611 END IF;
612 ELSIF p_object_type = 2 THEN
613 IF p_object_id2 IS NOT NULL
614 AND p_object_type2 = 1 THEN -- Lot Serial controlled
615 BEGIN
616 SELECT lot_number
617 INTO l_lot_number
618 FROM mtl_lot_numbers mln, mtl_system_items_kfv msikfv
619 WHERE mln.gen_object_id = p_object_id2
620 AND mln.inventory_item_id = msikfv.inventory_item_id
621 AND mln.organization_id = msikfv.organization_id;
622 EXCEPTION
623 WHEN NO_DATA_FOUND THEN
624 NULL;
625 END;
626 END IF;
627
628 IF inv_install.adv_inv_installed(p_organization_id => NULL) THEN
629 IF l_genealogy_prefix_suffix = 1 THEN -- prefix
630 SELECT l_lot_number
631 || DECODE(l_lot_number, NULL, '', l_genealogy_delimitter)
632 || serial_number
633 || l_genealogy_delimitter
634 || concatenated_segments
635 INTO l_object_number
636 FROM mtl_serial_numbers msn, mtl_system_items_kfv msikfv
637 WHERE msn.gen_object_id = p_object_id
638 AND msn.inventory_item_id = msikfv.inventory_item_id
639 AND msn.current_organization_id = msikfv.organization_id;
640 ELSIF l_genealogy_prefix_suffix = 2 THEN -- suffix
641 SELECT concatenated_segments
642 || DECODE(l_lot_number, NULL, '', l_genealogy_delimitter)
643 || l_lot_number
644 || l_genealogy_delimitter
645 || serial_number
646 INTO l_object_number
647 FROM mtl_serial_numbers msn, mtl_system_items_kfv msikfv
648 WHERE msn.gen_object_id = p_object_id
649 AND msn.inventory_item_id = msikfv.inventory_item_id
650 AND msn.current_organization_id = msikfv.organization_id;
651 ELSIF l_genealogy_prefix_suffix = 3 THEN --None
652 SELECT l_lot_number || DECODE(l_lot_number, NULL, '', l_genealogy_delimitter) || serial_number
653 INTO l_object_number
654 FROM mtl_serial_numbers msn, mtl_system_items_kfv msikfv
655 WHERE msn.gen_object_id = p_object_id
656 AND msn.inventory_item_id = msikfv.inventory_item_id
657 AND msn.current_organization_id = msikfv.organization_id;
658 END IF;
659 ELSE
660 SELECT l_lot_number || DECODE(l_lot_number, NULL, '', l_genealogy_delimitter) || serial_number
661 INTO l_object_number
662 FROM mtl_serial_numbers
663 WHERE gen_object_id = p_object_id;
664 END IF;
665 ELSIF p_object_type = 5 THEN
666 SELECT we.primary_item_id
667 , we.organization_id
668 , we.wip_entity_name
669 INTO l_item_id
670 , l_orgn_id
671 , l_job_name
672 FROM wip_entities we
673 WHERE we.gen_object_id = p_object_id;
674
675 IF (l_item_id IS NOT NULL) THEN
676 SELECT concatenated_segments
677 INTO l_con_seg
678 FROM mtl_system_items_kfv msikfv
679 WHERE msikfv.inventory_item_id = l_item_id
680 AND msikfv.organization_id = l_orgn_id;
681 ELSE
682 l_con_seg := '';
683 END IF;
684
685 IF l_genealogy_prefix_suffix = 1 THEN
686 l_object_number := l_job_name || l_genealogy_delimitter || l_con_seg;
687 ELSIF l_genealogy_prefix_suffix = 2 THEN
688 l_object_number := l_con_seg || l_genealogy_delimitter || l_job_name;
689 ELSIF l_genealogy_prefix_suffix = 3 THEN
690 l_object_number := l_con_seg;
691 END IF;
692 END IF;
693
694 RETURN(l_object_number);
695 END getobjectnumber;
696
697
698 FUNCTION getsource(p_org_id IN NUMBER, p_trx_src_type IN NUMBER, p_trx_src_id IN NUMBER)
699 RETURN VARCHAR2 IS
700 l_trx_src VARCHAR2(30);
701 row_count NUMBER := 0;
702 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
703 BEGIN
704 IF p_trx_src_type = 1 THEN -- PO
705 SELECT segment1
706 INTO l_trx_src
707 FROM po_headers_all
708 WHERE po_header_id = p_trx_src_id;
709 ELSIF p_trx_src_type IN(2, 8, 12) THEN -- SO,Internal Order,RMA
710 SELECT SUBSTRB(concatenated_segments, 1, 30) --BUG11856545
711 INTO l_trx_src
712 FROM mtl_sales_orders_kfv
713 WHERE sales_order_id = p_trx_src_id;
714 /* Removed p_trx-src_type = 3 for 2 reasons. one, that is function is not
715 used at all. Second that mtl_object_genealogy table never has data from this
716 source. Bug 4237802 */
717 ELSIF p_trx_src_type = 4 THEN -- Move Orders
718 SELECT request_number
719 INTO l_trx_src
720 FROM mtl_txn_request_headers
721 WHERE header_id = p_trx_src_id;
722 ELSIF p_trx_src_type = 5 THEN -- WIP
723 SELECT wip_entity_name
724 INTO l_trx_src
725 FROM wip_entities
726 WHERE wip_entity_id = p_trx_src_id
727 AND organization_id = p_org_id;
728 ELSIF p_trx_src_type = 6 THEN -- Account Alias
729 SELECT SUBSTR(concatenated_segments, 1, 30)
730 INTO l_trx_src
731 FROM mtl_generic_dispositions_kfv
732 WHERE disposition_id = p_trx_src_id
733 AND organization_id = p_org_id;
734 ELSIF p_trx_src_type = 7 THEN -- Internal Requisition
735 SELECT segment1
736 INTO l_trx_src
737 FROM po_requisition_headers_all
738 WHERE requisition_header_id = p_trx_src_id;
739 ELSIF p_trx_src_type = 9 THEN -- Cycle Count
740 SELECT cycle_count_header_name
741 INTO l_trx_src
742 FROM mtl_cycle_count_headers
743 WHERE cycle_count_header_id = p_trx_src_id
744 AND organization_id = p_org_id;
745 ELSIF p_trx_src_type = 10 THEN -- Physical Inventory
746 SELECT physical_inventory_name
747 INTO l_trx_src
748 FROM mtl_physical_inventories
749 WHERE physical_inventory_id = p_trx_src_id
750 AND organization_id = p_org_id;
751 ELSIF p_trx_src_type = 11 THEN -- Standard Cost Update
752 SELECT description
753 INTO l_trx_src
754 FROM cst_cost_updates
755 WHERE cost_update_id = p_trx_src_id
756 AND organization_id = p_org_id;
757 ELSIF p_trx_src_type = 13 THEN -- Inventory
758 -- Bug 2666620: BackFlush MO Type Removed. Hence checking for TxnSourceType also.
759 SELECT COUNT(*)
760 INTO row_count
761 FROM mtl_txn_request_lines mol
762 WHERE txn_source_id = p_trx_src_id
763 AND organization_id = p_org_id
764 AND EXISTS(SELECT NULL
765 FROM mtl_txn_request_headers
766 WHERE header_id = mol.header_id
767 AND move_order_type = 5
768 AND mol.transaction_source_type_id = 13);
769
770 IF row_count > 0 THEN
771 SELECT wip_entity_name
772 INTO l_trx_src
773 FROM wip_entities
774 WHERE wip_entity_id = p_trx_src_id
775 AND organization_id = p_org_id;
776 END IF;
777 END IF;
778
779 RETURN l_trx_src;
780 EXCEPTION
781 WHEN OTHERS THEN
782 RETURN NULL;
783 END getsource;
784
785 FUNCTION gettradingpartner(
786 p_org_id IN NUMBER
787 , p_trx_src_type IN NUMBER
788 , p_trx_src_id IN NUMBER
789 , p_trx_src_line_id IN NUMBER
790 , p_transfer_org_id IN NUMBER
791 )
792 RETURN VARCHAR2 IS
793 l_trading_partner VARCHAR2(240) := NULL;
794 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
795 BEGIN
796 /* For Bug#3420761: Changed the code p_trx_src_type = 2 to
797 p_trx_src_type in (2,12) to handle RMA transactions */
798 IF p_trx_src_type = 1 THEN -- PO
799 SELECT vendor_name
800 INTO l_trading_partner
801 FROM po_vendors pov, po_headers_all poh
802 WHERE poh.po_header_id = p_trx_src_id
803 AND poh.vendor_id = pov.vendor_id;
804 ELSIF p_trx_src_type IN(2, 12) THEN -- SO,RMA
805 SELECT party_name
806 INTO l_trading_partner
807 FROM hz_parties hp, hz_cust_accounts hca ,
808 -- R12 TCA Mandate to replace RA_CUSTOMERS with the above 2
809 oe_order_headers_all sha, oe_order_lines_all sla
810 WHERE sla.line_id = p_trx_src_line_id
811 AND sha.header_id = sla.header_id
812 --AND sha.sold_to_org_id = rac.customer_id; As part of R12 TCA changes
813 AND sha.sold_to_org_id = hca.cust_account_id
814 AND hca.party_id = hp.party_id;
815 ELSIF p_trx_src_type IN(7, 8) THEN
816 SELECT organization_code
817 INTO l_trading_partner
818 FROM mtl_parameters mp
819 WHERE mp.organization_id = p_transfer_org_id;
820 END IF;
821
822 RETURN l_trading_partner;
823 EXCEPTION
824 WHEN OTHERS THEN
825 RETURN '';
826 END gettradingpartner;
827
828 -- Added this pacakage as part of Bug 4018721
829 PROCEDURE init IS
830 BEGIN
831 g_ind := 0;
832 g_treeno := 1;
833 g_depth := 1;
834
835 DELETE FROM mtl_gen_temp;
836 END init;
837
838 PROCEDURE inv_populate_child(
839 p_object_id IN NUMBER
840 , p_related_object_id IN NUMBER
841 , p_object_type IN NUMBER DEFAULT NULL
842 , p_object_id2 IN NUMBER DEFAULT NULL
843 , p_object_type2 IN NUMBER DEFAULT NULL
844 , P_Genealogy_Order In Varchar2 Default Null -- added for the bug 16512538
845 ) IS
846 l_count NUMBER := 0;
847 l_previous_parent NUMBER := 0;
848 get_hierc VARCHAR2(1) := 'Y';
849
850 -- get_hierc is used to decide whether to select the next level of nodes for the selected node
851 -- do not get the next level, if the node has already been processed once.
852
853 CURSOR search_cur IS
854 SELECT *
855 FROM mtl_gen_temp
856 WHERE treeno < g_treeno
857 AND DEPTH < g_depth
858 ORDER BY ind DESC;
859
860 /* R12 Lot Serial Genealogy Project : Modified cursor */
861
862 /*11510 record group*/
863 CURSOR child_cur1 IS
864 SELECT object_id
865 , parent_object_id
866 , object_type
867 , NULL object_id2
868 , NULL object_type2
869 , NULL parent_object_id2
870 FROM mtl_object_genealogy
871 WHERE parent_object_id = p_object_id
872 AND object_type<>2
873 AND (end_date_active is null or end_date_active > SYSDATE);
874 --AND ((object_type = 2 AND parent_object_type = object_type) OR (object_type <> 2))
875
876
877 /* R12 RECORD GROUP*/
878 CURSOR child_cur2 IS
879 SELECT object_id
880 , parent_object_id
881 , object_type
882 , object_id2
883 , object_type2
884 , parent_object_id2
885 FROM mtl_object_genealogy
886 WHERE parent_object_id = p_object_id
887 AND (p_object_id2 IS NULL OR parent_object_id2 = p_object_id2)
888 AND (end_date_active IS NULL OR end_date_active > SYSDATE);
889 /*
890 Added the below cursor to get the data form mtl_object_genealogy table in decending order.
891 this cursor is created as part of the bug 16512538
892 */
893 CURSOR child_cur3 IS
894 SELECT object_id
895 , parent_object_id
896 , object_type
897 , object_id2
898 , object_type2
899 , parent_object_id2
900 FROM mtl_object_genealogy
901 WHERE parent_object_id = p_object_id
902 AND (p_object_id2 IS NULL OR parent_object_id2 = p_object_id2)
903 And (End_Date_Active Is Null Or End_Date_Active > Sysdate)
904 order by creation_date desc;
905
906 /*
907 Added the below cursor to get the data form mtl_object_genealogy table in decending order.
908 this cursor is created as part of the bug 16512538
909 */
910
911 CURSOR child_cur4 IS
912 SELECT object_id
913 , parent_object_id
914 , object_type
915 , object_id2
916 , object_type2
917 , parent_object_id2
918 FROM mtl_object_genealogy
919 WHERE parent_object_id = p_object_id
920 AND (p_object_id2 IS NULL OR parent_object_id2 = p_object_id2)
921 And (End_Date_Active Is Null Or End_Date_Active > Sysdate)
922 order by creation_date asc;
923
924 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
925
926
927 BEGIN
928 -- First condition
929 -- ===============
930 -- Check if the p_object_id is the first node of the tree
931 -- if yes, then no need to check other trees or nodes
932 IF (l_debug = 1) THEN
933 inv_trx_util_pub.TRACE('in the procedure inv_populate_child', 'INV_OBJECT_GENEALOGY', 9);
934 inv_trx_util_pub.TRACE('input param values to this proc are:', 'INV_OBJECT_GENEALOGY', 9);
935 inv_trx_util_pub.TRACE('p_object_id is ' || p_object_id || 'p_object_type ' || p_object_type, 'INV_OBJECT_GENEALOGY', 9);
936 inv_trx_util_pub.TRACE('p_object_id2 is ' || p_object_id2 || 'p_object_type2 ' || p_object_type2, 'INV_OBJECT_GENEALOGY', 9);
937 inv_trx_util_pub.TRACE('p_related_object_id is ' || p_related_object_id, 'INV_OBJECT_GENEALOGY', 9);
938 Inv_Trx_Util_Pub.Trace('GENEALOGY_ORDER is ' || P_Genealogy_Order, 'INV_OBJECT_GENEALOGY', 9);
939 END IF;
940
941 IF g_ind > 1 THEN
942 BEGIN
943 SELECT 1
944 INTO l_count
945 FROM mtl_gen_temp
946 WHERE label = p_object_id
947 AND related_label = 0
948 AND (p_object_id2 is null or label2=p_object_id2)
949 AND ROWNUM < 2;
950
951 IF l_count > 0 THEN
952 get_hierc := 'N';
953 g_ind := g_ind + 1;
954
955 IF l_debug =1 THEN
956 inv_trx_util_pub.TRACE('insert1, g_ind '||g_ind, 'INV_OBJECT_GENEALOGY', 9);
957 END IF;
958
959 INSERT INTO mtl_gen_temp
960 (
961 ind
962 , treeno
963 , DEPTH
964 , label
965 , related_label
966 , child_object_type
967 , label2
968 , child_object_type2
969 )
970 VALUES (
971 g_ind
972 , g_treeno
973 , g_depth
974 , p_object_id
975 , p_related_object_id
976 , p_object_type
977 , p_object_id2
978 , p_object_type2
979 );
980 END IF;
981 EXCEPTION
982 WHEN NO_DATA_FOUND THEN
983 l_count := 0;
984 IF l_debug = 1 THEN
985 inv_trx_util_pub.TRACE('exception 1', 'INV_OBJECT_GENEALOGY', 9);
986 END IF;
987
988 END;
989 END IF;
990
991 -- Second Condition
992 -- ================
993 IF g_ind > 1 and get_hierc='Y' THEN
994 BEGIN
995 SELECT 1
996 INTO l_count
997 FROM mtl_gen_temp
998 WHERE treeno = g_treeno
999 AND label = p_object_id
1000 AND (p_object_id2 is null or label2=p_object_id2)
1001 AND ROWNUM < 2;
1002
1003 -- If the node already exists in the present tree , that means
1004 -- it need not be exploded further.
1005 IF l_count > 0 THEN
1006 get_hierc := 'N';
1007 g_ind := g_ind + 1;
1008 IF l_debug=1 THEN
1009 inv_trx_util_pub.TRACE('insert2 g_ind '||g_ind, 'INV_OBJECT_GENEALOGY', 9);
1010 END IF;
1011
1012 INSERT INTO mtl_gen_temp
1013 (
1014 ind
1015 , treeno
1016 , DEPTH
1017 , label
1018 , related_label
1019 , child_object_type
1020 , label2
1021 , child_object_type2
1022 )
1023 VALUES (
1024 g_ind
1025 , g_treeno
1026 , g_depth
1027 , p_object_id
1028 , p_related_object_id
1029 , p_object_type
1030 , p_object_id2
1031 , p_object_type2
1032 );
1033 END IF;
1034 EXCEPTION
1035 WHEN NO_DATA_FOUND THEN
1036 l_count := 0;
1037 IF l_debug=1 THEN
1038 inv_trx_util_pub.TRACE('exception 2', 'INV_OBJECT_GENEALOGY', 9);
1039 END IF;
1040
1041 END;
1042 END IF;
1043
1044
1045 -- Third condition
1046 -- ===============
1047 IF get_hierc = 'Y'
1048 AND g_treeno > 1 THEN
1049 l_previous_parent := 0;
1050
1051 FOR cur_var IN search_cur LOOP
1052 IF cur_var.label = p_object_id
1053 AND cur_var.label = l_previous_parent THEN
1054 get_hierc := 'N';
1055 g_ind := g_ind + 1;
1056 IF l_debug=1 THEN
1057 inv_trx_util_pub.TRACE('insert3 g_ind '||g_ind, 'INV_OBJECT_GENEALOGY', 9);
1058 END IF;
1059
1060 INSERT INTO mtl_gen_temp
1061 (
1062 ind
1063 , treeno
1064 , DEPTH
1065 , label
1066 , related_label
1067 , child_object_type
1068 , label2
1069 , child_object_type2
1070 )
1071 VALUES (
1072 g_ind
1073 , g_treeno
1074 , g_depth
1075 , p_object_id
1076 , p_related_object_id
1077 , p_object_type
1078 , p_object_id2
1079 , p_object_type2
1080 );
1081
1082 EXIT;
1083 END IF;
1084
1085 l_previous_parent := cur_var.related_label;
1086 END LOOP;
1087 END IF;
1088
1089 IF (get_hierc = 'Y') THEN
1090 g_ind := g_ind + 1;
1091 IF l_debug=1 THEN
1092 inv_trx_util_pub.TRACE('insert4 g_ind '||g_ind, 'INV_OBJECT_GENEALOGY', 9);
1093 END IF;
1094
1095 INSERT INTO mtl_gen_temp
1096 (
1097 ind
1098 , treeno
1099 , DEPTH
1100 , label
1101 , related_label
1102 , child_object_type
1103 , label2
1104 , child_object_type2
1105 )
1106 VALUES (
1107 g_ind
1108 , g_treeno
1109 , g_depth
1110 , p_object_id
1111 , p_related_object_id
1112 , p_object_type
1113 , p_object_id2
1114 , p_object_type2
1115 );
1116 IF p_related_object_id <> p_object_id THEN
1117 IF g_jData=1 THEN
1118 FOR child_rec IN child_cur1 LOOP
1119 g_depth := g_depth + 1;
1120
1121 -- added the following condition, so that if for some reason there is an end-less
1122 -- loop, it will atleast stop after the depth of 45 and will not 'disconnect the server'
1123 -- Please note that if the user really has a hieracrchy that is more than 45 levels deep,
1124 -- The users may report a bug that the complete information is not diaplyes
1125 -- We do not expect anyone to have such a deep hierarchy
1126 IF g_depth > 45 THEN
1127 EXIT;
1128 END IF;
1129
1130 inv_populate_child(child_rec.object_id, child_rec.parent_object_id, child_rec.object_type, child_rec.object_id2
1131 , child_rec.object_type2);
1132 g_depth := g_depth - 1;
1133 g_treeno := g_treeno + 1;
1134 END LOOP;
1135 ELSE
1136 /*
1137 Added the below if condition to check what is order passed to the procedure.
1138 and based on the order cursor will be called accordingly
1139 These changes are done based on the bug 16512538
1140 */
1141 IF (p_GENEALOGY_ORDER='DESC') THEN
1142
1143 FOR child_rec IN child_cur3 LOOP
1144 g_depth := g_depth + 1;
1145
1146 IF g_depth > 45 THEN
1147 EXIT;
1148 END IF;
1149
1150 Inv_Populate_Child(Child_Rec.Object_Id, Child_Rec.Parent_Object_Id, Child_Rec.Object_Type, Child_Rec.Object_Id2
1151 , child_rec.object_type2,p_GENEALOGY_ORDER);
1152 g_depth := g_depth - 1;
1153 g_treeno := g_treeno + 1;
1154 End Loop;
1155 Elsif (P_Genealogy_Order='ASC') Then
1156 FOR child_rec IN child_cur4 LOOP
1157 g_depth := g_depth + 1;
1158
1159 IF g_depth > 45 THEN
1160 EXIT;
1161 END IF;
1162
1163 inv_populate_child(child_rec.object_id, child_rec.parent_object_id, child_rec.object_type, child_rec.object_id2
1164 , child_rec.object_type2 ,p_GENEALOGY_ORDER);
1165 g_depth := g_depth - 1;
1166 g_treeno := g_treeno + 1;
1167 End Loop;
1168 ELSE
1169 FOR child_rec IN child_cur2 LOOP
1170 g_depth := g_depth + 1;
1171
1172 IF g_depth > 45 THEN
1173 EXIT;
1174 END IF;
1175
1176 inv_populate_child(child_rec.object_id, child_rec.parent_object_id, child_rec.object_type, child_rec.object_id2
1177 , child_rec.object_type2 ,p_GENEALOGY_ORDER);
1178 g_depth := g_depth - 1;
1179 g_treeno := g_treeno + 1;
1180 End Loop;
1181 END IF;
1182 END IF;
1183 END IF;
1184 END IF;
1185 END inv_populate_child;
1186
1187 PROCEDURE inv_populate_child_tree(
1188 p_object_id IN NUMBER
1189 , p_related_object_id IN NUMBER
1190 , p_object_type IN NUMBER DEFAULT NULL
1191 , p_object_id2 IN NUMBER DEFAULT NULL
1192 , p_object_type2 IN NUMBER DEFAULT NULL
1193 , p_GENEALOGY_ORDER IN VARCHAR2 default null
1194 ) IS
1195 l_count NUMBER;
1196 l_prev_parent NUMBER := 1;
1197 l_min_level NUMBER := 1;
1198 --Bug 8467584
1199 n_level NUMBER;
1200 n_rows NUMBER;
1201
1202 /* R12 Lot Serial Genealogy Project : Modified cursor */
1203
1204 /*11510 record group*/
1205
1206 --Bug 6600064, Added end_date_active condition in the START WITH clause in child_cur1 and child_cur1
1207 --Bug 6643575, removed the where clause logic and moved it to the to connect by clause in child_cur1 and child_cur1
1208
1209 CURSOR child_cur1 IS
1210 SELECT level
1211 , mog.object_id
1212 , mog.parent_object_id
1213 , mog.object_type
1214 , NULL object_id2
1215 , NULL object_type2
1216 , NULL parent_object_id2
1217 FROM mtl_object_genealogy mog
1218 -- WHERE object_type<>2--((object_type = 2 AND parent_object_type = object_type) OR (object_type <> 2))
1219 -- AND (end_date_active is null or end_date_active > SYSDATE)
1220 START WITH (parent_object_id=p_object_id and (end_date_active is null or end_date_active > SYSDATE))
1221 CONNECT BY nocycle prior object_id = parent_object_id --12701288
1222 AND object_type<>2 --((object_type = 2 AND parent_object_type = object_type) OR (object_type <> 2))
1223 AND (end_date_active is null or end_date_active > SYSDATE);
1224
1225
1226 --R12 record group
1227 --Bug 8467584, Added v_level_num filter
1228 CURSOR child_cur2(v_level_num NUMBER) IS
1229 SELECT LEVEL
1230 , object_id
1231 , parent_object_id
1232 , object_type
1233 , object_id2
1234 , object_type2
1235 , parent_object_id2
1236 FROM mtl_object_genealogy
1237 -- WHERE end_date_active IS NULL
1238 -- OR end_date_active > SYSDATE
1239 START WITH (parent_object_id=p_object_id AND (end_date_active IS NULL OR end_date_active > SYSDATE))
1240 CONNECT BY nocycle PRIOR object_id = parent_object_id --12701288
1241 AND (end_date_active IS NULL OR end_date_active > SYSDATE)
1242 AND LEVEL < v_level_num;
1243
1244 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
1245 /*End of R12 Genealogy Lot serial Project*/
1246
1247 BEGIN
1248 IF (l_debug = 1) THEN
1249 inv_trx_util_pub.TRACE('in the procedure inv_populate_child_tree', 'INV_OBJECT_GENEALOGY', 9);
1250 inv_trx_util_pub.TRACE('input param values to this proc are:', 'INV_OBJECT_GENEALOGY', 9);
1251 inv_trx_util_pub.TRACE('p_object_id is ' || p_object_id || 'p_object_type ' || p_object_type, 'INV_OBJECT_GENEALOGY', 9);
1252 inv_trx_util_pub.TRACE('p_object_id2 is ' || p_object_id2 || 'p_object_type2 ' || p_object_type2, 'INV_OBJECT_GENEALOGY', 9);
1253 inv_trx_util_pub.TRACE('p_related_object_id is ' || p_related_object_id, 'INV_OBJECT_GENEALOGY', 9);
1254 inv_trx_util_pub.TRACE('GENEALOGY_ORDER== '||p_GENEALOGY_ORDER, 'INV_OBJECT_GENEALOGY', 9);
1255 END IF;
1256
1257 inv_object_genealogy.init;
1258 /*
1259 added the below code for the bug 16512538
1260 earlier Inv_Populate_Child is called only if there is any exception occured in inv_populate_child_tree
1261 since in the current procedure ordering is not possible hence we are calling the procedure inv_populate_child
1262 so that genealogy can be shown in some order.
1263 */
1264 if (p_GENEALOGY_ORDER='DESC' or p_GENEALOGY_ORDER='ASC') then
1265 Inv_Populate_Child(P_Object_Id, P_Related_Object_Id, P_Object_Type, P_Object_Id2, P_Object_Type2,P_Genealogy_Order);
1266 return;
1267 end if;
1268 g_ind := g_ind + 1;
1269 l_prev_parent := p_related_object_id;
1270
1271
1272
1273 INSERT INTO mtl_gen_temp
1274 (
1275 ind
1276 , treeno
1277 , DEPTH
1278 , label
1279 , related_label
1280 , child_object_type
1281 , label2
1282 , child_object_type2
1283 )
1284 VALUES (
1285 g_ind
1286 , g_treeno
1287 , g_depth
1288 , p_object_id
1289 , p_related_object_id
1290 , p_object_type
1291 , p_object_id2
1292 , p_object_type2
1293 );
1294
1295 IF g_jData=1 THEN
1296 --open 11510 cursor
1297 FOR child_rec IN child_cur1
1298 LOOP
1299 IF child_rec.parent_object_id = l_prev_parent THEN
1300 g_treeno := g_treeno + 1;
1301 END IF;
1302
1303 IF l_min_level = child_rec.LEVEL THEN
1304 g_treeno := g_ind;
1305 END IF;
1306
1307 IF g_depth < child_rec.LEVEL + 1 THEN
1308 g_depth := child_rec.LEVEL + 1;
1309 END IF;
1310
1311 g_ind := g_ind + 1;
1312
1313 INSERT INTO mtl_gen_temp
1314 (
1315 ind
1316 , treeno
1317 , DEPTH
1318 , label
1319 , related_label
1320 , child_object_type
1321 , label2
1322 , child_object_type2
1323 )
1324 VALUES
1325 ( g_ind,
1326 g_treeno,
1327 child_rec.level+1,
1328 child_rec.object_id,
1329 child_rec.parent_object_id,
1330 child_rec.object_type,
1331 child_rec.object_id2,
1332 child_rec.object_type2);
1333
1334 l_prev_parent := child_rec.parent_object_id;
1335 END LOOP;
1336
1337 ELSE
1338
1339 -- Dynamic Level calculation: bug 8467584
1340 -- Start with n_level = 2, calculate number of rows fetched (n_rows), compare with g_rowlimit.
1341 -- If less, increment n_level. Continue till n_rows > g_rowlimit.
1342 -- Pass (level - 1) to cursor.
1343
1344 n_level := 2;
1345 n_rows := 1;
1346
1347 LOOP
1348 SELECT Count(*)
1349 INTO n_rows
1350 FROM mtl_object_genealogy
1351 --WHERE level < n_level
1352 START WITH (parent_object_id=p_object_id AND (end_date_active IS NULL OR end_date_active > SYSDATE))
1353 CONNECT BY nocycle PRIOR object_id = parent_object_id --12701288
1354 AND (end_date_active IS NULL OR end_date_active > SYSDATE)
1355 AND LEVEL < n_level;
1356
1357 EXIT WHEN (n_rows > (g_rowlimit-1) OR n_rows = 0 OR n_level > 45);
1358 --assuming g_rowlimit records to be a 'safe' limit for displaying genealogy
1359 --max levels displayed: 45 (hard-coded in inv_populate_child)
1360 n_level := n_level + 1;
1361 END LOOP;
1362
1363 IF (l_debug = 1) THEN
1364 inv_trx_util_pub.TRACE('Fetching child_cur2 for level='||(n_level-1), 'INV_OBJECT_GENEALOGY', 9);
1365 END IF;
1366
1367 --open r12 cursor
1368 FOR child_rec IN child_cur2(n_level-1)
1369 LOOP
1370 IF child_rec.parent_object_id = l_prev_parent THEN
1371 g_treeno := g_treeno + 1;
1372 END IF;
1373
1374 IF l_min_level = child_rec.LEVEL THEN
1375 g_treeno := g_ind;
1376 END IF;
1377
1378 IF g_depth < child_rec.LEVEL + 1 THEN
1379 g_depth := child_rec.LEVEL + 1;
1380 END IF;
1381
1382 g_ind := g_ind + 1;
1383
1384 INSERT INTO mtl_gen_temp
1385 (
1386 ind
1387 , treeno
1388 , DEPTH
1389 , label
1390 , related_label
1391 , child_object_type
1392 , label2
1393 , child_object_type2
1394 )
1395 VALUES
1396 ( g_ind,
1397 g_treeno,
1398 child_rec.level+1,
1399 child_rec.object_id,
1400 child_rec.parent_object_id,
1401 child_rec.object_type,
1402 child_rec.object_id2,
1403 child_rec.object_type2);
1404
1405 l_prev_parent := child_rec.parent_object_id;
1406 END LOOP;
1407
1408
1409 END IF;
1410
1411
1412 EXCEPTION
1413 WHEN OTHERS THEN
1414 IF l_debug = 1 THEN
1415 inv_log_util.trace('Exception:'||SQLERRM,'INV_OBJECT_GENEALOGY',9);
1416 END IF;
1417 inv_object_genealogy.init;
1418 /* R12 Lot Serial Genealogy Project : Modified call to procedure to include 2 new parameters p_object_id2, p_object_type2 */
1419 inv_populate_child(p_object_id, p_related_object_id, p_object_type, p_object_id2, p_object_type2);
1420 END inv_populate_child_tree;
1421
1422 PROCEDURE inv_populate_parent(
1423 p_object_id IN NUMBER
1424 , p_related_object_id IN NUMBER
1425 , p_object_type IN NUMBER DEFAULT NULL
1426 , p_object_id2 IN NUMBER DEFAULT NULL
1427 , p_object_type2 IN NUMBER DEFAULT NULL
1428 ) IS
1429 l_count NUMBER := 0;
1430 l_previous_parent NUMBER := 0;
1431 get_hierc VARCHAR2(1) := 'Y';
1432
1433 -- get_hierc is used to decide whether to select the next level of nodes for the selected node
1434 -- do not get the next level, if the node has already been processed once.
1435
1436 /* R12 Lot Serial Genealogy Project : Added cursors to job, direct genealogy types */
1437 CURSOR parent_cur IS
1438 SELECT parent_object_id
1439 , object_id
1440 , parent_object_type
1441 , parent_object_id2
1442 , parent_object_type2, object_id2,object_type2,object_type
1443 FROM mtl_object_genealogy
1444 WHERE object_id = p_object_id
1445 AND (p_object_id2 IS NULL OR object_id2 = p_object_id2)
1446 AND(end_date_active IS NULL
1447 OR end_date_active > SYSDATE);
1448
1449 CURSOR search_cur IS
1450 SELECT *
1451 FROM mtl_gen_temp
1452 WHERE treeno < g_treeno
1453 AND DEPTH < g_depth
1454 ORDER BY ind DESC;
1455
1456
1457 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
1458 BEGIN
1459 -- First condition
1460 -- ===============
1461 -- Check if the p_object_id is the first node of the tree
1462 -- if yes, then no need to check other trees or nodes
1463 IF (l_debug = 1) THEN
1464 inv_trx_util_pub.TRACE('in the procedure inv_populate_parent', 'INV_OBJECT_GENEALOGY', 9);
1465 inv_trx_util_pub.TRACE('input param values to this proc are:', 'INV_OBJECT_GENEALOGY', 9);
1466 inv_trx_util_pub.TRACE('p_object_id is ' || p_object_id || 'p_object_type ' || p_object_type, 'INV_OBJECT_GENEALOGY', 9);
1467 inv_trx_util_pub.TRACE('p_object_id2 is ' || p_object_id2 || 'p_object_type2 ' || p_object_type2, 'INV_OBJECT_GENEALOGY', 9);
1468 inv_trx_util_pub.TRACE('p_related_object_id is ' || p_related_object_id, 'INV_OBJECT_GENEALOGY', 9);
1469 END IF;
1470 IF g_ind > 0 THEN
1471 BEGIN
1472 SELECT 1
1473 INTO l_count
1474 FROM mtl_gen_temp
1475 WHERE label = p_object_id
1476 AND related_label = 0
1477 AND (p_object_id2 is null or label2=p_object_id2)
1478 AND ROWNUM < 2;
1479
1480 IF l_count > 0 THEN
1481 get_hierc := 'N';
1482 g_ind := g_ind + 1;
1483
1484 IF l_debug =1 THEN
1485 inv_trx_util_pub.TRACE('insert1, g_ind '||g_ind, 'INV_OBJECT_GENEALOGY', 9);
1486 END IF;
1487
1488
1489 INSERT INTO mtl_gen_temp
1490 (
1491 ind
1492 , treeno
1493 , DEPTH
1494 , label
1495 , related_label
1496 , child_object_type
1497 , label2
1498 , child_object_type2
1499 )
1500 VALUES (
1501 g_ind
1502 , g_treeno
1503 , g_depth
1504 , p_object_id
1505 , p_related_object_id
1506 , p_object_type
1507 , p_object_id2
1508 , p_object_type2
1509 );
1510 END IF;
1511 EXCEPTION
1512 WHEN NO_DATA_FOUND THEN
1513 l_count := 0;
1514 IF l_debug = 1 THEN
1515 inv_trx_util_pub.TRACE('exception 1', 'INV_OBJECT_GENEALOGY', 9);
1516 END IF;
1517
1518 END;
1519 END IF;
1520
1521 -- Second Condition
1522 -- ================
1523 IF g_ind > 1 and get_hierc='Y' THEN
1524 BEGIN
1525 SELECT 1
1526 INTO l_count
1527 FROM mtl_gen_temp
1528 WHERE treeno = g_treeno
1529 AND label = p_object_id
1530 AND (p_object_id2 is null or label2=p_object_id2)
1531 AND ROWNUM < 2;
1532
1533 -- If the node already exists in the present tree , that means
1534 -- it need not be exploded further.
1535 IF l_count > 0 THEN
1536 get_hierc := 'N';
1537 g_ind := g_ind + 1;
1538
1539 IF l_debug = 2 THEN
1540 inv_trx_util_pub.TRACE('insert2, g_ind '||g_ind, 'INV_OBJECT_GENEALOGY', 9);
1541 END IF;
1542
1543
1544 INSERT INTO mtl_gen_temp
1545 (
1546 ind
1547 , treeno
1548 , DEPTH
1549 , label
1550 , related_label
1551 , child_object_type
1552 , label2
1553 , child_object_type2
1554 )
1555 VALUES (
1556 g_ind
1557 , g_treeno
1558 , g_depth
1559 , p_object_id
1560 , p_related_object_id
1561 , p_object_type
1562 , p_object_id2
1563 , p_object_type2
1564 );
1565 END IF;
1566 EXCEPTION
1567 WHEN NO_DATA_FOUND THEN
1568 l_count := 0;
1569 IF l_debug = 1 THEN
1570 inv_trx_util_pub.TRACE('exception 2', 'INV_OBJECT_GENEALOGY', 9);
1571 END IF;
1572
1573 END;
1574 END IF;
1575 -- Third condition
1576 -- ===============
1577 IF get_hierc = 'Y'
1578 AND g_treeno > 1 THEN
1579 l_previous_parent := 0;
1580
1581 FOR cur_var IN search_cur LOOP
1582 IF cur_var.label = p_object_id
1583 AND cur_var.label = l_previous_parent THEN
1584 get_hierc := 'N';
1585 g_ind := g_ind + 1;
1586
1587 IF l_debug = 3 THEN
1588 inv_trx_util_pub.TRACE('insert3, g_ind '||g_ind, 'INV_OBJECT_GENEALOGY', 9);
1589 END IF;
1590
1591
1592 INSERT INTO mtl_gen_temp
1593 (
1594 ind
1595 , treeno
1596 , DEPTH
1597 , label
1598 , related_label
1599 , child_object_type
1600 , label2
1601 , child_object_type2
1602 )
1603 VALUES (
1604 g_ind
1605 , g_treeno
1606 , g_depth
1607 , p_object_id
1608 , p_related_object_id
1609 , p_object_type
1610 , p_object_id2
1611 , p_object_type2
1612 );
1613
1614 EXIT;
1615 END IF;
1616
1617 l_previous_parent := cur_var.related_label;
1618 END LOOP;
1619 END IF;
1620
1621 IF (get_hierc = 'Y') THEN
1622 g_ind := g_ind + 1;
1623
1624 IF l_debug =1 THEN
1625 inv_trx_util_pub.TRACE('insert4, g_ind '||g_ind, 'INV_OBJECT_GENEALOGY', 9);
1626 END IF;
1627
1628
1629 INSERT INTO mtl_gen_temp
1630 (
1631 ind
1632 , treeno
1633 , DEPTH
1634 , label
1635 , related_label
1636 , child_object_type
1637 , label2
1638 , child_object_type2
1639 )
1640 VALUES (
1641 g_ind
1642 , g_treeno
1643 , g_depth
1644 , p_object_id
1645 , p_related_object_id
1646 , p_object_type
1647 , p_object_id2
1648 , p_object_type2
1649 );
1650
1651 IF p_object_id <> p_related_object_id THEN
1652 FOR parent_rec IN parent_cur LOOP
1653 g_depth := g_depth + 1;
1654
1655 -- added the following condition, so that if for some reason there is an end-less
1656 -- loop, it will atleast stop after the depth of 45 and will not 'disconnect the server'
1657 -- Please note that if the user really has a hieracrchy that is more than 45 levels deep,
1658 -- The users may report a bug that the complete information is not diaplyes
1659 -- We do not expect anyone to have such a deep hierarchy
1660 IF g_depth > 45 THEN
1661 EXIT;
1662 END IF;
1663
1664 inv_populate_parent(
1665 parent_rec.parent_object_id
1666 , parent_rec.object_id
1667 , parent_rec.parent_object_type
1668 , parent_rec.parent_object_id2
1669 , parent_rec.parent_object_type2
1670 );
1671 g_depth := g_depth - 1;
1672 g_treeno := g_treeno + 1;
1673 END LOOP;
1674 END IF;
1675 END IF;
1676 END inv_populate_parent;
1677
1678 PROCEDURE inv_populate_parent_tree(
1679 p_object_id IN NUMBER
1680 , p_related_object_id IN NUMBER
1681 , p_object_type IN NUMBER DEFAULT NULL
1682 , p_object_id2 IN NUMBER DEFAULT NULL
1683 , p_object_type2 IN NUMBER DEFAULT NULL
1684 ) IS
1685 l_count NUMBER;
1686 l_prev_parent NUMBER := 1;
1687 l_min_level NUMBER := 1;
1688 -- Bug 8467584
1689 n_level NUMBER;
1690 n_rows NUMBER;
1691
1692 /* R12 Lot Serial Genealogy Project : Modified cursor */
1693 --Bug 6600064, Added end_date_active condition in the START WITH clause in parent_cur
1694 --Bug 6643575, removed the where clause logic and moved it to the to connect by clause in parent_cur
1695 --Bug 8467584, added v_level_num filter
1696 CURSOR parent_cur(v_level_num NUMBER) IS
1697 SELECT LEVEL
1698 , mog.parent_object_id
1699 , mog.object_id
1700 , mog.parent_object_type
1701 , mog.parent_object_id2
1702 , mog.parent_object_type2
1703 FROM mtl_object_genealogy mog
1704 -- WHERE end_date_active IS NULL
1705 -- OR end_date_active > SYSDATE
1706 START WITH (object_id = p_object_id AND (end_date_active IS NULL OR end_date_active > SYSDATE))
1707 CONNECT BY nocycle PRIOR parent_object_id = object_id --12701288
1708 AND (end_date_active IS NULL OR end_date_active > SYSDATE)
1709 AND LEVEL < v_level_num;
1710
1711
1712 /* End of R12 Lot Serial Genealogy Project */
1713 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
1714
1715
1716
1717 BEGIN
1718 IF (l_debug = 1) THEN
1719 inv_trx_util_pub.TRACE('in the procedure inv_populate_parent_tree', 'INV_OBJECT_GENEALOGY', 9);
1720 inv_trx_util_pub.TRACE('input param values to this proc are:', 'INV_OBJECT_GENEALOGY', 9);
1721 inv_trx_util_pub.TRACE('p_object_id is ' || p_object_id || 'p_object_type ' || p_object_type, 'INV_OBJECT_GENEALOGY', 9);
1722 inv_trx_util_pub.TRACE('p_object_id2 is ' || p_object_id2 || 'p_object_type2 ' || p_object_type2, 'INV_OBJECT_GENEALOGY', 9);
1723 inv_trx_util_pub.TRACE('p_related_object_id is ' || p_related_object_id, 'INV_OBJECT_GENEALOGY', 9);
1724 END IF;
1725
1726 inv_object_genealogy.init;
1727 g_ind := g_ind + 1;
1728 l_prev_parent := p_related_object_id;
1729
1730 INSERT INTO mtl_gen_temp
1731 (
1732 ind
1733 , treeno
1734 , DEPTH
1735 , label
1736 , related_label
1737 , child_object_type
1738 , label2
1739 , child_object_type2
1740 )
1741 VALUES (
1742 g_ind
1743 , g_treeno
1744 , g_depth
1745 , p_object_id
1746 , p_related_object_id
1747 , p_object_type
1748 , p_object_id2
1749 , p_object_type2
1750 );
1751
1752 --IF g_jData=1 THEN
1753 -- Dynamic Level calculation: bug 8467584
1754 -- Start with n_level = 2, calculate number of rows fetched (n_rows), compare with g_rowlimit.
1755 -- If less, increment n_level. Continue till n_rows > g_rowlimit.
1756 -- Pass (level - 1) to cursor.
1757
1758 n_level := 2;
1759 n_rows := 1;
1760
1761 LOOP
1762 SELECT Count(*)
1763 INTO n_rows
1764 FROM mtl_object_genealogy
1765 --WHERE level < n_level
1766 START WITH (object_id=p_object_id AND (end_date_active IS NULL OR end_date_active > SYSDATE))
1767 CONNECT BY nocycle PRIOR parent_object_id = object_id --12701288
1768 AND (end_date_active IS NULL OR end_date_active > SYSDATE)
1769 AND LEVEL < n_level;
1770
1771 EXIT WHEN (n_rows > (g_rowlimit-1) OR n_rows = 0 OR n_level > 45);
1772 --assuming g_rowlimit records to be a 'safe' limit for displaying genealogy
1773 --max levels displayed: 45 (hard-coded in inv_populate_child)
1774 n_level := n_level + 1;
1775 END LOOP;
1776
1777 IF (l_debug = 1) THEN
1778 inv_trx_util_pub.TRACE('Fetching parent_cur for level='||(n_level-1), 'INV_OBJECT_GENEALOGY', 9);
1779 END IF;
1780
1781 FOR parent_rec IN parent_cur(n_level-1) LOOP
1782 IF parent_rec.parent_object_id = l_prev_parent THEN
1783 g_treeno := g_treeno + 1;
1784 END IF;
1785
1786 IF l_min_level = parent_rec.LEVEL THEN
1787 g_treeno := g_ind;
1788 END IF;
1789
1790 IF g_depth < parent_rec.LEVEL + 1 THEN
1791 g_depth := parent_rec.LEVEL + 1;
1792 END IF;
1793
1794 g_ind := g_ind + 1;
1795
1796 INSERT INTO mtl_gen_temp
1797 (
1798 ind
1799 , treeno
1800 , DEPTH
1801 , label
1802 , related_label
1803 , child_object_type
1804 , label2
1805 , child_object_type2
1806 )
1807 VALUES (
1808 g_ind
1809 ,g_treeno
1810 ,parent_rec.level+1
1811 ,parent_rec.parent_object_id
1812 ,parent_rec.object_id
1813 ,parent_rec.parent_object_type
1814 ,parent_rec.parent_object_id2
1815 ,parent_rec.parent_object_type2);
1816
1817 l_prev_parent := parent_rec.parent_object_id;
1818 END LOOP;
1819 --END IF;
1820 EXCEPTION
1821 WHEN OTHERS THEN
1822 IF l_debug = 1 THEN
1823 inv_log_util.trace('Exception:'||SQLERRM,'INV_OBJECT_GENEALOGY',9);
1824 END IF;
1825 inv_object_genealogy.init;
1826 inv_populate_parent(p_object_id, p_related_object_id, p_object_type, p_object_id2, p_object_type2);
1827
1828 END inv_populate_parent_tree;
1829
1830 -- Bug 8467584
1831 PROCEDURE set_rowlimit(p_numrows IN NUMBER)
1832 IS
1833 l_debug NUMBER := Nvl(fnd_profile.Value('INV_DEBUG_TRACE'), 0);
1834 BEGIN
1835 IF g_rowlimit <> p_numrows THEN
1836 g_rowlimit := p_numrows;
1837 END IF;
1838 IF l_debug=1 THEN
1839 inv_trx_util_pub.TRACE('g_rowlimit='||g_rowlimit, 'INV_OBJECT_GENEALOGY', 9);
1840 END IF;
1841 END;
1842
1843 END inv_object_genealogy;
[Home] [Help]