[Home] [Help]
PACKAGE BODY: APPS.INV_GENEALOGY_REPORT_GEN
Source
1 PACKAGE BODY inv_genealogy_report_gen AS
2 /* $Header: INVLTGNB.pls 120.11 2006/10/03 15:29:46 lgao noship $ */
3 --
4 -- File : INVLTGNB.pls
5 -- Content : inv_genealogy_report_gen Body
6 -- Description : generate XML file for genealogy report
7 -- Notes :
8 -- Modified : 07/18/05 lgao created orginal file
9 --
10 g_pkg_name CONSTANT VARCHAR2(30) := 'inv_genealogy_report_gen';
11 g_debug NUMBER;
12 g_inventory_item_id NUMBER;
13 g_organization_id NUMBER;
14 g_organization_desc VARCHAR2(240);
15 g_tracking_quantity_ind mtl_system_items_kfv.tracking_quantity_ind%type; --Bug#5436402
16 g_wip_entity_id NUMBER;
17 g_wip_entity_type NUMBER;
18 g_current_org_id NUMBER;
19 g_include_txns VARCHAR2(1);
20 g_include_move_txns VARCHAR2(1);
21 g_include_pending_txns VARCHAR2(1);
22 g_include_grd_sts VARCHAR2(1);
23 g_quality_control VARCHAR2(1);
24 g_genealogy_type NUMBER;
25 g_genealogy_type_code VARCHAR2(100);
26
27 TYPE item_array IS TABLE OF NUMBER
28 INDEX BY BINARY_INTEGER;
29
30 Procedure XML_write
31 ( column_name IN VARCHAR2
32 , column_value IN VARCHAR2
33 ) ;
34
35 procedure write_genealogy_report
36 ( p_object_id IN NUMBER
37 , p_object_type IN NUMBER
38 , p_object_id2 IN NUMBER
39 , p_object_type2 IN NUMBER
40 , p_level IN NUMBER
41 );
42 PROCEDURE write_query_input
43 (p_organization_code IN VARCHAR2
44 ,p_item_no IN VARCHAR2 DEFAULT null
45 ,p_lot_number IN VARCHAR2 DEFAULT null
46 ,p_serial_number IN VARCHAR2 DEFAULT null
47 ,p_wip_entity_name IN VARCHAR2 DEFAULT null
48 ,p_include_txns IN VARCHAR2 DEFAULT 'N'
49 ,p_include_move_txns IN VARCHAR2 DEFAULT 'N'
50 ,p_include_pending_txns IN VARCHAR2 DEFAULT 'N'
51 ,p_include_grd_sts IN VARCHAR2 DEFAULT 'N'
52 ,p_quality_control IN VARCHAR2 DEFAULT 'N'
53 ,p_genealogy_type IN NUMBER DEFAULT 1
54 );
55 Procedure write_header_info
56 ( p_object_id IN NUMBER
57 , p_object_type IN NUMBER
58 , p_object_id2 IN NUMBER
59 , p_object_type2 IN NUMBER
60 , p_item_no IN VARCHAR2
61 , p_lot_number IN VARCHAR2
62 , p_serial_number IN VARCHAR2
63 , p_wip_entity_name IN VARCHAR2
64 , p_level IN NUMBER -- 1 query item, 2 component item
65 );
66 Procedure write_item_info
67 ( p_item_info_rec IN OUT NOCOPY inv_genealogy_report_gen.item_info_rec_type
68 );
69
70 Procedure write_lot_info
71 ( p_lot_attributes_rec IN OUT NOCOPY inv_genealogy_report_gen.lot_attributes_rec_type
72 );
73
74 Procedure write_serial_info
75 ( p_serial_attributes_rec IN OUT NOCOPY inv_genealogy_report_gen.serial_attributes_rec_type
76 );
77
78 Procedure write_work_order_info
79 ( p_work_order_header_rec IN OUT NOCOPY inv_genealogy_report_gen.work_order_header_rec_type
80 , p_work_order_dtl_rec IN OUT NOCOPY inv_genealogy_report_gen.work_order_dtl_rec_type
81 , p_item_info_rec IN OUT NOCOPY inv_genealogy_report_gen.item_info_rec_type
82 );
83
84 Procedure write_material_txn_info
85 ( p_material_txn_rec IN OUT NOCOPY inv_genealogy_report_gen.material_txn_rec_type
86 );
87
88 Procedure write_pending_txn_info
89 ( p_pending_txn_rec IN OUT NOCOPY inv_genealogy_report_gen.pending_txn_rec_type
90 );
91
92 Procedure write_product_info
93 ( p_product_rec IN OUT NOCOPY inv_genealogy_report_gen.product_rec_type
94 );
95
96 Procedure write_component_info
97 ( p_component_rec IN OUT NOCOPY inv_genealogy_report_gen.component_rec_type
98 );
99
100 Procedure write_quality_collections_info
101 ( p_quality_collections_rec IN OUT NOCOPY inv_genealogy_report_gen.quality_collections_rec_type
102 );
103
104 Procedure write_quality_samples_info
105 ( p_quality_samples_rec IN OUT NOCOPY inv_genealogy_report_gen.quality_samples_rec_type
106 );
107
108 Procedure write_lotbased_wip_txn_info
109 ( p_lotbased_wip_txn_rec IN OUT NOCOPY inv_genealogy_report_gen.lotbased_wip_txn_rec_type
110 );
111
112 Procedure write_move_txn_info
113 ( p_move_txn_rec IN OUT NOCOPY inv_genealogy_report_gen.move_txn_rec_type
114 );
115
116 Procedure write_grade_status_info
117 ( p_grade_status_rec IN OUT NOCOPY inv_genealogy_report_gen.grade_status_rec_type
118 );
119
120 procedure get_all_children
121 ( p_object_id IN NUMBER
122 , p_object_type IN NUMBER
123 , p_object_id2 IN NUMBER
124 , p_object_type2 IN NUMBER
125 );
126 procedure get_one_level_child
127 ( p_object_id IN NUMBER
128 , p_object_type IN NUMBER
129 , p_object_id2 IN NUMBER
130 , p_object_type2 IN NUMBER
131 , p_level IN NUMBER -- 0, main level
132 ) ;
133 procedure write_children_reports
134 ( p_object_id IN NUMBER
135 , p_object_type IN NUMBER
136 , p_object_id2 IN NUMBER
137 , p_object_type2 IN NUMBER
138 , p_level IN NUMBER -- 0, main level
139 ) ;
140
141 procedure get_all_parents
142 ( p_object_id IN NUMBER
143 , p_object_type IN NUMBER
144 , p_object_id2 IN NUMBER
145 , p_object_type2 IN NUMBER
146 );
147
148 procedure write_parent_reports
149 ( p_object_id IN NUMBER
150 , p_object_type IN NUMBER
151 , p_object_id2 IN NUMBER
152 , p_object_type2 IN NUMBER
153 , p_level IN NUMBER -- 0, main level
154 );
155
156 Procedure Write_tree_node
157 (p_object_id IN NUMBER
158 ,p_object_type IN NUMBER
159 ,p_object_id2 IN NUMBER
160 ,p_object_type2 IN NUMBER
161 ,p_level IN NUMBER -- 0, main level
162 ,p_child_parent IN NUMBER -- 1 child, 2 parent
163 );
164
165 procedure get_one_level_parent
166 ( p_parent_object_id IN NUMBER
167 , p_parent_object_type IN NUMBER
168 , p_parent_object_id2 IN NUMBER
169 , p_parent_object_type2 IN NUMBER
170 , p_level IN NUMBER -- 0, main level
171 ) ;
172
173 Procedure write_group_begin
174 (column_name IN VARCHAR2
175 ) ;
176 Procedure write_group_end
177 (column_name IN VARCHAR2
178 );
179
180
181 --Procedures for logging messages
182 PROCEDURE debug(p_message VARCHAR2) IS
183 l_module VARCHAR2(255);
184 BEGIN
185 --l_module := 'inv.plsql.' || g_pkg_name || '.' || p_api_name || '.' || p_label;
186 inv_log_util.trace(p_message, g_pkg_name, 9);
187 gmi_reservation_util.println(l_module ||' '|| p_message);
188 END debug;
189
190
191 /*
192 p_organization_code is required
193 p_item_no is required
194 others are optional
195 */
196 PROCEDURE genealogy_report
197 (
198 errbuf OUT NOCOPY VARCHAR2
199 ,retcode OUT NOCOPY VARCHAR2
200 ,p_organization_code IN VARCHAR2
201 ,p_item_no IN VARCHAR2 DEFAULT null
202 ,p_lot_number IN VARCHAR2 DEFAULT null
203 ,p_serial_number IN VARCHAR2 DEFAULT null
204 ,p_wip_entity_name IN VARCHAR2 DEFAULT null
205 ,p_include_txns IN VARCHAR2 DEFAULT 'Y'
206 ,p_include_move_txns IN VARCHAR2 DEFAULT 'Y'
207 ,p_include_pending_txns IN VARCHAR2 DEFAULT 'Y'
208 ,p_include_grd_sts IN VARCHAR2 DEFAULT 'Y'
209 ,p_quality_control IN VARCHAR2 DEFAULT 'Y'
210 ,p_genealogy_type IN NUMBER DEFAULT 1
211 ) IS
212
213 type rc is ref cursor;
214 main_query rc;
215
216 l_main_query VARCHAR2(5000);
217 l_object_id NUMBER;
218 l_object_type NUMBER;
219 l_object_id2 NUMBER;
220 l_object_type2 NUMBER;
221 l_data VARCHAR2(30);
222 l_object_name VARCHAR2(200);
223 l_object_description VARCHAR2(720);
224 l_object_type_name VARCHAR2(240);
225 l_unit_number VARCHAR2(60);
226 l_org_code VARCHAR2(3);
227 l_expiration_date DATE;
228 l_inventory_item_id NUMBER;
229 l_object_number VARCHAR2(1200);
230 l_material_status VARCHAR2(30);
231 l_primary_uom VARCHAR2(3);
232 l_secondary_uom VARCHAR2(3);
233 l_start_quantity NUMBER;
234 l_status_type_disp VARCHAR2(2000);
235 l_assembly VARCHAR2(2000);
236 l_assembly_description VARCHAR2(2000);
237 l_datalength NUMBER;
238 l_wip_entity_id NUMBER;
239 l_operation_seq_num NUMBER;
240 l_intraoperation_step_type NUMBER;
241 l_wip_entity_type NUMBER;
242 l_wip_entity_name VARCHAR2(240);
243 l_step VARCHAR2(2000);
244 l_current_lot_number VARCHAR2(80);
245
246 --Bug#5436402 getting tracking_quantity_ind also
247 Cursor get_item_info is
248 Select inventory_item_id, tracking_quantity_ind
249 From mtl_system_items_kfv
250 Where concatenated_segments = p_item_no
251 and organization_id = g_organization_id;
252
253 Cursor get_wip_info is
254 Select wip_entity_id
255 , entity_type
256 From wip_entities
257 Where organization_id = g_organization_id
258 and wip_entity_name= p_wip_entity_name
259 ;
260
261 Begin
262 debug('Generate Genealogy Report ');
263 debug('p_organization_code '||p_organization_code);
264 debug('p_item_no '||p_item_no);
265 debug('p_lot_number '||p_lot_number);
266 debug('p_serial_number '||p_serial_number);
267 debug('p_wip_entity_name '||p_wip_entity_name);
268 write_group_begin('INVLTGEN'); -- the main group for the report
269
270 write_query_input
271 (p_organization_code => p_organization_code
272 ,p_item_no => p_item_no
273 ,p_lot_number => p_lot_number
274 ,p_serial_number => p_serial_number
275 ,p_wip_entity_name => p_wip_entity_name
276 ,p_include_txns => p_include_txns
277 ,p_include_move_txns => p_include_move_txns
278 ,p_include_pending_txns => p_include_pending_txns
279 ,p_include_grd_sts => p_include_grd_sts
280 ,p_quality_control => p_quality_control
281 ,p_genealogy_type => p_genealogy_type
282 );
283
284 -- get item info
285 --Bug#5436402 getting tracking_quantity_ind also
286 Open get_item_info;
287 Fetch get_item_info into g_inventory_item_id, g_tracking_quantity_ind;
288 Close get_item_info;
289
290 -- get wip info
291 if p_wip_entity_name is not null then
292 Open get_wip_info;
293 Fetch get_wip_info into g_wip_entity_id, g_wip_entity_type;
294 Close get_wip_info;
295 end if;
296
297 debug('item info, item_id '||g_inventory_item_id);
298 g_include_txns := p_include_txns;
299 g_include_move_txns := p_include_move_txns;
300 g_include_pending_txns := p_include_pending_txns;
301 g_include_grd_sts := p_include_grd_sts;
302 g_quality_control := p_quality_control;
303 g_genealogy_type := p_genealogy_type;
304
305 -- construct the cursor query_item based on the query input
306 /* we need a few cursors for the main query
307 1) only item and org
308 2) serial number is not null, generic query for lot_number and wip
309 3) only serial number
310 4) only wip name
311 5) lot + wip
312 */
313 if g_wip_entity_id is null then
314 g_wip_entity_id := -9999;
315 end if;
316
317 if p_lot_number is null
318 and p_serial_number is null
319 and p_wip_entity_name is null
320 then
321 l_main_query := ' Select gen_object_id object_id'
322 ||' , 1 object_type'
323 ||' , lot_number lot_number'
324 ||' From mtl_lot_numbers'
325 ||' Where inventory_item_id = '||g_inventory_item_id
326 ||' and organization_id = '||g_organization_id
327 ||' and lot_number not in (Select lot_number '
328 ||' from mtl_serial_numbers'
329 ||' where lot_number is not null) '
330 ||' Union all'
331 ||' Select gen_object_id object_id'
332 ||' , 5 object_type'
333 ||' , lot_number lot_number'
334 ||' From mtl_serial_numbers'
335 ||' Where inventory_item_id = '||g_inventory_item_id
336 ;
337 elsif p_serial_number is not null
338 and p_wip_entity_name is null then
339 l_main_query := ' Select gen_object_id object_id'
340 ||' , 2 object_type'
341 ||' , lot_number lot_number'
342 ||' From mtl_serial_numbers'
343 ||' Where inventory_item_id =' || g_inventory_item_id
344 ||' and serial_number = '||''''||p_serial_number||''''
345 ||' and decode(lot_number, null, '||''''||'%%%%%'||''''||', lot_number) '
346 ||' = decode('||''''||p_lot_number||''''||', null, '||''''||'%%%%%'||''''||','||''''||p_lot_number||''''||')'
347 ;
348 elsif p_lot_number is not null
349 and p_serial_number is null
350 and p_wip_entity_name is null
351 then
352 l_main_query := ' Select gen_object_id object_id'
353 ||' , 2 object_type'
354 ||' , lot_number lot_number'
355 ||' From mtl_serial_numbers'
356 ||' Where inventory_item_id = '||g_inventory_item_id
357 ||' and lot_number = '||''''||p_lot_number||''''
358 ||' Union all '
359 ||' Select gen_object_id object_id'
360 ||' , 1 object_type'
361 ||' , lot_number lot_number'
362 ||' From mtl_lot_numbers'
363 ||' Where inventory_item_id = '||g_inventory_item_id
364 ||' and organization_id = '||g_organization_id
365 ||' and lot_number = '||''''||p_lot_number||''''
366 ||' and lot_number not in (Select lot_number'
367 ||' From mtl_serial_numbers'
368 ||' Where lot_number is not null )'
369 ;
370 elsif p_wip_entity_name is not null
371 and p_lot_number is null
372 and p_serial_number is null
373 then
374 l_main_query := ' Select gen_object_id object_id'
375 ||' , 5 object_type'
376 ||' , null lot_number'
377 ||' From wip_entities'
378 ||' Where primary_item_id = '||g_inventory_item_id
379 ||' and organization_id = '||g_organization_id
380 ||' and wip_entity_name = '||''''||p_wip_entity_name||''''
381 ;
382 /*l_main_query := ' Select gen_object_id object_id'
383 ||' , 5 object_type'
384 ||' From wsm_discrete_jobs_lotattr_v'
385 ||' Where primary_item_id = '||g_inventory_item_id
386 ||' and organization_id = '||g_organization_id
387 ||' and wip_entity_name = '||p_wip_entity_name
388 ||' Union All'
389 ||' Select gen_object_id object_id'
390 ||' , 5 object_type'
391 ||' From mtl_mfg_genealogy_lotattr_v'
392 ||' Where primary_item_id = '||g_inventory_item_id
393 ||' and organization_id = '||g_organization_id
394 ||' and wip_entity_name = '||p_wip_entity_name
395 ||' Union All'
396 ;
397 */
398 elsif p_wip_entity_name is not null
399 and p_lot_number is not null
400 and p_serial_number is null
401 then
402 l_main_query := ' Select we.gen_object_id object_id'
403 ||' , 5 object_type'
404 ||' , mln.lot_number lot_number'
405 ||' From wip_entities we'
406 ||' , mtl_lot_numbers mln'
407 ||' Where we.primary_item_id = '||g_inventory_item_id
408 ||' and we.organization_id = '||g_organization_id
409 ||' and we.organization_id = mln.organization_id'
410 ||' and mln.inventory_item_id = we.primary_item_id'
411 ||' and mln.lot_number = '||''''||p_lot_number||''''
412 ||' and we.wip_entity_name = '||''''||p_wip_entity_name||''''
413 ;
414 elsif p_wip_entity_name is not null
415 and p_lot_number is null
416 and p_serial_number is not null
417 then
418 l_main_query := ' Select we.gen_object_id object_id'
419 ||' , 5 object_type'
420 ||' , lot_number lot_number'
421 ||' From mtl_serial_numbers msn '
422 ||' , wip_entities we '
423 ||' Where msn.serial_number = '||''''|| p_serial_number||''''
424 ||' and we.wip_entity_name = '||''''||p_wip_entity_name||''''
425 --||' and we.wip_entity_id = msn.wip_entity_id '
426 ||' and we.primary_item_id = msn.inventory_item_id '
427 ;
428 elsif p_wip_entity_name is not null
429 and p_lot_number is not null
430 and p_serial_number is not null
431 then
432 l_main_query := ' Select we.gen_object_id object_id'
433 ||' , 5 object_type'
434 ||' , lot_number lot_number'
435 ||' From mtl_serial_numbers msn '
436 ||' , wip_entities we '
437 ||' Where msn.serial_number = '||''''|| p_serial_number||''''
438 ||' and we.wip_entity_name = '||''''||p_wip_entity_name||''''
439 ||' and msn.lot_number = '||''''||p_lot_number||''''
440 ||' and we.primary_item_id = msn.inventory_item_id '
441 ;
442 end if;
443
444 debug('main query '||l_main_query);
445 -- do the loop for each tree node to get all the reports
446 Open main_query for l_main_query;
447 Loop
448 Fetch main_query into l_object_id, l_object_type, l_current_lot_number;
449 Exit when main_query %NOTFOUND;
450 debug('main query object_id '||l_object_id ||' object_type '||l_object_type);
451 if l_object_type = 2 and l_current_lot_number is not null then
452 Select gen_object_id
453 , 1
454 Into l_object_id2
455 , l_object_type2
456 From mtl_lot_numbers
457 Where lot_number = l_current_lot_number
458 and organization_id = g_organization_id
459 and inventory_item_id = g_inventory_item_id;
460 end if;
461 write_genealogy_report
462 ( p_object_id => l_object_id
463 , p_object_type => l_object_type
464 , p_object_id2 => l_object_id2
465 , p_object_type2 => l_object_type2
466 , p_level => 0
467 );
468
469 /* write the child or parent reports*/
470 if p_genealogy_type = 1 then
471 get_all_children
472 ( p_object_id => l_object_id
473 , p_object_type => l_object_type
474 , p_object_id2 => l_object_id2
475 , p_object_type2 => l_object_type2
476 );
477 else
478 get_all_parents
479 ( p_object_id => l_object_id
480 , p_object_type => l_object_type
481 , p_object_id2 => l_object_id2
482 , p_object_type2 => l_object_type2
483 );
484 end if;
485
486 --write_group_end('ITEM_INFO'); -- the item_info
487 end loop;
488 Close main_query;
489 write_group_end('INVLTGEN'); -- the main group for the report
490 End genealogy_report;
491
492 PROCEDURE write_query_input
493 (p_organization_code IN VARCHAR2
494 ,p_item_no IN VARCHAR2 DEFAULT null
495 ,p_lot_number IN VARCHAR2 DEFAULT null
496 ,p_serial_number IN VARCHAR2 DEFAULT null
497 ,p_wip_entity_name IN VARCHAR2 DEFAULT null
498 ,p_include_txns IN VARCHAR2 DEFAULT 'N'
499 ,p_include_move_txns IN VARCHAR2 DEFAULT 'N'
500 ,p_include_pending_txns IN VARCHAR2 DEFAULT 'N'
501 ,p_include_grd_sts IN VARCHAR2 DEFAULT 'N'
502 ,p_quality_control IN VARCHAR2 DEFAULT 'N'
503 ,p_genealogy_type IN NUMBER DEFAULT 1
504 ) IS
505 l_org_desc VARCHAR2(240);
506 Cursor get_organization is
507 SELECT mp.organization_id
508 , hou.NAME
509 FROM mtl_parameters mp
510 , hr_organization_units hou
511 WHERE mp.organization_code = p_organization_code
512 AND hou.organization_id = mp.organization_id;
513
514 Cursor get_genealogy_type is
515 Select meaning
516 From mfg_lookups
517 where lookup_type='INV_REPORT_GENEALOGY_TYPE'
518 and lookup_code = g_genealogy_type;
519 Begin
520 debug('write query input');
521 write_group_begin('Query_Input');
522 open get_organization;
523 Fetch get_organization into g_organization_id, g_organization_desc;
524 Close get_organization;
525
526 XML_write('org_code',p_organization_code);
527 XML_write('org_desc',g_organization_desc);
528 XML_write('item_no',p_item_no);
529 XML_write('lot_number',p_lot_number);
530 XML_write('serial_number',p_serial_number);
531 XML_write('job_batch',p_wip_entity_name);
532 XML_write('include_material_transactions',p_include_txns);
533 XML_write('include_move_transactions',p_include_move_txns);
534 XML_write('include_pending_transactions',p_include_pending_txns);
535 XML_write('include_grade_status',p_include_grd_sts);
536 XML_write('include_QC',p_quality_control);
537 XML_write('genealogy_type',p_genealogy_type);
538
539 /*Open get_genealogy_type;
540 Fetch get_genealogy_type into g_genealogy_type_code;
541 Close get_genealogy_type;
542
543 debug('write query input, catergory_code '||g_genealogy_type_code);
544 XML_write('report_catergory', g_genealogy_type_code);
545 */
546 if p_genealogy_type = 1 then
547 XML_write('report_catergory','Source');
548 else
549 XML_write('report_catergory','Where Used');
550 end if;
551 write_group_end('Query_Input');
552
553 End write_query_input;
554
555 Procedure write_header_info
556 ( p_object_id IN NUMBER
557 , p_object_type IN NUMBER
558 , p_object_id2 IN NUMBER
559 , p_object_type2 IN NUMBER
560 , p_item_no IN VARCHAR2
561 , p_lot_number IN VARCHAR2
562 , p_serial_number IN VARCHAR2
563 , p_wip_entity_name IN VARCHAR2
564 , p_level IN NUMBER -- 1 query item, 2 component item
565 ) IS
566 l_object_label VARCHAR2(1200);
567 Begin
568 debug('Write header info');
569 write_group_begin('Header_info');
570 if p_item_no is not null then
571 if p_level = 1 then
572 XML_write('item_assembly', 'Query Item: '||p_item_no);
573 else
574 XML_write('item_assembly', 'Component Item: '||p_item_no);
575 end if;
576 end if;
577 l_object_label := inv_object_genealogy.getobjectnumber
578 (p_object_id, p_object_type, p_object_id2, p_object_type2);
579 XML_write('object_label', l_object_label);
580 if p_lot_number is not null then
581 XML_write('lot', 'Lot: '||p_lot_number);
582 end if;
583 if p_serial_number is not null then
584 XML_write('serial', 'Serial: '||p_serial_number);
585 end if;
586 if p_wip_entity_name is not null then
587 XML_write('job_batch', 'Job/Batch: '||p_wip_entity_name);
588 end if;
589 XML_write('header2_item', 'Item: '||p_item_no);
590 write_group_end('Header_info');
591 End write_header_info;
592
593 procedure write_genealogy_report
594 ( p_object_id IN NUMBER
595 , p_object_type IN NUMBER
596 , p_object_id2 IN NUMBER
597 , p_object_type2 IN NUMBER
598 , p_level IN NUMBER
599 ) IS
600 l_object_id NUMBER;
601 l_object_type NUMBER;
602 l_object_id2 NUMBER;
603 l_object_type2 NUMBER;
604 l_data VARCHAR2(30);
605 l_object_name VARCHAR2(200);
606 l_object_description VARCHAR2(720);
607 l_object_type_name VARCHAR2(240);
608 l_unit_number VARCHAR2(60);
609 l_org_code VARCHAR2(3);
610 l_expiration_date DATE;
611 l_inventory_item_id NUMBER;
612 l_object_number VARCHAR2(1200);
613 l_material_status VARCHAR2(30);
614 l_primary_uom VARCHAR2(3);
615 l_secondary_uom VARCHAR2(3);
616 l_start_quantity NUMBER;
617 l_status_type_disp VARCHAR2(2000);
618 l_assembly VARCHAR2(2000);
619 l_assembly_description VARCHAR2(2000);
620 l_datalength NUMBER;
621 l_wip_entity_id NUMBER;
622 l_operation_seq_num NUMBER;
623 l_intraoperation_step_type NUMBER;
624 l_wip_entity_type NUMBER;
625 l_wip_entity_name VARCHAR2(240);
626 l_step VARCHAR2(2000);
627 l_current_lot_number VARCHAR2(80);
628 l_current_org_id NUMBER;
629 l_current_org_code VARCHAR2(5);
630 l_current_org_desc VARCHAR2(50);
631 l_allow_security VARCHAR2(1);
632 l_security Number;
633 x_return_status VARCHAR2(5);
634
635 l_item_info_rec inv_genealogy_report_gen.item_info_rec_type;
636 l_lot_attributes_rec inv_genealogy_report_gen.lot_attributes_rec_type;
637 l_serial_attributes_rec inv_genealogy_report_gen.serial_attributes_rec_type;
638 l_work_order_header_rec inv_genealogy_report_gen.work_order_header_rec_type;
639 l_work_order_dtl_rec inv_genealogy_report_gen.work_order_dtl_rec_type ;
640 l_material_txn_rec inv_genealogy_report_gen.material_txn_rec_type ;
641 l_pending_txn_rec inv_genealogy_report_gen.pending_txn_rec_type ;
642 l_product_rec inv_genealogy_report_gen.product_rec_type ;
643 l_component_rec inv_genealogy_report_gen.component_rec_type ;
644 l_quality_collections_rec inv_genealogy_report_gen.quality_collections_rec_type;
645 l_quality_samples_rec inv_genealogy_report_gen.quality_samples_rec_type;
646 l_move_txn_rec inv_genealogy_report_gen.move_txn_rec_type ;
647 l_lotbased_wip_txn_rec inv_genealogy_report_gen.lotbased_wip_txn_rec_type ;
648 l_grade_status_rec inv_genealogy_report_gen.grade_status_rec_type ;
649
650 Cursor get_wip IS
651 SELECT wip_entity_name
652 , entity_type
653 , organization_id
654 , wip_entity_id
655 , primary_item_id
656 FROM wip_entities
657 WHERE gen_object_id = l_object_id
658 ;
659 Cursor get_gme_security Is
660 Select 1
661 From gme_batch_header_vw
662 Where batch_id = l_wip_entity_id;
663 Begin
664 -- Sort out object information
665 debug('writing report for object_type '||p_object_type||' object_id '||p_object_id
666 ||'object_type2 '||p_object_type2|| 'object_id2 '||p_object_id2);
667 l_object_id := p_object_id;
668 l_object_type := p_object_type;
669 l_object_id2 := p_object_id2;
670 l_object_type2 := p_object_type2;
671 l_security := 1;
672
673 IF l_object_type = 1 THEN
674 SELECT mp.organization_code
675 , hou.NAME
676 , mln.organization_id
677 INTO l_current_org_code
678 , l_current_org_desc
679 , l_current_org_id
680 FROM mtl_parameters mp
681 , hr_organization_units hou
682 , mtl_lot_numbers mln
683 WHERE mln.gen_object_id = l_object_id
684 AND mp.organization_id = mln.organization_id
685 AND hou.organization_id = mln.organization_id;
686 ELSIF l_object_type = 2 THEN
687 SELECT mp.organization_code
688 , hou.NAME
689 , msn.current_organization_id
690 INTO l_current_org_code
691 , l_current_org_desc
692 , l_current_org_id
693 FROM mtl_parameters mp
694 , hr_organization_units hou
695 , mtl_serial_numbers msn
696 WHERE msn.gen_object_id = l_object_id
697 AND mp.organization_id = msn.current_organization_id
698 AND hou.organization_id = msn.current_organization_id;
699 END IF;
700
701 IF l_object_type = 2 THEN
702 inv_object_genealogy.getobjectinfo(
703 l_object_id
704 , l_object_type
705 , l_object_name
706 , l_object_description
707 , l_object_type_name
708 , l_expiration_date
709 , l_primary_uom
710 , l_inventory_item_id
711 , l_object_number
712 , l_material_status
713 , l_unit_number
714 , l_wip_entity_id
715 , l_operation_seq_num
716 , l_intraoperation_step_type
717 , l_current_lot_number
718 );
719 ELSIF l_object_type = 1 Then
720 inv_object_genealogy.getobjectinfo(
721 l_object_id
722 , l_object_type
723 , l_object_name
724 , l_object_description
725 , l_object_type_name
726 , l_expiration_date
727 , l_primary_uom
728 , l_inventory_item_id
729 , l_object_number
730 , l_material_status
731 , l_unit_number
732 );
733 END IF;
734
735 g_current_org_id := l_current_org_id;
736
737 l_item_info_rec.inventory_item_id := l_inventory_item_id;
738 l_item_info_rec.item_no := l_object_name;
739 l_item_info_rec.item_desc := l_object_description;
740 l_item_info_rec.primary_uom := l_primary_uom;
741 --l_item_info_rec.secondary_uom := l_secondary_uom;
742
743 l_lot_attributes_rec.expiration_date := l_expiration_date;
744 l_lot_attributes_rec.lot_number := l_current_lot_number;
745 if (l_object_type = 1 or (l_object_type = 2 and l_current_lot_number is not null)) then
746 l_lot_attributes_rec.object_id := l_object_id;
747 l_lot_attributes_rec.status := l_material_status;
748 if l_object_type = 1 then
749 l_lot_attributes_rec.lot_number := l_object_number;
750 else
751 l_lot_attributes_rec.lot_number := l_current_lot_number;
752 end if;
753 l_lot_attributes_rec.organization_id := l_current_org_id ;
754 l_lot_attributes_rec.org_code := l_current_org_code ;
755 l_lot_attributes_rec.org_desc := l_current_org_desc ;
756 l_lot_attributes_rec.inventory_item_id := l_inventory_item_id ;
757 end if;
758
759 l_serial_attributes_rec.unit_number := l_unit_number;
760 if (l_object_type = 2 ) then
761 l_serial_attributes_rec.object_id := l_object_id;
762 l_serial_attributes_rec.status := l_material_status;
763 l_serial_attributes_rec.serial_number := l_object_number;
764 l_serial_attributes_rec.org_code := l_current_org_code ;
765 l_serial_attributes_rec.org_desc := l_current_org_desc ;
766 end if;
767
768 if (l_object_type = 5 ) then
769 Open get_wip;
770 Fetch get_wip
771 INTO l_wip_entity_name
772 , l_wip_entity_type
773 , l_current_org_id
774 , l_wip_entity_id
775 , l_inventory_item_id
776 ;
777 Close get_wip;
778 /*
779 SELECT wip_entity_name
780 , entity_type
781 , organization_id
782 , wip_entity_id
783 , primary_item_id
784 INTO l_wip_entity_name
785 , l_wip_entity_type
786 , l_current_org_id
787 , l_wip_entity_id
788 , l_inventory_item_id
789 FROM wip_entities
790 WHERE wip_entity_id = l_object_id;
791 */
792 l_work_order_header_rec.object_id := l_object_id;
793 l_work_order_header_rec.work_order_number := l_wip_entity_name;
794 l_work_order_header_rec.wip_entity_type := l_wip_entity_type;
795 l_work_order_header_rec.wip_entity_id := l_wip_entity_id;
796 l_work_order_header_rec.current_org_id := l_current_org_id;
797 l_work_order_header_rec.org_code := l_current_org_code;
798 l_work_order_header_rec.org_desc := l_current_org_desc;
799 l_work_order_header_rec.prod_item_id := l_inventory_item_id;
800 if l_wip_entity_type in (9, 10) then
801 l_work_order_header_rec.work_order_type := 'BATCH';
802 GMD_API_GRP.fetch_parm_values(g_current_org_id,'GMI_LOTGENE_ENABLE_FMSEC',l_allow_security,x_return_status);
803 if (l_allow_security = '1' or l_allow_security = 'Y') then
804 l_security := 0 ; -- set it to 0
805 Open get_gme_security;
806 Fetch get_gme_security into l_security;
807 Close get_gme_security;
808 End if;
809 debug(' Gme security is '||l_security);
810 else
811 l_work_order_header_rec.work_order_type := 'JOBS';
812 end if;
813 -- find out he product name by query for the product_item_id
814
815 END IF;
816
817 write_group_begin('ITEM_INFO');
818 write_header_info
819 ( p_object_id => l_object_id
820 , p_object_type => l_object_type
821 , p_object_id2 => l_object_id2
822 , p_object_type2 => l_object_type2
823 , p_item_no => l_item_info_rec.item_no
824 , p_lot_number => l_lot_attributes_rec.lot_number
825 , p_serial_number => l_serial_attributes_rec.serial_number
826 , p_wip_entity_name => l_work_order_header_rec.work_order_number
827 , p_level => p_level
828 );
829
830 if p_level = 0 then
831 debug('Write Main item Report');
832 -- for the main query item
833 --write_group_begin('tree_node_main_level');
834 /* populate the child or parent tree for the main level*/
835 if g_genealogy_type = 1 then
836 write_tree_node (l_object_id, l_object_type, l_object_id2, l_object_type2, p_level, 1);
837 else
838 write_tree_node (l_object_id, l_object_type, l_object_id2, l_object_type2, p_level, 2);
839 end if;
840 --write_group_end('tree_node_main_level');
841 end if;
842
843 /*
844 write_tree_node
845 ( p_object_id => l_object_id
846 , p_object_type => l_object_type
847 , p_object_id2 => l_object_id2
848 , p_object_type2 => l_object_type2
849 );
850 */
851 l_item_info_rec.inventory_item_id := l_inventory_item_id;
852 l_lot_attributes_rec.inventory_item_id := l_inventory_item_id;
853 write_item_info
854 ( p_item_info_rec => l_item_info_rec
855 );
856 write_lot_info
857 ( p_lot_attributes_rec => l_lot_attributes_rec
858 );
859 write_serial_info
860 ( p_serial_attributes_rec => l_serial_attributes_rec
861 );
862 write_work_order_info
863 ( p_work_order_header_rec => l_work_order_header_rec
864 ,p_work_order_dtl_rec => l_work_order_dtl_rec
865 ,p_item_info_rec => l_item_info_rec
866 );
867 if g_include_txns = 'Y' and l_security = 1 then
868 l_material_txn_rec.object_id := l_object_id;
869 l_material_txn_rec.object_type := l_object_type;
870 l_material_txn_rec.current_org_id := l_current_org_id;
871 l_material_txn_rec.secondary_uom := l_item_info_rec.secondary_uom;
872 write_material_txn_info
873 ( p_material_txn_rec => l_material_txn_rec);
874 end if;
875 if g_include_pending_txns = 'Y' and l_security = 1 then
876 l_pending_txn_rec.object_id := l_object_id;
877 l_pending_txn_rec.object_type := l_object_type;
878 l_pending_txn_rec.current_org_id := l_current_org_id;
879 l_pending_txn_rec.secondary_uom := l_item_info_rec.secondary_uom;
880 write_pending_txn_info
881 ( p_pending_txn_rec => l_pending_txn_rec);
882 end if;
883 if (g_genealogy_type = 2 and l_object_type <> 5) then
884 l_product_rec.current_org_id := l_current_org_id;
885 l_product_rec.inventory_item_id := l_item_info_rec.inventory_item_id;
886 l_product_rec.comp_lot_number := l_lot_attributes_rec.lot_number;
887 l_product_rec.comp_serial_number := l_serial_attributes_rec.serial_number;
888 write_product_info
889 ( p_product_rec => l_product_rec
890 );
891 end if;
892 if (g_genealogy_type = 1 and l_object_type <> 5) then
893 l_component_rec.current_org_id := l_current_org_id;
894 l_component_rec.inventory_item_id := l_item_info_rec.inventory_item_id;
895 l_component_rec.product_lot_number := l_lot_attributes_rec.lot_number;
896 l_component_rec.product_serial_number := l_serial_attributes_rec.serial_number;
897 l_component_rec.wip_entity_id := l_work_order_header_rec.wip_entity_id;
898 write_component_info
899 ( p_component_rec => l_component_rec
900 );
901 end if;
902 if g_include_move_txns = 'Y' then
903 l_lotbased_wip_txn_rec.object_id := l_object_id;
904 write_lotbased_wip_txn_info
905 ( p_lotbased_wip_txn_rec => l_lotbased_wip_txn_rec );
906 end if;
907 if g_quality_control = 'Y' then
908 l_quality_collections_rec.inventory_item_id := l_inventory_item_id;
909 l_quality_collections_rec.wip_entity_id := l_wip_entity_id;
910 l_quality_collections_rec.lot_number := l_lot_attributes_rec.lot_number;
911 l_quality_collections_rec.serial_number := l_serial_attributes_rec.serial_number;
912 write_quality_collections_info
913 ( p_quality_collections_rec => l_quality_collections_rec );
914
915 l_quality_samples_rec.sampling_event_id := l_lot_attributes_rec.sampling_event_id;
916 l_quality_samples_rec.current_org_id := l_current_org_id;
917 l_quality_samples_rec.inventory_item_id := l_inventory_item_id;
918 l_quality_samples_rec.lot := l_lot_attributes_rec.lot_number;
919 l_quality_samples_rec.parent_lot := l_lot_attributes_rec.parent_lot;
920 write_quality_samples_info
921 ( p_quality_samples_rec => l_quality_samples_rec );
922 end if;
923 if g_include_move_txns = 'Y' then
924 l_move_txn_rec.object_id := l_object_id;
925 l_move_txn_rec.wip_entity_id := l_wip_entity_id;
926 l_move_txn_rec.organization_id := l_current_org_id;
927 l_move_txn_rec.assembly := l_work_order_header_rec.assembly;
928 write_move_txn_info
929 ( p_move_txn_rec => l_move_txn_rec );
930 end if;
931 if g_include_grd_sts = 'Y' then
932 l_grade_status_rec.inventory_item_id := l_inventory_item_id;
933 l_grade_status_rec.current_org_id := l_current_org_id ;
934 l_grade_status_rec.uom := l_item_info_rec.primary_uom ;
935 l_grade_status_rec.secondary_uom := l_item_info_rec.secondary_uom ;
936 l_grade_status_rec.lot_number := l_lot_attributes_rec.lot_number ;
937 write_grade_status_info
938 ( p_grade_status_rec => l_grade_status_rec );
939 end if;
940 write_group_end('ITEM_INFO');
941
942 End write_genealogy_report;
943
944 Procedure write_item_info
945 ( p_item_info_rec IN OUT NOCOPY inv_genealogy_report_gen.item_info_rec_type
946 ) IS
947 cursor get_more_item_info is
948 Select shelf_life_days
949 , concatenated_segments
950 , description
951 , retest_interval
952 , DECODE(tracking_quantity_ind,'PS',secondary_uom_code) secondary_uom_code --Bug#5436402
953 from mtl_system_items_kfv
954 where inventory_item_id= p_item_info_rec.inventory_item_id
955 and organization_id = g_organization_id;
956 Begin
957 debug('write item info');
958 open get_more_item_info;
959 fetch get_more_item_info
960 into p_item_info_rec.shelf_life
961 , p_item_info_rec.item_no
962 , p_item_info_rec.item_desc
963 , p_item_info_rec.retest_interval
964 , p_item_info_rec.secondary_uom
965 ;
966 Close get_more_item_info;
967 XML_write('organization_code', p_item_info_rec.org_code);
968 XML_write('inventory_item_id', p_item_info_rec.inventory_item_id);
969 XML_write('item_no', p_item_info_rec.item_no);
970 XML_write('item_desc1', p_item_info_rec.item_desc);
971 XML_write('shelf_life', p_item_info_rec.shelf_life);
972 XML_write('retest_interval', p_item_info_rec.retest_interval);
973 End write_item_info;
974
975 Procedure write_lot_info
976 ( p_lot_attributes_rec IN OUT NOCOPY inv_genealogy_report_gen.lot_attributes_rec_type
977 ) IS
978 l_orig_txn_id NUMBER;
979 l_txn_action_id NUMBER;
980 l_txn_type_id NUMBER;
981 l_transaction_id NUMBER;
982
983 cursor get_more_lot_info IS
984 select parent_lot_number
985 , grade_code
986 , retest_date
987 , expiration_date
988 , hold_date
989 , vendor_name
990 , origination_date
991 , sampling_event_id
992 From mtl_lot_numbers
993 where lot_number = p_lot_attributes_rec.lot_number
994 and organization_id = p_lot_attributes_rec.organization_id
995 and inventory_item_id = p_lot_attributes_rec.inventory_item_id;
996
997 /*Cursor get_orig_txn_id IS
998 Select origin_txn_id
999 From mtl_object_genealogy
1000 Where object_id = p_lot_attributes_rec.object_id;
1001 Cursor get_source_data IS
1002 Select transaction_source_name
1003 ,transaction_quantity
1004 ,transaction_uom
1005 ,receiving_document
1006 ,transaction_action_id
1007 ,transaction_date
1008 From mtl_material_transactions
1009 Where transaction_id = l_orig_txn_id;
1010 Cursor get_txn_action_code IS
1011 Select meaning
1012 From mfg_lookups
1013 Where lookup_type = 'MTL_TRANSACTION_ACTION'
1014 and lookup_code = l_txn_action_id;
1015 */
1016
1017 Cursor get_orig_trans IS
1018 Select transaction_id
1019 , transaction_source_name
1020 , transaction_source
1021 , transaction_date
1022 , transaction_uom
1023 , transaction_quantity
1024 , transaction_type_id
1025 , trading_partner
1026 From mtl_transaction_details_v
1027 Where object_id = p_lot_attributes_rec.object_id
1028 Order by transaction_date asc;
1029
1030 Cursor get_type_name IS
1031 Select mtt.transaction_type_name
1032 From mtl_transaction_types mtt
1033 Where mtt.transaction_type_id = l_txn_type_id;
1034
1035 Begin
1036 write_group_begin('Lot_attributes');
1037 Open get_more_lot_info;
1038 Fetch get_more_lot_info
1039 Into p_lot_attributes_rec.parent_lot
1040 , p_lot_attributes_rec.grade_code
1041 , p_lot_attributes_rec.retest_date
1042 , p_lot_attributes_rec.expiration_date
1043 , p_lot_attributes_rec.hold_date
1044 , p_lot_attributes_rec.supplier
1045 , p_lot_attributes_rec.init_date
1046 , p_lot_attributes_rec.sampling_event_id
1047 ;
1048 Close get_more_lot_info;
1049
1050 Open get_orig_trans;
1051 Fetch get_orig_trans
1052 Into l_transaction_id
1053 , p_lot_attributes_rec.source_origin
1054 , p_lot_attributes_rec.document
1055 , p_lot_attributes_rec.init_date
1056 , p_lot_attributes_rec.uom
1057 , p_lot_attributes_rec.init_quantity
1058 , l_txn_type_id
1059 , p_lot_attributes_rec.supplier
1060 ;
1061 Close get_orig_trans;
1062 if nvl(l_txn_type_id,0)<>0 then
1063 Open get_type_name;
1064 Fetch get_type_name
1065 into p_lot_attributes_rec.init_transaction;
1066 Close get_type_name;
1067 end if;
1068
1069 XML_write('lot_number', p_lot_attributes_rec.lot_number);
1070 XML_write('parent_lot', p_lot_attributes_rec.parent_lot);
1071 --Bug#5436511 Writing expiration tag to the XML file.
1072 XML_write('expiration_date', p_lot_attributes_rec.expiration_date);
1073 XML_write('lot_status', p_lot_attributes_rec.status);
1074 XML_write('grade_code', p_lot_attributes_rec.grade_code);
1075 XML_write('retest_date', p_lot_attributes_rec.retest_date);
1076 XML_write('hold_date', p_lot_attributes_rec.hold_date);
1077 XML_write('source_origin', p_lot_attributes_rec.source_origin);
1078 XML_write('init_quantity', p_lot_attributes_rec.init_quantity);
1079 XML_write('uom', p_lot_attributes_rec.uom);
1080 XML_write('init_transaction', p_lot_attributes_rec.init_transaction);
1081 XML_write('init_date', p_lot_attributes_rec.init_date);
1082 XML_write('document', p_lot_attributes_rec.document);
1083 XML_write('supplier', p_lot_attributes_rec.supplier);
1084 write_group_end('Lot_attributes');
1085 End write_lot_info;
1086
1087 Procedure write_serial_info
1088 ( p_serial_attributes_rec IN OUT NOCOPY inv_genealogy_report_gen.serial_attributes_rec_type
1089 ) IS
1090 l_wip_entity_id NUMBER;
1091 l_inventory_item_id NUMBER;
1092
1093 cursor get_more_serial_info is
1094 select current_status_name
1095 , completion_date
1096 , ship_date
1097 , original_wip_entity_id
1098 , inventory_item_id
1099 , operation_seq_num
1100 , intraoperation_step_type
1101 , lot_number
1102 from mtl_serial_numbers_all_v
1103 where serial_number = p_serial_attributes_rec.serial_number
1104 and gen_object_id = p_serial_attributes_rec.object_id;
1105
1106 cursor get_job_info IS
1107 select we.wip_entity_name
1108 from wip_entities we
1109 where we.wip_entity_id = p_serial_attributes_rec.wip_entity_id
1110 ;
1111
1112 Cursor get_intraoperation_step IS
1113 SELECT meaning
1114 FROM mfg_lookups
1115 WHERE lookup_type = 'WIP_INTRAOPERATION_STEP'
1116 and lookup_code = p_serial_attributes_rec.intraoperation_step_type;
1117
1118 Begin
1119 debug('Write Serial Info');
1120 write_group_begin('Serial_Info');
1121 Open get_more_serial_info;
1122 Fetch get_more_serial_info
1123 Into p_serial_attributes_rec.state
1124 , p_serial_attributes_rec.receipt_date
1125 , p_serial_attributes_rec.ship_date
1126 , p_serial_attributes_rec.wip_entity_id
1127 , l_inventory_item_id
1128 , p_serial_attributes_rec.operation_seq_num
1129 , p_serial_attributes_rec.intraoperation_step_type
1130 , p_serial_attributes_rec.current_lot_number
1131 ;
1132 Close get_more_serial_info;
1133
1134 if l_wip_entity_id is not null then
1135 Open get_job_info;
1136 Fetch get_job_info
1137 into p_serial_attributes_rec.job
1138 ;
1139 Close get_job_info;
1140 end if;
1141
1142 open get_intraoperation_step;
1143 Fetch get_intraoperation_step into p_serial_attributes_rec.step;
1144 Close get_intraoperation_step;
1145
1146 XML_write('unit_number', p_serial_attributes_rec.unit_number);
1147 XML_write('serial_number', p_serial_attributes_rec.serial_number);
1148 XML_write('serial_status', p_serial_attributes_rec.status);
1149 XML_write('state', p_serial_attributes_rec.state);
1150 XML_write('current_lot_number', p_serial_attributes_rec.current_lot_number);
1151 XML_write('receipt_date', p_serial_attributes_rec.receipt_date);
1152 XML_write('ship_date', p_serial_attributes_rec.ship_date);
1153 XML_write('job', p_serial_attributes_rec.job);
1154 XML_write('operation', p_serial_attributes_rec.operation_seq_num);
1155 XML_write('step', p_serial_attributes_rec.step);
1156
1157 write_group_end('Serial_Info');
1158 End write_serial_info;
1159
1160 Procedure write_work_order_info
1161 ( p_work_order_header_rec IN OUT NOCOPY inv_genealogy_report_gen.work_order_header_rec_type
1162 , p_work_order_dtl_rec IN OUT NOCOPY inv_genealogy_report_gen.work_order_dtl_rec_type
1163 , p_item_info_rec IN OUT NOCOPY inv_genealogy_report_gen.item_info_rec_type
1164 ) IS
1165
1166 l_batch_status VARCHAR2(10);
1167 l_job_status VARCHAR2(10);
1168
1169 l_select VARCHAR2(1000);
1170 l_from VARCHAR2(200);
1171 l_where VARCHAR2(200);
1172 l_query VARCHAR2(2000);
1173
1174 type rc is ref cursor;
1175 l_rec_query rc;
1176
1177 cursor get_work_order_header is
1178 Select item_number
1179 , item_description
1180 , batch_status
1181 , status_type
1182 , organization_code
1183 , organization_name
1184 , work_order_type
1185 , wip_entity_name
1186 , date_released
1187 , date_completed
1188 from mtl_work_order_header_v
1189 where wip_entity_id = p_work_order_header_rec.wip_entity_id
1190 ;
1191 cursor get_wip_dtl is
1192 select item_number
1193 , item_description
1194 , start_quantity
1195 , quantity_remaining
1196 , quantity_scrapped
1197 , quantity_completed
1198 , uom
1199 from wsm_wip_genealogy_lotattr_v
1200 where wip_entity_id = p_work_order_header_rec.wip_entity_id
1201 ;
1202 cursor get_gme_dtl is
1203 select item_number
1204 , item_description
1205 , start_quantity
1206 , quantity_remaining
1207 , quantity_scrapped
1208 , quantity_completed
1209 , uom
1210 from mtl_mfg_genealogy_lotattr_v
1211 where wip_entity_id = p_work_order_header_rec.wip_entity_id
1212 ;
1213 Begin
1214 debug('Write Work Order Info');
1215
1216 Open get_work_order_header;
1217 Fetch get_work_order_header
1218 Into p_work_order_header_rec.assembly
1219 , p_work_order_header_rec.assembly_desc
1220 , l_batch_status
1221 , l_job_status
1222 , p_work_order_header_rec.org_code
1223 , p_work_order_header_rec.org_desc
1224 , p_work_order_header_rec.work_order_type
1225 , p_work_order_header_rec.work_order_number
1226 , p_work_order_header_rec.date_released
1227 , p_work_order_header_rec.date_completed
1228 ;
1229 Close get_work_order_header;
1230
1231 if l_batch_status is not null then
1232 p_work_order_header_rec.status := l_batch_status;
1233 end if;
1234 if l_job_status is not null then
1235 p_work_order_header_rec.status := l_job_status;
1236 end if;
1237
1238 write_group_begin('Work_order_header');
1239 XML_write('assembly', p_work_order_header_rec.assembly);
1240 XML_write('assembly_desc', p_work_order_header_rec.assembly_desc);
1241 XML_write('status', p_work_order_header_rec.status);
1242 XML_write('org_code', p_work_order_header_rec.org_code);
1243 XML_write('org_desc', p_work_order_header_rec.org_desc);
1244 XML_write('work_order_type', p_work_order_header_rec.work_order_type);
1245 XML_write('work_order_number', p_work_order_header_rec.work_order_number);
1246 XML_write('date_released', p_work_order_header_rec.date_released);
1247 XML_write('date_completed', p_work_order_header_rec.date_completed);
1248 write_group_end('Work_order_header');
1249
1250 if p_work_order_header_rec.wip_entity_id is null then
1251 return;
1252 end if;
1253 l_select := 'select item_number'
1254 ||' , item_description'
1255 ||' , start_quantity'
1256 ||' , quantity_remaining'
1257 ||' , quantity_scrapped'
1258 ||' , quantity_completed'
1259 ||' , uom'
1260 ;
1261 debug('wip_entity_type '||p_work_order_header_rec.wip_entity_type);
1262 if p_work_order_header_rec.wip_entity_type in (9, 10) then
1263 l_from := ' from mtl_mfg_genealogy_lotattr_v';
1264 else
1265 l_from := ' from wsm_wip_genealogy_lotattr_v';
1266 end if;
1267
1268 l_where := ' where wip_entity_id = '||p_work_order_header_rec.wip_entity_id;
1269
1270 l_query := l_select||l_from||l_where;
1271 --debug('work order dtl query: '||l_query);
1272
1273 Open l_rec_query for l_query;
1274 Loop
1275 Fetch l_rec_query
1276 Into p_work_order_dtl_rec.product
1277 , p_work_order_dtl_rec.product_desc
1278 , p_work_order_dtl_rec.planned_qty
1279 , p_work_order_dtl_rec.qty_remaining
1280 , p_work_order_dtl_rec.qty_scrapped
1281 , p_work_order_dtl_rec.qty_completed
1282 , p_work_order_dtl_rec.uom
1283 ;
1284 Exit when l_rec_query %NOTFOUND;
1285
1286 write_group_begin('Work_order_details');
1287 XML_write('product', p_work_order_dtl_rec.product);
1288 XML_write('product_desc', p_work_order_dtl_rec.product_desc);
1289 XML_write('planned_qty', p_work_order_dtl_rec.planned_qty);
1290 XML_write('qty_scrapped', p_work_order_dtl_rec.qty_scrapped);
1291 XML_write('qty_remaining', p_work_order_dtl_rec.qty_remaining);
1292 XML_write('qty_completed', p_work_order_dtl_rec.qty_completed);
1293 XML_write('uom', p_work_order_dtl_rec.uom);
1294 write_group_end('Work_order_details');
1295 End loop;
1296 Close l_rec_query;
1297
1298 End write_work_order_info;
1299
1300 Procedure write_material_txn_info
1301 ( p_material_txn_rec IN OUT NOCOPY inv_genealogy_report_gen.material_txn_rec_type
1302 ) IS
1303
1304 l_transaction_type_id NUMBER;
1305 l_locator_id NUMBER;
1306 l_source_line_id NUMBER;
1307 l_source_code VARCHAR2(80);
1308
1309 l_select VARCHAR2(1000);
1310 l_from VARCHAR2(200);
1311 l_where VARCHAR2(200);
1312 l_order_by VARCHAR2(200);
1313 l_query VARCHAR2(2000);
1314
1315 type rc is ref cursor;
1316 l_rec_query rc;
1317
1318 cursor get_material_txns
1319 is
1320 select
1321 transaction_date
1322 , organization_code
1323 , transaction_source_name
1324 , transaction_type_id
1325 , transaction_source -- document?
1326 , transaction_quantity
1327 , transaction_uom
1328 , secondary_quantity
1329 , subinventory_code
1330 , locator_id
1331 , source_line_id
1332 , project
1333 , task
1334 , lpn_number
1335 , transfer_lpn_number
1336 , content_lpn_number
1337 from mtl_transaction_details_v
1338 where object_id = p_material_txn_rec.object_id
1339 and object_type = p_material_txn_rec.object_type
1340 and organization_id = p_material_txn_rec.current_org_id
1341 ;
1342 cursor get_wip_material_txns
1343 is
1344 select
1345 transaction_date
1346 , organization_code
1347 , transaction_source_name
1348 , transaction_type_id
1349 , source_code -- document?
1350 , transaction_quantity
1351 , transaction_uom
1352 , secondary_quantity
1353 , subinventory_code
1354 , locator_id
1355 , source_line_id
1356 , project
1357 , task
1358 , lpn_number
1359 , transfer_lpn_number
1360 , content_lpn_number
1361 from wsm_inv_txns_wip_lots_v
1362 where object_id = p_material_txn_rec.object_id
1363 and object_type = p_material_txn_rec.object_type
1364 and organization_id = p_material_txn_rec.current_org_id
1365 ;
1366 cursor get_gme_material_txns
1367 is
1368 select
1369 transaction_date
1370 , organization_code
1371 , transaction_source_name
1372 , transaction_type_id
1373 , source_code -- document?
1374 , transaction_quantity
1375 , transaction_uom
1376 , secondary_quantity
1377 , subinventory_code
1378 , locator_id
1379 , source_line_id
1380 , project
1381 , task
1382 , lpn_number
1383 , transfer_lpn_number
1384 , content_lpn_number
1385 from mtl_inv_txns_mfg_lots_v
1386 where object_id = p_material_txn_rec.object_id
1387 and object_type = p_material_txn_rec.object_type
1388 and organization_id = p_material_txn_rec.current_org_id
1389 ;
1390
1391 Begin
1392 debug('Write Material Txns');
1393
1394 l_select := 'select'
1395 ||' transaction_date'
1396 ||' , organization_code'
1397 ||' , transaction_source_name'
1398 ||' , transaction_source'
1399 ||' , transaction_type_id'
1400 ||' , source_code'
1401 ||' , transaction_quantity'
1402 ||' , transaction_uom'
1403 ||' , secondary_quantity'
1404 ||' , subinventory_code'
1405 ||' , locator_id'
1406 ||' , source_line_id'
1407 ||' , project'
1408 ||' , task'
1409 ||' , lpn_number'
1410 ||' , transfer_lpn_number'
1411 ||' , content_lpn_number'
1412 --Bug#5436511 Added grade_code column to the select statement.
1413 ||' , grade_code'
1414 ;
1415 l_where := ' where object_id = '||p_material_txn_rec.object_id
1416 ||' and object_type = '||p_material_txn_rec.object_type
1417 ||' and organization_id = '||p_material_txn_rec.current_org_id
1418 ;
1419 if p_material_txn_rec.object_type <> 5 then
1420 l_from := ' from mtl_transaction_details_v';
1421 elsif p_material_txn_rec.wip_entity_type in (9, 10) then
1422 l_from := ' from mtl_inv_txns_mfg_lots_v';
1423 else
1424 l_from := ' from wsm_inv_txns_wip_lots_v';
1425 end if;
1426 l_order_by := ' order by transaction_date desc';
1427 l_query := l_select||l_from||l_where||l_order_by;
1428 --debug('Material_txn Query: '||l_query);
1429
1430 Open l_rec_query for l_query;
1431 Loop
1432 Fetch l_rec_query
1433 Into p_material_txn_rec.transaction_date
1434 , p_material_txn_rec.organization
1435 , p_material_txn_rec.transaction_source_type
1436 , p_material_txn_rec.document
1437 , l_transaction_type_id
1438 , l_source_code
1439 , p_material_txn_rec.quantity
1440 , p_material_txn_rec.uom
1441 , p_material_txn_rec.secondary_quantity
1442 , p_material_txn_rec.subinventory
1443 , l_locator_id
1444 , l_source_line_id
1445 , p_material_txn_rec.project
1446 , p_material_txn_rec.task
1447 , p_material_txn_rec.lpn
1448 , p_material_txn_rec.transfer_lpn
1449 , p_material_txn_rec.content_lpn
1450 --Bug#5436511
1451 , p_material_txn_rec.grade
1452 ;
1453 Exit when l_rec_query %NOTFOUND;
1454
1455 /*Bug#5436402 if the item is not tracked in Primary and Secondary then
1456 we should not display sec qty.sec uom is already filtered by the cursor cur_get_item_info*/
1457 IF g_tracking_quantity_ind <> 'PS' THEN
1458 p_material_txn_rec.secondary_quantity := NULL;
1459 END IF;
1460
1461 p_material_txn_rec.locator := INV_PROJECT.GET_LOCATOR(l_locator_id,
1462 p_material_txn_rec.current_org_id) ;
1463 if (l_source_code='WSM'
1464 and p_material_txn_rec.object_type = 1
1465 and l_transaction_type_id in (32, 42) )
1466 then
1467 SELECT ml.meaning
1468 into p_material_txn_rec.TRANSACTION_TYPE
1469 from wsm_lot_split_merges wlsm
1470 , mfg_lookups ml
1471 where wlsm.transaction_id= l_source_line_id
1472 and ml.lookup_type='WSM_INV_LOT_TXN_TYPE'
1473 and ml.lookup_code=wlsm.transaction_type_id;
1474 else
1475 select mtt.transaction_type_name
1476 into p_material_txn_rec.transaction_type
1477 from MTL_TRANSACTION_TYPES mtt
1478 where mtt.transaction_type_id = l_transaction_type_id;
1479 end if;
1480
1481 write_group_begin('transaction_details');
1482 XML_write('transaction_date', p_material_txn_rec.transaction_date);
1483 XML_write('organization', p_material_txn_rec.organization);
1484 XML_write('transaction_source_type', p_material_txn_rec.transaction_source_type);
1485 XML_write('transaction_type', p_material_txn_rec.transaction_type);
1486 XML_write('document', p_material_txn_rec.document);
1487 XML_write('quantity', p_material_txn_rec.quantity);
1488 XML_write('uom', p_material_txn_rec.uom);
1489 XML_write('secondary_quantity', p_material_txn_rec.secondary_quantity);
1490 XML_write('secondary_uom', p_material_txn_rec.secondary_uom);
1491 XML_write('subinventory', p_material_txn_rec.subinventory);
1492 XML_write('locator', p_material_txn_rec.locator);
1493 XML_write('project', p_material_txn_rec.project);
1494 XML_write('task', p_material_txn_rec.task);
1495 XML_write('lpn', p_material_txn_rec.lpn);
1496 XML_write('transfer_lpn', p_material_txn_rec.transfer_lpn);
1497 XML_write('content_lpn', p_material_txn_rec.content_lpn);
1498 XML_write('grade', p_material_txn_rec.grade);
1499 write_group_end('transaction_details');
1500 End loop;
1501 Close l_rec_query;
1502 End write_material_txn_info;
1503
1504 Procedure write_pending_txn_info
1505 ( p_pending_txn_rec IN OUT NOCOPY inv_genealogy_report_gen.pending_txn_rec_type
1506 ) IS
1507 l_transaction_type_id NUMBER;
1508 l_locator_id NUMBER;
1509 l_txn_status_code NUMBER;
1510 l_source_line_id NUMBER;
1511 l_source_code VARCHAR2(20);
1512
1513 l_select VARCHAR2(1000);
1514 l_from VARCHAR2(200);
1515 l_where VARCHAR2(200);
1516 l_order_by VARCHAR2(200);
1517 l_query VARCHAR2(2000);
1518
1519 type rc is ref cursor;
1520 l_rec_query rc;
1521
1522 cursor get_pending_txns
1523 is
1524 select
1525 transaction_date
1526 , organization_code
1527 , transaction_source_name
1528 , transaction_type_id
1529 , source_code -- document?
1530 , transaction_quantity
1531 , transaction_uom
1532 , secondary_quantity
1533 , subinventory_code
1534 , locator_id
1535 , source_line_id
1536 , project
1537 , task
1538 , lpn_number
1539 , transfer_lpn_number
1540 , content_lpn_number
1541 , transaction_status
1542 from mtl_pending_txn_details_v
1543 where object_id = p_pending_txn_rec.object_id
1544 and object_type = p_pending_txn_rec.object_type
1545 and organization_id = p_pending_txn_rec.current_org_id
1546 ;
1547 cursor get_mfg_pending_txns
1548 is
1549 select
1550 transaction_date
1551 , organization_code
1552 , transaction_source_name
1553 , transaction_type_id
1554 , source_code -- document?
1555 , transaction_quantity
1556 , transaction_uom
1557 , secondary_quantity
1558 , subinventory_code
1559 , locator_id
1560 , source_line_id
1561 , project
1562 , task
1563 , lpn_number
1564 , transfer_lpn_number
1565 , content_lpn_number
1566 , transaction_status
1567 from mtl_pending_txns_mfg_lots_v
1568 where object_id = p_pending_txn_rec.object_id
1569 and object_type = p_pending_txn_rec.object_type
1570 and organization_id = p_pending_txn_rec.current_org_id
1571 ;
1572 Begin
1573 debug('Write pending txns ');
1574
1575 l_select := 'select transaction_date '
1576 ||' , organization_code'
1577 ||' , transaction_source_name'
1578 ||' , transaction_type_id'
1579 ||' , source_code'
1580 ||' , transaction_quantity'
1581 ||' , transaction_uom'
1582 ||' , secondary_quantity'
1583 ||' , subinventory_code'
1584 ||' , locator_id'
1585 ||' , source_line_id'
1586 ||' , project'
1587 ||' , task'
1588 ||' , lpn_number'
1589 ||' , transfer_lpn_number'
1590 ||' , content_lpn_number'
1591 ||' , transaction_status'
1592 ;
1593 l_where := ' where object_id = '||p_pending_txn_rec.object_id
1594 ||' and object_type = '||p_pending_txn_rec.object_type
1595 ||' and organization_id = '||p_pending_txn_rec.current_org_id
1596 ;
1597 if p_pending_txn_rec.object_type <> 5 then
1598 l_from := ' from mtl_pending_txn_details_v';
1599 else
1600 l_from := ' from mtl_pending_txns_mfg_lots_v';
1601 end if;
1602
1603 l_order_by := ' order by transaction_date desc';
1604 l_query := l_select||l_from||l_where||l_order_by;
1605 --debug('Pending_txn Query: '||l_query);
1606
1607 Open l_rec_query for l_query;
1608 Loop
1609 Fetch l_rec_query
1610 Into p_pending_txn_rec.transaction_date
1611 , p_pending_txn_rec.organization
1612 , p_pending_txn_rec.transaction_source_type
1613 , l_transaction_type_id
1614 , l_source_code
1615 , p_pending_txn_rec.quantity
1616 , p_pending_txn_rec.uom
1617 , p_pending_txn_rec.secondary_quantity
1618 , p_pending_txn_rec.subinventory
1619 , l_locator_id
1620 , l_source_line_id
1621 , p_pending_txn_rec.project
1622 , p_pending_txn_rec.task
1623 , p_pending_txn_rec.lpn
1624 , p_pending_txn_rec.transfer_lpn
1625 , p_pending_txn_rec.content_lpn
1626 , l_txn_status_code
1627 ;
1628 Exit when l_rec_query %NOTFOUND;
1629
1630 /*Bug#5436402 if the item is not tracked in Primary and Secondary then
1631 we should not display sec qty. sec uom is already filtered by the cursor cur_get_item_info*/
1632 IF g_tracking_quantity_ind <> 'PS' THEN
1633 p_pending_txn_rec.secondary_quantity := NULL;
1634 END IF;
1635
1636 p_pending_txn_rec.locator := INV_PROJECT.GET_LOCATOR(l_locator_id,
1637 p_pending_txn_rec.current_org_id) ;
1638 if (l_source_code='WSM'
1639 and p_pending_txn_rec.object_type = 1
1640 and l_transaction_type_id in (32, 42) )
1641 then
1642 SELECT ml.meaning
1643 into p_pending_txn_rec.TRANSACTION_TYPE
1644 from wsm_lot_split_merges wlsm
1645 , mfg_lookups ml
1646 where wlsm.transaction_id= l_source_line_id
1647 and ml.lookup_type='WSM_INV_LOT_TXN_TYPE'
1648 and ml.lookup_code=wlsm.transaction_type_id;
1649 else
1650 select mtt.transaction_type_name
1651 into p_pending_txn_rec.transaction_type
1652 from MTL_TRANSACTION_TYPES mtt
1653 where mtt.transaction_type_id = l_transaction_type_id;
1654 end if;
1655
1656 if l_txn_status_code = 2 then
1657 p_pending_txn_rec.transaction_status := 'Allocated';
1658 else
1659 p_pending_txn_rec.transaction_status := 'Pending';
1660 end if;
1661
1662 write_group_begin('pending_transaction_details');
1663 XML_write('transaction_date', p_pending_txn_rec.transaction_date);
1664 XML_write('organization', p_pending_txn_rec.organization);
1665 XML_write('transaction_source_type', p_pending_txn_rec.transaction_source_type);
1666 XML_write('transaction_type', p_pending_txn_rec.transaction_type);
1667 XML_write('document', p_pending_txn_rec.document);
1668 XML_write('quantity', p_pending_txn_rec.quantity);
1669 XML_write('uom', p_pending_txn_rec.uom);
1670 XML_write('secondary_quantity', p_pending_txn_rec.secondary_quantity);
1671 XML_write('secondary_uom', p_pending_txn_rec.secondary_uom);
1672 XML_write('subinventory', p_pending_txn_rec.subinventory);
1673 XML_write('locator', p_pending_txn_rec.locator);
1674 XML_write('project', p_pending_txn_rec.project);
1675 XML_write('task', p_pending_txn_rec.task);
1676 XML_write('lpn', p_pending_txn_rec.lpn);
1677 XML_write('transfer_lpn', p_pending_txn_rec.transfer_lpn);
1678 XML_write('content_lpn', p_pending_txn_rec.content_lpn);
1679 XML_write('grade', p_pending_txn_rec.grade);
1680 write_group_end('pending_transaction_details');
1681 End loop;
1682 Close l_rec_query;
1683
1684 End write_pending_txn_info;
1685
1686 Procedure write_product_info
1687 ( p_product_rec IN OUT NOCOPY inv_genealogy_report_gen.product_rec_type
1688 ) IS
1689
1690 l_locator_id NUMBER;
1691
1692 l_select VARCHAR2(1000);
1693 l_from VARCHAR2(200);
1694 l_where VARCHAR2(200);
1695 l_query VARCHAR2(2000);
1696
1697 type rc is ref cursor;
1698 l_rec_query rc;
1699
1700 cursor get_products is
1701 select organization_code
1702 , transaction_date
1703 , item_number
1704 , item_type
1705 , lot_number
1706 , primary_quantity
1707 , primary_uom_code
1708 , secondary_quantity
1709 , secondary_uom_code
1710 , subinventory_code
1711 , locator_id
1712 , grade_code
1713 , serial_number
1714 from mtl_mfg_products_v
1715 where comp_item_id = p_product_rec.inventory_item_id
1716 and organization_id = p_product_rec.current_org_id
1717 ;
1718 Begin
1719 debug('Write Product Info for item_id '||p_product_rec.inventory_item_id||'Org '||p_product_rec.current_org_id);
1720
1721 Open get_products;
1722 Loop
1723 Fetch get_products
1724 Into p_product_rec.organization
1725 , p_product_rec.transaction_date
1726 , p_product_rec.assembly
1727 , p_product_rec.product_type
1728 , p_product_rec.lot
1729 , p_product_rec.quantity
1730 , p_product_rec.uom
1731 , p_product_rec.secondary_quantity
1732 , p_product_rec.secondary_uom
1733 , p_product_rec.subinventory
1734 , l_locator_id
1735 , p_product_rec.grade
1736 , p_product_rec.serial
1737 ;
1738 Exit when get_products %NOTFOUND;
1739
1740 /*Bug#5436402 The selection from view mtl_mfg_products_v fetches secondary uom irrespeive of tracking id.
1741 if the item is not tracked in Primary and Secondary then we should not display sec qty and sec uom*/
1742 IF g_tracking_quantity_ind <> 'PS' THEN
1743 p_product_rec.secondary_quantity := NULL;
1744 p_product_rec.secondary_uom := NULL;
1745 END IF;
1746
1747 p_product_rec.locator := INV_PROJECT.GET_LOCATOR(l_locator_id,
1748 p_product_rec.current_org_id) ;
1749
1750 write_group_begin('Products');
1751 XML_write('organization',p_product_rec.organization);
1752 XML_write('assembly',p_product_rec.assembly);
1753 XML_write('transaction_date',p_product_rec.transaction_date);
1754 XML_write('product_type',p_product_rec.product_type);
1755 XML_write('product_lot',p_product_rec.lot);
1756 XML_write('product_serial',p_product_rec.serial);
1757 XML_write('quantity',p_product_rec.quantity);
1758 XML_write('uom',p_product_rec.uom);
1759 XML_write('secondary_quantity',p_product_rec.secondary_quantity);
1760 XML_write('secondary_uom',p_product_rec.secondary_uom);
1761 XML_write('subinventory',p_product_rec.subinventory);
1762 XML_write('locator',p_product_rec.locator);
1763 XML_write('grade',p_product_rec.grade);
1764 write_group_end('Products');
1765 End Loop;
1766 Close get_products;
1767
1768 End write_product_info;
1769
1770 Procedure write_component_info
1771 ( p_component_rec IN OUT NOCOPY inv_genealogy_report_gen.component_rec_type
1772 ) IS
1773
1774 l_locator_id NUMBER;
1775
1776 cursor get_components is
1777 select organization_code
1778 , transaction_date
1779 , item_number
1780 , lot_number
1781 , primary_quantity
1782 , primary_uom_code
1783 , secondary_quantity
1784 , secondary_uom_code
1785 , subinventory_code
1786 , locator_id
1787 , grade_code
1788 , serial_number
1789 , wip_entity_name
1790 from mtl_mfg_components_v
1791 where product_item_id = p_component_rec.inventory_item_id
1792 AND DECODE(product_lot_number,NULL, '%%$#', product_lot_number)
1793 = DECODE( p_component_rec.product_lot_number, NULL, '%%$#', p_component_rec.product_lot_number)
1794 AND DECODE(product_serial_number, NULL, '%%##', product_serial_number)
1795 = DECODE(p_component_rec.product_serial_number, NULL, '%%##', p_component_rec.product_serial_number)
1796 ORDER BY transaction_date DESC
1797 ;
1798 Begin
1799 debug('Write Component Info for item_id '||p_component_rec.inventory_item_id||'Org '||p_component_rec.current_org_id);
1800 Open get_components;
1801 Loop
1802 Fetch get_components
1803 Into p_component_rec.organization
1804 , p_component_rec.transaction_date
1805 , p_component_rec.item
1806 , p_component_rec.lot
1807 , p_component_rec.quantity
1808 , p_component_rec.uom
1809 , p_component_rec.secondary_quantity
1810 , p_component_rec.secondary_uom
1811 , p_component_rec.subinventory
1812 , l_locator_id
1813 , p_component_rec.grade
1814 , p_component_rec.serial
1815 , p_component_rec.wip_entity_name
1816 ;
1817 Exit when get_components %NOTFOUND;
1818
1819 /*Bug#5436402 The selection from view mtl_mfg_components_v fetches secondary uom irrespeive of tracking ind.
1820 if the item is not tracked in Primary and Secondary then we should not display sec qty and sec uom*/
1821 IF g_tracking_quantity_ind <> 'PS' THEN
1822 p_component_rec.secondary_quantity := NULL;
1823 p_component_rec.secondary_uom := NULL;
1824 END IF;
1825
1826 p_component_rec.locator := INV_PROJECT.GET_LOCATOR(l_locator_id,
1827 p_component_rec.current_org_id) ;
1828
1829 write_group_begin('Components');
1830 XML_write('organization', p_component_rec.organization);
1831 XML_write('component_item', p_component_rec.item);
1832 XML_write('transaction_date', p_component_rec.transaction_date);
1833 XML_write('component_lot', p_component_rec.lot);
1834 XML_write('component_serial', p_component_rec.serial);
1835 XML_write('quantity', p_component_rec.quantity);
1836 XML_write('uom', p_component_rec.uom);
1837 XML_write('secondary_quantity', p_component_rec.secondary_quantity);
1838 XML_write('secondary_uom', p_component_rec.secondary_uom);
1839 XML_write('subinventory', p_component_rec.subinventory);
1840 XML_write('locator', p_component_rec.locator);
1841 XML_write('grade', p_component_rec.grade);
1842 XML_write('wip_document', p_component_rec.wip_entity_name);
1843 write_group_end('Components');
1844 End Loop;
1845 Close get_components;
1846
1847 End write_component_info;
1848
1849 Procedure write_quality_collections_info
1850 ( p_quality_collections_rec IN OUT NOCOPY inv_genealogy_report_gen.quality_collections_rec_type
1851 ) IS
1852
1853 l_where VARCHAR2(2000);
1854 l_query VARCHAR2(5000);
1855
1856 type rc is ref cursor;
1857 l_rec_query rc;
1858
1859 /*cursor get_collections is
1860 Select name
1861 , description
1862 , plan_type_description
1863 from mtl_genealogy_qa_data
1864 where (item_id = p_quality_collections_rec.inventory_item_id)
1865 or comp_item_id = p_quality_collections_rec.inventory_item_id))
1866 and lot_number = decode(p_quality_collections_rec.lot_number, null,'%%XX', p_quality_collections_rec.lot_number)
1867 or comp_lot_number = decode(p_quality_collections_rec.lot_number, null,'%%XX', p_quality_collections_rec.lot_number))
1868 and serial_number = decode(p_quality_collections_rec.serial_number, null,'%%XX', p_quality_collections_rec.serial_number)
1869 or comp_serial_number = decode(p_quality_collections_rec.serial_number, null,'%%XX', p_quality_collections_rec.serial_number))
1870 and decode(wip_entity_id, null,'%%XX', wip_entity_id)
1871 = decode(p_quality_collections_rec.wip_entity_id, null,'%%XX', p_quality_collections_rec.wip_entity_id)
1872 ;
1873 */
1874 Begin
1875 debug('Write Quality Collections Info');
1876 debug(' item_id '||p_quality_collections_rec.inventory_item_id);
1877 debug(' lot_number '||p_quality_collections_rec.lot_number);
1878 debug(' serial_number '||p_quality_collections_rec.serial_number);
1879 debug(' wip_entity_id '||p_quality_collections_rec.wip_entity_id);
1880
1881 l_query := ' Select name '
1882 || ', description'
1883 || ', plan_type_description'
1884 || ' from mtl_genealogy_qa_data '
1885 ;
1886 l_where := ' where (item_id = '
1887 || p_quality_collections_rec.inventory_item_id
1888 ||' or comp_item_id = '
1889 || p_quality_collections_rec.inventory_item_id
1890 ||' ) '
1891 ;
1892 if p_quality_collections_rec.lot_number is not null then
1893 l_where := l_where ||' and '
1894 ||' (lot_number = '
1895 ||''''|| p_quality_collections_rec.lot_number ||''''
1896 ||' or comp_lot_number = '
1897 ||''''|| p_quality_collections_rec.lot_number ||''''
1898 ||' ) '
1899 ;
1900 end if;
1901 if p_quality_collections_rec.serial_number is not null then
1902 l_where := l_where ||' and '
1903 ||' (serial_number = '
1904 ||''''|| p_quality_collections_rec.serial_number ||''''
1905 ||' or comp_serial_number = '
1906 ||''''||p_quality_collections_rec.serial_number||''''
1907 ||' ) ';
1908 end if;
1909 if p_quality_collections_rec.wip_entity_id is not null then
1910 l_where := l_where ||' and '
1911 ||' wip_entity_id = '
1912 || p_quality_collections_rec.wip_entity_id ;
1913 end if;
1914
1915 l_query := l_query || l_where;
1916 debug('quality collections: '||l_query);
1917
1918 Open l_rec_query for l_query;
1919 Loop
1920 Fetch l_rec_query
1921 Into p_quality_collections_rec.collection_plan
1922 , p_quality_collections_rec.plan_description
1923 , p_quality_collections_rec.plan_type
1924 ;
1925 Exit when l_rec_query %NOTFOUND;
1926 write_group_begin('quality_collections');
1927 XML_write('collection_plan', p_quality_collections_rec.collection_plan);
1928 XML_write('plan_type', p_quality_collections_rec.plan_type);
1929 XML_write('plan_description', p_quality_collections_rec.plan_description);
1930 write_group_end('quality_collections');
1931 End Loop;
1932 Close l_rec_query;
1933
1934 End write_quality_collections_info;
1935
1936 Procedure write_quality_samples_info
1937 ( p_quality_samples_rec IN OUT NOCOPY inv_genealogy_report_gen.quality_samples_rec_type
1938 ) IS
1939
1940 l_locator_id NUMBER;
1941 l_count NUMBER;
1942
1943 l_select VARCHAR2(1000);
1944 l_from VARCHAR2(200);
1945 --# Sunitha Ch. 21jun06. Bug#5312854. Changed the size of l_where to 500 from 200
1946 -- l_where VARCHAR2(200);
1947 l_where VARCHAR2(500);
1948 l_query VARCHAR2(2000);
1949
1950 type rc is ref cursor;
1951 l_rec_query rc;
1952
1953 Cursor get_samples is
1954 select sample_no
1955 , sample_desc
1956 , date_drawn
1957 , source
1958 , lot_number
1959 , subinventory
1960 , sample_qty
1961 , sample_qty_uom
1962 , locator_id
1963 from gmd_samples
1964 where inventory_item_id = p_quality_samples_rec.inventory_item_id
1965 and organization_id = p_quality_samples_rec.current_org_id
1966 and decode(lot_number,'%%XX', lot_number) = decode(p_quality_samples_rec.lot, '%%XX', p_quality_samples_rec.lot)
1967 ;
1968
1969 Cursor get_samples_count is
1970 select count(*)
1971 from gmd_samples
1972 where inventory_item_id = p_quality_samples_rec.inventory_item_id
1973 and organization_id = p_quality_samples_rec.current_org_id
1974 and decode(lot_number,'%%XX', lot_number) = decode(p_quality_samples_rec.lot, '%%XX', p_quality_samples_rec.lot)
1975 ;
1976
1977 Cursor get_org is
1978 SELECT mp.organization_code
1979 FROM mtl_parameters mp
1980 WHERE mp.organization_id = p_quality_samples_rec.current_org_id
1981 ;
1982
1983 Cursor get_samples_parent is
1984 select sample_no
1985 , sample_desc
1986 , date_drawn
1987 , source
1988 , lot_number
1989 , subinventory
1990 , sample_qty
1991 , sample_qty_uom
1992 , locator_id
1993 from gmd_samples
1994 where inventory_item_id = p_quality_samples_rec.inventory_item_id
1995 and organization_id = p_quality_samples_rec.current_org_id
1996 and decode(lot_number,'%%XX', lot_number) = decode(p_quality_samples_rec.parent_lot, '%%XX', p_quality_samples_rec.parent_lot)
1997 ;
1998
1999 Cursor get_samples_parent_count is
2000 select count(*)
2001 from gmd_samples
2002 where inventory_item_id = p_quality_samples_rec.inventory_item_id
2003 and organization_id = p_quality_samples_rec.current_org_id
2004 and decode(lot_number,'%%XX', lot_number) = decode(p_quality_samples_rec.parent_lot, '%%XX', p_quality_samples_rec.parent_lot)
2005 ;
2006
2007 Cursor get_samples_sample_event is
2008 select sample_no
2009 , sample_desc
2010 , date_drawn
2011 , source
2012 , lot_number
2013 , subinventory
2014 , sample_qty
2015 , sample_qty_uom
2016 , locator_id
2017 from gmd_samples
2018 where sampling_event_id = p_quality_samples_rec.sampling_event_id
2019 ;
2020
2021 Begin
2022 debug('Write Quality Samples Info');
2023
2024 l_select := 'select sample_no'
2025 ||' , sample_desc'
2026 ||' , date_drawn'
2027 ||' , source'
2028 ||' , lot_number'
2029 ||' , subinventory'
2030 ||' , sample_qty'
2031 ||' , sample_qty_uom'
2032 ||' , locator_id'
2033 ;
2034 l_from := ' from gmd_samples';
2035 l_where := ' where inventory_item_id = '||p_quality_samples_rec.inventory_item_id
2036 ||' and organization_id = '||p_quality_samples_rec.current_org_id
2037 ||' and decode(lot_number,'||''''||'%%XX'||''''||', lot_number) = '
2038 ||'decode('||p_quality_samples_rec.lot||', '||''''||'%%XX'||''''||', '||p_quality_samples_rec.lot||')'
2039 ;
2040 Open get_samples_count;
2041 Fetch get_samples_count Into l_count;
2042 Close get_samples_count;
2043
2044 if l_count = 0 then
2045 l_where := '';
2046 l_where := ' where inventory_item_id = '||p_quality_samples_rec.inventory_item_id
2047 ||' and organization_id = '||p_quality_samples_rec.current_org_id
2048 ||' and decode(lot_number,'||''''||'%%XX'||''''||', lot_number) = '
2049 ||'decode('||p_quality_samples_rec.parent_lot||', '||''''||'%%XX'||''''||', '||p_quality_samples_rec.parent_lot||')'
2050 ;
2051
2052 Open get_samples_parent_count;
2053 Fetch get_samples_parent_count Into l_count;
2054 Close get_samples_parent_count;
2055
2056 if l_count = 0 then
2057 l_where := '';
2058 l_where := ' where sampling_event_id = '||p_quality_samples_rec.sampling_event_id ;
2059 if p_quality_samples_rec.sampling_event_id is null then
2060 return;
2061 end if;
2062 end if;
2063 end if;
2064
2065 l_query := l_select||l_from||l_where;
2066 Open l_rec_query for l_query;
2067 Loop
2068 Fetch l_rec_query
2069 Into p_quality_samples_rec.sample_number
2070 , p_quality_samples_rec.sample_description
2071 , p_quality_samples_rec.date_drawn
2072 , p_quality_samples_rec.sample_source
2073 , p_quality_samples_rec.lot
2074 , p_quality_samples_rec.subinventory
2075 , p_quality_samples_rec.sample_quantity
2076 , p_quality_samples_rec.uom
2077 , l_locator_id
2078 ;
2079 Exit when l_rec_query %NOTFOUND;
2080
2081 p_quality_samples_rec.locator := INV_PROJECT.GET_LOCATOR(l_locator_id,
2082 p_quality_samples_rec.current_org_id) ;
2083 Open get_org;
2084 Fetch get_org into p_quality_samples_rec.organization;
2085 Close get_org;
2086 write_group_begin('quality_samples');
2087 XML_write('sample_number', p_quality_samples_rec.organization||'-'||p_quality_samples_rec.sample_number);
2088 XML_write('sample_description', p_quality_samples_rec.sample_description);
2089 XML_write('date_drawn', p_quality_samples_rec.date_drawn);
2090 XML_write('sample_source', p_quality_samples_rec.sample_source);
2091 XML_write('lot', p_quality_samples_rec.lot);
2092 XML_write('subinventory', p_quality_samples_rec.subinventory);
2093 XML_write('locator', p_quality_samples_rec.locator);
2094 XML_write('sample_quantity', p_quality_samples_rec.sample_quantity);
2095 XML_write('uom', p_quality_samples_rec.uom);
2096 write_group_end('quality_samples');
2097 End loop;
2098 Close l_rec_query;
2099
2100 End write_quality_samples_info;
2101
2102 Procedure write_lotbased_wip_txn_info
2103 ( p_lotbased_wip_txn_rec IN OUT NOCOPY inv_genealogy_report_gen.lotbased_wip_txn_rec_type
2104 ) IS
2105 l_user_name VARCHAR2(240);
2106 l_wip_entity_name VARCHAR2(240);
2107 l_item_no VARCHAR2(240);
2108
2109 cursor get_move_txn Is
2110 select *
2111 from wsm_wip_lot_txns_v
2112 Where object_id = p_lotbased_wip_txn_rec.object_id;
2113
2114 cursor get_item_no (p_item_id IN NUMBER
2115 , p_organization_id IN NUMBER
2116 )
2117 is
2118 Select concatenated_segments
2119 from mtl_system_items_kfv
2120 Where inventory_item_id = p_item_id
2121 and organization_id = p_organization_id
2122 ;
2123 Begin
2124 debug('Write Lot Based WIP Txns info');
2125
2126 Open get_move_txn;
2127 Loop
2128
2129 fetch get_move_txn into p_lotbased_wip_txn_rec;
2130 Exit when get_move_txn %NOTFOUND;
2131
2132 Select user_name
2133 into l_user_name
2134 From fnd_user
2135 Where user_id = p_lotbased_wip_txn_rec.created_by
2136 ;
2137
2138 write_group_begin('lotbased_wip_txn');
2139
2140 XML_write('transaction_date', p_lotbased_wip_txn_rec.transaction_date);
2141 XML_write('transaction_type', p_lotbased_wip_txn_rec.transaction_type);
2142 XML_write('prev_wip_entity_name', p_lotbased_wip_txn_rec.prev_wip_entity_name);
2143 XML_write('prev_start_quantity', p_lotbased_wip_txn_rec.prev_start_quantity);
2144 XML_write('prev_alt_routing_designator', p_lotbased_wip_txn_rec.prev_alt_routing_designator);
2145
2146 Open get_item_no(g_organization_id, p_lotbased_wip_txn_rec.prev_primary_item_id);
2147 Fetch get_item_no Into l_item_no;
2148 Close get_item_no;
2149
2150 XML_write('prev_primary_item_no', l_item_no);
2151
2152 XML_write('chg_wip_entity_name', p_lotbased_wip_txn_rec.chg_wip_entity_name);
2153 XML_write('chg_start_quantity', p_lotbased_wip_txn_rec.chg_start_quantity);
2154 XML_write('chg_alt_routing_designator', p_lotbased_wip_txn_rec.chg_alt_routing_designator);
2155
2156 Open get_item_no(g_organization_id, p_lotbased_wip_txn_rec.chg_primary_item_id);
2157 Fetch get_item_no Into l_item_no;
2158 Close get_item_no;
2159
2160 XML_write('chg_primary_item_no', l_item_no);
2161 XML_write('created_by', l_user_name);
2162 XML_write('transaction_id', p_lotbased_wip_txn_rec.transaction_id);
2163
2164 write_group_end('lotbased_wip_txn');
2165 End loop;
2166
2167 End write_lotbased_wip_txn_info;
2168
2169 Procedure write_move_txn_info
2170 ( p_move_txn_rec IN OUT NOCOPY inv_genealogy_report_gen.move_txn_rec_type
2171 ) IS
2172 l_user_name VARCHAR2(240);
2173 l_wip_entity_name VARCHAR2(240);
2174 l_item_no VARCHAR2(240);
2175
2176 cursor get_move_txn Is
2177 select transaction_date
2178 , wip_entity_name
2179 , fm_operation_seq_num
2180 , fm_operation_code
2181 , fm_department_code
2182 , fm_intraoperation_step_meaning
2183 , to_operation_seq_num
2184 , to_operation_code
2185 , to_department_code
2186 , to_intraoperation_step_meaning
2187 , transaction_uom
2188 , transaction_quantity
2189 , primary_uom
2190 , primary_quantity
2191 , overcompletion_transaction_qty
2192 , overcompletion_primary_qty
2193 from wip_move_transactions_v
2194 Where wip_entity_id = p_move_txn_rec.wip_entity_id
2195 and organization_id = p_move_txn_rec.organization_id;
2196
2197 cursor get_item_no (p_item_id IN NUMBER
2198 , p_organization_id IN NUMBER
2199 )
2200 is
2201 Select concatenated_segments
2202 from mtl_system_items_kfv
2203 Where inventory_item_id = p_item_id
2204 and organization_id = p_organization_id
2205 ;
2206 Begin
2207 debug('Write Move Txns info');
2208
2209 Open get_move_txn;
2210 Loop
2211 fetch get_move_txn
2212 into p_move_txn_rec.transaction_date
2213 , p_move_txn_rec.Job
2214 , p_move_txn_rec.From_Seq
2215 , p_move_txn_rec.From_Code
2216 , p_move_txn_rec.From_Department
2217 , p_move_txn_rec.From_Step
2218 , p_move_txn_rec.To_Seq
2219 , p_move_txn_rec.To_Code
2220 , p_move_txn_rec.To_Department
2221 , p_move_txn_rec.To_Step
2222 , p_move_txn_rec.Transaction_UOM
2223 , p_move_txn_rec.Transaction_Quantity
2224 , p_move_txn_rec.Primary_UOM
2225 , p_move_txn_rec.Primary_Quantity
2226 , p_move_txn_rec.Over_Cplt_Txn_Qty
2227 , p_move_txn_rec.Over_Cplt_Primary_Qty
2228 ;
2229 Exit when get_move_txn %NOTFOUND;
2230
2231 write_group_begin('move_transactions');
2232
2233 XML_write('transaction_date', p_move_txn_rec.transaction_date);
2234 XML_write('job', p_move_txn_rec.job);
2235 XML_write('assembly', p_move_txn_rec.assembly);
2236 XML_write('from_seq', p_move_txn_rec.from_seq);
2237 XML_write('from_code', p_move_txn_rec.from_code);
2238 XML_write('from_department', p_move_txn_rec.from_department);
2239 XML_write('from_step', p_move_txn_rec.from_step);
2240 XML_write('to_seq', p_move_txn_rec.to_seq);
2241 XML_write('to_code', p_move_txn_rec.to_code);
2242 XML_write('to_department', p_move_txn_rec.to_department);
2243 XML_write('to_step', p_move_txn_rec.to_step);
2244 XML_write('transaction_uom', p_move_txn_rec.transaction_uom);
2245 XML_write('transaction_quantity', p_move_txn_rec.transaction_quantity);
2246 XML_write('primary_uom', p_move_txn_rec.primary_uom);
2247 XML_write('primary_quantity', p_move_txn_rec.primary_quantity);
2248 XML_write('over_cplt_txn_qty', p_move_txn_rec.over_cplt_txn_qty);
2249 XML_write('over_cplt_primary_qty', p_move_txn_rec.over_cplt_primary_qty);
2250
2251 write_group_end('move_transactions');
2252
2253 End loop;
2254
2255 End write_move_txn_info;
2256
2257 Procedure write_grade_status_info
2258 ( p_grade_status_rec IN OUT NOCOPY inv_genealogy_report_gen.grade_status_rec_type
2259 ) IS
2260
2261 l_user_id NUMBER;
2262 cursor get_grade_status is
2263 select date_stamp
2264 , action
2265 , old_value
2266 , new_value
2267 , primary_quantity
2268 , secondary_quantity
2269 , change_reason
2270 , user_id
2271 from mtl_grd_sts_history_v
2272 where inventory_item_id = p_grade_status_rec.inventory_item_id
2273 and organization_id = p_grade_status_rec.current_org_id
2274 and lot_number = p_grade_status_rec.lot_number
2275 ;
2276 Begin
2277 debug('Write grade status info');
2278
2279 Open get_grade_status;
2280 Loop
2281 Fetch get_grade_status
2282 Into p_grade_status_rec.date_time
2283 , p_grade_status_rec.action
2284 , p_grade_status_rec.from_value
2285 , p_grade_status_rec.to_value
2286 , p_grade_status_rec.quantity
2287 , p_grade_status_rec.secondary_quantity
2288 , p_grade_status_rec.reason
2289 , l_user_id
2290 ;
2291 Exit when get_grade_status %NOTFOUND;
2292 Select user_name
2293 into p_grade_status_rec.user
2294 From fnd_user
2295 Where user_id = l_user_id;
2296
2297 write_group_begin('grade_status_changes');
2298 XML_write('organization', p_grade_status_rec.organization);
2299 XML_write('date_time', p_grade_status_rec.date_time);
2300 XML_write('action', p_grade_status_rec.action);
2301 XML_write('from_value', p_grade_status_rec.from_value);
2302 XML_write('to_value', p_grade_status_rec.to_value);
2303 XML_write('quantity', p_grade_status_rec.quantity);
2304 XML_write('uom', p_grade_status_rec.uom);
2305 XML_write('secondary_quantity', p_grade_status_rec.secondary_quantity);
2306 XML_write('secondary_uom', p_grade_status_rec.secondary_uom);
2307 XML_write('source', p_grade_status_rec.source);
2308 XML_write('reason', p_grade_status_rec.reason);
2309 XML_write('user', p_grade_status_rec.user);
2310 write_group_end('grade_status_changes');
2311 End Loop;
2312 Close get_grade_status;
2313
2314 End write_grade_status_info;
2315
2316 procedure get_all_children
2317 ( p_object_id IN NUMBER
2318 , p_object_type IN NUMBER
2319 , p_object_id2 IN NUMBER
2320 , p_object_type2 IN NUMBER
2321 ) IS
2322
2323 l_node_name VARCHAR2(100);
2324 l_object_id NUMBER;
2325 l_object_type NUMBER;
2326 l_current_object_id NUMBER;
2327 l_level NUMBER;
2328
2329 l_statement VARCHAR2(2000);
2330 l_query VARCHAR2(2000);
2331
2332 type rc is ref cursor;
2333 l_rec_query rc;
2334
2335 Cursor get_child is
2336 SELECT object_id
2337 , inv_object_genealogy.getobjectnumber(object_id, object_type, object_id2, object_type2)
2338 FROM mtl_object_genealogy
2339 WHERE parent_object_id = l_object_id
2340 ;
2341
2342 Begin
2343 debug('Get All Children');
2344 l_node_name := inv_object_genealogy.getobjectnumber
2345 (p_object_id, p_object_type, p_object_id2, p_object_type2);
2346 l_object_id := p_object_id;
2347 l_object_type := p_object_type;
2348
2349 debug('Main level Node '||l_node_name);
2350
2351 /*write_group_begin('tree_node_main_level');
2352 XML_write('main_level', l_node_name);
2353
2354 write_tree_node (p_object_id, p_object_type, p_object_id2, p_object_type2, 1, 1);
2355
2356 write_group_end('tree_node_main_level');
2357
2358 -- for the query item
2359 write_genealogy_report
2360 ( p_object_id => l_object_id
2361 , p_object_type => l_object_type
2362 , p_object_id2 => l_object_id2
2363 , p_object_type2 => l_object_type2
2364 , p_level => 2
2365 );
2366 */
2367 -- for the children
2368 write_children_reports
2369 ( p_object_id => l_object_id
2370 , p_object_type => l_object_type
2371 , p_object_id2 => p_object_id2
2372 , p_object_type2 => p_object_type2
2373 , p_level => 0
2374 );
2375
2376 End get_all_children;
2377
2378 procedure get_one_level_child
2379 ( p_object_id IN NUMBER
2380 , p_object_type IN NUMBER
2381 , p_object_id2 IN NUMBER
2382 , p_object_type2 IN NUMBER
2383 , p_level IN NUMBER -- 0, main level
2384 ) IS
2385
2386 l_node_name VARCHAR2(100);
2387 l_object_id NUMBER;
2388 l_object_id2 NUMBER;
2389 l_object_type NUMBER;
2390 l_object_type2 NUMBER;
2391 l_current_object_id NUMBER;
2392 l_level NUMBER;
2393 l_fake_object_id2 NUMBER;
2394
2395 l_statement VARCHAR2(2000);
2396 l_query VARCHAR2(2000);
2397 l_child_rg_name VARCHAR2(2000);
2398 l_security Number;
2399 l_allow_security VARCHAR2(5);
2400 x_return_status VARCHAR2(5);
2401
2402 type rc is ref cursor;
2403 l_child_query rc;
2404
2405 Cursor get_child is
2406 SELECT object_id
2407 , object_type
2408 , object_id2
2409 , object_type2
2410 , inv_object_genealogy.getobjectnumber(object_id, object_type, object_id2, object_type2)
2411 FROM mtl_object_genealogy
2412 WHERE (end_date_active IS NULL OR end_date_active >= SYSDATE)
2413 CONNECT BY PRIOR object_id = parent_object_id
2414 START WITH (parent_object_id2 IS NULL OR parent_object_id2 = l_object_id2)
2415 AND parent_object_id = l_object_id
2416 ;
2417 Cursor get_gme_security IS
2418 Select 1
2419 From wip_entities wip
2420 , gme_batch_header_vw gme
2421 Where wip.gen_object_id = p_object_id
2422 and wip.entity_type in (9, 10)
2423 and wip.wip_entity_id = gme.batch_id
2424 ;
2425
2426 Begin
2427 debug('Get one level child, level '||p_level);
2428 l_current_object_id := p_object_id;
2429 l_object_id := p_object_id;
2430 l_object_type := p_object_type;
2431 l_object_id2 := p_object_id2;
2432 l_object_type2 := p_object_type2;
2433 l_level := p_level;
2434
2435 l_fake_object_id2 := l_object_id2;
2436 if l_fake_object_id2 is null then
2437 l_fake_object_id2 := -9999;
2438 end if;
2439
2440 if l_object_type <> 5 then
2441 l_query := ' SELECT object_id '
2442 ||' , object_type'
2443 ||' , object_id2'
2444 ||' , object_type2'
2445 ||' , inv_object_genealogy.getobjectnumber(object_id, object_type, object_id2, object_type2)'
2446 ||' FROM mtl_object_genealogy'
2447 ||' WHERE (end_date_active IS NULL OR end_date_active >= SYSDATE)'
2448 ||' and decode(parent_object_id2,null, -9999, parent_object_id2) = '||l_fake_object_id2
2449 ||' AND parent_object_id = '||l_object_id
2450 --||' CONNECT BY PRIOR object_id = parent_object_id'
2451 --||' START WITH decode(parent_object_id2,null, -9999, parent_object_id2) = '||l_fake_object_id2
2452 --||' AND parent_object_id = '||l_object_id
2453 ;
2454 /* It was decided that the direct job link should also be included */
2455 /*l_child_rg_name := inv_object_genealogy.findchildrecordgroup(l_object_id);
2456 if l_child_rg_name = 'CHILD_INFO_DGEN' THEN
2457 l_query := l_query || ' and object_type <> 5';
2458 end if;
2459 */
2460 else
2461 l_query := 'SELECT object_id '
2462 ||' , object_type '
2463 ||' , object_id2 '
2464 ||' , object_type2 '
2465 ||' , inv_object_genealogy.getobjectnumber(object_id, object_type, object_id2, object_type2)'
2466 ||' FROM mtl_object_genealogy '
2467 ||' WHERE parent_object_id = '||l_object_id
2468 ||' AND(end_date_active IS NULL '
2469 ||' OR TRUNC(end_date_active) >= TRUNC(SYSDATE)) '
2470 ;
2471 end if;
2472
2473 /* check gme formula security, no expansion what so ever, if security is on*/
2474 l_security := 1; -- security is off for all
2475 if( l_object_type = 5) then
2476 l_security := 0;
2477 GMD_API_GRP.fetch_parm_values(g_organization_id,'GMI_LOTGENE_ENABLE_FMSEC',l_allow_security,x_return_status);
2478 if (l_allow_security = '1' or l_allow_security = 'Y') then
2479 Open get_gme_security;
2480 Fetch get_gme_security into l_security;
2481 Close get_gme_security;
2482 end if;
2483 debug(' GME security: '|| l_security);
2484 end if;
2485
2486 --debug(' child query '||l_query);
2487
2488 if l_security = 1 Then
2489 Open l_child_query for l_query;
2490 Loop
2491
2492 Fetch l_child_query
2493 Into l_object_id
2494 , l_object_type
2495 , l_object_id2
2496 , l_object_type2
2497 , l_node_name
2498 ;
2499 --if l_level <> p_level then
2500 -- write_group_end('tree_node_level'||(l_level-1));
2501 --end if;
2502 Exit When l_child_query %NOTFOUND;
2503
2504 debug(' drill down the tree, level '||l_level||' '||l_node_name||' Object_type '||l_object_type||' object_id '||l_object_id);
2505
2506 l_node_name := inv_object_genealogy.getobjectnumber
2507 (l_object_id, l_object_type, l_object_id2, l_object_type2);
2508
2509 write_group_begin('tree_node_level'||l_level);
2510 XML_write('level'||l_level, l_node_name);
2511 Get_one_level_child(l_object_id, l_object_type, l_object_id2, l_object_type2, l_level + 1);
2512 write_group_end('tree_node_level'||l_level);
2513
2514 End loop;
2515 close l_child_query;
2516 end if;
2517
2518 End get_one_level_child;
2519
2520 procedure write_children_reports
2521 ( p_object_id IN NUMBER
2522 , p_object_type IN NUMBER
2523 , p_object_id2 IN NUMBER
2524 , p_object_type2 IN NUMBER
2525 , p_level IN NUMBER -- 0, main level
2526 ) IS
2527
2528 l_node_name VARCHAR2(100);
2529 l_object_id NUMBER;
2530 l_object_id2 NUMBER;
2531 l_object_type NUMBER;
2532 l_object_type2 NUMBER;
2533 l_current_object_id NUMBER;
2534 l_level NUMBER;
2535 l_fake_object_id2 NUMBER;
2536
2537 l_statement VARCHAR2(2000);
2538 l_query VARCHAR2(2000);
2539 l_child_rg_name VARCHAR2(2000);
2540 l_security Number;
2541 l_allow_security VARCHAR2(5);
2542 x_return_status VARCHAR2(5);
2543
2544 type rc is ref cursor;
2545 l_child_query rc;
2546
2547 Cursor get_child is
2548 SELECT object_id
2549 , object_type
2550 , object_id2
2551 , object_type2
2552 , inv_object_genealogy.getobjectnumber(object_id, object_type, object_id2, object_type2)
2553 FROM mtl_object_genealogy
2554 WHERE parent_object_id = l_object_id
2555 ;
2556 Cursor get_gme_security IS
2557 Select 1
2558 From wip_entities wip
2559 , gme_batch_header_vw gme
2560 Where wip.gen_object_id = p_object_id
2561 and wip.entity_type in (9, 10)
2562 and wip.wip_entity_id = gme.batch_id
2563 ;
2564 Begin
2565 debug(' Write children reports');
2566 l_node_name := inv_object_genealogy.getobjectnumber
2567 (p_object_id, p_object_type, p_object_id2, p_object_type2);
2568 l_current_object_id := p_object_id;
2569 l_object_id := p_object_id;
2570 l_object_type := p_object_type;
2571 l_object_id2 := p_object_id2;
2572 l_object_type2 := p_object_type2;
2573 --l_level := p_level + 1;
2574 l_level := p_level ;
2575
2576 -- write the tree from this object's all children
2577 write_tree_node(l_object_id, l_object_type, l_object_id2, l_object_type2, l_level, 1);
2578
2579 l_fake_object_id2 := l_object_id2;
2580 if l_fake_object_id2 is null then
2581 l_fake_object_id2 := -9999;
2582 end if;
2583
2584 if l_object_type <> 5 then
2585 l_query := ' SELECT object_id '
2586 ||' , object_type'
2587 ||' , object_id2'
2588 ||' , object_type2'
2589 ||' , inv_object_genealogy.getobjectnumber(object_id, object_type, object_id2, object_type2)'
2590 ||' FROM mtl_object_genealogy'
2591 ||' WHERE (end_date_active IS NULL OR end_date_active >= SYSDATE)'
2592 ||' and decode(parent_object_id2,null, -9999, parent_object_id2) = '||l_fake_object_id2
2593 ||' AND parent_object_id = '||l_object_id
2594 --||' CONNECT BY PRIOR object_id = parent_object_id'
2595 --||' START WITH decode(parent_object_id2,null, -9999, parent_object_id2) = '||l_fake_object_id2
2596 --||' AND parent_object_id = '||l_object_id
2597 ;
2598 /*l_child_rg_name := inv_object_genealogy.findchildrecordgroup(l_object_id);
2599 if l_child_rg_name = 'CHILD_INFO_DGEN' THEN
2600 l_query := l_query || ' and object_type <> 5';
2601 end if;
2602 */
2603 else
2604 l_query := 'SELECT object_id '
2605 ||' , object_type '
2606 ||' , object_id2 '
2607 ||' , object_type2 '
2608 ||' , inv_object_genealogy.getobjectnumber(object_id, object_type, object_id2, object_type2)'
2609 ||' FROM mtl_object_genealogy '
2610 ||' WHERE parent_object_id = '||l_object_id
2611 ||' AND(end_date_active IS NULL '
2612 ||' OR TRUNC(end_date_active) >= TRUNC(SYSDATE)) '
2613 ;
2614 end if;
2615
2616 /* check gme formula security, no expansion what so ever, if security is on*/
2617 l_security := 1; -- security is off for all
2618 if( l_object_type = 5) then
2619 l_security := 0;
2620 GMD_API_GRP.fetch_parm_values(g_organization_id,'GMI_LOTGENE_ENABLE_FMSEC',l_allow_security,x_return_status);
2621 if (l_allow_security = '1' or l_allow_security = 'Y') then
2622 Open get_gme_security;
2623 Fetch get_gme_security into l_security;
2624 Close get_gme_security;
2625 end if;
2626 debug(' GME security: '|| l_security);
2627 end if;
2628 --debug(' child query '||l_query);
2629
2630 if l_security = 1 Then -- security is allowed
2631 Open l_child_query for l_query;
2632 Loop
2633
2634 Fetch l_child_query
2635 Into l_object_id
2636 , l_object_type
2637 , l_object_id2
2638 , l_object_type2
2639 , l_node_name
2640 ;
2641 Exit When l_child_query %NOTFOUND;
2642
2643 debug(' Write children reports: drill down the tree, level '||l_level||' '||l_node_name);
2644 write_genealogy_report
2645 ( p_object_id => l_object_id
2646 , p_object_type => l_object_type
2647 , p_object_id2 => l_object_id2
2648 , p_object_type2 => l_object_type2
2649 , p_level => l_level
2650 );
2651 write_children_reports(l_object_id, l_object_type, l_object_id2, l_object_type2, l_level);
2652 End loop;
2653 close l_child_query;
2654 end if; -- security
2655
2656 End write_children_reports;
2657
2658 procedure get_all_parents
2659 ( p_object_id IN NUMBER
2660 , p_object_type IN NUMBER
2661 , p_object_id2 IN NUMBER
2662 , p_object_type2 IN NUMBER
2663 ) IS
2664
2665 l_node_name VARCHAR2(100);
2666 l_object_id NUMBER;
2667 l_object_type NUMBER;
2668 l_current_object_id NUMBER;
2669 l_level NUMBER;
2670
2671 l_statement VARCHAR2(2000);
2672 l_query VARCHAR2(2000);
2673
2674 type rc is ref cursor;
2675 l_rec_query rc;
2676
2677 Begin
2678 debug('Get All Parents');
2679 l_node_name := inv_object_genealogy.getobjectnumber
2680 (p_object_id, p_object_type, p_object_id2, p_object_type2);
2681 l_object_id := p_object_id;
2682 l_object_type := p_object_type;
2683
2684 debug('Main level Node '||l_node_name);
2685
2686 -- for the parents
2687 write_parent_reports
2688 ( p_object_id => l_object_id
2689 , p_object_type => l_object_type
2690 , p_object_id2 => p_object_id2
2691 , p_object_type2 => p_object_type2
2692 , p_level => 0
2693 );
2694 End get_all_parents;
2695
2696 procedure get_one_level_parent
2697 ( p_parent_object_id IN NUMBER
2698 , p_parent_object_type IN NUMBER
2699 , p_parent_object_id2 IN NUMBER
2700 , p_parent_object_type2 IN NUMBER
2701 , p_level IN NUMBER -- 0, main level
2702 ) IS
2703
2704 l_node_name VARCHAR2(100);
2705 l_parent_object_id NUMBER;
2706 l_parent_object_id2 NUMBER;
2707 l_parent_object_type NUMBER;
2708 l_parent_object_type2 NUMBER;
2709 l_current_parent_object_id NUMBER;
2710 l_level NUMBER;
2711 l_fake_object_id2 NUMBER;
2712
2713 l_statement VARCHAR2(2000);
2714 l_query VARCHAR2(2000);
2715 l_parent_rg_name VARCHAR2(2000);
2716 l_security Number;
2717 l_allow_security VARCHAR2(5);
2718 x_return_status VARCHAR2(5);
2719
2720 type rc is ref cursor;
2721 l_parent_query rc;
2722
2723 Cursor get_parent is
2724 SELECT parent_object_id
2725 , parent_object_type
2726 , parent_object_id2
2727 , parent_object_type2
2728 , inv_object_genealogy.getobjectnumber(parent_object_id, parent_object_type, parent_object_id2, parent_object_type2)
2729 FROM mtl_object_genealogy
2730 WHERE object_id = l_parent_object_id
2731 ;
2732 Cursor get_gme_security IS
2733 Select 1
2734 From wip_entities wip
2735 , gme_batch_header_vw gme
2736 Where wip.gen_object_id = l_parent_object_id
2737 and wip.entity_type in (9, 10)
2738 and wip.wip_entity_id = gme.batch_id
2739 ;
2740
2741 Begin
2742 debug('Get one level parent, level '||p_level);
2743 l_node_name := inv_object_genealogy.getobjectnumber
2744 (p_parent_object_id, p_parent_object_type, p_parent_object_id2, p_parent_object_type2);
2745 l_current_parent_object_id := p_parent_object_id;
2746 l_parent_object_id := p_parent_object_id;
2747 l_parent_object_type := p_parent_object_type;
2748 l_parent_object_id2 := p_parent_object_id2;
2749 l_parent_object_type2 := p_parent_object_type2;
2750 l_level := p_level;
2751 l_fake_object_id2 := l_parent_object_id2;
2752 if l_fake_object_id2 is null then
2753 l_fake_object_id2 := -9999;
2754 end if;
2755
2756 write_group_begin('tree_node_level'||l_level);
2757
2758 if l_parent_object_type <> 5 then
2759 l_query := ' SELECT parent_object_id '
2760 ||' , parent_object_type'
2761 ||' , parent_object_id2'
2762 ||' , parent_object_type2'
2763 ||' , inv_object_genealogy.getobjectnumber(parent_object_id, parent_object_type, parent_object_id2, parent_object_type2)'
2764 ||' FROM mtl_object_genealogy'
2765 ||' WHERE (end_date_active IS NULL OR end_date_active >= SYSDATE)'
2766 ||' and decode(object_id2,null, -9999, object_id2) = '||l_fake_object_id2
2767 ||' AND object_id = '||l_parent_object_id
2768 --||' CONNECT BY object_id = PRIOR parent_object_id '
2769 --||' START WITH decode(object_id2,null, -9999, object_id2) = '||l_fake_object_id2
2770 --||' AND object_id = '||l_parent_object_id
2771 ;
2772 /*l_parent_rg_name := inv_object_genealogy.findchildrecordgroup(l_parent_object_id);
2773 if l_parent_rg_name = 'CHILD_INFO_DGEN' THEN
2774 l_query := l_query || ' and parent_object_type <> 5';
2775 end if;
2776 */
2777 else
2778 l_query := ' SELECT parent_object_id '
2779 ||' , parent_object_type '
2780 ||' , parent_object_id2 '
2781 ||' , parent_object_type2 '
2782 ||' , inv_object_genealogy.getobjectnumber(parent_object_id, parent_object_type, parent_object_id2, parent_object_type2)'
2783 ||' FROM mtl_object_genealogy '
2784 ||' WHERE object_id = '||l_parent_object_id
2785 ||' AND(end_date_active IS NULL '
2786 ||' OR TRUNC(end_date_active) >= TRUNC(SYSDATE)) '
2787 ;
2788 end if;
2789 /* check gme formula security, no expansion what so ever, if security is on*/
2790 l_security := 1; -- security is off for all
2791 if( l_parent_object_type = 5) then
2792 l_security := 0;
2793 GMD_API_GRP.fetch_parm_values(g_organization_id,'GMI_LOTGENE_ENABLE_FMSEC',l_allow_security,x_return_status);
2794 if (l_allow_security = '1' or l_allow_security = 'Y') then
2795 Open get_gme_security;
2796 Fetch get_gme_security into l_security;
2797 Close get_gme_security;
2798 end if;
2799 debug(' GME security: '|| l_security);
2800 end if;
2801 --debug(' parent query '||l_query);
2802
2803 if l_security = 1 Then
2804 Open l_parent_query for l_query ;
2805 Loop
2806
2807 Fetch l_parent_query
2808 Into l_parent_object_id
2809 , l_parent_object_type
2810 , l_parent_object_id2
2811 , l_parent_object_type2
2812 , l_node_name
2813 ;
2814 Exit When l_parent_query %NOTFOUND;
2815
2816 debug(' drill down the tree, level '||l_level||' '||l_node_name);
2817
2818 XML_write('level'||l_level, l_node_name);
2819 Get_one_level_parent(l_parent_object_id, l_parent_object_type, l_parent_object_id2, l_parent_object_type2, l_level + 1);
2820 End loop;
2821 close l_parent_query;
2822 end if;
2823
2824 write_group_end('tree_node_level'||l_level);
2825
2826 End get_one_level_parent;
2827
2828 procedure write_parent_reports
2829 ( p_object_id IN NUMBER
2830 , p_object_type IN NUMBER
2831 , p_object_id2 IN NUMBER
2832 , p_object_type2 IN NUMBER
2833 , p_level IN NUMBER -- 0, main level
2834 ) IS
2835
2836 l_node_name VARCHAR2(100);
2837 l_object_id NUMBER;
2838 l_object_id2 NUMBER;
2839 l_object_type NUMBER;
2840 l_object_type2 NUMBER;
2841 l_current_object_id NUMBER;
2842 l_level NUMBER;
2843 l_fake_object_id2 NUMBER;
2844
2845 l_statement VARCHAR2(2000);
2846 l_query VARCHAR2(2000);
2847 l_parent_rg_name VARCHAR2(2000);
2848 l_security Number;
2849 l_allow_security VARCHAR2(5);
2850 x_return_status VARCHAR2(5);
2851
2852 type rc is ref cursor;
2853 l_parent_query rc;
2854
2855 Cursor get_gme_security IS
2856 Select 1
2857 From wip_entities wip
2858 , gme_batch_header_vw gme
2859 Where wip.gen_object_id = p_object_id
2860 and wip.entity_type in (9, 10)
2861 and wip.wip_entity_id = gme.batch_id
2862 ;
2863
2864 Begin
2865 debug(' Write Parents Reports');
2866 l_node_name := inv_object_genealogy.getobjectnumber
2867 (p_object_id, p_object_type, p_object_id2, p_object_type2);
2868 l_current_object_id := p_object_id;
2869 l_object_id := p_object_id;
2870 l_object_type := p_object_type;
2871 l_object_id2 := p_object_id2;
2872 l_object_type2 := p_object_type2;
2873 --l_level := p_level + 1;
2874 l_level := p_level ;
2875
2876 -- write the tree from this object's all parents
2877 write_tree_node(l_object_id, l_object_type, l_object_id2, l_object_type2, l_level, 2);
2878
2879 l_fake_object_id2 := l_object_id2;
2880 if l_fake_object_id2 is null then
2881 l_fake_object_id2 := -9999;
2882 end if;
2883
2884 if l_object_type <> 5 then
2885 l_query := ' SELECT parent_object_id '
2886 ||' , parent_object_type'
2887 ||' , parent_object_id2'
2888 ||' , parent_object_type2'
2889 ||' , inv_object_genealogy.getobjectnumber(parent_object_id, parent_object_type, parent_object_id2, parent_object_type2)'
2890 ||' FROM mtl_object_genealogy'
2891 ||' WHERE (end_date_active IS NULL OR end_date_active >= SYSDATE)'
2892 ||' and decode(object_id2,null, -9999, object_id2) = '||l_fake_object_id2
2893 ||' AND object_id = '||l_object_id
2894 --||' CONNECT BY object_id = PRIOR parent_object_id '
2895 --||' START WITH decode(object_id2,null, -9999, object_id2) = '||l_fake_object_id2
2896 --||' AND object_id = '||l_object_id
2897 ;
2898 /*l_parent_rg_name := inv_object_genealogy.findchildrecordgroup(l_object_id);
2899 if l_parent_rg_name = 'CHILD_INFO_DGEN' THEN
2900 l_query := l_query || ' and parent_object_type <> 5';
2901 end if;
2902 */
2903 else
2904 l_query := ' SELECT parent_object_id '
2905 ||' , parent_object_type '
2906 ||' , parent_object_id2 '
2907 ||' , parent_object_type2 '
2908 ||' , inv_object_genealogy.getobjectnumber(parent_object_id, parent_object_type, parent_object_id2, parent_object_type2)'
2909 ||' FROM mtl_object_genealogy '
2910 ||' WHERE object_id = '||l_object_id
2911 ||' AND(end_date_active IS NULL '
2912 ||' OR TRUNC(end_date_active) >= TRUNC(SYSDATE)) '
2913 ;
2914 end if;
2915 /*
2916 l_query := ' SELECT parent_object_id '
2917 ||' , parent_object_type'
2918 ||' , parent_object_id2'
2919 ||' , parent_object_type2'
2920 ||' , inv_object_genealogy.getobjectnumber(parent_object_id, parent_object_type, parent_object_id2, parent_object_type2)'
2921 ||' FROM mtl_object_genealogy'
2922 ||' WHERE (end_date_active IS NULL OR end_date_active >= SYSDATE)'
2923 ||' CONNECT BY object_id = PRIOR parent_object_id '
2924 ||' START WITH decode(object_id2,null, -9999, object_id2) = '||l_fake_object_id2
2925 ||' AND object_id = '||l_parent_object_id
2926 ;
2927 l_parent_rg_name := inv_object_genealogy.findchildrecordgroup(l_parent_object_id);
2928 if l_parent_rg_name = 'CHILD_INFO_DGEN' THEN
2929 l_query := l_query || ' and parent_object_type <> 5';
2930 end if;
2931 */
2932 /* check gme formula security, no expansion what so ever, if security is on*/
2933 l_security := 1; -- security is off for all
2934 if( l_object_type = 5) then
2935 l_security := 0;
2936 GMD_API_GRP.fetch_parm_values(g_organization_id,'GMI_LOTGENE_ENABLE_FMSEC',l_allow_security,x_return_status);
2937 if (l_allow_security = '1' or l_allow_security = 'Y') then
2938 Open get_gme_security;
2939 Fetch get_gme_security into l_security;
2940 Close get_gme_security;
2941 end if;
2942 debug(' GME security: '|| l_security);
2943 end if;
2944
2945 --debug(' parent query '||l_query);
2946
2947 if l_security = 1 then
2948 Open l_parent_query for l_query;
2949 Loop
2950
2951 Fetch l_parent_query
2952 Into l_object_id
2953 , l_object_type
2954 , l_object_id2
2955 , l_object_type2
2956 , l_node_name
2957 ;
2958 Exit When l_parent_query %NOTFOUND;
2959
2960 debug(' Write parents reports: drill down the tree, level '||l_level||' '||l_node_name);
2961 write_genealogy_report
2962 ( p_object_id => l_object_id
2963 , p_object_type => l_object_type
2964 , p_object_id2 => l_object_id2
2965 , p_object_type2 => l_object_type2
2966 , p_level => l_level
2967 );
2968 write_parent_reports(l_object_id, l_object_type, l_object_id2, l_object_type2, l_level);
2969 End loop;
2970 close l_parent_query;
2971 end if;
2972
2973 End write_parent_reports;
2974
2975 Procedure XML_write
2976 ( column_name IN VARCHAR2
2977 , column_value IN VARCHAR2
2978 ) IS
2979
2980 l_string VARCHAR2(240);
2981 Begin
2982 l_string := '<'||column_name||'>';
2983 l_string := l_string||column_value;
2984 l_string := l_string||'</'||column_name||'>';
2985 fnd_file.put_line(FND_FILE.OUTPUT,l_string);
2986 --dbms_output.put_line(l_string);
2987 End XML_write;
2988
2989 Procedure Write_tree_node
2990 (p_object_id IN NUMBER
2991 ,p_object_type IN NUMBER
2992 ,p_object_id2 IN NUMBER
2993 ,p_object_type2 IN NUMBER
2994 ,p_level IN NUMBER -- 0 main, >0 component
2995 ,p_child_parent IN NUMBER -- 1 child, 2 parent
2996 )
2997 Is
2998
2999 l_level VARCHAR2(50);
3000 --# Sunitha Ch. 21jun06. Bug#5312854. Changed the size of l_node_name to 100 from 50
3001 --l_node_name VARCHAR2(50);
3002 l_node_name VARCHAR2(100);
3003 l_object_id NUMBER;
3004 l_count NUMBER;
3005
3006 Begin
3007 debug('Write the tree node, level '||p_level);
3008 if p_level = 0 then
3009 write_group_begin('tree_node_main_level');
3010 l_node_name := inv_object_genealogy.getobjectnumber
3011 (p_object_id, p_object_type, p_object_id2, p_object_type2);
3012 XML_write('main_level', l_node_name);
3013 else
3014 l_count := 0 ;
3015 /*loop
3016 l_count := l_count + 1 ;
3017 exit when l_count = p_level;
3018 write_group_begin('tree_node_level'||l_count);
3019 end loop;
3020 */
3021 l_node_name := inv_object_genealogy.getobjectnumber
3022 (p_object_id, p_object_type, p_object_id2, p_object_type2);
3023 write_group_begin('tree_node_level'||p_level);
3024 XML_write('level'||p_level, l_node_name);
3025 end if;
3026
3027 if p_child_parent = 1 then
3028 get_one_level_child (p_object_id, p_object_type, p_object_id2, p_object_type2, p_level+1);
3029 else
3030 get_one_level_parent (p_object_id, p_object_type, p_object_id2, p_object_type2, p_level+1);
3031 end if;
3032
3033 if p_level = 0 then
3034 write_group_end('tree_node_main_level');
3035 null;
3036 else
3037 write_group_end('tree_node_level'||p_level);
3038
3039 l_count := p_level ;
3040 /*
3041 loop
3042 l_count := l_count - 1 ;
3043 exit when l_count = 0;
3044 write_group_end('tree_node_level'||l_count);
3045 end loop;
3046 */
3047 end if;
3048
3049 End write_tree_node;
3050
3051 Procedure write_group_begin
3052 (column_name IN VARCHAR2
3053 ) IS
3054 l_string VARCHAR2(240);
3055 Begin
3056 l_string :='';
3057 l_string := l_string||'<'||column_name||'>';
3058 fnd_file.put_line(FND_FILE.OUTPUT,l_string);
3059 --dbms_output.put_line(l_string);
3060 End write_group_begin;
3061
3062 Procedure write_group_end
3063 (column_name IN VARCHAR2
3064 ) IS
3065 l_string VARCHAR2(240);
3066 Begin
3067 l_string := '</'||column_name||'>';
3068 fnd_file.put_line(FND_FILE.OUTPUT,l_string);
3069 --dbms_output.put_line(l_string);
3070 End write_group_end;
3071
3072 END;