eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.INV_RCV_CACHE

Source


1 PACKAGE BODY INV_RCV_CACHE AS
2 /* $Header: INVRCSHB.pls 120.3.12010000.2 2010/05/20 09:40:04 skommine ship $*/
3 
4 PROCEDURE print_debug(p_err_msg VARCHAR2
5 		      ,p_module IN VARCHAR2 := ' '
6 		      ,p_level NUMBER := 4)
7   IS
8      l_debug NUMBER;
9 BEGIN
10    l_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
11    inv_mobile_helper_functions.tracelog
12      (p_err_msg => p_err_msg
13       ,p_module => 'INV_RCV_CACHE.'||p_module
14       ,p_level => p_level);
15 END;
16 
17 FUNCTION convert_qty
18   (p_inventory_item_id   IN NUMBER
19    ,p_from_qty           IN NUMBER
20    ,p_from_uom_code      IN VARCHAR2
21    ,p_to_uom_code        IN VARCHAR2
22    ,p_precision          IN NUMBER DEFAULT NULL
23    , p_organization_id   IN NUMBER DEFAULT NULL --Bug#9570776
24    , p_lot_number        IN VARCHAR2 DEFAULT NULL --Bug#9570776
25    )
26   RETURN NUMBER IS
27      l_conversion_rate          NUMBER;
28      l_to_qty                   NUMBER;
29 
30      l_debug                    NUMBER;
31      l_progress                 VARCHAR2(10);
32      l_module_name              VARCHAR2(30);
33 
34 BEGIN
35 
36    l_debug := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
37    l_progress := '10';
38    l_module_name := 'CONVERT_QTY';
39 
40    IF (l_debug = 1) THEN
41       print_debug('Entering convert_qty...',l_module_name,4);
42       print_debug(' p_inventory_item_id => '||p_inventory_item_id,l_module_name,4);
43       print_debug(' p_from_uom_code     => '||p_from_uom_code,l_module_name,4);
44       print_debug(' p_to_uom_code       => '||p_to_uom_code,l_module_name,4);
45       print_debug(' p_from_qty          => '||p_from_qty,l_module_name,4);
46       print_debug(' p_precision         => '||p_precision,l_module_name,4);
47    END IF;
48 
49    IF (p_inventory_item_id IS NULL) THEN
50       IF (l_debug = 1) THEN
51 	 print_debug(' No caching for cases without item_id',4);
52       END IF;
53 
54       l_to_qty := inv_convert.inv_um_convert
55 	             (item_id           => p_inventory_item_id,
56 		      precision         => Nvl(p_precision,g_conversion_precision),
57 		      from_quantity     => p_from_qty,
58 		      from_unit         => p_from_uom_code,
59 		      to_unit           => p_to_uom_code,
60 		      from_name         => null,
61 		      to_name           => null);
62 
63       IF (l_debug = 1) THEN
64 	 print_debug(' x_to_qty            => '||l_to_qty,l_module_name,4);
65       END IF;
66 
67       RETURN l_to_qty;
68    /*Bug#9570776 Added the below elsif to consider the lot specific conversion
69       when the lot number is not passed */
70    ELSIF (p_inventory_item_id IS NOT NULL) AND (p_lot_number IS NOT NULL) THEN
71       IF (l_debug = 1) THEN
72 	 print_debug(' lot specific conversion ',4);
73       END IF;
74 
75       l_to_qty := inv_convert.inv_um_convert
76 	             (item_id           => p_inventory_item_id,
77                       organization_id    => p_organization_id,
78 		      lot_number         => p_lot_number,
79 		      precision         => Nvl(p_precision,g_conversion_precision),
80 		      from_quantity     => p_from_qty,
81 		      from_unit         => p_from_uom_code,
82 		      to_unit           => p_to_uom_code,
83 		      from_name         => null,
84 		      to_name           => null);
85 
86       IF (l_debug = 1) THEN
87 	 print_debug(' x_to_qty            => '||l_to_qty,l_module_name,4);
88       END IF;
89 
90       RETURN l_to_qty;
91    END IF;
92 
93    IF (g_item_uom_conversion_tb.exists(p_inventory_item_id) AND
94        g_item_uom_conversion_tb(p_inventory_item_id).exists(p_from_uom_code) AND
95        g_item_uom_conversion_tb(p_inventory_item_id)(p_from_uom_code).exists(p_to_uom_code)) THEN
96       l_conversion_rate := g_item_uom_conversion_tb(p_inventory_item_id)(p_from_uom_code)(p_to_uom_code);
97     ELSE
98       inv_convert.inv_um_conversion(from_unit  => p_from_uom_code,
99 				    to_unit    => p_to_uom_code,
100 				    item_id    => p_inventory_item_id,
101 				    uom_rate   => l_conversion_rate);
102 
103       IF (l_conversion_rate < 0) THEN
104 	 RAISE fnd_api.g_exc_error;
105       END IF;
106 
107       g_item_uom_conversion_tb(p_inventory_item_id)(p_from_uom_code)(p_to_uom_code) := l_conversion_rate;
108       g_item_uom_conversion_tb(p_inventory_item_id)(p_to_uom_code)(p_from_uom_code) := 1 / l_conversion_rate;
109 
110    END IF;
111 
112    l_to_qty := Round(l_conversion_rate*p_from_qty,Nvl(p_precision,g_conversion_precision));
113 
114    IF (l_debug = 1) THEN
115       print_debug(' x_to_qty            => '||l_to_qty,l_module_name,4);
116    END IF;
117 
118    RETURN l_to_qty;
119 EXCEPTION
120    WHEN OTHERS THEN
121       IF (l_debug = 1) THEN
122 	 print_debug('Exception occurred inside convert_qty!',l_module_name,4);
123       END IF;
124       RETURN -1;
125 END convert_qty;
126 
127 FUNCTION get_primary_uom_code
128   (p_organization_id     IN NUMBER
129    ,p_inventory_item_id  IN NUMBER
130    ) RETURN VARCHAR2 IS
131       l_debug                    NUMBER;
132       l_progress                 VARCHAR2(10);
133       l_module_name              VARCHAR2(30);
134 BEGIN
135    l_debug := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
136    l_progress := '10';
137    l_module_name := 'GET_PRIMARY_UOM_CODE';
138 
139    IF (l_debug = 1) THEN
140       print_debug('Entering get_primary_uom_code...',l_module_name,4);
141       print_debug(' p_inventory_item_id    => '||p_inventory_item_id,l_module_name,4);
142       print_debug(' p_organization_id      => '||p_organization_id,l_module_name,4);
143    END IF;
144 
145    IF (g_org_item_attrib_tb.exists(p_organization_id) AND
146        g_org_item_attrib_tb(p_organization_id).exists(p_inventory_item_id)) THEN
147       IF (l_debug = 1) THEN
148 	 print_debug(' x_prim_uom_code (Cache) => '||
149 		     g_org_item_attrib_tb(p_organization_id)(p_inventory_item_id).primary_uom_code,l_module_name,4);
150       END IF;
151       RETURN g_org_item_attrib_tb(p_organization_id)(p_inventory_item_id).primary_uom_code;
152     ELSE
153       BEGIN
154 	 SELECT primary_uom_code
155 	   ,    secondary_uom_code
156 	   ,    lot_control_code
157 	   ,    serial_number_control_code
158 	   INTO g_org_item_attrib_tb(p_organization_id)(p_inventory_item_id).primary_uom_code
159 	   ,    g_org_item_attrib_tb(p_organization_id)(p_inventory_item_id).secondary_uom_code
160 	   ,    g_org_item_attrib_tb(p_organization_id)(p_inventory_item_id).lot_control_code
161 	   ,    g_org_item_attrib_tb(p_organization_id)(p_inventory_item_id).serial_number_control_code
162 	   FROM   mtl_system_items
163 	   WHERE  organization_id = p_organization_id
164 	   AND    inventory_item_id = p_inventory_item_id;
165       EXCEPTION
166 	 WHEN OTHERS THEN
167 	    IF (l_debug = 1) THEN
168 	       print_debug('Unable to query from db!',l_module_name,4);
169 	    END IF;
170 	    RAISE fnd_api.g_exc_error;
171       END;
172 
173       IF (l_debug = 1) THEN
174 	 print_debug(' x_prim_uom_code (DB)     => '||
175 		     g_org_item_attrib_tb(p_organization_id)(p_inventory_item_id).primary_uom_code,l_module_name,4);
176       END IF;
177 
178       RETURN g_org_item_attrib_tb(p_organization_id)(p_inventory_item_id).primary_uom_code;
179    END IF;
180 EXCEPTION
181    WHEN OTHERS THEN
182       IF (l_debug = 1) THEN
183 	 print_debug('Exception occurred inside GET_PRIMARY_UOM_CODE!',l_module_name,4);
184       END IF;
185       RETURN NULL;
186 END get_primary_uom_code;
187 
188 FUNCTION get_secondary_uom_code
189   (p_organization_id     IN NUMBER
190    ,p_inventory_item_id  IN NUMBER
191    ) RETURN VARCHAR2 IS
192       l_debug                    NUMBER;
193       l_progress                 VARCHAR2(10);
194       l_module_name              VARCHAR2(30);
195 BEGIN
196    l_debug := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
197    l_progress := '10';
198    l_module_name := 'GET_SECONDARY_UOM_CODE';
199 
200    IF (l_debug = 1) THEN
201       print_debug('Entering get_secondary_uom_code...',l_module_name,4);
202       print_debug(' p_inventory_item_id    => '||p_inventory_item_id,l_module_name,4);
203       print_debug(' p_organization_id      => '||p_organization_id,l_module_name,4);
204    END IF;
205 
206    IF (g_org_item_attrib_tb.exists(p_organization_id) AND
207        g_org_item_attrib_tb(p_organization_id).exists(p_inventory_item_id)) THEN
208       IF (l_debug = 1) THEN
209 	 print_debug(' x_prim_uom_code (Cache) => '||
210 		     g_org_item_attrib_tb(p_organization_id)(p_inventory_item_id).secondary_uom_code,l_module_name,4);
211       END IF;
212       RETURN g_org_item_attrib_tb(p_organization_id)(p_inventory_item_id).secondary_uom_code;
213     ELSE
214       BEGIN
215 	 SELECT primary_uom_code
216 	   ,    secondary_uom_code
217 	   ,    lot_control_code
218 	   ,    serial_number_control_code
219 	   INTO g_org_item_attrib_tb(p_organization_id)(p_inventory_item_id).primary_uom_code
220 	   ,    g_org_item_attrib_tb(p_organization_id)(p_inventory_item_id).secondary_uom_code
221 	   ,    g_org_item_attrib_tb(p_organization_id)(p_inventory_item_id).lot_control_code
222 	   ,    g_org_item_attrib_tb(p_organization_id)(p_inventory_item_id).serial_number_control_code
223 	   FROM   mtl_system_items
224 	   WHERE  organization_id = p_organization_id
225 	   AND    inventory_item_id = p_inventory_item_id;
226       EXCEPTION
227 	 WHEN OTHERS THEN
228 	    IF (l_debug = 1) THEN
229 	       print_debug('Unable to query from db!',l_module_name,4);
230 	    END IF;
231 	    RAISE fnd_api.g_exc_error;
232       END;
233 
234       IF (l_debug = 1) THEN
235 	 print_debug(' x_prim_uom_code (DB)     => '||
236 		     g_org_item_attrib_tb(p_organization_id)(p_inventory_item_id).secondary_uom_code,l_module_name,4);
237       END IF;
238 
239       RETURN g_org_item_attrib_tb(p_organization_id)(p_inventory_item_id).secondary_uom_code;
240    END IF;
241 EXCEPTION
242    WHEN OTHERS THEN
243       IF (l_debug = 1) THEN
244 	 print_debug('Exception occurred inside GET_SECONDARY_UOM_CODE!',l_module_name,4);
245       END IF;
246       RETURN NULL;
247 END get_secondary_uom_code;
248 
249 FUNCTION get_sn_ctrl_code
250   (p_organization_id     IN NUMBER
251    ,p_inventory_item_id  IN NUMBER
252    ) RETURN NUMBER IS
253       l_debug                    NUMBER;
254       l_progress                 VARCHAR2(10);
255       l_module_name              VARCHAR2(30);
256 BEGIN
257    l_debug := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
258    l_progress := '10';
259    l_module_name := 'GET_SN_CTRL_CODE';
260 
261    IF (l_debug = 1) THEN
262       print_debug('Entering get_sn_ctrl_code...',l_module_name,4);
263       print_debug(' p_inventory_item_id    => '||p_inventory_item_id,l_module_name,4);
264       print_debug(' p_organization_id      => '||p_organization_id,l_module_name,4);
265    END IF;
266 
267    IF (g_org_item_attrib_tb.exists(p_organization_id) AND
268        g_org_item_attrib_tb(p_organization_id).exists(p_inventory_item_id)) THEN
269       IF (l_debug = 1) THEN
270 	 print_debug(' x_sn_ctrl_code (Cache) => '||
271 		     g_org_item_attrib_tb(p_organization_id)(p_inventory_item_id).serial_number_control_code,l_module_name,4);
272       END IF;
273       RETURN g_org_item_attrib_tb(p_organization_id)(p_inventory_item_id).serial_number_control_code;
274     ELSE
275       BEGIN
276 	 SELECT primary_uom_code
277 	   ,    secondary_uom_code
278 	   ,    lot_control_code
279 	   ,    serial_number_control_code
280 	   INTO g_org_item_attrib_tb(p_organization_id)(p_inventory_item_id).primary_uom_code
281 	   ,    g_org_item_attrib_tb(p_organization_id)(p_inventory_item_id).secondary_uom_code
282 	   ,    g_org_item_attrib_tb(p_organization_id)(p_inventory_item_id).lot_control_code
283 	   ,    g_org_item_attrib_tb(p_organization_id)(p_inventory_item_id).serial_number_control_code
284 	   FROM   mtl_system_items
285 	   WHERE  organization_id = p_organization_id
286 	   AND    inventory_item_id = p_inventory_item_id;
287       EXCEPTION
288 	 WHEN OTHERS THEN
289 	    IF (l_debug = 1) THEN
290 	       print_debug('Unable to query from db!',l_module_name,4);
291 	    END IF;
292 	    RAISE fnd_api.g_exc_error;
293       END;
294 
295       IF (l_debug = 1) THEN
296 	 print_debug(' x_sn_ctrl_code (DB)     => '||
297 		     g_org_item_attrib_tb(p_organization_id)(p_inventory_item_id).serial_number_control_code,l_module_name,4);
298       END IF;
299 
300       RETURN g_org_item_attrib_tb(p_organization_id)(p_inventory_item_id).serial_number_control_code;
301    END IF;
302 EXCEPTION
303    WHEN OTHERS THEN
304       IF (l_debug = 1) THEN
305 	 print_debug('Exception occurred inside GET_SN_CTRL_CODE!',l_module_name,4);
306       END IF;
307       RETURN NULL;
308 END get_sn_ctrl_code;
309 
310 FUNCTION get_lot_control_code
311   (p_organization_id     IN NUMBER
312    ,p_inventory_item_id  IN NUMBER
313    ) RETURN NUMBER IS
314       l_debug                    NUMBER;
315       l_progress                 VARCHAR2(10);
316       l_module_name              VARCHAR2(30);
317 BEGIN
318    l_debug := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
319    l_progress := '10';
320    l_module_name := 'GET_LOT_CONTROL_CODE';
321 
322    IF (l_debug = 1) THEN
323       print_debug('Entering get_lot_control_code...',l_module_name,4);
324       print_debug(' p_inventory_item_id    => '||p_inventory_item_id,l_module_name,4);
325       print_debug(' p_organization_id      => '||p_organization_id,l_module_name,4);
326    END IF;
327 
328    IF (g_org_item_attrib_tb.exists(p_organization_id) AND
329        g_org_item_attrib_tb(p_organization_id).exists(p_inventory_item_id)) THEN
330       IF (l_debug = 1) THEN
331 	 print_debug(' x_lot_control_code (Cache) => '||
332 		     g_org_item_attrib_tb(p_organization_id)(p_inventory_item_id).lot_control_code,l_module_name,4);
333       END IF;
334       RETURN g_org_item_attrib_tb(p_organization_id)(p_inventory_item_id).lot_control_code;
335     ELSE
336       BEGIN
337 	 SELECT primary_uom_code
338 	   ,    secondary_uom_code
339 	   ,    lot_control_code
340 	   ,    serial_number_control_code
341 	   INTO g_org_item_attrib_tb(p_organization_id)(p_inventory_item_id).primary_uom_code
342 	   ,    g_org_item_attrib_tb(p_organization_id)(p_inventory_item_id).secondary_uom_code
343 	   ,    g_org_item_attrib_tb(p_organization_id)(p_inventory_item_id).lot_control_code
344 	   ,    g_org_item_attrib_tb(p_organization_id)(p_inventory_item_id).serial_number_control_code
345 	   FROM   mtl_system_items
346 	   WHERE  organization_id = p_organization_id
347 	   AND    inventory_item_id = p_inventory_item_id;
348       EXCEPTION
349 	 WHEN OTHERS THEN
350 	    IF (l_debug = 1) THEN
351 	       print_debug('Unable to query from db!',l_module_name,4);
352 	    END IF;
353 	    RAISE fnd_api.g_exc_error;
354       END;
355 
356       IF (l_debug = 1) THEN
357 	 print_debug(' x_lot_control_code (DB)     => '||
358 		     g_org_item_attrib_tb(p_organization_id)(p_inventory_item_id).lot_control_code,l_module_name,4);
359       END IF;
360 
361       RETURN g_org_item_attrib_tb(p_organization_id)(p_inventory_item_id).lot_control_code;
362    END IF;
363 EXCEPTION
364    WHEN OTHERS THEN
365       IF (l_debug = 1) THEN
366 	 print_debug('Exception occurred inside GET_LOT_CONTROL_CODE!',l_module_name,4);
367       END IF;
368       RETURN NULL;
369 END get_lot_control_code;
370 
371 FUNCTION get_conversion_rate
372   (p_inventory_item_id   IN NUMBER
373    ,p_from_uom_code      IN VARCHAR2
374    ,p_to_uom_code        IN VARCHAR2
375    )
376    RETURN NUMBER IS
377 
378      l_conversion_rate          NUMBER;
379 
380      l_debug                    NUMBER;
381      l_progress                 VARCHAR2(10);
382      l_module_name              VARCHAR2(30);
383 BEGIN
384 
385    l_debug := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
386    l_progress := '10';
387    l_module_name := 'GET_CONVERSION_RATE';
388 
389    IF (l_debug = 1) THEN
390       print_debug('Entering convert_qty...',l_module_name,4);
391       print_debug(' p_inventory_item_id => '||p_inventory_item_id,l_module_name,4);
392       print_debug(' p_from_uom_code     => '||p_from_uom_code,l_module_name,4);
393       print_debug(' p_to_uom_code       => '||p_to_uom_code,l_module_name,4);
394    END IF;
395 
396    IF (g_item_uom_conversion_tb.exists(p_inventory_item_id) AND
397        g_item_uom_conversion_tb(p_inventory_item_id).exists(p_from_uom_code) AND
398        g_item_uom_conversion_tb(p_inventory_item_id)(p_from_uom_code).exists(p_to_uom_code)) THEN
399       l_conversion_rate := g_item_uom_conversion_tb(p_inventory_item_id)(p_from_uom_code)(p_to_uom_code);
400     ELSE
401       inv_convert.inv_um_conversion(from_unit  => p_from_uom_code,
402 				    to_unit    => p_to_uom_code,
403 				    item_id    => p_inventory_item_id,
404 				    uom_rate   => l_conversion_rate);
405 
406       IF (l_conversion_rate < 0) THEN
407 	 RAISE fnd_api.g_exc_error;
408       END IF;
409 
410       g_item_uom_conversion_tb(p_inventory_item_id)(p_from_uom_code)(p_to_uom_code) := l_conversion_rate;
411       g_item_uom_conversion_tb(p_inventory_item_id)(p_to_uom_code)(p_from_uom_code) := 1 / l_conversion_rate;
412 
413    END IF;
414 
415    IF (l_debug = 1) THEN
416       print_debug(' x_conversion_rate   => '||l_conversion_rate,l_module_name,4);
417    END IF;
418 
419    RETURN l_conversion_rate;
420 EXCEPTION
421    WHEN OTHERS THEN
422       IF (l_debug = 1) THEN
423 	 print_debug('Exception occurred inside get_conversion_rate!',l_module_name,4);
424       END IF;
425       RETURN -1;
426 END get_conversion_rate;
427 END inv_rcv_cache;
428