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:
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
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
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
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
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
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
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
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
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: );
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
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: );
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
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:
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;
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
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
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
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
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
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
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: );
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 : '
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
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: );
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
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;
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);
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;
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:
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
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
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: (
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;
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: (
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;
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: (
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: (
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);
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;
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:
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;
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;
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);
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);
1300: inv_trx_util_pub.TRACE('p_related_object_id is ' || p_related_object_id, 'INV_OBJECT_GENEALOGY', 9);
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);
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);
1300: inv_trx_util_pub.TRACE('p_related_object_id is ' || p_related_object_id, 'INV_OBJECT_GENEALOGY', 9);
1301: END IF;
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);
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);
1300: inv_trx_util_pub.TRACE('p_related_object_id is ' || p_related_object_id, 'INV_OBJECT_GENEALOGY', 9);
1301: END IF;
1302: IF g_ind > 0 THEN
1295: IF (l_debug = 1) THEN
1296: inv_trx_util_pub.TRACE('in the procedure inv_populate_parent', '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);
1300: inv_trx_util_pub.TRACE('p_related_object_id is ' || p_related_object_id, 'INV_OBJECT_GENEALOGY', 9);
1301: END IF;
1302: IF g_ind > 0 THEN
1303: BEGIN
1296: inv_trx_util_pub.TRACE('in the procedure inv_populate_parent', '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);
1300: inv_trx_util_pub.TRACE('p_related_object_id is ' || p_related_object_id, 'INV_OBJECT_GENEALOGY', 9);
1301: END IF;
1302: IF g_ind > 0 THEN
1303: BEGIN
1304: SELECT 1
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
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;
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
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;
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
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
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);
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;
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:
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;
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;