1 PACKAGE inv_rcv_integration_apis AUTHID CURRENT_USER AS
2 /* $Header: INVRCVIS.pls 120.0.12020000.2 2012/07/09 08:15:04 asugandh ship $*/
3
4 G_EXISTS_ONLY CONSTANT NUMBER := 1;
5 G_EXISTS_OR_CREATE CONSTANT NUMBER := 2;
6 G_EXISTS_OR_VALIDATE CONSTANT NUMBER := 3;
7
8 G_SHIP CONSTANT NUMBER := 1;
9 G_RECEIVE CONSTANT NUMBER := 2;
10 G_DELIVER CONSTANT NUMBER := 3;
11 G_TRANSFER CONSTANT NUMBER := 4;
12 G_CORRECT CONSTANT NUMBER := 5;
13 G_RETURN_TO_RCV CONSTANT NUMBER := 6;
14 G_RETURN_TO_VENDOR CONSTANT NUMBER := 7;
15 G_ACCEPT CONSTANT NUMBER := 8;
16 G_REJECT CONSTANT NUMBER := 8;
17
18 G_RET_STS_ERROR CONSTANT VARCHAR2(1) := fnd_api.g_ret_sts_error;
19 G_RET_STS_UNEXP_ERR CONSTANT VARCHAR2(1) := fnd_api.g_ret_sts_unexp_error;
20 G_RET_STS_SUCCESS CONSTANT VARCHAR2(1) := FND_API.g_ret_sts_success;
21 G_TRUE CONSTANT VARCHAR2(1) := fnd_api.g_true;
22 G_FALSE CONSTANT VARCHAR2(1) := fnd_api.g_false;
23
24 G_PROD_CODE CONSTANT VARCHAR2(5) := 'RCV';
25
26 G_YES CONSTANT VARCHAR2(1) := 'Y';
27 G_NO CONSTANT VARCHAR2(1) := 'N';
28
29 g_empty_char_tbl inv_lot_api_pub.char_tbl;
30 g_empty_num_tbl inv_lot_api_pub.number_tbl;
31 g_empty_date_tbl inv_lot_api_pub.date_tbl;
32
33 -- Bug 3446419
34 -- Added primary quantity for Lot/Serial validations in the record type
35 -- if uom_code, organization_id,item_id is not passed then we will
36 -- get info from RTI to convert quantity to primary_quantity
37 -- to do the validation
38
39 TYPE child_record_info IS RECORD
40 (orig_interface_trx_id NUMBER,
41 new_interface_trx_id NUMBER,
42 quantity NUMBER,
43 to_organization_id NUMBER default null,
44 item_id NUMBER default null,
45 uom_code varchar2(3) default null,
46 sec_uom_code VARCHAR2(3) DEFAULT NULL,
47 sec_qty NUMBER DEFAULT NULL,
48 lot_number VARCHAR(80) ); -- for 13972742
49
50 TYPE child_rec_tb_tp IS TABLE OF child_record_info
51 INDEX BY BINARY_INTEGER;
52
53 TYPE number_tb_type IS TABLE OF NUMBER
54 INDEX BY BINARY_INTEGER;
55
56 TYPE mol_in_rec IS RECORD
57 ( prim_qty NUMBER,
58 line_id NUMBER,
59 sec_qty NUMBER,
60 wdd_id NUMBER,
61 reservation_id NUMBER
62 );
63
64 TYPE mo_in_tb_tp IS TABLE OF mol_in_rec
65 INDEX BY BINARY_INTEGER;
66
67 procedure insert_wlpni
68 (p_api_version IN NUMBER
69 , p_init_msg_lst IN VARCHAR2 DEFAULT g_false
70 , x_return_status OUT NOCOPY VARCHAR2
71 , x_msg_count OUT NOCOPY NUMBER
72 , x_msg_data OUT NOCOPY VARCHAR2
73 , p_ORGANIZATION_ID IN NUMBER
74 , p_LPN_ID IN NUMBER
75 , p_license_plate_number IN VARCHAR2
76 , p_LPN_GROUP_ID IN NUMBER
77 , p_PARENT_LPN_ID IN NUMBER DEFAULT NULL
78 , p_PARENT_LICENSE_PLATE_NUMBER IN VARCHAR2 DEFAULT NULL
79 , p_REQUEST_ID IN NUMBER DEFAULT NULL
80 , p_INVENTORY_ITEM_ID IN NUMBER DEFAULT NULL
81 , p_REVISION IN VARCHAR2 DEFAULT NULL
82 , p_LOT_NUMBER IN VARCHAR2 DEFAULT NULL
83 , p_SERIAL_NUMBER IN VARCHAR2 DEFAULT NULL
84 , p_SUBINVENTORY_CODE IN VARCHAR2 DEFAULT NULL
85 , p_LOCATOR_ID IN NUMBER DEFAULT NULL
86 , p_GROSS_WEIGHT_UOM_CODE IN VARCHAR2 DEFAULT NULL
87 , p_GROSS_WEIGHT IN NUMBER DEFAULT NULL
88 , p_CONTENT_VOLUME_UOM_CODE IN VARCHAR2 DEFAULT NULL
89 , p_CONTENT_VOLUME IN NUMBER DEFAULT NULL
90 , p_TARE_WEIGHT_UOM_CODE IN VARCHAR2 DEFAULT NULL
91 , p_TARE_WEIGHT IN NUMBER DEFAULT NULL
92 , p_STATUS_ID IN NUMBER DEFAULT NULL
93 , p_SEALED_STATUS IN NUMBER DEFAULT NULL
94 , p_ATTRIBUTE_CATEGORY IN VARCHAR2 DEFAULT NULL
95 , p_ATTRIBUTE1 IN VARCHAR2 DEFAULT NULL
96 , p_ATTRIBUTE2 IN VARCHAR2 DEFAULT NULL
97 , p_ATTRIBUTE3 IN VARCHAR2 DEFAULT NULL
98 , p_ATTRIBUTE4 IN VARCHAR2 DEFAULT NULL
99 , p_ATTRIBUTE5 IN VARCHAR2 DEFAULT NULL
100 , p_ATTRIBUTE6 IN VARCHAR2 DEFAULT NULL
101 , p_ATTRIBUTE7 IN VARCHAR2 DEFAULT NULL
102 , p_ATTRIBUTE8 IN VARCHAR2 DEFAULT NULL
103 , p_ATTRIBUTE9 IN VARCHAR2 DEFAULT NULL
104 , p_ATTRIBUTE10 IN VARCHAR2 DEFAULT NULL
105 , p_ATTRIBUTE11 IN VARCHAR2 DEFAULT NULL
106 , p_ATTRIBUTE12 IN VARCHAR2 DEFAULT NULL
107 , p_ATTRIBUTE13 IN VARCHAR2 DEFAULT NULL
108 , p_ATTRIBUTE14 IN VARCHAR2 DEFAULT NULL
109 , p_ATTRIBUTE15 IN VARCHAR2 DEFAULT NULL
110 , p_COST_GROUP_ID IN NUMBER DEFAULT NULL
111 , p_LPN_CONTEXT IN NUMBER DEFAULT NULL
112 , p_LPN_REUSABILITY IN NUMBER DEFAULT NULL
113 , p_OUTERMOST_LPN_ID IN NUMBER DEFAULT NULL
114 , p_outermost_lpn IN VARCHAR2 DEFAULT NULL
115 , p_HOMOGENEOUS_CONTAINER IN NUMBER DEFAULT NULL
116 , p_SOURCE_TYPE_ID IN NUMBER DEFAULT NULL
117 , p_SOURCE_HEADER_ID IN NUMBER DEFAULT NULL
118 , p_SOURCE_LINE_ID IN NUMBER DEFAULT NULL
119 , p_SOURCE_LINE_DETAIL_ID IN NUMBER DEFAULT NULL
120 , p_SOURCE_NAME IN VARCHAR2 DEFAULT NULL
121 );
122
123 /*----------------------------------------------------------------------------
124 * PROCEDURE: insert_mtli
125 * Description:
126 * This procedure inserts a record into MTL_TRANSACTION_LOTS_INTERFACE
127 * If there already exists a record with the transaction_interface_id
128 * and lot_number combination THEN
129 * Update transaction_quantity and primary_quantity
130 * Else
131 * Insert a new record into MTL_TRANSACTION_LOTS_INTERFACE
132 *
133 * @param p_api_version - Version of the API
134 * @param p_init_msg_lst - Flag to initialize message list
135 * @param x_return_status
136 * Return status indicating Success (S), Error (E), Unexpected Error (U)
137 * @param x_msg_count
138 * Number of messages in message list
139 * @param x_msg_data
140 * Stacked messages text
141 * @param p_transaction_interface_id - MTLI.Interface Transaction ID
142 * @param p_lot_number - Lot Number
143 * @param p_transaction_quantity - Transaction Quantity for the lot
144 * @param p_primary_quantity - Primary Quantity for the lot
145 * @param p_organization_id - Organization ID
146 * @param p_inventory_item_id - Inventory Item ID
147 * @param p_expiration_date - Lot Expiration Date
148 * @param p_status_id - Material Status for the lot
149 * @param x_serial_transaction_temp_id
150 * - Serial Transaction Temp Id (for lot and serial controlled item)
151 * @param p_product_transaction_id - Product Transaction Id. This parameter
152 * is stamped with the transaction identifier with
153 * @param p_product_code - Code of the product creating this record
154 * @param p_att_exist - Flag to indicate if attributes exist
155 * @param p_update_mln - Flag to update MLN with attributes
156 * @param named attributes - Named attributes
157 * @param C Attributes - Character atributes (1 - 20)
158 * @param D Attributes - Date atributes (1 - 10)
159 * @param N Attributes - Number atributes (1 - 10)
160 * @param p_attribute_cateogry - Attribute Category
161 * @param Attribute1-15 - INV Lot Attributes
162 *
163 * @ return: NONE
164 *---------------------------------------------------------------------------*/
165
166 PROCEDURE insert_mtli (
167 p_api_version IN NUMBER
168 , p_init_msg_lst IN VARCHAR2 DEFAULT G_FALSE
169 , x_return_status OUT NOCOPY VARCHAR2
170 , x_msg_count OUT NOCOPY NUMBER
171 , x_msg_data OUT NOCOPY VARCHAR2
172 , p_transaction_interface_id IN OUT NOCOPY NUMBER
173 , p_lot_number IN VARCHAR2
174 , p_transaction_quantity IN NUMBER
175 , p_primary_quantity IN NUMBER
176 , p_organization_id IN NUMBER
177 , p_inventory_item_id IN NUMBER
178 , p_expiration_date IN DATE
179 , p_status_id IN NUMBER
180 , x_serial_transaction_temp_id OUT NOCOPY NUMBER
181 , p_product_transaction_id IN OUT NOCOPY NUMBER
182 , p_product_code IN VARCHAR2 DEFAULT G_PROD_CODE
183 , p_att_exist IN VARCHAR2 DEFAULT G_YES
184 , p_update_mln IN VARCHAR2 DEFAULT G_NO
185 , p_description IN VARCHAR2 DEFAULT NULL
186 , p_vendor_name IN VARCHAR2 DEFAULT NULL
187 , p_supplier_lot_number IN VARCHAR2 DEFAULT NULL
188 , p_origination_date IN DATE DEFAULT NULL
189 , p_date_code IN VARCHAR2 DEFAULT NULL
190 , p_grade_code IN VARCHAR2 DEFAULT NULL
191 , p_change_date IN DATE DEFAULT NULL
192 , p_maturity_date IN DATE DEFAULT NULL
193 , p_retest_date IN DATE DEFAULT NULL
194 , p_age IN NUMBER DEFAULT NULL
195 , p_item_size IN NUMBER DEFAULT NULL
196 , p_color IN VARCHAR2 DEFAULT NULL
197 , p_volume IN NUMBER DEFAULT NULL
198 , p_volume_uom IN VARCHAR2 DEFAULT NULL
199 , p_place_of_origin IN VARCHAR2 DEFAULT NULL
200 , p_best_by_date IN DATE DEFAULT NULL
201 , p_length IN NUMBER DEFAULT NULL
202 , p_length_uom IN VARCHAR2 DEFAULT NULL
203 , p_recycled_content IN NUMBER DEFAULT NULL
204 , p_thickness IN NUMBER DEFAULT NULL
205 , p_thickness_uom IN VARCHAR2 DEFAULT NULL
206 , p_width IN NUMBER DEFAULT NULL
207 , p_width_uom IN VARCHAR2 DEFAULT NULL
208 , p_curl_wrinkle_fold IN VARCHAR2 DEFAULT NULL
209 , p_vendor_id IN NUMBER DEFAULT NULL
210 , p_territory_code IN VARCHAR2 DEFAULT NULL
211 , p_lot_attribute_category IN VARCHAR2 DEFAULT NULL
212 , p_c_attribute1 IN VARCHAR2 DEFAULT NULL
213 , p_c_attribute2 IN VARCHAR2 DEFAULT NULL
214 , p_c_attribute3 IN VARCHAR2 DEFAULT NULL
215 , p_c_attribute4 IN VARCHAR2 DEFAULT NULL
216 , p_c_attribute5 IN VARCHAR2 DEFAULT NULL
217 , p_c_attribute6 IN VARCHAR2 DEFAULT NULL
218 , p_c_attribute7 IN VARCHAR2 DEFAULT NULL
219 , p_c_attribute8 IN VARCHAR2 DEFAULT NULL
220 , p_c_attribute9 IN VARCHAR2 DEFAULT NULL
221 , p_c_attribute10 IN VARCHAR2 DEFAULT NULL
222 , p_c_attribute11 IN VARCHAR2 DEFAULT NULL
223 , p_c_attribute12 IN VARCHAR2 DEFAULT NULL
224 , p_c_attribute13 IN VARCHAR2 DEFAULT NULL
225 , p_c_attribute14 IN VARCHAR2 DEFAULT NULL
226 , p_c_attribute15 IN VARCHAR2 DEFAULT NULL
227 , p_c_attribute16 IN VARCHAR2 DEFAULT NULL
228 , p_c_attribute17 IN VARCHAR2 DEFAULT NULL
229 , p_c_attribute18 IN VARCHAR2 DEFAULT NULL
230 , p_c_attribute19 IN VARCHAR2 DEFAULT NULL
231 , p_c_attribute20 IN VARCHAR2 DEFAULT NULL
232 , p_d_attribute1 IN DATE DEFAULT NULL
233 , p_d_attribute2 IN DATE DEFAULT NULL
234 , p_d_attribute3 IN DATE DEFAULT NULL
235 , p_d_attribute4 IN DATE DEFAULT NULL
236 , p_d_attribute5 IN DATE DEFAULT NULL
237 , p_d_attribute6 IN DATE DEFAULT NULL
238 , p_d_attribute7 IN DATE DEFAULT NULL
239 , p_d_attribute8 IN DATE DEFAULT NULL
240 , p_d_attribute9 IN DATE DEFAULT NULL
241 , p_d_attribute10 IN DATE DEFAULT NULL
242 , p_n_attribute1 IN NUMBER DEFAULT NULL
243 , p_n_attribute2 IN NUMBER DEFAULT NULL
244 , p_n_attribute3 IN NUMBER DEFAULT NULL
245 , p_n_attribute4 IN NUMBER DEFAULT NULL
246 , p_n_attribute5 IN NUMBER DEFAULT NULL
247 , p_n_attribute6 IN NUMBER DEFAULT NULL
248 , p_n_attribute7 IN NUMBER DEFAULT NULL
249 , p_n_attribute8 IN NUMBER DEFAULT NULL
250 , p_n_attribute9 IN NUMBER DEFAULT NULL
251 , p_n_attribute10 IN NUMBER DEFAULT NULL
252 , p_attribute_category IN VARCHAR2 DEFAULT NULL
253 , p_attribute1 IN VARCHAR2 DEFAULT NULL
254 , p_attribute2 IN VARCHAR2 DEFAULT NULL
255 , p_attribute3 IN VARCHAR2 DEFAULT NULL
256 , p_attribute4 IN VARCHAR2 DEFAULT NULL
257 , p_attribute5 IN VARCHAR2 DEFAULT NULL
258 , p_attribute6 IN VARCHAR2 DEFAULT NULL
259 , p_attribute7 IN VARCHAR2 DEFAULT NULL
260 , p_attribute8 IN VARCHAR2 DEFAULT NULL
261 , p_attribute9 IN VARCHAR2 DEFAULT NULL
262 , p_attribute10 IN VARCHAR2 DEFAULT NULL
263 , p_attribute11 IN VARCHAR2 DEFAULT NULL
264 , p_attribute12 IN VARCHAR2 DEFAULT NULL
265 , p_attribute13 IN VARCHAR2 DEFAULT NULL
266 , p_attribute14 IN VARCHAR2 DEFAULT NULL
267 , p_attribute15 IN VARCHAR2 DEFAULT NULL
268 , p_from_org_id IN NUMBER DEFAULT NULL
269 , p_secondary_quantity IN NUMBER DEFAULT NULL--OPM Convergence
270 , p_origination_type IN NUMBER DEFAULT NULL--OPM Convergence
271 , p_expiration_action_code IN VARCHAR2 DEFAULT NULL--OPM Convergence
272 , p_expiration_action_date IN DATE DEFAULT NULL-- OPM Convergence
273 , p_hold_date IN DATE DEFAULT NULL--OPM Convergence
274 , p_parent_lot_number IN VARCHAR2 DEFAULT NULL--OPM Convergence
275 , p_reasond_id IN NUMBER DEFAULT NULL--OPM convergence
276 );
277
278 /*----------------------------------------------------------------------------
279 * PROCEDURE: insert_msni
280 * Description:
281 * This procedure inserts a record into MTL_SERIAL_NUMBERS_INTERFACE
282 * Generate transaction_interface_id if the parameter is NULL
283 * Generate product_transaction_id if the parameter is NULL
284 * The insert logic is based on the parameter p_att_exist.
285 * If p_att_exist is "N" Then (attributes are not available in table)
286 * Read the input parameters (including attributes) into a PL/SQL table
287 * Insert one record into MSNI with the from and to serial numbers passed
288 * Else
289 * Loop through each serial number between the from and to serial number
290 * Fetch the attributes into one row of the PL/SQL table and
291 * For each row in the PL/SQL table, insert one MSNI record
292 * End If
293 *
294 * @param p_api_version - Version of the API
295 * @param p_init_msg_lst - Flag to initialize message list
296 * @param x_return_status
297 * Return status indicating Success (S), Error (E), Unexpected Error (U)
298 * @param x_msg_count
299 * Number of messages in message list
300 * @param x_msg_data
301 * Stacked messages text
302 * @param p_transaction_interface_id - MTLI.Interface Transaction ID
303 * @param p_fm_serial_number - From Serial Number
304 * @param p_to_serial_number - To Serial Number
305 * @param p_organization_id - Organization ID
306 * @param p_inventory_item_id - Inventory Item ID
307 * @param p_status_id - Material Status for the lot
308 * @param p_product_transaction_id - Product Transaction Id. This parameter
309 * is stamped with the transaction identifier with
310 * @param p_product_code - Code of the product creating this record
311 * @param p_att_exist - Flag to indicate if attributes exist
312 * @param p_update_msn - Flag to update MSN with attributes
313 * @param named attributes - Named attributes
314 * @param C Attributes - Character atributes (1 - 20)
315 * @param D Attributes - Date atributes (1 - 10)
316 * @param N Attributes - Number atributes (1 - 10)
317 * @param p_attribute_cateogry - Attribute Category
318 * @param Attribute1-15 - INV Lot Attributes
319 *
320 * @ return: NONE
321 *---------------------------------------------------------------------------*/
322
323 PROCEDURE insert_msni (
324 p_api_version IN NUMBER
325 , p_init_msg_lst IN VARCHAR2 DEFAULT G_FALSE
326 , x_return_status OUT NOCOPY VARCHAR2
327 , x_msg_count OUT NOCOPY NUMBER
328 , x_msg_data OUT NOCOPY VARCHAR2
329 , p_transaction_interface_id IN OUT NOCOPY NUMBER
330 , p_fm_serial_number IN VARCHAR2
331 , p_to_serial_number IN VARCHAR2
332 , p_organization_id IN NUMBER
333 , p_inventory_item_id IN NUMBER
334 , p_status_id IN NUMBER
335 , p_product_transaction_id IN OUT NOCOPY NUMBER
336 , p_product_code IN VARCHAR2
337 , p_att_exist IN VARCHAR2 DEFAULT G_YES
338 , p_update_msn IN VARCHAR2 DEFAULT G_NO
339 , p_vendor_serial_number IN VARCHAR2 DEFAULT NULL
340 , p_vendor_lot_number IN VARCHAR2 DEFAULT NULL
341 , p_parent_serial_number IN VARCHAR2 DEFAULT NULL
342 , p_origination_date IN DATE DEFAULT NULL
343 , p_territory_code IN VARCHAR2 DEFAULT NULL
344 , p_time_since_new IN NUMBER DEFAULT NULL
345 , p_cycles_since_new IN NUMBER DEFAULT NULL
346 , p_time_since_overhaul IN NUMBER DEFAULT NULL
347 , p_cycles_since_overhaul IN NUMBER DEFAULT NULL
348 , p_time_since_repair IN NUMBER DEFAULT NULL
349 , p_cycles_since_repair IN NUMBER DEFAULT NULL
350 , p_time_since_visit IN NUMBER DEFAULT NULL
351 , p_cycles_since_visit IN NUMBER DEFAULT NULL
352 , p_time_since_mark IN NUMBER DEFAULT NULL
353 , p_cycles_since_mark IN NUMBER DEFAULT NULL
354 , p_number_of_repairs IN NUMBER DEFAULT NULL
355 , p_serial_attribute_category IN VARCHAR2 DEFAULT NULL
356 , p_c_attribute1 IN VARCHAR2 DEFAULT NULL
357 , p_c_attribute2 IN VARCHAR2 DEFAULT NULL
358 , p_c_attribute3 IN VARCHAR2 DEFAULT NULL
359 , p_c_attribute4 IN VARCHAR2 DEFAULT NULL
360 , p_c_attribute5 IN VARCHAR2 DEFAULT NULL
361 , p_c_attribute6 IN VARCHAR2 DEFAULT NULL
362 , p_c_attribute7 IN VARCHAR2 DEFAULT NULL
363 , p_c_attribute8 IN VARCHAR2 DEFAULT NULL
364 , p_c_attribute9 IN VARCHAR2 DEFAULT NULL
365 , p_c_attribute10 IN VARCHAR2 DEFAULT NULL
366 , p_c_attribute11 IN VARCHAR2 DEFAULT NULL
367 , p_c_attribute12 IN VARCHAR2 DEFAULT NULL
368 , p_c_attribute13 IN VARCHAR2 DEFAULT NULL
369 , p_c_attribute14 IN VARCHAR2 DEFAULT NULL
370 , p_c_attribute15 IN VARCHAR2 DEFAULT NULL
371 , p_c_attribute16 IN VARCHAR2 DEFAULT NULL
372 , p_c_attribute17 IN VARCHAR2 DEFAULT NULL
373 , p_c_attribute18 IN VARCHAR2 DEFAULT NULL
374 , p_c_attribute19 IN VARCHAR2 DEFAULT NULL
375 , p_c_attribute20 IN VARCHAR2 DEFAULT NULL
376 , p_d_attribute1 IN DATE DEFAULT NULL
377 , p_d_attribute2 IN DATE DEFAULT NULL
378 , p_d_attribute3 IN DATE DEFAULT NULL
379 , p_d_attribute4 IN DATE DEFAULT NULL
380 , p_d_attribute5 IN DATE DEFAULT NULL
381 , p_d_attribute6 IN DATE DEFAULT NULL
382 , p_d_attribute7 IN DATE DEFAULT NULL
383 , p_d_attribute8 IN DATE DEFAULT NULL
384 , p_d_attribute9 IN DATE DEFAULT NULL
385 , p_d_attribute10 IN DATE DEFAULT NULL
386 , p_n_attribute1 IN NUMBER DEFAULT NULL
387 , p_n_attribute2 IN NUMBER DEFAULT NULL
388 , p_n_attribute3 IN NUMBER DEFAULT NULL
389 , p_n_attribute4 IN NUMBER DEFAULT NULL
390 , p_n_attribute5 IN NUMBER DEFAULT NULL
391 , p_n_attribute6 IN NUMBER DEFAULT NULL
392 , p_n_attribute7 IN NUMBER DEFAULT NULL
393 , p_n_attribute8 IN NUMBER DEFAULT NULL
394 , p_n_attribute9 IN NUMBER DEFAULT NULL
395 , p_n_attribute10 IN NUMBER DEFAULT NULL
396 , p_attribute_category IN VARCHAR2 DEFAULT NULL
397 , p_attribute1 IN VARCHAR2 DEFAULT NULL
398 , p_attribute2 IN VARCHAR2 DEFAULT NULL
399 , p_attribute3 IN VARCHAR2 DEFAULT NULL
400 , p_attribute4 IN VARCHAR2 DEFAULT NULL
401 , p_attribute5 IN VARCHAR2 DEFAULT NULL
402 , p_attribute6 IN VARCHAR2 DEFAULT NULL
403 , p_attribute7 IN VARCHAR2 DEFAULT NULL
404 , p_attribute8 IN VARCHAR2 DEFAULT NULL
405 , p_attribute9 IN VARCHAR2 DEFAULT NULL
406 , p_attribute10 IN VARCHAR2 DEFAULT NULL
407 , p_attribute11 IN VARCHAR2 DEFAULT NULL
408 , p_attribute12 IN VARCHAR2 DEFAULT NULL
409 , p_attribute13 IN VARCHAR2 DEFAULT NULL
410 , p_attribute14 IN VARCHAR2 DEFAULT NULL
411 , p_attribute15 IN VARCHAR2 DEFAULT NULL
412 );
413
414 function validate_lot_number
415 (p_api_version IN NUMBER
416 , p_init_msg_lst IN VARCHAR2 DEFAULT g_false
417 , x_return_status OUT NOCOPY VARCHAR2
418 , x_msg_count OUT NOCOPY NUMBER
419 , x_msg_data OUT NOCOPY VARCHAR2
420 , x_is_new_lot OUT NOCOPY VARCHAR2
421 , p_validation_mode IN NUMBER DEFAULT G_EXISTS_ONLY
422 , p_org_id IN NUMBER
423 , p_inventory_item_id IN NUMBER
424 , p_lot_number IN VARCHAR2
425 , p_expiration_date IN DATE DEFAULT NULL
426 , p_txn_type IN NUMBER DEFAULT G_SHIP
427 , p_disable_flag IN NUMBER DEFAULT NULL
428 , p_attribute_category IN VARCHAR2 DEFAULT NULL
429 , p_lot_attribute_category IN VARCHAR2 DEFAULT NULL
430 , p_attributes_tbl IN inv_lot_api_pub.char_tbl DEFAULT g_empty_char_tbl
431 , p_c_attributes_tbl IN inv_lot_api_pub.char_tbl DEFAULT g_empty_char_tbl
432 , p_n_attributes_tbl IN inv_lot_api_pub.number_tbl DEFAULT g_empty_num_tbl
433 , p_d_attributes_tbl IN inv_lot_api_pub.date_tbl DEFAULT g_empty_date_tbl
434 , p_grade_code IN VARCHAR2 DEFAULT NULL
435 , p_origination_date IN DATE DEFAULT NULL
436 , p_date_code IN VARCHAR2 DEFAULT NULL
437 , p_status_id IN NUMBER DEFAULT NULL
438 , p_change_date IN DATE DEFAULT NULL
439 , p_age IN NUMBER DEFAULT NULL
440 , p_retest_date IN DATE DEFAULT NULL
441 , p_maturity_date IN DATE DEFAULT NULL
442 , p_item_size IN NUMBER DEFAULT NULL
443 , p_color IN VARCHAR2 DEFAULT NULL
444 , p_volume IN NUMBER DEFAULT NULL
445 , p_volume_uom IN VARCHAR2 DEFAULT NULL
446 , p_place_of_origin IN VARCHAR2 DEFAULT NULL
447 , p_best_by_date IN DATE DEFAULT NULL
448 , p_length IN NUMBER DEFAULT NULL
449 , p_length_uom IN VARCHAR2 DEFAULT NULL
450 , p_recycled_content IN NUMBER DEFAULT NULL
451 , p_thickness IN NUMBER DEFAULT NULL
452 , p_thickness_uom IN VARCHAR2 DEFAULT NULL
453 , p_width IN NUMBER DEFAULT NULL
454 , p_width_uom IN VARCHAR2 DEFAULT NULL
455 , p_territory_code IN VARCHAR2 DEFAULT NULL
456 , p_supplier_lot_number IN VARCHAR2 DEFAULT NULL
457 , p_vendor_name IN VARCHAR2 DEFAULT NULL
458 ) return boolean;
459
460 function validate_serial_range
461 (p_api_version IN NUMBER
462 , p_init_msg_lst IN VARCHAR2 DEFAULT g_false
463 , x_return_status OUT NOCOPY VARCHAR2
464 , x_msg_count OUT NOCOPY NUMBER
465 , x_msg_data OUT NOCOPY VARCHAR2
466 , p_validation_mode IN NUMBER DEFAULT G_EXISTS_ONLY
467 , p_org_id IN NUMBER
468 , p_inventory_item_id IN NUMBER
469 , p_quantity IN NUMBER
470 , p_revision IN VARCHAR2
471 , p_lot_number IN VARCHAR2
472 , p_fm_serial_number IN VARCHAR2
473 , p_to_serial_number IN OUT NOCOPY VARCHAR2
474 , p_txn_type IN NUMBER DEFAULT G_SHIP
475 ) return boolean;
476
477 function validate_lot_serial_info
478 (p_api_version IN NUMBER
479 , p_init_msg_lst IN VARCHAR2 DEFAULT g_false
480 , x_return_status OUT NOCOPY VARCHAR2
481 , x_msg_count OUT NOCOPY NUMBER
482 , x_msg_data OUT NOCOPY VARCHAR2
483 , p_validation_mode IN NUMBER DEFAULT G_EXISTS_OR_CREATE
484 , p_rti_id IN NUMBER
485 ) return boolean;
486
487 function generate_lot_number
488 (p_api_version IN NUMBER
489 , p_init_msg_lst IN VARCHAR2 DEFAULT g_false
490 , p_commit IN VARCHAR2 DEFAULT g_false
491 , x_return_status OUT NOCOPY VARCHAR2
492 , x_msg_count OUT NOCOPY NUMBER
493 , x_msg_data OUT NOCOPY VARCHAR2
494 , p_org_id IN NUMBER
495 , p_inventory_item_id IN NUMBER
496 ) return VARCHAR2;
497
498 procedure generate_serial_numbers
499 (p_api_version IN NUMBER
500 , p_init_msg_lst IN VARCHAR2 DEFAULT g_false
501 , p_commit IN VARCHAR2 DEFAULT g_false
502 , x_return_status OUT NOCOPY VARCHAR2
503 , x_msg_count OUT NOCOPY NUMBER
504 , x_msg_data OUT NOCOPY VARCHAR2
505 , p_org_id IN NUMBER
506 , p_inventory_item_id IN NUMBER
507 , p_quantity IN NUMBER
508 , p_revision IN VARCHAR2
509 , p_lot_number IN VARCHAR2
510 , x_start_serial OUT NOCOPY VARCHAR2
511 , x_end_serial OUT NOCOPY VARCHAR2
512 );
513
514 function validate_lpn
515 (p_api_version IN NUMBER
516 , p_init_msg_lst IN VARCHAR2 DEFAULT g_false
517 , x_return_status OUT NOCOPY VARCHAR2
518 , x_msg_count OUT NOCOPY NUMBER
519 , x_msg_data OUT NOCOPY VARCHAR2
520 , p_validation_mode IN NUMBER DEFAULT G_EXISTS_ONLY
521 , p_org_id IN NUMBER
522 , p_lpn_id IN OUT NOCOPY NUMBER
523 , p_lpn IN VARCHAR2
524 , p_parent_lpn_id IN NUMBER DEFAULT NULL
525 ) return boolean;
526
527 function validate_lpn_info
528 (p_api_version IN NUMBER DEFAULT 1.0
529 , p_init_msg_lst IN VARCHAR2 DEFAULT g_false
530 , x_return_status OUT NOCOPY VARCHAR2
531 , x_msg_count OUT NOCOPY NUMBER
532 , x_msg_data OUT NOCOPY VARCHAR2
533 , p_validation_mode IN NUMBER DEFAULT G_EXISTS_OR_CREATE
534 , p_lpn_group_id IN NUMBER
535 ) return boolean;
536
537 procedure generate_lpn
538 (p_api_version IN NUMBER
539 , p_init_msg_lst IN VARCHAR2 DEFAULT g_false
540 , p_commit IN VARCHAR2 DEFAULT g_false
541 , x_return_status OUT NOCOPY VARCHAR2
542 , x_msg_count OUT NOCOPY NUMBER
543 , x_msg_data OUT NOCOPY VARCHAR2
544 , p_lpn_id OUT NOCOPY NUMBER
545 , p_lpn OUT NOCOPY VARCHAR2
546 , p_organization_id IN NUMBER
547 );
548
549 procedure explode_lpn
550 (p_api_version IN NUMBER
551 , p_init_msg_lst IN VARCHAR2 DEFAULT g_false
552 , x_return_status OUT NOCOPY VARCHAR2
553 , x_msg_count OUT NOCOPY NUMBER
554 , x_msg_data OUT NOCOPY VARCHAR2
555 , p_group_id IN NUMBER
556 , p_request_id IN NUMBER
557 );
558
559 procedure validate_sub_loc
560 (p_api_version IN NUMBER
561 , p_init_msg_lst IN VARCHAR2 DEFAULT g_false
562 , x_return_status OUT NOCOPY VARCHAR2
563 , x_msg_count OUT NOCOPY NUMBER
564 , x_msg_data OUT NOCOPY VARCHAR2
565 , p_group_id IN NUMBER
566 , p_request_id IN NUMBER
567 , p_rti_id IN NUMBER
568 , p_validation_mode IN NUMBER DEFAULT G_EXISTS_OR_CREATE
569 );
570
571 function split_lot_serial
572 (p_api_version IN NUMBER DEFAULT 1.0
573 , p_init_msg_lst IN VARCHAR2 DEFAULT g_false
574 , x_return_status OUT NOCOPY VARCHAR2
575 , x_msg_count OUT NOCOPY NUMBER
576 , x_msg_data OUT NOCOPY VARCHAR2
577 , p_new_rti_info IN inv_rcv_integration_apis.child_rec_tb_tp
578 ) return boolean;
579
580 function process_transaction
581 (p_api_version IN NUMBER
582 , p_init_msg_lst IN VARCHAR2 DEFAULT g_false
583 , x_return_status OUT NOCOPY VARCHAR2
584 , x_msg_count OUT NOCOPY NUMBER
585 , x_msg_data OUT NOCOPY VARCHAR2
586 , p_rti_id IN NUMBER
587 ) return boolean;
588
589 function complete_lpn_group
590 (p_api_version IN NUMBER
591 , p_init_msg_lst IN VARCHAR2 DEFAULT g_false
592 , x_return_status OUT NOCOPY VARCHAR2
593 , x_msg_count OUT NOCOPY NUMBER
594 , x_msg_data OUT NOCOPY VARCHAR2
595 , p_lpn_group_id IN NUMBER
596 , p_group_id IN NUMBER
597 , p_shipment_header_id IN NUMBER
598 ) return boolean;
599
600 /*-----------------------------------------------------------------------------
601 * PROCEDURE: split_mo
602 * Description:
603 * Takes in an original MOL id, and a table of quantities as
604 [ * arguments. This procedure will split the original MOL, and return
605 * a table of MOL id, each of them having the quantities specified in
606 * the input quantities table. This procedure will also split the MMTTS
607 * corresponding to the original MOL and associate them with the new
608 * MOLs. For example:
609 *
610 * MOL1 Quantity:14 Quantity_delivered:4 Quantity_detailed:8
611 *
612 * Calling split_mo(p_orig_mol_id => MOL1, ( 7, 2 )) will create the
613 * following entries:
614 *
615 * MOL1 QUANTITY 5 Quantity_delivered:4 Quantity_detailed:0
616 * MMTT2 MOL1 QUANTITY 1
617 *
618 * MOL2 QUANTITY7 Quantity_delivered:0 Quantity_detailed:7
619 * MMTT1 MOL2 QUANTITY 4
620 * MMTT3 MOL2 QUANTITY 3
621 *
622 * MOL3 QUANTITY 2 Quantity_delivered:0 Quantity_detailed:1
623 * MMTT2 MOL3 QUANTITY:1
624 *
625 * Note that there the specific association of MMTTs with MOLs will
626 * depends on the original MOL, and this procedure will only guarantee that the
627 * final values will be consistent
628 * consistent.
629 * Output Parameters:
630 * x_mol_id_tb
631 * - new MOL ids created. Its index correspond to the index of
632 * p_prim_qty_tb, i.e. x_mol_id_tb(1) has quantity
633 * p_prim_qty_tb(1)
634 * x_return_status
635 * - Return status indicating Success (S), Error (E), Unexpected
636 * Error (U)
637 * x_msg_count
638 * - Number of messages in message list
639 * x_msg_data
640 * - Stacked messages text
641 *
642 * Input Parameters:
643 * p_orig_mol_id - The line_id of the mol to be split
644 * p_prim_qty_tb - primary_quantity to be split
645 * Returns: NONE
646 *---------------------------------------------------------------------------*/
647 PROCEDURE split_mo
648 (p_orig_mol_id IN NUMBER,
649 p_mo_splt_tb IN OUT nocopy mo_in_tb_tp,
650 p_updt_putaway_temp_tbl IN VARCHAR2 DEFAULT fnd_api.g_false,
651 p_txn_header_id IN NUMBER DEFAULT NULL,
652 p_operation_type IN VARCHAR2 DEFAULT NULL,
653 x_return_status OUT NOCOPY VARCHAR2,
654 x_msg_count OUT NOCOPY NUMBER,
655 x_msg_data OUT NOCOPY VARCHAR2
656 );
657
658 PROCEDURE split_mmtt
659 (p_orig_mmtt_id NUMBER
660 ,p_prim_qty_to_splt NUMBER
661 ,p_prim_uom_code VARCHAR2
662 ,x_new_mmtt_id OUT nocopy NUMBER
663 ,x_return_status OUT NOCOPY VARCHAR2
664 ,x_msg_count OUT NOCOPY NUMBER
665 ,x_msg_data OUT NOCOPY VARCHAR2
666 );
667 END inv_rcv_integration_apis;
[Home] [Help]