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