1 PACKAGE INV_LOT_TRX_VALIDATION_PUB AS
2 /* $Header: INVPLTVS.pls 120.6 2005/10/10 07:22:26 methomas noship $ */
3
4 -- Global constant holding the package name
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'INV_LOT_TRX_VALIDATION_PUB';
6
7 TYPE NUMBER_TABLE IS TABLE of NUMBER;
8 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
9 TYPE LOT_NUMBER_TABLE IS TABLE of VARCHAR2(80);
10 --Tables added to support Lot Serialized Items in Lot Transactions(Split/Merge/Translate).
11 TYPE SERIAL_NUMBER_TABLE IS TABLE OF MTL_SERIAL_NUMBERS.SERIAL_NUMBER%TYPE
12 INDEX BY MTL_SERIAL_NUMBERS.SERIAL_NUMBER%TYPE;
13 TYPE PARENT_LOT_TABLE IS TABLE OF MTL_LOT_NUMBERS.LOT_NUMBER%TYPE
14 INDEX BY MTL_SERIAL_NUMBERS.SERIAL_NUMBER%TYPE;
15 TYPE PARENT_LOC_TABLE IS TABLE OF MTL_ITEM_LOCATIONS.INVENTORY_LOCATION_ID%TYPE
16 INDEX BY MTL_SERIAL_NUMBERS.SERIAL_NUMBER%TYPE;
17 TYPE PARENT_SUB_TABLE IS TABLE OF MTL_ITEM_LOCATIONS.SUBINVENTORY_CODE%TYPE
18 INDEX BY MTL_SERIAL_NUMBERS.SERIAL_NUMBER%TYPE;
19
20
21 TYPE SUB_CODE_TABLE IS TABLE of Varchar2(30);
22 TYPE REVISION_TABLE IS TABLE of VARCHAR2(10);
23 TYPE UOM_TABLE IS TABLE OF VARCHAR2(3);
24 TYPE DATE_TABLE is Table of Date;
25 --Global table to hold attributes for Serialized Lot Items
26 g_lot_ser_attributes_tbl INV_LOT_SEL_ATTR.lot_sel_attributes_tbl_type;
27 g_lot_attributes_tbl INV_LOT_SEL_ATTR.lot_sel_attributes_tbl_type;
28
29
30 --********************************************************************************************
31 -- Procedure
32 -- Validate_Lots
33 -- Description:
34 -- This procedure will validate the records for lot transactions,
35 -- i.e., lot split, lot merge and lot translate.
36 -- It will validate that for lot trx, all records will have the same
37 -- organization and items. It will also validate that for lot
38 -- split, there is only one parent record and at least two resultant records.
39 -- For lot merge, it will have at least 2 parent records and 1 resultant records.
40 -- For lot translate, it will only have one parent record and 1 resultant records.
41 -- It will then call validate_start_lot to validate if the starting lot number is
42 -- a valid lot and validate_result_lot to validate the lot uniqueness of the
43 -- resultant lots.
44 -- Input Parameters:
45 -- p_transaction_type_id - The transaction type for the lot transactions
46 -- Lot Split - 81
47 -- Lot Merge - 82
48 -- Lot Translate - 83
49 -- p_st_org_id_tbl - the starting lot organization IDs. This is an array of the
50 -- of the organization ids for the starting lots.
51 -- p_rs_org_id_tbl -- this is an array of the organization ids for the resulting lots.
52 -- p_st_item_id_tbl -- this is an array of the inventory item ids for the starting lots.
53 -- p_rs_item_id_tbl -- this is an array of the inventory item ids for the resulting lots.
54 -- p_st_lot_num_tbl -- this is an array of starting lot numbers
55 -- p_rs_lot_num_tbl -- this is an array of resulting lot numbers
56 --
57 -- Output Parameters:
58 -- x_return_status -- return status, S- success, E - error, U- unexpected error
59 -- x_msg_count -- number of error message in the message stack.
60 -- x_msg_data -- the error message on the top of the message stack.
61 -- x_validation_status -- 'Y' if validation is successfull, 'N' if not successfull.
62 --
63 -- Dependency:
64 -- None.
65 --
66 -- Called By:
67 -- INV_LOT_TRX_VALIDATION_PVT (INVVLTVB.pls)
68 --************************************************************************************************
69 Procedure Validate_Lots(
70 x_return_status OUT NOCOPY VARCHAR2,
71 x_msg_count OUT NOCOPY NUMBER,
72 x_msg_data OUT NOCOPY VARCHAR2,
73 x_validation_status OUT NOCOPY VARCHAR2,
74 p_transaction_Type_id IN NUMBER,
75 p_st_org_id_tbl IN NUMBER_TABLE,
76 p_rs_org_id_tbl IN NUMBER_TABLE,
77 p_st_item_id_tbl IN NUMBER_TABLE,
78 p_rs_item_id_tbl IN NUMBER_TABLE,
79 p_st_lot_num_tbl IN LOT_NUMBER_TABLE,
80 p_rs_lot_num_tbl IN LOT_NUMBER_TABLE,
81 p_st_revision_tbl IN REVISION_TABLE,
82 p_rs_revision_tbl IN REVISION_TABLE,
83 p_st_quantity_tbl IN NUMBER_TABLE,
84 p_rs_quantity_tbl IN NUMBER_TABLE,
85 p_st_lot_exp_tbl IN DATE_TABLE,
86 p_rs_lot_exp_tbl IN DATE_TABLE
87 );
88
89 --********************************************************************************************
90 -- Procedure
91 -- Validate_Serials
92 -- Description:
93 -- This procedure will validate the records for lot transactions,
94 -- i.e., lot split, lot merge and lot translate for Serialized Lot Items
95 -- It validates wether the source serials match with the resulting serials. Also
96 -- checks if the serials are available by checking their group mark Id.
97 -- In case of lot translate transactions if the Items have changed then
98 -- INV_SERIAL_NUMBER_PUB.validate_serials is called to check for uniqueness and
99 -- create new serials if required.
100 -- Input Parameters:
101 -- p_transaction_type_id - The transaction type for the lot transactions
102 -- Lot Split - 81
103 -- Lot Merge - 82
104 -- Lot Translate - 83
105 -- p_st_org_id_tbl - the starting lot organization IDs. This is an array of the
106 -- of the organization ids for the starting lots.
107 -- p_rs_org_id_tbl -- this is an array of the organization ids for the resulting lots.
108 -- p_st_item_id_tbl -- this is an array of the inventory item ids for the starting lots.
109 -- p_rs_item_id_tbl -- this is an array of the inventory item ids for the resulting lots.
110 -- p_st_lot_num_tbl -- this is an array of starting lot numbers
111 -- p_rs_lot_num_tbl -- this is an array of resulting lot numbers
112 -- p_st_ser_grp_mark_id_tbl -- this is an array of GM IDs for the starting serials.
113 -- Output Parameters:
114 -- x_return_status -- return status, S- success, E - error, U- unexpected error
115 -- x_msg_count -- number of error message in the message stack.
116 -- x_msg_data -- the error message on the top of the message stack.
117 -- x_validation_status -- 'Y' if validation is successfull, 'N' if not successfull.
118 --
119 -- Dependency:
120 -- None.
121 --
122 -- Called By:
123 -- INV_LOT_TRX_VALIDATION_PVT (INVVLTVB.pls)
124 --************************************************************************************************
125
126
127 PROCEDURE validate_serials (
128 x_return_status OUT NOCOPY VARCHAR2
129 , x_msg_count OUT NOCOPY NUMBER
130 , x_msg_data OUT NOCOPY VARCHAR2
131 , x_validation_status OUT NOCOPY VARCHAR2
132 , p_transaction_type_id IN NUMBER
133 , p_st_org_id_tbl IN number_table
134 , p_rs_org_id_tbl IN number_table
135 , p_st_item_id_tbl IN number_table
136 , p_rs_item_id_tbl IN number_table
137 , p_rs_lot_num_tbl IN lot_number_table
138 , p_st_quantity_tbl IN number_table
139 , p_st_sub_code_tbl IN sub_code_table
140 , p_st_locator_id_tbl IN number_table
141 , p_st_ser_number_tbl IN serial_number_table
142 , p_st_ser_parent_lot_tbl IN parent_lot_table
143 , p_rs_ser_number_tbl IN serial_number_table
144 , p_st_ser_status_tbl IN number_table
145 , p_st_ser_grp_mark_id_tbl IN number_table
146 , p_st_ser_parent_sub_tbl IN parent_sub_table
147 , p_st_ser_parent_loc_tbl IN parent_loc_table
148 );
149
150
151 --********************************************************************************************
152 -- Procedure
153 -- Validate_Start_Lots
154 -- Description:
155 -- This procedure will validate the parent lot records for lot transactions,
156 -- i.e., lot split, lot merge and lot translate.
157 -- For lot split, it will validate if the lot is enabled for lot split transactions and
158 -- the lot is valid.
159 -- For lot merge, it will validate if the lot is enabled for lot merge transactions
160 -- and the lot is valid.
161 -- For Lot translate, it will validate if the item is lot control and the lot is valid.
162 --
163 -- Input Parameters:
164 -- p_transaction_type_id - The transaction type for the lot transactions
165 -- Lot Split - 81
166 -- Lot Merge - 82
167 -- Lot Translate - 83
168 -- p_lot_number - the starting lot number
169 -- p_inventory_item_id -- inventory item id for the staring lot
170 -- p_organization_id -- organization_id for the starting lot.
171 --
172 -- Output Parameters:
173 -- x_return_status -- return status, S- success, E - error, U- unexpected error
174 -- x_msg_count -- number of error message in the message stack.
175 -- x_msg_data -- the error message on the top of the message stack.
176 -- x_validation_status -- 'Y' if validation is successfull, 'N' if not successfull.
177 --
178 -- Dependency:
179 -- None.
180 --
181 -- Called By:
182 -- This procedure will be called by procedure Validate_Lot above.
183 -- For lot merge transactions, this will be called for each of the lots to be merged.
184 --
185 --************************************************************************************************
186
187 Procedure Validate_Start_Lot(
188 x_return_status OUT NOCOPY VARCHAR2,
189 x_msg_count OUT NOCOPY NUMBER,
190 x_msg_data OUT NOCOPY VARCHAR2,
191 x_validation_status OUT NOCOPY VARCHAR2,
192 p_transaction_type_id IN NUMBER,
193 p_lot_number IN VARCHAR2,
194 p_inventory_item_id IN NUMBER,
195 p_organization_id IN NUMBER
196 );
197
198 --********************************************************************************************
199 -- Procedure
200 -- Validate_Result_Lots
201 -- Description:
202 -- This procedure will validate the resultant lot records for lot transactions,
203 -- i.e., lot split, lot merge and lot translate.
204 -- It will validate for lot uniqueness of the resultant lots for lot split and translate.
205 -- It will validate if the resultant lot number is not one of the starting lot numbers
206 -- for lot merge.
207 --
208 -- Input Parameters:
209 -- p_transaction_type_id - The transaction type for the lot transactions
210 -- Lot Split - 81
211 -- Lot Merge - 82
212 -- Lot Translate - 83
213 -- p_st_lot_num_tbl -- array of starting lot numbers
214 -- p_rs_lot_num_tbl -- array of resultant lot numbers
215 -- p_inventory_item_id -- inventory item id for the resultant lot
216 -- p_organization_id -- organization_id for the resultant lot.
217 --
218 -- Output Parameters:
219 -- x_return_status -- return status, S- success, E - error, U- unexpected error
220 -- x_msg_count -- number of error message in the message stack.
221 -- x_msg_data -- the error message on the top of the message stack.
222 -- x_validation_status -- 'Y' if validation is successfull, 'N' if not successfull.
223 --
224 -- Dependency:
225 -- None.
226 --
227 -- Called By:
228 -- This procedure will be called by procedure Validate_Lot above.
229 -- For lot split transactions, this will be called for each resultant lots.
230 --
231 --************************************************************************************************
232
233 Procedure Validate_Result_Lot(
234 x_return_status OUT NOCOPY VARCHAR2,
235 x_msg_count OUT NOCOPY NUMBER,
236 x_msg_data OUT NOCOPY VARCHAR2,
237 x_validation_status OUT NOCOPY VARCHAR2,
238 p_transaction_type_id IN NUMBER,
239 p_st_lot_num_tbl IN LOT_NUMBER_TABLE,
240 p_rs_lot_num_tbl IN LOT_NUMBER_TABLE,
241 p_inventory_item_id IN NUMBER,
242 p_organization_id IN NUMBER
243 );
244
245 --********************************************************************************************
246 -- Procedure
247 -- Validate_Lot_Translate
248 -- Description:
249 -- This procedure will validate for lot translate, that either the lot number is changed
250 -- or the item is changed.
251 --
252 -- Input Parameters:
253 -- p_start_lot_number -- the starting lot number
254 -- p_start_inv_item_id -- the starting inventory item id.
255 -- p_result_lot_number -- the resultant lot number
256 -- p_result_inv_item_id -- the resulting inventory item id.
257 --
258 -- Output Parameters:
259 -- x_return_status -- return status, S- success, E - error, U- unexpected error
260 -- x_msg_count -- number of error message in the message stack.
261 -- x_msg_data -- the error message on the top of the message stack.
262 -- x_validation_status -- 'Y' if validation is successfull, 'N' if not successfull.
263 --
264 -- Dependency:
265 -- None.
266 --
267 -- Called By:
268 -- This procedure will be called by procedure Validate_Lot above.
269 --
270 --************************************************************************************************
271
272 Procedure Validate_Lot_Translate(
273 x_return_status OUT NOCOPY VARCHAR2,
274 x_msg_count OUT NOCOPY NUMBER,
275 x_msg_data OUT NOCOPY VARCHAR2,
276 x_validation_status OUT NOCOPY VARCHAR2,
277 p_start_lot_number IN VARCHAR2,
278 p_start_inv_item_id IN NUMBER,
279 p_result_lot_number IN VARCHAR2,
280 p_result_inv_item_id IN NUMBER
281 );
282
283
284 --********************************************************************************************
285 -- Procedure
286 -- Validate_Lpn_Info
287 -- Description:
288 -- Perform basic validations for the LPNs present in the Lot transactions.
289 --
290 --
291 -- Input Parameters:
292 -- p_st_lpn_id_tbl -- the starting lpn ids
293 -- p_rs_lpn_id_tbl -- the resulting lpn ids
294 --
295 -- Output Parameters:
296 -- x_return_status -- return status, S- success, E - error, U- unexpected error
297 -- x_msg_count -- number of error message in the message stack.
298 -- x_msg_data -- the error message on the top of the message stack.
299 -- x_validation_status -- 'Y' if validation is successfull, 'N' if not successfull.
300 --
301 -- Dependency:
302 -- None.
303 --
304 -- Called By:
305 -- This procedure will be called by procedure validate_lot_split_trx,validate_lot_merge_trx
306 -- and validate_lot_translate_trx.
307 --************************************************************************************************
308
309
310 PROCEDURE validate_lpn_info (
311 x_return_status OUT NOCOPY VARCHAR2
312 , x_msg_count OUT NOCOPY NUMBER
313 , x_msg_data OUT NOCOPY VARCHAR2
314 , x_validation_status OUT NOCOPY VARCHAR2
315 , p_st_lpn_id_tbl IN number_table
316 , p_rs_lpn_id_tbl IN number_table
317 , p_st_org_id_tbl IN number_table
318 , p_rs_org_id_tbl IN number_table
319 , p_rs_sub_code_tbl IN sub_code_table
320 , p_rs_locator_id_tbl IN number_table
321 );
322
323
324 --********************************************************************************************
325 -- Procedure
326 -- Validate_Material_Status
327 -- Description:
328 -- This procedure will validate if the lot split, lot merge or lot translate
329 -- are enabled for the item, subinventory, locator and lot based on the status
330 -- of the subinventory, locator, and lot.
331 --
332 -- Input Parameters:
333 -- p_transaction_type_id - The transaction type for the lot transactions
334 -- Lot Split - 81
335 -- Lot Merge - 82
336 -- Lot Translate - 83
337 -- p_lot_number - the starting lot number
338 -- p_inventory_item_id -- inventory item id for the staring lot
339 -- p_organization_id -- organization_id for the starting lot.
340 -- p_subinventory_code -- the subinventory where the lot resides
341 -- p_locator_id -- the locator where the lot resides
342 -- p_status_id -- the status of the lot number
343 --
344 -- Output Parameters:
345 -- x_return_status -- return status, S- success, E - error, U- unexpected error
346 -- x_msg_count -- number of error message in the message stack.
347 -- x_msg_data -- the error message on the top of the message stack.
348 -- x_validation_status -- 'Y' if validation is successfull, 'N' if not successfull.
349 --
350 -- Dependency:
351 -- INV_MATERIAL_STATUS_GROUP (INVMSGRB.pls and INVMSGRS.pls).
352 --
353 -- Called By:
354 -- This procedure will be called by INV_LOT_TRX_VALIDATION_PVT (INVVLTVB.pls)
355 --
356 --************************************************************************************************
357
358 Procedure Validate_Material_Status(
359 x_return_status OUT NOCOPY VARCHAR2,
360 x_msg_count OUT NOCOPY NUMBER,
361 x_msg_data OUT NOCOPY VARCHAR2,
362 x_validation_status OUT NOCOPY VARCHAR2,
363 p_transaction_type_id IN NUMBER,
364 p_organization_id IN NUMBER,
365 p_inventory_item_id IN NUMBER,
366 p_lot_number IN VARCHAR2,
367 p_subinventory_code IN VARCHAR2,
368 p_locator_id IN NUMBER,
369 p_status_id IN NUMBER
370 );
371
372 --********************************************************************************************
373 -- Procedure
374 -- Validate_Cost_Groups
375 -- Description:
376 -- This procedure will validate cost group of the parent lots.
377 -- If the cost group of the parent lots are not populated, this procedure will call the
378 -- cost group engine to get the cost group of the lot.
379 -- This procedure will also return the cost group of the resultant lots.
380 -- Resultan lots will have the same cost group as the parent lots.
381 -- For lot merge, this procedure will check, if all the parent lots have the same cost groups.
382 -- If any of the parent lots have different cost groups, it will throw an error.
383 --
384 -- If the user populate the parent cost groups, it will check if the cost group is the same
385 -- as the one from the cost group engine. If they are different, this procedure also
386 -- returns error.
387 --
388 -- Input Parameters:
389 -- p_transaction_type_id - The transaction type for the lot transactions
390 -- Lot Split - 81
391 -- Lot Merge - 82
392 -- Lot Translate - 83
393 -- p_transaction_Action_id - the transaction action id for lot transactions
394 -- Lot Split - 40
395 -- Lot Merge - 41
396 -- Lot Translate - 42
397 -- p_st_org_id_tbl -- array of starting lot organization id.
398 -- p_st_item_id_tbl -- array of starting lot inventory item id
399 -- p_st_sub_code_tbl -- array of subinventory code for the starting lot.
400 -- p_st_loc_id_tbl -- array of locator id for the starting lot
401 -- p_st_lot_num_tbl -- array of staring lot numbers
402 -- p_st_cost_group_tbl -- array of cost group for starting lot.
403 -- p_st_revision_tbl -- array of revision for the starting lots.
404 -- p_st_lpn_id_tbl -- array of the lpn id for the starting lots.
405 -- p_rs_org_id_tbl -- array of resultant lot organization id.
406 -- p_rs_item_id_tbl -- array of resultant lot inventory item id
407 -- p_rs_sub_code_tbl -- array of subinventory code for the resultant lot.
408 -- p_rs_loc_id_tbl -- array of locator id for the resultant lot
409 -- p_rs_lot_num_tbl -- array of resultant lot numbers
410 -- p_rs_cost_group_tbl -- array of cost group for resultant lot.
411 -- p_rs_revision_tbl -- array of revision for the resultant lots.
412 --
413 -- Output Parameters:
414 -- x_return_status -- return status, S- success, E - error, U- unexpected error
415 -- x_msg_count -- number of error message in the message stack.
416 -- x_msg_data -- the error message on the top of the message stack.
417 -- x_validation_status -- 'Y' if validation is successfull, 'N' if not successfull.
418 --
419 -- Dependency:
420 -- INV_COST_GROUP_UPDATE (INVCGUPB.pls and INVCGUPS.pls).
421 --
422 -- Called By:
423 -- This procedure will be called by INV_LOT_TRX_VALIDATION_PVT (INVVLTVB.pls)
424 --
425 --************************************************************************************************
426
427 Procedure Validate_Cost_Groups(
428 x_rs_cost_group_tbl IN OUT NOCOPY NUMBER_TABLE,
429 x_return_status OUT NOCOPY VARCHAR2,
430 x_msg_count OUT NOCOPY NUMBER,
431 x_msg_data OUT NOCOPY VARCHAR2,
432 x_validation_status OUT NOCOPY VARCHAR2,
433 p_transaction_type_id IN NUMBER,
434 p_transaction_action_id IN NUMBER,
435 p_st_org_id_tbl IN NUMBER_TABLE,
436 p_st_item_id_tbl IN NUMBER_TABLE,
437 p_st_sub_code_tbl IN SUB_CODE_TABLE,
438 p_st_loc_id_tbl IN NUMBER_TABLE,
439 p_st_lot_num_tbl IN LOT_NUMBER_TABLE,
440 p_st_cost_group_tbl IN NUMBER_TABLE,
441 p_st_revision_tbl IN REVISION_TABLE,
442 p_st_lpn_id_tbl IN NUMBER_TABLE,
443 p_rs_org_id_tbl IN NUMBER_TABLE,
444 p_rs_item_id_tbl IN NUMBER_TABLE,
445 p_rs_sub_code_tbl IN SUB_CODE_TABLE,
446 p_rs_loc_id_tbl IN NUMBER_TABLE,
447 p_rs_lot_num_tbl IN LOT_NUMBER_TABLE,
448 p_rs_revision_tbl IN REVISION_TABLE,
449 p_rs_lpn_id_tbl IN NUMBER_TABLE
450 );
451
452 --********************************************************************************************
453 -- Procedure
454 -- Validate_Quantity
455 -- Description:
456 -- This procedure will validate the quantity for lot split, merge or translate.
457 -- For lot split, it will validate if the parent lot quantity is the same as the total quantity
458 -- of the resultant lot
459 -- For lot merge, it will validate if the total qty of the parent lots match the quantity of the
460 -- resultant lot.
461 -- For Lot translate, it will validate if qty of the parent lot = qty of the resultant lot.
462 --
463 -- All qty is converted to the primary_qty (qty with primary unit of measure).
464
465 -- CHANGES FOR OSFM SUPPORT TO SERIALIZED LOT ITEMS :-
466 -- p_st_ser_number_tbl.COUNT should be equal to the starting lot quantity.
467 -- p_rs_ser_number_tbl.COUNT should equal the resulting lot quantity.
468 -- Individual lot quantity should match with the number of serials for that Lot.
469 -- The quantity in primary unit of measure should not be fractional.
470 -- Get the immediated LPN quantity ny passing the appropriate value for is_serial_control
471 -- Call the get_immediate_LPN_quantity for lot-merge transactions also.
472
473 -- Input Parameters:
474 -- p_transaction_type_id - The transaction type for the lot transactions
475 -- Lot Split - 81
476 -- Lot Merge - 82
477 -- Lot Translate - 83
478 -- p_st_org_id_tbl -- array of starting lot organization id.
479 -- p_st_item_id_tbl -- array of starting lot inventory item id
480 -- p_st_sub_code_tbl -- array of subinventory code for the starting lot.
481 -- p_st_loc_id_tbl -- array of locator id for the starting lot
482 -- p_st_lot_num_tbl -- array of staring lot numbers
483 -- p_st_cost_group_tbl -- array of cost group for starting lot.
484 -- p_st_revision_tbl -- array of revision for the starting lots.
485 -- p_st_lpn_id_tbl -- array of the lpn id for the starting lots.
486 -- p_st_quantity_tbl -- array of the quantity for starting lots.
487 -- p_st_uom_tbl -- array of the quantity for the starting lots.
488 -- p_rs_org_id_tbl -- array of resultant lot organization id.
489 -- p_rs_item_id_tbl -- array of resultant lot inventory item id
490 -- p_rs_sub_code_tbl -- array of subinventory code for the resultant lot.
491 -- p_rs_loc_id_tbl -- array of locator id for the resultant lot
492 -- p_rs_lot_num_tbl -- array of resultant lot numbers
493 -- p_rs_cost_group_tbl -- array of cost group for resultant lot.
494 -- p_rs_quantity_tbl -- array of the quantity for resultant lots.
495 -- p_rs_uom_tbl -- array of the quantity for the resultant lots.
496 --
497 -- Output Parameters:
498 -- x_return_status -- return status, S- success, E - error, U- unexpected error
499 -- x_msg_count -- number of error message in the message stack.
500 -- x_msg_data -- the error message on the top of the message stack.
501 -- x_validation_status -- 'Y' if validation is successfull, 'N' if not successfull.
502 --
503 -- Dependency:
504 -- INV_UM_CONVERT (INVUMCNB.pls and INVUMCNS.pls).
505 -- INV_TXN_VALIDATIONS (INVMWAVB.pls and INVMWAVS.pls).
506 --
507 -- Called By:
508 -- This procedure will be called by INV_LOT_TRX_VALIDATION_PVT (INVVLTVB.pls )
509 --
510 --************************************************************************************************
511
512 Procedure Validate_Quantity(
513 x_return_status OUT NOCOPY VARCHAR2,
514 x_msg_count OUT NOCOPY NUMBER,
515 x_msg_data OUT NOCOPY VARCHAR2,
516 x_validation_status OUT NOCOPY VARCHAR2,
517 p_transaction_type_id IN NUMBER,
518 p_st_org_id_tbl IN NUMBER_TABLE,
519 p_st_item_id_tbl IN NUMBER_TABLE,
520 p_st_sub_code_tbl IN SUB_CODE_TABLE,
521 p_st_loc_id_tbl IN NUMBER_TABLE,
522 p_st_lot_num_tbl IN LOT_NUMBER_TABLE,
523 p_st_cost_group_tbl IN NUMBER_TABLE,
524 p_st_revision_tbl IN REVISION_TABLE,
525 p_st_lpn_id_Tbl IN NUMBER_TABLE,
526 p_st_quantity_tbl IN NUMBER_TABLE,
527 p_st_uom_tbl IN UOM_TABLE,
528 --Added the following two tables for validations relevant to serials.
529 p_st_ser_number_tbl IN SERIAL_NUMBER_TABLE,
530 p_st_ser_parent_lot_tbl IN PARENT_LOT_TABLE,
531 p_rs_org_id_tbl IN NUMBER_TABLE,
532 p_rs_item_id_tbl IN NUMBER_TABLE,
533 p_rs_sub_code_tbl IN SUB_CODE_TABLE,
534 p_rs_loc_id_tbl IN NUMBER_TABLE,
535 p_rs_lot_num_tbl IN LOT_NUMBER_TABLE,
536 p_rs_cost_group_tbl IN NUMBER_TABLE,
537 p_rs_revision_tbl IN REVISION_TABLE,
538 p_rs_lpn_id_tbl IN NUMBER_TABLE,
539 p_rs_quantity_tbl IN NUMBER_TABLE,
540 p_rs_uom_tbl IN UOM_TABLE,
541 p_rs_ser_number_tbl IN SERIAL_NUMBER_TABLE,
542 p_rs_ser_parent_lot_tbl IN parent_lot_table
543 );
544
545
546 --********************************************************************************************
547 -- Procedure
548 -- Validate_Attributes
549 -- Description:
550 -- This procedure will validate the attributes of the resultant lots.
551 -- If the attributes of the resultant lots is not populated, and if the parent lot
552 -- has attributes, the resultant lot attributes will be derived from the parent lot.
553 -- If the parent lot does not have attributes, the resultant lot attributes will be
554 -- derived from default lot attributes.
555 -- THe procedure will then validate the lot attributes against the value set of
556 -- each segment in the attributes by calling descriptive flexfield validation APIs.
557 --
558 -- Input Parameters:
559 -- p_lot_number - the starting lot number
560 -- p_inventory_item_id -- inventory item id for the staring lot
561 -- p_organization_id -- organization_id for the starting lot.
562 -- p_parent_lot_attr_tbl -- the lot attributes data for the parent lot
563 -- p_result_lot_attr_tbl -- the lot attributes data for the resultant lot.
564 --
565 -- Output Parameters:
566 -- x_return_status -- return status, S- success, E - error, U- unexpected error
567 -- x_msg_count -- number of error message in the message stack.
568 -- x_msg_data -- the error message on the top of the message stack.
569 -- x_validation_status -- 'Y' if validation is successfull, 'N' if not successfull.
570 --
571 -- Dependency:
572 -- None.
573 --
574 -- Called By:
575 -- This procedure will be called by INV_LOT_TRX_VALIDATION_PVT (INVVLTVB.pls).
576 --
577 --************************************************************************************************
578
579 Procedure validate_attributes
580 (
581 x_return_status OUT NOCOPY VARCHAR2,
582 x_msg_count OUT NOCOPY NUMBER,
583 x_msg_data OUT NOCOPY VARCHAR2,
584 x_validation_status OUT NOCOPY VARCHAR2,
585 x_lot_attr_tbl OUT NOCOPY Inv_Lot_Sel_Attr.Lot_Sel_Attributes_Tbl_Type,
586 p_lot_number IN VARCHAR2,
587 p_organization_id IN NUMBER,
588 p_inventory_item_id IN NUMBER,
589 p_parent_lot_attr_tbl IN inv_lot_sel_attr.lot_sel_attributes_tbl_type,
590 p_result_lot_attr_tbl IN
591 inv_lot_sel_attr.lot_sel_attributes_tbl_type,
592 p_transaction_type_id IN NUMBER
593 );
594
595
596 --********************************************************************************************
597 -- Procedure
598 -- Validate_Serial_Attributes
599 -- Description:
600 -- This procedure will validate the attributes of the resulting serials.
601 -- If the attributes of the resultant serials is not populated, then we DO NOT
602 -- derive the attribute from the parent serial.
603 -- If the resulting serial does not have attributes, the resultant serial attributes will be
604 -- derived from default serial attributes.
605 -- THe procedure will then validate the serial attributes against the value set of
606 -- each segment in the attributes by calling descriptive flexfield validation APIs.
607 --
608 -- Input Parameters:
609 -- p_ser_number - the serial number
610 -- p_inventory_item_id -- inventory item id for the staring lot
611 -- p_organization_id -- organization_id for the starting lot.
612 -- p_result_ser_attr_tbl -- the serial attributes data for the resultant serial.
613 --
614 -- Output Parameters:
615 -- x_return_status -- return status, S- success, E - error, U- unexpected error
616 -- x_msg_count -- number of error message in the message stack.
617 -- x_msg_data -- the error message on the top of the message stack.
618 -- x_validation_status -- 'Y' if validation is successfull, 'N' if not successfull.
619 -- x_ser_attr_tbl -- populated and validated attributes.
620 -- Dependency:
621 -- None.
622 --
623 -- Called By:
624 -- This procedure will be called by INV_TXN_MANAGER_GRP (INVTXGGB.pls)
625 --
626 --************************************************************************************************
627
628 PROCEDURE validate_serial_attributes (
629 x_return_status OUT NOCOPY VARCHAR2
630 , x_msg_count OUT NOCOPY NUMBER
631 , x_msg_data OUT NOCOPY VARCHAR2
632 , x_validation_status OUT NOCOPY VARCHAR2
633 , x_ser_attr_tbl OUT NOCOPY inv_lot_sel_attr.lot_sel_attributes_tbl_type
634 , p_ser_number IN VARCHAR2
635 , p_organization_id IN NUMBER
636 , p_inventory_item_id IN NUMBER
637 , p_result_ser_attr_tbl IN inv_lot_sel_attr.lot_sel_attributes_tbl_type
638 );
639
640 --************************************************************************************************
641 -- Procedure
642 -- Validate_Organization
643 --
644 -- Description:
645 -- This procedure will validate the organization, checks if the Organization chosen
646 -- has a open period and also check if the acct_period_id pass is valid.
647 --
648 -- Input Parameters:
649 -- p_organization_id -- organization_Id of the starting lot.
650 -- p_period_id -- account period id of the organization.
651 --
652 -- Output Parameters:
653 -- x_return_status -- return status, S- success, E - error, U- unexpected error
654 -- x_msg_count -- number of error message in the message stack.
655 -- x_msg_data -- the error message on the top of the message stack.
656 -- x_validation_status -- 'Y' if validation is successfull, 'N' if not successfull.
657 --
658 -- Dependency:
659 -- INV_INV_LOVS (INVINVLS.pls and INVINVLB.pls)
660 -- INVTTMTX (INVTTMTS.pls and INVTTMTB.pls)
661 --
662 -- Called By:
663 -- This procedure will be called by INV_LOT_TRX_VALIDATION_PVT (INVVLTVB.pls)
664
665 --************************************************************************************************
666
667 Procedure Validate_Organization(
668 x_return_status OUT NOCOPY VARCHAR2,
669 x_msg_count OUT NOCOPY NUMBER,
670 x_msg_data OUT NOCOPY VARCHAR2,
671 x_validation_status OUT NOCOPY VARCHAR2,
672 p_organization_id IN NUMBER,
673 p_period_tbl IN NUMBER_TABLE
674 );
675
676 PROCEDURE compute_lot_expiration
677 (
678 x_return_status OUT NOCOPY VARCHAR2,
679 x_msg_count OUT NOCOPY NUMBER,
680 x_msg_data OUT NOCOPY VARCHAR2,
681 p_parent_id IN NUMBER,
682 p_transaction_type_id IN NUMBER,
683 p_item_id IN NUMBER,
684 p_organization_id IN NUMBER,
685 p_st_lot_num IN VARCHAR2,
686 p_rs_lot_num_tbl IN LOT_NUMBER_TABLE,
687 p_rs_lot_exp_tbl IN OUT NOCOPY date_table
688 );
689
690 procedure get_org_info
691 (
692 x_wms_installed OUT NOCOPY VARCHAR2,
693 x_wsm_enabled OUT NOCOPY VARCHAR2,
694 x_wms_enabled OUT NOCOPY VARCHAR2,
695 x_return_status OUT NOCOPY VARCHAR2,
696 x_msg_count OUT NOCOPY NUMBER,
697 x_msg_data OUT NOCOPY VARCHAR2,
698 p_organization_id IN NUMBER
699 );
700 --************************************************************************************************
701 -- Procedure
702 -- Update_Item_serial
703 --
704 -- Description:
705 -- This API is for the requirement from OSFM.
706 -- This procedure can possibly update the inventory_item_id (assembly), job number (Lot),
707 -- operation seq# and intraoperation step for the serial number passed.
708 --
709 -- Input Parameters:
710 -- p_org_id -- Organization Id for the Serial
711 -- p_item_id -- Current Item Id (assembly)
712 -- p_to_item_id -- New Inventory Item Id (Can be NULL)
713 -- p_wip_entity_id -- Current Job (Lot)
714 -- p_to_wip_entity_id -- New Job (Lot)
715 -- p_to_operation_sequence -- New operation sequence
716 -- p_intraoperation_step_type -- New intraoperation step type
717 --
718 --
719 -- Output Parameters:
720 -- x_return_status -- return status, S- success, E - error, U- unexpected error
721 -- x_msg_count -- number of error message in the message stack.
722 -- x_msg_data -- the error message on the top of the message stack.
723 -- x_validation_status -- 'Y' if validation is successfull, 'N' if not successfull.
724 --
725 -- Dependency:
726 --
727 -- Called By:
728 -- This procedure will be called by OSFM from their forms.
729
730 --************************************************************************************************
731
732 PROCEDURE update_item_serial (
733 x_msg_count OUT NOCOPY VARCHAR2
734 ,x_return_status OUT NOCOPY VARCHAR2
735 ,x_msg_data OUT NOCOPY VARCHAR2
736 ,x_validation_status OUT NOCOPY VARCHAR2
737 ,p_org_id IN NUMBER
738 ,p_item_id IN NUMBER
739 ,p_to_item_id IN NUMBER DEFAULT NULL
740 ,p_wip_entity_id IN NUMBER
741 ,p_to_wip_entity_id IN NUMBER DEFAULT NULL
742 ,p_to_operation_sequence IN NUMBER DEFAULT NULL
743 ,p_intraoperation_step_type IN NUMBER DEFAULT NULL
744 );
745 END INV_LOT_TRX_VALIDATION_PUB;
[Home] [Help]