1 PACKAGE inv_lot_api_pub AS
2 /* $Header: INVPLOTS.pls 120.3.12010000.2 2008/11/18 08:38:32 mporecha 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 );
97
98 PROCEDURE inserttrxlot(
99 p_api_version IN NUMBER
100 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
101 , p_commit IN VARCHAR2 := fnd_api.g_false
102 , p_validation_level IN NUMBER := fnd_api.g_valid_level_full
103 , p_primary_quantity IN NUMBER DEFAULT NULL
104 , p_transaction_id IN NUMBER
105 , p_inventory_item_id IN NUMBER
106 , p_organization_id IN NUMBER
107 , p_transaction_date IN DATE
108 , p_transaction_source_id IN NUMBER
109 , p_transaction_source_name IN VARCHAR2
110 , p_transaction_source_type_id IN NUMBER
111 , p_transaction_temp_id IN NUMBER
112 , p_transaction_action_id IN NUMBER
113 , p_serial_transaction_id IN NUMBER
114 , p_lot_number IN VARCHAR2
115 , x_return_status OUT NOCOPY VARCHAR2
116 , x_msg_count OUT NOCOPY NUMBER
117 , x_msg_data OUT NOCOPY VARCHAR2
118 );
119
120 /* This function (validate_unique_lot) validates a given lot number for an organization and item
121 depending on the uniqueness level set
122 This function is called from auto_gen_lot function in order to check the uniqueness of
123 the generated lot number*/
124 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)
125 RETURN BOOLEAN;
126
127 /* Created a wrapper around validate_unique_lot to be able to call it thru Mobile apps */
128 PROCEDURE validate_unique_lot(
129 p_org_id IN NUMBER
130 , p_inventory_item_id IN NUMBER
131 , p_lot_uniqueness IN NUMBER
132 , p_auto_lot_number IN VARCHAR2
133 , p_check_same_item IN VARCHAR2
134 , x_is_unique OUT NOCOPY VARCHAR2
135 );
136
137
138
139 /*================================================
140 This function inserts mtl_child_lot_numbers.
141 ================================================*/
142
143 FUNCTION ins_mtl_child_lot_num (
144 p_org_id IN NUMBER
145 , p_inventory_item_id IN NUMBER
146 , p_parent_lot_number IN VARCHAR2
147 , p_last_child_lot_seq IN NUMBER
148 )
149 RETURN NUMBER;
150
151 /*================================================
152 This function updates mtl_child_lot_numbers.
153 ================================================*/
154
155 FUNCTION upd_mtl_child_lot_num (
156 p_org_id IN NUMBER
157 , p_inventory_item_id IN NUMBER
158 , p_parent_lot_number IN VARCHAR2
159 , p_last_child_lot_seq IN NUMBER
160 )
161 RETURN NUMBER;
162
163
164
165
166
167
168
169 /**
170 * This function ( auto_gen_lot) replaces the auto_gen_lot function in the INVTTELT.pld .
171 * It generates a lot number for for a given organization and item id and
172 * these 2 are the mandatory parameters.
173 * Other input parameters if not provided, are retrieved in the function .
174 * This function calls a user defined pl/sql procedure to generate lot numbers
175 * if the user_defined_proc returns a lot number then it validtes the lot_number and returns to the caller
176 * if the user_defined_proc returns null then this function generates a lot number, checks for its
177 * validity and returns the lot_number along with the return_status
178 */
179 FUNCTION auto_gen_lot(
180 p_org_id IN NUMBER
181 , p_inventory_item_id IN NUMBER
182 , p_lot_generation IN NUMBER := NULL
183 , p_lot_uniqueness IN NUMBER := NULL
184 , p_lot_prefix IN VARCHAR2 := NULL
185 , p_zero_pad IN NUMBER := NULL
186 , p_lot_length IN NUMBER := NULL
187 , p_transaction_date IN DATE := NULL
188 , p_revision IN VARCHAR2 := NULL
189 , p_subinventory_code IN VARCHAR2 := NULL
190 , p_locator_id IN NUMBER := NULL
191 , p_transaction_type_id IN NUMBER := NULL
192 , p_transaction_action_id IN NUMBER := NULL
193 , p_transaction_source_type_id IN NUMBER := NULL
194 , p_lot_number IN VARCHAR2 := NULL
195 , p_api_version IN NUMBER
196 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
197 , p_commit IN VARCHAR2 := fnd_api.g_false
198 , p_validation_level IN NUMBER := fnd_api.g_valid_level_full
199 , p_parent_lot_number IN VARCHAR2
200 , x_return_status OUT NOCOPY VARCHAR2
201 , x_msg_count OUT NOCOPY NUMBER
202 , x_msg_data OUT NOCOPY VARCHAR2
203 )
204 RETURN VARCHAR2;
205
206 /* This is a procedure that accepts the Named WMS attributes, C_Attributes,
207 N_Attributes, D_attributes, and INV attributes as pl/sql table input parameter
208 and validates the values passed as input parameters against the valueset attached to it.
209 This has flex Api call that does the actual validation
210 */
211
212 PROCEDURE validate_lot_attr_info(
213 x_return_status OUT NOCOPY VARCHAR2
214 , x_msg_count OUT NOCOPY NUMBER
215 , x_msg_data OUT NOCOPY VARCHAR2
216 , p_wms_is_installed IN VARCHAR2
217 , p_attribute_category IN VARCHAR2
218 , p_lot_attribute_category IN VARCHAR2
219 , p_inventory_item_id IN NUMBER
220 , p_organization_id IN NUMBER
221 , p_attributes_tbl IN inv_lot_api_pub.char_tbl
222 , p_c_attributes_tbl IN inv_lot_api_pub.char_tbl
223 , p_n_attributes_tbl IN inv_lot_api_pub.number_tbl
224 , p_d_attributes_tbl IN inv_lot_api_pub.date_tbl
225 , p_disable_flag IN NUMBER
226 , p_grade_code IN VARCHAR2
227 , p_origination_date IN DATE
228 , p_date_code IN VARCHAR2
229 , p_change_date IN DATE
230 , p_age IN NUMBER
231 , p_retest_date IN DATE
232 , p_maturity_date IN DATE
233 , p_item_size IN NUMBER
234 , p_color IN VARCHAR2
235 , p_volume IN NUMBER
236 , p_volume_uom IN VARCHAR2
237 , p_place_of_origin IN VARCHAR2
238 , p_best_by_date IN DATE
239 , p_length IN NUMBER
240 , p_length_uom IN VARCHAR2
241 , p_recycled_content IN NUMBER
242 , p_thickness IN NUMBER
243 , p_thickness_uom IN VARCHAR2
244 , p_width IN NUMBER
245 , p_width_uom IN VARCHAR2
246 , p_territory_code IN VARCHAR2
247 , p_supplier_lot_number IN VARCHAR2
248 , p_vendor_name IN VARCHAR2
249 );
250
251 /*
252
253 This is a procedure that accepts the lot attributes as input parameters.
254 This procedure validates some the input parameters like lot_number, expiration date,
255 INV attributes, C_ATTRIBUTES, N_ATTRIBUTES and D_ATTRIBUTES.
256 If all the validations go through fine, data is inserted into mtl_lot_numbers
257 */
258 -- This procedure has now be converted into a stub and it internally calls
259 -- the overloaded create_inv_lot procedure.
260
261 PROCEDURE create_inv_lot(
262 x_return_status OUT NOCOPY VARCHAR2
263 , x_msg_count OUT NOCOPY NUMBER
264 , x_msg_data OUT NOCOPY VARCHAR2
265 , p_inventory_item_id IN NUMBER
266 , p_organization_id IN NUMBER
267 , p_lot_number IN VARCHAR2
268 , p_expiration_date IN DATE
269 , p_disable_flag IN NUMBER
270 , p_attribute_category IN VARCHAR2
271 , p_lot_attribute_category IN VARCHAR2
272 , p_attributes_tbl IN inv_lot_api_pub.char_tbl
273 , p_c_attributes_tbl IN inv_lot_api_pub.char_tbl
274 , p_n_attributes_tbl IN inv_lot_api_pub.number_tbl
275 , p_d_attributes_tbl IN inv_lot_api_pub.date_tbl
276 , p_grade_code IN VARCHAR2
277 , p_origination_date IN DATE
278 , p_date_code IN VARCHAR2
279 , p_status_id IN NUMBER
280 , p_change_date IN DATE
281 , p_age IN NUMBER
282 , p_retest_date IN DATE
283 , p_maturity_date IN DATE
284 , p_item_size IN NUMBER
285 , p_color IN VARCHAR2
286 , p_volume IN NUMBER
287 , p_volume_uom IN VARCHAR2
288 , p_place_of_origin IN VARCHAR2
289 , p_best_by_date IN DATE
290 , p_length IN NUMBER
291 , p_length_uom IN VARCHAR2
292 , p_recycled_content IN NUMBER
293 , p_thickness IN NUMBER
294 , p_thickness_uom IN VARCHAR2
295 , p_width IN NUMBER
296 , p_width_uom IN VARCHAR2
297 , p_territory_code IN VARCHAR2
298 , p_supplier_lot_number IN VARCHAR2
299 , p_vendor_name IN VARCHAR2
300 , p_source IN NUMBER
301 , p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false -- bug 7513308
302 );
303
304 /*
305 This is a procedure that validates the input parameters to the API.
306 It does the following validations
307 a. Check if the item passed is a lot controlled Item
308 b. Check for lot uniqueness
309 c. Also has a call to validate the DFF attributes values against the value set
310 attached to each of the segments
311 */
312
313 PROCEDURE validate_lot_attr_in_param(
314 x_return_status OUT NOCOPY VARCHAR2
315 , x_msg_count OUT NOCOPY NUMBER
316 , x_msg_data OUT NOCOPY VARCHAR2
317 , p_inventory_item_id IN NUMBER
318 , p_organization_id IN NUMBER
319 , p_lot_number IN VARCHAR2
320 , p_attribute_category IN VARCHAR2
321 , p_lot_attribute_category IN VARCHAR2
322 , p_attributes_tbl IN inv_lot_api_pub.char_tbl
323 , p_c_attributes_tbl IN inv_lot_api_pub.char_tbl
324 , p_n_attributes_tbl IN inv_lot_api_pub.number_tbl
325 , p_d_attributes_tbl IN inv_lot_api_pub.date_tbl
326 , p_wms_is_installed IN VARCHAR2
327 , p_source IN NUMBER
328 , p_disable_flag IN NUMBER
329 , p_grade_code IN VARCHAR2
330 , p_origination_date IN DATE
331 , p_date_code IN VARCHAR2
332 , p_change_date IN DATE
333 , p_age IN NUMBER
334 , p_retest_date IN DATE
335 , p_maturity_date IN DATE
336 , p_item_size IN NUMBER
337 , p_color IN VARCHAR2
338 , p_volume IN NUMBER
339 , p_volume_uom IN VARCHAR2
340 , p_place_of_origin IN VARCHAR2
341 , p_best_by_date IN DATE
342 , p_length IN NUMBER
343 , p_length_uom IN VARCHAR2
344 , p_recycled_content IN NUMBER
345 , p_thickness IN NUMBER
346 , p_thickness_uom IN VARCHAR2
347 , p_width IN NUMBER
348 , p_width_uom IN VARCHAR2
349 , p_territory_code IN VARCHAR2
350 , p_supplier_lot_number IN VARCHAR2
351 , p_vendor_name IN VARCHAR2
352 );
353
354 /*
355 This is a procedure that accepts the lot attributes as input parameters.
356 This procedure validates some the input parameters like lot_number, expiration_date,
357 INV attributes, C_ATTRIBUTES, N_ATTRIBUTES and D_ATTRIBUTES.
358 If all the validations go through fine, data is updated in mtl_lot_numbers table
359 */
360 PROCEDURE update_inv_lot(
361 x_return_status OUT NOCOPY VARCHAR2
362 , x_msg_count OUT NOCOPY NUMBER
363 , x_msg_data OUT NOCOPY VARCHAR2
364 , p_inventory_item_id IN NUMBER
365 , p_organization_id IN NUMBER
366 , p_lot_number IN VARCHAR2
367 , p_expiration_date IN DATE DEFAULT NULL
368 , p_disable_flag IN NUMBER DEFAULT NULL
369 , p_attribute_category IN VARCHAR2 DEFAULT NULL
370 , p_lot_attribute_category IN VARCHAR2 DEFAULT NULL
371 , p_attributes_tbl IN inv_lot_api_pub.char_tbl
372 , p_c_attributes_tbl IN inv_lot_api_pub.char_tbl
373 , p_n_attributes_tbl IN inv_lot_api_pub.number_tbl
374 , p_d_attributes_tbl IN inv_lot_api_pub.date_tbl
375 , p_grade_code IN VARCHAR2 DEFAULT NULL
376 , p_origination_date IN DATE DEFAULT NULL
377 , p_date_code IN VARCHAR2 DEFAULT NULL
378 , p_status_id IN NUMBER DEFAULT NULL
379 , p_change_date IN DATE DEFAULT NULL
383 , p_item_size IN NUMBER DEFAULT NULL
380 , p_age IN NUMBER DEFAULT NULL
381 , p_retest_date IN DATE DEFAULT NULL
382 , p_maturity_date IN DATE DEFAULT NULL
384 , p_color IN VARCHAR2 DEFAULT NULL
385 , p_volume IN NUMBER DEFAULT NULL
386 , p_volume_uom IN VARCHAR2 DEFAULT NULL
387 , p_place_of_origin IN VARCHAR2 DEFAULT NULL
388 , p_best_by_date IN DATE DEFAULT NULL
389 , p_length IN NUMBER DEFAULT NULL
390 , p_length_uom IN VARCHAR2 DEFAULT NULL
391 , p_recycled_content IN NUMBER DEFAULT NULL
392 , p_thickness IN NUMBER DEFAULT NULL
393 , p_thickness_uom IN VARCHAR2 DEFAULT NULL
394 , p_width IN NUMBER DEFAULT NULL
395 , p_width_uom IN VARCHAR2 DEFAULT NULL
396 , p_territory_code IN VARCHAR2 DEFAULT NULL
397 , p_supplier_lot_number IN VARCHAR2 DEFAULT NULL
398 , p_vendor_name IN VARCHAR2 DEFAULT NULL
399 , p_source IN NUMBER
400 );
401
402 -- nsinghi bug#5209065 START. Added following procedure.
403 /*
404 This is a procedure that accepts the lot record as input parameters.
405 This procedure validates some the input parameters like lot_number, expiration_date,
406 INV attributes, C_ATTRIBUTES, N_ATTRIBUTES and D_ATTRIBUTES.
407 If all the validations go through fine, data is updated in mtl_lot_numbers table
408 */
409
410 PROCEDURE update_inv_lot(
411 x_return_status OUT NOCOPY VARCHAR2
412 , x_msg_count OUT NOCOPY NUMBER
413 , x_msg_data OUT NOCOPY VARCHAR2
414 , x_lot_rec OUT NOCOPY MTL_LOT_NUMBERS%ROWTYPE
415 , p_lot_rec IN MTL_LOT_NUMBERS%ROWTYPE
416 , p_source IN NUMBER
417 , p_api_version IN NUMBER
418 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
419 , p_commit IN VARCHAR2 := fnd_api.g_false);
420 -- nsinghi bug#5209065 END.
421
422 /*
423 This is a procedure that accepts all the unnamed and named WMS attributes and
424 validates the values against the valueset attached to it.
425 */
426
427 PROCEDURE wms_lot_attr_validate(
428 x_return_status OUT NOCOPY VARCHAR2
429 , x_msg_count OUT NOCOPY NUMBER
430 , x_msg_data OUT NOCOPY VARCHAR2
431 , p_inventory_item_id IN NUMBER
432 , p_organization_id IN NUMBER
433 , p_disable_flag IN NUMBER
434 , p_lot_attribute_category IN VARCHAR2
435 , p_c_attributes_tbl IN inv_lot_api_pub.char_tbl
436 , p_n_attributes_tbl IN inv_lot_api_pub.number_tbl
437 , p_d_attributes_tbl IN inv_lot_api_pub.date_tbl
438 , p_grade_code IN VARCHAR2
439 , p_origination_date IN DATE
440 , p_date_code IN VARCHAR2
441 , p_change_date IN DATE
442 , p_age IN NUMBER
443 , p_retest_date IN DATE
444 , p_maturity_date IN DATE
445 , p_item_size IN NUMBER
446 , p_color IN VARCHAR2
447 , p_volume IN NUMBER
448 , p_volume_uom IN VARCHAR2
449 , p_place_of_origin IN VARCHAR2
450 , p_best_by_date IN DATE
451 , p_length IN NUMBER
452 , p_length_uom IN VARCHAR2
453 , p_recycled_content IN NUMBER
454 , p_thickness IN NUMBER
455 , p_thickness_uom IN VARCHAR2
456 , p_width IN NUMBER
457 , p_width_uom IN VARCHAR2
458 , p_territory_code IN VARCHAR2
459 , p_supplier_lot_number IN VARCHAR2
460 , p_vendor_name IN VARCHAR2
461 );
462
463 PROCEDURE set_wms_installed_flag(
464 p_wms_installed_flag IN VARCHAR2);
465
466
467
468 -- This procedure validates child lots against either org
469 -- or item setup parameters. Child lots must conform to
470 -- the setup defined for them.
471
472
473 PROCEDURE validate_child_lot
474 ( p_api_version IN NUMBER
475 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
476 , p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
477 , p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL
478 , p_organization_id IN NUMBER
479 , p_inventory_item_id IN NUMBER
480 , p_parent_lot_number IN VARCHAR2
481 , p_child_lot_number IN VARCHAR2
482 , x_return_status OUT NOCOPY VARCHAR2
483 , x_msg_count OUT NOCOPY NUMBER
484 , x_msg_data OUT NOCOPY VARCHAR2
485 );
486
487 -- This function validates, from the IN parameters,
488 -- whether a lot transaction can be added into the inventory
489 -- regarding the lot definition.
490 FUNCTION validate_lot_indivisible
491 ( p_api_version IN NUMBER
492 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
493 , p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
497 , p_inventory_item_id IN NUMBER
494 , p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL
495 , p_transaction_type_id IN NUMBER
496 , p_organization_id IN NUMBER
498 , p_revision IN VARCHAR2
499 , p_subinventory_code IN VARCHAR2
500 , p_locator_id IN NUMBER
501 , p_lot_number IN VARCHAR2
502 , p_primary_quantity IN NUMBER
503 , p_qoh IN NUMBER DEFAULT NULL
504 , p_atr IN NUMBER DEFAULT NULL
505 , x_return_status OUT NOCOPY VARCHAR2
506 , x_msg_count OUT NOCOPY NUMBER
507 , x_msg_data OUT NOCOPY VARCHAR2
508 ) RETURN BOOLEAN;
509
510 --Overloaded function which returns
511 --the Quantity as well
512 FUNCTION validate_lot_indivisible
513 ( p_api_version IN NUMBER
514 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
515 , p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
516 , p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL
517 , p_transaction_type_id IN NUMBER
518 , p_organization_id IN NUMBER
519 , p_inventory_item_id IN NUMBER
520 , p_revision IN VARCHAR2
521 , p_subinventory_code IN VARCHAR2
522 , p_locator_id IN NUMBER
523 , p_lot_number IN VARCHAR2
524 , p_primary_quantity IN NUMBER
525 , p_qoh IN NUMBER DEFAULT NULL
526 , p_atr IN NUMBER DEFAULT NULL
527 , x_primary_quantity OUT NOCOPY NUMBER
528 , x_return_status OUT NOCOPY VARCHAR2
529 , x_msg_count OUT NOCOPY NUMBER
530 , x_msg_data OUT NOCOPY VARCHAR2
531 ) RETURN BOOLEAN;
532
533
534 /* INVCONV, NSRIVAST, Start*/
535 -- This is the overloaded procedure for the exisiting Create_Inv_Lot procedure.
536 -- It inserts a record in mtl_lot_numbers table. Apart from this, it creates a
537 -- UOM Conversion record based on the value of copy_lot_uom_conversion.
538
539 PROCEDURE Create_Inv_lot(
540 x_return_status OUT NOCOPY VARCHAR2
541 , x_msg_count OUT NOCOPY NUMBER
542 , x_msg_data OUT NOCOPY VARCHAR2
543 , x_row_id OUT NOCOPY ROWID
544 , x_lot_rec OUT NOCOPY MTL_LOT_NUMBERS%ROWTYPE
545 , p_lot_rec IN MTL_LOT_NUMBERS%ROWTYPE
546 , p_source IN NUMBER
547 , p_api_version IN NUMBER
548 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
549 , p_commit IN VARCHAR2 := fnd_api.g_false
550 , p_validation_level IN NUMBER := fnd_api.g_valid_level_full
551 , p_origin_txn_id IN NUMBER
552 ) ;
553
554 /* INVCONV, NSRIVAST, End*/
555
556 /* INVCONV , HVERDDIN ADDED AUTO_GEN_LOT Wrapper for MSCA, Start */
557
558 FUNCTION auto_gen_lot(
559 p_org_id IN NUMBER
560 , p_inventory_item_id IN NUMBER
561 , p_lot_generation IN NUMBER := NULL
562 , p_lot_uniqueness IN NUMBER := NULL
563 , p_lot_prefix IN VARCHAR2 := NULL
564 , p_zero_pad IN NUMBER := NULL
565 , p_lot_length IN NUMBER := NULL
566 , p_transaction_date IN DATE := NULL
567 , p_revision IN VARCHAR2 := NULL
568 , p_subinventory_code IN VARCHAR2 := NULL
569 , p_locator_id IN NUMBER := NULL
570 , p_transaction_type_id IN NUMBER := NULL
571 , p_transaction_action_id IN NUMBER := NULL
572 , p_transaction_source_type_id IN NUMBER := NULL
573 , p_lot_number IN VARCHAR2 := NULL
574 , p_api_version IN NUMBER
575 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
576 , p_commit IN VARCHAR2 := fnd_api.g_false
577 , p_validation_level IN NUMBER := fnd_api.g_valid_level_full
578 , x_return_status OUT NOCOPY VARCHAR2
579 , x_msg_count OUT NOCOPY NUMBER
580 , x_msg_data OUT NOCOPY VARCHAR2
581 )
582 RETURN VARCHAR2;
583
584 /* INVCONV , HVERDDIN ADDED AUTO_GEN_LOT Wrapper for MSCA , End*/
585
586 /*INVCONV, Punit Kumar*/
587
588 PROCEDURE CHECK_LOT_INDIVISIBILITY ( p_api_version IN NUMBER DEFAULT 1.0
589 ,p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
590 ,p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
591 ,p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL
592 ,p_rti_id IN NUMBER
593 ,p_transaction_type_id IN NUMBER
594 ,p_lot_number IN VARCHAR2
595 ,p_lot_quantity IN NUMBER
596 ,p_revision IN VARCHAR2
597 ,p_qoh IN NUMBER DEFAULT NULL
598 ,p_atr IN NUMBER DEFAULT NULL
599 ,x_return_status OUT NOCOPY VARCHAR2
603 /*end, INVCONV, Punit Kumar*/
600 ,x_msg_count OUT NOCOPY NUMBER
601 ,x_msg_data OUT NOCOPY VARCHAR2
602 ) ;
604
605 -----------------------------------------------------------------------
606 -- Name : validate_quantities
607 -- Desc : This procedure is used to validate transaction quantity2
608 --
609 -- I/P Params :
610 -- All the relevant transaction details :
611 -- - organization id
612 -- - item_id
613 -- - lot, revision, subinventory
614 -- - transaction quantities
615 -- O/P Params :
616 -- x_rerturn_status.
617 -- RETURN VALUE :
618 -- TRUE : IF the transaction is valid regarding Quantity2 and lot indivisible
619 -- FALSE : IF the transaction is NOT valid regarding Quantity2 and lot indivisible
620 --
621 -----------------------------------------------------------------------
622 FUNCTION validate_quantities(
623 p_api_version IN NUMBER
624 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
625 , p_transaction_type_id IN NUMBER
626 , p_organization_id IN NUMBER
627 , p_inventory_item_id IN NUMBER
628 , p_revision IN VARCHAR2
629 , p_subinventory_code IN VARCHAR2
630 , p_locator_id IN NUMBER
631 , p_lot_number IN VARCHAR2
632 , p_transaction_quantity IN OUT NOCOPY NUMBER
633 , p_transaction_uom_code IN VARCHAR2
634 , p_primary_quantity IN OUT NOCOPY NUMBER
635 , p_primary_uom_code OUT NOCOPY VARCHAR2
636 , p_secondary_quantity IN OUT NOCOPY NUMBER
637 , p_secondary_uom_code IN OUT NOCOPY VARCHAR2
638 , x_return_status OUT NOCOPY VARCHAR2
639 , x_msg_count OUT NOCOPY NUMBER
640 , x_msg_data OUT NOCOPY VARCHAR2)
641 RETURN BOOLEAN;
642
643 END inv_lot_api_pub;
[Home] [Help]