[Home] [Help]
PACKAGE BODY: APPS.INV_LOGICAL_TRANSACTIONS_PVT
Source
1 PACKAGE BODY inv_logical_transactions_pvt AS
2 /* $Header: INVLTPVB.pls 120.3 2006/02/16 15:07:12 umoogala noship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'INV_LOGICAL_TRANSACTIONS_PVT';
5
6 PROCEDURE debug_print(p_message IN VARCHAR2, p_level IN NUMBER := 9) IS
7 BEGIN
8 inv_log_util.TRACE(p_message, 'INV_LOGICAL_TRANSACTIONS_PVT',
9 p_level);
10 -- dbms_output.put_line(p_message);
11 END debug_print;
12
13 -- Procedure
14 -- validate_input_parameters
15 -- Description
16 -- validate the inout paremters before populating the
17 -- inventory transactions table.
18
19 PROCEDURE validate_input_parameters
20 (
21 x_return_status OUT NOCOPY VARCHAR2
22 , x_msg_count OUT NOCOPY NUMBER
23 , x_msg_data OUT NOCOPY VARCHAR2
24 , p_api_version_number IN NUMBER
25 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
26 , p_mtl_trx_tbl IN inv_logical_transaction_global.mtl_trx_tbl_type
27 , p_validation_level IN VARCHAR2
28 , p_logical_trx_type_code IN NUMBER
29 ) IS
30
31 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
32 l_count NUMBER := 0;
33 l_so_line_id NUMBER := 0;
34 l_so_header_id NUMBER := 0;
35 l_order_number VARCHAR2(40);
36 l_project_id NUMBER := 0;
37 l_task_id NUMBER := 0;
38 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
39 l_transaction_action_id NUMBER := 0;
40 l_transaction_source_type_id NUMBER := 0;
41 l_transaction_type_id NUMBER := 0;
42 l_acct_period_id NUMBER := 0;
43 l_api_version_number CONSTANT NUMBER := 1.0;
44 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Input_Parameters';
45
46
47 -- For transactions that are processed through the transaction
48 -- manager the physical reacord has already been validated. So, do
49 -- not validate the physical record again. So, if the validation
50 -- flag is set to false and if the trx type code is 2 (SO issue
51 -- or RMA receipt, we will skip the record where the
52 -- paren_transaction_flag is set to 1 (TRUE).
53 BEGIN
54
55 --
56 -- Standard call to check for call compatibility
57 IF NOT fnd_api.compatible_api_call(l_api_version_number, p_api_version_number, l_api_name, g_pkg_name) THEN
58 RAISE fnd_api.g_exc_unexpected_error;
59 END IF;
60
61 --
62 -- Initialize message list.
63 IF fnd_api.to_boolean(p_init_msg_lst) THEN
64 fnd_msg_pub.initialize;
65 END IF;
66
67
68
69 IF (l_debug = 1) THEN
70 debug_print('Inside validate input parameters API', 9);
71 END IF;
72
73 FOR i IN 1..p_mtl_trx_tbl.COUNT LOOP
74
75
76 --1. Validate logical transaction type codes
77 IF (p_logical_trx_type_code IS NULL OR
78 (p_logical_trx_type_code NOT IN
79 (INV_LOGICAL_TRANSACTION_GLOBAL.G_LOGTRXCODE_DSRECEIPT,
80 INV_LOGICAL_TRANSACTION_GLOBAL.G_LOGTRXCODE_DSDELIVER,
81 INV_LOGICAL_TRANSACTION_GLOBAL.G_LOGTRXCODE_GLOBPROCRTV,
82 INV_LOGICAL_TRANSACTION_GLOBAL.G_LOGTRXCODE_RETROPRICEUPD,
83 INV_LOGICAL_TRANSACTION_GLOBAL.G_LOGTRXCODE_RMASOISSUE))) THEN
84
85 IF (l_debug = 1) THEN
86 debug_print('Invalid Logical Transaction Type Code', 9);
87 END IF;
88 fnd_message.set_name('INV', 'INV_INT_LOGTRXCODE');
89 fnd_msg_pub.ADD;
90 RAISE fnd_api.g_exc_error;
91
92 END IF;
93
94 -- For sales order transactions, we do not have to validate them
95 -- since they are already validated by INV TM
96
97 IF (p_validation_level = fnd_api.g_true) THEN
98
99 --2. Validate Transaction Action, Source and Type
100
101 IF (p_mtl_trx_tbl(i).transaction_type_id) IS NULL THEN
102 IF (l_debug = 1) THEN
103 debug_print('Transaction Type is null ', 9);
104 END IF;
105 fnd_message.set_name('INV', 'INV_INT_TRXTYPCODE');
106 fnd_msg_pub.ADD;
107 RAISE fnd_api.g_exc_error;
108
109 END IF;
110
111 BEGIN
112 SELECT COUNT(1),transaction_action_id, transaction_source_type_id
113 INTO l_count, l_transaction_action_id, l_transaction_source_type_id
114 FROM mtl_transaction_types mtt
115 WHERE mtt.transaction_type_id =
116 p_mtl_trx_tbl(i).transaction_type_id AND
117 nvl(MTT.DISABLE_DATE,SYSDATE+1) > Sysdate
118 group by transaction_action_id, transaction_source_type_id;
119 EXCEPTION
120 WHEN no_data_found THEN
121 IF (l_debug = 1) THEN
122 debug_print('Transaction Type not found ', 9);
123 END IF;
124 fnd_message.set_name('INV', 'INV_INT_TRXTYPCODE');
125 fnd_msg_pub.ADD;
126 RAISE fnd_api.g_exc_error;
127 END;
128
129 IF l_count <> 1 THEN
130 IF (l_debug = 1) THEN
131 debug_print('Transaction Type not found ', 9);
132 END IF;
133 fnd_message.set_name('INV', 'INV_INT_TRXTYPCODE');
134 fnd_msg_pub.ADD;
135 RAISE fnd_api.g_exc_error;
136
137 END IF;
138
139 IF (l_debug = 1) THEN
140 debug_print('Transaction Source Type ID : ' ||
141 p_mtl_trx_tbl(i).transaction_source_type_id, 9);
142 debug_print('Transaction Action ID : ' ||
143 p_mtl_trx_tbl(i).transaction_action_id, 9);
144 END IF;
145
146 IF ((p_mtl_trx_tbl(i).transaction_source_type_id IS NOT NULL and
147 p_mtl_trx_tbl(i).transaction_source_type_id <> l_transaction_source_type_id) OR
148 (p_mtl_trx_tbl(i).transaction_action_id IS NOT NULL and
149 p_mtl_trx_tbl(i).transaction_action_id <> l_transaction_action_id)) THEN
150 IF (l_debug = 1) THEN
151 debug_print('Transaction Source/Action is incorrect ', 9);
152 debug_print('Transaction Source Type ID : ' ||
153 l_transaction_source_type_id, 9);
154 debug_print('Transaction Action ID : ' ||
155 l_transaction_action_id, 9);
156 END IF;
157 fnd_message.set_name('INV', 'INV_INT_TRXTYPCODE');
158 fnd_msg_pub.ADD;
159 RAISE fnd_api.g_exc_error;
160
161 END IF;
162
163
164 -- 2.a. Validate all the actions that are for logical transactions.
165 -- Only these should be processed by this API
166
167 IF (Nvl(p_mtl_trx_tbl(i).transaction_action_id,0) NOT IN
168 (INV_GLOBALS.G_ACTION_LOGICALISSUE,INV_GLOBALS.G_ACTION_LOGICALICSALES ,
169 INV_GLOBALS.G_ACTION_LOGICALICRECEIPT, INV_GLOBALS.G_ACTION_LOGICALDELADJ,
170 INV_GLOBALS.G_ACTION_LOGICALICRCPTRETURN,INV_GLOBALS.G_ACTION_LOGICALICSALESRETURN,
171 INV_GLOBALS.G_ACTION_LOGICALEXPREQRECEIPT, INV_GLOBALS.G_ACTION_RETROPRICEUPDATE,
172 INV_GLOBALS.G_ACTION_LOGICALRECEIPT)) THEN
173 IF (l_debug = 1) THEN
174 debug_print('Transaction Action is invalid', 9);
175 END IF;
176 fnd_message.set_name('INV', 'INV_INT_TRXACTCODE');
177 fnd_msg_pub.ADD;
178 RAISE fnd_api.g_exc_error;
179
180 END IF;
181
182
183 --3. Validate Organization
184 l_count := 0;
185
186 BEGIN
187 SELECT COUNT(1) INTO l_count
188 FROM HR_ORGANIZATION_UNITS HOU, MTL_PARAMETERS MP
189 WHERE MP.ORGANIZATION_ID = p_mtl_trx_tbl(i).organization_id
190 AND MP.ORGANIZATION_ID = HOU.ORGANIZATION_ID
191 AND NVL(HOU.DATE_TO, SYSDATE + 1) > Sysdate;
192
193 EXCEPTION
194 WHEN no_data_found THEN
195 IF (l_debug = 1) THEN
196 debug_print('Org. not found :' ||
197 p_mtl_trx_tbl(i).organization_id, 9);
198 END IF;
199 fnd_message.set_name('INV', 'INV_INT_ORGCODE');
200 fnd_msg_pub.ADD;
201 RAISE fnd_api.g_exc_error;
202 END;
203
204 IF l_count <> 1 THEN
205 IF (l_debug = 1) THEN
206 debug_print('Invalid Organization :' ||
207 p_mtl_trx_tbl(i).organization_id, 9);
208 END IF;
209 fnd_message.set_name('INV', 'INV_INT_XORGCODE');
210 fnd_msg_pub.ADD;
211 RAISE fnd_api.g_exc_error;
212
213 END IF;
214
215 --4. Validate Transfer Organization
216 -- Transfer organization needs to be validated only for
217 -- intercompany transactions. Other transactions would not have
218 -- the transfer organization filled in.
219
220 l_count := 0;
221
222 IF (Nvl(p_mtl_trx_tbl(i).transaction_action_id,0) IN
223 (INV_GLOBALS.G_ACTION_LOGICALICSALES, INV_GLOBALS.G_ACTION_LOGICALICRECEIPT,
224 INV_GLOBALS.G_ACTION_LOGICALICRCPTRETURN, INV_GLOBALS.G_ACTION_LOGICALICSALESRETURN,
225 INV_GLOBALS.G_ACTION_LOGICALEXPREQRECEIPT))THEN
226
227 IF (p_mtl_trx_tbl(i).transfer_organization_id IS NULL) THEN
228 IF (l_debug = 1) THEN
229 debug_print('Transfer Org. is null', 9);
230 END IF;
231 fnd_message.set_name('INV', 'INV_INT_XORGCODE');
232 fnd_msg_pub.ADD;
233 RAISE fnd_api.g_exc_error;
234 END IF;
235
236 BEGIN
237 SELECT COUNT(1) INTO l_count
238 FROM HR_ORGANIZATION_UNITS HOU, MTL_PARAMETERS MP
239 WHERE MP.ORGANIZATION_ID = p_mtl_trx_tbl(i).transfer_organization_id
240 AND MP.ORGANIZATION_ID = HOU.ORGANIZATION_ID
241 AND NVL(HOU.DATE_TO, SYSDATE + 1) > Sysdate;
242
243 EXCEPTION
244 WHEN no_data_found THEN
245 IF (l_debug = 1) THEN
246 debug_print('Transfer Org. not found :' ||
247 p_mtl_trx_tbl(i).transfer_organization_id, 9);
248 END IF;
249 fnd_message.set_name('INV', 'INV_INT_XORGCODE');
250 fnd_msg_pub.ADD;
251 RAISE fnd_api.g_exc_error;
252 END;
253
254 IF (l_count <> 1) THEN
255 IF (l_debug = 1) THEN
256 debug_print('Invalid Transfer Organization :' ||
257 p_mtl_trx_tbl(i).transfer_organization_id,
258 9);
259 END IF;
260 fnd_message.set_name('INV', 'INV_INT_XORGCODE');
261 fnd_msg_pub.ADD;
262 RAISE fnd_api.g_exc_error;
263
264 END IF;
265 END IF;
266
267 --5. Validate Item
268 l_count := 0;
269
270 BEGIN
271 SELECT COUNT(1) INTO l_count FROM MTL_SYSTEM_ITEMS MSI
272 WHERE MSI.INVENTORY_ITEM_ID = p_mtl_trx_tbl(i).inventory_item_id
273 AND MSI.ORGANIZATION_ID = p_mtl_trx_tbl(i).organization_id
274 AND MSI.INVENTORY_ITEM_FLAG = 'Y';
275 EXCEPTION
276 WHEN no_data_found THEN
277 IF (l_debug = 1) THEN
278 debug_print('Item not found', 9);
279 END IF;
280 fnd_message.set_name('INV', 'INV_INT_ITMCODE');
281 fnd_msg_pub.ADD;
282 RAISE fnd_api.g_exc_error;
283 END;
284
285 IF l_count <> 1 THEN
286 IF (l_debug = 1) THEN
287 debug_print('Invalid item in the current Org. :' ||
288 p_mtl_trx_tbl(i).inventory_item_id, 9);
289 END IF;
290 fnd_message.set_name('INV', 'INV_INT_ITMCODE');
291 fnd_msg_pub.ADD;
292 RAISE fnd_api.g_exc_error;
293
294 END IF;
295
296 --6. Validate Item for the transfer organization - only if the
297 -- transfer organization is populated.
298
299 l_count := 0;
300
301 IF (p_mtl_trx_tbl(i).transaction_action_id IN
302 (INV_GLOBALS.G_ACTION_LOGICALICSALES, INV_GLOBALS.G_ACTION_LOGICALICRECEIPT,
303 INV_GLOBALS.G_ACTION_LOGICALICRCPTRETURN, INV_GLOBALS.G_ACTION_LOGICALICSALESRETURN,
304 INV_GLOBALS.G_ACTION_LOGICALEXPREQRECEIPT))THEN
305 BEGIN
306 SELECT COUNT(1) INTO l_count FROM MTL_SYSTEM_ITEMS MSI
307 WHERE MSI.INVENTORY_ITEM_ID = p_mtl_trx_tbl(i).inventory_item_id
308 AND MSI.ORGANIZATION_ID = p_mtl_trx_tbl(i).transfer_organization_id
309 AND MSI.INVENTORY_ITEM_FLAG = 'Y';
310 EXCEPTION
311 WHEN no_data_found THEN
312 IF (l_debug = 1) THEN
313 debug_print('Item not found in the transfer Org. :'
314 || p_mtl_trx_tbl(i).inventory_item_id, 9);
315 END IF;
316 fnd_message.set_name('INV', 'INV_INT_ITMCODE');
317 fnd_msg_pub.ADD;
318 RAISE fnd_api.g_exc_error;
319 END;
320
321 IF (l_count <> 1) THEN
322 IF (l_debug = 1) THEN
323 debug_print('Invalid item in the transfer Org. :' ||
324 p_mtl_trx_tbl(i).inventory_item_id, 9);
325 END IF;
326 fnd_message.set_name('INV', 'INV_INT_ITMCODE');
327 fnd_msg_pub.ADD;
328 RAISE fnd_api.g_exc_error;
329
330 END IF;
331 END IF;
332
333 -- 7. Retroactive Price update specific validations
334 IF (Nvl(p_mtl_trx_tbl(i).transaction_action_id,0) =
335 INV_GLOBALS.G_ACTION_RETROPRICEUPDATE AND
336 Nvl(p_mtl_trx_tbl(i).transaction_source_type_id,0) =
337 INV_GLOBALS.G_SOURCETYPE_PURCHASEORDER) THEN
338
339 IF (p_mtl_trx_tbl(i).CONSUMPTION_RELEASE_ID IS NULL AND
340 p_mtl_trx_tbl(i).CONSUMPTION_PO_HEADER_ID IS NULL) THEN
341
342 IF (l_debug = 1) THEN
343 debug_print('Both release id and po header id are null. One of them should have a valid value', 9);
344 END IF;
345 fnd_message.set_name('INV', 'INV_INT_RETCODE');
346 fnd_msg_pub.ADD;
347 RAISE fnd_api.g_exc_error;
348
349 END IF;
350
351 IF (p_mtl_trx_tbl(i).old_po_price IS NULL or
352 p_mtl_trx_tbl(i).new_po_price IS NULL) THEN
353
354 IF (l_debug = 1) THEN
355 debug_print('The old PO price and the new PO price shouldnt be null', 9);
356 END IF;
357 fnd_message.set_name('INV', 'INV_INT_RETQTYCODE');
358 fnd_msg_pub.ADD;
359 RAISE fnd_api.g_exc_error;
360
361 END IF;
362
363 --8. Check for invalid PO header ID
364 l_count := 0;
365
366 IF (p_mtl_trx_tbl(i).transaction_source_id IS NULL) THEN
367
368 IF (l_debug = 1) THEN
369 debug_print('Transaction Source ID is null', 9);
370 END IF;
371 fnd_message.set_name('INV', 'INV_INT_PO');
372 fnd_msg_pub.ADD;
373 RAISE fnd_api.g_exc_error;
374
375 END IF;
376
377 BEGIN
378 SELECT COUNT(1) INTO l_count FROM
379 po_headers_all po WHERE
380 po.po_header_id = p_mtl_trx_tbl(i).transaction_source_id AND
381 NVL(po.START_DATE_ACTIVE, SYSDATE - 1) <= Sysdate AND
382 NVL(po.END_DATE_ACTIVE, SYSDATE + 1) > SYSDATE AND ENABLED_FLAG = 'Y';
383
384 EXCEPTION
385 WHEN no_data_found THEN
386 IF (l_debug = 1) THEN
387 debug_print('Cannot find the purchase order information or the po is invalid', 9);
388 END IF;
389 fnd_message.set_name('INV', 'INV_INT_PO');
390 fnd_msg_pub.ADD;
391 RAISE fnd_api.g_exc_error;
392 END;
393
394 IF (l_count <> 1) THEN
395
396 IF (l_debug = 1) THEN
397 debug_print('Invalid puchase order header ID :' || p_mtl_trx_tbl(i).transaction_source_id, 9);
398 END IF;
399 fnd_message.set_name('INV', 'INV_INT_PO');
400 fnd_msg_pub.ADD;
401 RAISE fnd_api.g_exc_error;
402
403 END IF;
404
405 END IF;
406
407
408 --9. Validate subinventory if the subinventory is filled in.
409 l_count := 0;
410
411 IF (p_mtl_trx_tbl(i).subinventory_code IS NOT NULL) THEN
412 BEGIN
413 SELECT COUNT(1) INTO l_count FROM MTL_SECONDARY_INVENTORIES MSI
414 WHERE MSI.ORGANIZATION_ID = p_mtl_trx_tbl(i).organization_id
415 AND MSI.SECONDARY_INVENTORY_NAME = p_mtl_trx_tbl(i).subinventory_code
416 AND TRUNC(p_mtl_trx_tbl(i).transaction_date) <= NVL(MSI.DISABLE_DATE,p_mtl_trx_tbl(i).transaction_date + 1);
417 EXCEPTION
418 WHEN no_data_found THEN
419 IF (l_debug = 1) THEN
420 debug_print('Cannot find the specified subinventory :'
421 || p_mtl_trx_tbl(i).locator_id, 9);
422 END IF;
423 fnd_message.set_name('INV', 'INV_INT_SUBCODE');
424 fnd_msg_pub.ADD;
425 RAISE fnd_api.g_exc_error;
426 END;
427
428 IF (l_count <> 1) THEN
429 IF (l_debug = 1) THEN
430 debug_print('Invalid subinventory :' ||
431 p_mtl_trx_tbl(i).subinventory_code, 9);
432 END IF;
433 fnd_message.set_name('INV', 'INV_INT_SUBCODE');
434 fnd_msg_pub.ADD;
435 RAISE fnd_api.g_exc_error;
436
437 END IF;
438
439 l_count := 0;
440
441 BEGIN
442 SELECT 1 INTO l_count FROM dual WHERE exists
443 (
444 SELECT null FROM MTL_SECONDARY_INVENTORIES MTSI,
445 MTL_SYSTEM_ITEMS MSI
446 WHERE MSI.ORGANIZATION_ID = p_mtl_trx_tbl(i).organization_id
447 AND MSI.INVENTORY_ITEM_ID = p_mtl_trx_tbl(i).inventory_item_id
448 AND MSI.RESTRICT_SUBINVENTORIES_CODE = 1
449 AND MTSI.ORGANIZATION_ID = p_mtl_trx_tbl(i).organization_id
450 -- AND MTSI.INVENTORY_ITEM_ID = p_mtl_trx_tbl(i).inventory_item_id
451 AND MTSI.ORGANIZATION_ID = MSI.ORGANIZATION_ID
452 -- AND MTSI.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
453 AND MTSI.SECONDARY_INVENTORY_NAME = p_mtl_trx_tbl(i).subinventory_code
454 UNION
455 SELECT NULL FROM MTL_SYSTEM_ITEMS ITM
456 WHERE ITM.RESTRICT_SUBINVENTORIES_CODE = 2
457 AND ITM.ORGANIZATION_ID = p_mtl_trx_tbl(i).organization_id
458 AND ITM.INVENTORY_ITEM_ID = p_mtl_trx_tbl(i).inventory_item_id);
459 EXCEPTION
460 WHEN no_data_found THEN
461 IF (l_debug = 1) THEN
462 debug_print('Restricted Subinevntory', 9);
463 END IF;
464 fnd_message.set_name('INV', 'INV_INT_SUBCODE');
465 fnd_msg_pub.ADD;
466 RAISE fnd_api.g_exc_error;
467 END;
468
469 IF (l_count <> 1) THEN
470 IF (l_debug = 1) THEN
471 debug_print('Invalid subinventory', 9);
472 END IF;
473 fnd_message.set_name('INV', 'INV_INT_SUBCODE');
474 fnd_msg_pub.ADD;
475 RAISE fnd_api.g_exc_error;
476
477 END IF;
478
479 END IF;
480
481 --10. Validate Locators if the locator is filled in.
482 l_count := 0;
483
484 IF (p_mtl_trx_tbl(i).locator_id IS NOT NULL) THEN
485
486 BEGIN
487 SELECT COUNT(1) INTO l_count FROM MTL_ITEM_LOCATIONS MIL
488 WHERE MIL.ORGANIZATION_ID = p_mtl_trx_tbl(i).organization_id
489 AND MIL.SUBINVENTORY_CODE = p_mtl_trx_tbl(i).subinventory_code
490 AND MIL.INVENTORY_LOCATION_ID = p_mtl_trx_tbl(i).locator_id
491 AND TRUNC(p_mtl_trx_tbl(i).transaction_date) <= NVL(MIL.DISABLE_DATE,p_mtl_trx_tbl(i).transaction_date + 1);
492 EXCEPTION
493 WHEN no_data_found THEN
494 IF (l_debug = 1) THEN
495 debug_print('Cannot find the specified locator :'
496 || p_mtl_trx_tbl(i).locator_id, 9);
497 END IF;
498 fnd_message.set_name('INV', 'INV_INT_LOCCODE');
499 fnd_msg_pub.ADD;
500 RAISE fnd_api.g_exc_error;
501 END;
502
503 IF (l_count <> 1) THEN
504 IF (l_debug = 1) THEN
505 debug_print('Invalid locator :' ||
506 p_mtl_trx_tbl(i).locator_id, 9);
507 END IF;
508 fnd_message.set_name('INV', 'INV_INT_LOCCODE');
509 fnd_msg_pub.ADD;
510 RAISE fnd_api.g_exc_error;
511
512 END IF;
513
514 l_count := 0;
515
516 BEGIN
517 SELECT 1 INTO l_count FROM dual WHERE exists
518 (
519 SELECT null FROM MTL_SECONDARY_LOCATORS MSL,
520 MTL_SYSTEM_ITEMS MSI
521 WHERE MSI.ORGANIZATION_ID = p_mtl_trx_tbl(i).organization_id
522 AND MSI.INVENTORY_ITEM_ID = p_mtl_trx_tbl(i).inventory_item_id
523 AND MSI.RESTRICT_LOCATORS_CODE = 1
524 AND MSL.ORGANIZATION_ID = p_mtl_trx_tbl(i).organization_id
525 AND MSL.INVENTORY_ITEM_ID = p_mtl_trx_tbl(i).inventory_item_id
526 AND MSL.ORGANIZATION_ID = MSI.ORGANIZATION_ID
527 AND MSL.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
528 AND MSL.SUBINVENTORY_CODE = p_mtl_trx_tbl(i).subinventory_code
529 AND MSL.SECONDARY_LOCATOR = p_mtl_trx_tbl(i).locator_id
530 UNION
531 SELECT NULL FROM MTL_SYSTEM_ITEMS ITM
532 WHERE ITM.RESTRICT_LOCATORS_CODE = 2
533 AND ITM.ORGANIZATION_ID = p_mtl_trx_tbl(i).organization_id
534 AND ITM.INVENTORY_ITEM_ID = p_mtl_trx_tbl(i).inventory_item_id);
535 EXCEPTION
536 WHEN no_data_found THEN
537 IF (l_debug = 1) THEN
538 debug_print('Restricted Sub/loc', 9);
539 END IF;
540 fnd_message.set_name('INV', 'INV_INT_LOCCODE');
541 fnd_msg_pub.ADD;
542 RAISE fnd_api.g_exc_error;
543 END;
544
545 IF (l_count <> 1) THEN
546 IF (l_debug = 1) THEN
547 debug_print('Invalid locator', 9);
548 END IF;
549 fnd_message.set_name('INV', 'INV_INT_LOCCODE');
550 fnd_msg_pub.ADD;
551 RAISE fnd_api.g_exc_error;
552
553 END IF;
554
555 END IF;
556
557
558 --11. Validate transaction source type for sales order tied to drop
559 -- ment transactions.
560 IF (p_logical_trx_type_code = INV_LOGICAL_TRANSACTION_GLOBAL.G_LOGTRXCODE_DSDELIVER AND
561 (p_mtl_trx_tbl(i).transaction_source_type_id = inv_globals.g_sourcetype_salesorder AND
562 p_mtl_trx_tbl(i).transaction_action_id = inv_globals.g_action_logicalissue)) THEN
563 -- drop shipments across multiple OUs
564 -- get the sales order tied to the logical transaction
565 -- get the start active and end active dates and make sure that
566 -- transaction date is between them.
567 BEGIN
568 SELECT odss.header_id INTO l_so_header_id
569 FROM oe_drop_ship_sources odss, rcv_transactions RT
570 WHERE odss.line_location_id = rt.po_line_location_id AND
571 rt.transaction_id = p_mtl_trx_tbl(i).rcv_transaction_id
572 GROUP BY odss.header_id;
573 EXCEPTION
574 WHEN no_data_found THEN
575 IF (l_debug = 1) THEN
576 debug_print('Drop Ship Source not found', 9);
577 END IF;
578 fnd_message.set_name('INV', 'INV_INT_SRCCODE');
579 fnd_msg_pub.ADD;
580 RAISE fnd_api.g_exc_error;
581 END;
582
583 l_count := 0;
584 BEGIN
585 SELECT 1 INTO l_count FROM mtl_sales_orders mso, oe_order_headers_all oeha
586 WHERE oeha.header_id = l_so_header_id
587 AND oeha.order_number = mso.segment1
588 AND mso.sales_order_id = p_mtl_trx_tbl(i).transaction_source_id
589 AND NVL(START_DATE_ACTIVE, SYSDATE - 1)
590 <= Sysdate AND NVL(END_DATE_ACTIVE, SYSDATE + 1) > SYSDATE
591 AND ENABLED_FLAG = 'Y';
592 EXCEPTION
593 WHEN no_data_found THEN
594 IF (l_debug = 1) THEN
595 debug_print('Sales Order is not valid for the current date', 9);
596 END IF;
597 fnd_message.set_name('INV', 'INV_INT_SALEXP');
598 fnd_msg_pub.ADD;
599 RAISE fnd_api.g_exc_error;
600 END;
601
602 IF (l_count <> 1) THEN
603 IF (l_debug = 1) THEN
604 debug_print('Cannot find the sales order or sales order IS NOT active', 9);
605 END IF;
606 fnd_message.set_name('INV', 'INV_INT_SALEXP');
607 fnd_msg_pub.ADD;
608 RAISE fnd_api.g_exc_error;
609
610 END IF;
611
612 END IF;
613
614 -- Bug 3227829: Removing the check for quantity as the quantity
615 -- can vary depending on whether it is a correction or a receipt.
616 -- 12. Validate transaction quantity being passed.
617 -- For types
618 /*******
619 IF (p_mtl_trx_tbl(i).transaction_quantity > 0) AND
620 (p_mtl_trx_tbl(i).transaction_action_id in
621 (INV_GLOBALS.G_ACTION_LOGICALISSUE, INV_GLOBALS.G_ACTION_LOGICALICSALES, INV_GLOBALS.G_ACTION_LOGICALICRCPTRETURN)) THEN
622 IF (l_debug = 1) THEN
623 debug_print('Invalid transaction quantity : quantity should be negative', 9);
624 END IF;
625 fnd_message.set_name('INV', 'INV_INVALID_QUANTITY');
626 fnd_msg_pub.ADD;
627 RAISE fnd_api.g_exc_error;
628 END IF;
629
630 IF (p_mtl_trx_tbl(i).transaction_quantity < 0) AND
631 (p_mtl_trx_tbl(i).transaction_action_id in
632 (INV_GLOBALS.G_ACTION_LOGICALICRECEIPT, INV_GLOBALS.G_ACTION_LOGICALICSALESRETURN,
633 INV_GLOBALS.G_ACTION_LOGICALEXPREQRECEIPT, INV_GLOBALS.G_ACTION_LOGICALRECEIPT)) THEN
634 IF (l_debug = 1) THEN
635 debug_print('Invalid transaction quantity : quantity should be positive', 9);
636 END IF;
637 fnd_message.set_name('INV', 'INV_INVALID_QUANTITY');
638 fnd_msg_pub.ADD;
639 RAISE fnd_api.g_exc_error;
640 END IF;
641 ******/
642 --13. Validate distribution account id
643 -- Assumption is that the distribution account id should have been
644 -- populated before passing the record for validation
645 -- check for the distribution account from gl code combinations for
646 -- that oraganization.
647
648 IF (p_mtl_trx_tbl(i).distribution_account_id IS NULL) THEN
649 IF (l_debug = 1) THEN
650 debug_print('Distribution account is null', 9);
651 END IF;
652 fnd_message.set_name('INV', 'INV_INT_DISTCODE');
653 fnd_msg_pub.ADD;
654 RAISE fnd_api.g_exc_error;
655 END IF;
656
657 debug_print('Distribution account :' ||
658 p_mtl_trx_tbl(i).distribution_account_id, 9);
659
660 l_count := 0;
661 BEGIN
662 SELECT COUNT(1) INTO l_count FROM GL_CODE_COMBINATIONS GCC
663 WHERE GCC.CODE_COMBINATION_ID = p_mtl_trx_tbl(i).distribution_account_id
664 AND GCC.CHART_OF_ACCOUNTS_ID = (SELECT CHART_OF_ACCOUNTS_ID
665 FROM ORG_ORGANIZATION_DEFINITIONS OOD
666 WHERE OOD.ORGANIZATION_ID = p_mtl_trx_tbl(i).organization_id)
667 AND GCC.ENABLED_FLAG = 'Y'
668 AND NVL(GCC.START_DATE_ACTIVE, SYSDATE - 1) <= SYSDATE
669 AND NVL(GCC.END_DATE_ACTIVE, SYSDATE + 1) > SYSDATE;
670 EXCEPTION
671 WHEN no_data_found THEN
672 IF (l_debug = 1) THEN
673 debug_print('Distribution account not found :' ||
674 p_mtl_trx_tbl(i).distribution_account_id, 9);
675 END IF;
676 fnd_message.set_name('INV', 'INV_INT_DISTCODE');
677 fnd_msg_pub.ADD;
678 RAISE fnd_api.g_exc_error;
679 END;
680
681 IF (l_count <> 1) THEN
682
683 IF (l_debug = 1) THEN
684 debug_print('Invalid distribution account :' ||
685 p_mtl_trx_tbl(i).distribution_account_id, 9);
686 END IF;
687 fnd_message.set_name('INV', 'INV_INT_DISTCODE');
688 fnd_msg_pub.ADD;
689 RAISE fnd_api.g_exc_error;
690
691 END IF;
692
693
694 -- 14. Validate Account Period ID. The account_period_id should
695 -- be valid in all the organizations (primary and intermediate)
696 -- orgs. Check to see if it open for the date
697 -- specified/transaction date.
698
699 BEGIN
700 SELECT ACCT_PERIOD_ID
701 INTO l_acct_period_id
702 FROM ORG_ACCT_PERIODS
703 WHERE PERIOD_CLOSE_DATE IS NULL
704 AND ORGANIZATION_ID = p_mtl_trx_tbl(i).organization_id
705 AND TRUNC(SCHEDULE_CLOSE_DATE) >=
706 TRUNC(nvl(p_mtl_trx_tbl(i).transaction_date,SYSDATE))
707 AND TRUNC(PERIOD_START_DATE) <=
708 TRUNC(nvl(p_mtl_trx_tbl(i).transaction_date,SYSDATE)) ;
709 EXCEPTION
710 WHEN NO_DATA_FOUND THEN
711 IF (l_debug = 1) THEN
712 debug_print('Invalid Account Period ID :' ||
713 l_acct_period_id, 9);
714 END IF;
715 l_acct_period_id := 0;
716 WHEN OTHERS THEN
717 IF (l_debug = 1) THEN
718 debug_print('Invalid Account Period ID :' ||
719 l_acct_period_id, 9);
720 END IF;
721 l_acct_period_id := -1;
722 END;
723
724 IF (l_acct_period_id = -1 OR l_acct_period_id = 0) THEN
725 IF (l_debug = 1) THEN
726 debug_print('Period not open', 9);
727 END IF;
728 -- FND_MESSAGE.set_name('INV', 'INV_INT_PRDCODE');
729 FND_MESSAGE.set_name('INV', 'INV_NO_OPEN_PERIOD');
730 fnd_msg_pub.ADD;
731 RAISE fnd_api.g_exc_error;
732
733 END IF;
734
735 IF (((p_mtl_trx_tbl(i).acct_period_id IS NOT NULL) AND
736 (p_mtl_trx_tbl(i).acct_period_id <> l_acct_period_id))
737 OR (p_mtl_trx_tbl(i).acct_period_id IS NULL)) THEN
738 IF (l_debug = 1) THEN
739 debug_print('Invalid Account Period ID passed : ' ||
740 p_mtl_trx_tbl(i).acct_period_id || ' is not the same as ' || l_acct_period_id, 9);
741 END IF;
742 FND_MESSAGE.set_name('INV', 'INV_NO_OPEN_PERIOD');
743 fnd_msg_pub.ADD;
744 RAISE fnd_api.g_exc_error;
745
746 END IF;
747
748 --15. Validate transaction UOM
749
750 l_count := 0;
751
752 BEGIN
753 SELECT COUNT(1) INTO l_count
754 FROM MTL_ITEM_UOMS_VIEW MIUV
755 WHERE MIUV.INVENTORY_ITEM_ID = p_mtl_trx_tbl(i).inventory_item_id
756 AND MIUV.ORGANIZATION_ID = p_mtl_trx_tbl(i).organization_id
757 AND MIUV.UOM_CODE = p_mtl_trx_tbl(i).transaction_uom;
758 EXCEPTION
759 WHEN no_data_found THEN
760 IF (l_debug = 1) THEN
761 debug_print('Invalid Transaction UOM : ' ||
762 p_mtl_trx_tbl(i).transaction_uom, 9);
763 END IF;
764 fnd_message.set_name('INV', 'INV_INT_UOMCODE');
765 fnd_msg_pub.ADD;
766 RAISE fnd_api.g_exc_error;
767 END;
768
769 IF (l_count) = 0 THEN
770 IF (l_debug = 1) THEN
771 debug_print('Transaction UOM : ' ||
772 p_mtl_trx_tbl(i).transaction_uom || ' not found', 9);
773 END IF;
774 fnd_message.set_name('INV', 'INV_INT_UOMCODE');
775 fnd_msg_pub.ADD;
776 RAISE fnd_api.g_exc_error;
777
778 END IF;
779
780 --16. Validate cost groups. Cost groups should already be populated
781 -- before validation.
782
783 IF (p_mtl_trx_tbl(i).cost_group_id IS NULL) THEN
784 IF (l_debug = 1) THEN
785 debug_print('Invalid Cost Group', 9);
786 END IF;
787 fnd_message.set_name('INV', 'INV_INT_CSTGRP');
788 fnd_msg_pub.ADD;
789 RAISE fnd_api.g_exc_error;
790
791 END IF;
792
793 l_count := 0;
794
795 BEGIN
796 SELECT COUNT(1) INTO l_count
797 FROM CST_COST_GROUPS CCG
798 WHERE CCG.COST_GROUP_ID = p_mtl_trx_tbl(i).cost_group_id
799 AND NVL(CCG.ORGANIZATION_ID, p_mtl_trx_tbl(i).organization_id) = p_mtl_trx_tbl(i).organization_id
800 AND TRUNC(NVL(CCG.DISABLE_DATE,SYSDATE+1)) >= TRUNC(SYSDATE);
801 EXCEPTION
802 WHEN no_data_found THEN
803 IF (l_debug = 1) THEN
804 debug_print('Cost Group not found', 9);
805 END IF;
806 fnd_message.set_name('INV', 'INV_INT_CSTGRP');
807 fnd_msg_pub.ADD;
808 RAISE fnd_api.g_exc_error;
809 END;
810
811 IF (l_count = 0) THEN
812 IF (l_debug = 1) THEN
813 debug_print('Cost Group not found : ' || p_mtl_trx_tbl(i).cost_group_id, 9);
814 END IF;
815 fnd_message.set_name('INV', 'INV_INT_CSTGRP');
816 fnd_msg_pub.ADD;
817 RAISE fnd_api.g_exc_error;
818
819 END IF;
820
821 -- 17. Validate cost groups for transfer organziations.
822 -- Cost groups should already be populated before validation.
823
824 IF (p_mtl_trx_tbl(i).transaction_action_id IN
825 (INV_GLOBALS.G_ACTION_LOGICALICSALES, INV_GLOBALS.G_ACTION_LOGICALICRECEIPT,
826 INV_GLOBALS.G_ACTION_LOGICALICRCPTRETURN, INV_GLOBALS.G_ACTION_LOGICALICSALESRETURN,
827 INV_GLOBALS.G_ACTION_LOGICALEXPREQRECEIPT))THEN
828
829 IF (p_mtl_trx_tbl(i).transfer_cost_group_id IS NULL) THEN
830 IF (l_debug = 1) THEN
831 debug_print('Invalid Cost Group in the Transfer Org.', 9);
832 END IF;
833 fnd_message.set_name('INV', 'INV_INT_CSTGRP');
834 fnd_msg_pub.ADD;
835 RAISE fnd_api.g_exc_error;
836
837 END IF;
838
839 l_count := 0;
840
841 BEGIN
842 SELECT COUNT(1) INTO l_count
843 FROM CST_COST_GROUPS CCG
844 WHERE CCG.COST_GROUP_ID = p_mtl_trx_tbl(i).transfer_cost_group_id
845 AND NVL(CCG.ORGANIZATION_ID, p_mtl_trx_tbl(i).transfer_organization_id) = p_mtl_trx_tbl(i).transfer_organization_id
846 AND TRUNC(NVL(CCG.DISABLE_DATE,SYSDATE+1)) >= TRUNC(SYSDATE);
847 EXCEPTION
848 WHEN no_data_found THEN
849 IF (l_debug = 1) THEN
850 debug_print('Cost Group not found', 9);
851 END IF;
852 fnd_message.set_name('INV', 'INV_INT_CSTGRP');
853 fnd_msg_pub.ADD;
854 RAISE fnd_api.g_exc_error;
855 END;
856
857 IF (l_count = 0) THEN
858 IF (l_debug = 1) THEN
859 debug_print('Transfer Cost Group not found : ' || p_mtl_trx_tbl(i).transfer_cost_group_id , 9);
860 END IF;
861 fnd_message.set_name('INV', 'INV_INT_CSTGRP');
862 fnd_msg_pub.ADD;
863 RAISE fnd_api.g_exc_error;
864
865 END IF;
866
867
868 END IF;
869
870 --18. Validate transaction batch id and transaction batch sequence
871
872 IF (p_mtl_trx_tbl(i).transaction_batch_id IS NULL) OR
873 (p_mtl_trx_tbl(i).transaction_batch_seq IS NULL )THEN
874 IF (l_debug = 1) THEN
875 debug_print('Transaction batch and sequence are not populated', 9);
876 END IF;
877 fnd_message.set_name('INV', 'INV_INVALID_BATCH');
878 fnd_msg_pub.ADD;
879 RAISE fnd_api.g_exc_error;
880
881 END IF;
882
883 --19. If the locator is passed and it is project enabled, we would
884 -- have to make sure that the project and task is stamped on the line
885 --
886
887 IF (p_mtl_trx_tbl(i).locator_id IS NOT NULL) THEN
888
889 BEGIN
890 SELECT project_id, task_id INTO l_project_id, l_task_id FROM
891 mtl_item_locations WHERE inventory_location_id =
892 p_mtl_trx_tbl(i).locator_id AND organization_id =
893 p_mtl_trx_tbl(i).organization_id;
894 EXCEPTION
895 WHEN no_data_found THEN
896
897 IF (l_debug = 1) THEN
898 debug_print('Cannot find the locator information supplied', 9);
899 END IF;
900 WHEN others THEN
901 IF (l_debug = 1) THEN
902 debug_print('Invalid Locator ID', 9);
903 END IF;
904 fnd_message.set_name('INV', 'INV_INT_LOCCODE');
905 fnd_msg_pub.ADD;
906 RAISE fnd_api.g_exc_error;
907 END;
908
909
910 IF (l_project_id IS NOT NULL) THEN
911
912 IF (p_mtl_trx_tbl(i).project_id IS NULL) THEN
913 IF (l_debug = 1) THEN
914 debug_print('Invalid Project', 9);
915 END IF;
916 fnd_message.set_name('INV', 'INV_NO_PROJECT');
917 fnd_msg_pub.ADD;
918 RAISE fnd_api.g_exc_error;
919
920 END IF;
921
922 END IF;
923
924 IF (l_task_id IS NOT NULL) THEN
925
926 IF (p_mtl_trx_tbl(i).task_id IS NULL) then
927 IF (l_debug = 1) THEN
928 debug_print('Invalid Task', 9);
929 END IF;
930 fnd_message.set_name('INV', 'INV_NO_PROJECT');
931 fnd_msg_pub.ADD;
932 RAISE fnd_api.g_exc_error;
933
934 END IF;
935
936 END IF;
937
938 END IF;
939
940 -- Line level validation
941
942 -- 1. Validate the sales order that is passed for a logical sales
943 -- order issue transaction
944
945 l_count := 0;
946
947 IF ((p_mtl_trx_tbl(i).transaction_source_type_id = inv_globals.g_sourcetype_salesorder AND
948 p_mtl_trx_tbl(i).transaction_action_id = inv_globals.g_action_logicalissue) OR
949 (p_mtl_trx_tbl(i).transaction_source_type_id = inv_globals.g_sourcetype_rma AND
950 p_mtl_trx_tbl(i).transaction_action_id = inv_globals.g_action_logicalreceipt)) THEN
951
952 IF (p_mtl_trx_tbl(i).transaction_source_id IS NULL) THEN
953
954 IF (l_debug = 1) THEN
955 debug_print('Transaction Source ID is null', 9);
956 END IF;
957 fnd_message.set_name('INV', 'INV_COULD_NOT_GET_MSO_HEADER');
958 fnd_msg_pub.ADD;
959 RAISE fnd_api.g_exc_error;
960
961 END IF;
962
963
964 BEGIN
965 SELECT COUNT(1) INTO l_count FROM
966 mtl_sales_orders WHERE
967 sales_order_id = p_mtl_trx_tbl(i).transaction_source_id;
968
969 EXCEPTION
970 WHEN no_data_found THEN
971 IF (l_debug = 1) THEN
972 debug_print('Cannot find the sales order information', 9);
973 END IF;
974 fnd_message.set_name('INV', 'INV_COULD_NOT_GET_MSO_HEADER');
975 fnd_msg_pub.ADD;
976 RAISE fnd_api.g_exc_error;
977 END;
978
979 IF (l_count <> 1) THEN
980
981 IF (l_debug = 1) THEN
982 debug_print('Invalid sales order ID :' || p_mtl_trx_tbl(i).transaction_source_id, 9);
983 END IF;
984 fnd_message.set_name('INV', 'INV_COULD_NOT_GET_MSO_HEADER');
985 fnd_msg_pub.ADD;
986 RAISE fnd_api.g_exc_error;
987
988 END IF;
989
990 END IF;
991
992 -- 2. Validate the purchase order that is passed for a logical
993 -- po receipt or a logical RTV transaction
994
995 l_count := 0;
996
997 IF ((p_mtl_trx_tbl(i).transaction_source_type_id = inv_globals.g_sourcetype_purchaseorder
998 AND p_mtl_trx_tbl(i).transaction_action_id = inv_globals.g_action_logicalreceipt) OR
999 (p_mtl_trx_tbl(i).transaction_source_type_id = inv_globals.g_sourcetype_purchaseorder AND
1000 p_mtl_trx_tbl(i).transaction_action_id =
1001 inv_globals.g_action_logicalissue)) AND
1002 (p_logical_trx_type_code = INV_LOGICAL_TRANSACTION_GLOBAL.G_LOGTRXCODE_DSRECEIPT) THEN
1003
1004 IF ((p_mtl_trx_tbl(i).transaction_source_id IS NULL) OR (p_mtl_trx_tbl(i).rcv_transaction_id IS NULL)) THEN
1005
1006 IF (l_debug = 1) THEN
1007 debug_print('Transaction Source ID/rcv_transaction_id is null', 9);
1008 END IF;
1009 fnd_message.set_name('INV', 'INV_INT_PO');
1010 fnd_msg_pub.ADD;
1011 RAISE fnd_api.g_exc_error;
1012
1013 END IF;
1014
1015 BEGIN
1016 SELECT COUNT(1) INTO l_count FROM
1017 po_headers_all po, rcv_transactions rcv WHERE
1018 po.po_header_id = rcv.po_header_id AND
1019 po.po_header_id = p_mtl_trx_tbl(i).transaction_source_id AND
1020 rcv.transaction_id = p_mtl_trx_tbl(i).rcv_transaction_id AND
1021 NVL(po.START_DATE_ACTIVE, SYSDATE - 1) <= Sysdate AND
1022 NVL(po.END_DATE_ACTIVE, SYSDATE + 1) > SYSDATE AND ENABLED_FLAG = 'Y';
1023
1024 EXCEPTION
1025 WHEN no_data_found THEN
1026 IF (l_debug = 1) THEN
1027 debug_print('Cannot find the purchase order information', 9);
1028 END IF;
1029 fnd_message.set_name('INV', 'INV_INT_PO');
1030 fnd_msg_pub.ADD;
1031 RAISE fnd_api.g_exc_error;
1032 END;
1033
1034 IF (l_count <> 1) THEN
1035
1036 IF (l_debug = 1) THEN
1037 debug_print('Invalid puchase order header ID :' || p_mtl_trx_tbl(i).transaction_source_id, 9);
1038 END IF;
1039 fnd_message.set_name('INV', 'INV_INT_PO');
1040 fnd_msg_pub.ADD;
1041 RAISE fnd_api.g_exc_error;
1042
1043 END IF;
1044
1045 END IF;
1046
1047 -- 2. Validate the sales order line that is passed for a drop
1048 -- shipment or a global procurement flow.
1049
1050 l_count := 0;
1051
1052 IF (p_logical_trx_type_code IN
1053 (INV_LOGICAL_TRANSACTION_GLOBAL.G_LOGTRXCODE_DSDELIVER,
1054 INV_LOGICAL_TRANSACTION_GLOBAL.G_LOGTRXCODE_RMASOISSUE)) THEN
1055
1056 IF (p_mtl_trx_tbl(i).trx_source_line_id IS NULL) THEN
1057
1058 IF (l_debug = 1) THEN
1059 debug_print('Trx source line ID is null', 9);
1060 END IF;
1061 fnd_message.set_name('INV', 'INV_COULD_NOT_GET_MSO_HEADER');
1062 fnd_msg_pub.ADD;
1063 RAISE fnd_api.g_exc_error;
1064
1065 END IF;
1066
1067 BEGIN
1068 SELECT COUNT(1) INTO l_count FROM
1069 oe_order_lines_all WHERE
1070 line_id = p_mtl_trx_tbl(i).trx_source_line_id;
1071
1072 EXCEPTION
1073 WHEN no_data_found THEN
1074 IF (l_debug = 1) THEN
1075 debug_print('Cannot find the sales order line information', 9);
1076 END IF;
1077 fnd_message.set_name('INV', 'INV_COULD_NOT_GET_MSO_HEADER');
1078 fnd_msg_pub.ADD;
1079 RAISE fnd_api.g_exc_error;
1080 END;
1081
1082 IF (l_count <> 1) THEN
1083
1084 IF (l_debug = 1) THEN
1085 debug_print('Invalid sales order line :' || p_mtl_trx_tbl(i).transaction_source_id, 9);
1086 END IF;
1087 fnd_message.set_name('INV', 'INV_COULD_NOT_GET_MSO_HEADER');
1088 fnd_msg_pub.ADD;
1089 RAISE fnd_api.g_exc_error;
1090
1091 END IF;
1092
1093
1094 END IF;
1095
1096 IF (p_mtl_trx_tbl(i).transaction_action_id IN
1097 (inv_globals.g_action_logicalicsales,
1098 inv_globals.g_action_logicalicreceipt,
1099 inv_globals.g_action_logicalicrcptreturn,
1100 inv_globals.g_action_logicalicsalesreturn)) THEN
1101
1102 IF (p_mtl_trx_tbl(i).invoiced_flag <> 'N') THEN
1103
1104 IF (l_debug = 1) THEN
1105 debug_print('Invoiced flag is not set to N ', 9);
1106 END IF;
1107 fnd_message.set_name('INV', 'INV_INT_INVOICE_FLAG');
1108 fnd_msg_pub.ADD;
1109 RAISE fnd_api.g_exc_error;
1110
1111 END IF;
1112
1113 IF (p_mtl_trx_tbl(i).intercompany_cost IS NULL) OR
1114 (p_mtl_trx_tbl(i).intercompany_cost < 0) THEN
1115
1116 IF (l_debug = 1) THEN
1117 debug_print('I/C cost cannot be null ', 9);
1118 END IF;
1119 fnd_message.set_name('INV', 'INV_INT_IC_COST');
1120 fnd_msg_pub.ADD;
1121 RAISE fnd_api.g_exc_error;
1122
1123 END IF;
1124
1125 END IF;
1126
1127 IF (p_mtl_trx_tbl(i).costed_flag <> 'N') THEN
1128
1129 IF (l_debug = 1) THEN
1130 debug_print('Costed flag is not set to N ', 9);
1131 END IF;
1132 fnd_message.set_name('INV', 'INV_INT_COSTED_FLAG');
1133 fnd_msg_pub.ADD;
1134 RAISE fnd_api.g_exc_error;
1135
1136 END IF;
1137
1138 END IF; -- If p_validation level set to true.
1139
1140
1141 END LOOP; -- for loop for every record in the table of records
1142
1143 x_return_status := l_return_status;
1144
1145 EXCEPTION
1146 WHEN FND_API.G_EXC_ERROR THEN
1147 IF (l_debug = 1) THEN
1148 debug_print('Expected Error', 9);
1149 debug_print('SQL Error: ' || Sqlerrm(SQLCODE),1);
1150 END IF;
1151 x_return_status := FND_API.G_RET_STS_ERROR;
1152 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1153
1154 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1155 IF (l_debug = 1) THEN
1156 debug_print('Unexpected Error', 9);
1157 debug_print('SQL Error: ' || Sqlerrm(SQLCODE),1);
1158 END IF;
1159 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1160 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1161
1162 WHEN OTHERS THEN
1163 IF (l_debug = 1) THEN
1164 debug_print('Error Type Others', 9);
1165 debug_print('SQL Error: ' || Sqlerrm(SQLCODE),1);
1166 END IF;
1167 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1168 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1169
1170 END validate_input_parameters;
1171
1172
1173 /*==========================================================================*
1174 | Procedure : INV_MMT_INSERT |
1175 | |
1176 | Description : This API will be called by INV create logical transactions |
1177 | API to do a bulk insert into MTL_MATERIAL_TRANSACTIONS |
1178 | table. |
1179 | |
1180 | Input Parameters : |
1181 | p_api_version_number - API version number |
1182 | p_init_msg_lst - Whether initialize the error message list or not|
1183 | Should be fnd_api.g_false or fnd_api.g_true |
1184 | p_mtl_trx_rec - An array of mtl_trx_rec_type records |
1185 | |
1186 | Output Parameters : |
1187 | x_return_status - fnd_api.g_ret_sts_success, if succeeded |
1188 | fnd_api.g_ret_sts_exc_error, if an expected |
1189 | error occurred |
1190 | fnd_api.g_ret_sts_unexp_error, if an unexpected |
1191 | eror occurred |
1192 | x_msg_count - Number of error message in the error message |
1193 | list |
1194 | x_msg_data - If the number of error message in the error |
1195 | message list is one, the error message is in |
1196 | this output parameter |
1197 *==========================================================================*/
1198 PROCEDURE inv_mmt_insert
1199 (
1200 x_return_status OUT NOCOPY VARCHAR2
1201 , x_msg_count OUT NOCOPY NUMBER
1202 , x_msg_data OUT NOCOPY VARCHAR2
1203 , p_api_version_number IN NUMBER
1204 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
1205 , p_mtl_trx_tbl IN inv_logical_transaction_global.mtl_trx_tbl_type
1206 , p_logical_trx_type_code IN NUMBER
1207
1208
1209 )
1210 IS
1211 -- p_mtl_trx_tbl(i) inv_logical_transaction_global.mtl_trx_tbl_type := p_mtl_trx_tbl;
1212 l_debug NUMBER := NVL(fnd_profile.value('INV_DEBUG_TRACE'),0);
1213 l_api_version_number CONSTANT NUMBER := 1.0;
1214 l_api_name CONSTANT VARCHAR2(30) := 'Inv_Mmt_Insert';
1215 l_logical_transaction NUMBER := 1;
1216 l_quantity_adjusted NUMBER := 0;
1217 l_transaction_quantity NUMBER := 0;
1218 l_primary_quantity NUMBER := 0;
1219
1220 --
1221 -- Bug 5044147 umoogala 13-Feb-2006
1222 -- Issue: For process organizations, costed_flag is getting set to
1223 -- 'N' instead of opm_costed_flag.
1224 -- Resolution: Added code to get mtl_parameters.process_enabled_flag.
1225 -- Then in insert stmt, setting the costed_flag's based on
1226 -- variable value.
1227 --
1228 l_prev_organization_id BINARY_INTEGER := NULL;
1229 l_process_enabled_flag VARCHAR2(1) := NULL;
1230
1231 BEGIN
1232 IF (l_debug = 1) THEN
1233 debug_print('Enter inv_mmt_insert', 9);
1234 debug_print('p_api_version_number = ' || p_api_version_number, 9);
1235 debug_print('p_init_msg_lst = ' || p_init_msg_lst, 9);
1236 debug_print('p_logical_trx_type_code = ' || p_logical_trx_type_code, 9);
1237 END IF;
1238
1239 --
1240 -- Standard call to check for call compatibility
1241 IF NOT fnd_api.compatible_api_call(l_api_version_number, p_api_version_number, l_api_name, g_pkg_name) THEN
1242 RAISE fnd_api.g_exc_unexpected_error;
1243 END IF;
1244
1245 --
1246 -- Initialize message list.
1247 IF fnd_api.to_boolean(p_init_msg_lst) THEN
1248 fnd_msg_pub.initialize;
1249 END IF;
1250
1251 IF (p_logical_trx_type_code = INV_LOGICAL_TRANSACTIONS_PUB.G_LOGTRXCODE_RETROPRICEUPD) THEN
1252 l_logical_transaction := 2;
1253 ELSE
1254 l_logical_transaction := 1;
1255 END IF;
1256
1257
1258 IF (l_debug = 1) THEN
1259 debug_print('Inside inv insert API', 9);
1260 END IF;
1261
1262 FOR i in 1..p_mtl_trx_tbl.COUNT LOOP
1263
1264 IF (p_logical_trx_type_code = INV_LOGICAL_TRANSACTIONS_PUB.G_LOGTRXCODE_RETROPRICEUPD) THEN
1265 l_quantity_adjusted := p_mtl_trx_tbl(i).transaction_quantity;
1266 l_transaction_quantity := 0;
1267 l_primary_quantity := 0;
1268 ELSE
1269 l_transaction_quantity := p_mtl_trx_tbl(i).transaction_quantity;
1270 l_primary_quantity := p_mtl_trx_tbl(i).primary_quantity;
1271 l_quantity_adjusted := NULL;
1272 END IF;
1273
1274 --
1275 -- Bug 5044147 umoogala 13-Feb-2006
1276 -- Issue: For process organizations, costed_flag is getting set to
1277 -- 'N' instead of opm_costed_flag.
1278 -- Resolution: Added code to get mtl_parameters.process_enabled_flag.
1279 -- Then in insert stmt, setting the costed_flag's based on
1280 -- variable value.
1281 --
1282 IF l_prev_organization_id IS NULL OR
1283 p_mtl_trx_tbl(i).ORGANIZATION_ID <> l_prev_organization_id
1284 THEN
1285 l_prev_organization_id := p_mtl_trx_tbl(i).ORGANIZATION_ID;
1286
1287 SELECT NVL(process_enabled_flag, 'N')
1288 INTO l_process_enabled_flag
1289 FROM mtl_parameters
1290 WHERE organization_id = p_mtl_trx_tbl(i).ORGANIZATION_ID;
1291 END IF;
1292
1293
1294 INSERT
1295 INTO MTL_MATERIAL_TRANSACTIONS
1296 ( TRANSACTION_ID
1297 ,ORGANIZATION_ID
1298 ,INVENTORY_ITEM_ID
1299 ,REVISION
1300 ,SUBINVENTORY_CODE
1301 ,LOCATOR_ID
1302 ,TRANSACTION_TYPE_ID
1303 ,TRANSACTION_ACTION_ID
1304 ,TRANSACTION_SOURCE_TYPE_ID
1305 ,TRANSACTION_SOURCE_ID
1306 ,TRANSACTION_SOURCE_NAME
1307 ,TRANSACTION_QUANTITY
1308 ,TRANSACTION_UOM
1309 ,PRIMARY_QUANTITY
1310 ,TRANSACTION_DATE
1311 ,ACCT_PERIOD_ID
1312 ,DISTRIBUTION_ACCOUNT_ID
1313 ,COSTED_FLAG
1314 ,ACTUAL_COST
1315 ,INVOICED_FLAG
1316 ,TRANSACTION_COST
1317 ,CURRENCY_CODE
1318 ,CURRENCY_CONVERSION_RATE
1319 ,CURRENCY_CONVERSION_TYPE
1320 ,CURRENCY_CONVERSION_DATE
1321 ,PM_COST_COLLECTED
1322 ,TRX_SOURCE_LINE_ID
1323 ,SOURCE_CODE
1324 ,RCV_TRANSACTION_ID
1325 ,SOURCE_LINE_ID
1326 ,TRANSFER_ORGANIZATION_ID
1327 ,TRANSFER_SUBINVENTORY
1328 ,TRANSFER_LOCATOR_ID
1329 ,COST_GROUP_ID
1330 ,TRANSFER_COST_GROUP_ID
1331 ,PROJECT_ID
1332 ,TASK_ID
1333 ,TO_PROJECT_ID
1334 ,TO_TASK_ID
1335 ,SHIP_TO_LOCATION_ID
1336 ,TRANSACTION_MODE
1337 ,TRANSACTION_BATCH_ID
1338 ,TRANSACTION_BATCH_SEQ
1339 ,TRX_FLOW_HEADER_ID
1340 ,INTERCOMPANY_COST
1341 ,INTERCOMPANY_CURRENCY_CODE
1342 ,INTERCOMPANY_PRICING_OPTION
1343 ,parent_transaction_id
1344 ,lpn_id
1345 ,logical_trx_type_code
1346 ,logical_transaction
1347 ,last_update_date
1348 ,last_updated_by
1349 ,creation_date
1350 ,created_by
1351 ,last_update_login
1352 ,quantity_adjusted
1353 ,so_issue_account_type
1354 ,opm_costed_flag
1355 )
1356 VALUES
1357 ( p_mtl_trx_tbl(i).TRANSACTION_ID
1358 ,p_mtl_trx_tbl(i).ORGANIZATION_ID
1359 ,p_mtl_trx_tbl(i).INVENTORY_ITEM_ID
1360 ,p_mtl_trx_tbl(i).REVISION
1361 ,p_mtl_trx_tbl(i).SUBINVENTORY_CODE
1362 ,p_mtl_trx_tbl(i).LOCATOR_ID
1363 ,p_mtl_trx_tbl(i).TRANSACTION_TYPE_ID
1364 ,p_mtl_trx_tbl(i).TRANSACTION_ACTION_ID
1365 ,p_mtl_trx_tbl(i).TRANSACTION_SOURCE_TYPE_ID
1366 ,p_mtl_trx_tbl(i).TRANSACTION_SOURCE_ID
1367 ,p_mtl_trx_tbl(i).TRANSACTION_SOURCE_NAME
1368 ,l_transaction_quantity
1369 ,p_mtl_trx_tbl(i).TRANSACTION_UOM
1370 ,l_primary_quantity
1371 ,p_mtl_trx_tbl(i).TRANSACTION_DATE
1372 ,p_mtl_trx_tbl(i).ACCT_PERIOD_ID
1373 ,p_mtl_trx_tbl(i).DISTRIBUTION_ACCOUNT_ID
1374 ,decode(l_process_enabled_flag, 'N', p_mtl_trx_tbl(i).COSTED_FLAG, NULL) -- Bug 5044147
1375 ,p_mtl_trx_tbl(i).ACTUAL_COST
1376 ,p_mtl_trx_tbl(i).INVOICED_FLAG
1377 ,p_mtl_trx_tbl(i).TRANSACTION_COST
1378 ,p_mtl_trx_tbl(i).CURRENCY_CODE
1379 ,p_mtl_trx_tbl(i).CURRENCY_CONVERSION_RATE
1380 ,p_mtl_trx_tbl(i).CURRENCY_CONVERSION_TYPE
1381 ,p_mtl_trx_tbl(i).CURRENCY_CONVERSION_DATE
1382 ,p_mtl_trx_tbl(i).PM_COST_COLLECTED
1383 ,p_mtl_trx_tbl(i).TRX_SOURCE_LINE_ID
1384 ,p_mtl_trx_tbl(i).SOURCE_CODE
1385 ,p_mtl_trx_tbl(i).RCV_TRANSACTION_ID
1386 ,p_mtl_trx_tbl(i).SOURCE_LINE_ID
1387 ,p_mtl_trx_tbl(i).TRANSFER_ORGANIZATION_ID
1388 ,p_mtl_trx_tbl(i).TRANSFER_SUBINVENTORY
1389 ,p_mtl_trx_tbl(i).TRANSFER_LOCATOR_ID
1390 ,p_mtl_trx_tbl(i).COST_GROUP_ID
1391 ,p_mtl_trx_tbl(i).TRANSFER_COST_GROUP_ID
1392 ,p_mtl_trx_tbl(i).PROJECT_ID
1393 ,p_mtl_trx_tbl(i).TASK_ID
1394 ,p_mtl_trx_tbl(i).TO_PROJECT_ID
1395 ,p_mtl_trx_tbl(i).TO_TASK_ID
1396 ,p_mtl_trx_tbl(i).SHIP_TO_LOCATION_ID
1397 ,p_mtl_trx_tbl(i).TRANSACTION_MODE
1398 ,p_mtl_trx_tbl(i).TRANSACTION_BATCH_ID
1399 ,p_mtl_trx_tbl(i).TRANSACTION_BATCH_SEQ
1400 ,p_mtl_trx_tbl(i).TRX_FLOW_HEADER_ID
1401 ,p_mtl_trx_tbl(i).INTERCOMPANY_COST
1402 ,p_mtl_trx_tbl(i).INTERCOMPANY_CURRENCY_CODE
1403 ,p_mtl_trx_tbl(i).INTERCOMPANY_PRICING_OPTION
1404 ,p_mtl_trx_tbl(i).parent_transaction_id
1405 ,p_mtl_trx_tbl(i).lpn_id
1406 ,p_logical_trx_type_code
1407 ,l_logical_transaction
1408 ,Sysdate
1409 ,FND_GLOBAL.user_id
1410 ,Sysdate
1411 ,FND_GLOBAL.user_id
1412 ,FND_GLOBAL.login_id
1413 ,l_quantity_adjusted
1414 ,2--deffered cogs
1415 ,decode(l_process_enabled_flag, 'Y', 'N', NULL) -- Bug 5044147
1416 );
1417
1418 END LOOP;
1419
1420 x_return_status := fnd_api.g_ret_sts_success;
1421 IF (l_debug = 1) THEN
1422 debug_print('After inv insert', 9);
1423 debug_print('Return Status :' || x_return_status, 9);
1424 END IF;
1425
1426
1427 EXCEPTION
1428 WHEN FND_API.G_EXC_ERROR THEN
1429 x_return_status := FND_API.G_RET_STS_ERROR;
1430 IF (l_debug = 1) THEN
1431 debug_print('Expected Error', 9);
1432 debug_print('SQL Error: ' || Sqlerrm(SQLCODE),1);
1433 debug_print('Return Status :' || x_return_status);
1434 END IF;
1435 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1436
1437 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1438 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1439 IF (l_debug = 1) THEN
1440 debug_print('Expected Error', 9);
1441 debug_print('SQL Error: ' || Sqlerrm(SQLCODE),1);
1442 debug_print('Return Status :' || x_return_status);
1443 END IF;
1444 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1445
1446 WHEN OTHERS THEN
1447 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1448 IF (l_debug = 1) THEN
1449 debug_print('Error type others', 9);
1450 debug_print('SQL Error: ' || Sqlerrm(SQLCODE),1);
1451 debug_print('Return Status :' || x_return_status);
1452 END IF;
1453 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data =>
1454 x_msg_data);
1455
1456 END inv_mmt_insert;
1457
1458 /*==========================================================================*
1459 | Procedure : INV_LOT_SERIAL_INSERT |
1460 | |
1461 | Description : This API will be called by INV create_logical_transactions |
1462 | API to do a bulk insert into mtl_transaction_lot_numbers if|
1463 | the item is lot control and insert into |
1464 | mtl_unit_transactions if the item is serial control. |
1465 | |
1466 | Input Parameters : |
1467 | p_api_version_number - API version number |
1468 | p_init_msg_lst - Whether initialize the error message list or not|
1469 | Should be fnd_api.g_false or fnd_api.g_true |
1470 | p_parent_transaction_id - the transaction id of the parent transaction|
1471 | in mmt. |
1472 | p_transaction_id - the transaction id of the new logical |
1473 | transaction in mmt. |
1474 | p_lot_control_code - the lot control code of the item |
1475 | p_serial_control_code - the serial control code of the item |
1476 | |
1477 | Output Parameters : |
1478 | x_return_status - fnd_api.g_ret_sts_success, if succeeded |
1479 | fnd_api.g_ret_sts_exc_error, if an expected |
1480 | error occurred |
1481 | fnd_api.g_ret_sts_unexp_error, if an unexpected |
1482 | eror occurred |
1483 | x_msg_count - Number of error message in the error message |
1484 | list |
1485 | x_msg_data - If the number of error message in the error |
1486 | message list is one, the error message is in |
1487 | this output parameter |
1488 *==========================================================================*/
1489
1490 PROCEDURE inv_lot_serial_insert
1491 (
1492 x_return_status OUT NOCOPY VARCHAR2
1493 , x_msg_count OUT NOCOPY NUMBER
1494 , x_msg_data OUT NOCOPY VARCHAR2
1495 , p_api_version_number IN NUMBER := 1.0
1496 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
1497 , p_parent_transaction_id IN NUMBER
1498 , p_transaction_id IN NUMBER
1499 , p_lot_control_code IN NUMBER
1500 , p_serial_control_code IN NUMBER
1501 , p_organization_id IN NUMBER
1502 , p_inventory_item_id IN NUMBER
1503 , p_primary_quantity IN NUMBER
1504 , p_trx_source_type_id IN NUMBER
1505 , p_revision IN VARCHAR2
1506 )
1507 IS
1508 l_debug NUMBER := NVL(fnd_profile.value('INV_DEBUG_TRACE'),0);
1509 l_api_version_number CONSTANT NUMBER := 1.0;
1510 l_api_name CONSTANT VARCHAR2(30) := 'Inv_Mmt_Insert';
1511 l_serial_number_tbl VARCHAR30_TBL;
1512 l_return_status VARCHAR2(1);
1513 l_msg_count NUMBER;
1514 l_msg_data VARCHAR2(2000);
1515
1516 l_serial_transaction_id NUMBER;
1517
1518 cursor mtln_cur(p_transaction_id NUMBER) IS
1519 SELECT INVENTORY_ITEM_ID,
1520 ORGANIZATION_ID,
1521 TRANSACTION_SOURCE_ID,
1522 TRANSACTION_SOURCE_TYPE_ID,
1523 TRANSACTION_SOURCE_NAME,
1524 TRANSACTION_QUANTITY,
1525 PRIMARY_QUANTITY,
1526 LOT_NUMBER,
1527 SERIAL_TRANSACTION_ID,
1528 DESCRIPTION,
1529 VENDOR_NAME,
1530 SUPPLIER_LOT_NUMBER,
1531 ORIGINATION_DATE,
1532 DATE_CODE,
1533 GRADE_CODE,
1534 CHANGE_DATE,
1535 MATURITY_DATE,
1536 STATUS_ID,
1537 RETEST_DATE,
1538 AGE,
1539 ITEM_SIZE,
1540 COLOR,
1541 VOLUME,
1542 VOLUME_UOM,
1543 PLACE_OF_ORIGIN,
1544 BEST_BY_DATE,
1545 LENGTH,
1546 LENGTH_UOM,
1547 WIDTH,
1548 WIDTH_UOM,
1549 RECYCLED_CONTENT,
1550 THICKNESS,
1551 THICKNESS_UOM,
1552 CURL_WRINKLE_FOLD,
1553 LOT_ATTRIBUTE_CATEGORY,
1554 C_ATTRIBUTE1,
1555 C_ATTRIBUTE2,
1556 C_ATTRIBUTE3,
1557 C_ATTRIBUTE4,
1558 C_ATTRIBUTE5,
1559 C_ATTRIBUTE6,
1560 C_ATTRIBUTE7,
1561 C_ATTRIBUTE8,
1562 C_ATTRIBUTE9,
1563 C_ATTRIBUTE10,
1564 C_ATTRIBUTE11,
1565 C_ATTRIBUTE12,
1566 C_ATTRIBUTE13,
1567 C_ATTRIBUTE14,
1568 C_ATTRIBUTE15,
1569 C_ATTRIBUTE16,
1570 C_ATTRIBUTE17,
1571 C_ATTRIBUTE18,
1572 C_ATTRIBUTE19,
1573 C_ATTRIBUTE20,
1574 D_ATTRIBUTE1,
1575 D_ATTRIBUTE2,
1576 D_ATTRIBUTE3,
1577 D_ATTRIBUTE4,
1578 D_ATTRIBUTE5,
1579 D_ATTRIBUTE6,
1580 D_ATTRIBUTE7,
1581 D_ATTRIBUTE8,
1582 D_ATTRIBUTE9,
1583 D_ATTRIBUTE10,
1584 N_ATTRIBUTE1,
1585 N_ATTRIBUTE2,
1586 N_ATTRIBUTE3,
1587 N_ATTRIBUTE4,
1588 N_ATTRIBUTE5,
1589 N_ATTRIBUTE6,
1590 N_ATTRIBUTE7,
1591 N_ATTRIBUTE8,
1592 N_ATTRIBUTE9,
1593 N_ATTRIBUTE10,
1594 VENDOR_ID,
1595 TERRITORY_CODE,
1596 PRODUCT_CODE,
1597 PRODUCT_TRANSACTION_ID,
1598 ATTRIBUTE_CATEGORY,
1599 ATTRIBUTE1,
1600 ATTRIBUTE2,
1601 ATTRIBUTE3,
1602 ATTRIBUTE4,
1603 ATTRIBUTE5,
1604 ATTRIBUTE6,
1605 ATTRIBUTE7,
1606 ATTRIBUTE8,
1607 ATTRIBUTE9,
1608 ATTRIBUTE10,
1609 ATTRIBUTE11,
1610 ATTRIBUTE12,
1611 ATTRIBUTE13,
1612 ATTRIBUTE14,
1613 ATTRIBUTE15
1614 FROM mtl_transaction_lot_numbers
1615 WHERE transaction_id = p_transaction_id;
1616 BEGIN
1617
1618 IF (l_debug = 1) THEN
1619 debug_print('Enter inv_mmt_insert', 9);
1620 debug_print('p_api_version_number = ' || p_api_version_number, 9);
1621 debug_print('p_init_msg_lst = ' || p_init_msg_lst, 9);
1622 debug_print('p_parent_transaction_id = ' || p_parent_transaction_id, 9);
1623 debug_print('p_transaction_id = ' || p_transaction_id, 9);
1624 debug_print('p_lot_control_code = ' || p_lot_control_code, 9);
1625 debug_print('p_serial_control_code = ' || p_serial_control_code, 9);
1626 END IF;
1627
1628 IF (p_lot_control_code = 2) THEN
1629 IF (p_serial_control_code in (2, 5, 6)) THEN
1630 SELECT mtl_material_transactions_s.nextval
1631 INTO l_serial_transaction_id
1632 FROM dual;
1633 ELSE
1634 l_serial_transaction_id := null;
1635 END IF;
1636
1637 IF (l_debug = 1) THEN
1638 debug_print('l_serial_transaction_id = ' || l_serial_transaction_id, 9);
1639 END IF;
1640
1641 For l_mtln IN mtln_cur(p_parent_transaction_id) LOOP
1642 IF (l_debug = 1) THEN
1643 debug_print('In the mtln_cur loop: lot_number = ' || l_mtln.lot_number, 9);
1644 END IF;
1645
1646 -- insert into mtln same as the one of parent transaction id
1647 -- with the logical intercompany issue type
1648 INSERT INTO mtl_transaction_lot_numbers
1649 ( TRANSACTION_ID
1650 ,LAST_UPDATE_DATE
1651 ,LAST_UPDATED_BY
1652 ,CREATION_DATE
1653 ,CREATED_BY
1654 ,LAST_UPDATE_LOGIN
1655 ,INVENTORY_ITEM_ID
1656 ,ORGANIZATION_ID
1657 ,TRANSACTION_DATE
1658 ,TRANSACTION_SOURCE_ID
1659 ,TRANSACTION_SOURCE_TYPE_ID
1660 ,TRANSACTION_SOURCE_NAME
1661 ,TRANSACTION_QUANTITY
1662 ,PRIMARY_QUANTITY
1663 ,LOT_NUMBER
1664 ,SERIAL_TRANSACTION_ID
1665 ,DESCRIPTION
1666 ,VENDOR_NAME
1667 ,SUPPLIER_LOT_NUMBER
1668 ,ORIGINATION_DATE
1669 ,DATE_CODE
1670 ,GRADE_CODE
1671 ,CHANGE_DATE
1672 ,MATURITY_DATE
1673 ,STATUS_ID
1674 ,RETEST_DATE
1675 ,AGE
1676 ,ITEM_SIZE
1677 ,COLOR
1678 ,VOLUME
1679 ,VOLUME_UOM
1680 ,PLACE_OF_ORIGIN
1681 ,BEST_BY_DATE
1682 ,LENGTH
1683 ,LENGTH_UOM
1684 ,WIDTH
1685 ,WIDTH_UOM
1686 ,RECYCLED_CONTENT
1687 ,THICKNESS
1688 ,THICKNESS_UOM
1689 ,CURL_WRINKLE_FOLD
1690 ,LOT_ATTRIBUTE_CATEGORY
1691 ,C_ATTRIBUTE1
1692 ,C_ATTRIBUTE2
1693 ,C_ATTRIBUTE3
1694 ,C_ATTRIBUTE4
1695 ,C_ATTRIBUTE5
1696 ,C_ATTRIBUTE6
1697 ,C_ATTRIBUTE7
1698 ,C_ATTRIBUTE8
1699 ,C_ATTRIBUTE9
1700 ,C_ATTRIBUTE10
1701 ,C_ATTRIBUTE11
1702 ,C_ATTRIBUTE12
1703 ,C_ATTRIBUTE13
1704 ,C_ATTRIBUTE14
1705 ,C_ATTRIBUTE15
1706 ,C_ATTRIBUTE16
1707 ,C_ATTRIBUTE17
1708 ,C_ATTRIBUTE18
1709 ,C_ATTRIBUTE19
1710 ,C_ATTRIBUTE20
1711 ,D_ATTRIBUTE1
1712 ,D_ATTRIBUTE2
1713 ,D_ATTRIBUTE3
1714 ,D_ATTRIBUTE4
1715 ,D_ATTRIBUTE5
1716 ,D_ATTRIBUTE6
1717 ,D_ATTRIBUTE7
1718 ,D_ATTRIBUTE8
1719 ,D_ATTRIBUTE9
1720 ,D_ATTRIBUTE10
1721 ,N_ATTRIBUTE1
1722 ,N_ATTRIBUTE2
1723 ,N_ATTRIBUTE3
1724 ,N_ATTRIBUTE4
1725 ,N_ATTRIBUTE5
1726 ,N_ATTRIBUTE6
1727 ,N_ATTRIBUTE7
1728 ,N_ATTRIBUTE8
1729 ,N_ATTRIBUTE9
1730 ,N_ATTRIBUTE10
1731 ,VENDOR_ID
1732 ,TERRITORY_CODE
1733 ,PRODUCT_CODE
1734 ,PRODUCT_TRANSACTION_ID
1735 ,ATTRIBUTE_CATEGORY
1736 ,ATTRIBUTE1
1737 ,ATTRIBUTE2
1738 ,ATTRIBUTE3
1739 ,ATTRIBUTE4
1740 ,ATTRIBUTE5
1741 ,ATTRIBUTE6
1742 ,ATTRIBUTE7
1743 ,ATTRIBUTE8
1744 ,ATTRIBUTE9
1745 ,ATTRIBUTE10
1746 ,ATTRIBUTE11
1747 ,ATTRIBUTE12
1748 ,ATTRIBUTE13
1749 ,ATTRIBUTE14
1750 ,ATTRIBUTE15
1751 )
1752 VALUES(
1753 p_transaction_id
1754 ,SYSDATE
1755 ,FND_GLOBAL.user_id
1756 ,SYSDATE
1757 ,FND_GLOBAL.user_id
1758 ,FND_GLOBAL.login_id
1759 ,l_mtln.INVENTORY_ITEM_ID
1760 ,l_mtln.ORGANIZATION_ID
1761 ,SYSDATE
1762 ,l_mtln.TRANSACTION_SOURCE_ID
1763 ,l_mtln.TRANSACTION_SOURCE_TYPE_ID
1764 ,l_mtln.TRANSACTION_SOURCE_NAME
1765 ,l_mtln.TRANSACTION_QUANTITY
1766 ,l_mtln.PRIMARY_QUANTITY
1767 ,l_mtln.LOT_NUMBER
1768 ,l_serial_transaction_id
1769 ,l_mtln.DESCRIPTION
1770 ,l_mtln.VENDOR_NAME
1771 ,l_mtln.SUPPLIER_LOT_NUMBER
1772 ,l_mtln.ORIGINATION_DATE
1773 ,l_mtln.DATE_CODE
1774 ,l_mtln.GRADE_CODE
1775 ,l_mtln.CHANGE_DATE
1776 ,l_mtln.MATURITY_DATE
1777 ,l_mtln.STATUS_ID
1778 ,l_mtln.RETEST_DATE
1779 ,l_mtln.AGE
1780 ,l_mtln.ITEM_SIZE
1781 ,l_mtln.COLOR
1782 ,l_mtln.VOLUME
1783 ,l_mtln.VOLUME_UOM
1784 ,l_mtln.PLACE_OF_ORIGIN
1785 ,l_mtln.BEST_BY_DATE
1786 ,l_mtln.LENGTH
1787 ,l_mtln.LENGTH_UOM
1788 ,l_mtln.WIDTH
1789 ,l_mtln.WIDTH_UOM
1790 ,l_mtln.RECYCLED_CONTENT
1791 ,l_mtln.THICKNESS
1792 ,l_mtln.THICKNESS_UOM
1793 ,l_mtln.CURL_WRINKLE_FOLD
1794 ,l_mtln.LOT_ATTRIBUTE_CATEGORY
1795 ,l_mtln.C_ATTRIBUTE1
1796 ,l_mtln.C_ATTRIBUTE2
1797 ,l_mtln.C_ATTRIBUTE3
1798 ,l_mtln.C_ATTRIBUTE4
1799 ,l_mtln.C_ATTRIBUTE5
1800 ,l_mtln.C_ATTRIBUTE6
1801 ,l_mtln.C_ATTRIBUTE7
1802 ,l_mtln.C_ATTRIBUTE8
1803 ,l_mtln.C_ATTRIBUTE9
1804 ,l_mtln.C_ATTRIBUTE10
1805 ,l_mtln.C_ATTRIBUTE11
1806 ,l_mtln.C_ATTRIBUTE12
1807 ,l_mtln.C_ATTRIBUTE13
1808 ,l_mtln.C_ATTRIBUTE14
1809 ,l_mtln.C_ATTRIBUTE15
1810 ,l_mtln.C_ATTRIBUTE16
1811 ,l_mtln.C_ATTRIBUTE17
1812 ,l_mtln.C_ATTRIBUTE18
1813 ,l_mtln.C_ATTRIBUTE19
1814 ,l_mtln.C_ATTRIBUTE20
1815 ,l_mtln.D_ATTRIBUTE1
1816 ,l_mtln.D_ATTRIBUTE2
1817 ,l_mtln.D_ATTRIBUTE3
1818 ,l_mtln.D_ATTRIBUTE4
1819 ,l_mtln.D_ATTRIBUTE5
1820 ,l_mtln.D_ATTRIBUTE6
1821 ,l_mtln.D_ATTRIBUTE7
1822 ,l_mtln.D_ATTRIBUTE8
1823 ,l_mtln.D_ATTRIBUTE9
1824 ,l_mtln.D_ATTRIBUTE10
1825 ,l_mtln.N_ATTRIBUTE1
1826 ,l_mtln.N_ATTRIBUTE2
1827 ,l_mtln.N_ATTRIBUTE3
1828 ,l_mtln.N_ATTRIBUTE4
1829 ,l_mtln.N_ATTRIBUTE5
1830 ,l_mtln.N_ATTRIBUTE6
1831 ,l_mtln.N_ATTRIBUTE7
1832 ,l_mtln.N_ATTRIBUTE8
1833 ,l_mtln.N_ATTRIBUTE9
1834 ,l_mtln.N_ATTRIBUTE10
1835 ,l_mtln.VENDOR_ID
1836 ,l_mtln.TERRITORY_CODE
1837 ,l_mtln.PRODUCT_CODE
1838 ,l_mtln.PRODUCT_TRANSACTION_ID
1839 ,l_mtln.ATTRIBUTE_CATEGORY
1840 ,l_mtln.ATTRIBUTE1
1841 ,l_mtln.ATTRIBUTE2
1842 ,l_mtln.ATTRIBUTE3
1843 ,l_mtln.ATTRIBUTE4
1844 ,l_mtln.ATTRIBUTE5
1845 ,l_mtln.ATTRIBUTE6
1846 ,l_mtln.ATTRIBUTE7
1847 ,l_mtln.ATTRIBUTE8
1848 ,l_mtln.ATTRIBUTE9
1849 ,l_mtln.ATTRIBUTE10
1850 ,l_mtln.ATTRIBUTE11
1851 ,l_mtln.ATTRIBUTE12
1852 ,l_mtln.ATTRIBUTE13
1853 ,l_mtln.ATTRIBUTE14
1854 ,l_mtln.ATTRIBUTE15
1855 );
1856
1857 -- If it's serial control and the serial_number_control_code is 2, 5
1858 -- then also insert into the mtl_unit_transactions
1859 -- serial_number_control_code = 2 -- Predefined serial numbers
1860 -- serial_number_control_code = 5 -- Dynamic entry at inventory receipt
1861 IF (p_serial_control_code in (2, 5)) THEN
1862 IF (l_debug = 1) THEN
1863 debug_print('Before calling inv_mut_insert', 9);
1864 debug_print('serial_transaction_id is ' || l_mtln.serial_transaction_id, 9);
1865 END IF;
1866
1867 inv_mut_insert
1868 ( x_return_status => x_return_status,
1869 x_msg_count => x_msg_count,
1870 x_msg_data => x_msg_data,
1871 x_serial_number_tbl => l_serial_number_tbl,
1872 p_parent_serial_trx_id => l_mtln.serial_transaction_id,
1873 p_serial_transaction_id => l_serial_transaction_id,
1874 p_organization_id => null,
1875 p_inventory_item_id => null,
1876 p_trx_source_type_id => null,
1877 p_receipt_issue_type => null);
1878
1879 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1880 IF (l_debug = 1) THEN
1881 debug_print('generate_serial_number returns error: ' || x_msg_data, 9);
1882 END IF;
1883 RAISE FND_API.G_EXC_ERROR;
1884 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1885 IF (l_debug = 1) THEN
1886 debug_print('inv_mut_insert returns unexpected error: ' || x_msg_data, 9);
1887 END IF;
1888 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1889 END IF;
1890
1891 ELSIF (p_serial_control_code = 6) THEN
1892 IF (l_debug = 1) THEN
1893 debug_print('serial_transaction_id is ' || l_mtln.serial_transaction_id, 9);
1894 END IF;
1895
1896 -- generate serial number with the primary lot qty
1897 generate_serial_numbers
1898 (x_return_status => x_return_status,
1899 x_msg_count => x_msg_count,
1900 x_msg_data => x_msg_data,
1901 x_ser_num_tbl => l_serial_number_tbl,
1902 p_org_id => p_organization_id,
1903 p_item_id => p_inventory_item_id,
1904 p_lot_number => l_mtln.lot_number,
1905 p_qty => p_primary_quantity,
1906 p_revision => p_revision);
1907
1908 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1909 IF (l_debug = 1) THEN
1910 debug_print('generate_serial_number returns error: ' || x_msg_data, 9);
1911 END IF;
1912 RAISE FND_API.G_EXC_ERROR;
1913 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1914 IF (l_debug = 1) THEN
1915 debug_print('generate_serial_number returns unexpected error: ' || x_msg_data, 9);
1916 END IF;
1917 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1918 END IF;
1919
1920
1921 inv_mut_insert
1922 (x_return_status => x_return_status,
1923 x_msg_count => x_msg_count,
1924 x_msg_data => x_msg_data,
1925 x_serial_number_tbl => l_serial_number_tbl,
1926 p_parent_serial_trx_id => null,
1927 p_serial_transaction_id => l_serial_transaction_id,
1928 p_organization_id => l_mtln.organization_id,
1929 p_inventory_item_id => l_mtln.inventory_item_id,
1930 p_trx_source_type_id => l_mtln.transaction_source_type_id,
1931 p_receipt_issue_type => 1);
1932
1933 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1934 IF (l_debug = 1) THEN
1935 debug_print('inv_mut_insert returns error: ' || x_msg_data, 9);
1936 END IF;
1937 RAISE FND_API.G_EXC_ERROR;
1938 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1939 IF (l_debug = 1) THEN
1940 debug_print('inv_mut_insert returns unexpected error: ' || x_msg_data, 9);
1941 END IF;
1942 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1943 END IF;
1944
1945 END IF; -- end of if serial_control_code is in (2,5)
1946 END LOOP; -- end of loop l_mtln
1947 ELSE -- not lot controll
1948 IF (l_debug = 1) THEN
1949 debug_print('It is not lot controlled', 9);
1950 END IF;
1951
1952 IF (p_serial_control_code in (2,5)) THEN
1953 IF (l_debug = 1) THEN
1954 debug_print('serial_control_code is ' || p_serial_control_code, 9);
1955 debug_print('Before calling inv_mut_insert', 9);
1956 END IF;
1957
1958 inv_mut_insert
1959 ( x_return_status => x_return_status,
1960 x_msg_count => x_msg_count,
1961 x_msg_data => x_msg_data,
1962 x_serial_number_tbl => l_serial_number_tbl,
1963 p_parent_serial_trx_id => p_parent_transaction_id,
1964 p_serial_transaction_id => p_transaction_id,
1965 p_organization_id => null,
1966 p_inventory_item_id => null,
1967 p_trx_source_type_id => null,
1968 p_receipt_issue_type => null);
1969
1970 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1971 IF (l_debug = 1) THEN
1972 debug_print('generate_serial_number returns error: ' || x_msg_data, 9);
1973 END IF;
1974 RAISE FND_API.G_EXC_ERROR;
1975 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1976 IF (l_debug = 1) THEN
1977 debug_print('inv_mut_insert returns unexpected error: ' || x_msg_data, 9);
1978 END IF;
1979 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1980 END IF;
1981
1982 ELSIF (p_serial_control_code = 6) THEN
1983 IF (l_debug = 1) THEN
1984 debug_print('Before calling generate_serial_numbers', 9);
1985 debug_print('serial_transaction_id is ' || p_transaction_id, 9);
1986 END IF;
1987
1988 -- generate serial number with the primary lot qty
1989 generate_serial_numbers
1990 (x_return_status => x_return_status,
1991 x_msg_count => x_msg_count,
1992 x_msg_data => x_msg_data,
1993 x_ser_num_tbl => l_serial_number_tbl,
1994 p_org_id => p_organization_id,
1995 p_item_id => p_inventory_item_id,
1996 p_lot_number => null,
1997 p_qty => p_primary_quantity,
1998 p_revision => p_revision);
1999
2000 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2001 IF (l_debug = 1) THEN
2002 debug_print('generate_serial_number returns error: ' || x_msg_data, 9);
2003 END IF;
2004 RAISE FND_API.G_EXC_ERROR;
2005 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2006 IF (l_debug = 1) THEN
2007 debug_print('generate_serial_number returns unexpected error: ' || x_msg_data, 9);
2008 END IF;
2009 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2010 END IF;
2011
2012 IF (l_debug = 1) THEN
2013 debug_print('generate_serial_numbers returns success', 9);
2014 debug_print('Before calling inv_mut_insert', 9);
2015 debug_print('p_serial_transaction_id = ' || p_transaction_id, 9);
2016 END IF;
2017
2018 inv_mut_insert
2019 (x_return_status => x_return_status,
2020 x_msg_count => x_msg_count,
2021 x_msg_data => x_msg_data,
2022 x_serial_number_tbl => l_serial_number_tbl,
2023 p_parent_serial_trx_id => null,
2024 p_serial_transaction_id => p_transaction_id,
2025 p_organization_id => p_organization_id,
2026 p_inventory_item_id => p_inventory_item_id,
2027 p_trx_source_type_id => p_trx_source_type_id,
2028 p_receipt_issue_type => 1);
2029
2030 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2031 IF (l_debug = 1) THEN
2032 debug_print('inv_mut_insert returns error: ' || x_msg_data, 9);
2033 END IF;
2034 RAISE FND_API.G_EXC_ERROR;
2035 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2036 IF (l_debug = 1) THEN
2037 debug_print('inv_mut_insert returns unexpected error: ' || x_msg_data, 9);
2038 END IF;
2039 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2040 END IF;
2041 END IF;
2042 END IF; -- p_lot_control_code = 2
2043
2044 IF (p_serial_control_code in (2, 5, 6)) THEN
2045 IF (l_debug = 1) THEN
2046 debug_print('serial_control_code = ' || p_serial_control_code, 9);
2047 debug_print('Before calling update_serial_numbers', 9);
2048 END IF;
2049
2050 update_serial_numbers
2051 (x_return_status => x_return_status,
2052 x_msg_count => x_msg_count,
2053 x_msg_data => x_msg_data,
2054 p_ser_num_tbl => l_serial_number_tbl,
2055 p_organization_id => p_organization_id,
2056 p_inventory_item_id => p_inventory_item_id);
2057
2058 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2059 IF (l_debug = 1) THEN
2060 debug_print('update_serial_numbers returns error: ' || x_msg_data, 9);
2061 END IF;
2062 RAISE FND_API.G_EXC_ERROR;
2063 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2064 IF (l_debug = 1) THEN
2065 debug_print('update_serial_numbers returns unexpected error: ' || x_msg_data, 9);
2066 END IF;
2067 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2068 END IF;
2069 END IF;
2070
2071 x_return_status := fnd_api.g_ret_sts_success;
2072 IF (l_debug = 1) THEN
2073 debug_print('Before returning from inv_lot_serial_insert', 9);
2074 debug_print('Return Status :' || x_return_status, 9);
2075 END IF;
2076 EXCEPTION
2077 WHEN FND_API.G_EXC_ERROR THEN
2078 x_return_status := FND_API.G_RET_STS_ERROR;
2079 IF (l_debug = 1) THEN
2080 debug_print('Expected Error', 9);
2081 debug_print('SQL Error: ' || Sqlerrm(SQLCODE),1);
2082 debug_print('Return Status :' || x_return_status);
2083 END IF;
2084 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2085
2086 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2087 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2088 IF (l_debug = 1) THEN
2089 debug_print('Expected Error', 9);
2090 debug_print('SQL Error: ' || Sqlerrm(SQLCODE),1);
2091 debug_print('Return Status :' || x_return_status);
2092 END IF;
2093 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2094
2095 WHEN OTHERS THEN
2096 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2097 IF (l_debug = 1) THEN
2098 debug_print('Error type others', 9);
2099 debug_print('SQL Error: ' || Sqlerrm(SQLCODE),1);
2100 debug_print('Return Status :' || x_return_status);
2101 END IF;
2102 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data =>
2103 x_msg_data);
2104 END inv_lot_serial_insert;
2105
2106 PROCEDURE generate_serial_numbers
2107 (
2108 x_return_status OUT NOCOPY VARCHAR2
2109 , x_msg_count OUT NOCOPY NUMBER
2110 , x_msg_data OUT NOCOPY VARCHAR2
2111 , x_ser_num_tbl OUT NOCOPY VARCHAR30_TBL
2112 , p_org_id IN NUMBER
2113 , p_item_id IN NUMBER
2114 , p_lot_number IN VARCHAR2
2115 , p_qty IN NUMBER
2116 , p_revision IN VARCHAR2
2117 )
2118 IS
2119 l_debug NUMBER := NVL(fnd_profile.value('INV_DEBUG_TRACE'),0);
2120 l_start_ser VARCHAR2(30);
2121 l_end_ser VARCHAR2(30);
2122 l_ser_prefix VARCHAR2(30);
2123 l_from_ser_num VARCHAR2(30);
2124 l_to_ser_num VARCHAR2(30);
2125 l_ser_suffix_length NUMBER;
2126 l_qty VARCHAR2(30);
2127 l_number NUMBER;
2128 l_errorcode NUMBER;
2129 l_retval NUMBER;
2130 l_msg_data VARCHAR2(2000);
2131 BEGIN
2132 IF (l_debug = 1) THEN
2133 debug_print('Enter generate_serial_numbers', 9);
2134 debug_print('p_org_id = ' || p_org_id, 9);
2135 debug_print('p_item_id = ' || p_item_id, 9);
2136 debug_print('p_lot_number = ' || p_lot_number, 9);
2137 debug_print('p_qty = ' || p_qty, 9);
2138 debug_print('p_revision = ' || p_revision, 9);
2139 debug_print('Before calling INV_SERIAL_NUMBER_PUB.generate_serials', 9);
2140 END IF;
2141
2142 -- generate serial number with the primary lot qty
2143 l_retval := INV_SERIAL_NUMBER_PUB.generate_serials
2144 (p_org_id => p_org_id,
2145 p_item_id => p_item_id,
2146 p_qty => abs(p_qty),
2147 p_wip_id => null,
2148 p_rev => p_revision,
2149 p_lot => p_lot_number,
2150 p_group_mark_id => null,
2151 p_line_mark_id => null,
2152 x_start_ser => l_start_ser,
2153 x_end_ser => l_end_ser,
2154 x_proc_msg => l_msg_data,
2155 p_skip_serial => null);
2156
2157 IF (l_debug = 1) THEN
2158 debug_print('INV_SERIAL_NUMBER_PUB.generate_serials returns l_retval = '
2159 || l_retval, 9);
2160 debug_print('l_start_ser = ' || l_start_ser, 9);
2161 debug_print('l_end_ser = ' || l_end_ser, 9);
2162 debug_print('Before calling MTL_SERIAL_CHECK.INV_SERIAL_INFO', 9);
2163 END IF;
2164
2165 -- get the prefix and from number of the start serial number
2166 IF NOT MTL_SERIAL_CHECK.INV_SERIAL_INFO
2167 (p_from_serial_number => l_start_ser,
2168 p_to_serial_number => l_end_ser,
2169 x_prefix => l_ser_prefix,
2170 x_quantity => l_qty,
2171 x_from_number => l_from_ser_num,
2172 x_to_number => l_to_ser_num,
2173 x_errorcode => l_errorcode) THEN
2174
2175 IF (l_debug = 1) THEN
2176 debug_print('MTL_SERIAL_CHECK.INV_SERIAL_INFO returns error', 9);
2177 debug_print('error code: ' || l_errorcode, 9);
2178 END IF;
2179
2180 FND_MESSAGE.SET_NAME('INV', 'INV_GET_SER_INFO_ERR');
2181 FND_MSG_PUB.ADD;
2182 RAISE FND_API.G_EXC_ERROR;
2183 END IF;
2184
2185 IF (l_debug = 1) THEN
2186 debug_print('MTL_SERIAL_CHECK.INV_SERIAL_INFO returns true', 9);
2187 debug_print('l_ser_prefix = ' || l_ser_prefix, 9);
2188 debug_print('l_qty = ' || l_qty, 9);
2189 debug_print('l_from_ser_num = ' || l_from_ser_num, 9);
2190 debug_print('l_to_ser_num = ' || l_to_ser_num, 9);
2191 END IF;
2192
2193 l_ser_suffix_length := LENGTH(l_from_ser_num);
2194 l_number := to_number(l_from_ser_num);
2195 FOR i in 1..l_qty LOOP
2196 x_ser_num_tbl(i) := l_ser_prefix || LPAD(TO_CHAR(l_number), l_ser_suffix_length, '0');
2197 l_number := l_number + 1;
2198 IF (l_debug = 1) THEN
2199 debug_print('serial number: ' || x_ser_num_tbl(i), 9);
2200 END IF;
2201 END LOOP;
2202
2203 IF (x_ser_num_tbl(l_qty) <> l_end_ser) THEN
2204 IF (l_debug = 1) THEN
2205 debug_print('x_ser_num_tbl(l_qty) is ' || x_ser_num_tbl(l_qty), 9);
2206 debug_print('l_end_ser is ' || l_end_ser, 9);
2207 END IF;
2208 END IF;
2209
2210 x_return_status := fnd_api.g_ret_sts_success;
2211 IF (l_debug = 1) THEN
2212 debug_print('Before return from generate_serial_numbers', 9);
2213 debug_print('Return Status :' || x_return_status, 9);
2214 END IF;
2215
2216 EXCEPTION
2217 WHEN FND_API.G_EXC_ERROR THEN
2218 x_return_status := FND_API.G_RET_STS_ERROR;
2219 IF (l_debug = 1) THEN
2220 debug_print('Expected Error', 9);
2221 debug_print('SQL Error: ' || Sqlerrm(SQLCODE),1);
2222 debug_print('Return Status :' || x_return_status);
2223 END IF;
2224 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2225
2226 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2227 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2228 IF (l_debug = 1) THEN
2229 debug_print('Expected Error', 9);
2230 debug_print('SQL Error: ' || Sqlerrm(SQLCODE),1);
2231 debug_print('Return Status :' || x_return_status);
2232 END IF;
2233 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2234
2235 WHEN OTHERS THEN
2236 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2237 IF (l_debug = 1) THEN
2238 debug_print('Error type others', 9);
2239 debug_print('SQL Error: ' || Sqlerrm(SQLCODE),1);
2240 debug_print('Return Status :' || x_return_status);
2241 END IF;
2242 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data =>
2243 x_msg_data);
2244 END generate_serial_numbers;
2245
2246 PROCEDURE inv_mut_insert
2247 (
2248 x_return_status OUT NOCOPY VARCHAR2
2249 , x_msg_count OUT NOCOPY NUMBER
2250 , x_msg_data OUT NOCOPY VARCHAR2
2251 , x_serial_number_tbl IN OUT NOCOPY VARCHAR30_TBL
2252 , p_parent_serial_trx_id IN NUMBER
2253 , p_serial_transaction_id IN NUMBER
2254 , p_organization_id IN NUMBER
2255 , p_inventory_item_id IN NUMBER
2256 , p_trx_source_type_id IN NUMBER
2257 , p_receipt_issue_type IN NUMBER
2258 )
2259 IS
2260 l_debug NUMBER := NVL(fnd_profile.value('INV_DEBUG_TRACE'),0);
2261 l_index NUMBER := 0;
2262 cursor mut_cur(p_transaction_id NUMBER) IS
2263 SELECT SERIAL_NUMBER,
2264 INVENTORY_ITEM_ID,
2265 ORGANIZATION_ID,
2266 SUBINVENTORY_CODE,
2267 LOCATOR_ID,
2268 TRANSACTION_SOURCE_ID,
2269 TRANSACTION_SOURCE_TYPE_ID,
2270 TRANSACTION_SOURCE_NAME,
2271 RECEIPT_ISSUE_TYPE,
2272 CUSTOMER_ID,
2273 SHIP_ID,
2274 SERIAL_ATTRIBUTE_CATEGORY,
2275 ORIGINATION_DATE,
2276 C_ATTRIBUTE1,
2277 C_ATTRIBUTE2,
2278 C_ATTRIBUTE3,
2279 C_ATTRIBUTE4,
2280 C_ATTRIBUTE5,
2281 C_ATTRIBUTE6,
2282 C_ATTRIBUTE7,
2283 C_ATTRIBUTE8,
2284 C_ATTRIBUTE9,
2285 C_ATTRIBUTE10,
2286 C_ATTRIBUTE11,
2287 C_ATTRIBUTE12,
2288 C_ATTRIBUTE13,
2289 C_ATTRIBUTE14,
2290 C_ATTRIBUTE15,
2291 C_ATTRIBUTE16,
2292 C_ATTRIBUTE17,
2293 C_ATTRIBUTE18,
2294 C_ATTRIBUTE19,
2295 C_ATTRIBUTE20,
2296 D_ATTRIBUTE1,
2297 D_ATTRIBUTE2,
2298 D_ATTRIBUTE3,
2299 D_ATTRIBUTE4,
2300 D_ATTRIBUTE5,
2301 D_ATTRIBUTE6,
2302 D_ATTRIBUTE7,
2303 D_ATTRIBUTE8,
2304 D_ATTRIBUTE9,
2305 D_ATTRIBUTE10,
2306 N_ATTRIBUTE1,
2307 N_ATTRIBUTE2,
2308 N_ATTRIBUTE3,
2309 N_ATTRIBUTE4,
2310 N_ATTRIBUTE5,
2311 N_ATTRIBUTE6,
2312 N_ATTRIBUTE7,
2313 N_ATTRIBUTE8,
2314 N_ATTRIBUTE9,
2315 N_ATTRIBUTE10,
2316 STATUS_ID,
2317 TERRITORY_CODE,
2318 TIME_SINCE_NEW,
2319 CYCLES_SINCE_NEW,
2320 TIME_SINCE_OVERHAUL,
2321 CYCLES_SINCE_OVERHAUL,
2322 TIME_SINCE_REPAIR,
2323 CYCLES_SINCE_REPAIR,
2324 TIME_SINCE_VISIT,
2325 CYCLES_SINCE_VISIT,
2326 TIME_SINCE_MARK,
2327 CYCLES_SINCE_MARK,
2328 NUMBER_OF_REPAIRS,
2329 PRODUCT_CODE,
2330 PRODUCT_TRANSACTION_ID
2331 FROM mtl_unit_transactions
2332 WHERE transaction_id = p_transaction_id;
2333 BEGIN
2334 IF (l_debug = 1) THEN
2335 debug_print('Enter inv_mut_insert', 9);
2336 debug_print('p_parent_serial_trx_id = ' || p_parent_serial_trx_id, 9);
2337 debug_print('p_serial_transaction_id = ' || p_serial_transaction_id, 9);
2338 debug_print('p_organization_id = ' || p_organization_id, 9);
2339 debug_print('p_inventory_item_id = ' || p_inventory_item_id, 9);
2340 debug_print('p_trx_source_type_id = ' || p_trx_source_type_id, 9);
2341 debug_print('p_receipt_issue_type = ' || p_receipt_issue_type, 9);
2342 END IF;
2343
2344 IF (p_parent_serial_trx_id IS NOT NULL OR p_parent_serial_trx_id > 0) THEN
2345 FOR l_mut in mut_cur(p_parent_serial_trx_id) LOOP
2346 INSERT INTO mtl_unit_transactions
2347 (
2348 TRANSACTION_ID
2349 ,LAST_UPDATE_DATE
2350 ,LAST_UPDATED_BY
2351 ,CREATION_DATE
2352 ,CREATED_BY
2353 ,LAST_UPDATE_LOGIN
2354 ,SERIAL_NUMBER
2355 ,INVENTORY_ITEM_ID
2356 ,ORGANIZATION_ID
2357 ,SUBINVENTORY_CODE
2358 ,LOCATOR_ID
2359 ,TRANSACTION_DATE
2360 ,TRANSACTION_SOURCE_ID
2361 ,TRANSACTION_SOURCE_TYPE_ID
2362 ,TRANSACTION_SOURCE_NAME
2363 ,RECEIPT_ISSUE_TYPE
2364 ,CUSTOMER_ID
2365 ,SHIP_ID
2366 ,SERIAL_ATTRIBUTE_CATEGORY
2367 ,ORIGINATION_DATE
2368 ,C_ATTRIBUTE1
2369 ,C_ATTRIBUTE2
2370 ,C_ATTRIBUTE3
2371 ,C_ATTRIBUTE4
2372 ,C_ATTRIBUTE5
2373 ,C_ATTRIBUTE6
2374 ,C_ATTRIBUTE7
2375 ,C_ATTRIBUTE8
2376 ,C_ATTRIBUTE9
2377 ,C_ATTRIBUTE10
2378 ,C_ATTRIBUTE11
2379 ,C_ATTRIBUTE12
2380 ,C_ATTRIBUTE13
2381 ,C_ATTRIBUTE14
2382 ,C_ATTRIBUTE15
2383 ,C_ATTRIBUTE16
2384 ,C_ATTRIBUTE17
2385 ,C_ATTRIBUTE18
2386 ,C_ATTRIBUTE19
2387 ,C_ATTRIBUTE20
2388 ,D_ATTRIBUTE1
2389 ,D_ATTRIBUTE2
2390 ,D_ATTRIBUTE3
2391 ,D_ATTRIBUTE4
2392 ,D_ATTRIBUTE5
2393 ,D_ATTRIBUTE6
2394 ,D_ATTRIBUTE7
2395 ,D_ATTRIBUTE8
2396 ,D_ATTRIBUTE9
2397 ,D_ATTRIBUTE10
2398 ,N_ATTRIBUTE1
2399 ,N_ATTRIBUTE2
2400 ,N_ATTRIBUTE3
2401 ,N_ATTRIBUTE4
2402 ,N_ATTRIBUTE5
2403 ,N_ATTRIBUTE6
2404 ,N_ATTRIBUTE7
2405 ,N_ATTRIBUTE8
2406 ,N_ATTRIBUTE9
2407 ,N_ATTRIBUTE10
2408 ,STATUS_ID
2409 ,TERRITORY_CODE
2410 ,TIME_SINCE_NEW
2411 ,CYCLES_SINCE_NEW
2412 ,TIME_SINCE_OVERHAUL
2413 ,CYCLES_SINCE_OVERHAUL
2414 ,TIME_SINCE_REPAIR
2415 ,CYCLES_SINCE_REPAIR
2416 ,TIME_SINCE_VISIT
2417 ,CYCLES_SINCE_VISIT
2418 ,TIME_SINCE_MARK
2419 ,CYCLES_SINCE_MARK
2420 ,NUMBER_OF_REPAIRS
2421 ,PRODUCT_CODE
2422 ,PRODUCT_TRANSACTION_ID
2423 )
2424 VALUES
2425 (
2426 p_serial_transaction_id
2427 ,SYSDATE
2428 ,FND_GLOBAL.user_id
2429 ,SYSDATE
2430 ,FND_GLOBAL.user_id
2431 ,FND_GLOBAL.login_id
2432 ,l_mut.SERIAL_NUMBER
2433 ,l_mut.INVENTORY_ITEM_ID
2434 ,l_mut.ORGANIZATION_ID
2435 ,l_mut.SUBINVENTORY_CODE
2436 ,l_mut.LOCATOR_ID
2437 ,SYSDATE
2438 ,l_mut.TRANSACTION_SOURCE_ID
2439 ,l_mut.TRANSACTION_SOURCE_TYPE_ID
2440 ,l_mut.TRANSACTION_SOURCE_NAME
2441 ,l_mut.RECEIPT_ISSUE_TYPE
2442 ,l_mut.CUSTOMER_ID
2443 ,l_mut.SHIP_ID
2444 ,l_mut.SERIAL_ATTRIBUTE_CATEGORY
2445 ,l_mut.ORIGINATION_DATE
2446 ,l_mut.C_ATTRIBUTE1
2447 ,l_mut.C_ATTRIBUTE2
2448 ,l_mut.C_ATTRIBUTE3
2449 ,l_mut.C_ATTRIBUTE4
2450 ,l_mut.C_ATTRIBUTE5
2451 ,l_mut.C_ATTRIBUTE6
2452 ,l_mut.C_ATTRIBUTE7
2453 ,l_mut.C_ATTRIBUTE8
2454 ,l_mut.C_ATTRIBUTE9
2455 ,l_mut.C_ATTRIBUTE10
2456 ,l_mut.C_ATTRIBUTE11
2457 ,l_mut.C_ATTRIBUTE12
2458 ,l_mut.C_ATTRIBUTE13
2459 ,l_mut.C_ATTRIBUTE14
2460 ,l_mut.C_ATTRIBUTE15
2461 ,l_mut.C_ATTRIBUTE16
2462 ,l_mut.C_ATTRIBUTE17
2463 ,l_mut.C_ATTRIBUTE18
2464 ,l_mut.C_ATTRIBUTE19
2465 ,l_mut.C_ATTRIBUTE20
2466 ,l_mut.D_ATTRIBUTE1
2467 ,l_mut.D_ATTRIBUTE2
2468 ,l_mut.D_ATTRIBUTE3
2469 ,l_mut.D_ATTRIBUTE4
2470 ,l_mut.D_ATTRIBUTE5
2471 ,l_mut.D_ATTRIBUTE6
2472 ,l_mut.D_ATTRIBUTE7
2473 ,l_mut.D_ATTRIBUTE8
2474 ,l_mut.D_ATTRIBUTE9
2475 ,l_mut.D_ATTRIBUTE10
2476 ,l_mut.N_ATTRIBUTE1
2477 ,l_mut.N_ATTRIBUTE2
2478 ,l_mut.N_ATTRIBUTE3
2479 ,l_mut.N_ATTRIBUTE4
2480 ,l_mut.N_ATTRIBUTE5
2481 ,l_mut.N_ATTRIBUTE6
2482 ,l_mut.N_ATTRIBUTE7
2483 ,l_mut.N_ATTRIBUTE8
2484 ,l_mut.N_ATTRIBUTE9
2485 ,l_mut.N_ATTRIBUTE10
2486 ,l_mut.STATUS_ID
2487 ,l_mut.TERRITORY_CODE
2488 ,l_mut.TIME_SINCE_NEW
2489 ,l_mut.CYCLES_SINCE_NEW
2490 ,l_mut.TIME_SINCE_OVERHAUL
2491 ,l_mut.CYCLES_SINCE_OVERHAUL
2492 ,l_mut.TIME_SINCE_REPAIR
2493 ,l_mut.CYCLES_SINCE_REPAIR
2494 ,l_mut.TIME_SINCE_VISIT
2495 ,l_mut.CYCLES_SINCE_VISIT
2496 ,l_mut.TIME_SINCE_MARK
2497 ,l_mut.CYCLES_SINCE_MARK
2498 ,l_mut.NUMBER_OF_REPAIRS
2499 ,l_mut.PRODUCT_CODE
2500 ,l_mut.PRODUCT_TRANSACTION_ID
2501 );
2502
2503 l_index := l_index + 1;
2504 x_serial_number_tbl(l_index) := l_mut.serial_number;
2505 END LOOP; -- end of loop l_mut
2506 ELSE
2507 forall i IN 1..x_serial_number_tbl.COUNT
2508 INSERT INTO mtl_unit_transactions
2509 (
2510 TRANSACTION_ID
2511 ,LAST_UPDATE_DATE
2512 ,LAST_UPDATED_BY
2513 ,CREATION_DATE
2514 ,CREATED_BY
2515 ,LAST_UPDATE_LOGIN
2516 ,SERIAL_NUMBER
2517 ,INVENTORY_ITEM_ID
2518 ,ORGANIZATION_ID
2519 ,TRANSACTION_SOURCE_TYPE_ID
2520 ,RECEIPT_ISSUE_TYPE
2521 ,TRANSACTION_DATE
2522 )
2523 VALUES
2524 (
2525 p_serial_transaction_id
2526 ,SYSDATE
2527 ,FND_GLOBAL.user_id
2528 ,SYSDATE
2529 ,FND_GLOBAL.user_id
2530 ,FND_GLOBAL.login_id
2531 ,x_serial_number_tbl(i)
2532 ,p_inventory_item_id
2533 ,p_organization_id
2534 ,p_trx_source_type_id
2535 ,p_receipt_issue_type
2536 ,SYSDATE
2537 );
2538 END IF;
2539
2540 x_return_status := fnd_api.g_ret_sts_success;
2541 IF (l_debug = 1) THEN
2542 debug_print('Before return from inv_mut_insert', 9);
2543 debug_print('Return Status :' || x_return_status, 9);
2544 END IF;
2545 EXCEPTION
2546 WHEN FND_API.G_EXC_ERROR THEN
2547 x_return_status := FND_API.G_RET_STS_ERROR;
2548 IF (l_debug = 1) THEN
2549 debug_print('Expected Error', 9);
2550 debug_print('SQL Error: ' || Sqlerrm(SQLCODE),1);
2551 debug_print('Return Status :' || x_return_status);
2552 END IF;
2553 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2554
2555 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2556 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2557 IF (l_debug = 1) THEN
2558 debug_print('Expected Error', 9);
2559 debug_print('SQL Error: ' || Sqlerrm(SQLCODE),1);
2560 debug_print('Return Status :' || x_return_status);
2561 END IF;
2562 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2563
2564 WHEN OTHERS THEN
2565 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2566 IF (l_debug = 1) THEN
2567 debug_print('Error type others', 9);
2568 debug_print('SQL Error: ' || Sqlerrm(SQLCODE),1);
2569 debug_print('Return Status :' || x_return_status);
2570 END IF;
2571 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data =>
2572 x_msg_data);
2573
2574 END inv_mut_insert;
2575
2576 PROCEDURE update_serial_numbers
2577 (
2578 x_return_status OUT NOCOPY VARCHAR2
2579 , x_msg_count OUT NOCOPY NUMBER
2580 , x_msg_data OUT NOCOPY VARCHAR2
2581 , p_ser_num_tbl IN VARCHAR30_TBL
2582 , p_organization_id IN NUMBER
2583 , p_inventory_item_id IN NUMBER
2584 )
2585 IS
2586 l_debug NUMBER := NVL(fnd_profile.value('INV_DEBUG_TRACE'),0);
2587 BEGIN
2588 IF (l_debug = 1) THEN
2589 debug_print('Enter update_serial_numbers', 9);
2590 debug_print('p_organization_id = ' || p_organization_id, 9);
2591 debug_print('p_invventory_item_id = ' || p_inventory_item_id, 9);
2592 END IF;
2593
2594 forall i in p_ser_num_tbl.FIRST..p_ser_num_tbl.LAST
2595 UPDATE mtl_serial_numbers
2596 SET current_status = 4
2597 WHERE current_organization_id = p_organization_id
2598 AND serial_number = p_ser_num_tbl(i)
2599 AND inventory_item_id = p_inventory_item_id;
2600
2601 IF (SQL%ROWCOUNT <> p_ser_num_tbl.COUNT) THEN
2602 IF (l_debug = 1) THEN
2603 debug_print('The number of rows updated in mtl_serial_numbers is not equals
2604 to the number of serial numbers that needed to be updated', 9);
2605 END IF;
2606 END IF;
2607 EXCEPTION
2608 WHEN FND_API.G_EXC_ERROR THEN
2609 x_return_status := FND_API.G_RET_STS_ERROR;
2610 IF (l_debug = 1) THEN
2611 debug_print('Expected Error', 9);
2612 debug_print('SQL Error: ' || Sqlerrm(SQLCODE),1);
2613 debug_print('Return Status :' || x_return_status);
2614 END IF;
2615 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2616
2617 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2618 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2619 IF (l_debug = 1) THEN
2620 debug_print('Expected Error', 9);
2621 debug_print('SQL Error: ' || Sqlerrm(SQLCODE),1);
2622 debug_print('Return Status :' || x_return_status);
2623 END IF;
2624 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2625
2626 WHEN OTHERS THEN
2627 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2628 IF (l_debug = 1) THEN
2629 debug_print('Error type others', 9);
2630 debug_print('SQL Error: ' || Sqlerrm(SQLCODE),1);
2631 debug_print('Return Status :' || x_return_status);
2632 END IF;
2633 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data =>
2634 x_msg_data);
2635 END update_serial_numbers;
2636
2637 END inv_logical_transactions_pvt;
2638