[Home] [Help]
PACKAGE BODY: APPS.WSH_OPSM_ASN_ITEM_GENEALOGY
Source
1 PACKAGE BODY wsh_opsm_asn_item_genealogy AS
2 /* $Header: wshopsmasnigb.pls 120.9 2011/02/02 16:43:44 skaradib noship $ */
3
4
5 ---------------------------------------------------------------------------------------
6 --
7 -- Procedure: get_genealogy
8 -- Parameters: p_gen_object_id - It takes Genealogy object id has input
9 -- p_delivery_detail_id- It takes delivery_detail Id has input
10 -- p_delivery_id -It takes delivery_id has input
11 -- p_organization_id -It takes organization_id has input
12 -- p_frm_serial- This is not currently used
13 -- p_to_serial- This is not currently used
14 -- x_rslt_tab, --x_rslt_par_tab - both are identical and have the
15 -- genealogy records generated as output
16 -- Description: It gives all the Sub Componets, their Lot Numbers, Serial Numbers
17 -- and Hierarchy Levels Present Under the Main Assembly Item
18 -- (For Work Order the components used for making that Item).
19 -- This procedure is similar to the PIPELINED FUNCTION get_genealogy
20 -- which is replaced by the current procedure for performance reasons.
21 -- Instead of PIPE ROW returning the output, the output is captured in
22 -- x_rslt_tab
23 ---------------------------------------------------------------------------------------
24
25
26
27 PROCEDURE get_genealogy( p_gen_object_id IN NUMBER,
28 p_delivery_detail_id IN NUMBER,
29 p_delivery_id IN NUMBER,
30 p_organization_id IN NUMBER,
31 p_frm_serial IN VARCHAR2 DEFAULT NULL,
32 p_to_serial IN VARCHAR2 DEFAULT NULL,
33 x_rslt_tab OUT NOCOPY tab_genealogy)
34 IS
35
36 CURSOR c_nonmake_items
37 IS
38 SELECT msi.concatenated_segments item_number ,
39 msi.primary_uom_code ,
40 mln.lot_number ,
41 waid.from_serial_number from_serial_number ,
42 waid.to_serial_number to_serial_number ,
43 wdd.organization_id ,
44 NULL job_name ,
45 msi.inventory_item_id ,
46 mln.origination_date ,
47 mln.best_by_date ,
48 mln.retest_date ,
49 mln.expiration_date ,
50 waid.organization_code ,
51 1 rlevel ,
52 0 v_parent_rlevel ,
53 msi.lot_control_code ,
54 msi.serial_number_control_code ,
55 decode(( SELECT upper(mcr.cross_reference)
56 FROM mtl_cross_references_vl mcr
57 WHERE mcr.inventory_item_id = msi.inventory_item_id + 0
58 AND mcr.organization_id = msi.organization_id + 0
59 AND to_char(mcr.last_update_date, 'DD/MM/YYYY-HH24.MM.SS') = (SELECT MAX(to_char(mcr1.last_update_date, 'DD/MM/YYYY-HH24.MM.SS'))
60 FROM mtl_cross_references_vl mcr1
61 WHERE mcr1.inventory_item_id = msi.inventory_item_id + 0
62 AND mcr1.organization_id = msi.organization_id + 0
63 AND mcr1.cross_reference_type ='OPSM INTEGRATED')), 'NO', 0, 1) cross_reference,
64 (SELECT mcr.attribute1
65 FROM mtl_cross_references_vl mcr
66 WHERE mcr.inventory_item_id = msi.inventory_item_id + 0
67 AND mcr.organization_id = (SELECT master_organization_id
68 FROM mtl_parameters
69 WHERE organization_id = msi.organization_id + 0)
70 AND mcr.cross_reference_type ='OPSM INTEGRATED'
71 AND mcr.cross_reference = 'YES') serial_type,
72 NULL parent_lot_number ,
73 NULL parent_serial_number ,
74 0 parent_inventory_item_id
75 FROM mtl_system_items_vl msi ,
76 mtl_lot_numbers mln ,
77 wsh_opsm_asn_item_details_v waid ,
78 wsh_delivery_details wdd
79 WHERE wdd.delivery_detail_id = p_delivery_detail_id
80 AND wdd.organization_id = p_organization_id
81 AND waid.delivery_detail_id = wdd.delivery_detail_id + 0
82 AND waid.organization_id = wdd.organization_id + 0
83 AND msi.inventory_item_id = wdd.inventory_item_id + 0
84 AND msi.organization_id = wdd.organization_id + 0
85 AND mln.inventory_item_id = wdd.inventory_item_id + 0
86 AND mln.organization_id = wdd.organization_id + 0
87 AND mln.lot_number = waid.lot_number || ''
88 AND waid.from_serial_number = p_frm_serial
89 AND waid.to_serial_number = p_to_serial;
90
91 g_sub_sql VARCHAR2(32000) :=
92 'SELECT msi.concatenated_segments item_nbr ,
93 msi.primary_uom_code ,
94 mtln.lot_number ,
95 NULL serial_number ,
96 mmt.organization_id ,
97 we.wip_entity_name job_name ,
98 msi.inventory_item_id ,
99 mtln.origination_date ,
100 mtln.best_by_date ,
101 mtln.retest_date ,
102 mln.expiration_date ,
103 ood.organization_code ,
104 1 rlevel ,
105 0 v_parent_rlevel ,
106 msi.lot_control_code ,
107 msi.serial_number_control_code ,
108 decode(( SELECT upper(mcr.cross_reference)
109 FROM mtl_cross_references_vl mcr
110 WHERE mcr.inventory_item_id = msi.inventory_item_id+0
111 AND mcr.organization_id = msi.organization_id+0
112 AND to_char(mcr.last_update_date, ''DD/MM/YYYY-HH24.MM.SS'') = (SELECT MAX(to_char(mcr1.last_update_date, ''DD/MM/YYYY-HH24.MM.SS''))
113 FROM mtl_cross_references_vl mcr1
114 WHERE mcr1.inventory_item_id = msi.inventory_item_id+0
115 AND mcr1.organization_id = msi.organization_id+0
116 AND mcr1.cross_reference_type =''OPSM INTEGRATED'')),''NO'',0,1) cross_reference,
117 (SELECT mcr.attribute1
118 FROM mtl_cross_references_vl mcr
119 WHERE mcr.inventory_item_id = msi.inventory_item_id+0
120 AND mcr.organization_id = (SELECT master_organization_id
121 FROM mtl_parameters
122 WHERE organization_id=msi.organization_id+0)
123 AND mcr.cross_reference_type =''OPSM INTEGRATED''
124 AND mcr.cross_reference = ''YES'') serial_type,
125 NULL parent_lot_number ,
126 NULL parent_serial_number ,
127 0 parent_inventory_item_id
128 ,
129 to_number(null) DELIVERY_DETAIL_ID,
130 to_number(null) DELIVERY_ID,
131 to_number(null) GENEALOGY_OBJECT_ID
132 FROM mtl_transaction_lot_numbers mtln ,
133 wip_entities we ,
134 mtl_system_items_vl msi ,
135 org_organization_definitions ood ,
136 mtl_material_transactions mmt ,
137 mtl_object_genealogy mog ,
138 mtl_lot_numbers mln
139 WHERE mmt.transaction_source_id+0 = we.wip_entity_id
140 AND msi.inventory_item_id = mmt.inventory_item_id+0
141 AND msi.organization_id = mmt.organization_id+0
142 AND we.gen_object_id = mog.object_id
143 AND mog.parent_object_id = :1
144 AND mln.inventory_item_id = mtln.inventory_item_id+0
145 AND mln.organization_id = mtln.organization_id+0
146 AND mln.lot_number = mtln.lot_number||''''
147 AND ood.organization_id = mtln.organization_id
148 AND mmt.transaction_id = mog.origin_txn_id+0
149 AND mtln.transaction_id(+) = mmt.transaction_id+0
150 AND msi.serial_number_control_code NOT IN (6)';
151
152 g_gen_sql VARCHAR2(32000) :=
153 'SELECT mtln.lot_number ,
154 mut.serial_number ,
155 msi.concatenated_segments item_nbr ,
156 msi.primary_uom_code ,
157 msi.inventory_item_id ,
158 we.wip_entity_name job_name ,
159 mtln.origination_date ,
160 mtln.best_by_date ,
161 mtln.retest_date ,
162 mln.expiration_date ,
163 ood.organization_code ,
164 abs(mmt.transaction_quantity) transaction_quantity,
165 msi.lot_control_code ,
166 msi.serial_number_control_code ,
167 decode(( SELECT upper(mcr.cross_reference)
168 FROM mtl_cross_references_vl mcr
169 WHERE mcr.inventory_item_id = msi.inventory_item_id+0
170 AND mcr.organization_id = msi.organization_id+0
171 AND to_char(mcr.last_update_date, ''DD/MM/YYYY-HH24.MM.SS'') = (SELECT MAX(to_char(mcr1.last_update_date, ''DD/MM/YYYY-HH24.MM.SS''))
172 FROM mtl_cross_references_vl mcr1
173 WHERE mcr1.inventory_item_id = msi.inventory_item_id+0
174 AND mcr1.organization_id = msi.organization_id+0
175 AND mcr1.cross_reference_type =''OPSM INTEGRATED'')),''NO'',0,1) cross_reference,
176 (SELECT mcr.attribute1
177 FROM mtl_cross_references_vl mcr
178 WHERE mcr.inventory_item_id = msi.inventory_item_id+0
179 AND mcr.organization_id = (SELECT master_organization_id
180 FROM mtl_parameters
181 WHERE organization_id=msi.organization_id+0)
182 AND mcr.cross_reference_type =''OPSM INTEGRATED''
183 AND mcr.cross_reference = ''YES'') serial_type,
184 NULL parent_lot_number ,
185 NULL parent_serial_number ,
186 0 parent_inventory_item_id
187 FROM mtl_material_transactions mmt1 ,
188 mtl_transaction_lot_numbers mtln1 ,
189 mtl_unit_transactions mut1 ,
190 mtl_material_transactions mmt ,
191 mtl_transaction_lot_numbers mtln ,
192 mtl_unit_transactions mut ,
193 wip_entities we ,
194 mtl_system_items_vl msi ,
195 org_organization_definitions ood ,
196 mtl_lot_numbers mln
197 WHERE mtln1.transaction_id+0 = mmt1.transaction_id
198 AND mut1.transaction_id+0 = mtln1.serial_transaction_id
199 AND mmt1.transaction_type_id = 44
200 AND mmt1.transaction_quantity = 1
201 AND mut1.organization_id = :1
202 AND mut1.transaction_date = mut1.transaction_date
203 AND mut1.inventory_item_id = mut1.inventory_item_id
204 AND mut1.serial_number = :2
205 AND mmt1.inventory_item_id = :3
206 AND mmt.transaction_set_id = mmt1.transaction_set_id
207 AND mmt.transaction_type_id = 35
208 AND mtln.transaction_id = mmt.transaction_id+0
209 AND mut.transaction_id = mtln.serial_transaction_id+0
210 AND we.wip_entity_id = mmt.transaction_source_id+0
211 AND we.organization_id = :4
212 AND msi.inventory_item_id = mmt.inventory_item_id+0
213 AND msi.organization_id = mmt.organization_id+0
214 AND ood.organization_id = mmt.organization_id+0
215 AND mln.lot_number = mtln.lot_number||''''
216 AND mln.inventory_item_id = mmt.inventory_item_id+0
217 AND mln.organization_id = mtln.organization_id+0';
218
219 CURSOR c_main(r_serial_number IN VARCHAR2 ,
220 r_item_id IN NUMBER ,
221 r_org_id IN NUMBER)
222 IS
223 SELECT ROWNUM row_num ,
224 mog.object_id ,
225 mog.object_type ,
226 mtln.lot_number ,
227 mtln.transaction_source_id ,
228 msn.inventory_item_id ,
229 mut.serial_number ,
230 we.wip_entity_name job_name
231 FROM mtl_object_genealogy mog ,
232 mtl_transaction_lot_numbers mtln ,
233 mtl_unit_transactions mut ,
234 mtl_serial_numbers msn ,
235 mtl_material_transactions mmt ,
236 wip_entities we
237 WHERE msn.serial_number = r_serial_number
238 AND msn.inventory_item_id = r_item_id
239 AND mut.serial_number = r_serial_number
240 AND mut.inventory_item_id = r_item_id
241 AND mut.organization_id = r_org_id
242 AND mtln.organization_id = r_org_id
243 AND mut.transaction_id + 0 = mtln.serial_transaction_id
244 AND msn.serial_number = mut.serial_number
245 AND msn.inventory_item_id = mut.inventory_item_id
246 AND we.wip_entity_id = mtln.transaction_source_id + 0
247 AND we.organization_id = mtln.organization_id + 0
248 AND mog.object_id = msn.gen_object_id
249 AND mmt.transaction_id = mtln.transaction_id + 0
250 AND mmt.transaction_type_id = 44
251 AND mmt.transaction_action_id = 31
252 ORDER BY 7 DESC;
253
254 CURSOR c_top(rv_object_id IN NUMBER)
255 IS
256 SELECT (LEVEL - 1) rlevel ,
257 mog.* ,
258 '' serial_number ,
259 '' parent_serial_number ,
260 0 delivery_id ,
261 0 rTree
262 FROM mtl_object_genealogy mog
263 WHERE object_type IN (1, 2)
264 AND genealogy_origin = 1
265 AND (end_date_active IS NULL
266 OR TRUNC(end_date_active) >= TRUNC(SYSDATE))
267 START WITH parent_object_id = rv_object_id
268 CONNECT BY PRIOR object_id = parent_object_id;
269
270
271 CURSOR c_sub (r_gen_object_id IN NUMBER)
272 IS
273 SELECT we.wip_entity_name job_name ,
274 msi.concatenated_segments item_number ,
275 msi.primary_uom_code ,
276 msn.lot_number ,
277 msn.serial_number ,
278 mtln.origination_date ,
279 mtln.best_by_date ,
280 mtln.retest_date ,
281 mtln.expiration_date ,
282 ood.organization_code ,
283 msi.inventory_item_id ,
284 msi.organization_id ,
285 msi.lot_control_code ,
286 msi.serial_number_control_code ,
287 decode(( SELECT upper(mcr.cross_reference)
288 FROM mtl_cross_references_vl mcr
289 WHERE mcr.inventory_item_id = msi.inventory_item_id + 0
290 AND mcr.organization_id = msi.organization_id + 0
291 AND to_char(mcr.last_update_date, 'DD/MM/YYYY-HH24.MM.SS') = (SELECT MAX(to_char(mcr1.last_update_date, 'DD/MM/YYYY-HH24.MM.SS'))
292 FROM mtl_cross_references_vl mcr1
293 WHERE mcr1.inventory_item_id = msi.inventory_item_id + 0
294 AND mcr1.organization_id = msi.organization_id + 0
295 AND mcr1.cross_reference_type ='OPSM INTEGRATED')), 'NO', 0, 1) cross_reference,
296 (SELECT mcr.attribute1
297 FROM mtl_cross_references_vl mcr
298 WHERE mcr.inventory_item_id = msi.inventory_item_id + 0
299 AND mcr.organization_id = (SELECT master_organization_id
300 FROM mtl_parameters
301 WHERE organization_id = msi.organization_id + 0)
302 AND mcr.cross_reference_type ='OPSM INTEGRATED'
303 AND mcr.cross_reference = 'YES') serial_type
304 FROM wip_entities we ,
305 mtl_system_items_vl msi ,
306 mtl_serial_numbers msn ,
307 mtl_lot_numbers mtln ,
308 org_organization_definitions ood,
309 mtl_cross_references mcr
310 WHERE msn.gen_object_id = r_gen_object_id
311 AND msi.inventory_item_id = msn.inventory_item_id
312 AND msi.organization_id = we.organization_id + 0
313 AND we.wip_entity_id = msn.original_wip_entity_id
314 AND mtln.lot_number = msn.lot_number || ''
315 AND mtln.organization_id = we.organization_id + 0
316 AND ood.organization_id = we.organization_id + 0;
317
318 CURSOR c_sub_main(r_delivery_detail_id IN NUMBER,
319 r_organization_id IN NUMBER)
320 IS
321 SELECT waid.genealogy_object_id
322 FROM mtl_object_genealogy mog ,
323 mtl_material_transactions mmt ,
324 wip_entities we ,
325 mtl_system_items_vl msi ,
326 wsh_opsm_asn_item_details_v waid ,
327 mtl_transaction_lot_numbers mtln ,
328 mtl_unit_transactions mut ,
329 mtl_lot_numbers mln ,
330 org_organization_definitions ood
331 WHERE mog.parent_object_id = waid.genealogy_object_id + 0
332 AND mmt.transaction_id = mog.origin_txn_id + 0
333 AND mmt.transaction_type_id = 44
334 AND mmt.transaction_action_id = 31
335 AND we.wip_entity_id = mmt.transaction_source_id + 0
336 AND msi.inventory_item_id = mmt.inventory_item_id + 0
337 AND msi.organization_id = mmt.organization_id + 0
338 AND waid.delivery_detail_id = r_delivery_detail_id
339 AND mtln.transaction_id = mmt.transaction_id + 0
340 AND mut.transaction_id = mtln.serial_transaction_id + 0
341 AND mln.lot_number = mtln.lot_number || ''
342 AND mln.inventory_item_id = mtln.inventory_item_id + 0
343 AND mln.organization_id = mtln.organization_id + 0
344 AND ood.organization_id = msi.organization_id + 0
345 AND ood.organization_id = r_organization_id;
346
347 r_sub_main c_sub_main%ROWTYPE;
348
349 TYPE r_main
350 IS
351 TABLE OF c_main%ROWTYPE INDEX BY BINARY_INTEGER;
352
353 TYPE r_top
354 IS
355 TABLE OF c_top%ROWTYPE INDEX BY BINARY_INTEGER;
356
357 TYPE r_ser
358 IS
359 TABLE OF VARCHAR2(255) INDEX BY VARCHAR2(255);
360
361 TYPE r_sem
362 IS
363 TABLE OF NUMBER INDEX BY VARCHAR2(32000);
364
365 TYPE r_objects
366 IS
367 TABLE OF NUMBER INDEX BY VARCHAR2(32000);
368
369 genealogy_rec rec_genealogy;
370 genealogy_tab tab_genealogy;
371 sgenealogy_tab tab_sgenealogy;
372 s1genealogy_tab tab_sgenealogy;
373 s2genealogy_tab tab_sgenealogy;
374 s3genealogy_tab tab_sgenealogy;
375
376 dbg_genealogy_tab tab_genealogy;
377 dbg_sgenealogy_tab tab_sgenealogy;
378 dbg_s1genealogy_tab tab_sgenealogy;
379 dbg_s2genealogy_tab tab_sgenealogy;
380 dbg_s3genealogy_tab tab_sgenealogy;
381 dbg_v_tab_nodes tab_nodes;
382
383 v_tab_nodes tab_nodes;
384 occ recur;
385 v_jobname VARCHAR2(255) := NULL;
386 v_serial_number VARCHAR2(32000) := NULL;
387 v_inventory_item_id NUMBER;
388 v_delivery_id NUMBER;
389 v_delivery_id1 NUMBER;
390 vr_main c_main%ROWTYPE;
391 rr_main c_main%ROWTYPE;
392 v_level NUMBER := 1;
393 lv_level NUMBER := 1;
394 v_parent_rlevel NUMBER := 1;
395 v_wms_enabled NUMBER := 0;
396 v_make_items VARCHAR2(1) := 'Y';
397 v_top r_top;
398 v_ser t_ser;
399 v_sem r_sem;
400 v_objects r_objects;
401 v_ctr NUMBER;
402 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'get_genealogy';
403 l_msg_data VARCHAR2(2000) := NULL;
404 l_debug_on BOOLEAN;
405 x_return_status VARCHAR2(1);
406 Z NUMBER := 1;
407 Y NUMBER := 1;
408 l_stmt_no NUMBER;
409 l_rslt_tab_cnt NUMBER := 1;
410 BEGIN
411 x_rslt_tab := tab_genealogy();
412 --x_rslt_par_tab := tab_genealogy();
413 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
414 IF l_debug_on IS NULL
415 THEN
416 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
417 END IF;
418 IF l_debug_on THEN
419 WSH_DEBUG_SV.push(l_module_name);
420 WSH_DEBUG_SV.log(l_module_name,'GENEALOGY OBJECT ID', p_gen_object_id);
421 WSH_DEBUG_SV.log(l_module_name,'DELIVERY ID', p_delivery_id);
422 WSH_DEBUG_SV.log(l_module_name,'ORGANIZATION ID', p_organization_id);
423 WSH_DEBUG_SV.log(l_module_name,'FROM SERIAL NUMBER', p_frm_serial);
424 WSH_DEBUG_SV.log(l_module_name,'TO SERIAL NUMBER', p_to_serial);
425 END IF;
426
427 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
428 l_stmt_no := 0;
429 FND_LOG.String(
430 LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
431 MESSAGE => l_stmt_no ||': Entering get_genealogy',
432 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
433 FND_LOG.String(
434 LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
435 MESSAGE => ' p_gen_object_id= '|| p_gen_object_id,
436 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
437
438 FND_LOG.String(
439 LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
440 MESSAGE => ' p_delivery_detail_id= '|| p_delivery_detail_id,
441 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
442
443 FND_LOG.String(
444 LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
445 MESSAGE => ' p_delivery_id= '|| p_delivery_id,
446 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
447
448 FND_LOG.String(
449 LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
450 MESSAGE => ' p_organization_id= '|| p_organization_id,
451 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
452
453 FND_LOG.String(
454 LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
455 MESSAGE => ' p_frm_serial= '|| p_frm_serial,
456 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
457
458 FND_LOG.String(
459 LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
460 MESSAGE => ' p_to_serial= '|| p_to_serial,
461 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
462
463 END IF;
464
465 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
466 -- Check WMS enabled
467 --
468 ----dbms_output.put_line(1);
469 g_object_id := NULL;
470 BEGIN
471 SELECT DECODE(wms_enabled_flag, 'Y', 1, 0)
472 INTO v_wms_enabled
473 FROM mtl_parameters
474 WHERE organization_id = p_organization_id;
475 ----dbms_output.put_line(v_wms_enabled);
476 IF l_debug_on THEN
477 WSH_DEBUG_SV.log(l_module_name, 'WMS_ENABLED_FLAG', v_wms_enabled);
478 END IF;
479 IF v_wms_enabled = 1 THEN
480 g_object_id := p_gen_object_id;
481 ----dbms_output.put_line(p_gen_object_id);
482 GOTO WMS;
483 END IF;
484
485 EXCEPTION
486 WHEN OTHERS THEN
487 ----dbms_output.put_line(4);
488 NULL;
489 END; --
490 -- Handle non-make items
491 --
492 -- v_make_items := check_for_itemgenealogy(p_delivery_detail_id,p_organization_id);
493 v_make_items := 'Y';
494 IF l_debug_on THEN
495 WSH_DEBUG_SV.log(l_module_name,'GENEALOGY EXIST OR NOT', v_make_items);
496 END IF;
497 IF v_make_items = 'N' THEN
498 ----dbms_output.put_line(p_delivery_detail_id);
499 OPEN c_sub_main(p_delivery_detail_id, p_organization_id);
500 FETCH c_sub_main
501 INTO r_sub_main;
502 ----dbms_output.put_line(p_organization_id);
503 IF c_sub_main%NOTFOUND THEN
504 CLOSE c_sub_main;
505 GOTO MAKE_ITEMS;
506 ----dbms_output.put_line(7);
507 ELSE
508 v_wms_enabled := 1;
509 g_object_id := r_sub_main.genealogy_object_id;
510 ----dbms_output.put_line(g_object_id);
511 CLOSE c_sub_main;
512 ----dbms_output.put_line(9);
513 GOTO WMS;
514 END IF;
515 ELSE
516 --
517 -- Explode main assembly
518 --
519 v_make_items := 'Y';
520 l_stmt_no := 10;
521 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
522 FND_LOG.String(
523 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
524 MESSAGE => l_stmt_no ||': OPEN c_main_sql(p_delivery_detail_id, p_organization_id)= ',
525 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
526 FND_LOG.String(
527 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
528 MESSAGE => l_stmt_no ||': p_delivery_detail_id= '|| p_delivery_detail_id,
529 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
530
531 FND_LOG.String(
532 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
533 MESSAGE => l_stmt_no ||': p_organization_id= '|| p_organization_id,
534 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
535
536 END IF;
537 ----dbms_output.put_line(p_delivery_detail_id);
538 OPEN c_main_sql(p_delivery_detail_id, p_organization_id);
539 FETCH c_main_sql
540 BULK COLLECT INTO genealogy_tab;
541 ----dbms_output.put_line(p_organization_id);
542 CLOSE c_main_sql;
543
544 l_stmt_no := 20;
545 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
546 dbg_genealogy_tab := genealogy_tab;
547 IF dbg_genealogy_tab.COUNT > 0 THEN
548 FOR i3 IN dbg_genealogy_tab.FIRST .. dbg_genealogy_tab.LAST LOOP
549 FND_LOG.String(
550 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
551 MESSAGE => l_stmt_no ||': dbg_genealogy_tab(i).item_nbr= '|| dbg_genealogy_tab(i3).item_nbr,
552 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
553
554 FND_LOG.String(
555 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
556 MESSAGE => l_stmt_no ||': dbg_genealogy_tab(i).serial_number= '|| dbg_genealogy_tab(i3).serial_number,
557 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
558
559 FND_LOG.String(
560 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
561 MESSAGE => l_stmt_no ||': dbg_genealogy_tab(i).job_name= '|| dbg_genealogy_tab(i3).job_name,
562 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
563 END LOOP;
564 END IF;
565 END IF;
566
567 END IF;
568 --
569 -- IF top level assembly is not serialized or not lot controlled
570 --
571 IF genealogy_tab.COUNT = 0 THEN
572 l_stmt_no := 30;
573 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
574 FND_LOG.String(
575 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
576 MESSAGE => l_stmt_no ||': IF genealogy_tab.COUNT = 0 THEN',
577 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
578 FND_LOG.String(
579 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
580 MESSAGE => l_stmt_no ||': EXECUTE IMMEDIATE g_sub_sql BULK COLLECT INTO genealogy_tab USING p_gen_object_id;',
581 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
582
583 END IF;
584 ----dbms_output.put_line(12);
585 EXECUTE IMMEDIATE g_sub_sql
586 BULK COLLECT INTO genealogy_tab
587 USING p_gen_object_id;
588 END IF;
589
590 g_serial_number := NULL;
591
592 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
593 FND_LOG.String(
594 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
595 MESSAGE => l_stmt_no ||': FOR I IN 1..genealogy_tab.COUNT',
596 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
597
598 END IF;
599
600 FOR I IN 1..genealogy_tab.COUNT
601 LOOP
602 ----dbms_output.put_line(13);
603 ----dbms_output.put_line(genealogy_tab(i).item_nbr);
604 ----dbms_output.put_line(genealogy_tab(i).serial_number);
605
606 l_stmt_no := 40;
607 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
608 FND_LOG.String(
609 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
610 MESSAGE => l_stmt_no ||': genealogy_tab(i).item_nbr= '|| genealogy_tab(i).item_nbr,
611 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
612
613 FND_LOG.String(
614 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
615 MESSAGE => l_stmt_no ||': genealogy_tab(i).serial_number= '|| genealogy_tab(i).serial_number,
616 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
617 END IF;
618
619 v_level := 1;
620 genealogy_rec.item_nbr := genealogy_tab(i).item_nbr;
621 genealogy_rec.primary_uom_code := genealogy_tab(i).primary_uom_code;
622 genealogy_rec.lot_number := genealogy_tab(i).lot_number;
623 genealogy_rec.serial_number := genealogy_tab(i).serial_number;
624 genealogy_rec.organization_id := genealogy_tab(i).organization_id;
625 genealogy_rec.inventory_item_id := genealogy_tab(i).inventory_item_id;
626 genealogy_rec.job_name := genealogy_tab(i).job_name;
627 genealogy_rec.retest_date := genealogy_tab(i).retest_date;
628 genealogy_rec.expiration_date := genealogy_tab(i).expiration_date;
629 genealogy_rec.best_by_date := genealogy_tab(i).best_by_date;
630 genealogy_rec.origination_date := genealogy_tab(i).origination_date;
631 genealogy_rec.organization_code := genealogy_tab(i).organization_code;
632 genealogy_rec.lot_control_code := genealogy_tab(i).lot_control_code;
633 genealogy_rec.serial_number_control_code := genealogy_tab(i).serial_number_control_code;
634 genealogy_rec.cross_reference := genealogy_tab(i).cross_reference ;
635 genealogy_rec.serial_type := genealogy_tab(i).serial_type;
636 genealogy_rec.parent_lot_number := NULL;
637 genealogy_rec.parent_serial_number := NULL;
638 genealogy_rec.parent_inventory_item_id := 0;
639
640 l_stmt_no := 50;
641 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
642 FND_LOG.String(
643 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
644 MESSAGE => l_stmt_no ||': B4 IF NOT occ.EXISTS(genealogy_rec.item_nbr',
645 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
646 END IF;
647 IF NOT occ.EXISTS(genealogy_rec.item_nbr || '.' ||
648 genealogy_rec.lot_number || '.' ||
649 genealogy_rec.serial_number || '.' ||
650 TO_CHAR(genealogy_rec.organization_id) || '.' ||
651 TO_CHAR(genealogy_rec.inventory_item_id) || '.' ||
652 genealogy_rec.parent_lot_number || '.' ||
653 genealogy_rec.parent_serial_number || '.' ||
654 TO_CHAR(genealogy_rec.parent_inventory_item_id)) THEN
655 l_stmt_no := 60;
656 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
657 FND_LOG.String(
658 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
659 MESSAGE => l_stmt_no ||': After IF NOT occ.EXISTS(genealogy_rec.item_nbr',
660 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
661 END IF;
662 occ(genealogy_rec.item_nbr || '.' ||
663 genealogy_rec.lot_number || '.' ||
664 genealogy_rec.serial_number || '.' ||
665 TO_CHAR(genealogy_rec.organization_id) || '.' ||
666 TO_CHAR(genealogy_rec.inventory_item_id) || '.' ||
667 genealogy_rec.parent_lot_number || '.' ||
668 genealogy_rec.parent_serial_number || '.' ||
669 TO_CHAR(genealogy_rec.parent_inventory_item_id)) := i;
670 genealogy_rec.rlevel := v_level;
671 genealogy_rec.parent_rlevel := v_level - 1;
672 v_level := v_level + 1;
673 ----dbms_output.put_line(14);
674 IF v_wms_enabled <> 1 THEN
675 l_stmt_no := 70;
676 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
677 FND_LOG.String(
678 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
679 MESSAGE => l_stmt_no ||': IF v_wms_enabled <> 1 THEN',
680 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
681 END IF;
682
683 genealogy_rec.DELIVERY_DETAIL_ID := p_delivery_detail_id;
684 genealogy_rec.DELIVERY_ID := p_delivery_id;
685 genealogy_rec.GENEALOGY_OBJECT_ID := p_gen_object_id;
686 x_rslt_tab.EXTEND;
687 --x_rslt_par_tab.EXTEND;
688 x_rslt_tab(l_rslt_tab_cnt) := genealogy_rec;
689 --x_rslt_par_tab(l_rslt_tab_cnt) := genealogy_rec;
690 l_rslt_tab_cnt := l_rslt_tab_cnt+1;
691 -- PIPE ROW(genealogy_rec);
692 END IF;--v_wms_enabled <> 1
693 END IF;--NOT occ.EXISTS(genealogy_rec.item_nbr
694 l_stmt_no := 80;
695 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
696 FND_LOG.String(
697 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
698 MESSAGE => l_stmt_no ||': IF v_wms_enabled = 0 AND v_make_items = "Y"',
699 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
700 END IF;
701 IF v_wms_enabled = 0 AND v_make_items = 'Y' THEN
702 l_stmt_no := 90;
703 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
704
705 FND_LOG.String(
706 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
707 MESSAGE => l_stmt_no ||': genealogy_tab(i).item_nbr= '|| genealogy_tab(i).item_nbr,
708 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
709
710 FND_LOG.String(
711 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
712 MESSAGE => l_stmt_no ||': genealogy_tab(i).serial_number= '|| genealogy_tab(i).serial_number,
713 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
714
715 FND_LOG.String(
716 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
717 MESSAGE => l_stmt_no ||': genealogy_tab(i).inventory_item_id= '|| genealogy_tab(i).inventory_item_id,
718 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
719 FND_LOG.String(
720 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
721 MESSAGE => l_stmt_no ||': EXECUTE IMMEDIATE g_gen_sql BULK COLLECT INTO sgenealogy_tab',
722 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
723 END IF;
724
725 ----dbms_output.put_line(15);
726 EXECUTE IMMEDIATE g_gen_sql
727 BULK COLLECT INTO sgenealogy_tab
728 USING p_organization_id ,
729 genealogy_tab(i).serial_number ,
730 genealogy_tab(i).inventory_item_id,
731 p_organization_id;
732 ----dbms_output.put_line(genealogy_tab(i).inventory_item_id);
733 ----dbms_output.put_line(genealogy_tab(i).serial_number);
734 l_stmt_no := 100;
735 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
736 FND_LOG.String(
737 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
738 MESSAGE => l_stmt_no ||': IF sgenealogy_tab.COUNT <> 0 THEN ',
739 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
740
741 END IF;
742 IF sgenealogy_tab.COUNT <> 0 THEN
743 l_stmt_no := 110;
744 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
745 dbg_sgenealogy_tab := sgenealogy_tab;
746 IF dbg_sgenealogy_tab.COUNT > 0 THEN
747 FOR i4 IN dbg_sgenealogy_tab.FIRST .. dbg_sgenealogy_tab.LAST LOOP
748 FND_LOG.String(
749 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
750 MESSAGE => l_stmt_no ||': dbg_sgenealogy_tab(i).item_nbr= '|| dbg_sgenealogy_tab(i4).item_nbr,
751 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
752
753 FND_LOG.String(
754 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
755 MESSAGE => l_stmt_no ||': dbg_sgenealogy_tab(i).serial_number= '|| dbg_sgenealogy_tab(i4).serial_number,
756 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
757
758 FND_LOG.String(
759 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
760 MESSAGE => l_stmt_no ||': dbg_sgenealogy_tab(i).job_name= '|| dbg_sgenealogy_tab(i4).job_name,
761 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
762
763 END LOOP;
764 END IF;
765 END IF;
766 ----dbms_output.put_line(16);
767 l_stmt_no := 120;
768 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
769 FND_LOG.String(
770 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
771 MESSAGE => l_stmt_no ||': FOR j IN 1..sgenealogy_tab.COUNT',
772 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
773
774 END IF;
775 FOR j IN 1..sgenealogy_tab.COUNT
776 LOOP
777 ----dbms_output.put_line(17);
778 Y := 1;
779 ----dbms_output.put_line(sgenealogy_tab(j).item_nbr);
780 ----dbms_output.put_line(sgenealogy_tab(j).serial_number);
781 l_stmt_no := 130;
782 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
783 FND_LOG.String(
784 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
785 MESSAGE => l_stmt_no ||': sgenealogy_tab(i).item_nbr= '|| sgenealogy_tab(i).item_nbr,
786 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
787
788 FND_LOG.String(
789 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
790 MESSAGE => l_stmt_no ||': sgenealogy_tab(i).serial_number= '|| sgenealogy_tab(i).serial_number,
791 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
792 END IF;
793 v_level := 2;
794 genealogy_rec.item_nbr := sgenealogy_tab(j).item_nbr;
795 genealogy_rec.primary_uom_code := sgenealogy_tab(j).primary_uom_code;
796 genealogy_rec.lot_number := sgenealogy_tab(j).lot_number;
797 genealogy_rec.serial_number := sgenealogy_tab(j).serial_number;
798 genealogy_rec.organization_id := genealogy_tab(i).organization_id;
799 genealogy_rec.inventory_item_id := sgenealogy_tab(j).inventory_item_id;
800 genealogy_rec.job_name := sgenealogy_tab(j).job_name;
801 genealogy_rec.retest_date := sgenealogy_tab(j).retest_date;
802 genealogy_rec.expiration_date := sgenealogy_tab(j).expiration_date;
803 genealogy_rec.best_by_date := sgenealogy_tab(j).best_by_date;
804 genealogy_rec.origination_date := sgenealogy_tab(j).origination_date;
805 genealogy_rec.organization_code := sgenealogy_tab(j).organization_code;
806 genealogy_rec.lot_control_code := sgenealogy_tab(j).lot_control_code;
807 genealogy_rec.serial_number_control_code := sgenealogy_tab(j).serial_number_control_code;
808 genealogy_rec.cross_reference := sgenealogy_tab(j).cross_reference;
809 genealogy_rec.serial_type := sgenealogy_tab(j).serial_type;
810 genealogy_rec.parent_lot_number := genealogy_tab(i).lot_number;
811 genealogy_rec.parent_serial_number := genealogy_tab(i).serial_number;
812 genealogy_rec.parent_inventory_item_id := genealogy_tab(i).inventory_item_id;
813 l_stmt_no := 140;
814 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
815 FND_LOG.String(
816 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
817 MESSAGE => l_stmt_no ||': B4 IF NOT occ.EXISTS(genealogy_rec.item_nbr',
818 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
819 END IF;
820 IF NOT occ.EXISTS(genealogy_rec.item_nbr || '.' ||
821 genealogy_rec.lot_number || '.' ||
822 genealogy_rec.serial_number || '.' ||
823 TO_CHAR(genealogy_rec.organization_id) || '.' ||
824 TO_CHAR(genealogy_rec.inventory_item_id) || '.' ||
825 genealogy_rec.parent_lot_number || '.' ||
826 genealogy_rec.parent_serial_number || '.' ||
827 TO_CHAR(genealogy_rec.parent_inventory_item_id)) THEN
828 l_stmt_no := 150;
829 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
830 FND_LOG.String(
831 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
832 MESSAGE => l_stmt_no ||': After IF NOT occ.EXISTS(genealogy_rec.item_nbr',
833 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
834 END IF;
835 occ(genealogy_rec.item_nbr || '.' ||
836 genealogy_rec.lot_number || '.' ||
837 genealogy_rec.serial_number || '.' ||
838 TO_CHAR(genealogy_rec.organization_id) || '.' ||
839 TO_CHAR(genealogy_rec.inventory_item_id) || '.' ||
840 genealogy_rec.parent_lot_number || '.' ||
841 genealogy_rec.parent_serial_number || '.' ||
842 TO_CHAR(genealogy_rec.parent_inventory_item_id)) := j;
843 genealogy_rec.rlevel := v_level;
844 genealogy_rec.parent_rlevel := v_level - 1;
845 ----dbms_output.put_line(52);
846 l_stmt_no := 160;
847 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
848 FND_LOG.String(
849 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
850 MESSAGE => l_stmt_no ||': -- PIPE ROW(genealogy_rec);',
851 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
852
853 END IF;
854 genealogy_rec.DELIVERY_DETAIL_ID := p_delivery_detail_id;
855 genealogy_rec.DELIVERY_ID := p_delivery_id;
856 genealogy_rec.GENEALOGY_OBJECT_ID := p_gen_object_id;
857 x_rslt_tab.EXTEND;
858 --x_rslt_par_tab.EXTEND;
859 x_rslt_tab(l_rslt_tab_cnt) := genealogy_rec;
860 --x_rslt_par_tab(l_rslt_tab_cnt) := genealogy_rec;
861 l_rslt_tab_cnt := l_rslt_tab_cnt+1;
862 -- PIPE ROW(genealogy_rec);
863 v_level := v_level + 1;
864 END IF;
865 ----dbms_output.put_line(sgenealogy_tab(j).serial_number);
866 ----dbms_output.put_line(sgenealogy_tab(j).inventory_item_id);
867 ----dbms_output.put_line(53);
868 lv_level := v_level;
869 vr_main.serial_number := sgenealogy_tab(j).serial_number;
870 vr_main.inventory_item_id := sgenealogy_tab(j).inventory_item_id;
871 vr_main.lot_number := sgenealogy_tab(j).lot_number;
872 l_stmt_no := 170;
873 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
874 FND_LOG.String(
875 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
876 MESSAGE => l_stmt_no ||': vr_main.serial_number= '|| vr_main.serial_number,
877 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
878
879 FND_LOG.String(
880 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
881 MESSAGE => l_stmt_no ||': vr_main.inventory_item_id= '|| vr_main.inventory_item_id,
882 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
883
884 FND_LOG.String(
885 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
886 MESSAGE => l_stmt_no ||': vr_main.lot_number= '|| vr_main.lot_number,
887 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
888
889 END IF;
890 l_stmt_no := 180;
891 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
892 FND_LOG.String(
893 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
894 MESSAGE => l_stmt_no ||': LOOP',
895 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
896
897 END IF;
898
899 << REPEAT_LOOP>>
900 LOOP
901 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
902 l_stmt_no := 185;
903 FND_LOG.String(
904 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
905 MESSAGE => l_stmt_no ||': OPEN c_main',
906 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
907 FND_LOG.String(
908 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
909 MESSAGE => l_stmt_no ||': genealogy_rec.item_nbr= '|| genealogy_rec.item_nbr,
910 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
911
912 FND_LOG.String(
913 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
914 MESSAGE => l_stmt_no ||': genealogy_rec.inventory_item_id= '|| genealogy_rec.inventory_item_id,
915 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
916
917 FND_LOG.String(
918 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
919 MESSAGE => l_stmt_no ||': genealogy_rec.serial_number= '|| genealogy_rec.serial_number,
920 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
921
922 END IF;
923 OPEN c_main(genealogy_rec.serial_number ,
924 genealogy_rec.inventory_item_id,
925 p_organization_id);
926 ----dbms_output.put_line(18);
927 ----dbms_output.put_line(genealogy_rec.inventory_item_id);
928 ----dbms_output.put_line(genealogy_rec.serial_number);
929 FETCH c_main INTO vr_main;
930 ----dbms_output.put_line(19);
931 l_stmt_no := 190;
932 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
933 FND_LOG.String(
934 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
935 MESSAGE => l_stmt_no ||': vr_main.inventory_item_id= '|| vr_main.inventory_item_id,
936 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
937
938 FND_LOG.String(
939 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
940 MESSAGE => l_stmt_no ||': vr_main.serial_number= '|| vr_main.serial_number,
941 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
942
943 FND_LOG.String(
944 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
945 MESSAGE => l_stmt_no ||': b4 IF c_main%NOTFOUND THEN',
946 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
947
948 END IF;
949 IF c_main%NOTFOUND THEN
950 l_stmt_no := 200;
951 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
952 FND_LOG.String(
953 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
954 MESSAGE => l_stmt_no ||': after IF c_main%NOTFOUND THEN',
955 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
956
957 END IF;
958 ----dbms_output.put_line(21);
959 l_stmt_no := 210;
960 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
961 FND_LOG.String(
962 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
963 MESSAGE => l_stmt_no ||': b4 IF v_tab_nodes.COUNT > 0 THEN ',
964 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
965
966 END IF;
967 IF v_tab_nodes.COUNT > 0 THEN
968 l_stmt_no := 220;
969 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
970 FND_LOG.String(
971 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
972 MESSAGE => l_stmt_no ||': after IF v_tab_nodes.COUNT > 0 THEN ',
973 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
974 dbg_v_tab_nodes := v_tab_nodes;
975 IF dbg_v_tab_nodes.COUNT > 0 THEN
976 l_stmt_no := 220.1;
977 --FOR i7 IN dbg_v_tab_nodes.FIRST .. dbg_v_tab_nodes.LAST LOOP
978 /***************
979 FOR i7 IN 1 .. dbg_v_tab_nodes.COUNT LOOP
980 FND_LOG.String(
981 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
982 MESSAGE => l_stmt_no ||': dbg_v_tab_nodes(i).inventory_item_id= '|| dbg_v_tab_nodes(i7).inventory_item_id,
983 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
984
985 FND_LOG.String(
986 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
987 MESSAGE => l_stmt_no ||': dbg_v_tab_nodes(i).lot_number= '|| dbg_v_tab_nodes(i7).lot_number,
988 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
989
990 FND_LOG.String(
991 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
992 MESSAGE => l_stmt_no ||': dbg_v_tab_nodes(i).serial_number= '|| dbg_v_tab_nodes(i7).serial_number,
993 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
994
995 END LOOP;
996 ********************/
997 END IF;
998 FND_LOG.String(
999 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1000 MESSAGE => l_stmt_no ||': s2genealogy_tab(v_tab_nodes.LAST).item_nbr= '|| s2genealogy_tab(v_tab_nodes.LAST).item_nbr,
1001 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1002
1003 FND_LOG.String(
1004 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1005 MESSAGE => l_stmt_no ||': s2genealogy_tab(v_tab_nodes.LAST).serial_number= '|| s2genealogy_tab(v_tab_nodes.LAST).serial_number,
1006 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1007
1008 END IF;
1009 ----dbms_output.put_line(22);
1010 ----dbms_output.put_line(s2genealogy_tab(v_tab_nodes.LAST).item_nbr);
1011 ----dbms_output.put_line(s2genealogy_tab(v_tab_nodes.LAST).serial_number);
1012 vr_main.serial_number := v_tab_nodes(v_tab_nodes.LAST).serial_number;
1013 vr_main.inventory_item_id := v_tab_nodes(v_tab_nodes.LAST).inventory_item_id;
1014 vr_main.lot_number := v_tab_nodes(v_tab_nodes.LAST).lot_number;
1015 v_level := v_tab_nodes(v_tab_nodes.LAST).LEVEL;
1016 genealogy_rec.item_nbr := s2genealogy_tab(v_tab_nodes.LAST).item_nbr;
1017 genealogy_rec.primary_uom_code := s2genealogy_tab(v_tab_nodes.LAST).primary_uom_code;
1018 genealogy_rec.lot_number := s2genealogy_tab(v_tab_nodes.LAST).lot_number;
1019 genealogy_rec.serial_number := s2genealogy_tab(v_tab_nodes.LAST).serial_number;
1020 genealogy_rec.organization_id := genealogy_tab(i).organization_id;
1021 genealogy_rec.inventory_item_id := s2genealogy_tab(v_tab_nodes.LAST).inventory_item_id;
1022 genealogy_rec.job_name := s2genealogy_tab(v_tab_nodes.LAST).job_name;
1023 genealogy_rec.retest_date := s2genealogy_tab(v_tab_nodes.LAST).retest_date;
1024 genealogy_rec.expiration_date := s2genealogy_tab(v_tab_nodes.LAST).expiration_date;
1025 genealogy_rec.best_by_date := s2genealogy_tab(v_tab_nodes.LAST).best_by_date;
1026 genealogy_rec.origination_date := s2genealogy_tab(v_tab_nodes.LAST).origination_date;
1027 genealogy_rec.organization_code := s2genealogy_tab(v_tab_nodes.LAST).organization_code;
1028 genealogy_rec.lot_control_code := s2genealogy_tab(v_tab_nodes.LAST).lot_control_code;
1029 genealogy_rec.serial_number_control_code := s2genealogy_tab(v_tab_nodes.LAST).serial_number_control_code ;
1030 genealogy_rec.cross_reference := s2genealogy_tab(v_tab_nodes.LAST).cross_reference;
1031 genealogy_rec.serial_type := s2genealogy_tab(v_tab_nodes.LAST).serial_type;
1032 genealogy_rec.parent_lot_number := s2genealogy_tab(v_tab_nodes.LAST).parent_lot_number;
1033 genealogy_rec.parent_serial_number := s2genealogy_tab(v_tab_nodes.LAST).parent_serial_number;
1034 genealogy_rec.parent_inventory_item_id := s2genealogy_tab(v_tab_nodes.LAST).parent_inventory_item_id;
1035 v_tab_nodes.DELETE(v_tab_nodes.LAST);
1036 IF NOT occ.EXISTS(genealogy_rec.item_nbr || '.' ||
1037 genealogy_rec.lot_number || '.' ||
1038 genealogy_rec.serial_number || '.' ||
1039 TO_CHAR(genealogy_rec.organization_id) || '.' ||
1040 TO_CHAR(genealogy_rec.inventory_item_id) || '.' ||
1041 genealogy_rec.parent_lot_number || '.' ||
1042 genealogy_rec.parent_serial_number || '.' ||
1043 TO_CHAR(genealogy_rec.parent_inventory_item_id)) THEN
1044 l_stmt_no := 230;
1045 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1046 FND_LOG.String(
1047 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1048 MESSAGE => l_stmt_no ||': IF NOT occ.EXISTS(genealogy_rec.item_nbr',
1049 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1050
1051 END IF;
1052
1053 occ(genealogy_rec.item_nbr || '.' ||
1054 genealogy_rec.lot_number || '.' ||
1055 genealogy_rec.serial_number || '.' ||
1056 TO_CHAR(genealogy_rec.organization_id) || '.' ||
1057 TO_CHAR(genealogy_rec.inventory_item_id) || '.' ||
1058 genealogy_rec.parent_lot_number || '.' ||
1059 genealogy_rec.parent_serial_number || '.' ||
1060 TO_CHAR(genealogy_rec.parent_inventory_item_id)) := j;
1061 genealogy_rec.rlevel := v_level;
1062 genealogy_rec.parent_rlevel := v_level - 1;
1063 l_stmt_no := 240;
1064 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1065 FND_LOG.String(
1066 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1067 MESSAGE => l_stmt_no ||': PIPE ROW(genealogy_rec)',
1068 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1069
1070 END IF;
1071 genealogy_rec.DELIVERY_DETAIL_ID := p_delivery_detail_id;
1072 genealogy_rec.DELIVERY_ID := p_delivery_id;
1073 genealogy_rec.GENEALOGY_OBJECT_ID := p_gen_object_id;
1074 x_rslt_tab.EXTEND;
1075 --x_rslt_par_tab.EXTEND;
1076 x_rslt_tab(l_rslt_tab_cnt) := genealogy_rec;
1077 --x_rslt_par_tab(l_rslt_tab_cnt) := genealogy_rec;
1078 l_rslt_tab_cnt := l_rslt_tab_cnt+1;
1079 -- PIPE ROW(genealogy_rec);
1080 END IF;
1081 ----dbms_output.put_line(22);
1082 v_level := v_level + 1;
1083 ELSE
1084 ----dbms_output.put_line(23);
1085 l_stmt_no := 250;
1086 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1087 FND_LOG.String(
1088 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1089 MESSAGE => l_stmt_no ||': CLOSE c_main; EXIT;',
1090 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1091
1092 END IF;
1093 CLOSE c_main;
1094 EXIT;
1095 END IF;
1096 END IF;
1097 l_stmt_no := 260;
1098 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1099 FND_LOG.String(
1100 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1101 MESSAGE => l_stmt_no ||': CLOSE c_main;',
1102 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1103 FND_LOG.String(
1104 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1105 MESSAGE => l_stmt_no ||': vr_main.serial_number = '|| vr_main.serial_number ,
1106 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1107
1108 FND_LOG.String(
1109 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1110 MESSAGE => l_stmt_no ||': vr_main.inventory_item_id= '|| vr_main.inventory_item_id,
1111 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1112
1113 END IF;
1114 CLOSE c_main;
1115 ----dbms_output.put_line(24);
1116 EXECUTE IMMEDIATE g_gen_sql
1117 BULK COLLECT INTO s1genealogy_tab
1118 USING p_organization_id ,
1119 vr_main.serial_number ,
1120 vr_main.inventory_item_id ,
1121 p_organization_id;
1122 ----dbms_output.put_line(25);
1123 v_ctr := 1;
1124 ----dbms_output.put_line(26);
1125 l_stmt_no := 270;
1126 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1127 FND_LOG.String(
1128 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1129 MESSAGE => l_stmt_no ||':EXECUTE IMMEDIATE g_gen_sql BULK COLLECT INTO s1genealogy_tab USING p_organization_id ,vr_main.serial_number , vr_main.inventory_item_id ,p_organization_id;',
1130 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy.');
1131
1132 FND_LOG.String(
1133 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1134 MESSAGE => l_stmt_no ||': v_ctr= '|| v_ctr,
1135 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1136 dbg_s1genealogy_tab := s1genealogy_tab;
1137 IF dbg_s1genealogy_tab.COUNT > 0 THEN
1138 FOR i1 IN dbg_s1genealogy_tab.FIRST .. dbg_s1genealogy_tab.LAST LOOP
1139 FND_LOG.String(
1140 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1141 MESSAGE => l_stmt_no ||': dbg_s1genealogy_tab(i1).item_nbr= '|| dbg_s1genealogy_tab(i1).item_nbr,
1142 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1143
1144 FND_LOG.String(
1145 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1146 MESSAGE => l_stmt_no ||': dbg_s1genealogy_tab(i1).serial_number= '|| dbg_s1genealogy_tab(i1).serial_number,
1147 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1148 FND_LOG.String(
1149 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1150 MESSAGE => l_stmt_no ||': dbg_s1genealogy_tab(i1).serial_number= '|| dbg_s1genealogy_tab(i1).job_name,
1151 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1152
1153 END LOOP;
1154 END IF;
1155 END IF;
1156
1157 IF s1genealogy_tab.COUNT <> 0 THEN
1158 l_stmt_no := 280;
1159 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1160 FND_LOG.String(
1161 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1162 MESSAGE => l_stmt_no ||': IF s1genealogy_tab.COUNT <> 0',
1163 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1164
1165 END IF;
1166 ----dbms_output.put_line(27);
1167 ----dbms_output.put_line(vr_main.serial_number);
1168 ----dbms_output.put_line(vr_main.inventory_item_id);
1169 IF vr_main.inventory_item_id <> sgenealogy_tab(j).inventory_item_id THEN
1170 Z := 1;
1171 ELSE
1172 Z := Y;
1173 END IF;
1174 l_stmt_no := 290;
1175 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1176 FND_LOG.String(
1177 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1178 MESSAGE => l_stmt_no ||': IF vr_main.inventory_item_id <> sgenealogy_tab(j).inventory_item_id THEN',
1179 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy.');
1180 FND_LOG.String(
1181 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1182 MESSAGE => l_stmt_no ||': vr_main.inventory_item_id= '|| vr_main.inventory_item_id,
1183 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1184
1185 FND_LOG.String(
1186 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1187 MESSAGE => l_stmt_no ||': sgenealogy_tab(j).inventory_item_id= '|| sgenealogy_tab(j).inventory_item_id,
1188 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1189
1190 FND_LOG.String(
1191 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1192 MESSAGE => l_stmt_no ||': Y= '|| Y,
1193 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1194
1195 FND_LOG.String(
1196 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1197 MESSAGE => l_stmt_no ||': Z= '|| Z,
1198 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1199
1200 END IF;
1201 IF s1genealogy_tab.EXISTS(Z) THEN
1202 ----dbms_output.put_line(28);
1203 ----dbms_output.put_line(Z);
1204 l_stmt_no := 300;
1205 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1206 FND_LOG.String(
1207 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1208 MESSAGE => l_stmt_no ||': IF s1genealogy_tab.EXISTS(Z)',
1209 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1210 FND_LOG.String(
1211 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1212 MESSAGE => l_stmt_no ||': FOR l IN Z..s1genealogy_tab.COUNT',
1213 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1214 FND_LOG.String(
1215 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1216 MESSAGE => 's1genealogy_tab.COUNT= '|| s1genealogy_tab.COUNT,
1217 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1218
1219 END IF;
1220 FOR l IN Z..s1genealogy_tab.COUNT
1221 LOOP
1222 l_stmt_no := 310;
1223 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1224 FND_LOG.String(
1225 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1226 MESSAGE => l_stmt_no ||': s1genealogy_tab(l).item_nbr= '|| s1genealogy_tab(l).item_nbr,
1227 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1228
1229 FND_LOG.String(
1230 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1231 MESSAGE => l_stmt_no ||': s1genealogy_tab(l).serial_number= '|| s1genealogy_tab(l).serial_number,
1232 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1233 FND_LOG.String(
1234 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1235 MESSAGE => l_stmt_no ||': s1genealogy_tab(l).transaction_quantity= '|| s1genealogy_tab(l).transaction_quantity,
1236 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1237
1238 END IF;
1239 ----dbms_output.put_line(29);
1240 ----dbms_output.put_line(s1genealogy_tab(l).item_nbr);
1241 ----dbms_output.put_line(s1genealogy_tab(l).serial_number);
1242 genealogy_rec.item_nbr := s1genealogy_tab(l).item_nbr;
1243 genealogy_rec.primary_uom_code := s1genealogy_tab(l).primary_uom_code;
1244 genealogy_rec.lot_number := s1genealogy_tab(l).lot_number;
1245 genealogy_rec.serial_number := s1genealogy_tab(l).serial_number;
1246 genealogy_rec.organization_id := genealogy_tab(i).organization_id;
1247 genealogy_rec.inventory_item_id := s1genealogy_tab(l).inventory_item_id;
1248 genealogy_rec.job_name := s1genealogy_tab(l).job_name;
1249 genealogy_rec.retest_date := s1genealogy_tab(l).retest_date;
1250 genealogy_rec.expiration_date := s1genealogy_tab(l).expiration_date;
1251 genealogy_rec.best_by_date := s1genealogy_tab(l).best_by_date;
1252 genealogy_rec.origination_date := s1genealogy_tab(l).origination_date;
1253 genealogy_rec.organization_code := s1genealogy_tab(l).organization_code;
1254 genealogy_rec.lot_control_code := s1genealogy_tab(l).lot_control_code;
1255 genealogy_rec.serial_number_control_code := s1genealogy_tab(l).serial_number_control_code;
1256 genealogy_rec.cross_reference := s1genealogy_tab(l).cross_reference;
1257 genealogy_rec.serial_type := s1genealogy_tab(l).serial_type;
1258 genealogy_rec.parent_lot_number := vr_main.lot_number;
1259 genealogy_rec.parent_serial_number := vr_main.serial_number;
1260 genealogy_rec.parent_inventory_item_id := vr_main.inventory_item_id;
1261 ----dbms_output.put_line(s1genealogy_tab(l).transaction_quantity);
1262 IF NOT occ.EXISTS(genealogy_rec.item_nbr || '.' ||
1263 genealogy_rec.lot_number || '.' ||
1264 genealogy_rec.serial_number || '.' ||
1265 TO_CHAR(genealogy_rec.organization_id) || '.' ||
1266 TO_CHAR(genealogy_rec.inventory_item_id) || '.' ||
1267 genealogy_rec.parent_lot_number || '.' ||
1268 genealogy_rec.parent_serial_number || '.' ||
1269 TO_CHAR(genealogy_rec.parent_inventory_item_id)) THEN
1270 l_stmt_no := 320;
1271 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1272 FND_LOG.String(
1273 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1274 MESSAGE => l_stmt_no ||': IF NOT occ.EXISTS(genealogy_rec.item_nbr',
1275 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1276
1277 END IF;
1278
1279 occ(genealogy_rec.item_nbr || '.' ||
1280 genealogy_rec.lot_number || '.' ||
1281 genealogy_rec.serial_number || '.' ||
1282 TO_CHAR(genealogy_rec.organization_id) || '.' ||
1283 TO_CHAR(genealogy_rec.inventory_item_id) || '.' ||
1284 genealogy_rec.parent_lot_number || '.' ||
1285 genealogy_rec.parent_serial_number || '.' ||
1286 TO_CHAR(genealogy_rec.parent_inventory_item_id)) := j;
1287 genealogy_rec.rlevel := v_level;
1288 genealogy_rec.parent_rlevel := v_level - 1;
1289 ----dbms_output.put_line(30);
1290 l_stmt_no := 330;
1291 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1292 FND_LOG.String(
1293 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1294 MESSAGE => l_stmt_no ||': PIPE ROW(genealogy_rec)',
1295 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1296
1297 END IF;
1298 genealogy_rec.DELIVERY_DETAIL_ID := p_delivery_detail_id;
1299 genealogy_rec.DELIVERY_ID := p_delivery_id;
1300 genealogy_rec.GENEALOGY_OBJECT_ID := p_gen_object_id;
1301 x_rslt_tab.EXTEND;
1302 --x_rslt_par_tab.EXTEND;
1303 x_rslt_tab(l_rslt_tab_cnt) := genealogy_rec;
1304 --x_rslt_par_tab(l_rslt_tab_cnt) := genealogy_rec;
1305 l_rslt_tab_cnt := l_rslt_tab_cnt+1;
1306 -- PIPE ROW(genealogy_rec);
1307 ----dbms_output.put_line(50);
1308 ----dbms_output.put_line(sgenealogy_tab(j).transaction_quantity);
1309 ----dbms_output.put_line(s1genealogy_tab(l).transaction_quantity);
1310 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1311 l_stmt_no := 340;
1312 FND_LOG.String(
1313 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1314 MESSAGE => l_stmt_no ||': sgenealogy_tab(j).transaction_quantity= '|| sgenealogy_tab(j).transaction_quantity,
1315 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1316
1317 FND_LOG.String(
1318 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1319 MESSAGE => l_stmt_no ||': v_ctr= '|| v_ctr,
1320 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1321
1322 END IF;
1323 IF sgenealogy_tab(j).transaction_quantity >= v_ctr THEN
1324 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1325 l_stmt_no := 341;
1326 FND_LOG.String(
1327 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1328 MESSAGE => l_stmt_no ||': IF sgenealogy_tab(j).transaction_quantity >= v_ctr THEN',
1329 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy.');
1330
1331 END IF;
1332
1333 genealogy_rec.serial_number := sgenealogy_tab(j).serial_number;
1334 genealogy_rec.inventory_item_id := sgenealogy_tab(j).inventory_item_id;
1335 v_level := lv_level;
1336 ----dbms_output.put_line(31);
1337 ----dbms_output.put_line(genealogy_rec.serial_number);
1338 ----dbms_output.put_line(genealogy_rec.inventory_item_id);
1339 EXIT;
1340 ELSIF v_ctr < sgenealogy_tab(j).transaction_quantity THEN
1341 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1342 l_stmt_no := 342;
1343 FND_LOG.String(
1344 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1345 MESSAGE => l_stmt_no ||': ELSIF v_ctr < sgenealogy_tab(j).transaction_quantity THEN',
1346 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy.');
1347
1348 END IF;
1349
1350 ----dbms_output.put_line(32);
1351 IF v_ctr = 1 THEN
1352 ----dbms_output.put_line(33);
1353 lv_level := v_level;
1354 END IF;
1355 ----dbms_output.put_line(34);
1356 v_ctr := v_ctr + 1;
1357 v_level := v_level + 1;
1358 END IF;
1359 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1360 l_stmt_no := 350;
1361 FND_LOG.String(
1362 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1363 MESSAGE => l_stmt_no ||': v_ctr= '|| v_ctr,
1364 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1365
1366 FND_LOG.String(
1367 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1368 MESSAGE => l_stmt_no ||': v_level= '|| v_level,
1369 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1370
1371 END IF;
1372
1373 ELSIF s1genealogy_tab(l).transaction_quantity = v_ctr THEN
1374 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1375 l_stmt_no := 360;
1376 FND_LOG.String(
1377 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1378 MESSAGE => l_stmt_no ||': ELSIF s1genealogy_tab(l).transaction_quantity = v_ctr THEN',
1379 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy.');
1380
1381 FND_LOG.String(
1382 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1383 MESSAGE => l_stmt_no ||': s1genealogy_tab(l).transaction_quantity= '|| s1genealogy_tab(l).transaction_quantity,
1384 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1385
1386 FND_LOG.String(
1387 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1388 MESSAGE => l_stmt_no ||': v_ctr= '|| v_ctr,
1389 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1390
1391 END IF;
1392 ----dbms_output.put_line(35);
1393 IF s1genealogy_tab.EXISTS(l + 1) THEN
1394 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1395 l_stmt_no := 370;
1396 FND_LOG.String(
1397 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1398 MESSAGE => l_stmt_no ||': IF s1genealogy_tab.EXISTS(l+1)',
1399 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1400 FND_LOG.String(
1401 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1402 MESSAGE => l_stmt_no ||': FOR m IN l+1..s1genealogy_tab.COUNT',
1403 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1404
1405 END IF;
1406
1407 ----dbms_output.put_line(36);
1408 FOR m IN l + 1..s1genealogy_tab.COUNT
1409 LOOP
1410 ----dbms_output.put_line(37);
1411 ----dbms_output.put_line(s1genealogy_tab(m).item_nbr);
1412 ----dbms_output.put_line(s1genealogy_tab(m).serial_number);
1413 ----dbms_output.put_line(vr_main.serial_number);
1414 ----dbms_output.put_line(vr_main.inventory_item_id);
1415
1416 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1417 l_stmt_no := 380;
1418 FND_LOG.String(
1419 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1420 MESSAGE => l_stmt_no ||': s1genealogy_tab(m).item_nbr= '|| s1genealogy_tab(m).item_nbr,
1421 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1422
1423 FND_LOG.String(
1424 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1425 MESSAGE => l_stmt_no ||': s1genealogy_tab(m).serial_number= '|| s1genealogy_tab(m).serial_number,
1426 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1427
1428 FND_LOG.String(
1429 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1430 MESSAGE => l_stmt_no ||': vr_main.serial_number= '|| vr_main.serial_number,
1431 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1432
1433 FND_LOG.String(
1434 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1435 MESSAGE => l_stmt_no ||': vr_main.inventory_item_id= '|| vr_main.inventory_item_id,
1436 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1437
1438 END IF;
1439
1440 v_tab_nodes(m).serial_number := s1genealogy_tab(m).serial_number;
1441 v_tab_nodes(m).inventory_item_id := s1genealogy_tab(m).inventory_item_id;
1442 v_tab_nodes(m).lot_number := s1genealogy_tab(m).lot_number;
1443 v_tab_nodes(m).LEVEL := v_level;
1444 s2genealogy_tab(m).item_nbr := s1genealogy_tab(m).item_nbr;
1445 s2genealogy_tab(m).primary_uom_code := s1genealogy_tab(m).primary_uom_code;
1446 s2genealogy_tab(m).lot_number := s1genealogy_tab(m).lot_number;
1447 s2genealogy_tab(m).serial_number := s1genealogy_tab(m).serial_number;
1448 s2genealogy_tab(m).inventory_item_id := s1genealogy_tab(m).inventory_item_id;
1449 s2genealogy_tab(m).job_name := s1genealogy_tab(m).job_name;
1450 s2genealogy_tab(m).retest_date := s1genealogy_tab(m).retest_date;
1451 s2genealogy_tab(m).expiration_date := s1genealogy_tab(m).expiration_date;
1452 s2genealogy_tab(m).best_by_date := s1genealogy_tab(m).best_by_date;
1453 s2genealogy_tab(m).origination_date := s1genealogy_tab(m).origination_date;
1454 s2genealogy_tab(m).organization_code := s1genealogy_tab(m).organization_code;
1455 s2genealogy_tab(m).lot_control_code := s1genealogy_tab(m).lot_control_code;
1456 s2genealogy_tab(m).serial_number_control_code := s1genealogy_tab(m).serial_number_control_code;
1457 s2genealogy_tab(m).cross_reference := s1genealogy_tab(m).cross_reference;
1458 s2genealogy_tab(m).serial_type := s1genealogy_tab(m).serial_type;
1459 s2genealogy_tab(m).parent_lot_number := vr_main.lot_number;
1460 s2genealogy_tab(m).parent_serial_number := vr_main.serial_number;
1461 s2genealogy_tab(m).parent_inventory_item_id := vr_main.inventory_item_id;
1462 ------ADDED NEWLY
1463
1464 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1465 l_stmt_no := 390;
1466 FND_LOG.String(
1467 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1468 MESSAGE => l_stmt_no ||': OPEN c_main(v_tab_nodes(m).serial_number',
1469 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1470 FND_LOG.String(
1471 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1472 MESSAGE => l_stmt_no ||': v_tab_nodes(m).serial_number= '|| v_tab_nodes(m).serial_number,
1473 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1474
1475 FND_LOG.String(
1476 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1477 MESSAGE => l_stmt_no ||': v_tab_nodes(m).inventory_item_id= '|| v_tab_nodes(m).inventory_item_id,
1478 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1479
1480 END IF;
1481
1482 OPEN c_main(v_tab_nodes(m).serial_number ,
1483 v_tab_nodes(m).inventory_item_id,
1484 p_organization_id);
1485 FETCH c_main INTO vr_main;
1486 ----dbms_output.put_line(60);
1487 IF c_main%NOTFOUND THEN
1488 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1489 l_stmt_no := 400;
1490 FND_LOG.String(
1491 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1492 MESSAGE => l_stmt_no ||': IF c_main%NOTFOUND',
1493 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1494
1495 END IF;
1496
1497 ----dbms_output.put_line(61);
1498 IF v_tab_nodes.COUNT > 0 THEN
1499 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1500 l_stmt_no := 410;
1501 FND_LOG.String(
1502 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1503 MESSAGE => l_stmt_no ||': v_tab_nodes.COUNT > 0',
1504 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1505 FND_LOG.String(
1506 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1507 MESSAGE => l_stmt_no ||': s2genealogy_tab(v_tab_nodes.LAST).item_nbr= '|| s2genealogy_tab(v_tab_nodes.LAST).item_nbr,
1508 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1509
1510 FND_LOG.String(
1511 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1512 MESSAGE => l_stmt_no ||': s2genealogy_tab(v_tab_nodes.LAST).serial_number= '|| s2genealogy_tab(v_tab_nodes.LAST).serial_number,
1513 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1514
1515
1516 END IF;
1517 ----dbms_output.put_line(62);
1518 ----dbms_output.put_line(s2genealogy_tab(v_tab_nodes.LAST).item_nbr);
1519 ----dbms_output.put_line(s2genealogy_tab(v_tab_nodes.LAST).serial_number);
1520 v_level := v_tab_nodes(v_tab_nodes.LAST).LEVEL;
1521 genealogy_rec.item_nbr := s2genealogy_tab(v_tab_nodes.LAST).item_nbr;
1522 genealogy_rec.primary_uom_code := s2genealogy_tab(v_tab_nodes.LAST).primary_uom_code;
1523 genealogy_rec.lot_number := s2genealogy_tab(v_tab_nodes.LAST).lot_number;
1524 genealogy_rec.serial_number := s2genealogy_tab(v_tab_nodes.LAST).serial_number;
1525 genealogy_rec.organization_id := genealogy_tab(i).organization_id;
1526 genealogy_rec.inventory_item_id := s2genealogy_tab(v_tab_nodes.LAST).inventory_item_id;
1527 genealogy_rec.job_name := s2genealogy_tab(v_tab_nodes.LAST).job_name;
1528 genealogy_rec.retest_date := s2genealogy_tab(v_tab_nodes.LAST).retest_date;
1529 genealogy_rec.expiration_date := s2genealogy_tab(v_tab_nodes.LAST).expiration_date;
1530 genealogy_rec.best_by_date := s2genealogy_tab(v_tab_nodes.LAST).best_by_date;
1531 genealogy_rec.origination_date := s2genealogy_tab(v_tab_nodes.LAST).origination_date;
1532 genealogy_rec.organization_code := s2genealogy_tab(v_tab_nodes.LAST).organization_code;
1533 genealogy_rec.lot_control_code := s2genealogy_tab(v_tab_nodes.LAST).lot_control_code;
1534 genealogy_rec.serial_number_control_code := s2genealogy_tab(v_tab_nodes.LAST).serial_number_control_code ;
1535 genealogy_rec.cross_reference := s2genealogy_tab(v_tab_nodes.LAST).cross_reference;
1536 genealogy_rec.serial_type := s2genealogy_tab(v_tab_nodes.LAST).serial_type;
1537 genealogy_rec.parent_lot_number := s2genealogy_tab(v_tab_nodes.LAST).parent_lot_number;
1538 genealogy_rec.parent_serial_number := s2genealogy_tab(v_tab_nodes.LAST).parent_serial_number;
1539 genealogy_rec.parent_inventory_item_id := s2genealogy_tab(v_tab_nodes.LAST).parent_inventory_item_id;
1540 v_tab_nodes.DELETE(v_tab_nodes.LAST);
1541 IF NOT occ.EXISTS(genealogy_rec.item_nbr || '.' ||
1542 genealogy_rec.lot_number || '.' ||
1543 genealogy_rec.serial_number || '.' ||
1544 TO_CHAR(genealogy_rec.organization_id) || '.' ||
1545 TO_CHAR(genealogy_rec.inventory_item_id) || '.' ||
1546 genealogy_rec.parent_lot_number || '.' ||
1547 genealogy_rec.parent_serial_number || '.' ||
1548 TO_CHAR(genealogy_rec.parent_inventory_item_id)) THEN
1549 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1550 l_stmt_no := 420;
1551 FND_LOG.String(
1552 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1553 MESSAGE => l_stmt_no ||': IF NOT occ.EXISTS(genealogy_rec.item_nbr',
1554 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1555 FND_LOG.String(
1556 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1557 MESSAGE => l_stmt_no ||': PIPE ROW(genealogy_rec)',
1558 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1559 END IF;
1560 occ(genealogy_rec.item_nbr || '.' ||
1561 genealogy_rec.lot_number || '.' ||
1562 genealogy_rec.serial_number || '.' ||
1563 TO_CHAR(genealogy_rec.organization_id) || '.' ||
1564 TO_CHAR(genealogy_rec.inventory_item_id) || '.' ||
1565 genealogy_rec.parent_lot_number || '.' ||
1566 genealogy_rec.parent_serial_number || '.' ||
1567 TO_CHAR(genealogy_rec.parent_inventory_item_id)) := j;
1568 genealogy_rec.rlevel := v_level;
1569 genealogy_rec.parent_rlevel := v_level - 1;
1570 genealogy_rec.DELIVERY_DETAIL_ID := p_delivery_detail_id;
1571 genealogy_rec.DELIVERY_ID := p_delivery_id;
1572 genealogy_rec.GENEALOGY_OBJECT_ID := p_gen_object_id;
1573 x_rslt_tab.EXTEND;
1574 --x_rslt_par_tab.EXTEND;
1575 x_rslt_tab(l_rslt_tab_cnt) := genealogy_rec;
1576 --x_rslt_par_tab(l_rslt_tab_cnt) := genealogy_rec;
1577 l_rslt_tab_cnt := l_rslt_tab_cnt+1;
1578 ----dbms_output.put_line(66);
1579 -- PIPE ROW(genealogy_rec);
1580 END IF;
1581 ----dbms_output.put_line(63);
1582 --ADED NEWLY
1583 genealogy_rec.serial_number := s1genealogy_tab(l).serial_number;
1584 genealogy_rec.inventory_item_id := s1genealogy_tab(l).inventory_item_id;
1585 ----dbms_output.put_line(genealogy_rec.serial_number);
1586 ----dbms_output.put_line(genealogy_rec.inventory_item_id);
1587 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1588 l_stmt_no := 430;
1589 FND_LOG.String(
1590 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1591 MESSAGE => l_stmt_no ||': genealogy_rec.item_nbr= '|| genealogy_rec.item_nbr,
1592 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1593
1594 FND_LOG.String(
1595 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1596 MESSAGE => l_stmt_no ||': genealogy_rec.serial_number= '|| genealogy_rec.serial_number,
1597 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1598
1599 FND_LOG.String(
1600 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1601 MESSAGE => l_stmt_no ||': genealogy_rec.inventory_item_id= '|| genealogy_rec.inventory_item_id,
1602 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1603
1604 END IF;
1605 ELSE--IF v_tab_nodes.COUNT > 0 THEN
1606 ----dbms_output.put_line(64);
1607 CLOSE c_main;
1608 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1609 l_stmt_no := 440;
1610 FND_LOG.String(
1611 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1612 MESSAGE => l_stmt_no ||': CLOSE c_main;',
1613 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1614 FND_LOG.String(
1615 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1616 MESSAGE => l_stmt_no ||': END IF;--IF v_tab_nodes.COUNT > 0 THEN ',
1617 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1618
1619 END IF;
1620 END IF;--IF v_tab_nodes.COUNT > 0 THEN
1621 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1622 FND_LOG.String(
1623 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1624 MESSAGE => l_stmt_no ||': END IF;--IF c_main%NOTFOUND THEN ',
1625 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1626
1627 END IF;
1628
1629 END IF;--IF c_main%NOTFOUND THEN
1630
1631 CLOSE c_main;
1632 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1633 l_stmt_no := 451;
1634 FND_LOG.String(
1635 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1636 MESSAGE => l_stmt_no ||': CLOSE c_main;',
1637 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1638
1639 FND_LOG.String(
1640 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1641 MESSAGE => l_stmt_no ||': END LOOP;--FOR m IN l + 1..s1genealogy_tab.COUNT ',
1642 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1643
1644 END IF;
1645
1646 END LOOP;--FOR m IN l + 1..s1genealogy_tab.COUNT
1647 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1648 l_stmt_no := 452;
1649 FND_LOG.String(
1650 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1651 MESSAGE => l_stmt_no ||': END IF;--IF s1genealogy_tab.EXISTS(l + 1) THEN',
1652 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1653
1654 END IF;
1655
1656 END IF;--IF s1genealogy_tab.EXISTS(l + 1) THEN
1657 ----dbms_output.put_line(38);
1658 v_level := v_level + 1;
1659 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1660 l_stmt_no := 460;
1661 FND_LOG.String(
1662 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1663 MESSAGE => l_stmt_no ||': v_level= '|| v_level,
1664 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1665 FND_LOG.String(
1666 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1667 MESSAGE => l_stmt_no ||': EXIT;',
1668 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1669 END IF;
1670 EXIT;
1671 ----dbms_output.put_line(39);
1672 ELSIF s1genealogy_tab(l).transaction_quantity > v_ctr THEN
1673 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1674 l_stmt_no := 470;
1675 FND_LOG.String(
1676 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1677 MESSAGE => l_stmt_no ||': ELSIF s1genealogy_tab(l).transaction_quantity > v_ctr',
1678 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1679
1680 END IF;
1681
1682 ----dbms_output.put_line(80);
1683 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1684 l_stmt_no := 480;
1685 FND_LOG.String(
1686 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1687 MESSAGE => l_stmt_no ||': OPEN c_main(s1genealogy_tab(l).serial_number',
1688 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1689
1690 END IF;
1691
1692 OPEN c_main(s1genealogy_tab(l).serial_number ,
1693 s1genealogy_tab(l).inventory_item_id,
1694 p_organization_id);
1695 FETCH c_main INTO vr_main;
1696 IF c_main%FOUND THEN
1697 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1698 l_stmt_no := 490;
1699 FND_LOG.String(
1700 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1701 MESSAGE => l_stmt_no ||': IF c_main%FOUND',
1702 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1703
1704
1705
1706 END IF;
1707 genealogy_rec.serial_number := s1genealogy_tab(l).serial_number;
1708 genealogy_rec.inventory_item_id := s1genealogy_tab(l).inventory_item_id;
1709 v_level := v_level + 1;
1710 CLOSE c_main;
1711 ----dbms_output.put_line(81);
1712 ----dbms_output.put_line(genealogy_rec.serial_number);
1713 ----dbms_output.put_line(genealogy_rec.inventory_item_id);
1714 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1715 l_stmt_no := 500;
1716 FND_LOG.String(
1717 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1718 MESSAGE => l_stmt_no ||': s1genealogy_tab(l).serial_number= '|| s1genealogy_tab(l).serial_number,
1719 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1720
1721 FND_LOG.String(
1722 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1723 MESSAGE => l_stmt_no ||': s1genealogy_tab(l).inventory_item_id= '|| s1genealogy_tab(l).inventory_item_id,
1724 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1725
1726 FND_LOG.String(
1727 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1728 MESSAGE => l_stmt_no ||': v_level= '|| v_level,
1729 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1730
1731 FND_LOG.String(
1732 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1733 MESSAGE => l_stmt_no ||': CLOSE c_main',
1734 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1735
1736 FND_LOG.String(
1737 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1738 MESSAGE => l_stmt_no ||': EXIT',
1739 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1740
1741 END IF;
1742
1743 EXIT;
1744 ELSE
1745 ----dbms_output.put_line(82);
1746 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1747 l_stmt_no := 510;
1748 FND_LOG.String(
1749 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1750 MESSAGE => l_stmt_no ||': CLOSE c_main',
1751 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1752 FND_LOG.String(
1753 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1754 MESSAGE => l_stmt_no ||': END IF;--IF c_main%FOUND THEN ',
1755 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1756
1757
1758 END IF;
1759
1760 CLOSE c_main;
1761 END IF;--IF c_main%FOUND THEN
1762 END IF;--NOT occ.EXISTS(genealogy_rec.item_nbr || '.' || and s1genealogy_tab(l).transaction_quantity > v_ctr
1763 ----dbms_output.put_line(40);
1764 END LOOP;--FOR l IN Z..s1genealogy_tab.COUNT
1765 ----dbms_output.put_line(41);
1766 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1767 l_stmt_no := 511;
1768 FND_LOG.String(
1769 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1770 MESSAGE => l_stmt_no ||': END LOOP;--FOR l IN Z..s1genealogy_tab.COUNT',
1771 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1772
1773 FND_LOG.String(
1774 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1775 MESSAGE => l_stmt_no ||': END IF;--IF s1genealogy_tab.EXISTS(Z) THEN',
1776 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1777
1778 END IF;
1779
1780 END IF;--IF s1genealogy_tab.EXISTS(Z) THEN
1781 ----dbms_output.put_line(42);--
1782 ELSE--IF s1genealogy_tab.COUNT <> 0 THEN
1783 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1784 l_stmt_no := 512;
1785 FND_LOG.String(
1786 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1787 MESSAGE => l_stmt_no ||': ELSE--IF s1genealogy_tab.COUNT <> 0 THEN',
1788 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1789 FND_LOG.String(
1790 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1791 MESSAGE => l_stmt_no ||': EXIT; ',
1792 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1793
1794 END IF;
1795
1796 ----dbms_output.put_line(43);
1797 EXIT;
1798 END IF;--IF s1genealogy_tab.COUNT <> 0 THEN
1799 ----dbms_output.put_line(44);
1800 END LOOP;--c_main
1801 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1802 l_stmt_no := 513;
1803 FND_LOG.String(
1804 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1805 MESSAGE => l_stmt_no ||': END LOOP;--c_main',
1806 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1807
1808 END IF;
1809 ----dbms_output.put_line(45);
1810 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1811 l_stmt_no := 520;
1812 FND_LOG.String(
1813 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1814 MESSAGE => l_stmt_no ||': EXECUTE IMMEDIATE g_gen_sql BULK COLLECT INTO s3genealogy_tab ',
1815 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1816
1817 FND_LOG.String(
1818 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1819 MESSAGE => l_stmt_no ||': sgenealogy_tab(j).serial_number = '|| sgenealogy_tab(j).serial_number ,
1820 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1821
1822 FND_LOG.String(
1823 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1824 MESSAGE => l_stmt_no ||': sgenealogy_tab(j).inventory_item_id= '|| sgenealogy_tab(j).inventory_item_id,
1825 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1826
1827 END IF;
1828
1829 EXECUTE IMMEDIATE g_gen_sql
1830 BULK COLLECT INTO s3genealogy_tab
1831 USING p_organization_id ,
1832 sgenealogy_tab(j).serial_number ,
1833 sgenealogy_tab(j).inventory_item_id ,
1834 p_organization_id;
1835
1836 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1837 l_stmt_no := 521;
1838 FND_LOG.String(
1839 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1840 MESSAGE => l_stmt_no ||': EXECUTE IMMEDIATE g_gen_sql BULK COLLECT INTO s3genealogy_tab ',
1841 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1842 END IF;
1843
1844 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1845 l_stmt_no := 530;
1846 dbg_s3genealogy_tab := s3genealogy_tab;
1847 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1848 l_stmt_no := 522;
1849 FND_LOG.String(
1850 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1851 MESSAGE => l_stmt_no ||': EXECUTE IMMEDIATE g_gen_sql BULK COLLECT INTO s3genealogy_tab ',
1852 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1853 END IF;
1854 IF dbg_s3genealogy_tab.COUNT > 0 THEN
1855 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1856 l_stmt_no := 523;
1857 FND_LOG.String(
1858 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1859 MESSAGE => l_stmt_no ||': EXECUTE IMMEDIATE g_gen_sql BULK COLLECT INTO s3genealogy_tab ',
1860 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1861 END IF;
1862 FOR i6 IN dbg_s3genealogy_tab.FIRST .. dbg_s3genealogy_tab.LAST LOOP
1863 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1864 l_stmt_no := 524;
1865 FND_LOG.String(
1866 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1867 MESSAGE => l_stmt_no ||': EXECUTE IMMEDIATE g_gen_sql BULK COLLECT INTO s3genealogy_tab ',
1868 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1869 END IF;
1870 FND_LOG.String(
1871 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1872 MESSAGE => l_stmt_no ||': dbg_s3genealogy_tab(i).item_nbr= '|| dbg_s3genealogy_tab(i6).item_nbr,
1873 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1874 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1875 l_stmt_no := 525;
1876 FND_LOG.String(
1877 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1878 MESSAGE => l_stmt_no ||': EXECUTE IMMEDIATE g_gen_sql BULK COLLECT INTO s3genealogy_tab ',
1879 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1880 END IF;
1881 FND_LOG.String(
1882 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1883 MESSAGE => l_stmt_no ||': dbg_s3genealogy_tab(i).serial_number= '|| dbg_s3genealogy_tab(i6).serial_number,
1884 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1885 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1886 l_stmt_no := 526;
1887 FND_LOG.String(
1888 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1889 MESSAGE => l_stmt_no ||': EXECUTE IMMEDIATE g_gen_sql BULK COLLECT INTO s3genealogy_tab ',
1890 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1891 END IF;
1892 FND_LOG.String(
1893 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1894 MESSAGE => l_stmt_no ||': dbg_s3genealogy_tab(i).job_name= '|| dbg_s3genealogy_tab(i6).job_name,
1895 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1896 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1897 l_stmt_no := 527;
1898 FND_LOG.String(
1899 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1900 MESSAGE => l_stmt_no ||': EXECUTE IMMEDIATE g_gen_sql BULK COLLECT INTO s3genealogy_tab ',
1901 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1902 END IF;
1903
1904 END LOOP;
1905 END IF;
1906 END IF;
1907 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1908 l_stmt_no := 528;
1909 FND_LOG.String(
1910 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1911 MESSAGE => l_stmt_no ||': EXECUTE IMMEDIATE g_gen_sql BULK COLLECT INTO s3genealogy_tab ',
1912 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1913 END IF;
1914 IF s3genealogy_tab.COUNT <> 0 THEN
1915 ----dbms_output.put_line(90);
1916 Z := Y + 1;
1917 Y := Z;
1918 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1919 l_stmt_no := 540;
1920 FND_LOG.String(
1921 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1922 MESSAGE => l_stmt_no ||': Y= '|| Y,
1923 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1924
1925 FND_LOG.String(
1926 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1927 MESSAGE => l_stmt_no ||': Z= '|| Z,
1928 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1929
1930 END IF;
1931
1932 IF s3genealogy_tab.EXISTS(Z) THEN
1933 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1934 l_stmt_no := 550;
1935 FND_LOG.String(
1936 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1937 MESSAGE => l_stmt_no ||': IF s3genealogy_tab.EXISTS(Z)',
1938 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1939 FND_LOG.String(
1940 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1941 MESSAGE => l_stmt_no ||': FOR l IN Z..s3genealogy_tab.COUNT',
1942 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy.');
1943
1944 END IF;
1945
1946 FOR l IN Z..s3genealogy_tab.COUNT
1947 LOOP
1948 ----dbms_output.put_line(s3genealogy_tab(l).item_nbr);
1949 ----dbms_output.put_line(s3genealogy_tab(l).serial_number);
1950 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1951 l_stmt_no := 560;
1952 FND_LOG.String(
1953 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1954 MESSAGE => l_stmt_no ||': s3genealogy_tab(l).item_nbr= '|| s3genealogy_tab(l).item_nbr,
1955 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1956
1957 FND_LOG.String(
1958 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1959 MESSAGE => l_stmt_no ||': s3genealogy_tab(l).serial_number= '|| s3genealogy_tab(l).serial_number,
1960 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1961
1962 END IF;
1963
1964 genealogy_rec.item_nbr := s3genealogy_tab(l).item_nbr;
1965 genealogy_rec.primary_uom_code := s3genealogy_tab(l).primary_uom_code;
1966 genealogy_rec.lot_number := s3genealogy_tab(l).lot_number;
1967 genealogy_rec.serial_number := s3genealogy_tab(l).serial_number;
1968 genealogy_rec.organization_id := genealogy_tab(i).organization_id;
1969 genealogy_rec.inventory_item_id := s3genealogy_tab(l).inventory_item_id;
1970 genealogy_rec.job_name := s3genealogy_tab(l).job_name;
1971 genealogy_rec.retest_date := s3genealogy_tab(l).retest_date;
1972 genealogy_rec.expiration_date := s3genealogy_tab(l).expiration_date;
1973 genealogy_rec.best_by_date := s3genealogy_tab(l).best_by_date;
1974 genealogy_rec.origination_date := s3genealogy_tab(l).origination_date;
1975 genealogy_rec.organization_code := s3genealogy_tab(l).organization_code;
1976 genealogy_rec.lot_control_code := s3genealogy_tab(l).lot_control_code;
1977 genealogy_rec.serial_number_control_code := s3genealogy_tab(l).serial_number_control_code;
1978 genealogy_rec.cross_reference := s3genealogy_tab(l).cross_reference;
1979 genealogy_rec.serial_type := s3genealogy_tab(l).serial_type;
1980 genealogy_rec.parent_lot_number := sgenealogy_tab(j).lot_number;
1981 genealogy_rec.parent_serial_number := sgenealogy_tab(j).serial_number ;
1982 genealogy_rec.parent_inventory_item_id := sgenealogy_tab(j).inventory_item_id;
1983
1984 IF NOT occ.EXISTS(genealogy_rec.item_nbr || '.' ||
1985 genealogy_rec.lot_number || '.' ||
1986 genealogy_rec.serial_number || '.' ||
1987 TO_CHAR(genealogy_rec.organization_id) || '.' ||
1988 TO_CHAR(genealogy_rec.inventory_item_id) || '.' ||
1989 genealogy_rec.parent_lot_number || '.' ||
1990 genealogy_rec.parent_serial_number || '.' ||
1991 TO_CHAR(genealogy_rec.parent_inventory_item_id)) THEN
1992 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1993 FND_LOG.String(
1994 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1995 MESSAGE => l_stmt_no ||': IF NOT occ.EXISTS(genealogy_rec.item_nbr',
1996 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
1997
1998 END IF;
1999 occ(genealogy_rec.item_nbr || '.' ||
2000 genealogy_rec.lot_number || '.' ||
2001 genealogy_rec.serial_number || '.' ||
2002 TO_CHAR(genealogy_rec.organization_id) || '.' ||
2003 TO_CHAR(genealogy_rec.inventory_item_id) || '.' ||
2004 genealogy_rec.parent_lot_number || '.' ||
2005 genealogy_rec.parent_serial_number || '.' ||
2006 TO_CHAR(genealogy_rec.parent_inventory_item_id)) := j;
2007 genealogy_rec.rlevel := v_level - 1;
2008 genealogy_rec.parent_rlevel := v_level - 2;
2009 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2010 l_stmt_no := 570;
2011 FND_LOG.String(
2012 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
2013 MESSAGE => l_stmt_no ||': PIPE ROW(genealogy_rec)',
2014 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
2015 FND_LOG.String(
2016 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
2017 MESSAGE => l_stmt_no ||': GOTO REPEAT_LOOP',
2018 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
2019 END IF;
2020 genealogy_rec.DELIVERY_DETAIL_ID := p_delivery_detail_id;
2021 genealogy_rec.DELIVERY_ID := p_delivery_id;
2022 genealogy_rec.GENEALOGY_OBJECT_ID := p_gen_object_id;
2023 x_rslt_tab.EXTEND;
2024 --x_rslt_par_tab.EXTEND;
2025 x_rslt_tab(l_rslt_tab_cnt) := genealogy_rec;
2026 --x_rslt_par_tab(l_rslt_tab_cnt) := genealogy_rec;
2027 l_rslt_tab_cnt := l_rslt_tab_cnt+1;
2028 -- PIPE ROW(genealogy_rec);
2029 GOTO REPEAT_LOOP;
2030 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2031 l_stmt_no := 571;
2032 FND_LOG.String(
2033 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
2034 MESSAGE => l_stmt_no ||': -- PIPE ROW(genealogy_rec); ',
2035 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
2036 FND_LOG.String(
2037 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
2038 MESSAGE => l_stmt_no ||': GOTO REPEAT_LOOP;',
2039 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
2040
2041 FND_LOG.String(
2042 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
2043 MESSAGE => l_stmt_no ||': END IF;--NOT occ.EXISTS(genealogy_rec.item_nbr || "." ||',
2044 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
2045
2046 END IF;
2047 END IF;--NOT occ.EXISTS(genealogy_rec.item_nbr || '.' ||
2048 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2049 l_stmt_no := 572;
2050 FND_LOG.String(
2051 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
2052 MESSAGE => l_stmt_no ||': END LOOP;--FOR l IN Z..s3genealogy_tab.COUNT',
2053 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
2054
2055 END IF;
2056
2057 END LOOP;--FOR l IN Z..s3genealogy_tab.COUNT
2058 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2059 l_stmt_no := 573;
2060 FND_LOG.String(
2061 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
2062 MESSAGE => l_stmt_no ||': END IF;--s3genealogy_tab.EXISTS(Z) ',
2063 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
2064
2065 END IF;
2066
2067 END IF;--s3genealogy_tab.EXISTS(Z)
2068 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2069 l_stmt_no := 574;
2070 FND_LOG.String(
2071 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
2072 MESSAGE => l_stmt_no ||': END IF;--s3genealogy_tab.COUNT <> 0 ',
2073 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
2074
2075 END IF;
2076
2077 END IF;--s3genealogy_tab.COUNT <> 0
2078 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2079 l_stmt_no := 575;
2080 FND_LOG.String(
2081 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
2082 MESSAGE => l_stmt_no ||': END LOOP;--FOR j IN 1..sgenealogy_tab.COUNT',
2083 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
2084
2085 END IF;
2086
2087 END LOOP;--FOR j IN 1..sgenealogy_tab.COUNT
2088 ----dbms_output.put_line(46);
2089 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2090 l_stmt_no := 576;
2091 FND_LOG.String(
2092 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
2093 MESSAGE => l_stmt_no ||': END IF;--sgenealogy_tab.COUNT <> 0',
2094 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
2095
2096 END IF;
2097
2098 END IF;--sgenealogy_tab.COUNT <> 0
2099 ----dbms_output.put_line(47);
2100 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2101 l_stmt_no := 577;
2102 FND_LOG.String(
2103 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
2104 MESSAGE => l_stmt_no ||': END IF;--v_wms_enabled = 0 AND v_make_items = "Y"',
2105 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
2106
2107 END IF;
2108
2109 END IF;--v_wms_enabled = 0 AND v_make_items = 'Y'
2110 ----dbms_output.put_line(48);
2111 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2112 l_stmt_no := 578;
2113 FND_LOG.String(
2114 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
2115 MESSAGE => l_stmt_no ||': END LOOP;--I IN 1..genealogy_tab.COUNT',
2116 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
2117
2118 END IF;
2119 END LOOP;--I IN 1..genealogy_tab.COUNT
2120
2121 << WMS >>
2122
2123 IF v_wms_enabled = 1 THEN
2124 ----dbms_output.put_line(101);
2125 OPEN c_top(g_object_id);
2126 ----dbms_output.put_line(102);
2127 FETCH c_top
2128 BULK COLLECT INTO v_top;
2129 CLOSE c_top;
2130
2131 v_level := 0;
2132
2133 FOR i IN 1..v_top.COUNT
2134 LOOP
2135 ----dbms_output.put_line(103);
2136 IF v_top(i).rlevel = 1 THEN
2137 ----dbms_output.put_line(104);
2138 BEGIN
2139 SELECT wnd.delivery_id
2140 INTO v_delivery_id
2141 FROM mtl_transaction_details_v mtd ,
2142 mtl_material_transactions mmt ,
2143 wsh_new_deliveries wnd
2144 WHERE mtd.object_id = v_top(i).object_id
2145 AND mmt.transaction_id = mtd.transaction_id + 0
2146 AND wnd.delivery_id = mmt.trx_source_delivery_id + 0;
2147 IF l_debug_on THEN
2148 WSH_DEBUG_SV.log(l_module_name,'DELIVERY ID', v_delivery_id);
2149 END IF;
2150 ----dbms_output.put_line(v_delivery_id);
2151 EXCEPTION
2152 WHEN no_data_found THEN
2153 v_delivery_id := 0;
2154 IF l_debug_on THEN
2155 WSH_DEBUG_SV.logmsg(l_module_name,'No Data Found error has occured. Oracle error message is '|| substr(SQLERRM, 1, 200), WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2156 END IF;
2157 END;
2158 v_top(i).delivery_id := v_delivery_id;
2159 ELSE
2160 ----dbms_output.put_line(105);
2161 BEGIN
2162 SELECT wnd.delivery_id
2163 INTO v_delivery_id1
2164 FROM mtl_transaction_details_v mtd ,
2165 mtl_material_transactions mmt ,
2166 wsh_new_deliveries wnd
2167 WHERE mtd.object_id = v_top(i).object_id
2168 AND mmt.transaction_id = mtd.transaction_id + 0
2169 AND wnd.delivery_id = mmt.trx_source_delivery_id + 0;
2170 IF l_debug_on THEN
2171 WSH_DEBUG_SV.log(l_module_name,'DELIVERY ID1', v_delivery_id);
2172 END IF;
2173 ----dbms_output.put_line(v_delivery_id1);
2174 EXCEPTION
2175 WHEN no_data_found THEN
2176 v_delivery_id1 := 0;
2177 IF l_debug_on THEN
2178 WSH_DEBUG_SV.logmsg(l_module_name,'No Data Found error has occured. Oracle error message is '|| substr(SQLERRM, 1, 200), WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2179 END IF;
2180 END;
2181 IF v_delivery_id1 <> 0 THEN
2182 ----dbms_output.put_line(106);
2183 v_top(i).delivery_id := v_delivery_id1;
2184 v_delivery_id := v_delivery_id1;
2185 ELSE
2186 ----dbms_output.put_line(107);
2187 v_top(i).delivery_id := v_delivery_id;
2188 END IF;
2189 END IF;
2190 IF v_top(i).rlevel > 1 THEN
2191 ----dbms_output.put_line(108);
2192 BEGIN
2193 SELECT serial_number,
2194 NVL(parent_serial_number, serial_number || TO_CHAR(i))
2195 INTO v_top(i).serial_number,
2196 v_top(i).parent_serial_number
2197 FROM mtl_serial_numbers
2198 WHERE gen_object_id = v_top(i).object_id
2199 AND (serial_number <> parent_serial_number
2200 OR parent_serial_number IS NULL);
2201 ----dbms_output.put_line(109);
2202 IF v_top(i).parent_serial_number IS NOT NULL THEN
2203 v_sem(v_top(i).serial_number) := v_sem(v_top(i).parent_serial_number) + 1;
2204 ----dbms_output.put_line(110);
2205 ELSE
2206 v_sem(v_top(i).serial_number) := v_top(i).rlevel;
2207 ----dbms_output.put_line(111);
2208 END IF;
2209 EXCEPTION
2210 WHEN no_data_found THEN
2211 v_top(i).serial_number := NULL;
2212 v_top(i).parent_serial_number := NULL;
2213 END;
2214 ELSIF v_top(i).rlevel = 1 THEN
2215 ----dbms_output.put_line(112);
2216 BEGIN
2217 SELECT serial_number,
2218 serial_number
2219 INTO v_top(i).serial_number,
2220 v_top(i).parent_serial_number
2221 FROM mtl_serial_numbers
2222 WHERE gen_object_id = v_top(i).object_id;
2223
2224 v_sem(v_top(i).serial_number) := 1;
2225 EXCEPTION
2226 WHEN no_data_found THEN
2227 v_top(i).serial_number := NULL;
2228 v_top(i).parent_serial_number := NULL;
2229 END;
2230 END IF;
2231 IF v_top(i).serial_number IS NOT NULL THEN
2232 v_ser(v_top(i).serial_number) := v_top(i).parent_serial_number;
2233 END IF;
2234 END LOOP;
2235 FOR i IN 1..v_top.COUNT
2236 LOOP
2237 IF v_top(i).rlevel = 1 THEN
2238 v_sem(v_top(i).serial_number) := 1;
2239 ELSIF v_sem.exists(v_top(i).parent_serial_number) THEN
2240 v_top(i).rlevel := v_sem(v_top(i).parent_serial_number) + 1;
2241 END IF;
2242 IF (v_top(i).delivery_id <> p_delivery_id OR v_top(i).delivery_id IS NULL)
2243 AND v_ser.EXISTS(v_top(i).serial_number) THEN
2244
2245 v_ser(v_top(i).serial_number) := NULL;
2246
2247 ELSIF v_top(i).serial_number IS NOT NULL THEN
2248 v_ser(v_top(i).serial_number) := v_top(i).parent_serial_number || '.' || TO_CHAR(v_top(i).rlevel);
2249 END IF;
2250 END LOOP;
2251 FOR i IN 1..v_top.COUNT
2252 LOOP
2253 FOR j IN c_sub(v_top(i).object_id)
2254 LOOP
2255 IF NVL(v_top(i).delivery_id, 0) = p_delivery_id
2256 AND v_ser.EXISTS(v_top(i).serial_number)
2257 AND v_ser(v_top(i).serial_number) IS NOT NULL THEN
2258 genealogy_rec.item_nbr := j.item_number;
2259 genealogy_rec.primary_uom_code := j.primary_uom_code;
2260 genealogy_rec.lot_number := j.lot_number;
2261 genealogy_rec.serial_number := j.serial_number;
2262 genealogy_rec.organization_id := j.organization_id;
2263 genealogy_rec.inventory_item_id := j.inventory_item_id;
2264 genealogy_rec.job_name := j.job_name;
2265 genealogy_rec.retest_date := j.retest_date;
2266 genealogy_rec.expiration_date := j.expiration_date;
2267 genealogy_rec.best_by_date := j.best_by_date;
2268 genealogy_rec.origination_date := j.origination_date;
2269 genealogy_rec.organization_code := j.organization_code;
2270 genealogy_rec.lot_control_code := j.lot_control_code;
2271 genealogy_rec.serial_number_control_code := j.serial_number_control_code;
2272 genealogy_rec.cross_reference := j.cross_reference;
2273 genealogy_rec.serial_type := j.serial_type;
2274 IF NOT occ.EXISTS(genealogy_rec.item_nbr || '.' || genealogy_rec.lot_number || '.' || genealogy_rec.serial_number || '.' || TO_CHAR(genealogy_rec.organization_id) || '.' || TO_CHAR(genealogy_rec.inventory_item_id)) THEN
2275 occ(genealogy_rec.item_nbr || '.' || genealogy_rec.lot_number || '.' || genealogy_rec.serial_number || '.' || TO_CHAR(genealogy_rec.organization_id) || '.' || TO_CHAR(genealogy_rec.inventory_item_id)) := 1;
2276 genealogy_rec.rlevel := v_top(i).rlevel;
2277 genealogy_rec.parent_rlevel := v_top(i).rlevel - 1;
2278 genealogy_rec.DELIVERY_DETAIL_ID := p_delivery_detail_id;
2279 genealogy_rec.DELIVERY_ID := p_delivery_id;
2280 genealogy_rec.GENEALOGY_OBJECT_ID := p_gen_object_id;
2281 x_rslt_tab.EXTEND;
2282 --x_rslt_par_tab.EXTEND;
2283 x_rslt_tab(l_rslt_tab_cnt) := genealogy_rec;
2284 --x_rslt_par_tab(l_rslt_tab_cnt) := genealogy_rec;
2285 l_rslt_tab_cnt := l_rslt_tab_cnt+1;
2286 -- PIPE ROW(genealogy_rec);
2287 END IF;
2288 END IF;
2289 END LOOP;
2290 END LOOP;
2291 END IF;
2292 <<MAKE_ITEMS>>
2293 -- IF v_make_items = 'N' THEN
2294 --
2295 -- FOR r_make_items IN c_nonmake_items
2296 -- LOOP
2297 -- genealogy_rec.item_nbr := r_make_items.item_number;
2298 -- genealogy_rec.primary_uom_code := r_make_items.primary_uom_code;
2299 -- genealogy_rec.lot_number := r_make_items.lot_number;
2300 -- genealogy_rec.serial_number := NULL;
2301 -- genealogy_rec.organization_id := r_make_items.organization_id;
2302 -- genealogy_rec.inventory_item_id := r_make_items.inventory_item_id;
2303 -- genealogy_rec.job_name := NULL;
2304 -- genealogy_rec.retest_date := r_make_items.retest_date;
2305 -- genealogy_rec.expiration_date := r_make_items.expiration_date;
2306 -- genealogy_rec.best_by_date := r_make_items.best_by_date;
2307 -- genealogy_rec.origination_date := r_make_items.origination_date;
2308 -- genealogy_rec.organization_code := r_make_items.organization_code;
2309 -- genealogy_rec.lot_control_code := r_make_items.lot_control_code;
2310 -- genealogy_rec.serial_number_control_code := r_make_items.serial_number_control_code;
2311 -- genealogy_rec.cross_reference := r_make_items.cross_reference;
2312 -- genealogy_rec.serial_type := r_make_items.serial_type;
2313 -- IF r_make_items.from_serial_number IS NOT NULL AND r_make_items.to_serial_number IS NOT NULL THEN
2314 -- v_serial_number := r_make_items.from_serial_number;
2315 -- WHILE 1 =1
2316 -- LOOP
2317 -- IF v_serial_number IS NULL THEN
2318 -- EXIT;
2319 -- END IF;
2320 -- genealogy_rec.serial_number := v_serial_number;
2321 -- IF NOT occ.EXISTS(genealogy_rec.item_nbr ||'.'|| genealogy_rec.lot_number ||'.'|| genealogy_rec.serial_number ||'.'|| TO_CHAR(genealogy_rec.organization_id) ||'.'|| TO_CHAR(genealogy_rec.inventory_item_id)) THEN
2322 -- occ(genealogy_rec.item_nbr ||'.'|| genealogy_rec.lot_number ||'.'|| genealogy_rec.serial_number ||'.'|| TO_CHAR(genealogy_rec.organization_id) ||'.'|| TO_CHAR(genealogy_rec.inventory_item_id)) := 1;
2323 -- genealogy_rec.rlevel := 1;
2324 -- genealogy_rec.parent_rlevel := 0;
2325 -- -- PIPE ROW(genealogy_rec);
2326 -- END IF;
2327 -- -- ----dbms_output.put_line(r_make_items.from_serial_number||'.'||r_make_items.to_serial_number);
2328 -- IF r_make_items.from_serial_number = r_make_items.to_serial_number THEN
2329 -- EXIT;
2330 -- END IF;
2331 -- IF genealogy_rec.serial_number >= r_make_items.to_serial_number THEN
2332 -- EXIT;
2333 -- END IF;
2334 -- v_serial_number := get_next_serial(v_serial_number);
2335 -- END LOOP;
2336 -- END IF;
2337 -- END LOOP;
2338 -- END IF;
2339 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
2340 l_stmt_no := 590;
2341 FND_LOG.String(
2342 LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
2343 MESSAGE => l_stmt_no ||': Exiting get_genealogy',
2344 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
2345 END IF;
2346 RETURN;
2347 END;
2348
2349 ---------------------------------------------------------------------------------------
2350 --
2351 -- Function: get_genealogy
2352 -- Parameters: p_gen_object_id - It takes Genealogy object id has input
2353 -- p_delivery_detail_id- It takes delivery_detail Id has input
2354 -- p_delivery_id -It takes delivery_id has input
2355 -- p_organization_id -It takes organization_id has input
2356 -- p_frm_serial- It takes frm_serial has input
2357 -- p_to_serial-It takes to_serial has input
2358 -- Description: It gives all the Sub Componets, there Lot Numbers, Serial Numbers
2359 -- and Hierarchy Levels Present Under the Main Assembly Item
2360 -- (For Work Order the components used for making that Item).
2361 -- In case if it is not the Assembly Item it takes From Serial
2362 -- and TO Serial Numbers and Generates in between numbers.
2363 --
2364 ---------------------------------------------------------------------------------------
2365 FUNCTION get_genealogy(p_gen_object_id IN NUMBER,
2366 p_delivery_detail_id IN NUMBER,
2367 p_delivery_id IN NUMBER,
2368 p_organization_id IN NUMBER,
2369 p_frm_serial IN VARCHAR2 DEFAULT NULL,
2370 p_to_serial IN VARCHAR2 DEFAULT NULL)
2371 RETURN tab_genealogy PIPELINED
2372 IS
2373
2374 CURSOR c_nonmake_items
2375 IS
2376 SELECT msi.concatenated_segments item_number ,
2377 msi.primary_uom_code ,
2378 mln.lot_number ,
2379 waid.from_serial_number from_serial_number ,
2380 waid.to_serial_number to_serial_number ,
2381 wdd.organization_id ,
2382 NULL job_name ,
2383 msi.inventory_item_id ,
2384 mln.origination_date ,
2385 mln.best_by_date ,
2386 mln.retest_date ,
2387 mln.expiration_date ,
2388 waid.organization_code ,
2389 1 rlevel ,
2390 0 v_parent_rlevel ,
2391 msi.lot_control_code ,
2392 msi.serial_number_control_code ,
2393 decode(( SELECT upper(mcr.cross_reference)
2394 FROM mtl_cross_references_vl mcr
2395 WHERE mcr.inventory_item_id = msi.inventory_item_id + 0
2396 AND mcr.organization_id = msi.organization_id + 0
2397 AND to_char(mcr.last_update_date, 'DD/MM/YYYY-HH24.MM.SS') = (SELECT MAX(to_char(mcr1.last_update_date, 'DD/MM/YYYY-HH24.MM.SS'))
2398 FROM mtl_cross_references_vl mcr1
2399 WHERE mcr1.inventory_item_id = msi.inventory_item_id + 0
2400 AND mcr1.organization_id = msi.organization_id + 0
2401 AND mcr1.cross_reference_type ='OPSM INTEGRATED')), 'NO', 0, 1) cross_reference,
2402 (SELECT mcr.attribute1
2403 FROM mtl_cross_references_vl mcr
2404 WHERE mcr.inventory_item_id = msi.inventory_item_id + 0
2405 AND mcr.organization_id = (SELECT master_organization_id
2406 FROM mtl_parameters
2407 WHERE organization_id = msi.organization_id + 0)
2408 AND mcr.cross_reference_type ='OPSM INTEGRATED'
2409 AND mcr.cross_reference = 'YES') serial_type,
2410 NULL parent_lot_number ,
2411 NULL parent_serial_number ,
2412 0 parent_inventory_item_id
2413 FROM mtl_system_items_vl msi ,
2414 mtl_lot_numbers mln ,
2415 wsh_opsm_asn_item_details_v waid ,
2416 wsh_delivery_details wdd
2417 WHERE wdd.delivery_detail_id = p_delivery_detail_id
2418 AND wdd.organization_id = p_organization_id
2419 AND waid.delivery_detail_id = wdd.delivery_detail_id + 0
2420 AND waid.organization_id = wdd.organization_id + 0
2421 AND msi.inventory_item_id = wdd.inventory_item_id + 0
2422 AND msi.organization_id = wdd.organization_id + 0
2423 AND mln.inventory_item_id = wdd.inventory_item_id + 0
2424 AND mln.organization_id = wdd.organization_id + 0
2425 AND mln.lot_number = waid.lot_number || ''
2426 AND waid.from_serial_number = p_frm_serial
2427 AND waid.to_serial_number = p_to_serial;
2428
2429 g_sub_sql VARCHAR2(32000) :=
2430 'SELECT msi.concatenated_segments item_nbr ,
2431 msi.primary_uom_code ,
2432 mtln.lot_number ,
2433 NULL serial_number ,
2434 mmt.organization_id ,
2435 we.wip_entity_name job_name ,
2436 msi.inventory_item_id ,
2437 mtln.origination_date ,
2438 mtln.best_by_date ,
2439 mtln.retest_date ,
2440 mln.expiration_date ,
2441 ood.organization_code ,
2442 1 rlevel ,
2443 0 v_parent_rlevel ,
2444 msi.lot_control_code ,
2445 msi.serial_number_control_code ,
2446 decode(( SELECT upper(mcr.cross_reference)
2447 FROM mtl_cross_references_vl mcr
2448 WHERE mcr.inventory_item_id = msi.inventory_item_id+0
2449 AND mcr.organization_id = msi.organization_id+0
2450 AND to_char(mcr.last_update_date, ''DD/MM/YYYY-HH24.MM.SS'') = (SELECT MAX(to_char(mcr1.last_update_date, ''DD/MM/YYYY-HH24.MM.SS''))
2451 FROM mtl_cross_references_vl mcr1
2452 WHERE mcr1.inventory_item_id = msi.inventory_item_id+0
2453 AND mcr1.organization_id = msi.organization_id+0
2454 AND mcr1.cross_reference_type =''OPSM INTEGRATED'')),''NO'',0,1) cross_reference,
2455 (SELECT mcr.attribute1
2456 FROM mtl_cross_references_vl mcr
2457 WHERE mcr.inventory_item_id = msi.inventory_item_id+0
2458 AND mcr.organization_id = (SELECT master_organization_id
2459 FROM mtl_parameters
2460 WHERE organization_id=msi.organization_id+0)
2461 AND mcr.cross_reference_type =''OPSM INTEGRATED''
2462 AND mcr.cross_reference = ''YES'') serial_type,
2463 NULL parent_lot_number ,
2464 NULL parent_serial_number ,
2465 0 parent_inventory_item_id
2466 FROM mtl_transaction_lot_numbers mtln ,
2467 wip_entities we ,
2468 mtl_system_items_vl msi ,
2469 org_organization_definitions ood ,
2470 mtl_material_transactions mmt ,
2471 mtl_object_genealogy mog ,
2472 mtl_lot_numbers mln
2473 WHERE mmt.transaction_source_id+0 = we.wip_entity_id
2474 AND msi.inventory_item_id = mmt.inventory_item_id+0
2475 AND msi.organization_id = mmt.organization_id+0
2476 AND we.gen_object_id = mog.object_id
2477 AND mog.parent_object_id = :1
2478 AND mln.inventory_item_id = mtln.inventory_item_id+0
2479 AND mln.organization_id = mtln.organization_id+0
2480 AND mln.lot_number = mtln.lot_number||''''
2481 AND ood.organization_id = mtln.organization_id
2482 AND mmt.transaction_id = mog.origin_txn_id+0
2483 AND mtln.transaction_id(+) = mmt.transaction_id+0
2484 AND msi.serial_number_control_code NOT IN (6)';
2485
2486 g_gen_sql VARCHAR2(32000) :=
2487 'SELECT mtln.lot_number ,
2488 mut.serial_number ,
2489 msi.concatenated_segments item_nbr ,
2490 msi.primary_uom_code ,
2491 msi.inventory_item_id ,
2492 we.wip_entity_name job_name ,
2493 mtln.origination_date ,
2494 mtln.best_by_date ,
2495 mtln.retest_date ,
2496 mln.expiration_date ,
2497 ood.organization_code ,
2498 abs(mmt.transaction_quantity) transaction_quantity,
2499 msi.lot_control_code ,
2500 msi.serial_number_control_code ,
2501 decode(( SELECT upper(mcr.cross_reference)
2502 FROM mtl_cross_references_vl mcr
2503 WHERE mcr.inventory_item_id = msi.inventory_item_id+0
2504 AND mcr.organization_id = msi.organization_id+0
2505 AND to_char(mcr.last_update_date, ''DD/MM/YYYY-HH24.MM.SS'') = (SELECT MAX(to_char(mcr1.last_update_date, ''DD/MM/YYYY-HH24.MM.SS''))
2506 FROM mtl_cross_references_vl mcr1
2507 WHERE mcr1.inventory_item_id = msi.inventory_item_id+0
2508 AND mcr1.organization_id = msi.organization_id+0
2509 AND mcr1.cross_reference_type =''OPSM INTEGRATED'')),''NO'',0,1) cross_reference,
2510 (SELECT mcr.attribute1
2511 FROM mtl_cross_references_vl mcr
2512 WHERE mcr.inventory_item_id = msi.inventory_item_id+0
2513 AND mcr.organization_id = (SELECT master_organization_id
2514 FROM mtl_parameters
2515 WHERE organization_id=msi.organization_id+0)
2516 AND mcr.cross_reference_type =''OPSM INTEGRATED''
2517 AND mcr.cross_reference = ''YES'') serial_type,
2518 NULL parent_lot_number ,
2519 NULL parent_serial_number ,
2520 0 parent_inventory_item_id
2521 FROM mtl_material_transactions mmt1 ,
2522 mtl_transaction_lot_numbers mtln1 ,
2523 mtl_unit_transactions mut1 ,
2524 mtl_material_transactions mmt ,
2525 mtl_transaction_lot_numbers mtln ,
2526 mtl_unit_transactions mut ,
2527 wip_entities we ,
2528 mtl_system_items_vl msi ,
2529 org_organization_definitions ood ,
2530 mtl_lot_numbers mln
2531 WHERE mtln1.transaction_id+0 = mmt1.transaction_id
2532 AND mut1.transaction_id+0 = mtln1.serial_transaction_id
2533 AND mmt1.transaction_type_id = 44
2534 AND mmt1.transaction_quantity = 1
2535 AND mut1.organization_id = :1
2536 AND mut1.transaction_date = mut1.transaction_date
2537 AND mut1.inventory_item_id = mut1.inventory_item_id
2538 AND mut1.serial_number = :2
2539 AND mmt1.inventory_item_id = :3
2540 AND mmt.transaction_set_id = mmt1.transaction_set_id
2541 AND mmt.transaction_type_id = 35
2542 AND mtln.transaction_id = mmt.transaction_id+0
2543 AND mut.transaction_id = mtln.serial_transaction_id+0
2544 AND we.wip_entity_id = mmt.transaction_source_id+0
2545 AND we.organization_id = :4
2546 AND msi.inventory_item_id = mmt.inventory_item_id+0
2547 AND msi.organization_id = mmt.organization_id+0
2548 AND ood.organization_id = mmt.organization_id+0
2549 AND mln.lot_number = mtln.lot_number||''''
2550 AND mln.inventory_item_id = mmt.inventory_item_id+0
2551 AND mln.organization_id = mtln.organization_id+0';
2552
2553 CURSOR c_main(r_serial_number IN VARCHAR2 ,
2554 r_item_id IN NUMBER ,
2555 r_org_id IN NUMBER)
2556 IS
2557 SELECT ROWNUM row_num ,
2558 mog.object_id ,
2559 mog.object_type ,
2560 mtln.lot_number ,
2561 mtln.transaction_source_id ,
2562 msn.inventory_item_id ,
2563 mut.serial_number ,
2564 we.wip_entity_name job_name
2565 FROM mtl_object_genealogy mog ,
2566 mtl_transaction_lot_numbers mtln ,
2567 mtl_unit_transactions mut ,
2568 mtl_serial_numbers msn ,
2569 mtl_material_transactions mmt ,
2570 wip_entities we
2571 WHERE msn.serial_number = r_serial_number
2572 AND msn.inventory_item_id = r_item_id
2573 AND mut.serial_number = r_serial_number
2574 AND mut.inventory_item_id = r_item_id
2575 AND mut.organization_id = r_org_id
2576 AND mtln.organization_id = r_org_id
2577 AND mut.transaction_id + 0 = mtln.serial_transaction_id
2578 AND msn.serial_number = mut.serial_number
2579 AND msn.inventory_item_id = mut.inventory_item_id
2580 AND we.wip_entity_id = mtln.transaction_source_id + 0
2581 AND we.organization_id = mtln.organization_id + 0
2582 AND mog.object_id = msn.gen_object_id
2583 AND mmt.transaction_id = mtln.transaction_id + 0
2584 AND mmt.transaction_type_id = 44
2585 AND mmt.transaction_action_id = 31
2586 ORDER BY 7 DESC;
2587
2588 CURSOR c_top(rv_object_id IN NUMBER)
2589 IS
2590 SELECT (LEVEL - 1) rlevel ,
2591 mog.* ,
2592 '' serial_number ,
2593 '' parent_serial_number ,
2594 0 delivery_id ,
2595 0 rTree
2596 FROM mtl_object_genealogy mog
2597 WHERE object_type IN (1, 2)
2598 AND genealogy_origin = 1
2599 AND (end_date_active IS NULL
2600 OR TRUNC(end_date_active) >= TRUNC(SYSDATE))
2601 START WITH parent_object_id = rv_object_id
2602 CONNECT BY PRIOR object_id = parent_object_id;
2603
2604
2605 CURSOR c_sub (r_gen_object_id IN NUMBER)
2606 IS
2607 SELECT we.wip_entity_name job_name ,
2608 msi.concatenated_segments item_number ,
2609 msi.primary_uom_code ,
2610 msn.lot_number ,
2611 msn.serial_number ,
2612 mtln.origination_date ,
2613 mtln.best_by_date ,
2614 mtln.retest_date ,
2615 mtln.expiration_date ,
2616 ood.organization_code ,
2617 msi.inventory_item_id ,
2618 msi.organization_id ,
2619 msi.lot_control_code ,
2620 msi.serial_number_control_code ,
2621 decode(( SELECT upper(mcr.cross_reference)
2622 FROM mtl_cross_references_vl mcr
2623 WHERE mcr.inventory_item_id = msi.inventory_item_id + 0
2624 AND mcr.organization_id = msi.organization_id + 0
2625 AND to_char(mcr.last_update_date, 'DD/MM/YYYY-HH24.MM.SS') = (SELECT MAX(to_char(mcr1.last_update_date, 'DD/MM/YYYY-HH24.MM.SS'))
2626 FROM mtl_cross_references_vl mcr1
2627 WHERE mcr1.inventory_item_id = msi.inventory_item_id + 0
2628 AND mcr1.organization_id = msi.organization_id + 0
2629 AND mcr1.cross_reference_type ='OPSM INTEGRATED')), 'NO', 0, 1) cross_reference,
2630 (SELECT mcr.attribute1
2631 FROM mtl_cross_references_vl mcr
2632 WHERE mcr.inventory_item_id = msi.inventory_item_id + 0
2633 AND mcr.organization_id = (SELECT master_organization_id
2634 FROM mtl_parameters
2635 WHERE organization_id = msi.organization_id + 0)
2636 AND mcr.cross_reference_type ='OPSM INTEGRATED'
2637 AND mcr.cross_reference = 'YES') serial_type
2638 FROM wip_entities we ,
2639 mtl_system_items_vl msi ,
2640 mtl_serial_numbers msn ,
2641 mtl_lot_numbers mtln ,
2642 org_organization_definitions ood,
2643 mtl_cross_references mcr
2644 WHERE msn.gen_object_id = r_gen_object_id
2645 AND msi.inventory_item_id = msn.inventory_item_id
2646 AND msi.organization_id = we.organization_id + 0
2647 AND we.wip_entity_id = msn.original_wip_entity_id
2648 AND mtln.lot_number = msn.lot_number || ''
2649 AND mtln.organization_id = we.organization_id + 0
2650 AND ood.organization_id = we.organization_id + 0;
2651
2652 CURSOR c_sub_main(r_delivery_detail_id IN NUMBER,
2653 r_organization_id IN NUMBER)
2654 IS
2655 SELECT waid.genealogy_object_id
2656 FROM mtl_object_genealogy mog ,
2657 mtl_material_transactions mmt ,
2658 wip_entities we ,
2659 mtl_system_items_vl msi ,
2660 wsh_opsm_asn_item_details_v waid ,
2661 mtl_transaction_lot_numbers mtln ,
2662 mtl_unit_transactions mut ,
2663 mtl_lot_numbers mln ,
2664 org_organization_definitions ood
2665 WHERE mog.parent_object_id = waid.genealogy_object_id + 0
2666 AND mmt.transaction_id = mog.origin_txn_id + 0
2667 AND mmt.transaction_type_id = 44
2668 AND mmt.transaction_action_id = 31
2669 AND we.wip_entity_id = mmt.transaction_source_id + 0
2670 AND msi.inventory_item_id = mmt.inventory_item_id + 0
2671 AND msi.organization_id = mmt.organization_id + 0
2672 AND waid.delivery_detail_id = r_delivery_detail_id
2673 AND mtln.transaction_id = mmt.transaction_id + 0
2674 AND mut.transaction_id = mtln.serial_transaction_id + 0
2675 AND mln.lot_number = mtln.lot_number || ''
2676 AND mln.inventory_item_id = mtln.inventory_item_id + 0
2677 AND mln.organization_id = mtln.organization_id + 0
2678 AND ood.organization_id = msi.organization_id + 0
2679 AND ood.organization_id = r_organization_id;
2680
2681 r_sub_main c_sub_main%ROWTYPE;
2682
2683 TYPE r_main
2684 IS
2685 TABLE OF c_main%ROWTYPE INDEX BY BINARY_INTEGER;
2686
2687 TYPE r_top
2688 IS
2689 TABLE OF c_top%ROWTYPE INDEX BY BINARY_INTEGER;
2690
2691 TYPE r_ser
2692 IS
2693 TABLE OF VARCHAR2(255) INDEX BY VARCHAR2(255);
2694
2695 TYPE r_sem
2696 IS
2697 TABLE OF NUMBER INDEX BY VARCHAR2(32000);
2698
2699 TYPE r_objects
2700 IS
2701 TABLE OF NUMBER INDEX BY VARCHAR2(32000);
2702
2703 genealogy_rec rec_genealogy;
2704 genealogy_tab tab_genealogy;
2705 sgenealogy_tab tab_sgenealogy;
2706 s1genealogy_tab tab_sgenealogy;
2707 s2genealogy_tab tab_sgenealogy;
2708 s3genealogy_tab tab_sgenealogy;
2709
2710 dbg_genealogy_tab tab_genealogy;
2711 dbg_sgenealogy_tab tab_sgenealogy;
2712 dbg_s1genealogy_tab tab_sgenealogy;
2713 dbg_s2genealogy_tab tab_sgenealogy;
2714 dbg_s3genealogy_tab tab_sgenealogy;
2715 dbg_v_tab_nodes tab_nodes;
2716
2717 v_tab_nodes tab_nodes;
2718 occ recur;
2719 v_jobname VARCHAR2(255) := NULL;
2720 v_serial_number VARCHAR2(32000) := NULL;
2721 v_inventory_item_id NUMBER;
2722 v_delivery_id NUMBER;
2723 v_delivery_id1 NUMBER;
2724 vr_main c_main%ROWTYPE;
2725 rr_main c_main%ROWTYPE;
2726 v_level NUMBER := 1;
2727 lv_level NUMBER := 1;
2728 v_parent_rlevel NUMBER := 1;
2729 v_wms_enabled NUMBER := 0;
2730 v_make_items VARCHAR2(1) := 'Y';
2731 v_top r_top;
2732 v_ser t_ser;
2733 v_sem r_sem;
2734 v_objects r_objects;
2735 v_ctr NUMBER;
2736 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'get_genealogy';
2737 l_msg_data VARCHAR2(2000) := NULL;
2738 l_debug_on BOOLEAN;
2739 x_return_status VARCHAR2(1);
2740 Z NUMBER := 1;
2741 Y NUMBER := 1;
2742 l_stmt_no NUMBER;
2743 BEGIN
2744
2745 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2746 IF l_debug_on IS NULL
2747 THEN
2748 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2749 END IF;
2750 IF l_debug_on THEN
2751 WSH_DEBUG_SV.push(l_module_name);
2752 WSH_DEBUG_SV.log(l_module_name,'GENEALOGY OBJECT ID', p_gen_object_id);
2753 WSH_DEBUG_SV.log(l_module_name,'DELIVERY ID', p_delivery_id);
2754 WSH_DEBUG_SV.log(l_module_name,'ORGANIZATION ID', p_organization_id);
2755 WSH_DEBUG_SV.log(l_module_name,'FROM SERIAL NUMBER', p_frm_serial);
2756 WSH_DEBUG_SV.log(l_module_name,'TO SERIAL NUMBER', p_to_serial);
2757 END IF;
2758
2759 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
2760 l_stmt_no := 0;
2761 FND_LOG.String(
2762 LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
2763 MESSAGE => l_stmt_no ||': Entering get_genealogy',
2764 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
2765 FND_LOG.String(
2766 LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
2767 MESSAGE => ' p_gen_object_id= '|| p_gen_object_id,
2768 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
2769
2770 FND_LOG.String(
2771 LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
2772 MESSAGE => ' p_delivery_detail_id= '|| p_delivery_detail_id,
2773 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
2774
2775 FND_LOG.String(
2776 LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
2777 MESSAGE => ' p_delivery_id= '|| p_delivery_id,
2778 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
2779
2780 FND_LOG.String(
2781 LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
2782 MESSAGE => ' p_organization_id= '|| p_organization_id,
2783 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
2784
2785 FND_LOG.String(
2786 LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
2787 MESSAGE => ' p_frm_serial= '|| p_frm_serial,
2788 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
2789
2790 FND_LOG.String(
2791 LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
2792 MESSAGE => ' p_to_serial= '|| p_to_serial,
2793 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
2794
2795 END IF;
2796
2797 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2798 -- Check WMS enabled
2799 --
2800 ----dbms_output.put_line(1);
2801 g_object_id := NULL;
2802 BEGIN
2803 SELECT DECODE(wms_enabled_flag, 'Y', 1, 0)
2804 INTO v_wms_enabled
2805 FROM mtl_parameters
2806 WHERE organization_id = p_organization_id;
2807 ----dbms_output.put_line(v_wms_enabled);
2808 IF l_debug_on THEN
2809 WSH_DEBUG_SV.log(l_module_name, 'WMS_ENABLED_FLAG', v_wms_enabled);
2810 END IF;
2811 IF v_wms_enabled = 1 THEN
2812 g_object_id := p_gen_object_id;
2813 ----dbms_output.put_line(p_gen_object_id);
2814 GOTO WMS;
2815 END IF;
2816
2817 EXCEPTION
2818 WHEN OTHERS THEN
2819 ----dbms_output.put_line(4);
2820 NULL;
2821 END; --
2822 -- Handle non-make items
2823 --
2824 -- v_make_items := check_for_itemgenealogy(p_delivery_detail_id,p_organization_id);
2825 v_make_items := 'Y';
2826 IF l_debug_on THEN
2827 WSH_DEBUG_SV.log(l_module_name,'GENEALOGY EXIST OR NOT', v_make_items);
2828 END IF;
2829 IF v_make_items = 'N' THEN
2830 ----dbms_output.put_line(p_delivery_detail_id);
2831 OPEN c_sub_main(p_delivery_detail_id, p_organization_id);
2832 FETCH c_sub_main
2833 INTO r_sub_main;
2834 ----dbms_output.put_line(p_organization_id);
2835 IF c_sub_main%NOTFOUND THEN
2836 CLOSE c_sub_main;
2837 GOTO MAKE_ITEMS;
2838 ----dbms_output.put_line(7);
2839 ELSE
2840 v_wms_enabled := 1;
2841 g_object_id := r_sub_main.genealogy_object_id;
2842 ----dbms_output.put_line(g_object_id);
2843 CLOSE c_sub_main;
2844 ----dbms_output.put_line(9);
2845 GOTO WMS;
2846 END IF;
2847 ELSE
2848 --
2849 -- Explode main assembly
2850 --
2851 v_make_items := 'Y';
2852 l_stmt_no := 10;
2853 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2854 FND_LOG.String(
2855 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
2856 MESSAGE => l_stmt_no ||': OPEN c_main_sql(p_delivery_detail_id, p_organization_id)= ',
2857 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
2858 FND_LOG.String(
2859 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
2860 MESSAGE => l_stmt_no ||': p_delivery_detail_id= '|| p_delivery_detail_id,
2861 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
2862
2863 FND_LOG.String(
2864 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
2865 MESSAGE => l_stmt_no ||': p_organization_id= '|| p_organization_id,
2866 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
2867
2868 END IF;
2869 ----dbms_output.put_line(p_delivery_detail_id);
2870 OPEN c_main_sql(p_delivery_detail_id, p_organization_id);
2871 FETCH c_main_sql
2872 BULK COLLECT INTO genealogy_tab;
2873 ----dbms_output.put_line(p_organization_id);
2874 CLOSE c_main_sql;
2875
2876 l_stmt_no := 20;
2877 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2878 dbg_genealogy_tab := genealogy_tab;
2879 IF dbg_genealogy_tab.COUNT > 0 THEN
2880 FOR i3 IN dbg_genealogy_tab.FIRST .. dbg_genealogy_tab.LAST LOOP
2881 FND_LOG.String(
2882 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
2883 MESSAGE => l_stmt_no ||': dbg_genealogy_tab(i).item_nbr= '|| dbg_genealogy_tab(i3).item_nbr,
2884 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
2885
2886 FND_LOG.String(
2887 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
2888 MESSAGE => l_stmt_no ||': dbg_genealogy_tab(i).serial_number= '|| dbg_genealogy_tab(i3).serial_number,
2889 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
2890
2891 FND_LOG.String(
2892 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
2893 MESSAGE => l_stmt_no ||': dbg_genealogy_tab(i).job_name= '|| dbg_genealogy_tab(i3).job_name,
2894 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
2895 END LOOP;
2896 END IF;
2897 END IF;
2898
2899 END IF;
2900 --
2901 -- IF top level assembly is not serialized or not lot controlled
2902 --
2903 IF genealogy_tab.COUNT = 0 THEN
2904 l_stmt_no := 30;
2905 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2906 FND_LOG.String(
2907 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
2908 MESSAGE => l_stmt_no ||': IF genealogy_tab.COUNT = 0 THEN',
2909 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
2910 FND_LOG.String(
2911 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
2912 MESSAGE => l_stmt_no ||': EXECUTE IMMEDIATE g_sub_sql BULK COLLECT INTO genealogy_tab USING p_gen_object_id;',
2913 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
2914
2915 END IF;
2916 ----dbms_output.put_line(12);
2917 EXECUTE IMMEDIATE g_sub_sql
2918 BULK COLLECT INTO genealogy_tab
2919 USING p_gen_object_id;
2920 END IF;
2921
2922 g_serial_number := NULL;
2923
2924 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2925 FND_LOG.String(
2926 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
2927 MESSAGE => l_stmt_no ||': FOR I IN 1..genealogy_tab.COUNT',
2928 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
2929
2930 END IF;
2931
2932 FOR I IN 1..genealogy_tab.COUNT
2933 LOOP
2934 ----dbms_output.put_line(13);
2935 ----dbms_output.put_line(genealogy_tab(i).item_nbr);
2936 ----dbms_output.put_line(genealogy_tab(i).serial_number);
2937
2938 l_stmt_no := 40;
2939 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2940 FND_LOG.String(
2941 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
2942 MESSAGE => l_stmt_no ||': genealogy_tab(i).item_nbr= '|| genealogy_tab(i).item_nbr,
2943 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
2944
2945 FND_LOG.String(
2946 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
2947 MESSAGE => l_stmt_no ||': genealogy_tab(i).serial_number= '|| genealogy_tab(i).serial_number,
2948 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
2949 END IF;
2950
2951 v_level := 1;
2952 genealogy_rec.item_nbr := genealogy_tab(i).item_nbr;
2953 genealogy_rec.primary_uom_code := genealogy_tab(i).primary_uom_code;
2954 genealogy_rec.lot_number := genealogy_tab(i).lot_number;
2955 genealogy_rec.serial_number := genealogy_tab(i).serial_number;
2956 genealogy_rec.organization_id := genealogy_tab(i).organization_id;
2957 genealogy_rec.inventory_item_id := genealogy_tab(i).inventory_item_id;
2958 genealogy_rec.job_name := genealogy_tab(i).job_name;
2959 genealogy_rec.retest_date := genealogy_tab(i).retest_date;
2960 genealogy_rec.expiration_date := genealogy_tab(i).expiration_date;
2961 genealogy_rec.best_by_date := genealogy_tab(i).best_by_date;
2962 genealogy_rec.origination_date := genealogy_tab(i).origination_date;
2963 genealogy_rec.organization_code := genealogy_tab(i).organization_code;
2964 genealogy_rec.lot_control_code := genealogy_tab(i).lot_control_code;
2965 genealogy_rec.serial_number_control_code := genealogy_tab(i).serial_number_control_code;
2966 genealogy_rec.cross_reference := genealogy_tab(i).cross_reference ;
2967 genealogy_rec.serial_type := genealogy_tab(i).serial_type;
2968 genealogy_rec.parent_lot_number := NULL;
2969 genealogy_rec.parent_serial_number := NULL;
2970 genealogy_rec.parent_inventory_item_id := 0;
2971
2972 l_stmt_no := 50;
2973 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2974 FND_LOG.String(
2975 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
2976 MESSAGE => l_stmt_no ||': B4 IF NOT occ.EXISTS(genealogy_rec.item_nbr',
2977 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
2978 END IF;
2979 IF NOT occ.EXISTS(genealogy_rec.item_nbr || '.' ||
2980 genealogy_rec.lot_number || '.' ||
2981 genealogy_rec.serial_number || '.' ||
2982 TO_CHAR(genealogy_rec.organization_id) || '.' ||
2983 TO_CHAR(genealogy_rec.inventory_item_id) || '.' ||
2984 genealogy_rec.parent_lot_number || '.' ||
2985 genealogy_rec.parent_serial_number || '.' ||
2986 TO_CHAR(genealogy_rec.parent_inventory_item_id)) THEN
2987 l_stmt_no := 60;
2988 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2989 FND_LOG.String(
2990 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
2991 MESSAGE => l_stmt_no ||': After IF NOT occ.EXISTS(genealogy_rec.item_nbr',
2992 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
2993 END IF;
2994 occ(genealogy_rec.item_nbr || '.' ||
2995 genealogy_rec.lot_number || '.' ||
2996 genealogy_rec.serial_number || '.' ||
2997 TO_CHAR(genealogy_rec.organization_id) || '.' ||
2998 TO_CHAR(genealogy_rec.inventory_item_id) || '.' ||
2999 genealogy_rec.parent_lot_number || '.' ||
3000 genealogy_rec.parent_serial_number || '.' ||
3001 TO_CHAR(genealogy_rec.parent_inventory_item_id)) := i;
3002 genealogy_rec.rlevel := v_level;
3003 genealogy_rec.parent_rlevel := v_level - 1;
3004 v_level := v_level + 1;
3005 ----dbms_output.put_line(14);
3006 IF v_wms_enabled <> 1 THEN
3007 l_stmt_no := 70;
3008 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3009 FND_LOG.String(
3010 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3011 MESSAGE => l_stmt_no ||': IF v_wms_enabled <> 1 THEN',
3012 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3013 END IF;
3014 PIPE ROW(genealogy_rec);
3015 END IF;--v_wms_enabled <> 1
3016 END IF;--NOT occ.EXISTS(genealogy_rec.item_nbr
3017 l_stmt_no := 80;
3018 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3019 FND_LOG.String(
3020 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3021 MESSAGE => l_stmt_no ||': IF v_wms_enabled = 0 AND v_make_items = "Y"',
3022 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3023 END IF;
3024 IF v_wms_enabled = 0 AND v_make_items = 'Y' THEN
3025 l_stmt_no := 90;
3026 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3027
3028 FND_LOG.String(
3029 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3030 MESSAGE => l_stmt_no ||': genealogy_tab(i).item_nbr= '|| genealogy_tab(i).item_nbr,
3031 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3032
3033 FND_LOG.String(
3034 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3035 MESSAGE => l_stmt_no ||': genealogy_tab(i).serial_number= '|| genealogy_tab(i).serial_number,
3036 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3037
3038 FND_LOG.String(
3039 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3040 MESSAGE => l_stmt_no ||': genealogy_tab(i).inventory_item_id= '|| genealogy_tab(i).inventory_item_id,
3041 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3042 FND_LOG.String(
3043 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3044 MESSAGE => l_stmt_no ||': EXECUTE IMMEDIATE g_gen_sql BULK COLLECT INTO sgenealogy_tab',
3045 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3046 END IF;
3047
3048 ----dbms_output.put_line(15);
3049 EXECUTE IMMEDIATE g_gen_sql
3050 BULK COLLECT INTO sgenealogy_tab
3051 USING p_organization_id ,
3052 genealogy_tab(i).serial_number ,
3053 genealogy_tab(i).inventory_item_id,
3054 p_organization_id;
3055 ----dbms_output.put_line(genealogy_tab(i).inventory_item_id);
3056 ----dbms_output.put_line(genealogy_tab(i).serial_number);
3057 l_stmt_no := 100;
3058 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3059 FND_LOG.String(
3060 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3061 MESSAGE => l_stmt_no ||': IF sgenealogy_tab.COUNT <> 0 THEN ',
3062 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3063
3064 END IF;
3065 IF sgenealogy_tab.COUNT <> 0 THEN
3066 l_stmt_no := 110;
3067 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3068 dbg_sgenealogy_tab := sgenealogy_tab;
3069 IF dbg_sgenealogy_tab.COUNT > 0 THEN
3070 FOR i4 IN dbg_sgenealogy_tab.FIRST .. dbg_sgenealogy_tab.LAST LOOP
3071 FND_LOG.String(
3072 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3073 MESSAGE => l_stmt_no ||': dbg_sgenealogy_tab(i).item_nbr= '|| dbg_sgenealogy_tab(i4).item_nbr,
3074 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3075
3076 FND_LOG.String(
3077 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3078 MESSAGE => l_stmt_no ||': dbg_sgenealogy_tab(i).serial_number= '|| dbg_sgenealogy_tab(i4).serial_number,
3079 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3080
3081 FND_LOG.String(
3082 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3083 MESSAGE => l_stmt_no ||': dbg_sgenealogy_tab(i).job_name= '|| dbg_sgenealogy_tab(i4).job_name,
3084 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3085
3086 END LOOP;
3087 END IF;
3088 END IF;
3089 ----dbms_output.put_line(16);
3090 l_stmt_no := 120;
3091 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3092 FND_LOG.String(
3093 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3094 MESSAGE => l_stmt_no ||': FOR j IN 1..sgenealogy_tab.COUNT',
3095 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3096
3097 END IF;
3098 FOR j IN 1..sgenealogy_tab.COUNT
3099 LOOP
3100 ----dbms_output.put_line(17);
3101 Y := 1;
3102 ----dbms_output.put_line(sgenealogy_tab(j).item_nbr);
3103 ----dbms_output.put_line(sgenealogy_tab(j).serial_number);
3104 l_stmt_no := 130;
3105 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3106 FND_LOG.String(
3107 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3108 MESSAGE => l_stmt_no ||': sgenealogy_tab(i).item_nbr= '|| sgenealogy_tab(i).item_nbr,
3109 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3110
3111 FND_LOG.String(
3112 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3113 MESSAGE => l_stmt_no ||': sgenealogy_tab(i).serial_number= '|| sgenealogy_tab(i).serial_number,
3114 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3115 END IF;
3116 v_level := 2;
3117 genealogy_rec.item_nbr := sgenealogy_tab(j).item_nbr;
3118 genealogy_rec.primary_uom_code := sgenealogy_tab(j).primary_uom_code;
3119 genealogy_rec.lot_number := sgenealogy_tab(j).lot_number;
3120 genealogy_rec.serial_number := sgenealogy_tab(j).serial_number;
3121 genealogy_rec.organization_id := genealogy_tab(i).organization_id;
3122 genealogy_rec.inventory_item_id := sgenealogy_tab(j).inventory_item_id;
3123 genealogy_rec.job_name := sgenealogy_tab(j).job_name;
3124 genealogy_rec.retest_date := sgenealogy_tab(j).retest_date;
3125 genealogy_rec.expiration_date := sgenealogy_tab(j).expiration_date;
3126 genealogy_rec.best_by_date := sgenealogy_tab(j).best_by_date;
3127 genealogy_rec.origination_date := sgenealogy_tab(j).origination_date;
3128 genealogy_rec.organization_code := sgenealogy_tab(j).organization_code;
3129 genealogy_rec.lot_control_code := sgenealogy_tab(j).lot_control_code;
3130 genealogy_rec.serial_number_control_code := sgenealogy_tab(j).serial_number_control_code;
3131 genealogy_rec.cross_reference := sgenealogy_tab(j).cross_reference;
3132 genealogy_rec.serial_type := sgenealogy_tab(j).serial_type;
3133 genealogy_rec.parent_lot_number := genealogy_tab(i).lot_number;
3134 genealogy_rec.parent_serial_number := genealogy_tab(i).serial_number;
3135 genealogy_rec.parent_inventory_item_id := genealogy_tab(i).inventory_item_id;
3136 l_stmt_no := 140;
3137 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3138 FND_LOG.String(
3139 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3140 MESSAGE => l_stmt_no ||': B4 IF NOT occ.EXISTS(genealogy_rec.item_nbr',
3141 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3142 END IF;
3143 IF NOT occ.EXISTS(genealogy_rec.item_nbr || '.' ||
3144 genealogy_rec.lot_number || '.' ||
3145 genealogy_rec.serial_number || '.' ||
3146 TO_CHAR(genealogy_rec.organization_id) || '.' ||
3147 TO_CHAR(genealogy_rec.inventory_item_id) || '.' ||
3148 genealogy_rec.parent_lot_number || '.' ||
3149 genealogy_rec.parent_serial_number || '.' ||
3150 TO_CHAR(genealogy_rec.parent_inventory_item_id)) THEN
3151 l_stmt_no := 150;
3152 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3153 FND_LOG.String(
3154 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3155 MESSAGE => l_stmt_no ||': After IF NOT occ.EXISTS(genealogy_rec.item_nbr',
3156 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3157 END IF;
3158 occ(genealogy_rec.item_nbr || '.' ||
3159 genealogy_rec.lot_number || '.' ||
3160 genealogy_rec.serial_number || '.' ||
3161 TO_CHAR(genealogy_rec.organization_id) || '.' ||
3162 TO_CHAR(genealogy_rec.inventory_item_id) || '.' ||
3163 genealogy_rec.parent_lot_number || '.' ||
3164 genealogy_rec.parent_serial_number || '.' ||
3165 TO_CHAR(genealogy_rec.parent_inventory_item_id)) := j;
3166 genealogy_rec.rlevel := v_level;
3167 genealogy_rec.parent_rlevel := v_level - 1;
3168 ----dbms_output.put_line(52);
3169 l_stmt_no := 160;
3170 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3171 FND_LOG.String(
3172 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3173 MESSAGE => l_stmt_no ||': PIPE ROW(genealogy_rec);',
3174 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3175
3176 END IF;
3177 PIPE ROW(genealogy_rec);
3178 v_level := v_level + 1;
3179 END IF;
3180 ----dbms_output.put_line(sgenealogy_tab(j).serial_number);
3181 ----dbms_output.put_line(sgenealogy_tab(j).inventory_item_id);
3182 ----dbms_output.put_line(53);
3183 lv_level := v_level;
3184 vr_main.serial_number := sgenealogy_tab(j).serial_number;
3185 vr_main.inventory_item_id := sgenealogy_tab(j).inventory_item_id;
3186 vr_main.lot_number := sgenealogy_tab(j).lot_number;
3187 l_stmt_no := 170;
3188 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3189 FND_LOG.String(
3190 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3191 MESSAGE => l_stmt_no ||': vr_main.serial_number= '|| vr_main.serial_number,
3192 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3193
3194 FND_LOG.String(
3195 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3196 MESSAGE => l_stmt_no ||': vr_main.inventory_item_id= '|| vr_main.inventory_item_id,
3197 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3198
3199 FND_LOG.String(
3200 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3201 MESSAGE => l_stmt_no ||': vr_main.lot_number= '|| vr_main.lot_number,
3202 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3203
3204 END IF;
3205 l_stmt_no := 180;
3206 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3207 FND_LOG.String(
3208 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3209 MESSAGE => l_stmt_no ||': LOOP',
3210 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3211
3212 END IF;
3213
3214 << REPEAT_LOOP>>
3215 LOOP
3216 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3217 l_stmt_no := 185;
3218 FND_LOG.String(
3219 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3220 MESSAGE => l_stmt_no ||': OPEN c_main',
3221 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3222 FND_LOG.String(
3223 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3224 MESSAGE => l_stmt_no ||': genealogy_rec.item_nbr= '|| genealogy_rec.item_nbr,
3225 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3226
3227 FND_LOG.String(
3228 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3229 MESSAGE => l_stmt_no ||': genealogy_rec.inventory_item_id= '|| genealogy_rec.inventory_item_id,
3230 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3231
3232 FND_LOG.String(
3233 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3234 MESSAGE => l_stmt_no ||': genealogy_rec.serial_number= '|| genealogy_rec.serial_number,
3235 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3236
3237 END IF;
3238 OPEN c_main(genealogy_rec.serial_number ,
3239 genealogy_rec.inventory_item_id,
3240 p_organization_id);
3241 ----dbms_output.put_line(18);
3242 ----dbms_output.put_line(genealogy_rec.inventory_item_id);
3243 ----dbms_output.put_line(genealogy_rec.serial_number);
3244 FETCH c_main INTO vr_main;
3245 ----dbms_output.put_line(19);
3246 l_stmt_no := 190;
3247 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3248 FND_LOG.String(
3249 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3250 MESSAGE => l_stmt_no ||': vr_main.inventory_item_id= '|| vr_main.inventory_item_id,
3251 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3252
3253 FND_LOG.String(
3254 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3255 MESSAGE => l_stmt_no ||': vr_main.serial_number= '|| vr_main.serial_number,
3256 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3257
3258 FND_LOG.String(
3259 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3260 MESSAGE => l_stmt_no ||': b4 IF c_main%NOTFOUND THEN',
3261 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3262
3263 END IF;
3264 IF c_main%NOTFOUND THEN
3265 l_stmt_no := 200;
3266 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3267 FND_LOG.String(
3268 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3269 MESSAGE => l_stmt_no ||': after IF c_main%NOTFOUND THEN',
3270 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3271
3272 END IF;
3273 ----dbms_output.put_line(21);
3274 l_stmt_no := 210;
3275 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3276 FND_LOG.String(
3277 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3278 MESSAGE => l_stmt_no ||': b4 IF v_tab_nodes.COUNT > 0 THEN ',
3279 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3280
3281 END IF;
3282 IF v_tab_nodes.COUNT > 0 THEN
3283 l_stmt_no := 220;
3284 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3285 FND_LOG.String(
3286 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3287 MESSAGE => l_stmt_no ||': after IF v_tab_nodes.COUNT > 0 THEN ',
3288 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3289 dbg_v_tab_nodes := v_tab_nodes;
3290 IF dbg_v_tab_nodes.COUNT > 0 THEN
3291 FOR i7 IN dbg_v_tab_nodes.FIRST .. dbg_v_tab_nodes.LAST LOOP
3292 FND_LOG.String(
3293 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3294 MESSAGE => l_stmt_no ||': dbg_v_tab_nodes(i).inventory_item_id= '|| dbg_v_tab_nodes(i7).inventory_item_id,
3295 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3296
3297 FND_LOG.String(
3298 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3299 MESSAGE => l_stmt_no ||': dbg_v_tab_nodes(i).lot_number= '|| dbg_v_tab_nodes(i7).lot_number,
3300 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3301
3302 FND_LOG.String(
3303 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3304 MESSAGE => l_stmt_no ||': dbg_v_tab_nodes(i).serial_number= '|| dbg_v_tab_nodes(i7).serial_number,
3305 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3306
3307 END LOOP;
3308 END IF;
3309 FND_LOG.String(
3310 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3311 MESSAGE => l_stmt_no ||': s2genealogy_tab(v_tab_nodes.LAST).item_nbr= '|| s2genealogy_tab(v_tab_nodes.LAST).item_nbr,
3312 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3313
3314 FND_LOG.String(
3315 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3316 MESSAGE => l_stmt_no ||': s2genealogy_tab(v_tab_nodes.LAST).serial_number= '|| s2genealogy_tab(v_tab_nodes.LAST).serial_number,
3317 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3318
3319 END IF;
3320 ----dbms_output.put_line(22);
3321 ----dbms_output.put_line(s2genealogy_tab(v_tab_nodes.LAST).item_nbr);
3322 ----dbms_output.put_line(s2genealogy_tab(v_tab_nodes.LAST).serial_number);
3323 vr_main.serial_number := v_tab_nodes(v_tab_nodes.LAST).serial_number;
3324 vr_main.inventory_item_id := v_tab_nodes(v_tab_nodes.LAST).inventory_item_id;
3325 vr_main.lot_number := v_tab_nodes(v_tab_nodes.LAST).lot_number;
3326 v_level := v_tab_nodes(v_tab_nodes.LAST).LEVEL;
3327 genealogy_rec.item_nbr := s2genealogy_tab(v_tab_nodes.LAST).item_nbr;
3328 genealogy_rec.primary_uom_code := s2genealogy_tab(v_tab_nodes.LAST).primary_uom_code;
3329 genealogy_rec.lot_number := s2genealogy_tab(v_tab_nodes.LAST).lot_number;
3330 genealogy_rec.serial_number := s2genealogy_tab(v_tab_nodes.LAST).serial_number;
3331 genealogy_rec.organization_id := genealogy_tab(i).organization_id;
3332 genealogy_rec.inventory_item_id := s2genealogy_tab(v_tab_nodes.LAST).inventory_item_id;
3333 genealogy_rec.job_name := s2genealogy_tab(v_tab_nodes.LAST).job_name;
3334 genealogy_rec.retest_date := s2genealogy_tab(v_tab_nodes.LAST).retest_date;
3335 genealogy_rec.expiration_date := s2genealogy_tab(v_tab_nodes.LAST).expiration_date;
3336 genealogy_rec.best_by_date := s2genealogy_tab(v_tab_nodes.LAST).best_by_date;
3337 genealogy_rec.origination_date := s2genealogy_tab(v_tab_nodes.LAST).origination_date;
3338 genealogy_rec.organization_code := s2genealogy_tab(v_tab_nodes.LAST).organization_code;
3339 genealogy_rec.lot_control_code := s2genealogy_tab(v_tab_nodes.LAST).lot_control_code;
3340 genealogy_rec.serial_number_control_code := s2genealogy_tab(v_tab_nodes.LAST).serial_number_control_code ;
3341 genealogy_rec.cross_reference := s2genealogy_tab(v_tab_nodes.LAST).cross_reference;
3342 genealogy_rec.serial_type := s2genealogy_tab(v_tab_nodes.LAST).serial_type;
3343 genealogy_rec.parent_lot_number := s2genealogy_tab(v_tab_nodes.LAST).parent_lot_number;
3344 genealogy_rec.parent_serial_number := s2genealogy_tab(v_tab_nodes.LAST).parent_serial_number;
3345 genealogy_rec.parent_inventory_item_id := s2genealogy_tab(v_tab_nodes.LAST).parent_inventory_item_id;
3346 v_tab_nodes.DELETE(v_tab_nodes.LAST);
3347 IF NOT occ.EXISTS(genealogy_rec.item_nbr || '.' ||
3348 genealogy_rec.lot_number || '.' ||
3349 genealogy_rec.serial_number || '.' ||
3350 TO_CHAR(genealogy_rec.organization_id) || '.' ||
3351 TO_CHAR(genealogy_rec.inventory_item_id) || '.' ||
3352 genealogy_rec.parent_lot_number || '.' ||
3353 genealogy_rec.parent_serial_number || '.' ||
3354 TO_CHAR(genealogy_rec.parent_inventory_item_id)) THEN
3355 l_stmt_no := 230;
3356 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3357 FND_LOG.String(
3358 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3359 MESSAGE => l_stmt_no ||': IF NOT occ.EXISTS(genealogy_rec.item_nbr',
3360 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3361
3362 END IF;
3363
3364 occ(genealogy_rec.item_nbr || '.' ||
3365 genealogy_rec.lot_number || '.' ||
3366 genealogy_rec.serial_number || '.' ||
3367 TO_CHAR(genealogy_rec.organization_id) || '.' ||
3368 TO_CHAR(genealogy_rec.inventory_item_id) || '.' ||
3369 genealogy_rec.parent_lot_number || '.' ||
3370 genealogy_rec.parent_serial_number || '.' ||
3371 TO_CHAR(genealogy_rec.parent_inventory_item_id)) := j;
3372 genealogy_rec.rlevel := v_level;
3373 genealogy_rec.parent_rlevel := v_level - 1;
3374 l_stmt_no := 240;
3375 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3376 FND_LOG.String(
3377 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3378 MESSAGE => l_stmt_no ||': PIPE ROW(genealogy_rec)',
3379 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3380
3381 END IF;
3382
3383 PIPE ROW(genealogy_rec);
3384 END IF;
3385 ----dbms_output.put_line(22);
3386 v_level := v_level + 1;
3387 ELSE
3388 ----dbms_output.put_line(23);
3389 l_stmt_no := 250;
3390 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3391 FND_LOG.String(
3392 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3393 MESSAGE => l_stmt_no ||': CLOSE c_main; EXIT;',
3394 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3395
3396 END IF;
3397 CLOSE c_main;
3398 EXIT;
3399 END IF;
3400 END IF;
3401 l_stmt_no := 260;
3402 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3403 FND_LOG.String(
3404 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3405 MESSAGE => l_stmt_no ||': CLOSE c_main;',
3406 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3407 FND_LOG.String(
3408 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3409 MESSAGE => l_stmt_no ||': vr_main.serial_number = '|| vr_main.serial_number ,
3410 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3411
3412 FND_LOG.String(
3413 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3414 MESSAGE => l_stmt_no ||': vr_main.inventory_item_id= '|| vr_main.inventory_item_id,
3415 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3416
3417 END IF;
3418 CLOSE c_main;
3419 ----dbms_output.put_line(24);
3420 EXECUTE IMMEDIATE g_gen_sql
3421 BULK COLLECT INTO s1genealogy_tab
3422 USING p_organization_id ,
3423 vr_main.serial_number ,
3424 vr_main.inventory_item_id ,
3425 p_organization_id;
3426 ----dbms_output.put_line(25);
3427 v_ctr := 1;
3428 ----dbms_output.put_line(26);
3429 l_stmt_no := 270;
3430 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3431 FND_LOG.String(
3432 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3433 MESSAGE => l_stmt_no ||':EXECUTE IMMEDIATE g_gen_sql BULK COLLECT INTO s1genealogy_tab USING p_organization_id ,vr_main.serial_number , vr_main.inventory_item_id ,p_organization_id;',
3434 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy.');
3435
3436 FND_LOG.String(
3437 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3438 MESSAGE => l_stmt_no ||': v_ctr= '|| v_ctr,
3439 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3440 dbg_s1genealogy_tab := s1genealogy_tab;
3441 IF dbg_s1genealogy_tab.COUNT > 0 THEN
3442 FOR i1 IN dbg_s1genealogy_tab.FIRST .. dbg_s1genealogy_tab.LAST LOOP
3443 FND_LOG.String(
3444 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3445 MESSAGE => l_stmt_no ||': dbg_s1genealogy_tab(i1).item_nbr= '|| dbg_s1genealogy_tab(i1).item_nbr,
3446 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3447
3448 FND_LOG.String(
3449 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3450 MESSAGE => l_stmt_no ||': dbg_s1genealogy_tab(i1).serial_number= '|| dbg_s1genealogy_tab(i1).serial_number,
3451 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3452 FND_LOG.String(
3453 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3454 MESSAGE => l_stmt_no ||': dbg_s1genealogy_tab(i1).serial_number= '|| dbg_s1genealogy_tab(i1).job_name,
3455 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3456
3457 END LOOP;
3458 END IF;
3459 END IF;
3460
3461 IF s1genealogy_tab.COUNT <> 0 THEN
3462 l_stmt_no := 280;
3463 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3464 FND_LOG.String(
3465 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3466 MESSAGE => l_stmt_no ||': IF s1genealogy_tab.COUNT <> 0',
3467 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3468
3469 END IF;
3470 ----dbms_output.put_line(27);
3471 ----dbms_output.put_line(vr_main.serial_number);
3472 ----dbms_output.put_line(vr_main.inventory_item_id);
3473 IF vr_main.inventory_item_id <> sgenealogy_tab(j).inventory_item_id THEN
3474 Z := 1;
3475 ELSE
3476 Z := Y;
3477 END IF;
3478 l_stmt_no := 290;
3479 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3480 FND_LOG.String(
3481 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3482 MESSAGE => l_stmt_no ||': IF vr_main.inventory_item_id <> sgenealogy_tab(j).inventory_item_id THEN',
3483 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy.');
3484 FND_LOG.String(
3485 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3486 MESSAGE => l_stmt_no ||': vr_main.inventory_item_id= '|| vr_main.inventory_item_id,
3487 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3488
3489 FND_LOG.String(
3490 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3491 MESSAGE => l_stmt_no ||': sgenealogy_tab(j).inventory_item_id= '|| sgenealogy_tab(j).inventory_item_id,
3492 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3493
3494 FND_LOG.String(
3495 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3496 MESSAGE => l_stmt_no ||': Y= '|| Y,
3497 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3498
3499 FND_LOG.String(
3500 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3501 MESSAGE => l_stmt_no ||': Z= '|| Z,
3502 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3503
3504 END IF;
3505 IF s1genealogy_tab.EXISTS(Z) THEN
3506 ----dbms_output.put_line(28);
3507 ----dbms_output.put_line(Z);
3508 l_stmt_no := 300;
3509 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3510 FND_LOG.String(
3511 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3512 MESSAGE => l_stmt_no ||': IF s1genealogy_tab.EXISTS(Z)',
3513 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3514 FND_LOG.String(
3515 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3516 MESSAGE => l_stmt_no ||': FOR l IN Z..s1genealogy_tab.COUNT',
3517 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3518 FND_LOG.String(
3519 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3520 MESSAGE => 's1genealogy_tab.COUNT= '|| s1genealogy_tab.COUNT,
3521 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3522
3523 END IF;
3524 FOR l IN Z..s1genealogy_tab.COUNT
3525 LOOP
3526 l_stmt_no := 310;
3527 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3528 FND_LOG.String(
3529 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3530 MESSAGE => l_stmt_no ||': s1genealogy_tab(l).item_nbr= '|| s1genealogy_tab(l).item_nbr,
3531 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3532
3533 FND_LOG.String(
3534 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3535 MESSAGE => l_stmt_no ||': s1genealogy_tab(l).serial_number= '|| s1genealogy_tab(l).serial_number,
3536 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3537 FND_LOG.String(
3538 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3539 MESSAGE => l_stmt_no ||': s1genealogy_tab(l).transaction_quantity= '|| s1genealogy_tab(l).transaction_quantity,
3540 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3541
3542 END IF;
3543 ----dbms_output.put_line(29);
3544 ----dbms_output.put_line(s1genealogy_tab(l).item_nbr);
3545 ----dbms_output.put_line(s1genealogy_tab(l).serial_number);
3546 genealogy_rec.item_nbr := s1genealogy_tab(l).item_nbr;
3547 genealogy_rec.primary_uom_code := s1genealogy_tab(l).primary_uom_code;
3548 genealogy_rec.lot_number := s1genealogy_tab(l).lot_number;
3549 genealogy_rec.serial_number := s1genealogy_tab(l).serial_number;
3550 genealogy_rec.organization_id := genealogy_tab(i).organization_id;
3551 genealogy_rec.inventory_item_id := s1genealogy_tab(l).inventory_item_id;
3552 genealogy_rec.job_name := s1genealogy_tab(l).job_name;
3553 genealogy_rec.retest_date := s1genealogy_tab(l).retest_date;
3554 genealogy_rec.expiration_date := s1genealogy_tab(l).expiration_date;
3555 genealogy_rec.best_by_date := s1genealogy_tab(l).best_by_date;
3556 genealogy_rec.origination_date := s1genealogy_tab(l).origination_date;
3557 genealogy_rec.organization_code := s1genealogy_tab(l).organization_code;
3558 genealogy_rec.lot_control_code := s1genealogy_tab(l).lot_control_code;
3559 genealogy_rec.serial_number_control_code := s1genealogy_tab(l).serial_number_control_code;
3560 genealogy_rec.cross_reference := s1genealogy_tab(l).cross_reference;
3561 genealogy_rec.serial_type := s1genealogy_tab(l).serial_type;
3562 genealogy_rec.parent_lot_number := vr_main.lot_number;
3563 genealogy_rec.parent_serial_number := vr_main.serial_number;
3564 genealogy_rec.parent_inventory_item_id := vr_main.inventory_item_id;
3565 ----dbms_output.put_line(s1genealogy_tab(l).transaction_quantity);
3566 IF NOT occ.EXISTS(genealogy_rec.item_nbr || '.' ||
3567 genealogy_rec.lot_number || '.' ||
3568 genealogy_rec.serial_number || '.' ||
3569 TO_CHAR(genealogy_rec.organization_id) || '.' ||
3570 TO_CHAR(genealogy_rec.inventory_item_id) || '.' ||
3571 genealogy_rec.parent_lot_number || '.' ||
3572 genealogy_rec.parent_serial_number || '.' ||
3573 TO_CHAR(genealogy_rec.parent_inventory_item_id)) THEN
3574 l_stmt_no := 320;
3575 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3576 FND_LOG.String(
3577 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3578 MESSAGE => l_stmt_no ||': IF NOT occ.EXISTS(genealogy_rec.item_nbr',
3579 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3580
3581 END IF;
3582
3583 occ(genealogy_rec.item_nbr || '.' ||
3584 genealogy_rec.lot_number || '.' ||
3585 genealogy_rec.serial_number || '.' ||
3586 TO_CHAR(genealogy_rec.organization_id) || '.' ||
3587 TO_CHAR(genealogy_rec.inventory_item_id) || '.' ||
3588 genealogy_rec.parent_lot_number || '.' ||
3589 genealogy_rec.parent_serial_number || '.' ||
3590 TO_CHAR(genealogy_rec.parent_inventory_item_id)) := j;
3591 genealogy_rec.rlevel := v_level;
3592 genealogy_rec.parent_rlevel := v_level - 1;
3593 ----dbms_output.put_line(30);
3594 l_stmt_no := 330;
3595 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3596 FND_LOG.String(
3597 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3598 MESSAGE => l_stmt_no ||': PIPE ROW(genealogy_rec)',
3599 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3600
3601 END IF;
3602 PIPE ROW(genealogy_rec);
3603 ----dbms_output.put_line(50);
3604 ----dbms_output.put_line(sgenealogy_tab(j).transaction_quantity);
3605 ----dbms_output.put_line(s1genealogy_tab(l).transaction_quantity);
3606 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3607 l_stmt_no := 340;
3608 FND_LOG.String(
3609 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3610 MESSAGE => l_stmt_no ||': sgenealogy_tab(j).transaction_quantity= '|| sgenealogy_tab(j).transaction_quantity,
3611 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3612
3613 FND_LOG.String(
3614 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3615 MESSAGE => l_stmt_no ||': v_ctr= '|| v_ctr,
3616 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3617
3618 END IF;
3619 IF sgenealogy_tab(j).transaction_quantity >= v_ctr THEN
3620 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3621 l_stmt_no := 341;
3622 FND_LOG.String(
3623 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3624 MESSAGE => l_stmt_no ||': IF sgenealogy_tab(j).transaction_quantity >= v_ctr THEN',
3625 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy.');
3626
3627 END IF;
3628
3629 genealogy_rec.serial_number := sgenealogy_tab(j).serial_number;
3630 genealogy_rec.inventory_item_id := sgenealogy_tab(j).inventory_item_id;
3631 v_level := lv_level;
3632 ----dbms_output.put_line(31);
3633 ----dbms_output.put_line(genealogy_rec.serial_number);
3634 ----dbms_output.put_line(genealogy_rec.inventory_item_id);
3635 EXIT;
3636 ELSIF v_ctr < sgenealogy_tab(j).transaction_quantity THEN
3637 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3638 l_stmt_no := 342;
3639 FND_LOG.String(
3640 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3641 MESSAGE => l_stmt_no ||': ELSIF v_ctr < sgenealogy_tab(j).transaction_quantity THEN',
3642 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy.');
3643
3644 END IF;
3645
3646 ----dbms_output.put_line(32);
3647 IF v_ctr = 1 THEN
3648 ----dbms_output.put_line(33);
3649 lv_level := v_level;
3650 END IF;
3651 ----dbms_output.put_line(34);
3652 v_ctr := v_ctr + 1;
3653 v_level := v_level + 1;
3654 END IF;
3655 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3656 l_stmt_no := 350;
3657 FND_LOG.String(
3658 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3659 MESSAGE => l_stmt_no ||': v_ctr= '|| v_ctr,
3660 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3661
3662 FND_LOG.String(
3663 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3664 MESSAGE => l_stmt_no ||': v_level= '|| v_level,
3665 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3666
3667 END IF;
3668
3669 ELSIF s1genealogy_tab(l).transaction_quantity = v_ctr THEN
3670 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3671 l_stmt_no := 360;
3672 FND_LOG.String(
3673 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3674 MESSAGE => l_stmt_no ||': ELSIF s1genealogy_tab(l).transaction_quantity = v_ctr THEN',
3675 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy.');
3676
3677 FND_LOG.String(
3678 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3679 MESSAGE => l_stmt_no ||': s1genealogy_tab(l).transaction_quantity= '|| s1genealogy_tab(l).transaction_quantity,
3680 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3681
3682 FND_LOG.String(
3683 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3684 MESSAGE => l_stmt_no ||': v_ctr= '|| v_ctr,
3685 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3686
3687 END IF;
3688 ----dbms_output.put_line(35);
3689 IF s1genealogy_tab.EXISTS(l + 1) THEN
3690 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3691 l_stmt_no := 370;
3692 FND_LOG.String(
3693 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3694 MESSAGE => l_stmt_no ||': IF s1genealogy_tab.EXISTS(l+1)',
3695 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3696 FND_LOG.String(
3697 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3698 MESSAGE => l_stmt_no ||': FOR m IN l+1..s1genealogy_tab.COUNT',
3699 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3700
3701 END IF;
3702
3703 ----dbms_output.put_line(36);
3704 FOR m IN l + 1..s1genealogy_tab.COUNT
3705 LOOP
3706 ----dbms_output.put_line(37);
3707 ----dbms_output.put_line(s1genealogy_tab(m).item_nbr);
3708 ----dbms_output.put_line(s1genealogy_tab(m).serial_number);
3709 ----dbms_output.put_line(vr_main.serial_number);
3710 ----dbms_output.put_line(vr_main.inventory_item_id);
3711
3712 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3713 l_stmt_no := 380;
3714 FND_LOG.String(
3715 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3716 MESSAGE => l_stmt_no ||': s1genealogy_tab(m).item_nbr= '|| s1genealogy_tab(m).item_nbr,
3717 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3718
3719 FND_LOG.String(
3720 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3721 MESSAGE => l_stmt_no ||': s1genealogy_tab(m).serial_number= '|| s1genealogy_tab(m).serial_number,
3722 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3723
3724 FND_LOG.String(
3725 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3726 MESSAGE => l_stmt_no ||': vr_main.serial_number= '|| vr_main.serial_number,
3727 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3728
3729 FND_LOG.String(
3730 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3731 MESSAGE => l_stmt_no ||': vr_main.inventory_item_id= '|| vr_main.inventory_item_id,
3732 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3733
3734 END IF;
3735
3736 v_tab_nodes(m).serial_number := s1genealogy_tab(m).serial_number;
3737 v_tab_nodes(m).inventory_item_id := s1genealogy_tab(m).inventory_item_id;
3738 v_tab_nodes(m).lot_number := s1genealogy_tab(m).lot_number;
3739 v_tab_nodes(m).LEVEL := v_level;
3740 s2genealogy_tab(m).item_nbr := s1genealogy_tab(m).item_nbr;
3741 s2genealogy_tab(m).primary_uom_code := s1genealogy_tab(m).primary_uom_code;
3742 s2genealogy_tab(m).lot_number := s1genealogy_tab(m).lot_number;
3743 s2genealogy_tab(m).serial_number := s1genealogy_tab(m).serial_number;
3744 s2genealogy_tab(m).inventory_item_id := s1genealogy_tab(m).inventory_item_id;
3745 s2genealogy_tab(m).job_name := s1genealogy_tab(m).job_name;
3746 s2genealogy_tab(m).retest_date := s1genealogy_tab(m).retest_date;
3747 s2genealogy_tab(m).expiration_date := s1genealogy_tab(m).expiration_date;
3748 s2genealogy_tab(m).best_by_date := s1genealogy_tab(m).best_by_date;
3749 s2genealogy_tab(m).origination_date := s1genealogy_tab(m).origination_date;
3750 s2genealogy_tab(m).organization_code := s1genealogy_tab(m).organization_code;
3751 s2genealogy_tab(m).lot_control_code := s1genealogy_tab(m).lot_control_code;
3752 s2genealogy_tab(m).serial_number_control_code := s1genealogy_tab(m).serial_number_control_code;
3753 s2genealogy_tab(m).cross_reference := s1genealogy_tab(m).cross_reference;
3754 s2genealogy_tab(m).serial_type := s1genealogy_tab(m).serial_type;
3755 s2genealogy_tab(m).parent_lot_number := vr_main.lot_number;
3756 s2genealogy_tab(m).parent_serial_number := vr_main.serial_number;
3757 s2genealogy_tab(m).parent_inventory_item_id := vr_main.inventory_item_id;
3758 ------ADDED NEWLY
3759
3760 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3761 l_stmt_no := 390;
3762 FND_LOG.String(
3763 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3764 MESSAGE => l_stmt_no ||': OPEN c_main(v_tab_nodes(m).serial_number',
3765 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3766 FND_LOG.String(
3767 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3768 MESSAGE => l_stmt_no ||': v_tab_nodes(m).serial_number= '|| v_tab_nodes(m).serial_number,
3769 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3770
3771 FND_LOG.String(
3772 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3773 MESSAGE => l_stmt_no ||': v_tab_nodes(m).inventory_item_id= '|| v_tab_nodes(m).inventory_item_id,
3774 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3775
3776 END IF;
3777
3778 OPEN c_main(v_tab_nodes(m).serial_number ,
3779 v_tab_nodes(m).inventory_item_id,
3780 p_organization_id);
3781 FETCH c_main INTO vr_main;
3782 ----dbms_output.put_line(60);
3783 IF c_main%NOTFOUND THEN
3784 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3785 l_stmt_no := 400;
3786 FND_LOG.String(
3787 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3788 MESSAGE => l_stmt_no ||': IF c_main%NOTFOUND',
3789 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3790
3791 END IF;
3792
3793 ----dbms_output.put_line(61);
3794 IF v_tab_nodes.COUNT > 0 THEN
3795 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3796 l_stmt_no := 410;
3797 FND_LOG.String(
3798 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3799 MESSAGE => l_stmt_no ||': v_tab_nodes.COUNT > 0',
3800 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3801 FND_LOG.String(
3802 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3803 MESSAGE => l_stmt_no ||': s2genealogy_tab(v_tab_nodes.LAST).item_nbr= '|| s2genealogy_tab(v_tab_nodes.LAST).item_nbr,
3804 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3805
3806 FND_LOG.String(
3807 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3808 MESSAGE => l_stmt_no ||': s2genealogy_tab(v_tab_nodes.LAST).serial_number= '|| s2genealogy_tab(v_tab_nodes.LAST).serial_number,
3809 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3810
3811
3812 END IF;
3813 ----dbms_output.put_line(62);
3814 ----dbms_output.put_line(s2genealogy_tab(v_tab_nodes.LAST).item_nbr);
3815 ----dbms_output.put_line(s2genealogy_tab(v_tab_nodes.LAST).serial_number);
3816 v_level := v_tab_nodes(v_tab_nodes.LAST).LEVEL;
3817 genealogy_rec.item_nbr := s2genealogy_tab(v_tab_nodes.LAST).item_nbr;
3818 genealogy_rec.primary_uom_code := s2genealogy_tab(v_tab_nodes.LAST).primary_uom_code;
3819 genealogy_rec.lot_number := s2genealogy_tab(v_tab_nodes.LAST).lot_number;
3820 genealogy_rec.serial_number := s2genealogy_tab(v_tab_nodes.LAST).serial_number;
3821 genealogy_rec.organization_id := genealogy_tab(i).organization_id;
3822 genealogy_rec.inventory_item_id := s2genealogy_tab(v_tab_nodes.LAST).inventory_item_id;
3823 genealogy_rec.job_name := s2genealogy_tab(v_tab_nodes.LAST).job_name;
3824 genealogy_rec.retest_date := s2genealogy_tab(v_tab_nodes.LAST).retest_date;
3825 genealogy_rec.expiration_date := s2genealogy_tab(v_tab_nodes.LAST).expiration_date;
3826 genealogy_rec.best_by_date := s2genealogy_tab(v_tab_nodes.LAST).best_by_date;
3827 genealogy_rec.origination_date := s2genealogy_tab(v_tab_nodes.LAST).origination_date;
3828 genealogy_rec.organization_code := s2genealogy_tab(v_tab_nodes.LAST).organization_code;
3829 genealogy_rec.lot_control_code := s2genealogy_tab(v_tab_nodes.LAST).lot_control_code;
3830 genealogy_rec.serial_number_control_code := s2genealogy_tab(v_tab_nodes.LAST).serial_number_control_code ;
3831 genealogy_rec.cross_reference := s2genealogy_tab(v_tab_nodes.LAST).cross_reference;
3832 genealogy_rec.serial_type := s2genealogy_tab(v_tab_nodes.LAST).serial_type;
3833 genealogy_rec.parent_lot_number := s2genealogy_tab(v_tab_nodes.LAST).parent_lot_number;
3834 genealogy_rec.parent_serial_number := s2genealogy_tab(v_tab_nodes.LAST).parent_serial_number;
3835 genealogy_rec.parent_inventory_item_id := s2genealogy_tab(v_tab_nodes.LAST).parent_inventory_item_id;
3836 v_tab_nodes.DELETE(v_tab_nodes.LAST);
3837 IF NOT occ.EXISTS(genealogy_rec.item_nbr || '.' ||
3838 genealogy_rec.lot_number || '.' ||
3839 genealogy_rec.serial_number || '.' ||
3840 TO_CHAR(genealogy_rec.organization_id) || '.' ||
3841 TO_CHAR(genealogy_rec.inventory_item_id) || '.' ||
3842 genealogy_rec.parent_lot_number || '.' ||
3843 genealogy_rec.parent_serial_number || '.' ||
3844 TO_CHAR(genealogy_rec.parent_inventory_item_id)) THEN
3845 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3846 l_stmt_no := 420;
3847 FND_LOG.String(
3848 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3849 MESSAGE => l_stmt_no ||': IF NOT occ.EXISTS(genealogy_rec.item_nbr',
3850 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3851 FND_LOG.String(
3852 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3853 MESSAGE => l_stmt_no ||': PIPE ROW(genealogy_rec)',
3854 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3855 END IF;
3856 occ(genealogy_rec.item_nbr || '.' ||
3857 genealogy_rec.lot_number || '.' ||
3858 genealogy_rec.serial_number || '.' ||
3859 TO_CHAR(genealogy_rec.organization_id) || '.' ||
3860 TO_CHAR(genealogy_rec.inventory_item_id) || '.' ||
3861 genealogy_rec.parent_lot_number || '.' ||
3862 genealogy_rec.parent_serial_number || '.' ||
3863 TO_CHAR(genealogy_rec.parent_inventory_item_id)) := j;
3864 genealogy_rec.rlevel := v_level;
3865 genealogy_rec.parent_rlevel := v_level - 1;
3866 ----dbms_output.put_line(66);
3867 PIPE ROW(genealogy_rec);
3868 END IF;
3869 ----dbms_output.put_line(63);
3870 --ADED NEWLY
3871 genealogy_rec.serial_number := s1genealogy_tab(l).serial_number;
3872 genealogy_rec.inventory_item_id := s1genealogy_tab(l).inventory_item_id;
3873 ----dbms_output.put_line(genealogy_rec.serial_number);
3874 ----dbms_output.put_line(genealogy_rec.inventory_item_id);
3875 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3876 l_stmt_no := 430;
3877 FND_LOG.String(
3878 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3879 MESSAGE => l_stmt_no ||': genealogy_rec.item_nbr= '|| genealogy_rec.item_nbr,
3880 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3881
3882 FND_LOG.String(
3883 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3884 MESSAGE => l_stmt_no ||': genealogy_rec.serial_number= '|| genealogy_rec.serial_number,
3885 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3886
3887 FND_LOG.String(
3888 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3889 MESSAGE => l_stmt_no ||': genealogy_rec.inventory_item_id= '|| genealogy_rec.inventory_item_id,
3890 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3891
3892 END IF;
3893 ELSE--IF v_tab_nodes.COUNT > 0 THEN
3894 ----dbms_output.put_line(64);
3895 CLOSE c_main;
3896 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3897 l_stmt_no := 440;
3898 FND_LOG.String(
3899 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3900 MESSAGE => l_stmt_no ||': CLOSE c_main;',
3901 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3902 FND_LOG.String(
3903 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3904 MESSAGE => l_stmt_no ||': END IF;--IF v_tab_nodes.COUNT > 0 THEN ',
3905 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3906
3907 END IF;
3908 END IF;--IF v_tab_nodes.COUNT > 0 THEN
3909 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3910 FND_LOG.String(
3911 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3912 MESSAGE => l_stmt_no ||': END IF;--IF c_main%NOTFOUND THEN ',
3913 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3914
3915 END IF;
3916
3917 END IF;--IF c_main%NOTFOUND THEN
3918
3919 CLOSE c_main;
3920 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3921 l_stmt_no := 451;
3922 FND_LOG.String(
3923 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3924 MESSAGE => l_stmt_no ||': CLOSE c_main;',
3925 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3926
3927 FND_LOG.String(
3928 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3929 MESSAGE => l_stmt_no ||': END LOOP;--FOR m IN l + 1..s1genealogy_tab.COUNT ',
3930 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3931
3932 END IF;
3933
3934 END LOOP;--FOR m IN l + 1..s1genealogy_tab.COUNT
3935 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3936 l_stmt_no := 452;
3937 FND_LOG.String(
3938 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3939 MESSAGE => l_stmt_no ||': END IF;--IF s1genealogy_tab.EXISTS(l + 1) THEN',
3940 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3941
3942 END IF;
3943
3944 END IF;--IF s1genealogy_tab.EXISTS(l + 1) THEN
3945 ----dbms_output.put_line(38);
3946 v_level := v_level + 1;
3947 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3948 l_stmt_no := 460;
3949 FND_LOG.String(
3950 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3951 MESSAGE => l_stmt_no ||': v_level= '|| v_level,
3952 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3953 FND_LOG.String(
3954 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3955 MESSAGE => l_stmt_no ||': EXIT;',
3956 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3957 END IF;
3958 EXIT;
3959 ----dbms_output.put_line(39);
3960 ELSIF s1genealogy_tab(l).transaction_quantity > v_ctr THEN
3961 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3962 l_stmt_no := 470;
3963 FND_LOG.String(
3964 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3965 MESSAGE => l_stmt_no ||': ELSIF s1genealogy_tab(l).transaction_quantity > v_ctr',
3966 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3967
3968 END IF;
3969
3970 ----dbms_output.put_line(80);
3971 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3972 l_stmt_no := 480;
3973 FND_LOG.String(
3974 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3975 MESSAGE => l_stmt_no ||': OPEN c_main(s1genealogy_tab(l).serial_number',
3976 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3977
3978 END IF;
3979
3980 OPEN c_main(s1genealogy_tab(l).serial_number ,
3981 s1genealogy_tab(l).inventory_item_id,
3982 p_organization_id);
3983 FETCH c_main INTO vr_main;
3984 IF c_main%FOUND THEN
3985 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3986 l_stmt_no := 490;
3987 FND_LOG.String(
3988 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3989 MESSAGE => l_stmt_no ||': IF c_main%FOUND',
3990 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
3991
3992
3993
3994 END IF;
3995 genealogy_rec.serial_number := s1genealogy_tab(l).serial_number;
3996 genealogy_rec.inventory_item_id := s1genealogy_tab(l).inventory_item_id;
3997 v_level := v_level + 1;
3998 CLOSE c_main;
3999 ----dbms_output.put_line(81);
4000 ----dbms_output.put_line(genealogy_rec.serial_number);
4001 ----dbms_output.put_line(genealogy_rec.inventory_item_id);
4002 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
4003 l_stmt_no := 500;
4004 FND_LOG.String(
4005 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4006 MESSAGE => l_stmt_no ||': s1genealogy_tab(l).serial_number= '|| s1genealogy_tab(l).serial_number,
4007 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
4008
4009 FND_LOG.String(
4010 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4011 MESSAGE => l_stmt_no ||': s1genealogy_tab(l).inventory_item_id= '|| s1genealogy_tab(l).inventory_item_id,
4012 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
4013
4014 FND_LOG.String(
4015 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4016 MESSAGE => l_stmt_no ||': v_level= '|| v_level,
4017 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
4018
4019 FND_LOG.String(
4020 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4021 MESSAGE => l_stmt_no ||': CLOSE c_main',
4022 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
4023
4024 FND_LOG.String(
4025 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4026 MESSAGE => l_stmt_no ||': EXIT',
4027 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
4028
4029 END IF;
4030
4031 EXIT;
4032 ELSE
4033 ----dbms_output.put_line(82);
4034 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
4035 l_stmt_no := 510;
4036 FND_LOG.String(
4037 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4038 MESSAGE => l_stmt_no ||': CLOSE c_main',
4039 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
4040 FND_LOG.String(
4041 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4042 MESSAGE => l_stmt_no ||': END IF;--IF c_main%FOUND THEN ',
4043 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
4044
4045
4046 END IF;
4047
4048 CLOSE c_main;
4049 END IF;--IF c_main%FOUND THEN
4050 END IF;--NOT occ.EXISTS(genealogy_rec.item_nbr || '.' || and s1genealogy_tab(l).transaction_quantity > v_ctr
4051 ----dbms_output.put_line(40);
4052 END LOOP;--FOR l IN Z..s1genealogy_tab.COUNT
4053 ----dbms_output.put_line(41);
4054 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
4055 l_stmt_no := 511;
4056 FND_LOG.String(
4057 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4058 MESSAGE => l_stmt_no ||': END LOOP;--FOR l IN Z..s1genealogy_tab.COUNT',
4059 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
4060
4061 FND_LOG.String(
4062 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4063 MESSAGE => l_stmt_no ||': END IF;--IF s1genealogy_tab.EXISTS(Z) THEN',
4064 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
4065
4066 END IF;
4067
4068 END IF;--IF s1genealogy_tab.EXISTS(Z) THEN
4069 ----dbms_output.put_line(42);--
4070 ELSE--IF s1genealogy_tab.COUNT <> 0 THEN
4071 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
4072 l_stmt_no := 512;
4073 FND_LOG.String(
4074 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4075 MESSAGE => l_stmt_no ||': ELSE--IF s1genealogy_tab.COUNT <> 0 THEN',
4076 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
4077 FND_LOG.String(
4078 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4079 MESSAGE => l_stmt_no ||': EXIT; ',
4080 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
4081
4082 END IF;
4083
4084 ----dbms_output.put_line(43);
4085 EXIT;
4086 END IF;--IF s1genealogy_tab.COUNT <> 0 THEN
4087 ----dbms_output.put_line(44);
4088 END LOOP;--c_main
4089 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
4090 l_stmt_no := 513;
4091 FND_LOG.String(
4092 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4093 MESSAGE => l_stmt_no ||': END LOOP;--c_main',
4094 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
4095
4096 END IF;
4097 ----dbms_output.put_line(45);
4098 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
4099 l_stmt_no := 520;
4100 FND_LOG.String(
4101 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4102 MESSAGE => l_stmt_no ||': EXECUTE IMMEDIATE g_gen_sql BULK COLLECT INTO s3genealogy_tab ',
4103 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
4104
4105 FND_LOG.String(
4106 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4107 MESSAGE => l_stmt_no ||': sgenealogy_tab(j).serial_number = '|| sgenealogy_tab(j).serial_number ,
4108 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
4109
4110 FND_LOG.String(
4111 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4112 MESSAGE => l_stmt_no ||': sgenealogy_tab(j).inventory_item_id= '|| sgenealogy_tab(j).inventory_item_id,
4113 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
4114
4115 END IF;
4116
4117 EXECUTE IMMEDIATE g_gen_sql
4118 BULK COLLECT INTO s3genealogy_tab
4119 USING p_organization_id ,
4120 sgenealogy_tab(j).serial_number ,
4121 sgenealogy_tab(j).inventory_item_id ,
4122 p_organization_id;
4123
4124 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
4125 l_stmt_no := 530;
4126 dbg_s3genealogy_tab := s3genealogy_tab;
4127 IF dbg_s3genealogy_tab.COUNT > 0 THEN
4128 FOR i6 IN dbg_s3genealogy_tab.FIRST .. dbg_s3genealogy_tab.LAST LOOP
4129 FND_LOG.String(
4130 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4131 MESSAGE => l_stmt_no ||': dbg_s3genealogy_tab(i).item_nbr= '|| dbg_s3genealogy_tab(i6).item_nbr,
4132 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
4133
4134 FND_LOG.String(
4135 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4136 MESSAGE => l_stmt_no ||': dbg_s3genealogy_tab(i).serial_number= '|| dbg_s3genealogy_tab(i6).serial_number,
4137 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
4138
4139 FND_LOG.String(
4140 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4141 MESSAGE => l_stmt_no ||': dbg_s3genealogy_tab(i).job_name= '|| dbg_s3genealogy_tab(i6).job_name,
4142 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
4143
4144 END LOOP;
4145 END IF;
4146 END IF;
4147
4148 IF s3genealogy_tab.COUNT <> 0 THEN
4149 ----dbms_output.put_line(90);
4150 Z := Y + 1;
4151 Y := Z;
4152 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
4153 l_stmt_no := 540;
4154 FND_LOG.String(
4155 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4156 MESSAGE => l_stmt_no ||': Y= '|| Y,
4157 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
4158
4159 FND_LOG.String(
4160 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4161 MESSAGE => l_stmt_no ||': Z= '|| Z,
4162 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
4163
4164 END IF;
4165
4166 IF s3genealogy_tab.EXISTS(Z) THEN
4167 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
4168 l_stmt_no := 550;
4169 FND_LOG.String(
4170 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4171 MESSAGE => l_stmt_no ||': IF s3genealogy_tab.EXISTS(Z)',
4172 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
4173 FND_LOG.String(
4174 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4175 MESSAGE => l_stmt_no ||': FOR l IN Z..s3genealogy_tab.COUNT',
4176 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy.');
4177
4178 END IF;
4179
4180 FOR l IN Z..s3genealogy_tab.COUNT
4181 LOOP
4182 ----dbms_output.put_line(s3genealogy_tab(l).item_nbr);
4183 ----dbms_output.put_line(s3genealogy_tab(l).serial_number);
4184 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
4185 l_stmt_no := 560;
4186 FND_LOG.String(
4187 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4188 MESSAGE => l_stmt_no ||': s3genealogy_tab(l).item_nbr= '|| s3genealogy_tab(l).item_nbr,
4189 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
4190
4191 FND_LOG.String(
4192 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4193 MESSAGE => l_stmt_no ||': s3genealogy_tab(l).serial_number= '|| s3genealogy_tab(l).serial_number,
4194 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
4195
4196 END IF;
4197
4198 genealogy_rec.item_nbr := s3genealogy_tab(l).item_nbr;
4199 genealogy_rec.primary_uom_code := s3genealogy_tab(l).primary_uom_code;
4200 genealogy_rec.lot_number := s3genealogy_tab(l).lot_number;
4201 genealogy_rec.serial_number := s3genealogy_tab(l).serial_number;
4202 genealogy_rec.organization_id := genealogy_tab(i).organization_id;
4203 genealogy_rec.inventory_item_id := s3genealogy_tab(l).inventory_item_id;
4204 genealogy_rec.job_name := s3genealogy_tab(l).job_name;
4205 genealogy_rec.retest_date := s3genealogy_tab(l).retest_date;
4206 genealogy_rec.expiration_date := s3genealogy_tab(l).expiration_date;
4207 genealogy_rec.best_by_date := s3genealogy_tab(l).best_by_date;
4208 genealogy_rec.origination_date := s3genealogy_tab(l).origination_date;
4209 genealogy_rec.organization_code := s3genealogy_tab(l).organization_code;
4210 genealogy_rec.lot_control_code := s3genealogy_tab(l).lot_control_code;
4211 genealogy_rec.serial_number_control_code := s3genealogy_tab(l).serial_number_control_code;
4212 genealogy_rec.cross_reference := s3genealogy_tab(l).cross_reference;
4213 genealogy_rec.serial_type := s3genealogy_tab(l).serial_type;
4214 genealogy_rec.parent_lot_number := sgenealogy_tab(j).lot_number;
4215 genealogy_rec.parent_serial_number := sgenealogy_tab(j).serial_number ;
4216 genealogy_rec.parent_inventory_item_id := sgenealogy_tab(j).inventory_item_id;
4217
4218 IF NOT occ.EXISTS(genealogy_rec.item_nbr || '.' ||
4219 genealogy_rec.lot_number || '.' ||
4220 genealogy_rec.serial_number || '.' ||
4221 TO_CHAR(genealogy_rec.organization_id) || '.' ||
4222 TO_CHAR(genealogy_rec.inventory_item_id) || '.' ||
4223 genealogy_rec.parent_lot_number || '.' ||
4224 genealogy_rec.parent_serial_number || '.' ||
4225 TO_CHAR(genealogy_rec.parent_inventory_item_id)) THEN
4226 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
4227 FND_LOG.String(
4228 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4229 MESSAGE => l_stmt_no ||': IF NOT occ.EXISTS(genealogy_rec.item_nbr',
4230 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
4231
4232 END IF;
4233 occ(genealogy_rec.item_nbr || '.' ||
4234 genealogy_rec.lot_number || '.' ||
4235 genealogy_rec.serial_number || '.' ||
4236 TO_CHAR(genealogy_rec.organization_id) || '.' ||
4237 TO_CHAR(genealogy_rec.inventory_item_id) || '.' ||
4238 genealogy_rec.parent_lot_number || '.' ||
4239 genealogy_rec.parent_serial_number || '.' ||
4240 TO_CHAR(genealogy_rec.parent_inventory_item_id)) := j;
4241 genealogy_rec.rlevel := v_level - 1;
4242 genealogy_rec.parent_rlevel := v_level - 2;
4243 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
4244 l_stmt_no := 570;
4245 FND_LOG.String(
4246 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4247 MESSAGE => l_stmt_no ||': PIPE ROW(genealogy_rec)',
4248 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
4249 FND_LOG.String(
4250 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4251 MESSAGE => l_stmt_no ||': GOTO REPEAT_LOOP',
4252 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
4253 END IF;
4254 PIPE ROW(genealogy_rec);
4255 GOTO REPEAT_LOOP;
4256 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
4257 l_stmt_no := 571;
4258 FND_LOG.String(
4259 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4260 MESSAGE => l_stmt_no ||': PIPE ROW(genealogy_rec); ',
4261 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
4262 FND_LOG.String(
4263 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4264 MESSAGE => l_stmt_no ||': GOTO REPEAT_LOOP;',
4265 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
4266
4267 FND_LOG.String(
4268 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4269 MESSAGE => l_stmt_no ||': END IF;--NOT occ.EXISTS(genealogy_rec.item_nbr || "." ||',
4270 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
4271
4272 END IF;
4273 END IF;--NOT occ.EXISTS(genealogy_rec.item_nbr || '.' ||
4274 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
4275 l_stmt_no := 572;
4276 FND_LOG.String(
4277 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4278 MESSAGE => l_stmt_no ||': END LOOP;--FOR l IN Z..s3genealogy_tab.COUNT',
4279 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
4280
4281 END IF;
4282
4283 END LOOP;--FOR l IN Z..s3genealogy_tab.COUNT
4284 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
4285 l_stmt_no := 573;
4286 FND_LOG.String(
4287 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4288 MESSAGE => l_stmt_no ||': END IF;--s3genealogy_tab.EXISTS(Z) ',
4289 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
4290
4291 END IF;
4292
4293 END IF;--s3genealogy_tab.EXISTS(Z)
4294 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
4295 l_stmt_no := 574;
4296 FND_LOG.String(
4297 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4298 MESSAGE => l_stmt_no ||': END IF;--s3genealogy_tab.COUNT <> 0 ',
4299 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
4300
4301 END IF;
4302
4303 END IF;--s3genealogy_tab.COUNT <> 0
4304 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
4305 l_stmt_no := 575;
4306 FND_LOG.String(
4307 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4308 MESSAGE => l_stmt_no ||': END LOOP;--FOR j IN 1..sgenealogy_tab.COUNT',
4309 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
4310
4311 END IF;
4312
4313 END LOOP;--FOR j IN 1..sgenealogy_tab.COUNT
4314 ----dbms_output.put_line(46);
4315 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
4316 l_stmt_no := 576;
4317 FND_LOG.String(
4318 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4319 MESSAGE => l_stmt_no ||': END IF;--sgenealogy_tab.COUNT <> 0',
4320 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
4321
4322 END IF;
4323
4324 END IF;--sgenealogy_tab.COUNT <> 0
4325 ----dbms_output.put_line(47);
4326 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
4327 l_stmt_no := 577;
4328 FND_LOG.String(
4329 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4330 MESSAGE => l_stmt_no ||': END IF;--v_wms_enabled = 0 AND v_make_items = "Y"',
4331 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
4332
4333 END IF;
4334
4335 END IF;--v_wms_enabled = 0 AND v_make_items = 'Y'
4336 ----dbms_output.put_line(48);
4337 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
4338 l_stmt_no := 578;
4339 FND_LOG.String(
4340 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
4341 MESSAGE => l_stmt_no ||': END LOOP;--I IN 1..genealogy_tab.COUNT',
4342 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
4343
4344 END IF;
4345 END LOOP;--I IN 1..genealogy_tab.COUNT
4346
4347 << WMS >>
4348
4349 IF v_wms_enabled = 1 THEN
4350 ----dbms_output.put_line(101);
4351 OPEN c_top(g_object_id);
4352 ----dbms_output.put_line(102);
4353 FETCH c_top
4354 BULK COLLECT INTO v_top;
4355 CLOSE c_top;
4356
4357 v_level := 0;
4358
4359 FOR i IN 1..v_top.COUNT
4360 LOOP
4361 ----dbms_output.put_line(103);
4362 IF v_top(i).rlevel = 1 THEN
4363 ----dbms_output.put_line(104);
4364 BEGIN
4365 SELECT wnd.delivery_id
4366 INTO v_delivery_id
4367 FROM mtl_transaction_details_v mtd ,
4368 mtl_material_transactions mmt ,
4369 wsh_new_deliveries wnd
4370 WHERE mtd.object_id = v_top(i).object_id
4371 AND mmt.transaction_id = mtd.transaction_id + 0
4372 AND wnd.delivery_id = mmt.trx_source_delivery_id + 0;
4373 IF l_debug_on THEN
4374 WSH_DEBUG_SV.log(l_module_name,'DELIVERY ID', v_delivery_id);
4375 END IF;
4376 ----dbms_output.put_line(v_delivery_id);
4377 EXCEPTION
4378 WHEN no_data_found THEN
4379 v_delivery_id := 0;
4380 IF l_debug_on THEN
4381 WSH_DEBUG_SV.logmsg(l_module_name,'No Data Found error has occured. Oracle error message is '|| substr(SQLERRM, 1, 200), WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4382 END IF;
4383 END;
4384 v_top(i).delivery_id := v_delivery_id;
4385 ELSE
4386 ----dbms_output.put_line(105);
4387 BEGIN
4388 SELECT wnd.delivery_id
4389 INTO v_delivery_id1
4390 FROM mtl_transaction_details_v mtd ,
4391 mtl_material_transactions mmt ,
4392 wsh_new_deliveries wnd
4393 WHERE mtd.object_id = v_top(i).object_id
4394 AND mmt.transaction_id = mtd.transaction_id + 0
4395 AND wnd.delivery_id = mmt.trx_source_delivery_id + 0;
4396 IF l_debug_on THEN
4397 WSH_DEBUG_SV.log(l_module_name,'DELIVERY ID1', v_delivery_id);
4398 END IF;
4399 ----dbms_output.put_line(v_delivery_id1);
4400 EXCEPTION
4401 WHEN no_data_found THEN
4402 v_delivery_id1 := 0;
4403 IF l_debug_on THEN
4404 WSH_DEBUG_SV.logmsg(l_module_name,'No Data Found error has occured. Oracle error message is '|| substr(SQLERRM, 1, 200), WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4405 END IF;
4406 END;
4407 IF v_delivery_id1 <> 0 THEN
4408 ----dbms_output.put_line(106);
4409 v_top(i).delivery_id := v_delivery_id1;
4410 v_delivery_id := v_delivery_id1;
4411 ELSE
4412 ----dbms_output.put_line(107);
4413 v_top(i).delivery_id := v_delivery_id;
4414 END IF;
4415 END IF;
4416 IF v_top(i).rlevel > 1 THEN
4417 ----dbms_output.put_line(108);
4418 BEGIN
4419 SELECT serial_number,
4420 NVL(parent_serial_number, serial_number || TO_CHAR(i))
4421 INTO v_top(i).serial_number,
4422 v_top(i).parent_serial_number
4423 FROM mtl_serial_numbers
4424 WHERE gen_object_id = v_top(i).object_id
4425 AND (serial_number <> parent_serial_number
4426 OR parent_serial_number IS NULL);
4427 ----dbms_output.put_line(109);
4428 IF v_top(i).parent_serial_number IS NOT NULL THEN
4429 v_sem(v_top(i).serial_number) := v_sem(v_top(i).parent_serial_number) + 1;
4430 ----dbms_output.put_line(110);
4431 ELSE
4432 v_sem(v_top(i).serial_number) := v_top(i).rlevel;
4433 ----dbms_output.put_line(111);
4434 END IF;
4435 EXCEPTION
4436 WHEN no_data_found THEN
4437 v_top(i).serial_number := NULL;
4438 v_top(i).parent_serial_number := NULL;
4439 END;
4440 ELSIF v_top(i).rlevel = 1 THEN
4441 ----dbms_output.put_line(112);
4442 BEGIN
4443 SELECT serial_number,
4444 serial_number
4445 INTO v_top(i).serial_number,
4446 v_top(i).parent_serial_number
4447 FROM mtl_serial_numbers
4448 WHERE gen_object_id = v_top(i).object_id;
4449
4450 v_sem(v_top(i).serial_number) := 1;
4451 EXCEPTION
4452 WHEN no_data_found THEN
4453 v_top(i).serial_number := NULL;
4454 v_top(i).parent_serial_number := NULL;
4455 END;
4456 END IF;
4457 IF v_top(i).serial_number IS NOT NULL THEN
4458 v_ser(v_top(i).serial_number) := v_top(i).parent_serial_number;
4459 END IF;
4460 END LOOP;
4461 FOR i IN 1..v_top.COUNT
4462 LOOP
4463 IF v_top(i).rlevel = 1 THEN
4464 v_sem(v_top(i).serial_number) := 1;
4465 ELSIF v_sem.exists(v_top(i).parent_serial_number) THEN
4466 v_top(i).rlevel := v_sem(v_top(i).parent_serial_number) + 1;
4467 END IF;
4468 IF (v_top(i).delivery_id <> p_delivery_id OR v_top(i).delivery_id IS NULL)
4469 AND v_ser.EXISTS(v_top(i).serial_number) THEN
4470
4471 v_ser(v_top(i).serial_number) := NULL;
4472
4473 ELSIF v_top(i).serial_number IS NOT NULL THEN
4474 v_ser(v_top(i).serial_number) := v_top(i).parent_serial_number || '.' || TO_CHAR(v_top(i).rlevel);
4475 END IF;
4476 END LOOP;
4477 FOR i IN 1..v_top.COUNT
4478 LOOP
4479 FOR j IN c_sub(v_top(i).object_id)
4480 LOOP
4481 IF NVL(v_top(i).delivery_id, 0) = p_delivery_id
4482 AND v_ser.EXISTS(v_top(i).serial_number)
4483 AND v_ser(v_top(i).serial_number) IS NOT NULL THEN
4484 genealogy_rec.item_nbr := j.item_number;
4485 genealogy_rec.primary_uom_code := j.primary_uom_code;
4486 genealogy_rec.lot_number := j.lot_number;
4487 genealogy_rec.serial_number := j.serial_number;
4488 genealogy_rec.organization_id := j.organization_id;
4489 genealogy_rec.inventory_item_id := j.inventory_item_id;
4490 genealogy_rec.job_name := j.job_name;
4491 genealogy_rec.retest_date := j.retest_date;
4492 genealogy_rec.expiration_date := j.expiration_date;
4493 genealogy_rec.best_by_date := j.best_by_date;
4494 genealogy_rec.origination_date := j.origination_date;
4495 genealogy_rec.organization_code := j.organization_code;
4496 genealogy_rec.lot_control_code := j.lot_control_code;
4497 genealogy_rec.serial_number_control_code := j.serial_number_control_code;
4498 genealogy_rec.cross_reference := j.cross_reference;
4499 genealogy_rec.serial_type := j.serial_type;
4500 IF NOT occ.EXISTS(genealogy_rec.item_nbr || '.' || genealogy_rec.lot_number || '.' || genealogy_rec.serial_number || '.' || TO_CHAR(genealogy_rec.organization_id) || '.' || TO_CHAR(genealogy_rec.inventory_item_id)) THEN
4501 occ(genealogy_rec.item_nbr || '.' || genealogy_rec.lot_number || '.' || genealogy_rec.serial_number || '.' || TO_CHAR(genealogy_rec.organization_id) || '.' || TO_CHAR(genealogy_rec.inventory_item_id)) := 1;
4502 genealogy_rec.rlevel := v_top(i).rlevel;
4503 genealogy_rec.parent_rlevel := v_top(i).rlevel - 1;
4504 PIPE ROW(genealogy_rec);
4505 END IF;
4506 END IF;
4507 END LOOP;
4508 END LOOP;
4509 END IF;
4510 <<MAKE_ITEMS>>
4511 -- IF v_make_items = 'N' THEN
4512 --
4513 -- FOR r_make_items IN c_nonmake_items
4514 -- LOOP
4515 -- genealogy_rec.item_nbr := r_make_items.item_number;
4516 -- genealogy_rec.primary_uom_code := r_make_items.primary_uom_code;
4517 -- genealogy_rec.lot_number := r_make_items.lot_number;
4518 -- genealogy_rec.serial_number := NULL;
4519 -- genealogy_rec.organization_id := r_make_items.organization_id;
4520 -- genealogy_rec.inventory_item_id := r_make_items.inventory_item_id;
4521 -- genealogy_rec.job_name := NULL;
4522 -- genealogy_rec.retest_date := r_make_items.retest_date;
4523 -- genealogy_rec.expiration_date := r_make_items.expiration_date;
4524 -- genealogy_rec.best_by_date := r_make_items.best_by_date;
4525 -- genealogy_rec.origination_date := r_make_items.origination_date;
4526 -- genealogy_rec.organization_code := r_make_items.organization_code;
4527 -- genealogy_rec.lot_control_code := r_make_items.lot_control_code;
4528 -- genealogy_rec.serial_number_control_code := r_make_items.serial_number_control_code;
4529 -- genealogy_rec.cross_reference := r_make_items.cross_reference;
4530 -- genealogy_rec.serial_type := r_make_items.serial_type;
4531 -- IF r_make_items.from_serial_number IS NOT NULL AND r_make_items.to_serial_number IS NOT NULL THEN
4532 -- v_serial_number := r_make_items.from_serial_number;
4533 -- WHILE 1 =1
4534 -- LOOP
4535 -- IF v_serial_number IS NULL THEN
4536 -- EXIT;
4537 -- END IF;
4538 -- genealogy_rec.serial_number := v_serial_number;
4539 -- IF NOT occ.EXISTS(genealogy_rec.item_nbr ||'.'|| genealogy_rec.lot_number ||'.'|| genealogy_rec.serial_number ||'.'|| TO_CHAR(genealogy_rec.organization_id) ||'.'|| TO_CHAR(genealogy_rec.inventory_item_id)) THEN
4540 -- occ(genealogy_rec.item_nbr ||'.'|| genealogy_rec.lot_number ||'.'|| genealogy_rec.serial_number ||'.'|| TO_CHAR(genealogy_rec.organization_id) ||'.'|| TO_CHAR(genealogy_rec.inventory_item_id)) := 1;
4541 -- genealogy_rec.rlevel := 1;
4542 -- genealogy_rec.parent_rlevel := 0;
4543 -- PIPE ROW(genealogy_rec);
4544 -- END IF;
4545 -- -- ----dbms_output.put_line(r_make_items.from_serial_number||'.'||r_make_items.to_serial_number);
4546 -- IF r_make_items.from_serial_number = r_make_items.to_serial_number THEN
4547 -- EXIT;
4548 -- END IF;
4549 -- IF genealogy_rec.serial_number >= r_make_items.to_serial_number THEN
4550 -- EXIT;
4551 -- END IF;
4552 -- v_serial_number := get_next_serial(v_serial_number);
4553 -- END LOOP;
4554 -- END IF;
4555 -- END LOOP;
4556 -- END IF;
4557 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
4558 l_stmt_no := 590;
4559 FND_LOG.String(
4560 LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
4561 MESSAGE => l_stmt_no ||': Exiting get_genealogy',
4562 MODULE => 'wsh.plsql.wsh_opsm_asn_item_genealogy.get_genealogy');
4563 END IF;
4564 RETURN;
4565 END;
4566 ---------------------------------------------------------------------------------------
4567 --
4568 -- Function: get_digits
4569 -- Parameters: p_char - Character i.e FROM Serial and TO Serial Numbers
4570
4571 -- Description: It converts From serial numbers and To Serial Numbers to Alphabets
4572 --
4573 ---------------------------------------------------------------------------------------
4574 FUNCTION get_digits(p_char IN VARCHAR2)
4575 RETURN NUMBER
4576 IS
4577 l_char NUMBER;
4578 BEGIN
4579 BEGIN
4580 l_char := to_number(p_char);
4581 RETURN (l_char);
4582 EXCEPTION
4583 WHEN VALUE_ERROR THEN
4584 NULL;
4585 WHEN INVALID_NUMBER THEN
4586 NULL;
4587 END;
4588 RETURN TRANSLATE(p_char, g_alphabets, 0);
4589 EXCEPTION
4590 WHEN value_error THEN
4591 RETURN NULL;
4592 END;
4593 ---------------------------------------------------------------------------------------
4594 --
4595 -- Function: get_string
4596 -- Parameters: p_char - Character i.e FROM Serial and TO Serial Numbers
4597
4598 -- Description: It converts From serial numbers and To Serial Numbers to Alphabets
4599 --
4600 ---------------------------------------------------------------------------------------
4601 FUNCTION get_string(p_char IN VARCHAR2)
4602 RETURN VARCHAR2
4603 IS
4604 l_char NUMBER;
4605 BEGIN
4606 BEGIN
4607 l_char := to_number(p_char);
4608 RETURN ('A');
4609 EXCEPTION
4610 WHEN VALUE_ERROR THEN
4611 NULL;
4612 WHEN INVALID_NUMBER THEN
4613 NULL;
4614 END;
4615 RETURN SUBSTR(p_char, 1, instr(p_char, TO_CHAR(wsh_opsm_asn_item_genealogy.get_digits(p_char)), 1) - 1);
4616 END;
4617 ---------------------------------------------------------------------------------------
4618 --
4619 -- Function: check_for_itemgenealogy
4620 -- Parameters: p_delivery_detail_id- It takes delivery_detail Id has input
4621 -- p_organization_id -It takes organization_id has input
4622 -- Description: It checks if Ittem Genealogy exsits for the shiiped item
4623 -- If not it returns 'N' otherwise returns 'Y'
4624 --
4625 ---------------------------------------------------------------------------------------
4626 FUNCTION check_for_itemgenealogy(p_delivery_detail_id IN NUMBER ,
4627 p_organization_id IN NUMBER)
4628 RETURN VARCHAR2
4629 IS
4630 r_main_sql c_main_sql%ROWTYPE;
4631 BEGIN
4632 OPEN c_main_sql(p_delivery_detail_id, p_organization_id);
4633 FETCH c_main_sql INTO r_main_sql;
4634 IF c_main_sql%NOTFOUND THEN
4635 CLOSE c_main_sql;
4636 RETURN 'N';
4637 END IF;
4638 CLOSE c_main_sql;
4639 RETURN 'Y';
4640 END;
4641 ---------------------------------------------------------------------------------------
4642 --
4643 -- Function: get_next_serial
4644 -- Parameters: p_char - Character i.e FROM Serial and TO Serial Numbers
4645
4646 -- Description: It generates the next Serial Number. Eg: if Serial Number
4647 -- passed is SE001 it generates SE0002
4648 --
4649 ---------------------------------------------------------------------------------------
4650 FUNCTION get_next_serial(p_serial_number IN VARCHAR2)
4651 RETURN VARCHAR2
4652 IS
4653 v_serial_number VARCHAR2(32000);
4654 BEGIN
4655 FOR j IN 1..length(p_serial_number)
4656 LOOP
4657 BEGIN
4658 v_serial_number := SUBSTR(p_serial_number, 1, LENGTH(p_serial_number) - j) || lpad(TO_CHAR(to_number(SUBSTR(p_serial_number, (- 1 * j), j)) + 1), j, '0');
4659 EXCEPTION
4660 WHEN value_error THEN
4661 EXIT;
4662 WHEN invalid_number THEN
4663 EXIT;
4664 END;
4665 END LOOP;
4666 RETURN (v_serial_number);
4667 END;
4668 END wsh_opsm_asn_item_genealogy;