1 PACKAGE BODY MTL_SERIAL_CHECK AS
2 /* $Header: INVSERLB.pls 120.1 2005/10/10 08:51:05 methomas noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'MTL_SERIAL_CHECK';
5
6
7 /*====================================================+
8 | debugging utility |
9 +-----------------------------------------------------*/
10 procedure mdebug(msg in varchar2)
11 is
12 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
13 begin
14 --dbms_output.put_line(msg);
15 null;
16 end;
17
18 /*=========================================================================
19 | Error numbers
20 105 INV_SER_INVALID_STATUS
21 106 INV_SER_STATUS_NA
22 107 INV_SER_ORG_INVALID
23 108 INV_SER_REV_INVALID
24 109 INV_SER_LOT_INVALID
25 110 INV_SER_SUB_INVALID
26 111 INV_SER_LOC_INVALID
27 113 INV_SER_UNIQ1
28 114 INV_SER_UNIACR
29 115 INV_INLTIS_SER1
30 116 INV_QTYBTWN_ISSREC
31 118 INV_SER_NOTEXIST
32 119 INV_QTYBTWN_PFX
33 120 INV_QTYBTWN_LGTH
34 121 INV_QTYBTWN_LAST
35 122 INV_QTYBTWN_NUM
36 123 INV_INLTIS_SNGET_MASK
37 124 INV_QTYBTWN_NO_SERIAL
38 ===========================================================================*/
39 /*==========================================================================+
40 | TITLE: qtybtwn (user exit QTYBTWN)
41 | PURPOSE: Takes two alphanumeric serial numbers and returns both the
42 | quantity of individual serial numbers which fall between them and
43 | the alpha prefix of the first serial number.
44 |
45 | PARAMETERS:
46 | P_FROM_SERIAL_NUMBER and P_TO_SERIAL_NUMBER specify the range of
47 | alphanumeric serial numbers from which QTYBTWN is to determine the
48 | quantity. P_QUANTITY is the field name to which the quantity of
49 | serial numbers is to bewritten. P_PREFIX is the field name to which
50 | the alpha prefix is to be written.
51 |
52 | RETURN: Returns RET_FAILURE if failure.
53 +==========================================================================*/
54
55 /*-------------------------------------------------------------------------+
56 | Every serial number is given a current_status which indicates where the
57 | unit is and for what transactions it is available. Supported statuses
58 | are:
59 | o 1 The unit is defined but has not been received into or issued out
60 | of stores.
61 | o 3 The unit has been received into stores.
62 | o 4 The unit has been issued out of stores.
63 | o 5 The unit has been issued out of stores and now resides in
64 | intransit.
65 | A serialized unit is available to be used for a particular transaction
66 | according to the following criteria:
67 | o 1 Available to be received into or issued out of stores in any
68 | transaction requiring serialized units.
69 | o 3 Available to be issued out of stores in any transaction requiring
70 | serialized units
71 | o 4 Available to be received into stores in any transaction requiring
72 | serialized units
73 | o 5 Available to be received into stores by an intransit receipt
74 | transaction.
75 | In addition, there are several types of serial control which determine
76 | under what conditions serialized units are required. Supported serial
77 | controls are:
78 | o 1 No serial number control.
79 | o 2 Predefined S/N - full control.
80 | o 3 Predefined S/N - inventory receipt.
81 | o 5 Dynamic entry at inventory receipt.
82 | o 6 Dynamic entry at sales order issue.
83 |
84 | The type of transaction determines which statuses are valid for the
85 | transaction, and which status will be assigned to the unit after the
86 | transaction. The supported transactions can be divided into four basic
87 | groups, with a further division between issues and receipts in each
88 | group. The four groups are SO/RMA, Standard, Subinventory Transfer, and
89 | Intransit. The following diagram shows the relationships between
90 | the type of transaction, serial control, and status.
91 |
92 | A transaction may accept one or more statuses, but it only assigns one
93 | status. A transaction may accept serial numbers which have not yet
94 | been defined (Dynamic entry).
95 |
96 | SO/RMA
97 | Issue/ Serial Available Assigned
98 | Receipt Control Status Status
99 | ------- --------- ------------ ------------
100 | issue 1
101 | issue 2 3 5 3 4
102 | issue 6 1 3 Dyn 4
103 | receipt 1
104 | receipt 2 3 1 4 3
105 | receipt 5 1 4 Dyn 3
106 | receipt 6 1 4 Dyn 1
107 |
108 | Standard
109 | Issue/ Serial Available Assigned
110 | Receipt Control Status Status
111 | ------- --------- ------------ ------------
112 | receipt 1 6
113 | receipt 2 3 1 4 3
114 | receipt 5 1 4 Dyn 3
115 | issue 1 6
116 | issue 2 3 5 3 4
117 |
118 | Subinventory Transfer
119 | Issue/ Serial Available Assigned
120 | Receipt Control Status Status
121 | ------- --------- ------------ ------------
122 | receipt 1 6
123 | receipt 2 3 5 3 3
124 | issue 1 6
125 | issue 2 3 5 3 3
126 |
127 | Intransit
128 | Issue/ Serial Available Assigned
129 | Receipt Control Status Status
130 | ------- --------- ------------ ------------
131 | receipt 1 6
132 | receipt 2 3 1 4 5 3
133 | receipt 5 1 4 5 Dyn 3
134 | issue 1 6
135 | issue 2 3 5 3 5
136 |
137 | The array sn_mask is declared below. It is a representation of the
138 | above diagram.
139 +-------------------------------------------------------------------------*/
140
141 PROCEDURE INV_QTYBETWN
142 ( p_api_version IN NUMBER,
143 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
144 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
145 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
146 x_return_status OUT NOCOPY VARCHAR2,
147 x_msg_count OUT NOCOPY NUMBER,
148 x_msg_data OUT NOCOPY VARCHAR2,
149 x_errorcode OUT NOCOPY NUMBER,
150
151 P_FROM_SERIAL_NUMBER IN VARCHAR2,
152 P_TO_SERIAL_NUMBER IN VARCHAR2,
153 X_QUANTITY OUT NOCOPY NUMBER,
154 X_PREFIX OUT NOCOPY VARCHAR2,
155 P_ITEM_ID IN NUMBER,
156 P_ORGANIZATION_ID IN NUMBER,
157 P_SERIAL_NUMBER_TYPE IN NUMBER,
158 P_TRANSACTION_ACTION_ID IN NUMBER,
159 P_TRANSACTION_SOURCE_TYPE_ID IN NUMBER,
160 P_SERIAL_CONTROL IN NUMBER,
161 P_REVISION IN VARCHAR2,
162 P_LOT_NUMBER IN VARCHAR2,
163 P_SUBINVENTORY IN VARCHAR2,
164 P_LOCATOR_ID IN NUMBER,
165 P_RECEIPT_ISSUE_FLAG IN VARCHAR2,
166 p_simulate IN VARCHAR2 DEFAULT FND_API.G_FALSE
167 ) IS
168
169 -- Start OF comments
170 -- API name : INV_QTYBETWN
171 -- TYPE : Private
172 -- Pre-reqs : None
173 -- FUNCTION :
174 -- as explained above
175 -- Parameters:
176 -- IN :
177 -- p_api_version IN NUMBER (required)
178 -- API Version of this procedure
179 --
180 -- p_init_msg_list IN VARCHAR2 (optional)
181 -- DEFAULT = FND_API.G_FALSE,
182 --
183 -- p_commit IN VARCHAR2 (optional)
184 -- DEFAULT = FND_API.G_FALSE
185 --
186 -- p_validation_level IN NUMBER (optional)
187 -- DEFAULT = FND_API.G_VALID_LEVEL_FULL,
188 --
189 -- P_FROM_SERIAL_NUMBER VARCHAR2
190 -- P_TO_SERIAL_NUMBER VARCHAR2
191 -- X_QUANTITY VARCHAR2
192 -- X_PREFIX VARCHAR2
193 -- P_ITEM_ID NUMBER
194 -- P_ORGANIZATION_ID NUMBER
195 -- P_SERIAL_NUMBER_TYPE NUMBER
196 -- P_TRANSACTION_ACTION_ID NUMBER
197 -- P_TRANSACTION_SOURCE_TYPE_ID NUMBER
198 -- P_SERIAL_CONTROL NUMBER
199 -- P_REVISION VARCHAR2
200 -- P_LOT_NUMBER VARCHAR2
201 -- P_SUBINVENTORY VARCHAR2
202 -- P_LOCATOR_ID NUMBER
203 -- P_RECEIPT_ISSUE_FLAG VARCHAR2
204 -- P_VALIDATE VARCHAR2
205 -- p_simulate - flag that signals whether to do any insertions/updates
206 -- if equals to FND_API.G_TRUE then no inserts/updates will be done
207 -- and procedure will run in simulation mode. by default does necessary
208 -- inserts/updates
209 --
210 -- OUT :
211 -- X_Return_Status OUT NUMBER
212 -- Result of all the operations
213 --
214 -- X_Msg_Count OUT NUMBER,
215 --
216 -- X_Msg_Data OUT VARCHAR2,
217 --
218 -- X_ErrorCode OUT NUMBER
219 -- X_QUANTITY OUT VARCHAR2
220 -- X_PREFIX OUT VARCHAR2
221
222 -- Version: Current Version 0.9
223 -- Changed : Nothing
224 -- No Previous Version 0.0
225 -- Initial version 0.9
226 -- Notes : Note text
227 -- END OF comments
228
229 l_api_version CONSTANT NUMBER := 0.9;
230 l_api_name CONSTANT VARCHAR2(30) := 'INV_QTYBETWN';
231
232 -- Initialization of local variables
233
234 l_number_part NUMBER := 0;
235 l_counter NUMBER := 0;
236 l_serial_number VARCHAR2(30);
237 l_to_status NUMBER := 0;
238 l_dynamic_ok NUMBER := 0 ;
239 l_mask VARCHAR2(14);
240 l_SerExists NUMBER:= 1;
241 l_user_id NUMBER;
242 l_from_number VARCHAR2(30);
243 l_to_number VARCHAR2(30);
244 l_length NUMBER;
245 l_padded_length NUMBER;
246 l_obj_seq_num NUMBER;
247
248 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
249 BEGIN
250 --
251 -- Standard start of API savepoint
252 SAVEPOINT InvQtyBetwn;
253 -- Standard Call to check for call compatibility
254 IF NOT FND_API.Compatible_API_Call(l_api_version
255 , p_api_version
256 , l_api_name
257 , G_PKG_NAME) THEN
258 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
259 END IF;
260 --
261 -- Initialize message list if p_init_msg_list is set to true
262 IF FND_API.to_Boolean(p_init_msg_list) THEN
263 FND_MSG_PUB.initialize;
264 END IF;
265 --
266 -- Initialisize API return status to access
267 x_return_status := FND_API.G_RET_STS_SUCCESS;
268 x_errorcode := 0;
269
270 --
271 -- API Body
272 --
273
274
275 -- Check the Input variable receipt_issue is either Issue or Receipt
276 -- Otherwise return error message
277 IF p_receipt_issue_flag NOT IN ('I','R') then
278 FND_MESSAGE.set_name('INV','INV_QTYBTWN_ISSREC');
279 FND_MSG_PUB.Add;
280 x_errorcode := 116;
281 RAISE FND_API.G_EXC_ERROR;
282 END IF;
283
284 -- Calling Serial Info Routine
285 --
286
287 IF NOT INV_SERIAL_INFO
288 (p_from_serial_number => p_from_serial_number ,
289 p_to_serial_number => p_to_serial_number ,
290 x_prefix => x_prefix,
291 x_quantity => x_quantity,
292 x_from_number => l_from_number,
293 x_to_number => l_to_number,
294 x_errorcode => x_errorcode)
295 THEN
296 -- no need to process error here since it was already added to msg
297 -- list inside inv_serial_info subroutine
298 RAISE FND_API.G_EXC_ERROR;
299 END IF;
300
301 IF (l_debug = 1) THEN
302 mdebug('Quantity ' || to_char(x_quantity));
303 mdebug('Prefix ' || x_prefix );
304 mdebug('FROM QTY ' || l_from_number);
305 mdebug('TO QTY ' || l_to_number);
306 END IF;
307
308 IF (p_validation_level = FND_API.G_VALID_LEVEL_FULL) THEN
309
310 l_mask := '00000000000000';
311 --
312 IF (l_debug = 1) THEN
313 mdebug('Mask before '||l_mask);
314 END IF;
315 --
316 IF NOT SNGetMask(P_transaction_action_id,
317 P_transaction_source_type_id,
318 P_serial_control,
319 l_to_status,
320 l_dynamic_ok,
321 P_receipt_issue_flag,
322 l_mask,
323 x_errorcode) then
324
325 IF (l_debug = 1) THEN
326 mdebug('Mask Proc - Error');
327 END IF;
328 RAISE FND_API.G_EXC_ERROR ;
329 END IF;
330
331 IF (l_debug = 1) THEN
332 mdebug('Mask ' || l_mask);
333 mdebug('x_quantity ' || x_quantity);
334 mdebug('l_counter ' || l_counter);
335 END IF;
336
337 /*---------------------------------------------------------------+
338 | Generate the serial numbers starting from from_serial_number
339 | upto To serial number. Validate the serial numbers as you generate.
340 +---------------------------------------------------------------*/
341
342
343 l_number_part := TO_NUMBER(l_FROM_NUMBER);
344 l_counter := 1;
345 -- Get the length of the serial number
346 l_length := length(p_from_serial_number);
347
348 WHILE (l_counter <= x_quantity) LOOP
349 -- The padded length will be the length of the serial number minus
350 -- the length of the number part
351 -- Fix by etam
352 l_padded_length := l_length - nvl(length(l_number_part),0);
353 l_serial_number := RPAD(nvl(x_Prefix, '0'), l_padded_length, '0') ||
354 l_number_part;
355
356 IF (l_debug = 1) THEN
357 mdebug('Calling SNValidate procedure ');
358 mdebug('Item: =======> '|| to_char(p_item_id));
359 mdebug('Org: ========> '|| to_char(p_organization_id));
360 mdebug('Subinv: =====> '|| P_subinventory);
361 mdebug('Txn Src Id: => '|| to_char(P_transaction_source_type_id));
362 mdebug('Txn Act Id: => '|| to_char(P_transaction_action_id));
363 mdebug('Serial: =====> '|| l_serial_number);
364 mdebug('Loc: ========> '|| to_char(P_locator_id));
365 mdebug('Lot: ========> '|| P_lot_number);
366 mdebug('Rev: ========> '|| P_revision);
367 mdebug('Mask: =======> '|| l_mask);
368 mdebug('Dynamic: ====> '|| to_char(l_dynamic_ok));
369 END IF;
370
371 SNValidate
372 (p_api_version => 0.9,
373 x_return_status => x_return_status,
374 x_errorcode => x_errorcode,
375 x_msg_count => x_msg_count,
376 x_msg_data => x_msg_data,
377 p_item_id => P_item_id,
378 p_org_id => P_organization_id,
379 p_subinventory => P_subinventory,
380 p_txn_src_type_id => P_transaction_source_type_id,
381 p_txn_action_id => P_transaction_action_id,
382 p_serial_number => l_serial_number,
383 p_locator_id => p_locator_id,
384 p_lot_number => P_lot_number,
385 p_revision => P_revision,
386 x_SerExists => l_SerExists,
387 p_mask => l_mask,
388 p_dynamic_ok => l_dynamic_ok);
389
390
394 END IF;
391 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
392 IF (l_debug = 1) THEN
393 mdebug('Calling SNValidate : Error!');
395 RAISE FND_API.G_EXC_ERROR ;
396 END IF;
397
398 IF (l_debug = 1) THEN
399 mdebug('After Calling SNValidate procedure ');
400 END IF;
401
402 --
403 IF l_SerExists = 0 then -- serial num does not exist
404 IF (l_debug = 1) THEN
405 mdebug('Calling SNUniqueCheck Proc ');
406 END IF;
407
408 SNUniqueCheck(
409 p_api_version => 0.9,
410 x_return_status => x_return_status,
411 x_errorcode => x_errorcode,
412 x_msg_count => x_msg_count,
413 x_msg_data => x_msg_data,
414 p_org_id => P_Organization_Id,
415 p_serial_number_type => P_Serial_number_type,
416 p_serial_number => l_Serial_number);
417
418 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
419 RAISE FND_API.G_EXC_ERROR ;
420 END IF;
421
422
423 IF (l_debug = 1) THEN
424 mdebug('After Calling SNUniqueCheck Proc ');
425 END IF;
426 /*
427 * Get the next sequence number for this 'object' and insert
428 * into GEN_OBJECT_ID field : Object Genealogy
429 */
430
431 SELECT MTL_GEN_OBJECT_ID_S.NEXTVAL
432 INTO l_obj_seq_num FROM DUAL;
433
434
435 IF NOT FND_API.to_Boolean(p_simulate) THEN
436 l_user_id := FND_GLOBAL.USER_ID ;
437 BEGIN
438 IF (l_debug = 1) THEN
439 mdebug('Inserting row with SL NO '||l_serial_number);
440 END IF;
441 INSERT INTO MTL_SERIAL_NUMBERS
442 (INVENTORY_ITEM_ID,
443 SERIAL_NUMBER,
444 LAST_UPDATE_DATE,
445 LAST_UPDATED_BY,
446 INITIALIZATION_DATE,
447 CREATION_DATE,
448 CREATED_BY,
449 LAST_UPDATE_LOGIN,
450 CURRENT_STATUS,
451 CURRENT_ORGANIZATION_ID,
452 GEN_OBJECT_ID)
453 VALUES
454 (P_Item_id, l_serial_number, sysdate,
455 l_user_id, sysdate, sysdate,
456 l_user_id, -1, 6, P_organization_id,l_obj_seq_num);
457
458 IF SQL%FOUND THEN
459 IF (l_debug = 1) THEN
460 mdebug('Inserted row with SL NO '||l_serial_number);
461 END IF;
462 ELSE
463 IF (l_debug = 1) THEN
464 mdebug('Inserted failure');
465 END IF;
466 END IF;
467
468 EXCEPTION
469 WHEN OTHERS THEN
470 IF (l_debug = 1) THEN
471 mdebug('Exception : Inserted failure:');
472 END IF;
473 NULL;
474 END;
475 END IF; -- if not in simulation mode
476
477 END IF; -- if serial number does not exist
478 /*---------------------------------------------------------+
479 | Get next serial number
480 +---------------------------------------------------------*/
481 l_number_part := l_number_part + 1;
482 l_counter := l_counter + 1;
483 END LOOP;
484
485 END IF;
486
487 --
488 -- END of API Body
489 --
490
491 -- Standard check of p_commit
492 IF FND_API.to_Boolean(p_commit) THEN
493 COMMIT;
494 END IF;
495
496 -- Standard call to get message count and if count is 1, get message info
497 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
498 , p_data => x_msg_data);
499
500 EXCEPTION
501 WHEN FND_API.G_EXC_ERROR THEN
502 --
503 ROLLBACK TO InvQtyBetwn;
504 --
505 x_return_status := FND_API.G_RET_STS_ERROR;
506 --
507 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
508 , p_data => x_msg_data);
509 --
510 IF (l_debug = 1) THEN
511 mdebug('Exception :FND_API.G_EXC_ERROR');
512 END IF;
513
514 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
515 IF (l_debug = 1) THEN
516 mdebug('InvQtyBetwn: Unexpected error');
517 END IF;
518 --
519 ROLLBACK TO InvQtyBetwn;
520 --
521 IF (l_debug = 1) THEN
522 mdebug('InvQtyBetwn: Unexpected error-2');
523 END IF;
524 x_errorcode := -1;
525 IF (l_debug = 1) THEN
526 mdebug('InvQtyBetwn: Unexpected error-3');
527 END IF;
528 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
529 IF (l_debug = 1) THEN
530 mdebug('InvQtyBetwn: x_return_status='||x_return_status);
531 END IF;
532
533 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
534 , p_data => x_msg_data);
535 --
536 IF (l_debug = 1) THEN
537 mdebug('Exception :FND_API.G_EXC_UNEXPECTED_ERROR');
538 END IF;
539
540 WHEN OTHERS THEN
541 --
542 ROLLBACK TO InvQtyBetwn;
543 --
544 x_errorcode := -1;
545 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
546 --
547 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
548 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
552 , p_data => x_msg_data);
549 END IF;
550 --
551 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
553 IF (l_debug = 1) THEN
554 mdebug('Exception :');
555 END IF;
556
557 END INV_QTYBETWN ;
558 /*---------------------------------------------------------------------------
559 ----------------------------End of INV_QTYBETWN -----------------------------
560 ----------------------------------------------------------------------------*/
561
562 /*==========================================================================+
563 | TITLE: FUNCTION : SNUniqueCheck
564 | PURPOSE:
565 | Determine whether or not a given serial can be created without
566 | violating the organization uniqueness criteria.
567 |
568 | PARAMETERS:
569 | org_id is the organization_id, serial_number_type is the value from
570 | MTL_PARAMETERS, ser_number is the serial number in question, message
571 | is expected to point to a text[241].
572 |
573 | RETURN:
574 | Returns TRUE on success, FALSE on error.
575 |
576 | ERROR CONDITIONS:
577 |
578 | Violation of uniqueness criteria.
579 +==========================================================================*/
580 /*------------------------------------------------------------+
581 | Dynamically create a new serial number record. We must
582 | follow the serial number uniqueness criteria specified
583 | in the inventory parameters of this organization. The
584 | possible criteria are:
585 |
586 | o 1 Unique serial numbers within inventory items.
587 | No duplicate serial numbers for any particular
588 | inventory item across all organizations.
589 |
590 | A serial number may be assigned to at most one
591 | unit of each item across all organizations. This
592 | translates into at most one record in
593 | MTL_SERIAL_NUMBERS for each combination of
594 | SERIAL_NUMBER and INVENTORY_ITEM_ID.
595 |
596 | o 2 Unique serial numbers within organization.
597 | No duplicate serial numbers within any particular
598 | organization.
599 |
600 | A serial number may be assigned to at most one unit
601 | of one item in each organization, with the caveat
602 | that the same serial number may not be assigned to
603 | the same item in two different organizations. This
604 | translates into at most one record in
605 | MTL_SERIAL_NUMBERS for each combination of
606 | SERIAL_NUMBER and INVENTORY_ITEM_ID with the
607 | overriding condition that there be at most one
608 | record for any given combination of SERIAL_NUMBER
609 | and ORGANIZATION_ID.
610 |
611 | o 3 Unique serial numbers across organizations.
612 | No duplicate serial numbers in the entire system.
613 |
614 | A serial number may be assigned to at most one unit
615 | of one item across all organizations. This
616 | translates into at most one record in
617 | MTL_SERIAL_NUMBERS for each value of SERIAL_NUMBER.
618 +-------------------------------------------------------------*/
619 PROCEDURE SNUniqueCheck
620 (
621 p_api_version IN NUMBER,
622 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
623 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
624 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
625 x_return_status OUT NOCOPY VARCHAR2,
626 x_msg_count OUT NOCOPY NUMBER,
627 x_msg_data OUT NOCOPY VARCHAR2,
628 x_errorcode OUT NOCOPY NUMBER,
629
630 p_org_id IN NUMBER,
631 p_serial_number_type IN NUMBER ,
632 p_serial_number IN VARCHAR2 )
633 IS
634 l_api_version constant number := 0.9;
635 l_api_name constant varchar2(30) := 'SNUniqueCheck';
636
637 L_nothing VARCHAR2(10) ;
638 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
639 BEGIN
640 -- Standard start of API savepoint
641 SAVEPOINT SNUniqueCheck;
642 -- Standard Call to check for call compatibility
643 IF NOT FND_API.Compatible_API_Call(l_api_version
644 , p_api_version
645 , l_api_name
646 , G_PKG_NAME) THEN
647 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
648 END IF;
649 --
650 -- Initialize message list if p_init_msg_list is set to true
651 IF FND_API.to_Boolean(p_init_msg_list) THEN
652 FND_MSG_PUB.initialize;
653 END IF;
654 --
655 -- Initialisize API return status to access
656 x_return_status := FND_API.G_RET_STS_SUCCESS;
657 x_errorcode := 0;
658
659 --
660 -- API Body
661 --
662
663 IF P_serial_number_type = 2 then
664 BEGIN
665 SELECT 'X'
666 INTO L_nothing
667 FROM MTL_SERIAL_NUMBERS
668 WHERE SERIAL_NUMBER = P_serial_number
669 AND CURRENT_ORGANIZATION_ID + 0 = P_org_id;
670 --
671 if L_nothing is not NULL then
672 FND_MESSAGE.set_name('INV','INV_SER_UNIQ1');
673 FND_MESSAGE.SET_TOKEN('TOKEN1',P_serial_number);
674 FND_MSG_PUB.Add;
675 x_errorcode := 113;
676 RAISE FND_API.G_EXC_ERROR;
677 end if;
678 EXCEPTION
682 FND_MSG_PUB.Add;
679 WHEN TOO_MANY_ROWS THEN
680 FND_MESSAGE.set_name('INV','INV_SER_UNIQ1');
681 FND_MESSAGE.SET_TOKEN('TOKEN1',P_serial_number);
683 x_errorcode := 113;
684 RAISE FND_API.G_EXC_ERROR;
685
686 WHEN NO_DATA_FOUND then
687 null;
688 END;
689
690 BEGIN
691 SELECT 'x'
692 INTO L_nothing
693 FROM MTL_SERIAL_NUMBERS S,
694 MTL_PARAMETERS P
695 WHERE S.CURRENT_ORGANIZATION_ID = P.ORGANIZATION_ID
696 AND S.SERIAL_NUMBER = P_serial_number
697 AND P.SERIAL_NUMBER_TYPE = 3;
698
699 if L_nothing is not NULL then
700 FND_MESSAGE.set_name('INV','INV_SER_UNIACR');
701 FND_MESSAGE.SET_TOKEN('TOKEN1',P_serial_number);
702 FND_MSG_PUB.Add;
703 x_errorcode := 114;
704 raise FND_API.G_EXC_ERROR;
705 end if;
706
707 EXCEPTION
708 WHEN TOO_MANY_ROWS THEN
709 FND_MESSAGE.set_name('INV','INV_SER_UNIACR');
710 FND_MESSAGE.SET_TOKEN('TOKEN1',P_serial_number);
711 FND_MSG_PUB.Add;
712 x_errorcode := 114;
713 RAISE FND_API.G_EXC_ERROR;
714 WHEN NO_DATA_FOUND THEN
715 null;
716 END;
717
718 ELSIF P_serial_number_type = 3 then
719 BEGIN
720 SELECT 'x'
721 INTO L_nothing
722 FROM MTL_SERIAL_NUMBERS
723 WHERE SERIAL_NUMBER = P_serial_number;
724 if L_nothing is not NULL then
725 FND_MESSAGE.set_name('INV','INV_INLTIS_SER1');
726 FND_MESSAGE.SET_TOKEN('TOKEN1',P_serial_number);
727 FND_MSG_PUB.Add;
728 x_errorcode := 115;
729 RAISE FND_API.G_EXC_ERROR;
730 end if;
731 EXCEPTION
732 WHEN TOO_MANY_ROWS THEN
733 FND_MESSAGE.set_name('INV','INV_INLTIS_SER1');
734 FND_MESSAGE.SET_TOKEN('TOKEN1',P_serial_number);
735 FND_MSG_PUB.Add;
736 x_errorcode := 115;
737 RAISE FND_API.G_EXC_ERROR;
738 WHEN NO_DATA_FOUND THEN
739 null;
740 END;
741 END IF;
742
743 --
744 -- END of API body
745 --
746
747 -- Standard check of p_commit
748 IF FND_API.to_Boolean(p_commit) THEN
749 COMMIT;
750 END IF;
751
752 -- Standard call to get message count and if count is 1, get message info
753 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
754 , p_data => x_msg_data);
755
756
757 EXCEPTION
758 WHEN FND_API.G_EXC_ERROR THEN
759 --
760 ROLLBACK TO SNUniqueCheck;
761 --
762 x_return_status := FND_API.G_RET_STS_ERROR;
763 --
764 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
765
766 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
767 --
768 ROLLBACK TO SNUniqueCheck;
769
770 x_errorcode := -1;
771 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
772
773 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
774 , p_data => x_msg_data);
775
776 WHEN OTHERS THEN
777 --
778 ROLLBACK TO SNUniqueCheck;
779 --
780 x_errorcode := -1;
781 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
782 --
783 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
784 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
785 END IF;
786 --
787 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
788 , p_data => x_msg_data);
789
790 END SNUniqueCheck;
791 /*----------------------------------------------------------------------------
792 -----------------------End of SNUniqueCheck Function -----------------------
793 ----------------------------------------------------------------------------*/
794
795 /*---------------------------------------------------------------------------
796 | Retrieves mask corresponding to passed transaction action id, transaction
797 | source type id, serial number control code and receive or issue flag
798 | the mask represents the above serial number status transition diagram
799 | also returns whether dynamic creation is ok, and to what status the
800 | serial number would go as result of this transaction
801 | in case of error returns false and x_errorcode indicates error
802 +--------------------------------------------------------------------------*/
803 FUNCTION SNGetMask(P_txn_act_id IN NUMBER,
804 P_txn_src_type_id IN NUMBER,
805 P_serial_control IN NUMBER,
806 x_to_status OUT NOCOPY NUMBER,
807 x_dynamic_ok OUT NOCOPY NUMBER,
808 P_receipt_issue_flag IN VARCHAR2,
809 x_mask OUT NOCOPY VARCHAR2,
810 x_errorcode OUT NOCOPY NUMBER)
811 RETURN BOOLEAN IS
812 --
813 TYPE L_mask_tab IS TABLE OF VARCHAR2(14)
814 INDEX BY BINARY_INTEGER;
815 L_sn_mask L_mask_tab;
816 L_group NUMBER := 0;
817 --
818 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
819 BEGIN
820 -- initialize out variables
821 x_errorcode := 0;
822 x_to_status := 0;
826 L_sn_mask(2) := 'I0000011010014';
823 x_dynamic_ok := 0;
824
825 L_sn_mask(1) := 'I0110100010004';
827 L_sn_mask(3) := 'R0110001001003';
828 L_sn_mask(4) := 'R0000101991013';
829 L_sn_mask(5) := 'R0000011001011';
830 L_sn_mask(6) := '00000000000000';
831 L_sn_mask(7) := 'R0110001001003';
832 L_sn_mask(8) := 'R0000101001013';
833 L_sn_mask(9) := 'I0110100010004';
834 L_sn_mask(10):= '00000000000000';
835 L_sn_mask(11):= 'R0110110001003';
836 L_sn_mask(12):= 'I0110110010004';
837 L_sn_mask(13):= '00000000000000';
838 L_sn_mask(14):= 'R0110001001103';
839 L_sn_mask(15):= 'R0000101001113';
840 L_sn_mask(16):= 'I0110100010005';
841 L_sn_mask(17):= '00000000000000';
842 /*---------------------------------------------------------------------
843 | Determine which group the transactions to. the value of
844 | group will be used to provide the appropriate offset in the sn_mask
845 | array table
846 +----------------------------------------------------------------------*/
847 -- Sales Order [SO] - 2
848 -- RMA - 12
849 -- SO RMA GROUP - 0
850 IF P_txn_src_type_id in (2,12) then
851 L_group := 0;
852 ELSE
853 IF P_txn_act_id = 2 then -- SUBXFR
854 L_group := 10 ; -- SUB_XFER_GROUP
855 ELSIF P_txn_act_id = 12 then -- INTERECEIPT
856 null; -- Not defined yet
857 ELSIF P_txn_act_id = 21 then -- INTSHIP
858 L_group := 13 ; -- INTRANS_GROUP
859 ELSE -- Default Value
860 L_group := 6 ; -- STD_GROUP
861 END IF;
862 END IF;
863 L_group := L_group + 1; -- It starts from 0th position, just to avoid
864 x_mask := L_sn_mask(L_group);
865 /*---------------------------------------------------------------------
866 | Match up the transaction with the appropriate mas and get the assigned
867 | status. If there is no match, then to_status will still be zero after
868 | the loop
869 +-----------------------------------------------------------------------*/
870 WHILE ( substr(x_mask,1,1) <> '0' )
871 LOOP
872 if ( substr(x_mask,1,1) = P_receipt_issue_flag ) AND
873 ( substr(x_mask,P_serial_control+1,1) = '1' ) then
874 x_to_status := to_number(substr(x_mask,14,1)); -- get the 14th character from mask
875 x_dynamic_ok := to_number(substr(x_mask,13,1)); -- get the 13th character from mask
876 exit;
877 end if;
878 L_group := L_group + 1; -- go to next mask group
879 x_mask := L_sn_mask(L_group);
880 END LOOP;
881
882 IF x_to_status = 0 then
883 FND_MESSAGE.SET_NAME('INV', 'INV_INLTIS_SNGET_MASK');
884 FND_MSG_PUB.Add;
885 x_errorcode := 123;
886 return(FALSE);
887 ELSE
888 return(TRUE);
889 END IF;
890
891 EXCEPTION
892 WHEN OTHERS THEN
893 x_errorcode := -1;
894 return(FALSE);
895 END SNGetmask;
896 /*---------------------------------------------------------------------------
897 -----------------------End of SNGetmask Function ---------------------------
898 ----------------------------------------------------------------------------*/
899
900 -- TODO: need to add serial geneology stuff
901 PROCEDURE SNValidate
902 (p_api_version IN NUMBER,
903 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE ,
904 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
905 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
906 x_return_status OUT NOCOPY VARCHAR2,
907 x_msg_count OUT NOCOPY NUMBER,
908 x_msg_data OUT NOCOPY VARCHAR2,
909 x_errorcode OUT NOCOPY NUMBER,
910
911 p_item_id IN NUMBER,
912 p_org_id IN NUMBER,
913 p_subinventory IN VARCHAR2,
914 p_txn_src_type_id IN NUMBER,
915 p_txn_action_id IN NUMBER,
916 p_serial_number IN VARCHAR2,
917 p_locator_id IN NUMBER,
918 p_lot_number IN VARCHAR2,
919 p_revision IN VARCHAR2,
920 x_SerExists OUT NOCOPY NUMBER,
921 P_mask IN VARCHAR2,
922 P_dynamic_ok IN NUMBER)
923 IS
924 L_api_version CONSTANT NUMBER := 0.9;
925 L_api_name CONSTANT VARCHAR2(30) := 'SNValidate';
926
927 -- Declare Local variables
928 L_current_status NUMBER;
929 L_current_revision VARCHAR2(4);
930 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
931 L_current_lot_number VARCHAR2(80);
932 L_current_subinventory VARCHAR2(10);
933 L_current_locator_id NUMBER;
934 L_current_organization_id NUMBER;
935 L_wip_entity_id NUMBER;
936 L_nothing VARCHAR2(10);
937 L_user_id NUMBER;
938 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
939 BEGIN
940 SAVEPOINT SNValidate;
941 -- Standard Call to check for call compatibility
942 IF NOT FND_API.Compatible_API_Call(l_api_version
946 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
943 , p_api_version
944 , l_api_name
945 , G_PKG_NAME) THEN
947 END IF;
948 --
949 -- Initialize message list if p_init_msg_list is set to true
950 IF FND_API.to_Boolean(p_init_msg_list) THEN
951 FND_MSG_PUB.initialize;
952 END IF;
953 --
954 -- Initialisize API return status to success
955 x_return_status := FND_API.G_RET_STS_SUCCESS;
956 x_errorcode := 0;
957
958
959 --
960 x_serexists := 1;
961
962 --
963 -- API Body
964 --
965
966
967 /*-------------------------------------------+
968 | Check for existence of the serial number
969 +-------------------------------------------*/
970 IF (l_debug = 1) THEN
971 mdebug ('SNValidate : Begin ');
972 END IF;
973 -- Validate Serial Number for exist
974 BEGIN
975 SELECT decode(current_status,6,1,current_status),
976 revision,
977 lot_number,
978 current_subinventory_code,
979 current_locator_id,
980 current_organization_id,
981 original_wip_entity_id
982 INTO L_current_status,
983 L_current_revision,
984 L_current_lot_number,
985 L_current_subinventory,
986 L_current_locator_id,
987 L_current_organization_id,
988 L_wip_entity_id
989 FROM MTL_SERIAL_NUMBERS
990 WHERE inventory_item_id = P_Item_id
991 AND serial_number = P_serial_number;
992 IF (l_debug = 1) THEN
993 mdebug ('SNValidate : After SQL ');
994 END IF;
995
996
997 IF L_current_locator_id is null then
998 L_current_locator_id := 0;
999 END IF;
1000
1001 IF L_current_organization_id is null then
1002 L_current_organization_id := 0;
1003 END IF;
1004
1005 IF (l_debug = 1) THEN
1006 mdebug ('SNValidate : Before status check ');
1007 END IF;
1008
1009 IF L_current_status is NULL or
1010 L_current_status = 2 or
1011 L_current_status < 1 or
1012 L_current_status > 5 then
1013 FND_MESSAGE.set_name('INV','INV_SER_INVALID_STATUS');
1014 FND_MESSAGE.SET_TOKEN('TOKEN',P_serial_number);
1015 FND_MSG_PUB.Add;
1016 x_errorcode := 105;
1017 raise FND_API.G_EXC_ERROR;
1018 END IF;
1019
1020 IF (l_debug = 1) THEN
1021 mdebug ('SNValidate : After status check ');
1022 END IF;
1023 /* Check the current status aginst the available status in the mask
1024 */
1025
1026 IF (l_debug = 1) THEN
1027 mdebug ('SNValidate : current status '||to_char(L_current_status));
1028 mdebug ('SNValidate : Mask '||P_mask);
1029 END IF;
1030 IF substr(P_mask,L_current_status+7,1)='0' then
1031 FND_MESSAGE.set_name('INV','INV_SER_STATUS_NA');
1032 FND_MESSAGE.SET_TOKEN('TOKEN',P_serial_number);
1033 FND_MSG_PUB.Add;
1034 x_errorcode := 106;
1035 raise FND_API.G_EXC_ERROR;
1036 END IF;
1037 IF (l_debug = 1) THEN
1038 mdebug ('SNValidate : After Stats+7 ');
1039 END IF;
1040
1041 /*-----------------------------------------------------------+
1042 | If the unit is currently in inventory (status 3), then it
1043 | must be issued or transferred to the same revision,
1044 | lot number, and org that it was received against.
1045 | If the unit is defined but not transacted (status 1) then
1046 | it must be received to or issued from the organization
1047 | for which it is defined.
1048 +----------------------------------------------------------*/
1049 IF (l_debug = 1) THEN
1050 mdebug ('SNValidate : Curr Stat'||to_char(L_current_status));
1051 mdebug ('SNValidate : P_Org '||to_char(p_org_id));
1052 mdebug ('SNValidate : L_Org '||to_char(L_current_organization_id));
1053 END IF;
1054
1055 IF (L_current_status = 3 or
1056 L_current_status = 1) AND
1057 L_current_organization_id <> p_org_id then
1058 FND_MESSAGE.set_name('INV','INV_SER_ORG_INVALID');
1059 FND_MESSAGE.SET_TOKEN('TOKEN',P_serial_number);
1060 FND_MSG_PUB.Add;
1061 x_errorcode := 107;
1062 RAISE FND_API.G_EXC_ERROR;
1063 END IF;
1064
1065 IF (l_debug = 1) THEN
1066 mdebug ('SNValidate : After Stats - not org ');
1067 END IF;
1068
1069 IF L_current_status = 3 then
1070 IF L_current_revision <> P_revision then
1071 FND_MESSAGE.set_name('INV','INV_SER_REV_INVALID');
1072 FND_MESSAGE.SET_TOKEN('TOKEN1',P_serial_number);
1073 FND_MESSAGE.SET_TOKEN('TOKEN2',L_current_revision);
1074 FND_MSG_PUB.Add;
1075 x_errorcode := 108;
1076 raise FND_API.G_EXC_ERROR;
1077 ELSIF L_current_lot_number <> P_lot_number then
1078 FND_MESSAGE.set_name('INV','INV_SER_LOT_INVALID');
1079 FND_MESSAGE.SET_TOKEN('TOKEN1',P_serial_number);
1080 FND_MESSAGE.SET_TOKEN('TOKEN2',L_current_lot_number);
1081 FND_MSG_PUB.Add;
1082 x_errorcode := 109;
1083 raise FND_API.G_EXC_ERROR;
1084 END IF;
1085 END IF;
1086 /*--------------------------------------------------------------+
1087 | If issuing a unit which is currently in inventory, then we
1091 IF (l_debug = 1) THEN
1088 | must issue from the organization, subinventory, and locator
1089 | in which the unit is currently located.
1090 +--------------------------------------------------------------*/
1092 mdebug ('SNValidate : Before mask check 0 - 3 ');
1093 END IF;
1094 IF substr(P_mask,1,1)='I' and L_current_status = 3 then
1095 IF L_current_subinventory <> P_subinventory then
1096 FND_MESSAGE.set_name('INV','INV_SER_SUB_INVALID');
1097 FND_MESSAGE.SET_TOKEN('TOKEN1',P_serial_number);
1098 FND_MESSAGE.SET_TOKEN('TOKEN2',L_current_subinventory);
1099 FND_MSG_PUB.Add;
1100 x_errorcode := 110;
1101 RAISE FND_API.G_EXC_ERROR;
1102 ELSIF L_current_locator_id <> P_locator_id then
1103 FND_MESSAGE.set_name('INV','INV_SER_LOC_INVALID');
1104 FND_MESSAGE.SET_TOKEN('TOKEN1',P_serial_number);
1105 FND_MSG_PUB.Add;
1106 x_errorcode := 111;
1107 RAISE FND_API.G_EXC_ERROR;
1108 END IF;
1109 END IF;
1110
1111 EXCEPTION
1112 WHEN NO_DATA_FOUND THEN
1113 IF (l_debug = 1) THEN
1114 mdebug('Exception :whennodatafound dynamic '||to_char(nvl(P_dynamic_ok,0)));
1115 END IF;
1116
1117 IF nvl(P_dynamic_ok,0)= 0 then
1118 FND_MESSAGE.set_name('INV','INV_SER_NOTEXIST');
1119 FND_MESSAGE.SET_TOKEN('TOKEN',P_serial_number);
1120 FND_MSG_PUB.Add;
1121 x_errorcode := 118;
1122 RAISE FND_API.G_EXC_ERROR;
1123 ELSE
1124 x_SerExists := 0;
1125 END IF;
1126 END;
1127
1128
1129 IF P_txn_src_type_id = 5 AND
1130 (( P_txn_action_id = 31 and L_current_status <> 1) OR
1131 ( P_txn_action_id = 32 ) OR
1132 ( P_txn_action_id = 27 ) ) AND
1133 L_wip_entity_id is NULL then
1134 FND_MESSAGE.set_name('INV','INV_SER_STATUS_NA');
1135 FND_MESSAGE.SET_TOKEN('TOKEN',P_serial_number);
1136 FND_MSG_PUB.Add;
1137 x_errorcode := 106;
1138 raise FND_API.G_EXC_ERROR;
1139 END IF;
1140
1141 --
1142 -- SERIAL GENEOLOGY UPDATE GOES HERE
1143 --
1144
1145
1146 --
1147 -- END of API body
1148 --
1149
1150 IF FND_API.to_Boolean(p_commit) THEN
1151 COMMIT;
1152 END IF;
1153 -- Standard call to get message count and if count is 1, get message info
1154 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
1155 , p_data => x_msg_data);
1156
1157 EXCEPTION
1158 WHEN FND_API.G_EXC_ERROR THEN
1159 --
1160 ROLLBACK TO SNValidate;
1161 --
1162 x_return_status := FND_API.G_RET_STS_ERROR;
1163 --
1164 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
1165 , p_data => x_msg_data);
1166 --
1167
1168 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1169 IF (l_debug = 1) THEN
1170 mdebug('InvQtyBetwn: Unexpected error '||sqlerrm);
1171 END IF;
1172 --
1173 ROLLBACK TO SNValidate;
1174 --
1175 x_errorcode := -1;
1176
1177 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1178
1179 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
1180 , p_data => x_msg_data);
1181
1182
1183 WHEN OTHERS THEN
1184 IF (l_debug = 1) THEN
1185 mdebug('SNValidate other:' || sqlerrm);
1186 END IF;
1187 --
1188 ROLLBACK TO SNValidate;
1189 --
1190 x_errorcode := -1;
1191 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1192 --
1193 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1194 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1195 END IF;
1196 --
1197 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
1198 , p_data => x_msg_data);
1199
1200 END SNValidate;
1201 /*---------------------------------------------------------------------------
1202 -----------------------End of SNValidate Function --------------------------
1203 ----------------------------------------------------------------------------*/
1204
1205 /* ========================================================================
1206 | Helper procedure called form inv_qtybetwn
1207 | takes p_from_serial_number and p_to_serial_number and
1208 | parses out common prefix and range of numbers from them
1209 | (if p_to_serial_number is not supplied than it will be assumed to be the
1210 | same as p_from_serial_number)
1211 |
1212 | pre: none
1213 |
1214 | returns: true upon successful completion
1215 |
1216 | false upon failure, x_errorcode will contain error number
1217 |
1218 | if function completes successfully then
1219 | x_prefix will contain common prefix or null if both from and two do not
1220 | have alpha prefix
1221 | x_quantity will contain positive quantity (# of serial numbers between
1222 | p_from_serial_number and p_to_serial_number)
1223 | x_from number will contain starting number
1224 | x_to_serial_number will contain ending range number
1225 +-------------------------------------------------------------------------*/
1226
1227
1228 FUNCTION INV_SERIAL_INFO(P_FROM_SERIAL_NUMBER IN VARCHAR2,
1229 P_TO_SERIAL_NUMBER IN VARCHAR2,
1230 x_PREFIX OUT NOCOPY VARCHAR2,
1234 x_errorcode OUT NOCOPY NUMBER)
1231 x_QUANTITY OUT NOCOPY VARCHAR2,
1232 X_FROM_NUMBER OUT NOCOPY VARCHAR2,
1233 X_TO_NUMBER OUT NOCOPY VARCHAR2,
1235 RETURN BOOLEAN IS
1236 L_f_alp_part VARCHAR2(30);
1237 L_t_alp_part VARCHAR2(30);
1238 L_f_num_part VARCHAR2(30);
1239 L_t_num_part VARCHAR2(30);
1240 L_ser_col_val VARCHAR2(30);
1241 L_ser_col_num NUMBER;
1242 L_from_length NUMBER;
1243 L_to_length NUMBER;
1244 L_f_ser_num VARCHAR2(30);
1245 L_t_ser_num VARCHAR2(30);
1246 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1247 BEGIN
1248 x_errorcode := 0;
1249
1250 L_f_ser_num := P_FROM_SERIAL_NUMBER;
1251 L_t_ser_num := P_TO_SERIAL_NUMBER;
1252
1253
1254
1255 /*--------------------------------------------------------------+
1256 | Get the lengths of the two serial numbers. If the to serial
1257 | number is not specified copy from serial number to it.
1258 +--------------------------------------------------------------*/
1259 L_from_length := nvl(LENGTH(L_f_ser_num),0);
1260 L_to_length := nvl(LENGTH(L_t_ser_num),0);
1261 IF (l_debug = 1) THEN
1262 mdebug('L_from_length='||L_from_length);
1263 mdebug('L_to_length='||L_to_length);
1264 END IF;
1265
1266 --
1267 IF L_from_length = 0 then
1268 FND_MESSAGE.SET_NAME('INV', 'INV_QTYBTWN_NO_SERIAL');
1269 FND_MSG_PUB.Add;
1270 x_errorcode := 124;
1271 return (FALSE);
1272 END IF;
1273
1274 IF L_to_length = 0 then
1275 L_t_ser_num := L_f_ser_num;
1276 L_to_length := L_from_length;
1277 END IF;
1278 /*-----------------------------------------------------------------+
1279 | Split the given serial number into alpha
1280 | prefix and numeric part.
1281 +-----------------------------------------------------------------*/
1282 -- From Serial Number
1283 L_ser_col_num := L_from_length;
1284 --
1285 while (L_ser_col_num > 0 )
1286 loop
1287 L_ser_col_val := substr(L_f_ser_num,L_ser_col_num,1);
1288 if ASCII(L_ser_col_val) >= 48 and ASCII(L_ser_col_val) <= 57 then
1289 L_f_num_part := L_ser_col_val||L_f_num_part;
1290 else
1291 L_f_alp_part := substr(L_f_ser_num,1,L_ser_col_num);
1292 exit;
1293 end if;
1294 L_ser_col_num := L_ser_col_num - 1;
1295 end loop;
1296 -- To Serial Number
1297 -- Values for 0 to 9 is corresponds to ASCII value 48 TO 57
1298 -- All other values are Non-numeric value
1299 --
1300 L_ser_col_num := L_to_length;
1301 while (L_ser_col_num > 0)
1302 loop
1303 L_ser_col_val := substr(L_t_ser_num,L_ser_col_num,1);
1304 if ascii(L_ser_col_val) >= 48 and ascii(L_ser_col_val) <= 57 then
1305 L_t_num_part := L_ser_col_val||L_t_num_part;
1306 else
1307 L_t_alp_part := substr(L_t_ser_num,1,L_ser_col_num);
1308 exit;
1309 end if;
1310 L_ser_col_num := L_ser_col_num - 1;
1311 end loop;
1312 /*----------------------------------------------------------------+
1313 | We compare the prefixes to see if they are the same
1314 +----------------------------------------------------------------*/
1315
1316 if (L_f_alp_part <> L_t_alp_part) or
1317 (l_f_alp_part is null and l_t_alp_part is not null) or
1318 (l_f_alp_part is not null and l_t_alp_part is null)
1319 then
1320 FND_MESSAGE.set_name('INV','INV_QTYBTWN_PFX');
1321 FND_MSG_PUB.Add;
1322 x_errorcode := 119;
1323 RETURN(FALSE);
1324
1325 end if;
1326 /*---------------------------------------------------------------+
1327 | Check the lengths of the two serial numbers to make sure they
1328 | match.
1329 +---------------------------------------------------------------*/
1330 if (L_from_length <> L_to_length) then
1331 -- Message Name : INV_QTYBTWN_LGTH
1332 FND_MESSAGE.set_name('INV','INV_QTYBTWN_LGTH');
1333 FND_MSG_PUB.Add;
1334 x_errorcode := 120;
1335 RETURN(FALSE);
1336 end if;
1337 /*-------------------------------------------------------
1338 | Check whether the serial numbers are matched
1339 | If not, check the last character of serial number is character
1340 | If yes, return error message
1341 +-------------------------------------*/
1342 -- XXX checks only one
1343 if L_f_ser_num <> L_t_ser_num then
1344 if ascii(substr(L_f_ser_num,LENGTH(L_f_ser_num),1)) < 48 and
1345 ascii(substr(L_f_ser_num,LENGTH(L_f_ser_num),1)) > 57 then
1346 FND_MESSAGE.set_name('INV','INV_QTYBTWN_LAST');
1347 FND_MSG_PUB.Add;
1348 x_errorcode := 121;
1349 RETURN (FALSE);
1350 end if;
1351 end if;
1352 -- Calculate the difference of serial numbers
1353 -- How many serial nos are there in the given range
1354 --
1355 IF (l_debug = 1) THEN
1356 mdebug('L_t_num_part='||L_t_num_part);
1357 mdebug('L_f_num_part='||L_f_num_part);
1358 END IF;
1359
1360 -- Out variables
1361 X_Quantity :=
1362 nvl(to_number(L_t_num_part),0) - nvl(to_number(L_f_num_part),0) + 1;
1363
1364 if (X_Quantity <= 0) then
1365 -- Message Name : INV_QTYBTWN_NUM
1366 FND_MESSAGE.set_name('INV','INV_QTYBTWN_NUM');
1367 FND_MSG_PUB.Add;
1368 x_errorcode := 122;
1369 RETURN (FALSE);
1370 end if;
1371 --
1372 /*--------------------------------------------------------------+
1373 | Check to make sure To serial number is greater than
1374 | From serial number.
1375 +--------------------------------------------------------------*/
1376
1377 X_PREFIX := L_f_alp_part;
1378 X_FROM_NUMBER := L_f_num_part ;
1379 X_TO_NUMBER := L_t_num_part;
1380
1381 RETURN(TRUE);
1382
1383 EXCEPTION
1384 WHEN OTHERS THEN
1385 x_errorcode := -1;
1386 RETURN(FALSE);
1387 END;
1388 END MTL_SERIAL_CHECK;
[Home] [Help]