4 -- Global constant holding the package name
1 PACKAGE INV_LOT_TRX_VALIDATION_PUB AUTHID CURRENT_USER AS
2 /* $Header: INVPLTVS.pls 120.6.12020000.2 2012/07/11 09:17:08 rdudani ship $ */
3
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
136 , p_rs_item_id_tbl IN number_table
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
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 --
175 -- x_msg_data -- the error message on the top of the message stack.
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.
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:
308
305 -- This procedure will be called by procedure validate_lot_split_trx,validate_lot_merge_trx
306 -- and validate_lot_translate_trx.
307 --************************************************************************************************
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 p_lpn_id IN NUMBER DEFAULT NULL -- bug 14269152
371 );
372
373 --********************************************************************************************
374 -- Procedure
375 -- Validate_Cost_Groups
376 -- Description:
377 -- This procedure will validate cost group of the parent lots.
378 -- If the cost group of the parent lots are not populated, this procedure will call the
379 -- cost group engine to get the cost group of the lot.
380 -- This procedure will also return the cost group of the resultant lots.
381 -- Resultan lots will have the same cost group as the parent lots.
382 -- For lot merge, this procedure will check, if all the parent lots have the same cost groups.
383 -- If any of the parent lots have different cost groups, it will throw an error.
384 --
385 -- If the user populate the parent cost groups, it will check if the cost group is the same
386 -- as the one from the cost group engine. If they are different, this procedure also
387 -- returns error.
388 --
389 -- Input Parameters:
390 -- p_transaction_type_id - The transaction type for the lot transactions
391 -- Lot Split - 81
392 -- Lot Merge - 82
393 -- Lot Translate - 83
394 -- p_transaction_Action_id - the transaction action id for lot transactions
395 -- Lot Split - 40
396 -- Lot Merge - 41
397 -- Lot Translate - 42
398 -- p_st_org_id_tbl -- array of starting lot organization id.
399 -- p_st_item_id_tbl -- array of starting lot inventory item id
400 -- p_st_sub_code_tbl -- array of subinventory code for the starting lot.
401 -- p_st_loc_id_tbl -- array of locator id for the starting lot
402 -- p_st_lot_num_tbl -- array of staring lot numbers
403 -- p_st_cost_group_tbl -- array of cost group for starting lot.
404 -- p_st_revision_tbl -- array of revision for the starting lots.
405 -- p_st_lpn_id_tbl -- array of the lpn id for the starting lots.
406 -- p_rs_org_id_tbl -- array of resultant lot organization id.
407 -- p_rs_item_id_tbl -- array of resultant lot inventory item id
408 -- p_rs_sub_code_tbl -- array of subinventory code for the resultant lot.
409 -- p_rs_loc_id_tbl -- array of locator id for the resultant lot
410 -- p_rs_lot_num_tbl -- array of resultant lot numbers
411 -- p_rs_cost_group_tbl -- array of cost group for resultant lot.
412 -- p_rs_revision_tbl -- array of revision for the resultant lots.
413 --
414 -- Output Parameters:
415 -- x_return_status -- return status, S- success, E - error, U- unexpected error
416 -- x_msg_count -- number of error message in the message stack.
420 -- Dependency:
417 -- x_msg_data -- the error message on the top of the message stack.
418 -- x_validation_status -- 'Y' if validation is successfull, 'N' if not successfull.
419 --
421 -- INV_COST_GROUP_UPDATE (INVCGUPB.pls and INVCGUPS.pls).
422 --
423 -- Called By:
424 -- This procedure will be called by INV_LOT_TRX_VALIDATION_PVT (INVVLTVB.pls)
425 --
426 --************************************************************************************************
427
428 Procedure Validate_Cost_Groups(
429 x_rs_cost_group_tbl IN OUT NOCOPY NUMBER_TABLE,
430 x_return_status OUT NOCOPY VARCHAR2,
431 x_msg_count OUT NOCOPY NUMBER,
432 x_msg_data OUT NOCOPY VARCHAR2,
433 x_validation_status OUT NOCOPY VARCHAR2,
434 p_transaction_type_id IN NUMBER,
435 p_transaction_action_id IN NUMBER,
436 p_st_org_id_tbl IN NUMBER_TABLE,
437 p_st_item_id_tbl IN NUMBER_TABLE,
438 p_st_sub_code_tbl IN SUB_CODE_TABLE,
439 p_st_loc_id_tbl IN NUMBER_TABLE,
440 p_st_lot_num_tbl IN LOT_NUMBER_TABLE,
441 p_st_cost_group_tbl IN NUMBER_TABLE,
442 p_st_revision_tbl IN REVISION_TABLE,
443 p_st_lpn_id_tbl IN NUMBER_TABLE,
444 p_rs_org_id_tbl IN NUMBER_TABLE,
445 p_rs_item_id_tbl IN NUMBER_TABLE,
446 p_rs_sub_code_tbl IN SUB_CODE_TABLE,
447 p_rs_loc_id_tbl IN NUMBER_TABLE,
448 p_rs_lot_num_tbl IN LOT_NUMBER_TABLE,
449 p_rs_revision_tbl IN REVISION_TABLE,
450 p_rs_lpn_id_tbl IN NUMBER_TABLE
451 );
452
453 --********************************************************************************************
454 -- Procedure
455 -- Validate_Quantity
456 -- Description:
457 -- This procedure will validate the quantity for lot split, merge or translate.
458 -- For lot split, it will validate if the parent lot quantity is the same as the total quantity
459 -- of the resultant lot
460 -- For lot merge, it will validate if the total qty of the parent lots match the quantity of the
461 -- resultant lot.
462 -- For Lot translate, it will validate if qty of the parent lot = qty of the resultant lot.
463 --
464 -- All qty is converted to the primary_qty (qty with primary unit of measure).
465
466 -- CHANGES FOR OSFM SUPPORT TO SERIALIZED LOT ITEMS :-
467 -- p_st_ser_number_tbl.COUNT should be equal to the starting lot quantity.
468 -- p_rs_ser_number_tbl.COUNT should equal the resulting lot quantity.
469 -- Individual lot quantity should match with the number of serials for that Lot.
470 -- The quantity in primary unit of measure should not be fractional.
471 -- Get the immediated LPN quantity ny passing the appropriate value for is_serial_control
472 -- Call the get_immediate_LPN_quantity for lot-merge transactions also.
473
474 -- Input Parameters:
475 -- p_transaction_type_id - The transaction type for the lot transactions
476 -- Lot Split - 81
477 -- Lot Merge - 82
478 -- Lot Translate - 83
479 -- p_st_org_id_tbl -- array of starting lot organization id.
480 -- p_st_item_id_tbl -- array of starting lot inventory item id
481 -- p_st_sub_code_tbl -- array of subinventory code for the starting lot.
482 -- p_st_loc_id_tbl -- array of locator id for the starting lot
483 -- p_st_lot_num_tbl -- array of staring lot numbers
484 -- p_st_cost_group_tbl -- array of cost group for starting lot.
485 -- p_st_revision_tbl -- array of revision for the starting lots.
486 -- p_st_lpn_id_tbl -- array of the lpn id for the starting lots.
487 -- p_st_quantity_tbl -- array of the quantity for starting lots.
488 -- p_st_uom_tbl -- array of the quantity for the starting lots.
489 -- p_rs_org_id_tbl -- array of resultant lot organization id.
490 -- p_rs_item_id_tbl -- array of resultant lot inventory item id
491 -- p_rs_sub_code_tbl -- array of subinventory code for the resultant lot.
492 -- p_rs_loc_id_tbl -- array of locator id for the resultant lot
493 -- p_rs_lot_num_tbl -- array of resultant lot numbers
494 -- p_rs_cost_group_tbl -- array of cost group for resultant lot.
495 -- p_rs_quantity_tbl -- array of the quantity for resultant lots.
496 -- p_rs_uom_tbl -- array of the quantity for the resultant lots.
497 --
498 -- Output Parameters:
499 -- x_return_status -- return status, S- success, E - error, U- unexpected error
500 -- x_msg_count -- number of error message in the message stack.
501 -- x_msg_data -- the error message on the top of the message stack.
502 -- x_validation_status -- 'Y' if validation is successfull, 'N' if not successfull.
503 --
504 -- Dependency:
505 -- INV_UM_CONVERT (INVUMCNB.pls and INVUMCNS.pls).
506 -- INV_TXN_VALIDATIONS (INVMWAVB.pls and INVMWAVS.pls).
507 --
508 -- Called By:
509 -- This procedure will be called by INV_LOT_TRX_VALIDATION_PVT (INVVLTVB.pls )
510 --
511 --************************************************************************************************
512
513 Procedure Validate_Quantity(
514 x_return_status OUT NOCOPY VARCHAR2,
515 x_msg_count OUT NOCOPY NUMBER,
516 x_msg_data OUT NOCOPY VARCHAR2,
517 x_validation_status OUT NOCOPY VARCHAR2,
518 p_transaction_type_id IN NUMBER,
519 p_st_org_id_tbl IN NUMBER_TABLE,
520 p_st_item_id_tbl IN NUMBER_TABLE,
521 p_st_sub_code_tbl IN SUB_CODE_TABLE,
522 p_st_loc_id_tbl IN NUMBER_TABLE,
523 p_st_lot_num_tbl IN LOT_NUMBER_TABLE,
524 p_st_cost_group_tbl IN NUMBER_TABLE,
525 p_st_revision_tbl IN REVISION_TABLE,
526 p_st_lpn_id_Tbl IN NUMBER_TABLE,
527 p_st_quantity_tbl IN NUMBER_TABLE,
528 p_st_uom_tbl IN UOM_TABLE,
529 --Added the following two tables for validations relevant to serials.
530 p_st_ser_number_tbl IN SERIAL_NUMBER_TABLE,
531 p_st_ser_parent_lot_tbl IN PARENT_LOT_TABLE,
532 p_rs_org_id_tbl IN NUMBER_TABLE,
533 p_rs_item_id_tbl IN NUMBER_TABLE,
534 p_rs_sub_code_tbl IN SUB_CODE_TABLE,
535 p_rs_loc_id_tbl IN NUMBER_TABLE,
539 p_rs_lpn_id_tbl IN NUMBER_TABLE,
536 p_rs_lot_num_tbl IN LOT_NUMBER_TABLE,
537 p_rs_cost_group_tbl IN NUMBER_TABLE,
538 p_rs_revision_tbl IN REVISION_TABLE,
540 p_rs_quantity_tbl IN NUMBER_TABLE,
541 p_rs_uom_tbl IN UOM_TABLE,
542 p_rs_ser_number_tbl IN SERIAL_NUMBER_TABLE,
543 p_rs_ser_parent_lot_tbl IN parent_lot_table
544 );
545
546
547 --********************************************************************************************
548 -- Procedure
549 -- Validate_Attributes
550 -- Description:
551 -- This procedure will validate the attributes of the resultant lots.
552 -- If the attributes of the resultant lots is not populated, and if the parent lot
553 -- has attributes, the resultant lot attributes will be derived from the parent lot.
554 -- If the parent lot does not have attributes, the resultant lot attributes will be
555 -- derived from default lot attributes.
556 -- THe procedure will then validate the lot attributes against the value set of
557 -- each segment in the attributes by calling descriptive flexfield validation APIs.
558 --
559 -- Input Parameters:
560 -- p_lot_number - the starting lot number
561 -- p_inventory_item_id -- inventory item id for the staring lot
562 -- p_organization_id -- organization_id for the starting lot.
563 -- p_parent_lot_attr_tbl -- the lot attributes data for the parent lot
564 -- p_result_lot_attr_tbl -- the lot attributes data for the resultant lot.
565 --
566 -- Output Parameters:
567 -- x_return_status -- return status, S- success, E - error, U- unexpected error
568 -- x_msg_count -- number of error message in the message stack.
569 -- x_msg_data -- the error message on the top of the message stack.
570 -- x_validation_status -- 'Y' if validation is successfull, 'N' if not successfull.
571 --
572 -- Dependency:
573 -- None.
574 --
575 -- Called By:
576 -- This procedure will be called by INV_LOT_TRX_VALIDATION_PVT (INVVLTVB.pls).
577 --
578 --************************************************************************************************
579
580 Procedure validate_attributes
581 (
582 x_return_status OUT NOCOPY VARCHAR2,
583 x_msg_count OUT NOCOPY NUMBER,
584 x_msg_data OUT NOCOPY VARCHAR2,
585 x_validation_status OUT NOCOPY VARCHAR2,
586 x_lot_attr_tbl OUT NOCOPY Inv_Lot_Sel_Attr.Lot_Sel_Attributes_Tbl_Type,
587 p_lot_number IN VARCHAR2,
588 p_organization_id IN NUMBER,
589 p_inventory_item_id IN NUMBER,
590 p_parent_lot_attr_tbl IN inv_lot_sel_attr.lot_sel_attributes_tbl_type,
591 p_result_lot_attr_tbl IN
592 inv_lot_sel_attr.lot_sel_attributes_tbl_type,
593 p_transaction_type_id IN NUMBER
594 );
595
596
597 --********************************************************************************************
598 -- Procedure
599 -- Validate_Serial_Attributes
600 -- Description:
601 -- This procedure will validate the attributes of the resulting serials.
602 -- If the attributes of the resultant serials is not populated, then we DO NOT
603 -- derive the attribute from the parent serial.
604 -- If the resulting serial does not have attributes, the resultant serial attributes will be
605 -- derived from default serial attributes.
606 -- THe procedure will then validate the serial attributes against the value set of
607 -- each segment in the attributes by calling descriptive flexfield validation APIs.
608 --
609 -- Input Parameters:
610 -- p_ser_number - the serial number
611 -- p_inventory_item_id -- inventory item id for the staring lot
612 -- p_organization_id -- organization_id for the starting lot.
613 -- p_result_ser_attr_tbl -- the serial attributes data for the resultant serial.
614 --
615 -- Output Parameters:
616 -- x_return_status -- return status, S- success, E - error, U- unexpected error
617 -- x_msg_count -- number of error message in the message stack.
618 -- x_msg_data -- the error message on the top of the message stack.
619 -- x_validation_status -- 'Y' if validation is successfull, 'N' if not successfull.
620 -- x_ser_attr_tbl -- populated and validated attributes.
621 -- Dependency:
622 -- None.
623 --
624 -- Called By:
625 -- This procedure will be called by INV_TXN_MANAGER_GRP (INVTXGGB.pls)
626 --
627 --************************************************************************************************
628
629 PROCEDURE validate_serial_attributes (
630 x_return_status OUT NOCOPY VARCHAR2
631 , x_msg_count OUT NOCOPY NUMBER
632 , x_msg_data OUT NOCOPY VARCHAR2
633 , x_validation_status OUT NOCOPY VARCHAR2
634 , x_ser_attr_tbl OUT NOCOPY inv_lot_sel_attr.lot_sel_attributes_tbl_type
635 , p_ser_number IN VARCHAR2
636 , p_organization_id IN NUMBER
637 , p_inventory_item_id IN NUMBER
638 , p_result_ser_attr_tbl IN inv_lot_sel_attr.lot_sel_attributes_tbl_type
639 );
640
641 --************************************************************************************************
642 -- Procedure
643 -- Validate_Organization
644 --
645 -- Description:
646 -- This procedure will validate the organization, checks if the Organization chosen
647 -- has a open period and also check if the acct_period_id pass is valid.
648 --
649 -- Input Parameters:
650 -- p_organization_id -- organization_Id of the starting lot.
651 -- p_period_id -- account period id of the organization.
652 --
653 -- Output Parameters:
654 -- x_return_status -- return status, S- success, E - error, U- unexpected error
655 -- x_msg_count -- number of error message in the message stack.
656 -- x_msg_data -- the error message on the top of the message stack.
657 -- x_validation_status -- 'Y' if validation is successfull, 'N' if not successfull.
658 --
659 -- Dependency:
660 -- INV_INV_LOVS (INVINVLS.pls and INVINVLB.pls)
661 -- INVTTMTX (INVTTMTS.pls and INVTTMTB.pls)
662 --
663 -- Called By:
664 -- This procedure will be called by INV_LOT_TRX_VALIDATION_PVT (INVVLTVB.pls)
665
666 --************************************************************************************************
667
668 Procedure Validate_Organization(
669 x_return_status OUT NOCOPY VARCHAR2,
670 x_msg_count OUT NOCOPY NUMBER,
671 x_msg_data OUT NOCOPY VARCHAR2,
672 x_validation_status OUT NOCOPY VARCHAR2,
673 p_organization_id IN NUMBER,
674 p_period_tbl IN NUMBER_TABLE
675 );
676
677 PROCEDURE compute_lot_expiration
678 (
679 x_return_status OUT NOCOPY VARCHAR2,
680 x_msg_count OUT NOCOPY NUMBER,
681 x_msg_data OUT NOCOPY VARCHAR2,
682 p_parent_id IN NUMBER,
683 p_transaction_type_id IN NUMBER,
684 p_item_id IN NUMBER,
685 p_organization_id IN NUMBER,
686 p_st_lot_num IN VARCHAR2,
687 p_rs_lot_num_tbl IN LOT_NUMBER_TABLE,
688 p_rs_lot_exp_tbl IN OUT NOCOPY date_table
689 );
690
691 procedure get_org_info
692 (
693 x_wms_installed OUT NOCOPY VARCHAR2,
694 x_wsm_enabled OUT NOCOPY VARCHAR2,
695 x_wms_enabled OUT NOCOPY VARCHAR2,
696 x_return_status OUT NOCOPY VARCHAR2,
697 x_msg_count OUT NOCOPY NUMBER,
698 x_msg_data OUT NOCOPY VARCHAR2,
699 p_organization_id IN NUMBER
700 );
701 --************************************************************************************************
702 -- Procedure
703 -- Update_Item_serial
704 --
705 -- Description:
706 -- This API is for the requirement from OSFM.
707 -- This procedure can possibly update the inventory_item_id (assembly), job number (Lot),
708 -- operation seq# and intraoperation step for the serial number passed.
709 --
710 -- Input Parameters:
711 -- p_org_id -- Organization Id for the Serial
712 -- p_item_id -- Current Item Id (assembly)
713 -- p_to_item_id -- New Inventory Item Id (Can be NULL)
714 -- p_wip_entity_id -- Current Job (Lot)
715 -- p_to_wip_entity_id -- New Job (Lot)
716 -- p_to_operation_sequence -- New operation sequence
717 -- p_intraoperation_step_type -- New intraoperation step type
718 --
719 --
720 -- Output Parameters:
721 -- x_return_status -- return status, S- success, E - error, U- unexpected error
722 -- x_msg_count -- number of error message in the message stack.
723 -- x_msg_data -- the error message on the top of the message stack.
724 -- x_validation_status -- 'Y' if validation is successfull, 'N' if not successfull.
725 --
726 -- Dependency:
727 --
728 -- Called By:
729 -- This procedure will be called by OSFM from their forms.
730
731 --************************************************************************************************
732
733 PROCEDURE update_item_serial (
734 x_msg_count OUT NOCOPY VARCHAR2
735 ,x_return_status OUT NOCOPY VARCHAR2
736 ,x_msg_data OUT NOCOPY VARCHAR2
737 ,x_validation_status OUT NOCOPY VARCHAR2
738 ,p_org_id IN NUMBER
739 ,p_item_id IN NUMBER
740 ,p_to_item_id IN NUMBER DEFAULT NULL
741 ,p_wip_entity_id IN NUMBER
742 ,p_to_wip_entity_id IN NUMBER DEFAULT NULL
743 ,p_to_operation_sequence IN NUMBER DEFAULT NULL
744 ,p_intraoperation_step_type IN NUMBER DEFAULT NULL
745 );
746 END INV_LOT_TRX_VALIDATION_PUB;
[Home] [Help]