[Home] [Help]
PACKAGE BODY: APPS.GMO_DISPENSE_PVT
Source
1 PACKAGE BODY GMO_DISPENSE_PVT AS
2 /* $Header: GMOVDSPB.pls 120.18.12000000.4 2007/04/19 06:46:45 achawla ship $ */
3
4 /* Used in Dispensing BC4J objects to getProduct */
5 /* Returns batch's primary product number */
6 FUNCTION GET_PRODUCT_NUMBER(P_BATCH_ID NUMBER) RETURN VARCHAR2
7 IS
8 l_return_value Varchar2(240);
9 CURSOR C_GET_BATCH_PRODUCT IS
10 SELECT msi.concatenated_segments
11 FROM mtl_system_items_vl msi,
12 gme_material_details gme
13 WHERE gme.organization_id = msi.organization_id
14 AND gme.inventory_item_id = msi.inventory_item_id
15 AND gme.line_type = 1
16 AND gme.line_no = 1
17 AND gme.batch_id = p_batch_id;
18 BEGIN
19 OPEN C_GET_BATCH_PRODUCT ;
20 FETCH C_GET_BATCH_PRODUCT INTO l_return_value;
21 CLOSE C_GET_BATCH_PRODUCT;
22 return l_return_value;
23 END GET_PRODUCT_NUMBER;
24 /* Returns batch's primary product's description */
25 FUNCTION GET_PRODUCT_DESCRIPTION(P_BATCH_ID NUMBER) RETURN VARCHAR2
26 IS
27 l_return_value Varchar2(240);
28 CURSOR C_GET_BATCH_PRODUCT IS
29 SELECT msi.description
30 FROM mtl_system_items_vl msi,
31 gme_material_details gme
32 WHERE gme.organization_id = msi.organization_id
33 AND gme.inventory_item_id = msi.inventory_item_id
34 AND gme.line_type = 1
35 AND gme.line_no = 1
36 AND gme.batch_id = p_batch_id;
37 BEGIN
38 OPEN C_GET_BATCH_PRODUCT ;
39 FETCH C_GET_BATCH_PRODUCT INTO l_return_value;
40 CLOSE C_GET_BATCH_PRODUCT;
41 return l_return_value;
42 END GET_PRODUCT_DESCRIPTION;
43 /* Used by GET_DISPENSE_DATA API */
44 FUNCTION GET_NET_RES_DISPENSED_QTY(P_RESERVATION_ID NUMBER, P_UOM VARCHAR2) RETURN NUMBER
45 IS
46 l_return_value NUMBER;
47 l_total_dispensed_qty NUMBER;
48 l_total_undispensed_qty NUMBER;
49 l_undispensed_qty NUMBER;
50 l_dispensed_qty NUMBER;
51 CURSOR C_GET_DISPENSES IS
52 SELECT inventory_item_id,lot_number, dispensed_qty, dispense_uom, organization_id, dispense_id
53 FROM GMO_MATERIAL_DISPENSES
54 WHERE MATERIAL_STATUS = 'DISPENSD'
55 AND RESERVATION_ID = P_RESERVATION_ID;
56 CURSOR C_GET_UNDISPENSED_QTY(p_dispense_id NUMBER) IS
57 SELECT nvl(sum(nvl(undispensed_qty,0)+ nvl(material_loss,0)),0)
58 FROM GMO_MATERIAL_UNDISPENSES
59 WHERE dispense_id = p_dispense_id;
60 l_dispense_rec C_GET_DISPENSES%ROWTYPE;
61 BEGIN
62 l_total_dispensed_qty := 0;
63 l_total_undispensed_qty := 0;
64 /* select total dispensed for reservation */
65 OPEN C_GET_DISPENSES;
66 LOOP
67 FETCH C_GET_DISPENSES INTO l_dispense_rec;
68 EXIT WHEN C_GET_DISPENSES %NOTFOUND;
69 /* get the total of undispensed qty for current dispense id */
70 OPEN C_GET_UNDISPENSED_QTY(l_dispense_rec.dispense_id);
71 FETCH C_GET_UNDISPENSED_QTY into l_undispensed_qty;
72 CLOSE C_GET_UNDISPENSED_QTY;
73 IF(l_dispense_rec.dispense_uom <> p_uom) THEN
74 /* convert the dispensed qty in p_uom */
75 /* It is safe to assume that conversion will not fail because this is an internal
76 procedure and will only be called where conversion do exist and p_uom is not null.
77 Therefore no need to check the failure (-99999 return value) */
78 l_dispensed_qty := inv_convert.inv_um_convert(l_dispense_rec.inventory_item_id,
79 l_dispense_rec.lot_number,
80 l_dispense_rec.organization_id,
81 5,
82 l_dispense_rec.dispensed_qty,
83 l_dispense_rec.dispense_uom,
84 p_uom,
85 null,
86 null);
87 /* convert the undispensed qty too */
88 IF (l_undispensed_qty > 0) THEN
89 l_undispensed_qty := inv_convert.inv_um_convert(l_dispense_rec.inventory_item_id,
90 l_dispense_rec.lot_number,
91 l_dispense_rec.organization_id,
92 5,
93 l_undispensed_qty,
94 l_dispense_rec.dispense_uom,
95 p_uom,
96 null,
97 null);
98 END IF;
99 ELSE
100 l_dispensed_qty := l_dispense_rec.dispensed_qty;
101 END IF;
102 l_total_dispensed_qty := l_total_dispensed_qty + l_dispensed_qty;
103 l_total_undispensed_qty := l_total_undispensed_qty + l_undispensed_qty;
104 END LOOP;
105 l_return_value := l_total_dispensed_qty - l_total_undispensed_qty;
106 return l_return_value;
107 END GET_NET_RES_DISPENSED_QTY;
108 /* Used by GET_DISPENSE_DATA API */
109 FUNCTION GET_NET_MTL_DISPENSED_QTY(P_MATERIAL_DETAIL_ID NUMBER, P_UOM VARCHAR2) RETURN NUMBER
110 IS
111 l_return_value NUMBER;
112 l_mtl_dispensed_quantity NUMBER;
113 l_mtl_undispensed_quantity NUMBER;
114 l_undispensed_qty NUMBER;
115 l_dispensed_qty NUMBER;
116 CURSOR C_GET_DISPENSES IS
117 SELECT inventory_item_id,lot_number, dispensed_qty, dispense_uom, organization_id, dispense_id
118 FROM GMO_MATERIAL_DISPENSES
119 WHERE MATERIAL_STATUS = 'DISPENSD'
120 AND MATERIAL_DETAIL_ID = P_MATERIAL_DETAIL_ID;
121 l_dispense_rec C_GET_DISPENSES%ROWTYPE;
122 CURSOR C_GET_UNDISPENSED_QTY(p_dispense_id NUMBER) IS
123 SELECT nvl(sum(nvl(undispensed_qty,0)+ nvl(material_loss,0)),0)
124 FROM GMO_MATERIAL_UNDISPENSES
125 WHERE dispense_id = p_dispense_id;
126 BEGIN
127 l_mtl_dispensed_quantity := 0;
128 l_mtl_undispensed_quantity := 0;
129 OPEN C_GET_DISPENSES;
130 LOOP
131 FETCH C_GET_DISPENSES INTO l_dispense_rec;
132 EXIT WHEN C_GET_DISPENSES %NOTFOUND;
133 /* get the total of undispensed qty for current dispense id */
134 OPEN C_GET_UNDISPENSED_QTY(l_dispense_rec.dispense_id);
135 FETCH C_GET_UNDISPENSED_QTY into l_undispensed_qty;
136 CLOSE C_GET_UNDISPENSED_QTY;
137 /* convert the dispensed qty in p_uom */
138 IF(l_dispense_rec.dispense_uom <> p_uom) THEN
139 /* It is safe to assume that conversion will not fail because this is an internal
140 procedure and will only be called where conversion do exist and p_uom is not null.
141 Therefore no need to check the failure (-99999 return value) */
142 l_dispensed_qty := inv_convert.inv_um_convert(l_dispense_rec.inventory_item_id,
143 l_dispense_rec.lot_number,
144 l_dispense_rec.organization_id,
145 5,
146 l_dispense_rec.dispensed_qty,
147 l_dispense_rec.dispense_uom,
148 p_uom,
149 null,
150 null);
151 /* convert the undispensed qty too */
152 IF (l_undispensed_qty > 0) THEN
153 l_undispensed_qty := inv_convert.inv_um_convert(l_dispense_rec.inventory_item_id,
154 l_dispense_rec.lot_number,
155 l_dispense_rec.organization_id,
156 5,
157 l_undispensed_qty,
158 l_dispense_rec.dispense_uom,
159 p_uom,
160 null,
161 null);
162 END IF;
163 ELSE
164 l_dispensed_qty := l_dispense_rec.dispensed_qty;
165 END IF;
166 l_mtl_dispensed_quantity := l_mtl_dispensed_quantity + l_dispensed_qty;
167 l_mtl_undispensed_quantity := l_mtl_undispensed_quantity + l_undispensed_qty;
168 END LOOP;
169 l_return_value := l_mtl_dispensed_quantity - l_mtl_undispensed_quantity;
170 return l_return_value ;
171 END GET_NET_MTL_DISPENSED_QTY;
172 /* Used by GET_DISPENSE_DATA API */
173 FUNCTION GET_NET_DISP_DISPENSED_QTY(P_DISPENSE_ID NUMBER) RETURN NUMBER
174 IS
175 l_return_value NUMBER;
176 l_dispensed_qty NUMBER;
177 l_undispensed_qty NUMBER;
178 CURSOR C_GET_DISPENSED_QTY IS
179 select dispensed_qty l_dispensed_qty
180 from gmo_material_dispenses
181 where dispense_id = p_dispense_id
182 and material_status = 'DISPENSD';
183 CURSOR C_GET_UNDISPENSED_QTY IS
184 SELECT nvl(sum(nvl(undispensed_qty,0)+ nvl(material_loss,0)),0)
185 from gmo_material_undispenses u , gmo_material_dispenses d
186 where d.dispense_id = p_dispense_id
187 and d.dispense_id = u.dispense_id(+)
188 and d.material_status = 'DISPENSD';
189 BEGIN
190 l_return_value := null;
191 l_dispensed_qty := 0;
192 l_undispensed_qty :=0;
193 OPEN C_GET_DISPENSED_QTY;
194 FETCH C_GET_DISPENSED_QTY into l_dispensed_qty;
195 CLOSE C_GET_DISPENSED_QTY;
196 if(l_dispensed_qty = 0) then
197 return l_dispensed_qty;
198 end if;
199 /* select total undispensed for material line */
200 OPEN C_GET_UNDISPENSED_QTY;
201 FETCH C_GET_UNDISPENSED_QTY into l_undispensed_qty;
202 CLOSE C_GET_UNDISPENSED_QTY;
203 if(l_undispensed_qty = 0 ) then
204 return l_dispensed_qty ;
205 end if;
206 l_return_value := (l_dispensed_qty - l_undispensed_qty);
207 return l_return_value;
208 END GET_NET_DISP_DISPENSED_QTY;
209 /* Used by Dispensing BC4J objects (MaterialListVORowImpl.java) to get dispensing related information */
210 -- Kiosk : Start
211 PROCEDURE GET_DISPENSE_DATA (P_RESERVATION_ID NUMBER,
212 P_INVENTORY_ITEM_ID NUMBER,
213 P_ORGANIZATION_ID NUMBER,
214 P_RECIPE_ID NUMBER ,
215 P_MATERIAL_DETAILS_ID NUMBER,
216 P_RESERVATION_UOM VARCHAR2,
217 P_RESERVED_QUANTITY NUMBER,
218 P_PLAN_QUANTITY NUMBER,
219 P_PLAN_UOM VARCHAR2,
220 P_LOT_NUMBER VARCHAR2,
221 P_SHOW_IN_TOLERANCE_DATA VARCHAR2 DEFAULT GMO_CONSTANTS_GRP.YES,
222 X_DISPENSE_UOM OUT NOCOPY VARCHAR2,
223 X_DISPENSE_CONFIG_ID OUT NOCOPY NUMBER,
224 X_RESERVED_QUANTITY OUT NOCOPY NUMBER,
225 X_PENDING_DISPENSE_QUANTITY OUT NOCOPY NUMBER,
226 X_MAX_ALLOWED_QUANTITY OUT NOCOPY NUMBER,
227 X_MIN_ALLOWED_QUANTITY OUT NOCOPY NUMBER,
228 X_INSTRUCTION_ENTITY_DEF_KEY OUT NOCOPY VARCHAR2,
229 X_PLAN_UOM_CONVERTIBLE OUT NOCOPY VARCHAR2,
230 X_RESERVATION_UOM_CONVERTIBLE OUT NOCOPY VARCHAR2,
231 X_SECURITY_FLAG OUT NOCOPY VARCHAR2
232 )
233 IS
234 l_dispense_config_rec GMO_DISPENSE_CONFIG%ROWTYPE;
235 l_mtl_dispensed_quantity NUMBER;
236 l_mtl_undispensed_quantity NUMBER;
237 l_net_dispensed_quantity NUMBER;
238 l_net_mtl_dispensed_quantity NUMBER;
239 l_planned_quantity NUMBER;
240 l_reservation_quantity NUMBER;
241 l_mtl_reserved_qty NUMBER;
242 l_dispense_uom VARCHAR(10);
243 BEGIN
244 -- ER: 4575836 : Ends
245 /* Get the applicable dispense configuration data */
246 X_INSTRUCTION_ENTITY_DEF_KEY := null;
247 /* Bug 4946534 : Starts : Commenting existing code
248 GMO_DISPENSE_SETUP_PVT.GET_DISPENSE_CONFIG(P_INVENTORY_ITEM_ID => P_INVENTORY_ITEM_ID,
249 P_ORGANIZATION_ID => P_ORGANIZATION_ID,
250 P_RECIPE_ID => P_RECIPE_ID,
251 X_DISPENSE_CONFIG =>l_dispense_config_rec,
252 X_INSTRUCTION_DEFINITION_KEY => X_INSTRUCTION_ENTITY_DEF_KEY);
253 /* Bug 4946534 : Ends : Commenting existing code */
254 /* Bug 4946534 : Starts : New API Call to get instance*/
255 -- Get the Dispense Setup Record for the given material line. This API will
256 --search in dispense setup instances and return config records if exists otherwise null.
257 GMO_DISPENSE_SETUP_PVT.GET_DISPENSE_CONFIG_INST(P_ENTITY_NAME => GMO_DISPENSE_GRP.G_MATERIAL_LINE_ENTITY,
258 P_ENTITY_KEY => P_MATERIAL_DETAILS_ID,
259 X_DISPENSE_CONFIG => l_dispense_config_rec,
260 X_INSTRUCTION_DEFINITION_KEY => X_INSTRUCTION_ENTITY_DEF_KEY);
261 IF(l_dispense_config_rec.config_id is null ) THEN
262 RETURN;
263 END IF;
264 X_DISPENSE_UOM := l_dispense_config_rec.dispense_uom;
265 l_dispense_uom := x_dispense_uom;
266
267 X_DISPENSE_CONFIG_ID := l_dispense_config_rec.config_id;
268 -- change for kiosk : start
269 X_SECURITY_FLAG := l_dispense_config_rec.SECURECODE_REQUIRED_FLAG;
270 -- change for kiosk : End
271 l_mtl_reserved_qty := inv_convert.inv_um_convert(p_inventory_item_id,
272 p_lot_number,
273 p_organization_id,
274 5,
275 P_RESERVED_QUANTITY,
276 P_reservation_uom,
277 l_dispense_uom,
278 null,
279 null);
280
281 if(l_mtl_reserved_qty = -99999) then
282 X_RESERVATION_UOM_CONVERTIBLE := FND_API.G_FALSE;
283 else
284 X_RESERVATION_UOM_CONVERTIBLE := FND_API.G_TRUE;
285 end if ;
286 X_RESERVED_QUANTITY := l_mtl_reserved_qty;
287 l_net_mtl_dispensed_quantity := GET_NET_MTL_DISPENSED_QTY(P_MATERIAL_DETAILS_ID, l_dispense_uom);
288 l_planned_quantity := inv_convert.inv_um_convert(p_inventory_item_id,
289 p_lot_number,
290 p_organization_id,
291 5,
292 p_plan_quantity,
293 p_plan_uom,
294 l_dispense_uom,
295 null,
296 null);
297 IF (l_planned_quantity = -99999) THEN
298 X_PLAN_UOM_CONVERTIBLE := FND_API.G_FALSE;
299 else
300 X_PLAN_UOM_CONVERTIBLE := FND_API.G_TRUE;
301 END IF;
302 IF (l_planned_quantity = -99999 OR l_mtl_reserved_qty = -99999) THEN
303 X_PENDING_DISPENSE_QUANTITY := -99999; -- Make this row as dispense required
304 X_MAX_ALLOWED_QUANTITY := 0;
305 X_MIN_ALLOWED_QUANTITY := 0;
306 Return;
307 END IF;
308 -- Bug 5667543 : Kiosk : Start
309 -- X_PENDING_DISPENSE_QUANTITY := least(l_planned_quantity, X_RESERVED_QUANTITY) - l_net_mtl_dispensed_quantity ;
310 l_net_dispensed_quantity := GET_NET_RES_DISPENSED_QTY(p_reservation_id, l_dispense_uom);
311
312 IF ((l_planned_quantity - l_net_mtl_dispensed_quantity) >= (l_mtl_reserved_qty - l_net_dispensed_quantity)) THEN
313 X_PENDING_DISPENSE_QUANTITY := l_mtl_reserved_qty - l_net_dispensed_quantity;
314 ELSE
315 X_PENDING_DISPENSE_QUANTITY := l_planned_quantity - l_net_mtl_dispensed_quantity;
316 END IF;
317
318
319 -- Bug 5667543 : Kiosk : End
320 X_MAX_ALLOWED_QUANTITY := X_PENDING_DISPENSE_QUANTITY;
321 X_MIN_ALLOWED_QUANTITY := X_PENDING_DISPENSE_QUANTITY;
322 IF(l_dispense_config_rec.Tolerance_type = 'Q') THEN
323 IF (l_dispense_config_rec.low_tolerance is not null) then
324 X_MIN_ALLOWED_QUANTITY := X_PENDING_DISPENSE_QUANTITY - l_dispense_config_rec.low_tolerance;
325 end if;
326 if(l_dispense_config_rec.high_tolerance is not null) then
327 X_MAX_ALLOWED_QUANTITY := X_PENDING_DISPENSE_QUANTITY + l_dispense_config_rec.high_tolerance;
328 end if;
329 ELSE
330 if(l_dispense_config_rec.low_tolerance is not null) then
331 X_MIN_ALLOWED_QUANTITY := X_PENDING_DISPENSE_QUANTITY - ((P_PLAN_QUANTITY/100) * l_dispense_config_rec.low_tolerance);
332 end if;
333 if(l_dispense_config_rec.high_tolerance is not null) then
334 X_MAX_ALLOWED_QUANTITY := X_PENDING_DISPENSE_QUANTITY + ((P_PLAN_QUANTITY/100) * l_dispense_config_rec.high_tolerance);
335 end if;
336 END IF;
337 X_MAX_ALLOWED_QUANTITY := greatest( least(X_MAX_ALLOWED_QUANTITY , (X_RESERVED_QUANTITY - L_NET_DISPENSED_QUANTITY)),0);
338 X_MIN_ALLOWED_QUANTITY := greatest(X_MIN_ALLOWED_QUANTITY, 0);
339 -- ER 4575836 : Starts
340 IF ( P_SHOW_IN_TOLERANCE_DATA = GMO_CONSTANTS_GRP.NO) THEN
341 IF (X_MIN_ALLOWED_QUANTITY = 0 OR X_PENDING_DISPENSE_QUANTITY < X_MIN_ALLOWED_QUANTITY) THEN
342 X_PENDING_DISPENSE_QUANTITY := 0;
343 END IF;
344 END IF;
345 -- ER 4575836 : Ends
346 END GET_DISPENSE_DATA;
347 -- Kiosk : End
348 /* Used MaterialListVO.xml */
349 FUNCTION IS_DISPENSE_REQUIRED(P_RESERVATION_ID NUMBER,
350 P_INVENTORY_ITEM_ID NUMBER,
351 P_ORGANIZATION_ID NUMBER,
352 P_RECIPE_ID NUMBER ,
353 P_MATERIAL_DETAILS_ID NUMBER,
354 P_RESERVED_QUANTITY NUMBER,
355 P_RESERVATION_UOM VARCHAR2,
356 P_PLAN_QUANTITY NUMBER,
357 P_PLAN_UOM VARCHAR2,
358 P_LOT_NUMBER VARCHAR2,
359 P_SHOW_IN_TOLERANCE_DATA VARCHAR2 DEFAULT GMO_CONSTANTS_GRP.YES
360 ) RETURN VARCHAR2
361 IS
362 l_DISPENSE_UOM VARCHAR2(10);
363 l_DISPENSE_CONFIG_ID NUMBER;
364 l_RESERVED_QUANTITY NUMBER;
365 l_PENDING_DISPENSE_QUANTITY NUMBER;
366 l_MAX_ALLOWED_QUANTITY NUMBER;
367 l_MIN_ALLOWED_QUANTITY NUMBER;
368 l_INSTRUCTION_ENTITY_DEF_KEY VARCHAR2(20);
369 l_plan_uom_converted VARCHAR2(1);
370 l_revervation_uom_converted VARCHAR2(1);
371 l_security_flag varchar2(1);
372 BEGIN
373 -- change for kIosk : start
374 GET_DISPENSE_DATA( P_RESERVATION_ID =>P_RESERVATION_ID ,
375 P_INVENTORY_ITEM_ID => P_INVENTORY_ITEM_ID ,
376 P_ORGANIZATION_ID => P_ORGANIZATION_ID,
377 P_RECIPE_ID => P_RECIPE_ID ,
378 P_MATERIAL_DETAILS_ID => P_MATERIAL_DETAILS_ID ,
379 P_RESERVED_QUANTITY => P_RESERVED_QUANTITY ,
380 P_RESERVATION_UOM => P_RESERVATION_UOM,
381 P_PLAN_QUANTITY => P_PLAN_QUANTITY,
382 P_PLAN_UOM => P_PLAN_UOM,
383 P_LOT_NUMBER => P_LOT_NUMBER,
384 P_SHOW_IN_TOLERANCE_DATA => P_SHOW_IN_TOLERANCE_DATA,
385 X_DISPENSE_UOM => l_DISPENSE_UOM ,
386 X_DISPENSE_CONFIG_ID => l_DISPENSE_CONFIG_ID,
387 X_RESERVED_QUANTITY => l_RESERVED_QUANTITY ,
388 X_PENDING_DISPENSE_QUANTITY => l_PENDING_DISPENSE_QUANTITY ,
389 X_MAX_ALLOWED_QUANTITY => l_MAX_ALLOWED_QUANTITY ,
390 X_MIN_ALLOWED_QUANTITY => l_MIN_ALLOWED_QUANTITY ,
391 X_INSTRUCTION_ENTITY_DEF_KEY=> l_INSTRUCTION_ENTITY_DEF_KEY,
392 X_PLAN_UOM_CONVERTIBLE => l_plan_uom_converted,
393 X_RESERVATION_UOM_CONVERTIBLE => l_revervation_uom_converted,
394 X_SECURITY_FLAG =>l_security_flag);
395 -- change for kIosk : End
396 IF ( l_PENDING_DISPENSE_QUANTITY > 0 OR l_PENDING_DISPENSE_QUANTITY = -99999) THEN
397 return FND_API.G_TRUE;
398 END IF;
399 return FND_API.G_FALSE;
400 END IS_DISPENSE_REQUIRED;
401 /* Used by Dispesnig Bc4J objects to get reverse dispense related information */
402 -- Kiosk : Start
403 PROCEDURE GET_REVERSE_DISPENSE_DATA(P_DISPENSE_ID IN NUMBER,
404 X_MIN_ALLOWED_QTY OUT NOCOPY NUMBER,
405 X_MAX_ALLOWED_QTY OUT NOCOPY NUMBER,
406 X_CONFIG_ID OUT NOCOPY NUMBER,
407 X_INSTRUCTION_ENTITY_DEF_KEY OUT NOCOPY VARCHAR2,
408 X_SECURITY_FLAG OUT NOCOPY VARCHAR2)
409 IS
410 l_inventory_item_id NUMBER;
411 l_organization_id NUMBER;
412 l_material_details_id NUMBER;
413 l_dispense_config_rec GMO_DISPENSE_CONFIG%ROWTYPE;
414 l_net_dispensed_quantity NUMBER;
415 CURSOR C_GET_DISP_DETAILS IS
416 SELECT GMO.MATERIAL_DETAIL_ID, GMO.INVENTORY_ITEM_ID, GMO.ORGANIZATION_ID
417 FROM GMO_MATERIAL_DISPENSES GMO
418 WHERE GMO.DISPENSE_ID = P_DISPENSE_ID;
419 l_GET_DISP_DETAILS_REC C_GET_DISP_DETAILS%ROWTYPE;
420 BEGIN
421 X_INSTRUCTION_ENTITY_DEF_KEY := null;
422 X_CONFIG_ID := null;
423 -- Get the recipe id for the given dispense id
424 OPEN C_GET_DISP_DETAILS;
425 FETCH C_GET_DISP_DETAILS into l_GET_DISP_DETAILS_REC;
426 l_inventory_item_id := l_GET_DISP_DETAILS_REC.INVENTORY_ITEM_ID;
427 l_organization_id := l_GET_DISP_DETAILS_REC.ORGANIZATION_ID;
428 l_material_details_id := l_GET_DISP_DETAILS_REC.MATERIAL_DETAIL_ID;
429 CLOSE C_GET_DISP_DETAILS;
430 IF(l_inventory_item_id is null) THEN
431 return;
432 END IF;
433 l_net_dispensed_quantity := GET_NET_DISP_DISPENSED_QTY(P_DISPENSE_ID);
434 /* Bug 4946534: Starts. Replacing Dispense Seup API with new one to get
435 dispense config row based on instance table. */
436 GMO_DISPENSE_SETUP_PVT.GET_DISPENSE_CONFIG_INST(P_ENTITY_NAME => GMO_DISPENSE_GRP.G_MATERIAL_LINE_ENTITY,
437 P_ENTITY_KEY => l_material_details_id,
438 X_DISPENSE_CONFIG =>l_dispense_config_rec,
439 X_INSTRUCTION_DEFINITION_KEY => X_INSTRUCTION_ENTITY_DEF_KEY);
440 /* Bug 4946534: Ends */
441 IF(l_dispense_config_rec.config_id is null ) THEN
442 X_MIN_ALLOWED_QTY := 0;
443 X_MAX_ALLOWED_QTY := l_net_dispensed_quantity;
444 RETURN;
445 END IF;
446 X_CONFIG_ID := l_dispense_config_rec.CONFIG_ID;
447 -- change for kiosk : start
448 X_SECURITY_FLAG := l_dispense_config_rec.SECURECODE_REQUIRED_FLAG;
449
450 -- no partial reverse dispense check is required as per new requirement so
451 -- min qty would always be matching with dispensed qty to mimic 0 tolerance
452 X_MIN_ALLOWED_QTY := 0;
453 X_MAX_ALLOWED_QTY := l_net_dispensed_quantity;
454 /*
455 IF(l_dispense_config_rec.Tolerance_type = 'Q') THEN
456 X_MIN_ALLOWED_QTY := l_net_dispensed_quantity - l_dispense_config_rec.low_tolerance;
457 X_MAX_ALLOWED_QTY := l_net_dispensed_quantity;
458 ELSE
459 X_MIN_ALLOWED_QTY := l_net_dispensed_quantity - ((l_net_dispensed_quantity/100) * l_dispense_config_rec.low_tolerance);
460 X_MAX_ALLOWED_QTY := l_net_dispensed_quantity ;
461 END IF;
462 X_MIN_ALLOWED_QTY := greatest(X_MIN_ALLOWED_QTY, 0); -- must be a positive quantity
463 */
464 END GET_REVERSE_DISPENSE_DATA;
465 -- Kiosk : End
466 /* Used by group API */
467 PROCEDURE GET_MATERIAL_DISPENSE_DATA(p_material_detail_id IN NUMBER,
468 x_dispense_data OUT NOCOPY GME_COMMON_PVT.reservations_tab)
469 IS
470 CURSOR C_GET_RESERVATIONS IS
471 SELECT res.*
472 FROM MTL_RESERVATIONS res,
473 gme_material_Details gmd,
474 gme_batch_header gbh
475 WHERE res.demand_source_type_id = 5
476 and res.inventory_item_id = gmd.inventory_item_id
477 and GBH.BATCH_ID = GMD.BATCH_ID
478 and GBH.BATCH_STATUS in (1,2)
479 and RES.DEMAND_SOURCE_HEADER_ID = GBH.BATCH_ID
480 and res.demand_source_line_id = p_material_detail_id
481 and gmd.line_type = -1
482 and gme_api_grp.IS_RESERVATION_FULLY_SPECIFIED(res.reservation_id) = 1;
483 CURSOR C_GET_DISPENSES(p_reservation_id NUMBER) IS
484 SELECT Dispense_id,
485 dispensed_qty,
486 dispense_uom,
487 GMO_DISPENSE_PVT.GET_NET_DISP_DISPENSED_QTY(dispense_id) net_dispensed_qty
488 from gmo_material_dispenses
489 WHERE reservation_id = p_reservation_id
490 and material_status = 'DISPENSD'
491 and GMO_DISPENSE_PVT.GET_NET_DISP_DISPENSED_QTY(dispense_id) > 0;
492 l_reservation_record mtl_reservations%ROWTYPE;
493 l_dispense_record C_GET_DISPENSES%ROWTYPE;
494 l_result_record mtl_reservations%ROWTYPE;
495 l_Pending_reservations_tab GME_COMMON_PVT.reservations_tab;
496 CURSOR C_GET_MATERIAL_LINE IS
497 SELECT gmd.organization_id
498 FROM gme_material_details gmd
499 WHERE gmd.material_detail_id = p_material_detail_id;
500 l_material_line C_GET_MATERIAL_LINE%ROWTYPE;
501 RES_UOM_CONV_EXCEPTION EXCEPTION;
502 DISP_NOT_REQ_EXCEPTION EXCEPTION;
503 NO_MATERIAL_LINE_EXCEPTION EXCEPTION;
504 INSTRUNCTION_EXCEPTION EXCEPTION;
505 l_dispense_uom VARCHAR2(10);
506 l_dispense_config_row GMO_DISPENSE_CONFIG%ROWTYPE;
507 l_organization_id NUMBER;
508 l_RESERVED_QUANTITY NUMBER;
509 l_DISPENSE_CONFIG_ID NUMBER;
510 l_MAX_ALLOWED_QUANTITY NUMBER;
511 l_MIN_ALLOWED_QUANTITY NUMBER;
512 l_INSTRUCTION_ENTITY_DEF_KEY VARCHAR2(100);
513 l_revervation_uom_converted VARCHAR2(1);
514 l_reserved_qty NUMBER;
515 l_reserved_qty_in_sec_UOM NUMBER;
516 l_net_disp_qty_in_prim_UOM NUMBER;
517 l_instr_def_key VARCHAR2(40);
518 i integer;
519 j integer;
520 k integer;
521 l_lot_number varchar2(80);
522 L_TEMP_DISP_QTY NUMBER;
523 l_entity_type CONSTANT VARCHAR2(30) :='DISPENSE';
524 l_entity_name CONSTANT VARCHAR2(30) :='DISPENSE_ITEM';
525 l_disp_conf_entity_name CONSTANT VARCHAR2(30) :='MATERIAL_DETAILS_ID';
526 L_TOTAL_INSTRUCTIONS NUMBER;
527 L_OPTIONAL_PENDING_INSTR NUMBER;
528 L_MANDATORY_PENDING_INSTR NUMBER;
529 L_INSTRUCTION_PENDING VARCHAR2(5);
530 l_return_status varchar2(10);
531 l_msg_count NUMBER;
532 l_msg_data VARCHAR2(4000);
533 BEGIN
534 -- get the material line
535 OPEN C_GET_MATERIAL_LINE;
536 FETCH C_GET_MATERIAL_LINE INTO l_material_line;
537 CLOSE C_GET_MATERIAL_LINE;
538 -- raise exception if there is no material line
539 IF(l_material_line.organization_id is null) THEN
540 RAISE NO_MATERIAL_LINE_EXCEPTION;
541 ELSE
542 l_organization_id := l_material_line.organization_id;
543 END IF;
544 -- now that material line is found, get the dispense configuration for the same.
545 i := 1;
546 j := 1;
547 -- Bug 4946534: Starts. Replaced GET_DISPENSE_CONFIG with GET_DISPENSE_CONFIG_INST
548 --- This API would look into instances table and return the instantiated
549 -- configuration.
550 GMO_DISPENSE_SETUP_PVT.GET_DISPENSE_CONFIG_INST(P_ENTITY_NAME=> GMO_DISPENSE_GRP.G_MATERIAL_LINE_ENTITY,
551 P_ENTITY_KEY=> p_material_detail_id ,
552 X_DISPENSE_CONFIG => l_dispense_config_row,
553 X_INSTRUCTION_DEFINITION_KEY => l_instr_def_key);
554 -- Bug 4946534: Ends
555 if(l_dispense_config_row.config_id is null) then
556 RAISE DISP_NOT_REQ_EXCEPTION;
557 end if;
558 l_dispense_uom := l_dispense_config_row.dispense_uom;
559 /* Start with all reservations for the given material line */
560 OPEN C_GET_RESERVATIONS;
561 LOOP
562 FETCH C_GET_RESERVATIONS INTO l_reservation_record;
563 EXIT WHEN C_GET_RESERVATIONS%NOTFOUND;
564 l_reserved_qty := inv_convert.inv_um_convert(l_reservation_record.inventory_item_id,
565 l_reservation_record.lot_number,
566 l_organization_id,
567 5,
568 l_reservation_record.primary_reservation_quantity,
569 l_reservation_record.primary_uom_code,
570 l_dispense_uom,
571 null,
572 null
573 );
574 /* Check Reservation UOM */
575 IF(l_reserved_qty = -99999) THEN
576 RAISE RES_UOM_CONV_EXCEPTION ;
577 END IF;
578 /* Select dispensed rows that can be consumed for the given reservations */
579 OPEN C_GET_DISPENSES(l_reservation_record.reservation_id) ;
580 LOOP
581 FETCH C_GET_DISPENSES into l_dispense_record;
582 EXIT WHEN C_GET_DISPENSES%NOTFOUND;
583 l_result_record := l_reservation_record;
584 /* calculate pending consume quantity */
585 -- SP: 12/20/2005 Changed the condition
586 IF(l_dispense_record.dispense_uom = l_reservation_record.reservation_uom_code) THEN
587 l_reserved_qty := l_reserved_qty - l_dispense_record.net_dispensed_qty;
588 l_result_record.reservation_quantity := l_dispense_record.net_dispensed_qty; -- To be consumed for this dispense
589 ELSE
590 -- SP: 12/20/2005
591 -- Following Code was handling UOM change from Dispense row to dispense row with
592 -- Static Setup for a batch functionality implementation this is nolonger need
593 -- following code is modified to address additional Qty filed population
594 -- of reservation record (Bug 4892510)
595 l_temp_disp_qty := inv_convert.inv_um_convert(l_reservation_record.inventory_item_id,
596 l_reservation_record.lot_number,
597 l_organization_id,
598 5,
599 l_dispense_record.net_dispensed_qty,
600 l_dispense_record.dispense_uom,
601 l_reservation_record.reservation_uom_code,
602 null,
603 null
604 );
605 l_reserved_qty := l_reserved_qty - l_dispense_record.net_dispensed_qty;
606 l_result_record.reservation_quantity := l_temp_disp_qty; -- To be consumed for this dispense
607 END IF;
608 -- push the dispensed qty in secondary UOM also
609 If (l_reservation_record.SECONDARY_RESERVATION_QUANTITY is not null AND
610 l_reservation_record.SECONDARY_UOM_CODE is not null) THEN
611 l_reserved_qty_in_sec_UOM := inv_convert.inv_um_convert(l_reservation_record.inventory_item_id,
612 l_reservation_record.lot_number,
613 l_organization_id,
614 5,
615 l_dispense_record.net_dispensed_qty,
616 l_dispense_uom,
617 l_reservation_record.SECONDARY_UOM_CODE,
618 null,
619 null);
620 IF(l_reserved_qty_in_sec_UOM <> -9999 ) THEN
621 -- push secondary reservation quantity
622 l_result_record.SECONDARY_RESERVATION_QUANTITY := l_reserved_qty_in_sec_UOM;
623 -- push secondary detailed quantity
624 l_result_record.secondary_detailed_quantity := l_reserved_qty_in_sec_UOM;
625 END IF;
626 END IF;
627 -- push primary reservation quantity
628 IF(l_reservation_record.primary_uom_code is not null ) then
629 l_net_disp_qty_in_prim_UOM := inv_convert.inv_um_convert(l_reservation_record.inventory_item_id,
630 l_reservation_record.lot_number,
631 l_organization_id,
632 5,
633 l_dispense_record.net_dispensed_qty,
634 l_dispense_uom,
635 l_reservation_record.primary_uom_code,
636 null,
637 null);
638 IF(l_net_disp_qty_in_prim_UOM <> -9999) THEN
639 l_result_record.primary_reservation_quantity := l_net_disp_qty_in_prim_UOM;
640 END IF;
641 END IF;
642 -- push detailed quantity
643 l_result_record.detailed_quantity := l_result_record.reservation_quantity;
644 -- push serial reservation quantity
645 l_result_record.serial_reservation_quantity := l_result_record.reservation_quantity;
646 -- SP: 12/20/2005
647 -- Following UOM Code assignment code is commented as
648 -- Quantity need to be returned in reservation UOM.
649 --l_result_record.reservation_uom_code := l_dispense_uom;
650 l_result_record.external_source_line_id := l_dispense_record.dispense_id;
651 -- Bug 4959469: Starts
652 -- Make external_source_line_id as -1 if there are any mandatory instruction
653 --- pending for this dispense record.
654 GMO_INSTRUCTION_PVT.HAS_PENDING_INSTRUCTIONS(P_ENTITY_NAME =>l_entity_name,
655 P_ENTITY_KEY =>l_dispense_record.dispense_id,
656 P_INSTRUCTION_TYPE =>l_entity_type,
657 X_INSTRUCTION_PENDING =>L_INSTRUCTION_PENDING,
658 X_TOTAL_INSTRUCTIONS => L_TOTAL_INSTRUCTIONS,
659 X_OPTIONAL_PENDING_INSTR =>L_OPTIONAL_PENDING_INSTR,
660 X_MANDATORY_PENDING_INSTR =>L_MANDATORY_PENDING_INSTR,
661 X_RETURN_STATUS =>l_return_status,
662 X_MSG_COUNT =>l_msg_count,
663 X_MSG_DATA =>l_msg_data);
664 IF(l_return_status<> FND_API.G_RET_STS_SUCCESS ) THEN
665 RAISE INSTRUNCTION_EXCEPTION;
666 END IF;
667 IF (L_MANDATORY_PENDING_INSTR > 0) THEN
668 l_result_record.external_source_line_id := -1;
669 END IF;
670 -- Bug 4959469 : Ends
671 x_dispense_data(i) := l_result_record;
672 i:= i+1;
673 END LOOP; /* End loop for dispense records */
674 CLOSE C_GET_DISPENSES;
675 if(l_reserved_qty > 0) then
676 -- SP: 12/20/2005
677 -- We need convert reservation qty from dispense UOM to reservation UOM
678 -- (Bug 4892510)
679 l_reserved_qty := inv_convert.inv_um_convert(l_reservation_record.inventory_item_id,
680 l_reservation_record.lot_number,
681 l_organization_id,
682 5,
683 l_reserved_qty,
684 l_dispense_uom,
685 l_reservation_record.reservation_uom_code ,
686 null,
687 null
688 );
689 l_reservation_record.reservation_quantity := l_reserved_qty;
690 -- SP: 12/20/2005
691 -- Following UOM Code assignment code is commented as
692 -- Quantity need to be returned in reservation UOM.
693 -- l_reservation_record.reservation_uom_code := l_dispense_uom;
694 -- push primary reservation quantity
695 IF (l_reservation_record.primary_uom_code is not null ) then
696 l_net_disp_qty_in_prim_UOM := inv_convert.inv_um_convert(l_reservation_record.inventory_item_id,
697 l_reservation_record.lot_number,
698 l_organization_id,
699 5,
700 l_reserved_qty,
701 l_dispense_uom,
702 l_reservation_record.primary_uom_code,
703 null,
704 null);
705 IF(l_net_disp_qty_in_prim_UOM <> -9999) THEN
706 l_reservation_record.primary_reservation_quantity := l_net_disp_qty_in_prim_UOM;
707 END IF;
708 END IF;
709 -- push detailed quantity
710 l_reservation_record.detailed_quantity := l_reservation_record.reservation_quantity;
711 -- push serial reservation quantity
712 l_reservation_record.serial_reservation_quantity := l_reservation_record.reservation_quantity;
713 If(l_reservation_record.SECONDARY_RESERVATION_QUANTITY is not null AND
714 l_reservation_record.SECONDARY_UOM_CODE is not null) THEN
715 l_reserved_qty_in_sec_UOM := inv_convert.inv_um_convert(l_reservation_record.inventory_item_id,
716 l_reservation_record.lot_number,
717 l_organization_id,
718 5,
719 l_reserved_qty,
720 l_dispense_uom,
721 l_reservation_record.SECONDARY_UOM_CODE,
722 null,
723 null);
724 IF (l_reserved_qty_in_sec_UOM <> -9999 ) THEN
725 l_reservation_record.SECONDARY_RESERVATION_QUANTITY := l_reserved_qty_in_sec_UOM;
726 -- push secondary detailed quantity
727 l_reservation_record.secondary_detailed_quantity := l_reserved_qty_in_sec_UOM;
728 END IF;
729 END IF;
730 l_pending_reservations_tab(j) := l_reservation_record;
731 j := j+1;
732 end if;
733 END LOOP; /* End loop for reservations records */
734 CLOSE C_GET_RESERVATIONS;
735 /* Append the pending reservation in the end to x_dispense_data. */
736 IF(j>1) THEN
737 FOR K IN 1..J-1 LOOP
738 x_dispense_data(i) := l_pending_reservations_tab(k);
739 i := i+1;
740 END LOOP;
741 END IF;
742 EXCEPTION
743 WHEN INSTRUNCTION_EXCEPTION THEN
744 FND_MESSAGE.SET_ENCODED(l_msg_data);
745 APP_EXCEPTION.RAISE_EXCEPTION;
746 WHEN NO_MATERIAL_LINE_EXCEPTION THEN
747 FND_MESSAGE.SET_NAME('GMO','GMO_DISP_NO_MTL_LINE_ERR');
748 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
749 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
750 'gmo.plsql.GMO_DISPENSE_GRP.GET_MATERIAL_DISPENSE_DATA',
751 FALSE
752 );
753 end if;
754 RAISE;
755 WHEN DISP_NOT_REQ_EXCEPTION THEN
756 FND_MESSAGE.SET_NAME('GMO','GMO_DISP_DISPENSE_NOT_REQ_ERR');
757 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
758 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
759 'gmo.plsql.GMO_DISPENSE_GRP.GET_MATERIAL_DISPENSE_DATA',
760 FALSE
761 );
762 end if;
763 RAISE;
764 WHEN RES_UOM_CONV_EXCEPTION THEN
765 FND_MESSAGE.SET_NAME('GMO','GMO_DISP_RES_CONV_ERR');
766 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
767 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
768 'gmo.plsql.GMO_DISPENSE_GRP.GET_MATERIAL_DISPENSE_DATA',
769 FALSE
770 );
771 end if;
772 RAISE;
773 END GET_MATERIAL_DISPENSE_DATA;
774 /* Returns last dispense Id, used in dispense instructions checks */
775 FUNCTION GET_LATEST_DISPENSE_ID (p_batch_id IN NUMBER) RETURN NUMBER
776 IS
777 l_return_value NUMBER;
778 -- change for kiosk : start
779 CURSOR C_GET_MAX_DISPENSE_ID IS
780 SELECT MAX(dispense_id)
781 FROM GMO_MATERIAL_DISPENSES
782 WHERE BATCH_ID = p_batch_id;
783 -- change for kiosk : End
784 BEGIN
785 l_return_value := null;
786 OPEN C_GET_MAX_DISPENSE_ID;
787 FETCH C_GET_MAX_DISPENSE_ID into l_return_value;
788 CLOSE C_GET_MAX_DISPENSE_ID;
789 return l_return_value;
790 END;
791 /* Returns last reverse dispense Id, used in dispense instructions checks */
792 FUNCTION GET_LATEST_REVERSE_DISPENSE_ID (p_dispense_id IN NUMBER) RETURN NUMBER
793 IS
794 l_return_value NUMBER;
795 CURSOR C_GET_LATEST_UNDISPENSE_ID IS
796 SELECT MAX(undispense_id)
797 FROM GMO_MATERIAL_UNDISPENSES
798 WHERE DISPENSE_ID = p_dispense_id;
799 BEGIN
800 l_return_value := null;
801 OPEN C_GET_LATEST_UNDISPENSE_ID;
802 FETCH C_GET_LATEST_UNDISPENSE_ID into l_return_value;
803 CLOSE C_GET_LATEST_UNDISPENSE_ID;
804 return l_return_value;
805 END;
806 /* Dispese wrapper for label request */
807 FUNCTION GET_LABEL_REQUEST_ID (p_entity_id NUMBER,
808 p_context_param_names FND_TABLE_OF_VARCHAR2_255,
809 p_context_param_values FND_TABLE_OF_VARCHAR2_255,
810 p_label_string VARCHAR2,
811 p_entity_type VARCHAR2) RETURN NUMBER
812 IS
813 l_label_request_id NUMBER;
814 L_CONTEXT GMO_LABEL_MGMT_GRP.CONTEXT_TABLE;
815 L_RETURN_STATUS VARCHAR2(1);
816 L_MSG_COUNT integer;
817 l_transaction_type VARCHAR2(100);
818 L_MSG_DATA VARCHAR2(4000);
819 l_EXCEPTION EXCEPTION;
820 BEGIN
821 FOR i IN p_context_param_names.first..p_context_param_names.last LOOP
822 L_CONTEXT(i).NAME := p_context_param_names(i);
823 L_CONTEXT(i).VALUE := p_context_param_values(i);
824 L_CONTEXT(i).DISPLAY_SEQUENCE := i;
825 END LOOP;
826 if(p_entity_type='DISPENSE') then
827 l_transaction_type := INV_LABEL.TRX_ID_DIS;
828 else
829 l_transaction_type := INV_LABEL.TRX_ID_UNDIS;
830 end if;
831 GMO_LABEL_MGMT_GRP.PRINT_LABEL
832 (P_API_VERSION=>1,
833 P_INIT_MSG_LIST=>FND_API.G_TRUE,
834 X_RETURN_STATUS=>L_RETURN_STATUS,
835 X_MSG_COUNT=>L_MSG_COUNT,
836 X_MSG_DATA=>L_MSG_DATA,
837 P_ENTITY_NAME=>'GMO_DISPENSING',
838 P_ENTITY_KEY=> to_char(p_entity_id),
839 P_WMS_BUSINESS_FLOW_CODE => 38,
840 P_LABEL_TYPE=>p_label_string,
841 P_TRANSACTION_ID=> to_char(p_entity_id),
842 P_TRANSACTION_TYPE=>l_transaction_type,
843 P_APPLICATION_SHORT_NAME=>'GMO',
844 P_REQUESTER=>fnd_global.user_id,
845 P_CONTEXT=>L_CONTEXT,
846 X_LABEL_ID=> l_label_request_id);
847 IF(L_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS ) THEN
848 RAISE l_EXCEPTION;
849 END IF;
850 return l_label_request_id;
851 EXCEPTION WHEN l_EXCEPTION THEN
852 FND_MESSAGE.SET_ENCODED(L_MSG_DATA);
853 APP_EXCEPTION.RAISE_EXCEPTION;
854 WHEN OTHERS THEN
855 raise;
856 END GET_LABEL_REQUEST_ID;
857 /* Label wrapper for dispensing */
858 FUNCTION IS_AUTO_PRINT_ENABLED RETURN VARCHAR2
859 IS
860 BEGIN
861 IF(GMO_LABEL_MGMT_GRP.AUTO_PRINT_ENABLED ()) THEN
862 return FND_API.G_TRUE;
863 END IF;
864 return FND_API.G_FALSE;
865 END;
866 /* Used by dispense dispatch report query*/
867 FUNCTION GET_PENDING_DISPENSE_QTY(P_RESERVATION_ID NUMBER,
868 P_INVENTORY_ITEM_ID NUMBER,
869 P_ORGANIZATION_ID NUMBER,
870 P_RECIPE_ID NUMBER ,
871 P_MATERIAL_DETAILS_ID NUMBER,
872 P_RESERVATION_UOM VARCHAR2,
873 P_RESERVED_QUANTITY NUMBER,
874 P_PLAN_QUANTITY NUMBER,
875 P_PLAN_UOM VARCHAR2,
876 P_LOT_NUMBER VARCHAR2) RETURN NUMBER
877 IS
878 l_DISPENSE_UOM VARCHAR2(10);
879 l_DISPENSE_CONFIG_ID NUMBER;
880 l_RESERVED_QUANTITY NUMBER;
881 l_PENDING_DISPENSE_QUANTITY NUMBER;
882 l_MAX_ALLOWED_QUANTITY NUMBER;
883 l_MIN_ALLOWED_QUANTITY NUMBER;
884 l_INSTRUCTION_ENTITY_DEF_KEY VARCHAR2(20);
885 l_plan_uom_converted VARCHAR2(1);
886 l_revervation_uom_converted VARCHAR2(1);
887 l_security_flag varchar2(1);
888 BEGIN
889 -- kiosk : start
890 GET_DISPENSE_DATA( P_RESERVATION_ID =>P_RESERVATION_ID ,
891 P_INVENTORY_ITEM_ID => P_INVENTORY_ITEM_ID ,
892 P_ORGANIZATION_ID => P_ORGANIZATION_ID,
893 P_RECIPE_ID => P_RECIPE_ID ,
894 P_MATERIAL_DETAILS_ID => P_MATERIAL_DETAILS_ID ,
895 P_RESERVED_QUANTITY => P_RESERVED_QUANTITY ,
896 P_RESERVATION_UOM => P_RESERVATION_UOM,
897 P_PLAN_QUANTITY => P_PLAN_QUANTITY,
898 P_PLAN_UOM => P_PLAN_UOM,
899 P_LOT_NUMBER => P_LOT_NUMBER,
900 X_DISPENSE_UOM => l_DISPENSE_UOM ,
901 X_DISPENSE_CONFIG_ID => l_DISPENSE_CONFIG_ID,
902 X_RESERVED_QUANTITY => l_RESERVED_QUANTITY ,
903 X_PENDING_DISPENSE_QUANTITY => l_PENDING_DISPENSE_QUANTITY ,
904 X_MAX_ALLOWED_QUANTITY => l_MAX_ALLOWED_QUANTITY ,
905 X_MIN_ALLOWED_QUANTITY => l_MIN_ALLOWED_QUANTITY ,
906 X_INSTRUCTION_ENTITY_DEF_KEY=> l_INSTRUCTION_ENTITY_DEF_KEY,
907 X_PLAN_UOM_CONVERTIBLE => l_plan_uom_converted,
908 X_RESERVATION_UOM_CONVERTIBLE => l_revervation_uom_converted,
909 X_SECURITY_FLAG=>l_security_flag );
910 -- kiosk : End
911 IF ( l_PENDING_DISPENSE_QUANTITY = -99999) THEN
912 l_PENDING_DISPENSE_QUANTITY := 0;
913 END IF;
914 return l_PENDING_DISPENSE_QUANTITY ;
915 END;
916 --This procedure performs the following operations:
917 -- 1. It obtains the process instruction details identified by the instruction process ID
918 -- in XML FORMAT.
919 -- 2. If P_CURRENT_XML (which is the current transaction XML) is not null then it is merged with the XML data
920 -- fetched in the previous step.
921 -- 3. The merged XML is encapsulated in the root node <ERecord> with UTF-8 encoding.
922 PROCEDURE GET_TRANSACTION_XML(P_INSTRUCTION_PROCESS_ID IN NUMBER,
923 P_CURRENT_XML IN CLOB,
924 X_OUTPUT_XML OUT NOCOPY CLOB)
925 IS
926 --This variable hold the return status value returned by the process instructions API.
927 L_RETURN_STATUS VARCHAR2(10);
928 --This variable holds the message count value returned by the process instructions API.
929 L_MSG_COUNT NUMBER;
930 --This variable holds the messsage data returned by the process instructions API.
931 L_MSG_DATA VARCHAR2(4000);
932 --This variable would be used to hold the XML data representing the process instructions data identified
933 --by the instruction process ID.
934 L_INSTR_XML CLOB;
935 --This exception would be raised if the API to get the XML from process instructions return an error status.
936 XML_ERROR EXCEPTION;
937 BEGIN
938 --Set L_INSTR_XML to null.
939 L_INSTR_XML := null;
940 --Initialize the final XML CLOB holder.
941 DBMS_LOB.CREATETEMPORARY(X_OUTPUT_XML, TRUE, DBMS_LOB.SESSION);
942 --Write the XML Header into the CLOB.
943 DBMS_LOB.WRITEAPPEND(X_OUTPUT_XML,LENGTH(EDR_CONSTANTS_GRP.G_ERECORD_XML_HEADER),EDR_CONSTANTS_GRP.G_ERECORD_XML_HEADER);
944 --If the current transaction XML is not null then append the same into the final XML CLOB holder.
945 IF P_CURRENT_XML IS NOT NULL AND DBMS_LOB.GETLENGTH(P_CURRENT_XML) > 0 THEN
946 DBMS_LOB.APPEND(X_OUTPUT_XML,P_CURRENT_XML);
947 END IF;
948 --Obtain the process instruction details in XML format for the specified instruction process ID.
949 GMO_INSTRUCTION_GRP.GET_INSTR_INSTANCE_XML
950 (P_API_VERSION => 1.0,
951 P_INIT_MSG_LIST => FND_API.G_TRUE,
952 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_NONE,
953 P_INSTRUCTION_PROCESS_ID => P_INSTRUCTION_PROCESS_ID,
954 X_OUTPUT_XML => L_INSTR_XML,
955 X_RETURN_STATUS => L_RETURN_STATUS,
956 X_MSG_COUNT => L_MSG_COUNT,
957 X_MSG_DATA => L_MSG_DATA);
958 --If the return status is EXCEPTION or UNEXPECTED ERROR then raise an exception.
959 IF L_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR OR L_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
960 RAISE XML_ERROR;
961 END IF;
962 --If the XML representing the process instruction details is not null then append the same into the final XMl CLOB holder.
963 IF L_INSTR_XML IS NOT NULL AND DBMS_LOB.GETLENGTH(L_INSTR_XML) > 0 THEN
964 DBMS_LOB.APPEND(X_OUTPUT_XML,L_INSTR_XML);
965 END IF;
966 --Append the XML Footer into the final XML CLOB holder.
967 DBMS_LOB.WRITEAPPEND(X_OUTPUT_XML,LENGTH(EDR_CONSTANTS_GRP.G_ERECORD_XML_FOOTER),EDR_CONSTANTS_GRP.G_ERECORD_XML_FOOTER);
968 EXCEPTION
969 WHEN XML_ERROR THEN
970 FND_MESSAGE.SET_ENCODED(L_MSG_DATA);
971 --Diagnostics Start
972 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
973 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
974 'gmo.plsql.GMO_DISPENSE_PVT.GET_TRANSACTION_XML',
975 FALSE
976 );
977 end if;
978 --Diagnostics End
979 APP_EXCEPTION.RAISE_EXCEPTION;
980 WHEN OTHERS THEN
981 FND_MESSAGE.SET_NAME('FND','FND_AS_UNEXPECTED_ERROR');
982 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
983 FND_MESSAGE.SET_TOKEN('PKG_NAME','GMO_DISPENSE_PVT');
984 FND_MESSAGE.SET_TOKEN('PROCEDURE_NAME','GET_TRANSACTION_XML');
985 --Diagnostics Start
986 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
987 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
988 'gmo.plsql.GMO_DISPENSE_PVT.GET_TRANSACTION_XML',
989 FALSE
990 );
991 end if;
992 --Diagnostics End
993 APP_EXCEPTION.RAISE_EXCEPTION;
994 END GET_TRANSACTION_XML;
995 -- Returns Total Reverse Dispensed Quantity for a given dispense id
996 FUNCTION GET_NET_REVERSE_DISPENSED_QTY (P_DISPENSE_ID IN NUMBER) RETURN NUMBER
997 IS
998 CURSOR REV_DISP_CUR IS
999 SELECT sum(nvl(undispensed_qty,0))
1000 FROM GMO_MATERIAL_UNDISPENSES
1001 WHERE dispense_id = p_dispense_id;
1002 l_return_value NUMBER;
1003 BEGIN
1004 l_return_value := null;
1005 OPEN REV_DISP_CUR;
1006 FETCH REV_DISP_CUR INTO l_return_value;
1007 CLOSE REV_DISP_CUR;
1008 return l_return_value;
1009 END GET_NET_REVERSE_DISPENSED_QTY;
1010 -- Returns Total Material Loss for a given dispense id
1011 FUNCTION GET_NET_MATERIAL_LOSS (P_DISPENSE_ID IN NUMBER) RETURN NUMBER
1012 IS
1013 CURSOR REV_DISP_CUR IS
1014 SELECT sum(nvl(material_loss,0))
1015 FROM GMO_MATERIAL_UNDISPENSES
1016 WHERE dispense_id = p_dispense_id;
1017 l_return_value NUMBER;
1018 BEGIN
1019 l_return_value := null;
1020 OPEN REV_DISP_CUR;
1021 FETCH REV_DISP_CUR INTO l_return_value;
1022 CLOSE REV_DISP_CUR;
1023 return l_return_value;
1024 END GET_NET_MATERIAL_LOSS;
1025
1026 Function isDispenseOccuredAtDispBooth(disp_booth_id number) return varchar2
1027 as
1028
1029 countValue number default 0;
1030
1031 begin
1032
1033 select count(dispense_booth_id) into countValue from gmo_material_dispenses
1034 where dispense_booth_id = disp_booth_id;
1035
1036 if(countValue > 0) then
1037 return 'Yes';
1038 else
1039 select count(dispense_booth_id) into countValue from gmo_material_undispenses
1040 where dispense_booth_id = disp_booth_id;
1041 if(countValue > 0) then
1042 return 'Yes';
1043 end if;
1044 end if;
1045 return 'No';
1046 end;
1047
1048 Function isDispenseOccuredAtDispArea(disp_area_id number) return varchar2
1049 as
1050
1051 countValue number default 0;
1052
1053 begin
1054
1055 select count(dispense_area_id) into countValue from gmo_material_dispenses
1056 where dispense_area_id = disp_area_id;
1057
1058 if(countValue > 0) then
1059 return 'Yes';
1060 else
1061 select count(dispense_area_id) into countValue from gmo_material_undispenses
1062 where dispense_area_id = disp_area_id;
1063 if(countValue > 0) then
1064 return 'Yes';
1065 else
1066 select count(dispense_area_id) into countValue from gmo_dispensing_planning
1067 where dispense_area_id = disp_area_id;
1068 if(countValue > 0) then
1069 return 'Yes';
1070 end if;
1071 end if;
1072 end if;
1073 return 'No';
1074 end;
1075
1076
1077 END GMO_DISPENSE_PVT;