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