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