1 PACKAGE BODY GML_RCV_DB_COMMON AS
2 /* $Header: GMLRCVCB.pls 120.2 2006/03/02 10:36:40 pbamb noship $ */
3
4 -- Read the profile option that enables/disables the debug log
5 g_fnd_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
6
7 G_PKG_NAME CONSTANT VARCHAR2(30) :='GML_RCV_DB_COMMON';
8 g_module_prefix CONSTANT VARCHAR2(40) := 'gml.plsql.' || g_pkg_name || '.';
9
10 /*##########################################################################
11 #
12 # FUNCTION
13 # raise_quality_event
14 #
15 # DESCRIPTION
16 #
17 # This function raises the OPM Quality to create samples if and OPM item
18 # is received into a receiving location of a process org. This procedure
19 # is called from rvtth.lpc.
20 #
21 #
22 # MODIFICATION HISTORY
23 # 31-OCT-2002 PBamb Created
24 #
25 ## #######################################################################*/
26
27 PROCEDURE RAISE_QUALITY_EVENT( x_transaction_id IN NUMBER,
28 x_item_id IN NUMBER,
29 x_organization_id IN NUMBER) IS
30
31 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
32 l_event_name VARCHAR2(120);
33 l_event_key VARCHAR2(240);
34 l_process_org VARCHAR2(2);
35 l_process_qlty_flag VARCHAR2(2);
36
37 CURSOR cr_check_process_qlty_flag IS
38 SELECT PROCESS_QUALITY_ENABLED_FLAG
39 FROM mtl_system_items_b mi
40 WHERE mi.inventory_item_id = x_item_id
41 AND mi.organization_id = x_organization_id;
42
43 BEGIN
44 /*Check if the organization is process enabled */
45 BEGIN
46 Select process_enabled_flag
47 Into l_process_org
48 From mtl_parameters
49 Where organization_id = x_organization_id;
50
51 EXCEPTION WHEN NO_DATA_FOUND THEN
52 l_process_org := 'N';
53 END;
54
55
56 /*Check if the item is process quality enabled */
57 Open cr_check_process_qlty_flag;
58 Fetch cr_check_process_qlty_flag into l_process_qlty_flag;
59 If cr_check_process_qlty_flag%NOTFOUND Then
60 l_process_qlty_flag:='N';
61 End if;
62 Close cr_check_process_qlty_flag;
63
64 IF l_process_org = 'Y' and l_process_qlty_flag= 'Y' THEN
65
66
67 wf_event.AddParameterToList( p_name=>'TRANSACTION_ID',
68 p_value=> X_Transaction_id ,
69 p_parameterlist=> l_parameter_list);
70
71
72 l_event_name := 'oracle.apps.gml.po.receipt.created';
73
74 l_event_key := X_Transaction_id;
75
76 wf_event.raise( p_event_name => l_event_name,
77 p_event_key => l_event_key,
78 p_parameters => l_parameter_list);
79 END IF;
80
81 EXCEPTION
82 WHEN OTHERS THEN
83 null;
84
85
86 END RAISE_QUALITY_EVENT;
87
88 /*##########################################################################
89 #
90 # PROCEDURE
91 # validate_rma_lot_quantities
92 #
93 # DESCRIPTION
94 #
95 # This function gets input of receiving lot quantity and validates whether
96 # RMA OE line has that quantity to be received by checking the rma lot
97 # quantity and the received quantity against that lot.
98 # Its called from ROI and RCVGMLCR.pld
99 #
100 #
101 # MODIFICATION HISTORY
102 # 16-OCT-2003 PBamb Created
103 #
104 ## #######################################################################*/
105
106 Procedure VALIDATE_RMA_LOT_QUANTITIES( p_api_version IN NUMBER,
107 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
108 p_opm_item_id IN NUMBER,
109 p_lot_id IN NUMBER,
110 p_lot_no IN VARCHAR2,
111 p_sublot_no IN VARCHAR2,
112 p_oe_header_id IN NUMBER,
113 p_oe_line_id IN NUMBER,
114 p_trx_qty IN NUMBER,
115 p_trx_uom IN VARCHAR2,
116 p_rma_lot_qty IN NUMBER,
117 p_rma_lot_uom IN VARCHAR2,
118 p_line_set_id IN NUMBER,
119 p_called_from IN VARCHAR2 DEFAULT 'FORM',
120 X_allowed OUT NOCOPY VARCHAR2,
121 X_allowed_quantity OUT NOCOPY NUMBER,
122 x_return_status OUT NOCOPY VARCHAR2)
123
124
125 IS
126
127 l_lot_recv_qty NUMBER;
128 l_rma_answer BOOLEAN := FALSE;
129
130 Cursor Cr_lot_recv_qty IS
131 Select Sum(trans_qty),trans_um
132 From ic_tran_pnd itp,
133 rcv_transactions rcv
134 Where rcv.oe_order_header_id = p_oe_header_id
135 And (rcv.oe_order_line_id = p_oe_line_id
136 or
137 rcv.oe_order_line_id in
138 (select line_id
139 from oe_order_lines_all
140 where header_id = p_oe_header_id
141 and line_set_id = p_line_set_id
142 )
143 )
144 And rcv.shipment_header_id = itp.doc_id
145 And rcv.transaction_id = itp.line_id
146 And itp.doc_type = 'PORC'
147 And itp.lot_id = p_lot_id
148 And itp.delete_mark = 0
149 Group by trans_um;
150
151
152 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Rma_Lot_Quantities' ;
153 l_api_version CONSTANT NUMBER := 1.0 ;
154 l_progress VARCHAR2(3) := '000';
155
156 l_trx_opm_uom SY_UOMS_MST.UM_CODE%TYPE;
157 l_rma_lot_opm_uom SY_UOMS_MST.UM_CODE%TYPE;
158 l_lot_recv_uom SY_UOMS_MST.UM_CODE%TYPE;
159 l_trx_opm_qty NUMBER;
160 l_rma_lot_opm_qty NUMBER;
161
162 l_rma_lot_uom VARCHAR2(25);
163 l_rma_lot_qty NUMBER;
164
165 BEGIN
166
167 -- IF (g_fnd_debug = 'Y') THEN Bug 4502018
168 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
169 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE, g_module_prefix || l_api_name,
170 'Entering ' || l_api_name );
171 END IF;
172
173 IF FND_API.to_boolean(p_init_msg_list) THEN
174 FND_MSG_PUB.Initialize;
175 END IF;
176
177 -- Standard call to check for call compatibility.
178 IF NOT FND_API.Compatible_API_Call ( l_api_version,
179 p_api_version,
180 l_api_name ,
181 G_PKG_NAME
182 ) THEN
183 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
184 END IF;
185
186 x_return_status :=FND_API.G_RET_STS_SUCCESS;
187
188 --If any of these parameters are NULL nothing can be done so just
189 --returns as if successful.
190 IF p_opm_item_id IS NULL OR
191 p_oe_header_id IS NULL OR
192 p_oe_line_id IS NULL OR
193 p_trx_qty IS NULL OR
194 p_trx_uom IS NULL THEN
195
196 X_allowed := 'Y';
197 RETURN;
198 END IF;
199
200 l_progress := '001';
201
202 --Fetch UOM of RMA if not passed and get the corresponding OPM UOM Code.
203 --Cannot use po_gml_db_common,get_opm_uom_code because this procedure expects
204 --a 25 character unit of measure and OM does not have it.OM has the 4 character
205 --uom_code - so derive the corresponding opm uom code.
206 IF p_rma_lot_uom IS NULL THEN
207
208 l_progress := '002';
209
210 /*Select ORDER_QUANTITY_UOM
211 Into l_rma_lot_uom
212 From oe_order_lines_all
213 Where header_id = p_oe_header_id
214 And line_id = p_oe_line_id;
215 --Get corresponding OPM UOM.
216 l_rma_lot_opm_uom := PO_GML_DB_COMMON.GET_OPM_UOM_CODE(l_rma_lot_uom);
217 */
218 BEGIN
219 Select decode(length(uom.unit_of_measure), 1, uom.unit_of_measure,
220 2, uom.unit_of_measure, 3, uom.unit_of_measure,
221 4, uom.unit_of_measure, uom.uom_code)
222 Into l_rma_lot_opm_uom
223 From oe_order_lines_all , mtl_units_of_measure uom
224 Where header_id = p_oe_header_id
225 And line_id = p_oe_line_id
226 And uom_code = ORDER_QUANTITY_UOM;
227
228 EXCEPTION
229 WHEN OTHERS THEN
230 FND_MESSAGE.SET_NAME('GMI','GMI_SQL_ERROR');
231 FND_MESSAGE.SET_TOKEN('WHERE','GML_RCV_DB_COMMON.VALIDATE_RMA_LOT_QUANTITIES'||'-'||l_progress);
232 FND_MESSAGE.SET_TOKEN('SQL_CODE',SQLCODE);
233 FND_MESSAGE.SET_TOKEN('SQL_ERRM',SQLERRM(SQLCODE));
234 FND_MSG_PUB.ADD;
235 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
236 END;
237
238
239 ELSE
240 l_progress := '003';
241 --Get corresponding OPM UOM Code.
242 --l_rma_lot_opm_uom := PO_GML_DB_COMMON.GET_OPM_UOM_CODE(p_rma_lot_uom);
243 BEGIN
244 Select decode(length(uom.unit_of_measure), 1, uom.unit_of_measure,
245 2, uom.unit_of_measure, 3, uom.unit_of_measure,
246 4, uom.unit_of_measure, uom.uom_code)
247 Into l_rma_lot_opm_uom
248 From mtl_units_of_measure uom
249 Where uom_code = p_rma_lot_uom;
250
251 EXCEPTION
252 WHEN OTHERS THEN
253 FND_MESSAGE.SET_NAME('GMI','GMI_SQL_ERROR');
254 FND_MESSAGE.SET_TOKEN('WHERE','GML_RCV_DB_COMMON.VALIDATE_RMA_LOT_QUANTITIES'||'-'||l_progress);
255 FND_MESSAGE.SET_TOKEN('SQL_CODE',SQLCODE);
256 FND_MESSAGE.SET_TOKEN('SQL_ERRM',SQLERRM(SQLCODE));
257 FND_MSG_PUB.ADD;
258 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
259 END;
260 END IF;
261
262 --Fetch QTY of the LOT in the RMA if not passed.
263 IF p_rma_lot_qty IS NULL THEN
264 l_progress := '004';
265
266 BEGIN
267 -- PB -- OM allows duplicate entry of lots in their lot serial form. We need to sum up the quantity
268 -- since we do not allow duplicate lots on the lot serial form while receiving. So person has to
269 -- receive in one lot..sum up the quantities.
270 Select nvl(SUM(QUANTITY),0)
271 Into l_rma_lot_qty
272 From oe_lot_Serial_numbers
273 Where (line_id = p_oe_line_id OR line_set_id = p_line_set_id)
274 And lot_number = p_lot_no
275 And nvl(sublot_number,' ') = nvl(p_sublot_no,' ');
276
277 EXCEPTION
278 WHEN OTHERS THEN
279 FND_MESSAGE.SET_NAME('GMI','GMI_SQL_ERROR');
280 FND_MESSAGE.SET_TOKEN('WHERE','GML_RCV_DB_COMMON.VALIDATE_RMA_LOT_QUANTITIES'||'-'||l_progress);
281 FND_MESSAGE.SET_TOKEN('SQL_CODE',SQLCODE);
282 FND_MESSAGE.SET_TOKEN('SQL_ERRM',SQLERRM(SQLCODE));
283 FND_MSG_PUB.ADD;
284 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
285 END;
286 ELSE
287 l_rma_lot_qty := p_rma_lot_qty;
288
289 l_progress := '005';
290 END IF;
291
292 --Get the Transaction (receipt) OPM UOM CODE.
293 BEGIN
294 l_trx_opm_uom := PO_GML_DB_COMMON.GET_OPM_UOM_CODE(p_trx_uom);
295
296 EXCEPTION
297 WHEN OTHERS THEN
298 FND_MSG_PUB.ADD;
299 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
300 END;
301
302 l_progress := '006';
303
304 --If lot id is passed that means it was probably received before
305 --or they used an existing lot.
306 IF p_lot_id IS NOT NULL THEN
307
308 l_progress := '007';
309
310 --Get received quantity of the Lot.
311 Open Cr_lot_recv_qty;
312 Fetch Cr_lot_recv_qty Into l_lot_recv_qty, l_lot_recv_uom;
313 Close Cr_lot_recv_qty;
314
315 --If this lot was never received then the receiving uom will be null then fetch
316 --Primary uom from the ic_item_mst.
317 IF l_lot_recv_uom IS NULL THEN
318 BEGIN
319 Select item_um
320 Into l_lot_recv_uom
321 From ic_item_mst
322 Where item_id = p_opm_item_id;
323
324 EXCEPTION
325 WHEN OTHERS THEN
326 FND_MESSAGE.SET_NAME('GMI','GMI_SQL_ERROR');
327 FND_MESSAGE.SET_TOKEN('WHERE','GML_RCV_DB_COMMON.VALIDATE_RMA_LOT_QUANTITIES'||'-'||l_progress);
328 FND_MESSAGE.SET_TOKEN('SQL_CODE',SQLCODE);
329 FND_MESSAGE.SET_TOKEN('SQL_ERRM',SQLERRM(SQLCODE));
330 FND_MSG_PUB.ADD;
331 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
332 END;
333
334 l_lot_recv_qty := 0;
335 END IF;
336
337 --IF receipt uom is different than primary uom then convert receipt qty to primary uom.
338 IF l_trx_opm_uom <> l_lot_recv_uom THEN
339
340 l_progress := '008';
341
342 l_trx_opm_qty := gmicuom.uom_conversion(p_opm_item_id,
343 nvl(p_lot_id,0),
344 p_trx_qty,
345 l_trx_opm_uom,
346 l_lot_recv_uom,
347 0);
348 IF (l_trx_opm_qty < 0) THEN
349 IF (l_trx_opm_qty = -1) THEN
350 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_PACKAGE_ERR');
351 ELSIF (l_trx_opm_qty = -3) THEN
352 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_CURUMTYPE_ERR');
353 ELSIF (l_trx_opm_qty = -4) THEN
354 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_NEWUMTYPE_ERR');
355 ELSIF (l_trx_opm_qty = -5) THEN
356 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_INVUOMTYPE_ERR2');
357 FND_MESSAGE.set_token('FROMUOM',l_trx_opm_uom);
358 FND_MESSAGE.set_token('TOUOM',l_lot_recv_uom);
359 ELSIF (l_trx_opm_qty = -6) THEN
360 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_INVUMTYPE_ERR');
361 ELSIF (l_trx_opm_qty = -7) THEN
362 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_CURFACTOR_ERR');
363 ELSIF (l_trx_opm_qty = -10) THEN
364 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_INVUOMTYPE_ERR2');
365 FND_MESSAGE.set_token('FROMUOM',l_trx_opm_uom);
366 FND_MESSAGE.set_token('TOUOM',l_lot_recv_uom);
367 ELSIF (l_trx_opm_qty = -11) THEN
368 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_NOITEMID_ERR');
369 ELSIF (l_trx_opm_qty < -11) THEN
370 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_FATAL_ERR');
371 END IF;
372 FND_MSG_PUB.ADD;
373 RAISE FND_API.G_EXC_ERROR;
374 END IF;
375
376 ELSE
377 l_trx_opm_qty := p_trx_qty;
378
379 l_progress := '009';
380 END IF;
381
382 --IF RMA uom is different than primary uom then convert RMA qty to primary uom.
383 IF l_rma_lot_opm_uom <> l_lot_recv_uom THEN
384
385 l_progress := '010';
386
387 l_rma_lot_opm_qty := gmicuom.uom_conversion(p_opm_item_id,
388 nvl(p_lot_id,0),
389 l_rma_lot_qty,
390 l_rma_lot_opm_uom,
391 l_lot_recv_uom,
392 0);
393 IF (l_rma_lot_opm_qty < 0) THEN
394 IF (l_rma_lot_opm_qty = -1) THEN
395 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_PACKAGE_ERR');
396 ELSIF (l_rma_lot_opm_qty = -3) THEN
397 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_CURUMTYPE_ERR');
398 ELSIF (l_rma_lot_opm_qty = -4) THEN
399 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_NEWUMTYPE_ERR');
400 ELSIF (l_rma_lot_opm_qty = -5) THEN
401 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_INVUOMTYPE_ERR2');
402 FND_MESSAGE.set_token('FROMUOM',l_rma_lot_opm_uom);
403 FND_MESSAGE.set_token('TOUOM',l_lot_recv_uom);
404 ELSIF (l_rma_lot_opm_qty = -6) THEN
405 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_INVUMTYPE_ERR');
406 ELSIF (l_rma_lot_opm_qty = -7) THEN
407 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_CURFACTOR_ERR');
408 ELSIF (l_rma_lot_opm_qty = -10) THEN
409 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_INVUOMTYPE_ERR2');
410 FND_MESSAGE.set_token('FROMUOM',l_rma_lot_opm_uom);
411 FND_MESSAGE.set_token('TOUOM',l_lot_recv_uom);
412 ELSIF (l_rma_lot_opm_qty = -11) THEN
413 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_NOITEMID_ERR');
414 ELSIF (l_rma_lot_opm_qty < -11) THEN
415 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_FATAL_ERR');
416 END IF;
417 FND_MSG_PUB.ADD;
418 RAISE FND_API.G_EXC_ERROR;
419 END IF;
420 ELSE
421 l_rma_lot_opm_qty := l_rma_lot_qty;
422
423 l_progress := '011';
424 END IF;
425
426 --If receipt quantity is greater than the total rma lot quantity minus the received qty for that lot then
427 --pass N no the allowe flag along with the remaining qty for the lot.
428 IF l_trx_opm_qty > (l_rma_lot_opm_qty - nvl(l_lot_recv_qty,0)) THEN
429
430 X_allowed := 'N';
431
432 IF l_trx_opm_uom <> l_lot_recv_uom THEN
433
434 l_progress := '012';
435
436 X_allowed_quantity := gmicuom.uom_conversion(p_opm_item_id,
437 nvl(p_lot_id,0),
438 (l_rma_lot_opm_qty - nvl(l_lot_recv_qty,0)),
439 l_lot_recv_uom,
440 l_trx_opm_uom,
441 0);
442 IF (X_allowed_quantity < 0) THEN
443 IF (X_allowed_quantity = -1) THEN
444 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_PACKAGE_ERR');
445 ELSIF (X_allowed_quantity = -3) THEN
446 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_CURUMTYPE_ERR');
447 ELSIF (X_allowed_quantity = -4) THEN
448 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_NEWUMTYPE_ERR');
449 ELSIF (X_allowed_quantity = -5) THEN
450 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_INVUOMTYPE_ERR2');
451 FND_MESSAGE.set_token('FROMUOM',l_lot_recv_uom);
452 FND_MESSAGE.set_token('TOUOM',l_trx_opm_uom);
453 ELSIF (X_allowed_quantity = -6) THEN
454 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_INVUMTYPE_ERR');
455 ELSIF (X_allowed_quantity = -7) THEN
456 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_CURFACTOR_ERR');
457 ELSIF (X_allowed_quantity = -10) THEN
458 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_INVUOMTYPE_ERR2');
459 FND_MESSAGE.set_token('FROMUOM',l_lot_recv_uom);
460 FND_MESSAGE.set_token('TOUOM',l_trx_opm_uom);
461 ELSIF (X_allowed_quantity = -11) THEN
462 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_NOITEMID_ERR');
463 ELSIF (X_allowed_quantity < -11) THEN
464 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_FATAL_ERR');
465 END IF;
466 FND_MSG_PUB.ADD;
467 RAISE FND_API.G_EXC_ERROR;
468 END IF;
469 ELSE
470 X_allowed_quantity := (l_rma_lot_opm_qty - nvl(l_lot_recv_qty,0));
471
472 l_progress := '013';
473 END IF;
474 ELSE
475 X_allowed := 'Y';
476 END IF;
477
478 --Lot ID NULL means that its a new LOT and we need to check only the RMA Lot quantity
479 --Since its a new Lot it was never received before.
480 ELSE
481 --set the receipt quantity into local variable
482 l_trx_opm_qty := p_trx_qty;
483
484 --Convert the rma lot quantity into receiving uom to compare
485 --if incase the rma and receiving uoms are different.
486 IF l_trx_opm_uom <> l_rma_lot_opm_uom THEN
487 l_rma_lot_opm_qty := gmicuom.uom_conversion(p_opm_item_id,
488 0,
489 l_rma_lot_qty,
490 l_rma_lot_opm_uom,
491 l_trx_opm_uom,
492 0);
493 IF (l_rma_lot_opm_qty < 0) THEN
494 IF (l_rma_lot_opm_qty = -1) THEN
495 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_PACKAGE_ERR');
496 ELSIF (l_rma_lot_opm_qty = -3) THEN
497 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_CURUMTYPE_ERR');
498 ELSIF (l_rma_lot_opm_qty = -4) THEN
499 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_NEWUMTYPE_ERR');
500 ELSIF (l_rma_lot_opm_qty = -5) THEN
501 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_INVUOMTYPE_ERR2');
502 FND_MESSAGE.set_token('FROMUOM',l_rma_lot_opm_uom);
503 FND_MESSAGE.set_token('TOUOM',l_trx_opm_uom);
504 ELSIF (l_rma_lot_opm_qty = -6) THEN
505 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_INVUMTYPE_ERR');
506 ELSIF (l_rma_lot_opm_qty = -7) THEN
507 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_CURFACTOR_ERR');
508 ELSIF (l_rma_lot_opm_qty = -10) THEN
509 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_INVUOMTYPE_ERR2');
510 FND_MESSAGE.set_token('FROMUOM',l_rma_lot_opm_uom);
511 FND_MESSAGE.set_token('TOUOM',l_trx_opm_uom);
512 ELSIF (l_rma_lot_opm_qty = -11) THEN
513 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_NOITEMID_ERR');
514 ELSIF (l_rma_lot_opm_qty < -11) THEN
515 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_FATAL_ERR');
516 END IF;
517 FND_MSG_PUB.ADD;
518 RAISE FND_API.G_EXC_ERROR;
519 END IF;
520
521 l_progress := '014';
522 ELSE
523 l_rma_lot_opm_qty := l_rma_lot_qty;
524
525 l_progress := '015';
526 END IF;
527
528 --If receipt quantity is greater than the total rma lot quantity then
529 --pass N the allowed flag along with the qty for the lot in receiving UOM.
530 IF l_trx_opm_qty > l_rma_lot_opm_qty THEN
531 X_allowed := 'N';
532
533 X_allowed_quantity := l_rma_lot_opm_qty;
534
535 l_progress := '016';
536 ELSE
537 X_allowed := 'Y';
538
539 l_progress := '017';
540 END IF;
541 END IF;
542
543 EXCEPTION
544 WHEN FND_API.G_EXC_ERROR THEN
545 x_return_status := FND_API.G_RET_STS_ERROR;
546
547 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
548 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
549
550 WHEN OTHERS THEN
551 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
552 FND_MESSAGE.SET_NAME('GMI','GMI_SQL_ERROR');
553 FND_MESSAGE.SET_TOKEN('WHERE','GML_RCV_DB_COMMON.VALIDATE_RMA_LOT_QUANTITIES'||'-'||l_progress);
554 FND_MESSAGE.SET_TOKEN('SQL_CODE',SQLCODE);
555 FND_MESSAGE.SET_TOKEN('SQL_ERRM',SQLERRM(SQLCODE));
556 FND_MSG_PUB.ADD;
557 /* Bug 4502018 */
558 -- IF (g_fnd_debug = 'Y') THEN
559 if( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
560 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED,
561 g_module_prefix || l_api_name || '.' || l_progress, SQLERRM(SQLCODE));
562 END IF;
563
564 END VALIDATE_RMA_LOT_QUANTITIES;
565
566 /*##########################################################################
567 #
568 # PROCEDURE
569 # validate_io_lot_quantities
570 #
571 # DESCRIPTION
572 #
573 # For Internal Orders for process organizations
574 # In the Lot Serial form of Receipts or Receiving transactions form where we
575 # transafer to inventory (Deliver Transaction) we shall validate the lot
576 # quantity by the following formula.
577 # Available quantity <= Shipped Quantity- Quantity already received
578 # All the above quantities are for that combination of lot and sublot only.
579 #
580 # If the validation fails then an error message is popped up saying
581 # " The quantity being delivered cannot be greater than the available
582 # quantity "and quantity field is focussed again
583 #
584 # MODIFICATION HISTORY
585 # 15-JUN-2004 Punit Kumar Created
586 #
587 ## #######################################################################*/
588
589 Procedure VALIDATE_IO_LOT_QUANTITIES (p_api_version IN NUMBER ,
590 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
591 p_opm_item_id IN NUMBER ,
592 p_lot_id IN NUMBER ,
593 p_trx_qty IN NUMBER ,
594 p_trx_uom IN VARCHAR2 ,
595 p_order_num IN NUMBER ,
596 p_req_header_id IN NUMBER ,
597 p_req_line_id IN NUMBER ,
598 p_shipment_header_id IN NUMBER ,
599 p_shipment_line_id IN NUMBER ,
600 p_req_distribution_id IN NUMBER ,
601 p_called_from IN VARCHAR2 DEFAULT 'FORM' ,
602 X_allowed OUT NOCOPY VARCHAR2 ,
603 X_allowed_quantity OUT NOCOPY NUMBER ,
604 x_return_status OUT NOCOPY VARCHAR2)
605 IS
606
607 l_trx_opm_uom_code SY_UOMS_MST.UM_CODE%TYPE;
608 l_lot_shipped_uom VARCHAR2(25);
609 l_lot_delivered_uom VARCHAR2(25);
610 l_lot_shipped_qty NUMBER;
611 l_lot_delivered_qty NUMBER;
612 l_trx_qty NUMBER;
613
614 l_api_name CONSTANT VARCHAR2(30) := 'Validate_IO_Lot_Quantities' ;
615 l_api_version CONSTANT NUMBER := 1.0 ;
616
617 /*
618 OM may ship duplicate lots. We need to sum up the quantity
619 since we do not allow duplicate lots on the lot serial form while receiving. So person has to
620 receive in one lot..sum up the quantities.
621 */
622
623 /* Selecting the shipped quantity for that Lot and Sublot */
624
625 Cursor Cr_Io_lot_shipped_qty IS
626 SELECT ic.trans_um ,sum(ic.trans_qty)*-1
627 FROM wsh_delivery_details wdd, wsh_delivery_assignments assign,ic_tran_pnd ic,
628 oe_order_lines_all ol
629 WHERE ic.lot_id = p_lot_id
630 and assign.delivery_id = p_order_num
631 and wdd.delivery_detail_id =assign.delivery_detail_id
632 and wdd.delivery_detail_id = ic.line_detail_id
633 and wdd.source_header_id = ol.header_id
634 and wdd.source_line_id = ol.line_id
635 and ol.source_document_id =p_req_header_id
636 and ol.source_document_line_id = p_req_line_id
637 and ic.line_id = ol.line_id
638 and ic.doc_type ='OMSO'
639 and ic.delete_mark = 0
640 and ic.completed_ind = 1
641 group by ic.trans_um;
642
643
644 /* Selecting the quantity received for that Lot and Sublot*/
645
646 Cursor Cr_Io_delivered_quantity IS
647 SELECT ic.trans_um,sum(ic.trans_qty)
648 FROM ic_tran_pnd ic ,rcv_transactions rcv
649 WHERE ic.lot_id = p_lot_id and ic.doc_type ='PORC'
650 and ic.line_id =rcv.transaction_id
651 and rcv.requisition_line_id = p_req_line_id
652 and rcv.shipment_header_id = p_shipment_header_id
653 and rcv.transaction_type in ('DELIVER')
654 and rcv.shipment_line_id = p_shipment_line_id
655 and (rcv.req_distribution_id = p_req_distribution_id or rcv.req_distribution_id is null)
656 group by ic.trans_um ;
657
658 BEGIN
659 --initialize x_allowed =
660 X_allowed := 'Y';
661
662 -- Standard call to check for call compatibility.
663 IF NOT FND_API.Compatible_API_Call ( l_api_version,
664 p_api_version,
665 l_api_name ,
666 G_PKG_NAME
667 ) THEN
668 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
669 END IF;
670
671 ----If any of these parameters are NULL , nothing can be done so just
672 ----returns as if successful.
673 IF p_order_num IS NULL OR
674 p_req_header_id IS NULL OR
675 p_req_line_id IS NULL OR
676 p_shipment_header_id IS NULL OR
677 p_shipment_line_id IS NULL OR
678 p_lot_id IS NULL OR
679 p_trx_qty IS NULL OR
680 p_trx_uom IS NULL THEN
681
682 X_allowed := 'Y';
683 RETURN;
684 END IF;
685
686 --Get the Trx OPM UOM CODE.
687 BEGIN
688 l_trx_opm_uom_code := PO_GML_DB_COMMON.GET_OPM_UOM_CODE(p_trx_uom);
689
690 EXCEPTION
691 WHEN OTHERS THEN
692 FND_MSG_PUB.ADD;
693 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
694 END;
695
696 ---Get total shipped quantity for that IO.
697 Open Cr_Io_lot_shipped_qty;
698 Fetch Cr_Io_lot_shipped_qty Into l_lot_shipped_uom, l_lot_shipped_qty;
699 Close Cr_Io_lot_shipped_qty;
700
701 --Get total delivered quantity of the Lot.
702 Open Cr_Io_delivered_quantity;
703 Fetch Cr_Io_delivered_quantity Into l_lot_delivered_uom, l_lot_delivered_qty;
704 Close Cr_Io_delivered_quantity;
705
706 --If this lot was never received then the delivered uom will be null
707 IF l_lot_delivered_uom IS NULL THEN
708 l_lot_delivered_uom := l_lot_shipped_uom;
709 l_lot_delivered_qty := 0;
710 END IF;
711
712 --get lot receipt qty into a local varialbe
713 l_trx_qty := p_trx_qty;
714
715 /* If shipped uom is different than transaction uom (Receipt uom) then convert transaction
716 quantity to shipped uom */
717 IF l_trx_opm_uom_code <> l_lot_shipped_uom THEN
718 BEGIN
719 l_trx_qty:= gmicuom.uom_conversion( p_opm_item_id,
720 nvl(p_lot_id,0),
721 l_trx_qty,
722 l_trx_opm_uom_code,
723 l_lot_shipped_uom,
724 0);
725 END;
726 END IF;
727
728 /* If transaction quantity is greater than the total shipped lot quantity
729 minus the received qty for that lot then
730 pass N to the allowed flag along with the allowed qty for that lot. */
731
732 IF l_trx_qty > (l_lot_shipped_qty - nvl(l_lot_delivered_qty,0)) THEN
733 X_allowed := 'N';
734 X_allowed_quantity := l_lot_shipped_qty - nvl(l_lot_delivered_qty,0);
735
736 IF l_trx_opm_uom_code <> l_lot_shipped_uom THEN
737 BEGIN
738 X_allowed_quantity := gmicuom.uom_conversion( p_opm_item_id,
739 nvl(p_lot_id,0),
740 X_allowed_quantity ,
741 l_lot_shipped_uom,
742 l_trx_opm_uom_code,
743 0);
744 X_allowed_quantity :=round(X_allowed_quantity ,5);
745 END;
746 END IF;
747 ELSE
748 X_allowed := 'Y';
749 X_allowed_quantity := l_lot_shipped_qty;
750 END IF;
751
752 END VALIDATE_IO_LOT_QUANTITIES;
753
754
755 END GML_RCV_DB_COMMON;
[Home] [Help]