1 PACKAGE MTL_SERIAL_CHECK AUTHID CURRENT_USER AS
2 /* $Header: INVSERLS.pls 115.6 2004/02/13 22:53:57 gayu ship $ */
3
4 /*==========================================================================+
5 |---------------------------------------------------------------------------
6 | TITLE: PROCEDURE : INV_QTYBTWN
7 |---------------------------------------------------------------------------
8 | PURPOSE: Takes two alphanumeric serial numbers and returns both the
9 | quantity of individual serial numbers which fall between them and
10 | the alpha prefix of the first serial number.
11 |
12 | PARAMETERS:
13 | P_FROM_SERIAL_NUMBER and P_TO_SERIAL_NUMBER specify the range of
14 | alphanumeric serial numbers from which QTYBTWN is to determine the
15 | quantity. P_QUANTITY is the field name to which the quantity of
16 | serial numbers is to bewritten. P_PREFIX is the field name to which
17 | the alpha prefix is to be written.
18 |
19 | RETURN: Returns RET_FAILURE if failure.
20 +==========================================================================*/
21 PROCEDURE INV_QTYBETWN
22 ( p_api_version IN NUMBER,
23 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
24 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
25 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
26 x_return_status OUT NOCOPY VARCHAR2,
27 x_msg_count OUT NOCOPY NUMBER,
28 x_msg_data OUT NOCOPY VARCHAR2,
29 x_errorcode OUT NOCOPY NUMBER,
30
31 P_FROM_SERIAL_NUMBER IN VARCHAR2,
32 P_TO_SERIAL_NUMBER IN VARCHAR2,
33 X_QUANTITY OUT NOCOPY NUMBER,
34 X_PREFIX OUT NOCOPY VARCHAR2,
35 P_ITEM_ID IN NUMBER,
36 P_ORGANIZATION_ID IN NUMBER,
37 P_SERIAL_NUMBER_TYPE IN NUMBER,
38 P_TRANSACTION_ACTION_ID IN NUMBER,
39 P_TRANSACTION_SOURCE_TYPE_ID IN NUMBER,
40 P_SERIAL_CONTROL IN NUMBER,
41 P_REVISION IN VARCHAR2,
42 P_LOT_NUMBER IN VARCHAR2,
43 P_SUBINVENTORY IN VARCHAR2,
44 P_LOCATOR_ID IN NUMBER,
45 P_RECEIPT_ISSUE_FLAG IN VARCHAR2,
46 p_simulate IN VARCHAR2 DEFAULT FND_API.G_FALSE
47 ) ;
48
49 /*==========================================================================+
50 |---------------------------------------------------------------------------
51 | TITLE: FUNCTION : SNUniqueCheck
52 |---------------------------------------------------------------------------
53 | PURPOSE:
54 | Determine whether or not a given serial can be created without
55 | violating the organization's uniqueness criteria.
56 |
57 | PARAMETERS:
58 | org_id is the organization_id, serial_number_type is the value from
59 | MTL_PARAMETERS, ser_number is the serial number in question, message
60 | is expected to point to a text[241].
61 |
62 | RETURN:
63 | Returns TRUE on success, FALSE on error.
64 |
65 | ERROR CONDITIONS:
66 | SQLERROR.
67 | Violation of uniqueness criteria.
68 +==========================================================================*/
69 PROCEDURE SNUniqueCheck
70 ( p_api_version IN NUMBER,
71 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
72 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
73 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
74 x_return_status OUT NOCOPY VARCHAR2,
75 x_msg_count OUT NOCOPY NUMBER,
76 x_msg_data OUT NOCOPY VARCHAR2,
77 x_errorcode OUT NOCOPY NUMBER,
78
79 p_org_id IN NUMBER,
80 p_serial_number_type IN NUMBER ,
81 p_serial_number IN VARCHAR2 )
82
83 ;
84 /*==========================================================================+
85 |---------------------------------------------------------------------------
86 | TITLE: FUNCTION : SNGetMask
87 |---------------------------------------------------------------------------
88 | PURPOSE:
89 | Get the sn_mask row appropriate for a given transaction from the array
90 | sn_mask defined at the top of this file.
91 |
92 | PARAMETERS:
93 | transaction_type and disposition_type are self-explanatory.
94 | receipt_issue_flag should be a pointer to 'I' or 'R'.
95 | serial_control is the serial control value for the item being
96 | transacted.
97 | to_status is an sb2 pointer. The resultant current_status that the
98 | transacted serial number should get is written to this variable.
99 | dynamic_ok is an sb2 pointer. If dynamic inserts are possible for
100 | the transaction, a 1 is written to this variable.
101 | mask is a pointer to a text (*x)[15] (pointer to an array of 15 text
102 | characters). The address of the appropriate sn_mask row is
103 | written to this variable.
104 | message is expected to point to a text[241].
105 +==========================================================================*/
106 FUNCTION SNGetMask
107 (P_txn_act_id IN NUMBER,
108 P_txn_src_type_id IN NUMBER,
109 P_serial_control IN NUMBER,
110 x_to_status OUT NOCOPY NUMBER,
111 x_dynamic_ok OUT NOCOPY NUMBER,
112 P_receipt_issue_flag IN VARCHAR2,
113 x_mask OUT NOCOPY VARCHAR2,
114 x_errorcode OUT NOCOPY NUMBER)
115 RETURN BOOLEAN;
116
117 /*==========================================================================+
118 |---------------------------------------------------------------------------
119 | TITLE: FUNCTION : SNValidate
120 |---------------------------------------------------------------------------
121 | PURPOSE:
122 | Check the validity of a particular serial number for a particular
123 | transaction.
124 |
125 | PARAMETERS:
126 | inltis_args is a pointer to an INLTIS_ARGS array. Some of the
127 | elements of the array are not used by this function.
128 | sncur_args is a pointer to an SNCUR_ARGS array. If the serial
129 | number already exists, then the current values of the serial number
130 | are written to this array, otherwise the array is filled with 0's.
131 | ser_number is the serial number to be validated.
132 | dynamic_ok (0,1) indicates whether dynamic entry is allowed.
133 | message is expected to point to a text[241].
134 +==========================================================================*/
135 PROCEDURE SNValidate
136 (p_api_version IN NUMBER,
137 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE ,
138 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
139 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
140 x_return_status OUT NOCOPY VARCHAR2,
141 x_msg_count OUT NOCOPY NUMBER,
142 x_msg_data OUT NOCOPY VARCHAR2,
143 x_errorcode OUT NOCOPY NUMBER,
144
145 p_item_id IN NUMBER,
146 p_org_id IN NUMBER,
147 p_subinventory IN VARCHAR2,
148 p_txn_src_type_id IN NUMBER,
149 p_txn_action_id IN NUMBER,
150 p_serial_number IN VARCHAR2,
151 p_locator_id IN NUMBER,
152 p_lot_number IN VARCHAR2,
153 p_revision IN VARCHAR2,
154 x_SerExists OUT NOCOPY NUMBER,
155 P_mask IN VARCHAR2,
156 P_dynamic_ok IN NUMBER)
157 ;
158 /*==========================================================================*/
159 FUNCTION INV_SERIAL_INFO
160 (P_FROM_SERIAL_NUMBER IN VARCHAR2,
161 P_TO_SERIAL_NUMBER IN VARCHAR2,
162 x_PREFIX OUT NOCOPY VARCHAR2,
163 x_QUANTITY OUT NOCOPY VARCHAR2,
164 X_FROM_NUMBER OUT NOCOPY VARCHAR2,
165 X_TO_NUMBER OUT NOCOPY VARCHAR2,
166 x_errorcode OUT NOCOPY NUMBER)
167
168 RETURN BOOLEAN;
169 END MTL_SERIAL_CHECK;