1 PACKAGE BODY INV_ATTACHMENTS_UTILS AS
2 /* $Header: INVATCHB.pls 120.1 2005/06/15 11:49:20 appldev $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'INV_ATTACHMENTS_UTILS';
5
6 PROCEDURE print_debug(p_err_msg VARCHAR2,
7 p_level NUMBER default 4)
8 IS
9 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
10 BEGIN
11 IF (l_debug = 1) THEN
12 inv_mobile_helper_functions.tracelog
13 (p_err_msg => p_err_msg,
14 p_module => 'INV_ATTACHMENTS_UTILS',
15 p_level => p_level);
16 END IF;
17
18
19 /* dbms_output.put_line(p_err_msg); */
20 END print_debug;
21
22 /*
23 ** -------------------------------------------------------------------------
24 ** Procedure: get_item_and_catgy_attachments
25 ** Description:
26 ** Output:
27 ** x_return_status
28 ** return status indicating success, error, unexpected error
29 ** x_msg_count
30 ** number of messages in message list
31 ** x_msg_data
32 ** if the number of messages in message list is 1, contains
33 ** message text
34 ** x_attachments_number
35 ** number of category and item attachments for given item
36 ** x_concat_attachment
37 ** concatenated string of attachments for given item
38 ** Input:
39 ** p_inventory_item_id
40 ** item whose attachment is required
41 ** p_organization_id
42 ** organization of item whose attachment is required
43 ** p_document_category
44 ** document category of attached document. this
45 ** maps to a Mobile Applications functionality
46 ** 1 - 'To Mobile Receiver'
47 ** 2 - 'To Mobile Putaway'
48 ** 3 - 'To Mobile Picker'
49 ** p_transaction_temp id
50 ** unique identifier of the transaction and is null by default.
51 **
52 ** Returns:
53 ** none
54 ** --------------------------------------------------------------------------
55 */
56
57 procedure get_item_and_catgy_attachments(
58 x_return_status OUT NOCOPY VARCHAR2
59 , x_msg_count OUT NOCOPY NUMBER
60 , x_msg_data OUT NOCOPY VARCHAR2
61 , x_attachments_number OUT NOCOPY NUMBER
62 , x_concat_attachment OUT NOCOPY VARCHAR2
63 , p_inventory_item_id IN NUMBER
64 , p_organization_id IN NUMBER
65 , p_document_category IN NUMBER
66 , p_transaction_temp_id IN NUMBER default NULL)
67 is
68
69 l_concat_attachment varchar2(2000);
70 l_short_text varchar2(2000);
71 l_long_text varchar2(2000);
72 l_attachments_number number := 0;
73 l_category_id number;
74 l_trx_source_line_id number;
75
76 /* Category - Short Text */
77 cursor category_st_cursor(
78 k_organization_id NUMBER
79 , k_inventory_item_id NUMBER
80 , k_document_category NUMBER)
81 is
82 select f.short_text
83 from fnd_attached_documents a,
84 fnd_documents b,
85 fnd_documents_vl c,
86 fnd_document_categories_vl d,
87 fnd_documents_short_text f
88 where a.ENTITY_NAME = 'MTL_CATEGORIES'
89 and a.PK1_VALUE in (select distinct e.category_id
90 from mtl_item_categories_v e
91 where e.organization_id = k_organization_id
92 and e.inventory_item_id = k_inventory_item_id)
93 and a.DOCUMENT_ID = b.document_id
94 and b.datatype_id = 1 -- short text
95 and b.category_id = d.category_id
96 and d.USER_NAME = decode(k_document_category,
97 1,'To Mobile Receiver',
98 2,'To Mobile Putaway',
99 3,'To Mobile Picker')
100 --and d.SOURCE_LANG = 'US'
101 and b.document_id = c.document_id
102 and f.media_id = c.media_id;
103
104 /* Category */
105 cursor category_cursor(
106 k_organization_id NUMBER
107 , k_inventory_item_id NUMBER)
108 is
109 select distinct category_id
110 from mtl_item_categories_v
111 where organization_id = k_organization_id
112 and inventory_item_id = k_inventory_item_id;
113
114 /* Category - Long Text */
115 cursor category_lt_cursor(
116 k_document_category NUMBER
117 , k_category_id NUMBER)
118 is
119 select f.long_text
120 from fnd_attached_documents a,
121 fnd_documents b,
122 fnd_documents_vl c,
123 fnd_document_categories_vl d,
124 fnd_documents_long_text f
125 where a.ENTITY_NAME = 'MTL_CATEGORIES'
126 and a.PK1_VALUE = k_category_id
127 and a.DOCUMENT_ID = b.document_id
128 and b.datatype_id = 2 -- long text
129 and b.category_id = d.category_id
130 and d.USER_NAME = decode(k_document_category,
131 1,'To Mobile Receiver',
132 2,'To Mobile Putaway',
133 3,'To Mobile Picker')
134 --and d.SOURCE_LANG = 'US'
135 and b.document_id = c.document_id
136 and f.media_id = c.media_id;
137
138 /* Item Attachment - Short Text */
139 cursor item_st_cursor(
140 k_organization_id NUMBER
141 , k_inventory_item_id NUMBER
142 , k_document_category NUMBER)
143 is
144 select f.short_text
145 from fnd_attached_documents a,
146 fnd_documents b,
147 fnd_documents_vl c,
148 fnd_document_categories_vl d,
149 fnd_documents_short_text f
150 where a.ENTITY_NAME = 'MTL_SYSTEM_ITEMS'
151 and a.PK1_VALUE = k_organization_id
152 and a.PK2_VALUE = k_inventory_item_id
153 and a.DOCUMENT_ID = b.document_id
154 and b.datatype_id = 1 -- short text
155 and b.category_id = d.category_id
156 and d.USER_NAME = decode(p_document_category,
157 1,'To Mobile Receiver',
158 2,'To Mobile Putaway',
159 3,'To Mobile Picker')
160 --and d.SOURCE_LANG = 'US'
161 and b.document_id = c.document_id
162 and f.media_id = c.media_id;
163
164
165
166 /* Item Attachment - Long Text */
167 cursor item_lt_cursor(
168 k_organization_id NUMBER
169 , k_inventory_item_id NUMBER
170 , k_document_category NUMBER)
171 is
172 select f.long_text
173 from fnd_attached_documents a,
174 fnd_documents b,
175 fnd_documents_vl c,
176 fnd_document_categories_vl d,
177 fnd_documents_long_text f
178 where a.ENTITY_NAME = 'MTL_SYSTEM_ITEMS'
179 and a.PK1_VALUE = k_organization_id
180 and a.PK2_VALUE = k_inventory_item_id
181 and a.DOCUMENT_ID = b.document_id
182 and b.datatype_id = 2 -- long text
183 and b.category_id = d.category_id
184 and d.USER_NAME = decode(p_document_category,
185 1,'To Mobile Receiver',
186 2,'To Mobile Putaway',
187 3,'To Mobile Picker')
188 --and d.SOURCE_LANG = 'US'
189 and b.document_id = c.document_id
190 and f.media_id = c.media_id;
191
192
193 /* Transaction Source Line Id cursor */
194
195 cursor trx_source_line_cursor(k_transaction_temp_id NUMBER)
196 is
197 select distinct trx_source_line_id
198 from mtl_material_transactions_temp
199 where transaction_source_type_id in (2,8,12)
200 and ( nvl(parent_transaction_temp_id,0) = k_transaction_temp_id
201 or
202 (transaction_temp_id = k_transaction_temp_id
203 and
204 not exists ( select 1
205 from mtl_material_transactions_temp
206 where
207 nvl(parent_transaction_temp_id,0) = k_transaction_temp_id
208 )
209 )
210 );
211
212 /* Sales Order Line Short Text cursor */
213 cursor sales_order_line_st_cursor(
214 k_trx_source_line_id NUMBER )
215 is
216 select f.short_text
217 from fnd_attached_documents a,
218 fnd_documents b,
219 fnd_documents_vl c,
220 fnd_document_categories_vl d,
221 fnd_documents_short_text f
222 where a.ENTITY_NAME = 'OE_ORDER_LINES'
223 and a.PK1_VALUE = k_trx_source_line_id
224 and a.DOCUMENT_ID = b.document_id
225 and b.datatype_id = 1 -- short text
226 and b.category_id = d.category_id
227 and d.USER_NAME = 'To Mobile Picker'
228 --and d.SOURCE_LANG = 'US'
229 and b.document_id = c.document_id
230 and f.media_id = c.media_id;
231
232 /* Sales Order Line Long Text cursor */
233
234 cursor sales_order_line_lt_cursor(
235 k_trx_source_line_id NUMBER)
236 is
237 select f.long_text
238 from fnd_attached_documents a,
239 fnd_documents b,
240 fnd_documents_vl c,
241 fnd_document_categories_vl d,
242 fnd_documents_long_text f
243 where a.ENTITY_NAME = 'OE_ORDER_LINES'
244 and a.PK1_VALUE = k_trx_source_line_id
245 and a.DOCUMENT_ID = b.document_id
246 and b.datatype_id = 2 -- long text
247 and b.category_id = d.category_id
248 and d.USER_NAME = 'To Mobile Picker'
249 --and d.SOURCE_LANG = 'US'
250 and b.document_id = c.document_id
251 and f.media_id = c.media_id;
252
253
254 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
255 begin
256 x_return_status := fnd_api.g_ret_sts_success;
257
258 IF (l_debug = 1) THEN
259 print_debug('Item ID : ' || p_inventory_item_id, 1);
260 print_debug('Org ID : ' || to_char(p_organization_id), 1);
261 print_debug('Document Category: ' || to_char(p_document_category), 1);
262 END IF;
263
264 -- Open Category Attachments Short Text cursor
265 open category_st_cursor(
266 p_organization_id
267 , p_inventory_item_id
268 , p_document_category);
269
270 IF (l_debug = 1) THEN
271 print_debug('category_st_cursor open', 1);
272 END IF;
273
274 while(1 > 0)
275 loop
276 fetch category_st_cursor into
277 l_short_text;
278
279 if category_st_cursor%notfound then
280 IF (l_debug = 1) THEN
281 print_debug('Count attach: ' || to_char(l_attachments_number), 1);
282 END IF;
283 exit;
284 end if;
285
286 l_attachments_number := l_attachments_number + 1;
287
288 l_concat_attachment := l_concat_attachment || l_short_text;
289
290 IF (l_debug = 1) THEN
291 print_debug('Count attach: ' || to_char(l_attachments_number), 1 );
292 print_debug('Text : ' || l_short_text, 1);
293 print_debug('Total Text : ' || l_concat_attachment, 1);
294 END IF;
295 end loop;
296
297 /*
298 ** Not very elegant, BUT....
299 ** To get unique category_ids for an item,
300 ** Adding category to category_lt_cursor like category_st_cursor
301 ** doesn't work. It gives invalid operation of long datatype
302 ** Hence adopting this method where we will have 2 cursors -
303 ** category cursor and category_lt_cursor
304 */
305
306 -- Open Category cursor
307 open category_cursor(
308 p_organization_id
309 , p_inventory_item_id);
310
311 IF (l_debug = 1) THEN
312 print_debug('category_cursor open', 1);
313 END IF;
314
315 while(1 > 0)
316 loop
317 fetch category_cursor into
318 l_category_id;
319
320 if category_cursor%notfound then
321 IF (l_debug = 1) THEN
322 print_debug('Count attach: ' || to_char(l_attachments_number), 1);
323 END IF;
324 exit;
325 end if;
326
327 -- Open Category Attachments Long Text cursor
328 open category_lt_cursor(
329 p_document_category
330 , l_category_id);
331
332 while(1 > 0)
333 loop
334 fetch category_lt_cursor into
335 l_long_text;
336
337 if category_lt_cursor%notfound then
338 IF (l_debug = 1) THEN
339 print_debug('Count attach: ' || to_char(l_attachments_number), 1);
340 END IF;
341 exit;
342 end if;
343
344 l_attachments_number := l_attachments_number + 1;
345
346 l_concat_attachment := l_concat_attachment || l_long_text;
347
348 IF (l_debug = 1) THEN
349 print_debug('Count attach: ' || to_char(l_attachments_number), 1);
350 print_debug('Text : ' || l_long_text, 1);
351 print_debug('Total Text : ' || l_concat_attachment, 1);
352 END IF;
353 end loop;
354
355 IF (category_lt_cursor%isopen) THEN
356 close category_lt_cursor;
357 END IF;
358 end loop;
359
360 -- Open Item Attachments Short Text cursor
361 open item_st_cursor(
362 p_organization_id
363 , p_inventory_item_id
364 , p_document_category);
365
366 IF (l_debug = 1) THEN
367 print_debug('item_st_cursor open', 1);
368 END IF;
369
370 while(1 > 0)
371 loop
372 fetch item_st_cursor into
373 l_short_text;
374
375 if item_st_cursor%notfound then
376 IF (l_debug = 1) THEN
377 print_debug('Count attach: ' || to_char(l_attachments_number), 1);
378 END IF;
379 exit;
380 end if;
381
382 l_attachments_number := l_attachments_number + 1;
383
384 l_concat_attachment := l_concat_attachment || l_short_text;
385
386 IF (l_debug = 1) THEN
387 print_debug('Count attach: ' || to_char(l_attachments_number), 1);
388 print_debug('Text : ' || l_short_text, 1);
389 print_debug('Total Text : ' || l_concat_attachment, 1);
390 END IF;
391
392 end loop;
393
394 -- Open Item Attachments Long Text cursor
395 open item_lt_cursor(
396 p_organization_id
397 , p_inventory_item_id
398 , p_document_category);
399
400 IF (l_debug = 1) THEN
401 print_debug('item_lt_cursor open', 1);
402 END IF;
403
404 while(1 > 0)
405 loop
406 fetch item_lt_cursor into
407 l_long_text;
408
409 if item_lt_cursor%notfound then
410 IF (l_debug = 1) THEN
411 print_debug('Count attach: ' || to_char(l_attachments_number), 1);
412 END IF;
413 exit;
414 end if;
415
416 l_attachments_number := l_attachments_number + 1;
417
418 l_concat_attachment := l_concat_attachment || l_long_text;
419 IF (l_debug = 1) THEN
420 print_debug('Count attach: ' || to_char(l_attachments_number), 1);
421 print_debug('Text : ' || l_long_text, 1);
422 print_debug('Total Text : ' || l_concat_attachment, 1);
423 END IF;
424
425 end loop;
426
427
428 -- sales order Line Attachment begins
429
430 if ( p_transaction_temp_id is not null and p_transaction_temp_id > 0) then
431
432 -- Open Transaction source line id cursor
433 open trx_source_line_cursor(p_transaction_temp_id);
434
435 IF (l_debug = 1) THEN
436 print_debug('Transaction source line id cursor open', 1);
437 END IF;
438
439 while(1 > 0)
440 loop
441 fetch trx_source_line_cursor into l_trx_source_line_id;
442
443 if trx_source_line_cursor%notfound then
444 IF (l_debug = 1) THEN
445 print_debug('Count attach: ' || to_char(l_attachments_number), 1);
446 END IF;
447 exit;
448 end if;
449
450 -- Open Sales Order Line Short Text cursor
451 open sales_order_line_st_cursor(l_trx_source_line_id);
452
453 while(1 > 0)
454 loop
455 fetch sales_order_line_st_cursor into l_short_text;
456
457 if sales_order_line_st_cursor%notfound then
458 IF (l_debug = 1) THEN
459 print_debug('Count attach: ' ||to_char(l_attachments_number), 1);
460 END IF;
461 exit;
462 end if;
463
464 l_attachments_number := l_attachments_number + 1;
465
466 l_concat_attachment := l_concat_attachment || l_short_text;
467
468 IF (l_debug = 1) THEN
469 print_debug('Count attach: ' || to_char(l_attachments_number), 1);
470 print_debug('Text : ' || l_short_text, 1);
471 print_debug('Total Text : ' || l_concat_attachment, 1);
472 END IF;
473 end loop;
474
475 IF (sales_order_line_st_cursor%isopen) THEN
476 close sales_order_line_st_cursor;
477 END IF;
478
479 -- Open Sales Order Line Long Text cursor
480 open sales_order_line_lt_cursor(
481 l_trx_source_line_id);
482
483 while(1 > 0)
484 loop
485 fetch sales_order_line_lt_cursor into l_long_text;
486
487 if sales_order_line_lt_cursor%notfound then
488 IF (l_debug = 1) THEN
489 print_debug('Count attach: ' || to_char(l_attachments_number),1);
490 END IF;
491 exit;
492 end if;
493
494 l_attachments_number := l_attachments_number + 1;
495
496 l_concat_attachment := l_concat_attachment || l_long_text;
497 IF (l_debug = 1) THEN
498 print_debug('Count attach: ' || to_char(l_attachments_number), 1);
499 print_debug('Text : ' || l_long_text, 1);
500 print_debug('Total Text : ' || l_concat_attachment, 1);
501 END IF;
502 end loop;
503
504 IF (sales_order_line_lt_cursor%isopen) THEN
505 close sales_order_line_lt_cursor;
506 END IF;
507
508 end loop;
509
510 end if;
511
512 if (l_attachments_number > 0) then
513 x_concat_attachment := l_concat_attachment;
514 x_attachments_number := l_attachments_number;
515 else
516 x_concat_attachment := NULL;
517 x_attachments_number := 0;
518 end if;
519
520 IF (l_debug = 1) THEN
521 print_debug('Count attach: ' || to_char(l_attachments_number), 1);
522 print_debug('Total Text : ' || l_concat_attachment, 1);
523 END IF;
524
525 IF (category_st_cursor%isopen) THEN
526 CLOSE category_st_cursor;
527 END IF;
528
529 IF (category_cursor%isopen) THEN
530 close category_cursor;
531 END IF;
532
533 IF (category_lt_cursor%isopen) THEN
534 CLOSE category_lt_cursor;
535 END IF;
536
537 IF (item_st_cursor%isopen) THEN
538 CLOSE item_st_cursor;
539 END IF;
540
541 IF (trx_source_line_cursor%isopen) THEN
542 close trx_source_line_cursor;
543 END IF;
544
545 IF (sales_order_line_st_cursor%isopen) THEN
546 close sales_order_line_st_cursor;
547 END IF;
548
549 IF (sales_order_line_lt_cursor%isopen) THEN
550 close sales_order_line_lt_cursor;
551 END IF;
552
553
554 exception
555 when fnd_api.g_exc_error THEN
556 x_return_status := fnd_api.g_ret_sts_error;
557 x_concat_attachment := NULL;
558 x_attachments_number := 0;
559
560 IF (l_debug = 1) THEN
561 print_debug('g_ret_sts_error', 1);
562 END IF;
563
564 -- Get message count and data
565 fnd_msg_pub.count_and_get
566 ( p_count => x_msg_count
567 , p_data => x_msg_data
568 );
569
570 IF (category_st_cursor%isopen) THEN
571 CLOSE category_st_cursor;
572 END IF;
573
574 IF (category_cursor%isopen) THEN
575 close category_cursor;
576 END IF;
577
578 IF (category_lt_cursor%isopen) THEN
579 CLOSE category_lt_cursor;
580 END IF;
581
582 IF (item_st_cursor%isopen) THEN
583 CLOSE item_st_cursor;
584 END IF;
585
586 IF (item_lt_cursor%isopen) THEN
587 CLOSE item_lt_cursor;
588 END IF;
589
590 IF (trx_source_line_cursor%isopen) THEN
591 close trx_source_line_cursor;
592 END IF;
593
594 IF (sales_order_line_st_cursor%isopen) THEN
595 close sales_order_line_st_cursor;
596 END IF;
597
598 IF (sales_order_line_lt_cursor%isopen) THEN
599 close sales_order_line_lt_cursor;
600 END IF;
601
602
603 when fnd_api.g_exc_unexpected_error THEN
604 x_return_status := fnd_api.g_ret_sts_unexp_error ;
605 x_concat_attachment := NULL;
606 x_attachments_number := 0;
607
608 IF (l_debug = 1) THEN
609 print_debug('g_ret_sts_unexp_error', 1);
610 END IF;
611
612 -- Get message count and data
613 fnd_msg_pub.count_and_get
614 ( p_count => x_msg_count
615 , p_data => x_msg_data
616 );
617
618 IF (category_st_cursor%isopen) THEN
619 CLOSE category_st_cursor;
620 END IF;
621
622 IF (category_cursor%isopen) THEN
623 close category_cursor;
624 END IF;
625
626 IF (category_lt_cursor%isopen) THEN
627 CLOSE category_lt_cursor;
628 END IF;
629
630 IF (item_st_cursor%isopen) THEN
631 CLOSE item_st_cursor;
632 END IF;
633
634 IF (item_lt_cursor%isopen) THEN
635 CLOSE item_lt_cursor;
636 END IF;
637
638 IF (trx_source_line_cursor%isopen) THEN
639 close trx_source_line_cursor;
640 END IF;
641
642 IF (sales_order_line_st_cursor%isopen) THEN
643 close sales_order_line_st_cursor;
644 END IF;
645
646 IF (sales_order_line_lt_cursor%isopen) THEN
647 close sales_order_line_lt_cursor;
648 END IF;
649
650
651 when others THEN
652
653 x_return_status := fnd_api.g_ret_sts_unexp_error ;
654 x_concat_attachment := NULL;
655 x_attachments_number := 0;
656 --
657 IF (l_debug = 1) THEN
658 print_debug('others', 1);
659 END IF;
660
661 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
662 THEN
663 fnd_msg_pub.add_exc_msg
664 ( g_pkg_name
665 , 'get_item_and_catgy_attachments'
666 );
667 END IF;
668
669 -- Get message count and data
670 fnd_msg_pub.count_and_get
671 ( p_count => x_msg_count
672 , p_data => x_msg_data
673 );
674
675 IF (category_st_cursor%isopen) THEN
676 CLOSE category_st_cursor;
677 END IF;
678
679 IF (category_cursor%isopen) THEN
680 close category_cursor;
681 END IF;
682
683 IF (category_lt_cursor%isopen) THEN
684 CLOSE category_lt_cursor;
685 END IF;
686
687 IF (item_st_cursor%isopen) THEN
688 CLOSE item_st_cursor;
689 END IF;
690
691 IF (item_lt_cursor%isopen) THEN
692 CLOSE item_lt_cursor;
693 END IF;
694
695 IF (trx_source_line_cursor%isopen) THEN
696 close trx_source_line_cursor;
697 END IF;
698
699
700 IF (sales_order_line_st_cursor%isopen) THEN
701 close sales_order_line_st_cursor;
702 END IF;
703
704 IF (sales_order_line_lt_cursor%isopen) THEN
705 close sales_order_line_lt_cursor;
706 END IF;
707
708
709 end get_item_and_catgy_attachments;
710
711 end inv_attachments_utils;
[Home] [Help]