1 PACKAGE inv_lot_api_pub AUTHID CURRENT_USER AS
2 /* $Header: INVPLOTS.pls 120.13.12020000.4 2013/01/31 07:26:40 alxue ship $ */
3
4 /**
5 * global variable for lots attributes
6 */
7 g_lot_attributes_tbl inv_lot_sel_attr.lot_sel_attributes_tbl_type;
8 g_firstscan BOOLEAN := TRUE;
9
10 G_WMS_INSTALLED VARCHAR2(10);
11
12 TYPE char_tbl IS TABLE OF VARCHAR2(1000)
13 INDEX BY BINARY_INTEGER;
14
15 TYPE number_tbl IS TABLE OF NUMBER
16 INDEX BY BINARY_INTEGER;
17
18 TYPE date_tbl IS TABLE OF DATE
19 INDEX BY BINARY_INTEGER;
20
21 TYPE WMS_NAMED_ATTRIBUTES IS RECORD
22 ( grade_code VARCHAR2(150)
23 , DISABLE_FLAG NUMBER
24 , origination_date DATE
25 , date_code VARCHAR2(150)
26 , change_date DATE
27 , age NUMBER
28 , retest_date DATE
29 , maturity_date DATE
30 , item_size NUMBER
31 , color VARCHAR2(150)
32 , volume NUMBER
33 , volume_uom VARCHAR2(3)
34 , place_of_origin VARCHAR2(150)
35 , best_by_date DATE
36 , length NUMBER
37 , length_uom VARCHAR2(3)
38 , recycled_content NUMBER
39 , thickness NUMBER
40 , thickness_uom VARCHAR2(3)
41 , width NUMBER
42 , width_uom VARCHAR2(3)
43 , territory_code VARCHAR2(30)
44 , supplier_lot_number VARCHAR2(150)
45 , VENDOR_NAME VARCHAR2(240)
46 );
47
48 g_ret_sts_success CONSTANT VARCHAR2(1) := 'S';
49 g_ret_sts_error CONSTANT VARCHAR2(1) := 'E';
50 g_ret_sts_unexp_error CONSTANT VARCHAR2(1) := 'U';
51 g_miss_num CONSTANT NUMBER := 9.99e125;
52 g_miss_char CONSTANT VARCHAR2(1) := CHR(0);
53 g_miss_date CONSTANT DATE := TO_DATE('1', 'j');
54 /*Exception definitions */
55 g_exc_error EXCEPTION;
56 g_exc_unexpected_error EXCEPTION;
57 /*Local variable for stoRing the INV:DEBUG TRACE profile value */
58 g_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
59 osfm_form_no_validate CONSTANT NUMBER := 1;
60 osfm_open_interface CONSTANT NUMBER := 2;
61 osfm_form_validate CONSTANT NUMBER := 3;
62 inv CONSTANT NUMBER := 4;
63
64 PROCEDURE populateattributescolumn;
65
66 PROCEDURE set_firstscan(p_firstscan BOOLEAN);
67
68 /**
69 * This procedure inserts a lot into the MTL_LOT_NUMBERS table.
70 * It does all the necessary validation before inserting the lot.
71 * It derives the expiration date depending on the controls set
72 * for shelf_life_code and shelf_life_days for the item.
73 * Returns success if it is able to insert the lot.
74 * If the lot already exists for the same item and org, it still
75 * returns success. However, it places a message on the stack
76 * informing that the lot already exists.
77 * It returns error if there is any validation error.
78 * Standard WHO information is used from the fnd_global api.
79 */
80 PROCEDURE insertlot(
81 p_api_version IN NUMBER
82 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
83 , p_commit IN VARCHAR2 := fnd_api.g_false
84 , p_validation_level IN NUMBER := fnd_api.g_valid_level_full
85 , p_inventory_item_id IN NUMBER
86 , p_organization_id IN NUMBER
87 , p_lot_number IN VARCHAR2
88 , p_expiration_date IN OUT NOCOPY DATE
89 , p_transaction_temp_id IN NUMBER DEFAULT NULL
90 , p_transaction_action_id IN NUMBER DEFAULT NULL
91 , p_transfer_organization_id IN NUMBER DEFAULT NULL
92 , x_object_id OUT NOCOPY NUMBER
93 , x_return_status OUT NOCOPY VARCHAR2
94 , x_msg_count OUT NOCOPY NUMBER
95 , x_msg_data OUT NOCOPY VARCHAR2
96 , p_parent_lot_number IN VARCHAR2 DEFAULT NULL -- bug 10176719 - inserting parent lot number
97 , p_origination_type IN NUMBER DEFAULT NULL -- bug 15896641 - inserting origination type
98 );
99
100 PROCEDURE inserttrxlot(
101 p_api_version IN NUMBER
102 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
103 , p_commit IN VARCHAR2 := fnd_api.g_false
104 , p_validation_level IN NUMBER := fnd_api.g_valid_level_full
105 , p_primary_quantity IN NUMBER DEFAULT NULL
106 , p_transaction_id IN NUMBER
107 , p_inventory_item_id IN NUMBER
108 , p_organization_id IN NUMBER
109 , p_transaction_date IN DATE
110 , p_transaction_source_id IN NUMBER
111 , p_transaction_source_name IN VARCHAR2
112 , p_transaction_source_type_id IN NUMBER
113 , p_transaction_temp_id IN NUMBER
114 , p_transaction_action_id IN NUMBER
115 , p_serial_transaction_id IN NUMBER
116 , p_lot_number IN VARCHAR2
117 , x_return_status OUT NOCOPY VARCHAR2
118 , x_msg_count OUT NOCOPY NUMBER
119 , x_msg_data OUT NOCOPY VARCHAR2
120 );
121
122 /* This function (validate_unique_lot) validates a given lot number for an organization and item
123 depending on the uniqueness level set
124 This function is called from auto_gen_lot function in order to check the uniqueness of
125 the generated lot number*/
126 FUNCTION validate_unique_lot(p_org_id IN NUMBER, p_inventory_item_id IN NUMBER, p_lot_uniqueness IN NUMBER, p_auto_lot_number IN VARCHAR2)
127 RETURN BOOLEAN;
128
129 /* Created a wrapper around validate_unique_lot to be able to call it thru Mobile apps */
130 PROCEDURE validate_unique_lot(
131 p_org_id IN NUMBER
132 , p_inventory_item_id IN NUMBER
133 , p_lot_uniqueness IN NUMBER
134 , p_auto_lot_number IN VARCHAR2
135 , p_check_same_item IN VARCHAR2
136 , x_is_unique OUT NOCOPY VARCHAR2
137 );
138
139
140
141 /*================================================
142 This function inserts mtl_child_lot_numbers.
143 ================================================*/
144
145 FUNCTION ins_mtl_child_lot_num (
146 p_org_id IN NUMBER
147 , p_inventory_item_id IN NUMBER
148 , p_parent_lot_number IN VARCHAR2
149 , p_last_child_lot_seq IN NUMBER
150 )
151 RETURN NUMBER;
152
153 /*================================================
154 This function updates mtl_child_lot_numbers.
155 ================================================*/
156
157 FUNCTION upd_mtl_child_lot_num (
158 p_org_id IN NUMBER
159 , p_inventory_item_id IN NUMBER
160 , p_parent_lot_number IN VARCHAR2
161 , p_last_child_lot_seq IN NUMBER
162 )
163 RETURN NUMBER;
164
165
166
167
168
169
170
171 /**
172 * This function ( auto_gen_lot) replaces the auto_gen_lot function in the INVTTELT.pld .
173 * It generates a lot number for for a given organization and item id and
174 * these 2 are the mandatory parameters.
175 * Other input parameters if not provided, are retrieved in the function .
176 * This function calls a user defined pl/sql procedure to generate lot numbers
177 * if the user_defined_proc returns a lot number then it validtes the lot_number and returns to the caller
178 * if the user_defined_proc returns null then this function generates a lot number, checks for its
179 * validity and returns the lot_number along with the return_status
180 */
181 -- Fix for Bug#12925054
182 -- Added new parameters p_transaction_source_id and p_transaction_source_line_id
183
184 FUNCTION auto_gen_lot(
185 p_org_id IN NUMBER
186 , p_inventory_item_id IN NUMBER
187 , p_lot_generation IN NUMBER := NULL
188 , p_lot_uniqueness IN NUMBER := NULL
189 , p_lot_prefix IN VARCHAR2 := NULL
190 , p_zero_pad IN NUMBER := NULL
191 , p_lot_length IN NUMBER := NULL
192 , p_transaction_date IN DATE := NULL
193 , p_revision IN VARCHAR2 := NULL
194 , p_subinventory_code IN VARCHAR2 := NULL
195 , p_locator_id IN NUMBER := NULL
196 , p_transaction_type_id IN NUMBER := NULL
197 , p_transaction_action_id IN NUMBER := NULL
198 , p_transaction_source_type_id IN NUMBER := NULL
199 , p_lot_number IN VARCHAR2 := NULL
200 , p_api_version IN NUMBER
201 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
202 , p_commit IN VARCHAR2 := fnd_api.g_false
203 , p_validation_level IN NUMBER := fnd_api.g_valid_level_full
204 , p_parent_lot_number IN VARCHAR2
205 , x_return_status OUT NOCOPY VARCHAR2
206 , x_msg_count OUT NOCOPY NUMBER
207 , x_msg_data OUT NOCOPY VARCHAR2
208 , p_transaction_source_id IN NUMBER DEFAULT NULL /* 13368816 */
209 , p_transaction_source_line_id IN NUMBER DEFAULT NULL /* 13368816 */
210 )
211 RETURN VARCHAR2;
212
213 /* This is a procedure that accepts the Named WMS attributes, C_Attributes,
214 N_Attributes, D_attributes, and INV attributes as pl/sql table input parameter
215 and validates the values passed as input parameters against the valueset attached to it.
216 This has flex Api call that does the actual validation
217 */
218
219 PROCEDURE validate_lot_attr_info(
220 x_return_status OUT NOCOPY VARCHAR2
221 , x_msg_count OUT NOCOPY NUMBER
222 , x_msg_data OUT NOCOPY VARCHAR2
223 , p_wms_is_installed IN VARCHAR2
224 , p_attribute_category IN VARCHAR2
225 , p_lot_attribute_category IN VARCHAR2
226 , p_inventory_item_id IN NUMBER
227 , p_organization_id IN NUMBER
228 , p_attributes_tbl IN inv_lot_api_pub.char_tbl
229 , p_c_attributes_tbl IN inv_lot_api_pub.char_tbl
230 , p_n_attributes_tbl IN inv_lot_api_pub.number_tbl
231 , p_d_attributes_tbl IN inv_lot_api_pub.date_tbl
232 , p_disable_flag IN NUMBER
233 , p_grade_code IN VARCHAR2
234 , p_origination_date IN DATE
235 , p_date_code IN VARCHAR2
236 , p_change_date IN DATE
237 , p_age IN NUMBER
238 , p_retest_date IN DATE
239 , p_maturity_date IN DATE
240 , p_item_size IN NUMBER
241 , p_color IN VARCHAR2
242 , p_volume IN NUMBER
243 , p_volume_uom IN VARCHAR2
244 , p_place_of_origin IN VARCHAR2
245 , p_best_by_date IN DATE
246 , p_length IN NUMBER
247 , p_length_uom IN VARCHAR2
248 , p_recycled_content IN NUMBER
249 , p_thickness IN NUMBER
250 , p_thickness_uom IN VARCHAR2
251 , p_width IN NUMBER
252 , p_width_uom IN VARCHAR2
253 , p_territory_code IN VARCHAR2
254 , p_supplier_lot_number IN VARCHAR2
255 , p_vendor_name IN VARCHAR2
256 );
257
258 /*
259
260 This is a procedure that accepts the lot attributes as input parameters.
261 This procedure validates some the input parameters like lot_number, expiration date,
262 INV attributes, C_ATTRIBUTES, N_ATTRIBUTES and D_ATTRIBUTES.
263 If all the validations go through fine, data is inserted into mtl_lot_numbers
264 */
265 -- This procedure has now be converted into a stub and it internally calls
266 -- the overloaded create_inv_lot procedure.
267
268 PROCEDURE create_inv_lot(
269 x_return_status OUT NOCOPY VARCHAR2
270 , x_msg_count OUT NOCOPY NUMBER
271 , x_msg_data OUT NOCOPY VARCHAR2
272 , p_inventory_item_id IN NUMBER
273 , p_organization_id IN NUMBER
274 , p_lot_number IN VARCHAR2
275 , p_expiration_date IN DATE
276 , p_disable_flag IN NUMBER
277 , p_attribute_category IN VARCHAR2
278 , p_lot_attribute_category IN VARCHAR2
279 , p_attributes_tbl IN inv_lot_api_pub.char_tbl
280 , p_c_attributes_tbl IN inv_lot_api_pub.char_tbl
281 , p_n_attributes_tbl IN inv_lot_api_pub.number_tbl
282 , p_d_attributes_tbl IN inv_lot_api_pub.date_tbl
283 , p_grade_code IN VARCHAR2
284 , p_origination_date IN DATE
285 , p_date_code IN VARCHAR2
286 , p_status_id IN NUMBER
287 , p_change_date IN DATE
288 , p_age IN NUMBER
289 , p_retest_date IN DATE
290 , p_maturity_date IN DATE
291 , p_item_size IN NUMBER
292 , p_color IN VARCHAR2
293 , p_volume IN NUMBER
294 , p_volume_uom IN VARCHAR2
295 , p_place_of_origin IN VARCHAR2
296 , p_best_by_date IN DATE
297 , p_length IN NUMBER
298 , p_length_uom IN VARCHAR2
299 , p_recycled_content IN NUMBER
300 , p_thickness IN NUMBER
301 , p_thickness_uom IN VARCHAR2
302 , p_width IN NUMBER
303 , p_width_uom IN VARCHAR2
304 , p_territory_code IN VARCHAR2
305 , p_supplier_lot_number IN VARCHAR2
306 , p_vendor_name IN VARCHAR2
307 , p_source IN NUMBER
308 , p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false -- bug 7513308
309 );
310
311 /*
312 This is a procedure that validates the input parameters to the API.
313 It does the following validations
314 a. Check if the item passed is a lot controlled Item
315 b. Check for lot uniqueness
316 c. Also has a call to validate the DFF attributes values against the value set
317 attached to each of the segments
318 */
319
320 PROCEDURE validate_lot_attr_in_param(
321 x_return_status OUT NOCOPY VARCHAR2
322 , x_msg_count OUT NOCOPY NUMBER
323 , x_msg_data OUT NOCOPY VARCHAR2
324 , p_inventory_item_id IN NUMBER
325 , p_organization_id IN NUMBER
326 , p_lot_number IN VARCHAR2
327 , p_attribute_category IN VARCHAR2
328 , p_lot_attribute_category IN VARCHAR2
329 , p_attributes_tbl IN inv_lot_api_pub.char_tbl
330 , p_c_attributes_tbl IN inv_lot_api_pub.char_tbl
331 , p_n_attributes_tbl IN inv_lot_api_pub.number_tbl
332 , p_d_attributes_tbl IN inv_lot_api_pub.date_tbl
333 , p_wms_is_installed IN VARCHAR2
334 , p_source IN NUMBER
335 , p_disable_flag IN NUMBER
336 , p_grade_code IN VARCHAR2
337 , p_origination_date IN DATE
338 , p_date_code IN VARCHAR2
339 , p_change_date IN DATE
340 , p_age IN NUMBER
341 , p_retest_date IN DATE
342 , p_maturity_date IN DATE
343 , p_item_size IN NUMBER
344 , p_color IN VARCHAR2
345 , p_volume IN NUMBER
346 , p_volume_uom IN VARCHAR2
347 , p_place_of_origin IN VARCHAR2
348 , p_best_by_date IN DATE
349 , p_length IN NUMBER
350 , p_length_uom IN VARCHAR2
351 , p_recycled_content IN NUMBER
352 , p_thickness IN NUMBER
353 , p_thickness_uom IN VARCHAR2
354 , p_width IN NUMBER
355 , p_width_uom IN VARCHAR2
356 , p_territory_code IN VARCHAR2
357 , p_supplier_lot_number IN VARCHAR2
358 , p_vendor_name IN VARCHAR2
359 );
360
361 /*
362 This is a procedure that accepts the lot attributes as input parameters.
363 This procedure validates some the input parameters like lot_number, expiration_date,
364 INV attributes, C_ATTRIBUTES, N_ATTRIBUTES and D_ATTRIBUTES.
365 If all the validations go through fine, data is updated in mtl_lot_numbers table
366 */
367 /*Bug 14835444 Teh default values changed to g_miss_date, g_miss_num and g_miss_char from NULL */
368 PROCEDURE update_inv_lot(
369 x_return_status OUT NOCOPY VARCHAR2
370 , x_msg_count OUT NOCOPY NUMBER
371 , x_msg_data OUT NOCOPY VARCHAR2
372 , p_inventory_item_id IN NUMBER
373 , p_organization_id IN NUMBER
374 , p_lot_number IN VARCHAR2
375 , p_expiration_date IN DATE DEFAULT NULL
376 , p_disable_flag IN NUMBER DEFAULT g_miss_num --NULL
377 , p_attribute_category IN VARCHAR2 DEFAULT g_miss_char --NULL
378 , p_lot_attribute_category IN VARCHAR2 DEFAULT g_miss_char --NULL
379 , p_attributes_tbl IN inv_lot_api_pub.char_tbl
380 , p_c_attributes_tbl IN inv_lot_api_pub.char_tbl
381 , p_n_attributes_tbl IN inv_lot_api_pub.number_tbl
382 , p_d_attributes_tbl IN inv_lot_api_pub.date_tbl
383 , p_grade_code IN VARCHAR2 DEFAULT NULL
384 , p_origination_date IN DATE DEFAULT g_miss_date --NULL
385 , p_date_code IN VARCHAR2 DEFAULT g_miss_char --NULL
386 , p_status_id IN NUMBER DEFAULT NULL
387 , p_change_date IN DATE DEFAULT g_miss_date --NULL
388 , p_age IN NUMBER DEFAULT g_miss_num --NULL
389 , p_retest_date IN DATE DEFAULT g_miss_date --NULL
390 , p_maturity_date IN DATE DEFAULT g_miss_date --NULL
391 , p_item_size IN NUMBER DEFAULT g_miss_num--NULL
392 , p_color IN VARCHAR2 DEFAULT g_miss_char --NULL
393 , p_volume IN NUMBER DEFAULT g_miss_num --NULL
394 , p_volume_uom IN VARCHAR2 DEFAULT g_miss_char --NULL
395 , p_place_of_origin IN VARCHAR2 DEFAULT g_miss_char --NULL
396 , p_best_by_date IN DATE DEFAULT g_miss_date --NULL
397 , p_length IN NUMBER DEFAULT g_miss_num --NULL
398 , p_length_uom IN VARCHAR2 DEFAULT g_miss_char --NULL
399 , p_recycled_content IN NUMBER DEFAULT g_miss_num --NULL
400 , p_thickness IN NUMBER DEFAULT g_miss_num --NULL
401 , p_thickness_uom IN VARCHAR2 DEFAULT g_miss_char --NULL
402 , p_width IN NUMBER DEFAULT g_miss_num --NULL
403 , p_width_uom IN VARCHAR2 DEFAULT g_miss_char --NULL
404 , p_territory_code IN VARCHAR2 DEFAULT g_miss_char --NULL
405 , p_supplier_lot_number IN VARCHAR2 DEFAULT g_miss_char --NULL
406 , p_vendor_name IN VARCHAR2 DEFAULT g_miss_char --NULL
407 , p_source IN NUMBER
408 );
409
410 -- nsinghi bug#5209065 START. Added following procedure.
411 /*
412 This is a procedure that accepts the lot record as input parameters.
413 This procedure validates some the input parameters like lot_number, expiration_date,
414 INV attributes, C_ATTRIBUTES, N_ATTRIBUTES and D_ATTRIBUTES.
415 If all the validations go through fine, data is updated in mtl_lot_numbers table
416 */
417
418 PROCEDURE update_inv_lot(
419 x_return_status OUT NOCOPY VARCHAR2
420 , x_msg_count OUT NOCOPY NUMBER
421 , x_msg_data OUT NOCOPY VARCHAR2
422 , x_lot_rec OUT NOCOPY MTL_LOT_NUMBERS%ROWTYPE
423 , p_lot_rec IN MTL_LOT_NUMBERS%ROWTYPE
424 , p_source IN NUMBER
425 , p_api_version IN NUMBER
426 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
427 , p_commit IN VARCHAR2 := fnd_api.g_false);
428 -- nsinghi bug#5209065 END.
429
430 /*
431 This is a procedure that accepts all the unnamed and named WMS attributes and
432 validates the values against the valueset attached to it.
433 */
434
435 PROCEDURE wms_lot_attr_validate(
436 x_return_status OUT NOCOPY VARCHAR2
437 , x_msg_count OUT NOCOPY NUMBER
438 , x_msg_data OUT NOCOPY VARCHAR2
439 , p_inventory_item_id IN NUMBER
440 , p_organization_id IN NUMBER
441 , p_disable_flag IN NUMBER
442 , p_lot_attribute_category IN VARCHAR2
443 , p_c_attributes_tbl IN inv_lot_api_pub.char_tbl
444 , p_n_attributes_tbl IN inv_lot_api_pub.number_tbl
445 , p_d_attributes_tbl IN inv_lot_api_pub.date_tbl
446 , p_grade_code IN VARCHAR2
447 , p_origination_date IN DATE
448 , p_date_code IN VARCHAR2
449 , p_change_date IN DATE
450 , p_age IN NUMBER
451 , p_retest_date IN DATE
452 , p_maturity_date IN DATE
453 , p_item_size IN NUMBER
454 , p_color IN VARCHAR2
455 , p_volume IN NUMBER
456 , p_volume_uom IN VARCHAR2
457 , p_place_of_origin IN VARCHAR2
458 , p_best_by_date IN DATE
459 , p_length IN NUMBER
460 , p_length_uom IN VARCHAR2
461 , p_recycled_content IN NUMBER
462 , p_thickness IN NUMBER
463 , p_thickness_uom IN VARCHAR2
464 , p_width IN NUMBER
465 , p_width_uom IN VARCHAR2
466 , p_territory_code IN VARCHAR2
467 , p_supplier_lot_number IN VARCHAR2
468 , p_vendor_name IN VARCHAR2
469 );
470
471 PROCEDURE set_wms_installed_flag(
472 p_wms_installed_flag IN VARCHAR2);
473
474
475
476 -- This procedure validates child lots against either org
477 -- or item setup parameters. Child lots must conform to
478 -- the setup defined for them.
479
480
481 PROCEDURE validate_child_lot
482 ( p_api_version IN NUMBER
483 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
484 , p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
485 , p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL
486 , p_organization_id IN NUMBER
487 , p_inventory_item_id IN NUMBER
488 , p_parent_lot_number IN VARCHAR2
489 , p_child_lot_number IN VARCHAR2
490 , x_return_status OUT NOCOPY VARCHAR2
491 , x_msg_count OUT NOCOPY NUMBER
492 , x_msg_data OUT NOCOPY VARCHAR2
493 );
494
495 -- This function validates, from the IN parameters,
496 -- whether a lot transaction can be added into the inventory
497 -- regarding the lot definition.
498 FUNCTION validate_lot_indivisible
499 ( p_api_version IN NUMBER
500 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
501 , p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
502 , p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL
503 , p_transaction_type_id IN NUMBER
504 , p_organization_id IN NUMBER
505 , p_inventory_item_id IN NUMBER
506 , p_revision IN VARCHAR2
507 , p_subinventory_code IN VARCHAR2
508 , p_locator_id IN NUMBER
509 , p_lot_number IN VARCHAR2
510 , p_primary_quantity IN NUMBER
511 , p_qoh IN NUMBER DEFAULT NULL
512 , p_atr IN NUMBER DEFAULT NULL
513 , x_return_status OUT NOCOPY VARCHAR2
514 , x_msg_count OUT NOCOPY NUMBER
515 , x_msg_data OUT NOCOPY VARCHAR2
516 ) RETURN BOOLEAN;
517
518 --Overloaded function which returns
519 --the Quantity as well
520 FUNCTION validate_lot_indivisible
521 ( p_api_version IN NUMBER
522 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
523 , p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
524 , p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL
525 , p_transaction_type_id IN NUMBER
526 , p_organization_id IN NUMBER
527 , p_inventory_item_id IN NUMBER
528 , p_revision IN VARCHAR2
529 , p_subinventory_code IN VARCHAR2
530 , p_locator_id IN NUMBER
531 , p_lot_number IN VARCHAR2
532 , p_lpn_id IN NUMBER DEFAULT NULL /*Bug#10113239 */
533 , p_primary_quantity IN NUMBER
534 , p_secondary_quantity IN NUMBER DEFAULT NULL /*Bug#11729772*/
535 , p_qoh IN NUMBER DEFAULT NULL
536 , p_atr IN NUMBER DEFAULT NULL
537 , x_primary_quantity OUT NOCOPY NUMBER
538 , x_secondary_quantity OUT NOCOPY NUMBER /*Bug#11729772*/
539 , x_return_status OUT NOCOPY VARCHAR2
540 , x_msg_count OUT NOCOPY NUMBER
541 , x_msg_data OUT NOCOPY VARCHAR2
542 ) RETURN BOOLEAN;
543
544
545 /* INVCONV, NSRIVAST, Start*/
546 -- This is the overloaded procedure for the exisiting Create_Inv_Lot procedure.
547 -- It inserts a record in mtl_lot_numbers table. Apart from this, it creates a
548 -- UOM Conversion record based on the value of copy_lot_uom_conversion.
549
550 PROCEDURE Create_Inv_lot(
551 x_return_status OUT NOCOPY VARCHAR2
552 , x_msg_count OUT NOCOPY NUMBER
553 , x_msg_data OUT NOCOPY VARCHAR2
554 , x_row_id OUT NOCOPY ROWID
555 , x_lot_rec OUT NOCOPY MTL_LOT_NUMBERS%ROWTYPE
556 , p_lot_rec IN MTL_LOT_NUMBERS%ROWTYPE
557 , p_source IN NUMBER
558 , p_api_version IN NUMBER
559 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
560 , p_commit IN VARCHAR2 := fnd_api.g_false
561 , p_validation_level IN NUMBER := fnd_api.g_valid_level_full
562 , p_origin_txn_id IN NUMBER
563 ) ;
564
565
566 --OVER LOAD 14192172
567 PROCEDURE Create_Inv_lot(
568 x_return_status OUT NOCOPY VARCHAR2
569 , x_msg_count OUT NOCOPY NUMBER
570 , x_msg_data OUT NOCOPY VARCHAR2
571 , x_row_id OUT NOCOPY ROWID
572 , x_lot_rec OUT NOCOPY MTL_LOT_NUMBERS%ROWTYPE
573 , p_lot_rec IN MTL_LOT_NUMBERS%ROWTYPE
574 , p_source IN NUMBER
575 , p_api_version IN NUMBER
576 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
577 , p_commit IN VARCHAR2 := fnd_api.g_false
578 , p_validation_level IN NUMBER := fnd_api.g_valid_level_full
579 , p_origin_txn_id IN NUMBER
580 , p_caculate_flag IN VARCHAR2 -- 14192172
581 ) ;
582
583 /* INVCONV, NSRIVAST, End*/
584
585 /* INVCONV , HVERDDIN ADDED AUTO_GEN_LOT Wrapper for MSCA, Start */
586 -- Fix for Bug#12925054
587 -- Added new parameters p_transaction_source_id and p_transaction_source_line_id
588
589
590 FUNCTION auto_gen_lot(
591 p_org_id IN NUMBER
592 , p_inventory_item_id IN NUMBER
593 , p_lot_generation IN NUMBER := NULL
594 , p_lot_uniqueness IN NUMBER := NULL
595 , p_lot_prefix IN VARCHAR2 := NULL
596 , p_zero_pad IN NUMBER := NULL
597 , p_lot_length IN NUMBER := NULL
598 , p_transaction_date IN DATE := NULL
599 , p_revision IN VARCHAR2 := NULL
600 , p_subinventory_code IN VARCHAR2 := NULL
601 , p_locator_id IN NUMBER := NULL
602 , p_transaction_type_id IN NUMBER := NULL
603 , p_transaction_action_id IN NUMBER := NULL
604 , p_transaction_source_type_id IN NUMBER := NULL
605 , p_lot_number IN VARCHAR2 := NULL
606 , p_api_version IN NUMBER
607 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
608 , p_commit IN VARCHAR2 := fnd_api.g_false
609 , p_validation_level IN NUMBER := fnd_api.g_valid_level_full
610 , x_return_status OUT NOCOPY VARCHAR2
611 , x_msg_count OUT NOCOPY NUMBER
612 , x_msg_data OUT NOCOPY VARCHAR2
613 , p_transaction_source_id IN NUMBER DEFAULT NULL /* 13368816 */
614 , p_transaction_source_line_id IN NUMBER DEFAULT NULL /* 13368816 */
615 )
616 RETURN VARCHAR2;
617
618 /* INVCONV , HVERDDIN ADDED AUTO_GEN_LOT Wrapper for MSCA , End*/
619
620 /*INVCONV, Punit Kumar*/
621
622 PROCEDURE CHECK_LOT_INDIVISIBILITY ( p_api_version IN NUMBER DEFAULT 1.0
623 ,p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
624 ,p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
625 ,p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL
626 ,p_rti_id IN NUMBER
627 ,p_transaction_type_id IN NUMBER
628 ,p_lot_number IN VARCHAR2
629 ,p_lot_quantity IN NUMBER
630 ,p_revision IN VARCHAR2
631 ,p_qoh IN NUMBER DEFAULT NULL
632 ,p_atr IN NUMBER DEFAULT NULL
633 ,x_return_status OUT NOCOPY VARCHAR2
634 ,x_msg_count OUT NOCOPY NUMBER
635 ,x_msg_data OUT NOCOPY VARCHAR2
636 ) ;
637 /*end, INVCONV, Punit Kumar*/
638
639 -----------------------------------------------------------------------
640 -- Name : validate_quantities
641 -- Desc : This procedure is used to validate transaction quantity2
642 --
643 -- I/P Params :
644 -- All the relevant transaction details :
645 -- - organization id
646 -- - item_id
647 -- - lot, revision, subinventory
648 -- - transaction quantities
649 -- O/P Params :
650 -- x_rerturn_status.
651 -- RETURN VALUE :
652 -- TRUE : IF the transaction is valid regarding Quantity2 and lot indivisible
653 -- FALSE : IF the transaction is NOT valid regarding Quantity2 and lot indivisible
654 --
655 -----------------------------------------------------------------------
656 FUNCTION validate_quantities(
657 p_api_version IN NUMBER
658 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
659 , p_transaction_type_id IN NUMBER
660 , p_organization_id IN NUMBER
661 , p_inventory_item_id IN NUMBER
662 , p_revision IN VARCHAR2
663 , p_subinventory_code IN VARCHAR2
664 , p_locator_id IN NUMBER
665 , p_lot_number IN VARCHAR2
666 , p_transaction_quantity IN OUT NOCOPY NUMBER
667 , p_transaction_uom_code IN VARCHAR2
668 , p_primary_quantity IN OUT NOCOPY NUMBER
669 , p_primary_uom_code OUT NOCOPY VARCHAR2
670 , p_secondary_quantity IN OUT NOCOPY NUMBER
671 , p_secondary_uom_code IN OUT NOCOPY VARCHAR2
672 , x_return_status OUT NOCOPY VARCHAR2
673 , x_msg_count OUT NOCOPY NUMBER
674 , x_msg_data OUT NOCOPY VARCHAR2)
675 RETURN BOOLEAN;
676
677 --This procedure checks whether lot specific conversion exist in source org or not
678 --If lot specific conversion exist then it will create the lot specific conversion
679 -- in desitnation org
680 --BUG#10202198
681
682 PROCEDURE lot_UOM_conv_OrgTxf (
683 p_organization_id IN NUMBER
684 , p_inventory_item_id IN NUMBER
685 , p_xfr_organization_id IN NUMBER
686 , p_lot_number IN VARCHAR2
687 , p_transaction_temp_id IN NUMBER
688 , p_to_UOM_code IN VARCHAR2
689 , p_from_UOM_code IN VARCHAR2
690 , x_return_status OUT NOCOPY VARCHAR2
691 , x_msg_count OUT NOCOPY NUMBER
692 , x_msg_data OUT NOCOPY VARCHAR2);
693
694 --this procedure inserts data in MTL_LOT_UOM_CLASS_CONVERSIONS table
695 --Bug#10202198
696 PROCEDURE create_lot_UOM_conv_orgtxf (
697 p_organization_id IN NUMBER
698 , p_inventory_item_id IN NUMBER
699 , p_xfr_organization_id IN NUMBER
700 , p_lot_number IN VARCHAR2
701 , p_to_UOM_code IN VARCHAR2
702 , p_from_UOM_code IN VARCHAR2
703 , x_return_status OUT NOCOPY VARCHAR2
704 , x_msg_count OUT NOCOPY NUMBER
705 , x_msg_data OUT NOCOPY VARCHAR2);
706
707 END inv_lot_api_pub;
[Home] [Help]