1 PACKAGE inv_serial_number_pub AUTHID CURRENT_USER AS
2 /* $Header: INVPSNS.pls 120.6.12020000.3 2012/11/06 18:43:45 bhshet ship $ */
3 /*#
4 * The Serial Numbers procedures allow users to create, update and validate serials.
5 * In Addition users can the uniqueness of a serial number,
6 * get difference between two serial numbers, validate and update serial attributes and
7 * create unit (serial) transactions
8 * @rep:scope public
9 * @rep:product INV
10 * @rep:lifecycle active
11 * @rep:displayname Inventory Serial Number
12 * @rep:category BUSINESS_ENTITY INV_SERIAL_NUMBER
13 */
14
15 g_org_id NUMBER;
16 g_transfer_org_id NUMBER;
17 g_firstscan BOOLEAN := TRUE;
18 g_serial_attributes_tbl inv_lot_sel_attr.lot_sel_attributes_tbl_type;
19
20 /*#
21 * Use this procedure to populate Serial Attribute columns
22 * @rep:scope public
23 * @rep:lifecycle active
24 * @rep:displayname Populate Serial Attribute Columns
25 */
26 PROCEDURE populateattributescolumn;
27
28 /*#
29 * Use this procedure to Set the value of g_firstscan variable
30 * @param p_firstscan TRUE or FALSE can be passed as input in this variable to set the value of g_firstscan
31 * @rep:scope public
32 * @rep:lifecycle active
33 * @rep:displayname Set First Scan
34 */
35 PROCEDURE set_firstscan(p_firstscan IN BOOLEAN);
36
37 -- Overloaded Procedure insertSerial for eAM
38 /*#
39 * Use this procedure to validate and insert a given Serial Number.
40 * @param p_api_version API version is passed as input in this variable
41 * @paraminfo {@rep:required}
42 * @param p_init_msg_list fnd_api.g_false or fnd_api.g_true is passed as input in this variable to determine whether to Initialize message list or not
43 * @paraminfo {@rep:required}
44 * @param p_commit fnd_api.g_false or fnd_api.g_true is passed as input in this variable to determine whether to commit or not
45 * @paraminfo {@rep:required}
46 * @param p_validation_level Validation level is passed as input in this variable
47 * @paraminfo {@rep:required}
48 * @param p_inventory_item_id Inventory Item id is passed as input in this variable
49 * @paraminfo {@rep:required}
50 * @param p_organization_id Organization id is passed as input in this variable
51 * @paraminfo {@rep:required}
52 * @param p_serial_number Serial Number is passed as input in this variable
53 * @paraminfo {@rep:required}
54 * @param p_current_status Current Status of the Serial Number is passed as input in this variable
55 * @paraminfo {@rep:required}
56 * @param p_group_mark_id Group Mark id is passed as input in this variable
57 * @paraminfo {@rep:required}
58 * @param p_lot_number Lot Number is passed as input in this variable
59 * @paraminfo {@rep:required}
60 * @param p_initialization_date Initialization Date is passed as input in this variable
61 * @param x_return_status Return Status indiacation success or failure.
62 * @paraminfo {@rep:required}
63 * @param x_msg_count message count from the error stack in case of failure
64 * @paraminfo {@rep:required}
65 * @param x_msg_data x_msg_data Return the error message in case of failure
66 * @paraminfo {@rep:required}
67 * @param p_organization_type Organization type is passed as input in this variable
68 * @param p_owning_org_id Owning Organization id is passed as input in this variable
69 * @param p_owning_tp_type Owning Trading partner type is passed as input in this variable
70 * @param p_planning_org_id Planning Organization id is passed as input in this variable
71 * @param p_planning_tp_type Planning Trading partner type is passed as input in this variable
72 * @rep:scope public
73 * @rep:lifecycle active
74 * @rep:displayname Insert Serial Number
75 */
76 PROCEDURE insertserial(
77 p_api_version IN NUMBER
78 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
79 , p_commit IN VARCHAR2 := fnd_api.g_false
80 , p_validation_level IN NUMBER := fnd_api.g_valid_level_full
81 , p_inventory_item_id IN NUMBER
82 , p_organization_id IN NUMBER
83 , p_serial_number IN VARCHAR2
84 , p_current_status IN NUMBER
85 , p_group_mark_id IN NUMBER
86 , p_lot_number IN VARCHAR2
87 , p_initialization_date IN DATE DEFAULT SYSDATE
88 , x_return_status OUT NOCOPY VARCHAR2
89 , x_msg_count OUT NOCOPY NUMBER
90 , x_msg_data OUT NOCOPY VARCHAR2
91 , p_organization_type IN NUMBER DEFAULT NULL
92 , p_owning_org_id IN NUMBER DEFAULT NULL
93 , p_owning_tp_type IN NUMBER DEFAULT NULL
94 , p_planning_org_id IN NUMBER DEFAULT NULL
95 , p_planning_tp_type IN NUMBER DEFAULT NULL
96 );
97
98 -- 'Serial Tracking in WIP project. add wip_entity_id, operation_seq_num, intraooperation_step_type
99 -- also as the input parameters.
100 /*#
101 * Use this procedure to validate and insert a given Serial Number.
102 * @param p_api_version API version is passed as input in this variable
103 * @paraminfo {@rep:required}
104 * @param p_init_msg_list fnd_api.g_false or fnd_api.g_true is passed as input in this variable to determine whether to Initialize message list or not
105 * @paraminfo {@rep:required}
106 * @param p_commit fnd_api.g_false or fnd_api.g_true is passed as input in this variable to determine whether to commit or not
107 * @paraminfo {@rep:required}
108 * @param p_validation_level Validation level is passed as input in this variable
109 * @paraminfo {@rep:required}
110 * @param p_inventory_item_id Inventory Item id is passed as input in this variable
111 * @paraminfo {@rep:required}
112 * @param p_organization_id Organization id is passed as input in this variable
113 * @paraminfo {@rep:required}
114 * @param p_serial_number Serial Number is passed as input in this variable
115 * @paraminfo {@rep:required}
116 * @param p_initialization_date Initialization Date is passed as input in this variable
117 * @paraminfo {@rep:required}
118 * @param p_completion_date Unit completion date is passed as input in this variable
119 * @paraminfo {@rep:required}
120 * @param p_ship_date Unit Ship Date is passed as input in this variable
121 * @paraminfo {@rep:required}
122 * @param p_revision Inventory Item Revision code is passed as input in this variable
123 * @paraminfo {@rep:required}
124 * @param p_lot_number Lot Number is passed as input in this variable
125 * @paraminfo {@rep:required}
126 * @param p_current_locator_id Current Locator id is passed as input in this variable
127 * @paraminfo {@rep:required}
128 * @param p_subinventory_code Current Subinventry Code is passed as input in this variable
129 * @paraminfo {@rep:required}
130 * @param p_trx_src_id Transaction Source id is passed as input in this variable
131 * @paraminfo {@rep:required}
132 * @param p_unit_vendor_id Unit Supplier Code is passed as input in this variable
133 * @paraminfo {@rep:required}
134 * @param p_vendor_lot_number Supplier Lot Number is passed as input in this variable
135 * @paraminfo {@rep:required}
136 * @param p_vendor_serial_number Supplier Serial Number is passed as input in this variable
137 * @paraminfo {@rep:required}
138 * @param p_receipt_issue_type Transaction Type is passed as input in this variable
139 * @paraminfo {@rep:required}
140 * @param p_txn_src_id Transaction Source id is passed as input in this variable
141 * @paraminfo {@rep:required}
142 * @param p_txn_src_name Transaction source Name is passed as input in this variable
143 * @paraminfo {@rep:required}
144 * @param p_txn_src_type_id Transaction Source type id is passed as input in this variable
145 * @paraminfo {@rep:required}
146 * @param p_transaction_id Transaction id is passed as input in this variable
147 * @paraminfo {@rep:required}
148 * @param p_current_status Current Status of the Serial Number is passed as input in this variable
149 * @paraminfo {@rep:required}
150 * @param p_parent_item_id Component parent part Number is passed as input in this variable
151 * @paraminfo {@rep:required}
152 * @param p_parent_serial_number Component parent Serial Number is passed as input in this variable
153 * @paraminfo {@rep:required}
154 * @param p_cost_group_id Cost Group id is passed as input in this variable
155 * @paraminfo {@rep:required}
156 * @param p_transaction_action_id Transaction action id is passed as input in this variable
157 * @paraminfo {@rep:required}
158 * @param p_transaction_temp_id Transaction Temp id is passed as input in this variable
159 * @paraminfo {@rep:required}
160 * @param p_status_id Status id is passed as input in this variable
161 * @paraminfo {@rep:required}
162 * @param x_object_id Return Object id
163 * @paraminfo {@rep:required}
164 * @param x_return_status Return Status indiacation success or failure.
165 * @paraminfo {@rep:required}
166 * @param x_msg_count message count from the error stack in case of failure
167 * @paraminfo {@rep:required}
168 * @param x_msg_data x_msg_data Return the error message in case of failure
169 * @paraminfo {@rep:required}
170 * @param p_organization_type Organization type is passed as input in this variable
171 * @param p_owning_org_id Owning Organization id is passed as input in this variable
172 * @param p_owning_tp_type Owning Trading partner type is passed as input in this variable
173 * @param p_planning_org_id Planning Organization id is passed as input in this variable
174 * @param p_planning_tp_type Planning Trading partner type is passed as input in this variable
175 * @param p_wip_entity_id Wip entity id is passed as input in this variable
176 * @param p_operation_seq_num Wip operaion Sequence Number is passed as input in this variable
177 * @param p_intraoperation_step_type Code for Interopration step in mfg_lookups is passed as input in this variable
178 * @paraminfo {@rep:required}
179 * @rep:scope public
180 * @rep:lifecycle active
181 * @rep:displayname Insert Serial Number
182 */
183 PROCEDURE insertserial(
184 p_api_version IN NUMBER
185 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
186 , p_commit IN VARCHAR2 := fnd_api.g_false
187 , p_validation_level IN NUMBER := fnd_api.g_valid_level_full
188 , p_inventory_item_id IN NUMBER
189 , p_organization_id IN NUMBER
190 , p_serial_number IN VARCHAR2
191 , p_initialization_date IN DATE
192 , p_completion_date IN DATE
193 , p_ship_date IN DATE
194 , p_revision IN VARCHAR2
195 , p_lot_number IN VARCHAR2
196 , p_current_locator_id IN NUMBER
197 , p_subinventory_code IN VARCHAR2
198 , p_trx_src_id IN NUMBER
199 , p_unit_vendor_id IN NUMBER
200 , p_vendor_lot_number IN VARCHAR2
201 , p_vendor_serial_number IN VARCHAR2
202 , p_receipt_issue_type IN NUMBER
203 , p_txn_src_id IN NUMBER
204 , p_txn_src_name IN VARCHAR2
205 , p_txn_src_type_id IN NUMBER
206 , p_transaction_id IN NUMBER
207 , p_current_status IN NUMBER
208 , p_parent_item_id IN NUMBER
209 , p_parent_serial_number IN VARCHAR2
210 , p_cost_group_id IN NUMBER
211 , p_transaction_action_id IN NUMBER
212 , p_transaction_temp_id IN NUMBER
213 , p_status_id IN NUMBER
214 , x_object_id OUT NOCOPY NUMBER
215 , x_return_status OUT NOCOPY VARCHAR2
216 , x_msg_count OUT NOCOPY NUMBER
217 , x_msg_data OUT NOCOPY VARCHAR2
218 , p_organization_type IN NUMBER DEFAULT NULL
219 , p_owning_org_id IN NUMBER DEFAULT NULL
220 , p_owning_tp_type IN NUMBER DEFAULT NULL
221 , p_planning_org_id IN NUMBER DEFAULT NULL
222 , p_planning_tp_type IN NUMBER DEFAULT NULL
223 , p_wip_entity_id IN NUMBER DEFAULT NULL
224 , p_operation_seq_num IN NUMBER DEFAULT NULL
225 , p_intraoperation_step_type IN NUMBER DEFAULT NULL
226 );
227
228 -- Overloaded Procedure insert_range_serial for Serial Blacklist
229 /*#
230 * Use this procedure to validate and insert a given Range Serial Number .
231 * @param p_api_version API version is passed as input in this variable
232 * @paraminfo {@rep:required}
233 * @param p_init_msg_list fnd_api.g_false or fnd_api.g_true is passed as input in this variable to determine whether to Initialize message list or not
234 * @paraminfo {@rep:required}
235 * @param p_commit fnd_api.g_false or fnd_api.g_true is passed as input in this variable to determine whether to commit or not
236 * @paraminfo {@rep:required}
237 * @param p_validation_level Validation level is passed as input in this variable
238 * @paraminfo {@rep:required}
239 * @param p_inventory_item_id Inventory Item id is passed as input in this variable
240 * @paraminfo {@rep:required}
241 * @param p_organization_id Organization id is passed as input in this variable
242 * @paraminfo {@rep:required}
243 * @param p_fm_serial_number Serial Number is passed as input in this variable
244 * @paraminfo {@rep:required}
245 * @param p_to_serial_number Serial Number is passed as input in this variable
246 * @paraminfo {@rep:required}
247 * @param p_group_mark_id Group Mark id is passed as input in this variable
248 * @paraminfo {@rep:required}
249 * @param p_lot_number Lot Number is passed as input in this variable
250 * @paraminfo {@rep:required}
251 * @param p_initialization_date Initialization Date is passed as input in this variable
252 * @param x_return_status Return Status indiacation success or failure.
253 * @paraminfo {@rep:required}
254 * @param x_msg_count message count from the error stack in case of failure
255 * @paraminfo {@rep:required}
256 * @param x_msg_data x_msg_data Return the error message in case of failure
257 * @paraminfo {@rep:required}
258 * @param p_organization_type Organization type is passed as input in this variable
259 * @param p_owning_org_id Owning Organization id is passed as input in this variable
260 * @param p_owning_tp_type Owning Trading partner type is passed as input in this variable
261 * @param p_planning_org_id Planning Organization id is passed as input in this variable
262 * @param p_planning_tp_type Planning Trading partner type is passed as input in this variable
263 * @rep:scope public
264 * @rep:lifecycle active
265 * @rep:displayname Insert Serial Number
266 */
267 PROCEDURE insert_range_serial(
268 p_api_version IN NUMBER
269 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
270 , p_commit IN VARCHAR2 := fnd_api.g_false
271 , p_validation_level IN NUMBER := fnd_api.g_valid_level_full
272 , p_inventory_item_id IN NUMBER
273 , p_organization_id IN NUMBER
274 , p_fm_serial_number IN VARCHAR2
275 , p_to_serial_number IN VARCHAR2
276 , p_current_status IN NUMBER
277 , p_status_id IN NUMBER
278 , p_group_mark_id IN NUMBER
279 , p_lot_number IN VARCHAR2
280 , p_initialization_date IN DATE DEFAULT SYSDATE
281 , x_return_status OUT NOCOPY VARCHAR2
282 , x_msg_count OUT NOCOPY NUMBER
283 , x_msg_data OUT NOCOPY VARCHAR2
284 , p_organization_type IN NUMBER DEFAULT NULL
285 , p_owning_org_id IN NUMBER DEFAULT NULL
286 , p_owning_tp_type IN NUMBER DEFAULT NULL
287 , p_planning_org_id IN NUMBER DEFAULT NULL
288 , p_planning_tp_type IN NUMBER DEFAULT NULL
289 );
290
291 -- This api is the wrapper to insert a range of serial numbers into
292 -- mtl_serial_numbers
293
294 /* FP-J Lot/Serial Support Enhancement
295 * Created a new parameter p_rcv_serial_flag that would be used to identify
296 * that this API is called from receiving UI, which is used to control updates
297 * to MTL_SERIAL_NUMBERS
298 */
299 /*#
300 * Use this procedure to validate and insert a range of Serial Numbers
301 * based on Start Serial Number and End Serial Number.
302 * @param p_api_version API version is passed as input in this variable
303 * @paraminfo {@rep:required}
304 * @param p_init_msg_list fnd_api.g_false or fnd_api.g_true is passed as input in this variable to determine whether to Initialize message list or not
305 * @paraminfo {@rep:required}
306 * @param p_commit fnd_api.g_false or fnd_api.g_true is passed as input in this variable to determine whether to commit or not
307 * @paraminfo {@rep:required}
308 * @param p_validation_level Validation level is passed as input in this variable
309 * @paraminfo {@rep:required}
310 * @param p_inventory_item_id Inventory Item id is passed as input in this variable
311 * @paraminfo {@rep:required}
312 * @param p_organization_id Organization id is passed as input in this variable
313 * @paraminfo {@rep:required}
314 * @param p_from_serial_number Start Serial Number is passed as input in this variable
315 * @paraminfo {@rep:required}
316 * @param p_to_serial_number End Serial Number is passed as input in this variable
317 * @paraminfo {@rep:required}
318 * @param p_initialization_date Initialization Date is passed as input in this variable
319 * @paraminfo {@rep:required}
320 * @param p_completion_date Unit completion date is passed as input in this variable
321 * @paraminfo {@rep:required}
322 * @param p_ship_date Unit Ship Date is passed as input in this variable
323 * @paraminfo {@rep:required}
324 * @param p_revision Inventory Item Revision code is passed as input in this variable
325 * @paraminfo {@rep:required}
326 * @param p_lot_number Lot Number is passed as input in this variable
327 * @paraminfo {@rep:required}
328 * @param p_current_locator_id Current Locator id is passed as input in this variable
329 * @paraminfo {@rep:required}
330 * @param p_subinventory_code Current Subinventry Code is passed as input in this variable
331 * @paraminfo {@rep:required}
332 * @param p_trx_src_id Transaction Source id is passed as input in this variable
333 * @paraminfo {@rep:required}
334 * @param p_unit_vendor_id Unit Supplier Code is passed as input in this variable
335 * @paraminfo {@rep:required}
336 * @param p_vendor_lot_number Supplier Lot Number is passed as input in this variable
337 * @paraminfo {@rep:required}
338 * @param p_vendor_serial_number Supplier Serial Number is passed as input in this variable
339 * @paraminfo {@rep:required}
340 * @param p_receipt_issue_type Transaction Type is passed as input in this variable
341 * @paraminfo {@rep:required}
342 * @param p_txn_src_id Transaction Source id is passed as input in this variable
343 * @paraminfo {@rep:required}
344 * @param p_txn_src_name Transaction source Name is passed as input in this variable
345 * @paraminfo {@rep:required}
346 * @param p_txn_src_type_id Transaction Source type id is passed as input in this variable
347 * @paraminfo {@rep:required}
348 * @param p_transaction_id Transaction id is passed as input in this variable
349 * @paraminfo {@rep:required}
350 * @param p_current_status Current Status of the Serial Number is passed as input in this variable
351 * @paraminfo {@rep:required}
352 * @param p_parent_item_id Component parent part Number is passed as input in this variable
353 * @paraminfo {@rep:required}
354 * @param p_parent_serial_number Component parent Serial Number is passed as input in this variable
355 * @paraminfo {@rep:required}
356 * @param p_cost_group_id Cost Group id is passed as input in this variable
357 * @paraminfo {@rep:required}
358 * @param p_transaction_action_id Transaction action id is passed as input in this variable
359 * @paraminfo {@rep:required}
360 * @param p_transaction_temp_id Transaction Temp id is passed as input in this variable
361 * @paraminfo {@rep:required}
362 * @param p_status_id Status id is passed as input in this variable
363 * @paraminfo {@rep:required}
364 * @param p_inspection_status Status after Inspection is passed as input in this variable
365 * @paraminfo {@rep:required}
366 * @param x_object_id Return Object id
367 * @paraminfo {@rep:required}
368 * @param x_return_status Return Status indiacation success or failure.
369 * @paraminfo {@rep:required}
370 * @param x_msg_count message count from the error stack in case of failure
371 * @paraminfo {@rep:required}
372 * @param x_msg_data x_msg_data Return the error message in case of failure
373 * @paraminfo {@rep:required}
374 * @param p_organization_type Organization type is passed as input in this variable
375 * @param p_owning_org_id Owning Organization id is passed as input in this variable
376 * @param p_owning_tp_type Owning Trading partner type is passed as input in this variable
377 * @param p_planning_org_id Planning Organization id is passed as input in this variable
378 * @param p_planning_tp_type Planning Trading partner type is passed as input in this variable
379 * @param p_rcv_serial_flag Flag specicying receiving serial or not is passed as input in this variable
380 * @rep:scope public
381 * @rep:lifecycle active
382 * @rep:displayname Insert Range of Serial Numbers
383 */
384 PROCEDURE insert_range_serial(
385 p_api_version IN NUMBER
386 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
387 , p_commit IN VARCHAR2 := fnd_api.g_false
388 , p_validation_level IN NUMBER := fnd_api.g_valid_level_full
389 , p_inventory_item_id IN NUMBER
390 , p_organization_id IN NUMBER
391 , p_from_serial_number IN VARCHAR2
392 , p_to_serial_number IN VARCHAR2
393 , p_initialization_date IN DATE
394 , p_completion_date IN DATE
395 , p_ship_date IN DATE
396 , p_revision IN VARCHAR2
397 , p_lot_number IN VARCHAR2
398 , p_current_locator_id IN NUMBER
399 , p_subinventory_code IN VARCHAR2
400 , p_trx_src_id IN NUMBER
401 , p_unit_vendor_id IN NUMBER
402 , p_vendor_lot_number IN VARCHAR2
403 , p_vendor_serial_number IN VARCHAR2
404 , p_receipt_issue_type IN NUMBER
405 , p_txn_src_id IN NUMBER
406 , p_txn_src_name IN VARCHAR2
407 , p_txn_src_type_id IN NUMBER
408 , p_transaction_id IN NUMBER
409 , p_current_status IN NUMBER
410 , p_parent_item_id IN NUMBER
411 , p_parent_serial_number IN VARCHAR2
412 , p_cost_group_id IN NUMBER
413 , p_transaction_action_id IN NUMBER
414 , p_transaction_temp_id IN NUMBER
415 , p_status_id IN NUMBER
416 , p_inspection_status IN NUMBER
417 , x_object_id OUT NOCOPY NUMBER
418 , x_return_status OUT NOCOPY VARCHAR2
419 , x_msg_count OUT NOCOPY NUMBER
420 , x_msg_data OUT NOCOPY VARCHAR2
421 , p_organization_type IN NUMBER DEFAULT NULL
422 , p_owning_org_id IN NUMBER DEFAULT NULL
423 , p_owning_tp_type IN NUMBER DEFAULT NULL
424 , p_planning_org_id IN NUMBER DEFAULT NULL
425 , p_planning_tp_type IN NUMBER DEFAULT NULL
426 , p_rcv_serial_flag IN VARCHAR2 DEFAULT NULL
427 );
428
429 -- 'Serial Tracking in WIP project. add wip_entity_id, operation_seq_num, intraooperation_step_type, line_mark_id
430 -- also as the input parameters.
431 /*#
432 * Use this procedure to update a given Serial Number
433 * @param p_api_version API version is passed as input in this variable
434 * @paraminfo {@rep:required}
435 * @param p_init_msg_list fnd_api.g_false or fnd_api.g_true is passed as input in this variable to determine whether to Initialize message list or not
436 * @paraminfo {@rep:required}
437 * @param p_commit fnd_api.g_false or fnd_api.g_true is passed as input in this variable to determine whether to commit or not
438 * @paraminfo {@rep:required}
439 * @param p_validation_level Validation level is passed as input in this variable
440 * @paraminfo {@rep:required}
441 * @param p_inventory_item_id Inventory Item id is passed as input in this variable
442 * @paraminfo {@rep:required}
443 * @param p_organization_id Organization id is passed as input in this variable
444 * @paraminfo {@rep:required}
445 * @param p_serial_number Serial Number is passed as input in this variable
446 * @paraminfo {@rep:required}
447 * @param p_initialization_date Initialization Date is passed as input in this variable
448 * @paraminfo {@rep:required}
449 * @param p_completion_date Unit completion date is passed as input in this variable
450 * @paraminfo {@rep:required}
451 * @param p_ship_date Unit Ship Date is passed as input in this variable
452 * @paraminfo {@rep:required}
453 * @param p_revision Inventory Item Revision code is passed as input in this variable
454 * @paraminfo {@rep:required}
455 * @param p_lot_number Lot Number is passed as input in this variable
456 * @paraminfo {@rep:required}
457 * @param p_current_locator_id Current Locator id is passed as input in this variable
458 * @paraminfo {@rep:required}
459 * @param p_subinventory_code Current Subinventry Code is passed as input in this variable
460 * @paraminfo {@rep:required}
461 * @param p_trx_src_id Transaction Source id is passed as input in this variable
462 * @paraminfo {@rep:required}
463 * @param p_unit_vendor_id Unit Supplier Code is passed as input in this variable
464 * @paraminfo {@rep:required}
465 * @param p_vendor_lot_number Supplier Lot Number is passed as input in this variable
466 * @paraminfo {@rep:required}
467 * @param p_vendor_serial_number Supplier Serial Number is passed as input in this variable
468 * @paraminfo {@rep:required}
469 * @param p_receipt_issue_type Transaction Type is passed as input in this variable
470 * @paraminfo {@rep:required}
471 * @param p_txn_src_id Transaction Source id is passed as input in this variable
472 * @paraminfo {@rep:required}
473 * @param p_txn_src_name Transaction source Name is passed as input in this variable
474 * @paraminfo {@rep:required}
475 * @param p_txn_src_type_id Transaction Source type id is passed as input in this variable
476 * @paraminfo {@rep:required}
477 * @param p_current_status Current Status of the Serial Number is passed as input in this variable
478 * @paraminfo {@rep:required}
479 * @param p_parent_item_id Component parent part Number is passed as input in this variable
480 * @paraminfo {@rep:required}
481 * @param p_parent_serial_number Component parent Serial Number is passed as input in this variable
482 * @paraminfo {@rep:required}
483 * @param p_serial_temp_id Transaction temp id is passed as input in this variable
484 * @paraminfo {@rep:required}
485 * @param p_last_status Last Status is passed as input in this variable
486 * @paraminfo {@rep:required}
487 * @param p_status_id Status id is passed as input in this variable
488 * @paraminfo {@rep:required}
489 * @param x_object_id Return Object id
490 * @paraminfo {@rep:required}
491 * @param x_return_status Return Status indiacation success or failure.
492 * @paraminfo {@rep:required}
493 * @param x_msg_count message count from the error stack in case of failure
494 * @paraminfo {@rep:required}
495 * @param x_msg_data x_msg_data Return the error message in case of failure
496 * @paraminfo {@rep:required}
497 * @param p_organization_type Organization type is passed as input in this variable
498 * @param p_owning_org_id Owning Organization id is passed as input in this variable
499 * @param p_owning_tp_type Owning Trading partner type is passed as input in this variable
500 * @param p_transaction_action_id Transaction action id is passed as input in this variable
501 * @param p_planning_org_id Planning Organization id is passed as input in this variable
502 * @param p_planning_tp_type Planning Trading partner type is passed as input in this variable
503 * @param p_wip_entity_id Wip entity id is passed as input in this variable
504 * @param p_operation_seq_num Wip operaion Sequence Number is passed as input in this variable
505 * @param p_intraoperation_step_type Code for Interopration step in mfg_lookups is passed as input in this variable
506 * @param p_line_mark_id Line identifier is passed as input in this variable
507 * @rep:scope public
508 * @rep:lifecycle active
509 * @rep:displayname Update Serial Number
510 */
511 PROCEDURE updateserial(
512 p_api_version IN NUMBER
513 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
514 , p_commit IN VARCHAR2 := fnd_api.g_false
515 , p_validation_level IN NUMBER := fnd_api.g_valid_level_full
516 , p_inventory_item_id IN NUMBER
517 , p_organization_id IN NUMBER
518 , p_serial_number IN VARCHAR2
519 , p_initialization_date IN DATE
520 , p_completion_date IN DATE
521 , p_ship_date IN DATE
522 , p_revision IN VARCHAR2
523 , p_lot_number IN VARCHAR2
524 , p_current_locator_id IN NUMBER
525 , p_subinventory_code IN VARCHAR2
526 , p_trx_src_id IN NUMBER
527 , p_unit_vendor_id IN NUMBER
528 , p_vendor_lot_number IN VARCHAR2
529 , p_vendor_serial_number IN VARCHAR2
530 , p_receipt_issue_type IN NUMBER
531 , p_txn_src_id IN NUMBER
532 , p_txn_src_name IN VARCHAR2
533 , p_txn_src_type_id IN NUMBER
534 , p_current_status IN NUMBER
535 , p_parent_item_id IN NUMBER
536 , p_parent_serial_number IN VARCHAR2
537 , p_serial_temp_id IN NUMBER
538 , p_last_status IN NUMBER
539 , p_status_id IN NUMBER
540 , x_object_id OUT NOCOPY NUMBER
541 , x_return_status OUT NOCOPY VARCHAR2
542 , x_msg_count OUT NOCOPY NUMBER
543 , x_msg_data OUT NOCOPY VARCHAR2
544 , p_organization_type IN NUMBER DEFAULT NULL
545 , p_owning_org_id IN NUMBER DEFAULT NULL
546 , p_owning_tp_type IN NUMBER DEFAULT NULL
547 , p_planning_org_id IN NUMBER DEFAULT NULL
548 , p_planning_tp_type IN NUMBER DEFAULT NULL
549 , p_transaction_action_id IN NUMBER DEFAULT NULL
550 , p_wip_entity_id IN NUMBER DEFAULT NULL
551 , p_operation_seq_num IN NUMBER DEFAULT NULL
552 , p_intraoperation_step_type IN NUMBER DEFAULT NULL
553 , p_line_mark_id IN NUMBER DEFAULT NULL
554 );
555
556
557 /*#
558 * Use this procedure to create Transactions of serialized units.
559 * @param p_api_version API version is passed as input in this variable
560 * @paraminfo {@rep:required}
561 * @param p_init_msg_list fnd_api.g_false or fnd_api.g_true is passed as input in this variable to determine whether to Initialize message list or not
562 * @paraminfo {@rep:required}
563 * @param p_commit fnd_api.g_false or fnd_api.g_true is passed as input in this variable to determine whether to commit or not
564 * @paraminfo {@rep:required}
565 * @param p_validation_level Validation level is passed as input in this variable
566 * @paraminfo {@rep:required}
567 * @param p_inventory_item_id Inventory Item id is passed as input in this variable
568 * @paraminfo {@rep:required}
569 * @param p_organization_id Organization id is passed as input in this variable
570 * @paraminfo {@rep:required}
571 * @param p_serial_number Serial Number is passed as input in this variable
572 * @paraminfo {@rep:required}
573 * @param p_current_locator_id Current Locator id is passed as input in this variable
574 * @paraminfo {@rep:required}
575 * @param p_subinventory_code Current Subinventry Code is passed as input in this variable
576 * @paraminfo {@rep:required}
577 * @param p_transaction_date Transaction date is passed as input in this variable
578 * @paraminfo {@rep:required}
579 * @param p_txn_src_id Transaction Source id is passed as input in this variable
580 * @paraminfo {@rep:required}
581 * @param p_txn_src_name Transaction source Name is passed as input in this variable
582 * @paraminfo {@rep:required}
583 * @param p_txn_src_type_id Transaction Source type id is passed as input in this variable
584 * @paraminfo {@rep:required}
585 * @param p_transaction_id Transaction id is passed as input in this variable
586 * @paraminfo {@rep:required}
587 * @param p_transaction_action_id Transaction action id is passed as input in this variable
588 * @paraminfo {@rep:required}
589 * @param p_transaction_temp_id Transaction Temp id is passed as input in this variable
590 * @paraminfo {@rep:required}
591 * @param p_receipt_issue_type Transaction Type is passed as input in this variable
592 * @paraminfo {@rep:required}
593 * @param p_customer_id Customer id is passed as input in this variable
594 * @paraminfo {@rep:required}
595 * @param p_ship_id Ship code is passed as input in this variable
596 * @paraminfo {@rep:required}
597 * @param p_status_id Status id is passed as input in this variable
598 * @paraminfo {@rep:required}
599 * @param x_return_status Return Status indiacation success or failure.
600 * @paraminfo {@rep:required}
601 * @param x_msg_count message count from the error stack in case of failure
602 * @paraminfo {@rep:required}
603 * @param x_msg_data x_msg_data Return the error message in case of failure
604 * @paraminfo {@rep:required}
605 * @rep:scope public
606 * @rep:lifecycle active
607 * @rep:displayname Insert Unit Transactions
608 */
609 PROCEDURE insertunittrx(
610 p_api_version IN NUMBER
611 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
612 , p_commit IN VARCHAR2 := fnd_api.g_false
613 , p_validation_level IN NUMBER := fnd_api.g_valid_level_full
614 , p_inventory_item_id IN NUMBER
615 , p_organization_id IN NUMBER
616 , p_serial_number IN VARCHAR2
617 , p_current_locator_id IN NUMBER
618 , p_subinventory_code IN VARCHAR2
619 , p_transaction_date IN DATE
620 , p_txn_src_id IN NUMBER
621 , p_txn_src_name IN VARCHAR2
622 , p_txn_src_type_id IN NUMBER
623 , p_transaction_id IN NUMBER
624 , p_transaction_action_id IN NUMBER
625 , p_transaction_temp_id IN NUMBER
626 , p_receipt_issue_type IN NUMBER
627 , p_customer_id IN NUMBER
628 , p_ship_id IN NUMBER
629 , p_status_id IN NUMBER
630 , x_return_status OUT NOCOPY VARCHAR2
631 , x_msg_count OUT NOCOPY NUMBER
632 , x_msg_data OUT NOCOPY VARCHAR2
633 );
634
635 --
636 -- Name: GENERATE_SERIALS (Concurrent Program )
637 --
638 -- Input parameters:
639 -- p_org_id Organization ID
640 -- p_item_id Item ID
641 -- p_qty Count of Serial Numbers
642 -- p_wip_id Wip Entity ID
643 -- p_rev Revision
644 -- p_lot Lot Number
645 --
646 -- Output parameters:
647 -- x_retcode
648 -- x_errbuf
649 --
650 -- Functions: This API generates a batch of Serial Numbers
651 -- in MTL_SERIAL_NUMBERS and sets their status as
652 -- 'DEFINED_BUT_NOT_USED'. Before inserting into the table
653 -- it ensures that there is no clash with existing Serial Numbers
654 -- as per the configured Serial-Number-Uniqueness attribute.
655 -- Note: This API works in an autonomous transaction.
656 -- Note: This API is called by the Serial-Generation concurrent program
657 --
658
659 /*#
660 * Use this procedure to generate a range of Serial Numbers.
661 * All these Serial Numbers will have the status 'DEFINED_BUT_NOT_USED'.
662 * It makes sure that there is no clash with the existing Serial Numbers
663 * according to the Serial-Number-Uniqueness attribute.
664 * It works in an autonomous transaction, and is called by
665 * the Serial-Generation concurrent program.
666 * @param x_retcode Return status indicating success or failure
667 * @paraminfo {@rep:required}
668 * @param x_errbuf Return the error message in case of failure
669 * @paraminfo {@rep:required}
670 * @param p_org_id Organization Id is passed as input in this variable
671 * @paraminfo {@rep:required}
672 * @param p_item_id Inventory Item id passed as input in this variable
673 * @paraminfo {@rep:required}
674 * @param p_qty Quantity passed as input in this variable
675 * @paraminfo {@rep:required}
676 * @param p_serial_code Serial contol code is passed as input in this variable
677 * @paraminfo {@rep:required}
678 * @param p_wip_id Wip entity id is passed as input in this variable
679 * @paraminfo {@rep:required}
680 * @param p_rev Revision is passed as input in this variable
681 * @paraminfo {@rep:required}
682 * @param p_lot Lot Number is passed as input in this variable
683 * @paraminfo {@rep:required}
684 * @param p_group_mark_id Group identifier is passed as input in this variable
685 * @param p_line_mark_id Line identifier is passed as input in this variable
686 * @rep:scope public
687 * @rep:lifecycle active
688 * @rep:displayname Generate Serial Numbers
689 */
690 PROCEDURE generate_serials(
691 x_retcode OUT NOCOPY VARCHAR2
692 , x_errbuf OUT NOCOPY VARCHAR2
693 , p_org_id IN NUMBER
694 , p_item_id IN NUMBER
695 , p_qty IN NUMBER
696 , p_serial_code IN VARCHAR2
697 , p_wip_id IN NUMBER
698 , p_rev IN NUMBER
699 , p_lot IN NUMBER
700 , p_group_mark_id IN NUMBER DEFAULT NULL
701 , p_line_mark_id IN NUMBER DEFAULT NULL
702 );
703
704 --
705 -- Name: GENERATE_SERIALS
706 --
707 -- Input parameters:
708 -- p_org_id Organization ID
709 -- p_item_id Item ID
710 -- p_qty Count of Serial Numbers
711 -- p_wip_id Wip Entity ID
712 -- p_rev Revision
713 -- p_lot Lot Number
714 --
715 -- Output parameters:
716 -- x_start_serial Starting Serial Number
717 -- x_end_serial Ending Serial Number
718 -- x_proc_msg Message from the Process-Manager
719 -- return_status 0 on Success, 1 on Error
720 --
721 -- Functions: This API generates a batch of Serial Numbers
722 -- in MTL_SERIAL_NUMBERS and sets their status as
723 -- 'DEFINED_BUT_NOT_USED'. Before inserting into the table
724 -- it ensures that there is no clash with existing Serial Numbers
725 -- as per the configured Serial-Number-Uniqueness attribute.
726 -- Note: This API works in an autonomous transaction
727 --
728 /*#
729 * Use this function to generate a range of Serial Numbers
730 * @param p_org_id Organization Id is passed as input in this variable
731 * @paraminfo {@rep:required}
732 * @param p_item_id Inventory Item id passed as input in this variable
733 * @paraminfo {@rep:required}
734 * @param p_qty Quantity passed as input in this variable
735 * @paraminfo {@rep:required}
736 * @param p_wip_id Wip entity id is passed as input in this variable
737 * @paraminfo {@rep:required}
738 * @param p_rev Revision is passed as input in this variable
739 * @paraminfo {@rep:required}
740 * @param p_lot Lot Number is passed as input in this variable
741 * @paraminfo {@rep:required}
742 * @param p_group_mark_id Group identifier is passed as input in this variable
743 * @param p_line_mark_id Line identifier is passed as input in this variable
744 * @param x_start_ser Return Start serial
745 * @paraminfo {@rep:required}
746 * @param x_end_ser Return End serial
747 * @paraminfo {@rep:required}
748 * @param x_proc_msg Return Message from the Process-Manager
749 * @paraminfo {@rep:required}
750 * @param p_skip_serial Serial number to be excluded is passed as input in this variable
751 * @paraminfo {@rep:required}
752 * @return status indicating success or failure
753 * @rep:scope public
754 * @rep:lifecycle active
755 * @rep:displayname Generate Serial Numbers
756 */
757 FUNCTION generate_serials(
758 p_org_id IN NUMBER
759 , p_item_id IN NUMBER
760 , p_qty IN NUMBER
761 , p_wip_id IN NUMBER
762 , p_rev IN VARCHAR2
763 , p_lot IN VARCHAR2
764 , p_group_mark_id IN NUMBER DEFAULT NULL
765 , p_line_mark_id IN NUMBER DEFAULT NULL
766 , x_start_ser OUT NOCOPY VARCHAR2
767 , x_end_ser OUT NOCOPY VARCHAR2
768 , x_proc_msg OUT NOCOPY VARCHAR2
769 , p_skip_serial IN NUMBER DEFAULT NULL
770 )
771 RETURN NUMBER;
772
773 --
774 -- Name: IS_SERIAL_UNIQUE
775 --
776 -- Input parameters:
777 -- p_org_id Organization ID
778 -- p_item_id Item ID
779 -- p_serial Serial Number
780 --
781 -- Output parameters:
782 -- x_proc_msg Message from the Process-Manager
783 -- return_status 0 on Success, 1 on Error
784 --
785 -- Functions: This API checks wheather a Serial Number
786 -- can be entered into MTL_SERIAL_NUMBER after considering the
787 -- SERIAL_NUMBER_TYPE of MTL_PARAMETERS. This attribute can
788 -- have the following values :
789 -- 1 - Unique serial numbers within inventory item
790 -- 2 - Unique serial numbers within organization.
791 -- 3 - Unique serial numbers across organizations.
792 --
793 --
794 /*#
795 * Use this function to check whether a Serial number is unique or not
796 * @param p_org_id Organization Id is passed as input in this variable
797 * @paraminfo {@rep:required}
798 * @param p_item_id Inventory Item id passed as input in this variable
799 * @paraminfo {@rep:required}
800 * @param p_serial Serial Number passed as input in this variable
801 * @paraminfo {@rep:required}
802 * @param x_proc_msg Return Message from the Process-Manager
803 * @paraminfo {@rep:required}
804 * @return Status indicating success or failure
805 * @rep:scope public
806 * @rep:lifecycle active
807 * @rep:displayname Is Serial Unique
808 */
809 FUNCTION is_serial_unique(p_org_id IN NUMBER, p_item_id IN NUMBER, p_serial IN VARCHAR2, x_proc_msg OUT NOCOPY VARCHAR2)
810 RETURN NUMBER;
811
812 --
813 -- Name: GET_SERIAL_DIFF
814 --
815 -- Input parameters:
816 -- p_fm_serial 'from' Serial Number
817 -- p_to_serial 'to' Serial Number
818 --
819 -- Output parameters:
820 -- return_status quantity between passed serial numbers
821 --
822 -- Functions: This API returns the numeric difference between the
823 -- fromSerNum and toSerNum by first seperating the numeric part
824 -- from the strings and getting its difference
825 -- * Note: - string-prefix part of both the numbers should match
826 -- * - numeric part lengths should match
827 -- * - difference should be greater than 0
828 --
829 --
830 /*#
831 * Use this function to get the quantity of units between two Serial Numbers.
832 * @param p_fm_serial From Serial Number is passed as input in this variable
833 * @paraminfo {@rep:required}
834 * @param p_to_serial To Serial Number is passed as input in this variable
835 * @paraminfo {@rep:required}
836 * @return The quantity between two Serial Numbers
837 * @rep:scope public
838 * @rep:lifecycle active
839 * @rep:displayname Get Serial Difference
840 */
841 FUNCTION get_serial_diff(p_fm_serial IN VARCHAR2, p_to_serial IN VARCHAR2)
842 RETURN NUMBER;
843
844 --
845 -- Name: VALIDATE_SERIALS
846 --
847 -- Input parameters:
848 -- p_org_id Organization ID
849 -- p_item_id Item ID
850 -- p_wip_id Wip Entity ID
851 -- p_rev Revision
852 -- p_lot Lot Number
853 -- p_locator_id Locator Id
854 -- p_subinventory_code SubInv Code
855 -- p_issue_receipt Issue/Receipt Flag
856 -- IN/OUT parameters:
857 -- p_qty Quantity/Count of Serial Numbers
858 -- x_end_ser End Serial Number
859 -- Output parameters:
860 -- x_proc_msg Error Message
861 --
862 -- Functions: This API Validate a batch of Serial Numbers
863 -- and if the serial number is new then insert the
864 -- serial number in MTL_SERIAL_NUMBERS and sets their status
865 -- appropriate to the transaction. Before inserting into the table
866 -- it ensures that there is no clash with existing Serial Numbers
867 -- as per the configured Serial-Number-Uniqueness attribute.
868 --
869 -- Bug 3194093 added two more parameters to validate_serials()
870 -- p_rcv_validate,p_rcv_shipment_line_id to support serial
871 -- validation for intransit receipt transactions
872 -- applicable for Inter-org,Internal sales order Intransit txns
873 -- Bug 3384652 Changing the param name p_rcv_shipment_line_id to
874 -- p_rcv_source_line_id.And the value passed to this is either
875 -- shipment_line_id or ram_line_id depending on the transaction
876 -- Source type and action. To support serial validation for RMA
877
878 -- Bug 7541512, added p_rcv_parent_txn_id to validate serial number of return to vendor transactions.
879
880 /*#
881 * Use this function to validate a batch of Serial Numbers
882 * and, if the serial numbers are new, to insert the
883 * serial number in MTL_SERIAL_NUMBERS and to set the status
884 * appropriate to the transaction. Before inserting into the table
885 * it ensures that there is no clash with existing Serial Numbers
886 * according to the configured Serial-Number-Uniqueness attribute.
887 * @param p_org_id Organization Id is passed as input in this variable
888 * @paraminfo {@rep:required}
889 * @param p_item_id Inventory Item id passed as input in this variable
890 * @paraminfo {@rep:required}
891 * @param p_qty Quantity passed as input in this variable and Return the valid quantity
892 * @paraminfo {@rep:required}
893 * @param p_rev Revision is passed as input in this variable
894 * @param p_lot Lot Number is passed as input in this variable
895 * @param p_start_ser Start Serial Number is passed as input in this variable
896 * @paraminfo {@rep:required}
897 * @param p_trx_src_id Transaction Source Type id is passed as input in this variable
898 * @param p_trx_action_id Transaction Action id is passed as input in this variable
899 * @param p_subinventory_code Subinventory code is passed as input in this variable
900 * @param p_locator_id Locator id is passed as input in this variable
901 * @param p_wip_entity_id Wip entity id is passed as input in this variable
902 * @param p_group_mark_id Group identifier is passed as input in this variable
903 * @param p_line_mark_id Line identifier is passed as input in this variable
904 * @param p_issue_receipt Issue or Receipt code is passed as input in this variable
905 * @param x_end_ser End Serial Number is passed as input in this variable and returns the valid End Serial Number
906 * @paraminfo {@rep:required}
907 * @param x_proc_msg Return Message from the Process-Manager
908 * @paraminfo {@rep:required}
909 * @param p_check_for_grp_mark_id The flag that determines whether to check for group mark id is passed as input in this variable
910 * @param p_rcv_validate The flag that determines whether the Serial Number should be validated or not is passed as input in this variable
911 * @param p_rcv_source_line_id Source Line id is passed as input in this variable
912 * @return Return status indicating success or failure
913 * @rep:scope public
914 * @rep:lifecycle active
915 * @rep:displayname Validate Serials
916 */
917
918 /* Bug 6898933 Added parameter p_transaction_type_id in below procedure */
919
920 FUNCTION validate_serials(
921 p_org_id IN NUMBER
922 , p_item_id IN NUMBER
923 , p_qty IN OUT NOCOPY NUMBER
924 , p_rev IN VARCHAR2 DEFAULT NULL
925 , p_lot IN VARCHAR2 DEFAULT NULL
926 , p_start_ser IN VARCHAR2
927 , p_trx_src_id IN NUMBER DEFAULT NULL
928 , p_trx_action_id IN NUMBER DEFAULT NULL
929 , p_subinventory_code IN VARCHAR2 DEFAULT NULL
930 , p_locator_id IN NUMBER DEFAULT NULL
931 , p_wip_entity_id IN NUMBER DEFAULT NULL
932 , p_group_mark_id IN NUMBER DEFAULT NULL
933 , p_line_mark_id IN NUMBER DEFAULT NULL
934 , p_issue_receipt IN VARCHAR2 DEFAULT NULL
935 , x_end_ser IN OUT NOCOPY VARCHAR2
936 , x_proc_msg OUT NOCOPY VARCHAR2
937 , p_check_for_grp_mark_id IN VARCHAR2 DEFAULT 'N'
938 , p_rcv_validate IN VARCHAR2 DEFAULT 'N'
939 , p_rcv_source_line_id IN NUMBER DEFAULT -1
940 , p_xfr_org_id IN NUMBER DEFAULT -1 -- Bug#4153297
941 , p_rcv_parent_txn_id IN NUMBER DEFAULT -1
942 , p_transaction_type_id IN NUMBER DEFAULT 0
943 , p_ship_header_id IN NUMBER DEFAULT -1 -- Bug 14778131
944 ) --Bug# 2656316
945 RETURN NUMBER;
946
947 /*#
948 * Use this function to get the next Serial Number using the current Serial
949 * Number and Increment Value.
950 * @param p_curr_serial Current Serial number is passed as input in this variable
951 * @param p_inc_value Increment value is passed as input in this variable
952 * @return Returns next Serial Number
953 * @rep:scope public
954 * @rep:lifecycle active
955 * @rep:displayname Increment Serial Number
956 */
957 FUNCTION increment_ser_num(p_curr_serial VARCHAR2, p_inc_value NUMBER)
958 RETURN VARCHAR2;
959
960 --Description
961 --Procedure for validating and updating serial attributes.
962 /*#
963 * Use this procedure to validate and update serial attributes.
964 * @param x_return_status Return status indicating success or failure
965 * @paraminfo {@rep:required}
966 * @param x_msg_count Return message count from the error stack in case of failure
967 * @paraminfo {@rep:required}
968 * @param x_msg_data Return the error message in case of failure
969 * @paraminfo {@rep:required}
970 * @param x_validation_status Return the validation status
971 * @paraminfo {@rep:required}
972 * @param p_serial_number Serial number is passed as input in this variable
973 * @paraminfo {@rep:required}
974 * @param p_organization_id Organization Id is passed as input in this variable
975 * @paraminfo {@rep:required}
976 * @param p_inventory_item_id Inventory Item id passed as input in this variable
977 * @paraminfo {@rep:required}
978 * @param p_serial_att_tbl Serial Attributes table is passed as input in this variable
979 * @paraminfo {@rep:required}
980 * @param p_validate_only TRUE is passed as input in this variable if only validation is required
981 * @rep:scope public
982 * @rep:lifecycle active
983 * @rep:displayname Validate and Update Serial Attributes
984 */
985 PROCEDURE validate_update_serial_att
986 (x_return_status OUT NOCOPY VARCHAR2,
987 x_msg_count OUT NOCOPY NUMBER,
988 x_msg_data OUT NOCOPY VARCHAR2,
989 x_validation_status OUT NOCOPY VARCHAR2,
990 p_serial_number IN VARCHAR2,
991 p_organization_id IN NUMBER,
992 p_inventory_item_id IN NUMBER,
993 p_serial_att_tbl IN
994 inv_lot_sel_attr.lot_sel_attributes_tbl_type,
995 p_validate_only IN BOOLEAN DEFAULT FALSE
996 );
997
998 FUNCTION SNGetMask(P_txn_act_id IN NUMBER,
999 P_txn_src_type_id IN NUMBER,
1000 P_serial_control IN NUMBER,
1001 x_to_status OUT NOCOPY NUMBER,
1002 x_dynamic_ok OUT NOCOPY NUMBER,
1003 P_receipt_issue_flag IN VARCHAR2,
1004 x_mask OUT NOCOPY VARCHAR2,
1005 x_errorcode OUT NOCOPY NUMBER)
1006 RETURN BOOLEAN;
1007
1008 PROCEDURE update_msn
1009 (x_return_status OUT NOCOPY VARCHAR2,
1010 x_msg_count OUT NOCOPY NUMBER,
1011 x_msg_data OUT NOCOPY VARCHAR2,
1012 p_trxdate IN DATE,
1013 p_transaction_temp_id IN NUMBER,
1014 p_rev IN VARCHAR2,
1015 p_lotnum IN VARCHAR2,
1016 p_orgid IN NUMBER,
1017 p_locid IN NUMBER, -- :lii,
1018 p_subinv IN VARCHAR2,
1019 p_trxsrctypid IN NUMBER,
1020 p_trxsrcid IN NUMBER,
1021 p_trx_act_id IN NUMBER,
1022 p_vendid IN NUMBER, -- :i_vendor_idi,
1023 p_venlot IN VARCHAR2,
1024 p_receipt_issue_type IN NUMBER,
1025 p_trxsname IN VARCHAR2,
1026 p_lstupdby IN NUMBER,
1027 p_parent_item_id IN NUMBER, -- :parent_item_i,
1028 p_parent_ser_num IN VARCHAR2, -- :parent_sn_i,
1029 p_ser_ctrl_code IN NUMBER,
1030 p_xfr_ser_ctrl_code IN NUMBER,
1031 p_trx_qty IN NUMBER,
1032 p_invitemid IN NUMBER,
1033 p_f_ser_num IN VARCHAR2,
1034 p_t_ser_num IN VARCHAR2,
1035 x_serial_updated OUT NOCOPY NUMBER
1036 );
1037
1038 FUNCTION getGroupId(
1039 p_trx_source_type_id IN number,
1040 p_trx_action_id IN number) RETURN NUMBER;
1041
1042 FUNCTION validate_status(
1043 p_trx_src_type_id IN NUMBER,
1044 p_trx_action_id IN NUMBER,
1045 p_isIssue IN BOOLEAN,
1046 p_ser_num_ctrl_code IN NUMBER,
1047 p_curr_status IN NUMBER,
1048 p_last_trx_src_type_id IN NUMBER,
1049 p_xfr_ser_num_ctrl_code IN NUMBER,
1050 p_isRestrictRcptSerial IN NUMBER
1051 ) return number;
1052
1053 FUNCTION valsn(
1054 p_trx_src_type_id IN NUMBER,
1055 p_trx_action_id IN NUMBER,
1056 p_revision IN VARCHAR2,
1057 p_curr_subinv_code IN VARCHAR2,
1058 p_locator_id IN NUMBER,
1059 p_item IN NUMBER,
1060 p_curr_org_id IN NUMBER,
1061 p_lot IN VARCHAR2,
1062 p_curr_ser_num IN VARCHAR2,
1063 p_ser_num_ctrl_code IN NUMBER,
1064 p_xfr_ser_num_ctrl_code IN NUMBER,
1065 p_trx_qty IN NUMBER,
1066 p_acct_prof_value IN VARCHAR2,
1067 p_mask IN VARCHAR2,
1068 p_db_current_status IN NUMBER,
1069 p_db_current_organization_id IN NUMBER,
1070 p_db_revision IN VARCHAR2,
1071 p_db_lot_number IN VARCHAR2,
1072 p_db_current_subinventory_code IN VARCHAR2,
1073 p_db_current_locator_id IN NUMBER,
1074 p_db_wip_ent_id_ind IN NUMBER,
1075 p_db_lst_txn_src_type_id IN NUMBER
1076 ) RETURN NUMBER DETERMINISTIC;
1077 --pragma restrict_references(valsn, WNDS, WNPS, RNDS, RNPS);
1078
1079 PROCEDURE insertRangeUnitTrx(
1080 p_api_version IN NUMBER,
1081 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1082 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1083 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1084 p_inventory_item_id IN NUMBER,
1085 p_organization_id IN NUMBER,
1086 p_fm_serial_number IN VARCHAR2,
1087 p_to_serial_number IN VARCHAR2,
1088 p_current_locator_id IN NUMBER,
1089 p_subinventory_code IN VARCHAR2,
1090 p_transaction_date IN DATE,
1091 p_txn_src_id IN NUMBER,
1092 p_txn_src_name IN VARCHAR2,
1093 p_txn_src_type_id IN NUMBER,
1094 p_transaction_id IN NUMBER,
1095 p_transaction_action_id IN NUMBER,
1096 p_transaction_temp_id IN NUMBER,
1097 p_receipt_issue_type IN NUMBER,
1098 p_customer_id IN NUMBER,
1099 p_ship_id IN NUMBER,
1100 p_status_id IN NUMBER,
1101 x_return_status OUT NOCOPY VARCHAR2,
1102 x_msg_count OUT NOCOPY NUMBER,
1103 x_msg_data OUT NOCOPY VARCHAR2);
1104
1105 --serial tagging
1106 PROCEDURE is_serial_controlled(
1107 p_inventory_item_id IN NUMBER,
1108 p_organization_id IN NUMBER,
1109 p_transfer_org_id IN NUMBER DEFAULT NULL,
1110 p_txn_type_id IN NUMBER DEFAULT NULL,
1111 p_txn_src_type_id IN NUMBER DEFAULT NULL,
1112 p_txn_action_id IN NUMBER DEFAULT NULL,
1113 p_serial_control IN NUMBER DEFAULT NULL,
1114 p_xfer_serial_control IN NUMBER DEFAULT NULL,
1115 x_serial_control OUT NOCOPY NUMBER,
1116 x_return_status OUT NOCOPY VARCHAR2);
1117
1118 --serial tagging
1119 FUNCTION is_serial_tagged(
1120 p_inventory_item_id IN NUMBER,
1121 p_organization_id IN NUMBER
1122 ) RETURN NUMBER;
1123
1124 FUNCTION is_serial_tagged(
1125 p_inventory_item_id IN NUMBER DEFAULT NULL,
1126 p_organization_id IN NUMBER DEFAULT NULL,
1127 p_template_id IN NUMBER
1128 ) RETURN NUMBER;
1129
1130 PROCEDURE copy_serial_tag_assignments(
1131 p_from_org_id IN NUMBER DEFAULT NULL,
1132 p_from_item_id IN NUMBER DEFAULT NULL,
1133 p_from_template_id IN NUMBER DEFAULT NULL,
1134 p_to_org_id IN NUMBER DEFAULT NULL,
1135 p_to_item_id IN NUMBER DEFAULT NULL,
1136 p_to_template_id IN NUMBER DEFAULT NULL,
1137 x_return_status OUT NOCOPY VARCHAR2);
1138
1139 PROCEDURE delete_serial_tag_assignments(
1140 p_inventory_item_id IN NUMBER,
1141 p_organization_id IN NUMBER,
1142 x_return_status OUT NOCOPY VARCHAR2);
1143
1144 PROCEDURE delete_serial_tag_assignments(
1145 p_inventory_item_id IN NUMBER DEFAULT NULL,
1146 p_organization_id IN NUMBER DEFAULT NULL,
1147 p_template_id IN NUMBER,
1148 x_return_status OUT NOCOPY VARCHAR2);
1149
1150 END inv_serial_number_pub;
[Home] [Help]