[Home] [Help]
PACKAGE BODY: APPS.INV_LOGICAL_TRANSACTIONS_PVT
Source
1 PACKAGE BODY inv_logical_transactions_pvt AS
2 /* $Header: INVLTPVB.pls 120.5 2010/07/27 23:28:59 gjyoti ship $ */
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
701
702 /* Commented for bug 9691823 to have same logic same as that of BaseTransaction
703 SELECT ACCT_PERIOD_ID
704 INTO l_acct_period_id
705 FROM ORG_ACCT_PERIODS
706 WHERE PERIOD_CLOSE_DATE IS NULL
707 AND ORGANIZATION_ID = p_mtl_trx_tbl(i).organization_id
708 AND TRUNC(SCHEDULE_CLOSE_DATE) >=
709 TRUNC(nvl(p_mtl_trx_tbl(i).transaction_date,SYSDATE))
710 AND TRUNC(PERIOD_START_DATE) <=
711 TRUNC(nvl(p_mtl_trx_tbl(i).transaction_date,SYSDATE)) ;
712 */
713
714 /* Added following statement for bug 9691823 */
715 SELECT acct_period_id
716 INTO l_acct_period_id
717 FROM org_acct_periods
718 WHERE period_close_date IS NULL
719 AND organization_id = p_mtl_trx_tbl(i).organization_id
720 AND (schedule_close_date + 1) >
721 inv_le_timezone_pub.get_le_day_for_inv_org(nvl(p_mtl_trx_tbl(i).transaction_date,SYSDATE),
722 p_mtl_trx_tbl(i).organization_id)
723 AND period_start_date <=
724 inv_le_timezone_pub.get_le_day_for_inv_org(nvl(p_mtl_trx_tbl(i).transaction_date,SYSDATE),
725 p_mtl_trx_tbl(i).organization_id) ;
726
727 EXCEPTION
728 WHEN NO_DATA_FOUND THEN
729 IF (l_debug = 1) THEN
730 debug_print('Invalid Account Period ID :' ||
731 l_acct_period_id, 9);
732 END IF;
733 l_acct_period_id := 0;
734 WHEN OTHERS THEN
735 IF (l_debug = 1) THEN
736 debug_print('Invalid Account Period ID :' ||
737 l_acct_period_id, 9);
738 END IF;
739 l_acct_period_id := -1;
740 END;
741
742 IF (l_acct_period_id = -1 OR l_acct_period_id = 0) THEN
743 IF (l_debug = 1) THEN
744 debug_print('Period not open', 9);
745 END IF;
746 -- FND_MESSAGE.set_name('INV', 'INV_INT_PRDCODE');
747 FND_MESSAGE.set_name('INV', 'INV_NO_OPEN_PERIOD');
748 fnd_msg_pub.ADD;
749 RAISE fnd_api.g_exc_error;
750
751 END IF;
752
753 IF (((p_mtl_trx_tbl(i).acct_period_id IS NOT NULL) AND
754 (p_mtl_trx_tbl(i).acct_period_id <> l_acct_period_id))
755 OR (p_mtl_trx_tbl(i).acct_period_id IS NULL)) THEN
756 IF (l_debug = 1) THEN
757 debug_print('Invalid Account Period ID passed : ' ||
758 p_mtl_trx_tbl(i).acct_period_id || ' is not the same as ' || l_acct_period_id, 9);
759 END IF;
760 FND_MESSAGE.set_name('INV', 'INV_NO_OPEN_PERIOD');
761 fnd_msg_pub.ADD;
762 RAISE fnd_api.g_exc_error;
763
764 END IF;
765
766 --15. Validate transaction UOM
767
768 l_count := 0;
769
770 BEGIN
771 SELECT COUNT(1) INTO l_count
772 FROM MTL_ITEM_UOMS_VIEW MIUV
773 WHERE MIUV.INVENTORY_ITEM_ID = p_mtl_trx_tbl(i).inventory_item_id
774 AND MIUV.ORGANIZATION_ID = p_mtl_trx_tbl(i).organization_id
775 AND MIUV.UOM_CODE = p_mtl_trx_tbl(i).transaction_uom;
776 EXCEPTION
777 WHEN no_data_found THEN
778 IF (l_debug = 1) THEN
779 debug_print('Invalid Transaction UOM : ' ||
780 p_mtl_trx_tbl(i).transaction_uom, 9);
781 END IF;
782 fnd_message.set_name('INV', 'INV_INT_UOMCODE');
783 fnd_msg_pub.ADD;
784 RAISE fnd_api.g_exc_error;
785 END;
786
787 IF (l_count) = 0 THEN
788 IF (l_debug = 1) THEN
789 debug_print('Transaction UOM : ' ||
790 p_mtl_trx_tbl(i).transaction_uom || ' not found', 9);
791 END IF;
792 fnd_message.set_name('INV', 'INV_INT_UOMCODE');
793 fnd_msg_pub.ADD;
794 RAISE fnd_api.g_exc_error;
795
796 END IF;
797
798 --16. Validate cost groups. Cost groups should already be populated
799 -- before validation.
800
801 IF (p_mtl_trx_tbl(i).cost_group_id IS NULL) THEN
802 IF (l_debug = 1) THEN
803 debug_print('Invalid Cost Group', 9);
804 END IF;
805 fnd_message.set_name('INV', 'INV_INT_CSTGRP');
806 fnd_msg_pub.ADD;
807 RAISE fnd_api.g_exc_error;
808
809 END IF;
810
811 l_count := 0;
812
813 BEGIN
814 SELECT COUNT(1) INTO l_count
815 FROM CST_COST_GROUPS CCG
816 WHERE CCG.COST_GROUP_ID = p_mtl_trx_tbl(i).cost_group_id
817 AND NVL(CCG.ORGANIZATION_ID, p_mtl_trx_tbl(i).organization_id) = p_mtl_trx_tbl(i).organization_id
818 AND TRUNC(NVL(CCG.DISABLE_DATE,SYSDATE+1)) >= TRUNC(SYSDATE);
819 EXCEPTION
820 WHEN no_data_found THEN
821 IF (l_debug = 1) THEN
822 debug_print('Cost Group not found', 9);
823 END IF;
824 fnd_message.set_name('INV', 'INV_INT_CSTGRP');
825 fnd_msg_pub.ADD;
826 RAISE fnd_api.g_exc_error;
827 END;
828
829 IF (l_count = 0) THEN
830 IF (l_debug = 1) THEN
831 debug_print('Cost Group not found : ' || p_mtl_trx_tbl(i).cost_group_id, 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 -- 17. Validate cost groups for transfer organziations.
840 -- Cost groups should already be populated before validation.
841
842 IF (p_mtl_trx_tbl(i).transaction_action_id IN
843 (INV_GLOBALS.G_ACTION_LOGICALICSALES, INV_GLOBALS.G_ACTION_LOGICALICRECEIPT,
844 INV_GLOBALS.G_ACTION_LOGICALICRCPTRETURN, INV_GLOBALS.G_ACTION_LOGICALICSALESRETURN,
845 INV_GLOBALS.G_ACTION_LOGICALEXPREQRECEIPT))THEN
846
847 IF (p_mtl_trx_tbl(i).transfer_cost_group_id IS NULL) THEN
848 IF (l_debug = 1) THEN
849 debug_print('Invalid Cost Group in the Transfer Org.', 9);
850 END IF;
851 fnd_message.set_name('INV', 'INV_INT_CSTGRP');
852 fnd_msg_pub.ADD;
853 RAISE fnd_api.g_exc_error;
854
855 END IF;
856
857 l_count := 0;
858
859 BEGIN
860 SELECT COUNT(1) INTO l_count
861 FROM CST_COST_GROUPS CCG
862 WHERE CCG.COST_GROUP_ID = p_mtl_trx_tbl(i).transfer_cost_group_id
863 AND NVL(CCG.ORGANIZATION_ID, p_mtl_trx_tbl(i).transfer_organization_id) = p_mtl_trx_tbl(i).transfer_organization_id
864 AND TRUNC(NVL(CCG.DISABLE_DATE,SYSDATE+1)) >= TRUNC(SYSDATE);
865 EXCEPTION
866 WHEN no_data_found THEN
867 IF (l_debug = 1) THEN
868 debug_print('Cost Group not found', 9);
869 END IF;
870 fnd_message.set_name('INV', 'INV_INT_CSTGRP');
871 fnd_msg_pub.ADD;
872 RAISE fnd_api.g_exc_error;
873 END;
874
875 IF (l_count = 0) THEN
876 IF (l_debug = 1) THEN
877 debug_print('Transfer Cost Group not found : ' || p_mtl_trx_tbl(i).transfer_cost_group_id , 9);
878 END IF;
879 fnd_message.set_name('INV', 'INV_INT_CSTGRP');
880 fnd_msg_pub.ADD;
881 RAISE fnd_api.g_exc_error;
882
883 END IF;
884
885
886 END IF;
887
888 --18. Validate transaction batch id and transaction batch sequence
889
890 IF (p_mtl_trx_tbl(i).transaction_batch_id IS NULL) OR
891 (p_mtl_trx_tbl(i).transaction_batch_seq IS NULL )THEN
892 IF (l_debug = 1) THEN
893 debug_print('Transaction batch and sequence are not populated', 9);
894 END IF;
895 fnd_message.set_name('INV', 'INV_INVALID_BATCH');
896 fnd_msg_pub.ADD;
897 RAISE fnd_api.g_exc_error;
898
899 END IF;
900
901 --19. If the locator is passed and it is project enabled, we would
902 -- have to make sure that the project and task is stamped on the line
903 --
904
905 IF (p_mtl_trx_tbl(i).locator_id IS NOT NULL) THEN
906
907 BEGIN
908 SELECT project_id, task_id INTO l_project_id, l_task_id FROM
909 mtl_item_locations WHERE inventory_location_id =
910 p_mtl_trx_tbl(i).locator_id AND organization_id =
911 p_mtl_trx_tbl(i).organization_id;
912 EXCEPTION
913 WHEN no_data_found THEN
914
915 IF (l_debug = 1) THEN
916 debug_print('Cannot find the locator information supplied', 9);
917 END IF;
918 WHEN others THEN
919 IF (l_debug = 1) THEN
920 debug_print('Invalid Locator ID', 9);
921 END IF;
922 fnd_message.set_name('INV', 'INV_INT_LOCCODE');
923 fnd_msg_pub.ADD;
924 RAISE fnd_api.g_exc_error;
925 END;
926
927
928 IF (l_project_id IS NOT NULL) THEN
929
930 IF (p_mtl_trx_tbl(i).project_id IS NULL) THEN
931 IF (l_debug = 1) THEN
932 debug_print('Invalid Project', 9);
933 END IF;
934 fnd_message.set_name('INV', 'INV_NO_PROJECT');
935 fnd_msg_pub.ADD;
936 RAISE fnd_api.g_exc_error;
937
938 END IF;
939
940 END IF;
941
942 IF (l_task_id IS NOT NULL) THEN
943
944 IF (p_mtl_trx_tbl(i).task_id IS NULL) then
945 IF (l_debug = 1) THEN
946 debug_print('Invalid Task', 9);
947 END IF;
948 fnd_message.set_name('INV', 'INV_NO_PROJECT');
949 fnd_msg_pub.ADD;
950 RAISE fnd_api.g_exc_error;
951
952 END IF;
953
954 END IF;
955
956 END IF;
957
958 -- Line level validation
959
960 -- 1. Validate the sales order that is passed for a logical sales
961 -- order issue transaction
962
963 l_count := 0;
964
965 IF ((p_mtl_trx_tbl(i).transaction_source_type_id = inv_globals.g_sourcetype_salesorder AND
966 p_mtl_trx_tbl(i).transaction_action_id = inv_globals.g_action_logicalissue) OR
967 (p_mtl_trx_tbl(i).transaction_source_type_id = inv_globals.g_sourcetype_rma AND
968 p_mtl_trx_tbl(i).transaction_action_id = inv_globals.g_action_logicalreceipt)) THEN
969
970 IF (p_mtl_trx_tbl(i).transaction_source_id IS NULL) THEN
971
972 IF (l_debug = 1) THEN
973 debug_print('Transaction Source ID is null', 9);
974 END IF;
975 fnd_message.set_name('INV', 'INV_COULD_NOT_GET_MSO_HEADER');
976 fnd_msg_pub.ADD;
977 RAISE fnd_api.g_exc_error;
978
979 END IF;
980
981
982 BEGIN
983 SELECT COUNT(1) INTO l_count FROM
984 mtl_sales_orders WHERE
985 sales_order_id = p_mtl_trx_tbl(i).transaction_source_id;
986
987 EXCEPTION
988 WHEN no_data_found THEN
989 IF (l_debug = 1) THEN
990 debug_print('Cannot find the sales order information', 9);
991 END IF;
992 fnd_message.set_name('INV', 'INV_COULD_NOT_GET_MSO_HEADER');
993 fnd_msg_pub.ADD;
994 RAISE fnd_api.g_exc_error;
995 END;
996
997 IF (l_count <> 1) THEN
998
999 IF (l_debug = 1) THEN
1000 debug_print('Invalid sales order ID :' || p_mtl_trx_tbl(i).transaction_source_id, 9);
1001 END IF;
1002 fnd_message.set_name('INV', 'INV_COULD_NOT_GET_MSO_HEADER');
1003 fnd_msg_pub.ADD;
1004 RAISE fnd_api.g_exc_error;
1005
1006 END IF;
1007
1008 END IF;
1009
1010 -- 2. Validate the purchase order that is passed for a logical
1011 -- po receipt or a logical RTV transaction
1012
1013 l_count := 0;
1014
1015 IF ((p_mtl_trx_tbl(i).transaction_source_type_id = inv_globals.g_sourcetype_purchaseorder
1016 AND p_mtl_trx_tbl(i).transaction_action_id = inv_globals.g_action_logicalreceipt) OR
1017 (p_mtl_trx_tbl(i).transaction_source_type_id = inv_globals.g_sourcetype_purchaseorder AND
1018 p_mtl_trx_tbl(i).transaction_action_id =
1019 inv_globals.g_action_logicalissue)) AND
1020 (p_logical_trx_type_code = INV_LOGICAL_TRANSACTION_GLOBAL.G_LOGTRXCODE_DSRECEIPT) THEN
1021
1022 IF ((p_mtl_trx_tbl(i).transaction_source_id IS NULL) OR (p_mtl_trx_tbl(i).rcv_transaction_id IS NULL)) THEN
1023
1024 IF (l_debug = 1) THEN
1025 debug_print('Transaction Source ID/rcv_transaction_id is null', 9);
1026 END IF;
1027 fnd_message.set_name('INV', 'INV_INT_PO');
1028 fnd_msg_pub.ADD;
1029 RAISE fnd_api.g_exc_error;
1030
1031 END IF;
1032
1033 BEGIN
1034 SELECT COUNT(1) INTO l_count FROM
1035 po_headers_all po, rcv_transactions rcv WHERE
1036 po.po_header_id = rcv.po_header_id AND
1037 po.po_header_id = p_mtl_trx_tbl(i).transaction_source_id AND
1038 rcv.transaction_id = p_mtl_trx_tbl(i).rcv_transaction_id AND
1039 NVL(po.START_DATE_ACTIVE, SYSDATE - 1) <= Sysdate AND
1040 NVL(po.END_DATE_ACTIVE, SYSDATE + 1) > SYSDATE AND ENABLED_FLAG = 'Y';
1041
1042 EXCEPTION
1043 WHEN no_data_found THEN
1044 IF (l_debug = 1) THEN
1045 debug_print('Cannot find the purchase order information', 9);
1046 END IF;
1047 fnd_message.set_name('INV', 'INV_INT_PO');
1048 fnd_msg_pub.ADD;
1049 RAISE fnd_api.g_exc_error;
1050 END;
1051
1052 IF (l_count <> 1) THEN
1053
1054 IF (l_debug = 1) THEN
1055 debug_print('Invalid puchase order header ID :' || p_mtl_trx_tbl(i).transaction_source_id, 9);
1056 END IF;
1057 fnd_message.set_name('INV', 'INV_INT_PO');
1058 fnd_msg_pub.ADD;
1059 RAISE fnd_api.g_exc_error;
1060
1061 END IF;
1062
1063 END IF;
1064
1065 -- 2. Validate the sales order line that is passed for a drop
1066 -- shipment or a global procurement flow.
1067
1068 l_count := 0;
1069
1070 IF (p_logical_trx_type_code IN
1071 (INV_LOGICAL_TRANSACTION_GLOBAL.G_LOGTRXCODE_DSDELIVER,
1072 INV_LOGICAL_TRANSACTION_GLOBAL.G_LOGTRXCODE_RMASOISSUE)) THEN
1073
1074 IF (p_mtl_trx_tbl(i).trx_source_line_id IS NULL) THEN
1075
1076 IF (l_debug = 1) THEN
1077 debug_print('Trx source line ID is null', 9);
1078 END IF;
1079 fnd_message.set_name('INV', 'INV_COULD_NOT_GET_MSO_HEADER');
1080 fnd_msg_pub.ADD;
1081 RAISE fnd_api.g_exc_error;
1082
1083 END IF;
1084
1085 BEGIN
1086 SELECT COUNT(1) INTO l_count FROM
1087 oe_order_lines_all WHERE
1088 line_id = p_mtl_trx_tbl(i).trx_source_line_id;
1089
1090 EXCEPTION
1091 WHEN no_data_found THEN
1092 IF (l_debug = 1) THEN
1093 debug_print('Cannot find the sales order line information', 9);
1094 END IF;
1095 fnd_message.set_name('INV', 'INV_COULD_NOT_GET_MSO_HEADER');
1096 fnd_msg_pub.ADD;
1097 RAISE fnd_api.g_exc_error;
1098 END;
1099
1100 IF (l_count <> 1) THEN
1101
1102 IF (l_debug = 1) THEN
1103 debug_print('Invalid sales order line :' || p_mtl_trx_tbl(i).transaction_source_id, 9);
1104 END IF;
1105 fnd_message.set_name('INV', 'INV_COULD_NOT_GET_MSO_HEADER');
1106 fnd_msg_pub.ADD;
1107 RAISE fnd_api.g_exc_error;
1108
1109 END IF;
1110
1111
1112 END IF;
1113
1114 IF (p_mtl_trx_tbl(i).transaction_action_id IN
1115 (inv_globals.g_action_logicalicsales,
1116 inv_globals.g_action_logicalicreceipt,
1117 inv_globals.g_action_logicalicrcptreturn,
1118 inv_globals.g_action_logicalicsalesreturn)) THEN
1119
1120 IF (p_mtl_trx_tbl(i).invoiced_flag <> 'N') THEN
1121
1122 IF (l_debug = 1) THEN
1123 debug_print('Invoiced flag is not set to N ', 9);
1124 END IF;
1125 fnd_message.set_name('INV', 'INV_INT_INVOICE_FLAG');
1126 fnd_msg_pub.ADD;
1127 RAISE fnd_api.g_exc_error;
1128
1129 END IF;
1130
1131 IF (p_mtl_trx_tbl(i).intercompany_cost IS NULL) OR
1132 (p_mtl_trx_tbl(i).intercompany_cost < 0) THEN
1133
1134 IF (l_debug = 1) THEN
1135 debug_print('I/C cost cannot be null ', 9);
1136 END IF;
1137 fnd_message.set_name('INV', 'INV_INT_IC_COST');
1138 fnd_msg_pub.ADD;
1139 RAISE fnd_api.g_exc_error;
1140
1141 END IF;
1142
1143 END IF;
1144
1145 IF (p_mtl_trx_tbl(i).costed_flag <> 'N') THEN
1146
1147 IF (l_debug = 1) THEN
1148 debug_print('Costed flag is not set to N ', 9);
1149 END IF;
1150 fnd_message.set_name('INV', 'INV_INT_COSTED_FLAG');
1151 fnd_msg_pub.ADD;
1152 RAISE fnd_api.g_exc_error;
1153
1154 END IF;
1155
1156 END IF; -- If p_validation level set to true.
1157
1158
1159 END LOOP; -- for loop for every record in the table of records
1160
1161 x_return_status := l_return_status;
1162
1163 EXCEPTION
1164 WHEN FND_API.G_EXC_ERROR THEN
1165 IF (l_debug = 1) THEN
1166 debug_print('Expected Error', 9);
1167 debug_print('SQL Error: ' || Sqlerrm(SQLCODE),1);
1168 END IF;
1169 x_return_status := FND_API.G_RET_STS_ERROR;
1170 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1171
1172 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1173 IF (l_debug = 1) THEN
1174 debug_print('Unexpected Error', 9);
1175 debug_print('SQL Error: ' || Sqlerrm(SQLCODE),1);
1176 END IF;
1177 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1178 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1179
1180 WHEN OTHERS THEN
1181 IF (l_debug = 1) THEN
1182 debug_print('Error Type Others', 9);
1183 debug_print('SQL Error: ' || Sqlerrm(SQLCODE),1);
1184 END IF;
1185 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1186 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1187
1188 END validate_input_parameters;
1189
1190
1191 /*==========================================================================*
1192 | Procedure : INV_MMT_INSERT |
1193 | |
1194 | Description : This API will be called by INV create logical transactions |
1195 | API to do a bulk insert into MTL_MATERIAL_TRANSACTIONS |
1196 | table. |
1197 | |
1198 | Input Parameters : |
1199 | p_api_version_number - API version number |
1200 | p_init_msg_lst - Whether initialize the error message list or not|
1201 | Should be fnd_api.g_false or fnd_api.g_true |
1202 | p_mtl_trx_rec - An array of mtl_trx_rec_type records |
1203 | |
1204 | Output Parameters : |
1205 | x_return_status - fnd_api.g_ret_sts_success, if succeeded |
1206 | fnd_api.g_ret_sts_exc_error, if an expected |
1207 | error occurred |
1208 | fnd_api.g_ret_sts_unexp_error, if an unexpected |
1209 | eror occurred |
1210 | x_msg_count - Number of error message in the error message |
1211 | list |
1212 | x_msg_data - If the number of error message in the error |
1213 | message list is one, the error message is in |
1214 | this output parameter |
1215 *==========================================================================*/
1216 PROCEDURE inv_mmt_insert
1217 (
1218 x_return_status OUT NOCOPY VARCHAR2
1219 , x_msg_count OUT NOCOPY NUMBER
1220 , x_msg_data OUT NOCOPY VARCHAR2
1221 , p_api_version_number IN NUMBER
1222 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
1223 , p_mtl_trx_tbl IN inv_logical_transaction_global.mtl_trx_tbl_type
1224 , p_logical_trx_type_code IN NUMBER
1225
1226
1227 )
1228 IS
1229 -- p_mtl_trx_tbl(i) inv_logical_transaction_global.mtl_trx_tbl_type := p_mtl_trx_tbl;
1230 l_debug NUMBER := NVL(fnd_profile.value('INV_DEBUG_TRACE'),0);
1231 l_api_version_number CONSTANT NUMBER := 1.0;
1232 l_api_name CONSTANT VARCHAR2(30) := 'Inv_Mmt_Insert';
1233 l_logical_transaction NUMBER := 1;
1234 l_quantity_adjusted NUMBER := 0;
1235 l_transaction_quantity NUMBER := 0;
1236 l_primary_quantity NUMBER := 0;
1237
1238 --
1239 -- Bug 5044147 umoogala 13-Feb-2006
1240 -- Issue: For process organizations, costed_flag is getting set to
1241 -- 'N' instead of opm_costed_flag.
1242 -- Resolution: Added code to get mtl_parameters.process_enabled_flag.
1243 -- Then in insert stmt, setting the costed_flag's based on
1244 -- variable value.
1245 --
1246 l_prev_organization_id BINARY_INTEGER := NULL;
1247 l_process_enabled_flag VARCHAR2(1) := NULL;
1248
1249 BEGIN
1250 IF (l_debug = 1) THEN
1251 debug_print('Enter inv_mmt_insert', 9);
1252 debug_print('p_api_version_number = ' || p_api_version_number, 9);
1253 debug_print('p_init_msg_lst = ' || p_init_msg_lst, 9);
1254 debug_print('p_logical_trx_type_code = ' || p_logical_trx_type_code, 9);
1255 END IF;
1256
1257 --
1258 -- Standard call to check for call compatibility
1259 IF NOT fnd_api.compatible_api_call(l_api_version_number, p_api_version_number, l_api_name, g_pkg_name) THEN
1260 RAISE fnd_api.g_exc_unexpected_error;
1261 END IF;
1262
1263 --
1264 -- Initialize message list.
1265 IF fnd_api.to_boolean(p_init_msg_lst) THEN
1266 fnd_msg_pub.initialize;
1267 END IF;
1268
1269 IF (p_logical_trx_type_code = INV_LOGICAL_TRANSACTIONS_PUB.G_LOGTRXCODE_RETROPRICEUPD) THEN
1270 l_logical_transaction := 2;
1271 ELSE
1272 l_logical_transaction := 1;
1273 END IF;
1274
1275
1276 IF (l_debug = 1) THEN
1277 debug_print('Inside inv insert API', 9);
1278 END IF;
1279
1280 FOR i in 1..p_mtl_trx_tbl.COUNT LOOP
1281
1282 IF (p_logical_trx_type_code = INV_LOGICAL_TRANSACTIONS_PUB.G_LOGTRXCODE_RETROPRICEUPD) THEN
1283 l_quantity_adjusted := p_mtl_trx_tbl(i).transaction_quantity;
1284 l_transaction_quantity := 0;
1285 l_primary_quantity := 0;
1286 ELSE
1287 l_transaction_quantity := p_mtl_trx_tbl(i).transaction_quantity;
1288 l_primary_quantity := p_mtl_trx_tbl(i).primary_quantity;
1289 l_quantity_adjusted := NULL;
1290 END IF;
1291
1292 --
1293 -- Bug 5044147 umoogala 13-Feb-2006
1294 -- Issue: For process organizations, costed_flag is getting set to
1295 -- 'N' instead of opm_costed_flag.
1296 -- Resolution: Added code to get mtl_parameters.process_enabled_flag.
1297 -- Then in insert stmt, setting the costed_flag's based on
1298 -- variable value.
1299 --
1300 IF l_prev_organization_id IS NULL OR
1301 p_mtl_trx_tbl(i).ORGANIZATION_ID <> l_prev_organization_id
1302 THEN
1303 l_prev_organization_id := p_mtl_trx_tbl(i).ORGANIZATION_ID;
1304
1305 SELECT NVL(process_enabled_flag, 'N')
1306 INTO l_process_enabled_flag
1307 FROM mtl_parameters
1308 WHERE organization_id = p_mtl_trx_tbl(i).ORGANIZATION_ID;
1309 END IF;
1310
1311
1312 INSERT
1313 INTO MTL_MATERIAL_TRANSACTIONS
1314 ( TRANSACTION_ID
1315 ,ORGANIZATION_ID
1316 ,INVENTORY_ITEM_ID
1317 ,REVISION
1318 ,SUBINVENTORY_CODE
1319 ,LOCATOR_ID
1320 ,TRANSACTION_TYPE_ID
1321 ,TRANSACTION_ACTION_ID
1322 ,TRANSACTION_SOURCE_TYPE_ID
1323 ,TRANSACTION_SOURCE_ID
1324 ,TRANSACTION_SOURCE_NAME
1325 ,TRANSACTION_QUANTITY
1326 ,TRANSACTION_UOM
1327 ,PRIMARY_QUANTITY
1328 ,TRANSACTION_DATE
1329 ,ACCT_PERIOD_ID
1330 ,DISTRIBUTION_ACCOUNT_ID
1331 ,COSTED_FLAG
1332 ,ACTUAL_COST
1333 ,INVOICED_FLAG
1334 ,TRANSACTION_COST
1335 ,CURRENCY_CODE
1336 ,CURRENCY_CONVERSION_RATE
1337 ,CURRENCY_CONVERSION_TYPE
1338 ,CURRENCY_CONVERSION_DATE
1339 ,PM_COST_COLLECTED
1340 ,TRX_SOURCE_LINE_ID
1341 ,SOURCE_CODE
1342 ,RCV_TRANSACTION_ID
1343 ,SOURCE_LINE_ID
1344 ,TRANSFER_ORGANIZATION_ID
1345 ,TRANSFER_SUBINVENTORY
1346 ,TRANSFER_LOCATOR_ID
1347 ,COST_GROUP_ID
1348 ,TRANSFER_COST_GROUP_ID
1349 ,PROJECT_ID
1350 ,TASK_ID
1351 ,TO_PROJECT_ID
1352 ,TO_TASK_ID
1353 ,SHIP_TO_LOCATION_ID
1354 ,TRANSACTION_MODE
1355 ,TRANSACTION_BATCH_ID
1356 ,TRANSACTION_BATCH_SEQ
1357 ,TRX_FLOW_HEADER_ID
1358 ,INTERCOMPANY_COST
1359 ,INTERCOMPANY_CURRENCY_CODE
1360 ,INTERCOMPANY_PRICING_OPTION
1361 ,parent_transaction_id
1362 ,lpn_id
1363 ,logical_trx_type_code
1364 ,logical_transaction
1365 ,last_update_date
1366 ,last_updated_by
1367 ,creation_date
1368 ,created_by
1369 ,last_update_login
1370 ,quantity_adjusted
1371 ,so_issue_account_type
1372 ,opm_costed_flag
1373 )
1374 VALUES
1375 ( p_mtl_trx_tbl(i).TRANSACTION_ID
1376 ,p_mtl_trx_tbl(i).ORGANIZATION_ID
1377 ,p_mtl_trx_tbl(i).INVENTORY_ITEM_ID
1378 ,p_mtl_trx_tbl(i).REVISION
1379 ,p_mtl_trx_tbl(i).SUBINVENTORY_CODE
1380 ,p_mtl_trx_tbl(i).LOCATOR_ID
1381 ,p_mtl_trx_tbl(i).TRANSACTION_TYPE_ID
1382 ,p_mtl_trx_tbl(i).TRANSACTION_ACTION_ID
1383 ,p_mtl_trx_tbl(i).TRANSACTION_SOURCE_TYPE_ID
1384 ,p_mtl_trx_tbl(i).TRANSACTION_SOURCE_ID
1385 ,p_mtl_trx_tbl(i).TRANSACTION_SOURCE_NAME
1386 ,l_transaction_quantity
1387 ,p_mtl_trx_tbl(i).TRANSACTION_UOM
1388 ,l_primary_quantity
1389 ,p_mtl_trx_tbl(i).TRANSACTION_DATE
1390 ,p_mtl_trx_tbl(i).ACCT_PERIOD_ID
1391 ,p_mtl_trx_tbl(i).DISTRIBUTION_ACCOUNT_ID
1392 ,decode(l_process_enabled_flag, 'N', p_mtl_trx_tbl(i).COSTED_FLAG, NULL) -- Bug 5044147
1393 ,p_mtl_trx_tbl(i).ACTUAL_COST
1394 ,p_mtl_trx_tbl(i).INVOICED_FLAG
1395 ,p_mtl_trx_tbl(i).TRANSACTION_COST
1396 ,p_mtl_trx_tbl(i).CURRENCY_CODE
1397 ,p_mtl_trx_tbl(i).CURRENCY_CONVERSION_RATE
1398 ,p_mtl_trx_tbl(i).CURRENCY_CONVERSION_TYPE
1399 ,p_mtl_trx_tbl(i).CURRENCY_CONVERSION_DATE
1400 ,p_mtl_trx_tbl(i).PM_COST_COLLECTED
1401 ,p_mtl_trx_tbl(i).TRX_SOURCE_LINE_ID
1402 ,p_mtl_trx_tbl(i).SOURCE_CODE
1403 ,p_mtl_trx_tbl(i).RCV_TRANSACTION_ID
1404 ,p_mtl_trx_tbl(i).SOURCE_LINE_ID
1405 ,p_mtl_trx_tbl(i).TRANSFER_ORGANIZATION_ID
1406 ,p_mtl_trx_tbl(i).TRANSFER_SUBINVENTORY
1407 ,p_mtl_trx_tbl(i).TRANSFER_LOCATOR_ID
1408 ,p_mtl_trx_tbl(i).COST_GROUP_ID
1409 ,p_mtl_trx_tbl(i).TRANSFER_COST_GROUP_ID
1410 ,p_mtl_trx_tbl(i).PROJECT_ID
1411 ,p_mtl_trx_tbl(i).TASK_ID
1412 ,p_mtl_trx_tbl(i).TO_PROJECT_ID
1413 ,p_mtl_trx_tbl(i).TO_TASK_ID
1414 ,p_mtl_trx_tbl(i).SHIP_TO_LOCATION_ID
1415 ,p_mtl_trx_tbl(i).TRANSACTION_MODE
1416 ,p_mtl_trx_tbl(i).TRANSACTION_BATCH_ID
1417 ,p_mtl_trx_tbl(i).TRANSACTION_BATCH_SEQ
1418 ,p_mtl_trx_tbl(i).TRX_FLOW_HEADER_ID
1419 ,p_mtl_trx_tbl(i).INTERCOMPANY_COST
1420 ,p_mtl_trx_tbl(i).INTERCOMPANY_CURRENCY_CODE
1421 ,p_mtl_trx_tbl(i).INTERCOMPANY_PRICING_OPTION
1422 ,p_mtl_trx_tbl(i).parent_transaction_id
1423 ,p_mtl_trx_tbl(i).lpn_id
1424 ,p_logical_trx_type_code
1425 ,l_logical_transaction
1426 ,Sysdate
1427 ,FND_GLOBAL.user_id
1428 ,Sysdate
1429 ,FND_GLOBAL.user_id
1430 ,FND_GLOBAL.login_id
1431 ,l_quantity_adjusted
1432 ,2--deffered cogs
1433 ,decode(l_process_enabled_flag, 'Y', 'N', NULL) -- Bug 5044147
1434 );
1435
1436 END LOOP;
1437
1438 x_return_status := fnd_api.g_ret_sts_success;
1439 IF (l_debug = 1) THEN
1440 debug_print('After inv insert', 9);
1441 debug_print('Return Status :' || x_return_status, 9);
1442 END IF;
1443
1444
1445 EXCEPTION
1446 WHEN FND_API.G_EXC_ERROR THEN
1447 x_return_status := FND_API.G_RET_STS_ERROR;
1448 IF (l_debug = 1) THEN
1449 debug_print('Expected Error', 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 => x_msg_data);
1454
1455 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1456 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1457 IF (l_debug = 1) THEN
1458 debug_print('Expected Error', 9);
1459 debug_print('SQL Error: ' || Sqlerrm(SQLCODE),1);
1460 debug_print('Return Status :' || x_return_status);
1461 END IF;
1462 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1463
1464 WHEN OTHERS THEN
1465 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1466 IF (l_debug = 1) THEN
1467 debug_print('Error type others', 9);
1468 debug_print('SQL Error: ' || Sqlerrm(SQLCODE),1);
1469 debug_print('Return Status :' || x_return_status);
1470 END IF;
1471 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data =>
1472 x_msg_data);
1473
1474 END inv_mmt_insert;
1475
1476 /*==========================================================================*
1477 | Procedure : INV_LOT_SERIAL_INSERT |
1478 | |
1479 | Description : This API will be called by INV create_logical_transactions |
1480 | API to do a bulk insert into mtl_transaction_lot_numbers if|
1481 | the item is lot control and insert into |
1482 | mtl_unit_transactions if the item is serial control. |
1483 | |
1484 | Input Parameters : |
1485 | p_api_version_number - API version number |
1486 | p_init_msg_lst - Whether initialize the error message list or not|
1487 | Should be fnd_api.g_false or fnd_api.g_true |
1488 | p_parent_transaction_id - the transaction id of the parent transaction|
1489 | in mmt. |
1490 | p_transaction_id - the transaction id of the new logical |
1491 | transaction in mmt. |
1492 | p_lot_control_code - the lot control code of the item |
1493 | p_serial_control_code - the serial control code of the item |
1494 | |
1495 | Output Parameters : |
1496 | x_return_status - fnd_api.g_ret_sts_success, if succeeded |
1497 | fnd_api.g_ret_sts_exc_error, if an expected |
1498 | error occurred |
1499 | fnd_api.g_ret_sts_unexp_error, if an unexpected |
1500 | eror occurred |
1501 | x_msg_count - Number of error message in the error message |
1502 | list |
1503 | x_msg_data - If the number of error message in the error |
1504 | message list is one, the error message is in |
1505 | this output parameter |
1506 *==========================================================================*/
1507
1508 PROCEDURE inv_lot_serial_insert
1509 (
1510 x_return_status OUT NOCOPY VARCHAR2
1511 , x_msg_count OUT NOCOPY NUMBER
1512 , x_msg_data OUT NOCOPY VARCHAR2
1513 , p_api_version_number IN NUMBER := 1.0
1514 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
1515 , p_parent_transaction_id IN NUMBER
1516 , p_transaction_id IN NUMBER
1517 , p_lot_control_code IN NUMBER
1518 , p_serial_control_code IN NUMBER
1519 , p_organization_id IN NUMBER
1520 , p_inventory_item_id IN NUMBER
1521 , p_primary_quantity IN NUMBER
1522 , p_trx_source_type_id IN NUMBER
1523 , p_revision IN VARCHAR2
1524 )
1525 IS
1526 l_debug NUMBER := NVL(fnd_profile.value('INV_DEBUG_TRACE'),0);
1527 l_api_version_number CONSTANT NUMBER := 1.0;
1528 l_api_name CONSTANT VARCHAR2(30) := 'Inv_Mmt_Insert';
1529 l_serial_number_tbl VARCHAR30_TBL;
1530 l_return_status VARCHAR2(1);
1531 l_msg_count NUMBER;
1532 l_msg_data VARCHAR2(2000);
1533
1534 /* Bug 8530979: New variables to store the values from MMT */
1535 l_mmt_trx_qty NUMBER;
1536 l_mmt_pri_qty NUMBER;
1537 l_mmt_src_id NUMBER;
1538 l_mmt_src_type_id NUMBER;
1539 l_mmt_src_name VARCHAR2(240);
1540
1541 l_serial_transaction_id NUMBER;
1542
1543 cursor mtln_cur(p_transaction_id NUMBER) IS
1544 SELECT INVENTORY_ITEM_ID,
1545 ORGANIZATION_ID,
1546 TRANSACTION_SOURCE_ID,
1547 TRANSACTION_SOURCE_TYPE_ID,
1548 TRANSACTION_SOURCE_NAME,
1549 TRANSACTION_QUANTITY,
1550 PRIMARY_QUANTITY,
1551 LOT_NUMBER,
1552 SERIAL_TRANSACTION_ID,
1553 DESCRIPTION,
1554 VENDOR_NAME,
1555 SUPPLIER_LOT_NUMBER,
1556 ORIGINATION_DATE,
1557 DATE_CODE,
1558 GRADE_CODE,
1559 CHANGE_DATE,
1560 MATURITY_DATE,
1561 STATUS_ID,
1562 RETEST_DATE,
1563 AGE,
1564 ITEM_SIZE,
1565 COLOR,
1566 VOLUME,
1567 VOLUME_UOM,
1568 PLACE_OF_ORIGIN,
1569 BEST_BY_DATE,
1570 LENGTH,
1571 LENGTH_UOM,
1572 WIDTH,
1573 WIDTH_UOM,
1574 RECYCLED_CONTENT,
1575 THICKNESS,
1576 THICKNESS_UOM,
1577 CURL_WRINKLE_FOLD,
1578 LOT_ATTRIBUTE_CATEGORY,
1579 C_ATTRIBUTE1,
1580 C_ATTRIBUTE2,
1581 C_ATTRIBUTE3,
1582 C_ATTRIBUTE4,
1583 C_ATTRIBUTE5,
1584 C_ATTRIBUTE6,
1585 C_ATTRIBUTE7,
1586 C_ATTRIBUTE8,
1587 C_ATTRIBUTE9,
1588 C_ATTRIBUTE10,
1589 C_ATTRIBUTE11,
1590 C_ATTRIBUTE12,
1591 C_ATTRIBUTE13,
1592 C_ATTRIBUTE14,
1593 C_ATTRIBUTE15,
1594 C_ATTRIBUTE16,
1595 C_ATTRIBUTE17,
1596 C_ATTRIBUTE18,
1597 C_ATTRIBUTE19,
1598 C_ATTRIBUTE20,
1599 D_ATTRIBUTE1,
1600 D_ATTRIBUTE2,
1601 D_ATTRIBUTE3,
1602 D_ATTRIBUTE4,
1603 D_ATTRIBUTE5,
1604 D_ATTRIBUTE6,
1605 D_ATTRIBUTE7,
1606 D_ATTRIBUTE8,
1607 D_ATTRIBUTE9,
1608 D_ATTRIBUTE10,
1609 N_ATTRIBUTE1,
1610 N_ATTRIBUTE2,
1611 N_ATTRIBUTE3,
1612 N_ATTRIBUTE4,
1613 N_ATTRIBUTE5,
1614 N_ATTRIBUTE6,
1615 N_ATTRIBUTE7,
1616 N_ATTRIBUTE8,
1617 N_ATTRIBUTE9,
1618 N_ATTRIBUTE10,
1619 VENDOR_ID,
1620 TERRITORY_CODE,
1621 PRODUCT_CODE,
1622 PRODUCT_TRANSACTION_ID,
1623 ATTRIBUTE_CATEGORY,
1624 ATTRIBUTE1,
1625 ATTRIBUTE2,
1626 ATTRIBUTE3,
1627 ATTRIBUTE4,
1628 ATTRIBUTE5,
1629 ATTRIBUTE6,
1630 ATTRIBUTE7,
1631 ATTRIBUTE8,
1632 ATTRIBUTE9,
1633 ATTRIBUTE10,
1634 ATTRIBUTE11,
1635 ATTRIBUTE12,
1636 ATTRIBUTE13,
1637 ATTRIBUTE14,
1638 ATTRIBUTE15
1639 FROM mtl_transaction_lot_numbers
1640 WHERE transaction_id = p_transaction_id;
1641
1642 BEGIN
1643
1644 IF (l_debug = 1) THEN
1645 debug_print('Enter inv_mmt_insert', 9);
1646 debug_print('p_api_version_number = ' || p_api_version_number, 9);
1647 debug_print('p_init_msg_lst = ' || p_init_msg_lst, 9);
1648 debug_print('p_parent_transaction_id = ' || p_parent_transaction_id, 9);
1649 debug_print('p_transaction_id = ' || p_transaction_id, 9);
1650 debug_print('p_lot_control_code = ' || p_lot_control_code, 9);
1651 debug_print('p_serial_control_code = ' || p_serial_control_code, 9);
1652 END IF;
1653
1654 IF (p_lot_control_code = 2) THEN
1655 IF (p_serial_control_code in (2, 5, 6)) THEN
1656 SELECT mtl_material_transactions_s.nextval
1657 INTO l_serial_transaction_id
1658 FROM dual;
1659 ELSE
1660 l_serial_transaction_id := null;
1661 END IF;
1662
1663 IF (l_debug = 1) THEN
1664 debug_print('l_serial_transaction_id = ' || l_serial_transaction_id, 9);
1665 END IF;
1666
1667 For l_mtln IN mtln_cur(p_parent_transaction_id) LOOP
1668 IF (l_debug = 1) THEN
1669 debug_print('In the mtln_cur loop: lot_number = ' || l_mtln.lot_number, 9);
1670 END IF;
1671
1672 /* Bug 8530979: Getting the quantities and source_id and
1673 * source_type_id from the corresponding MMT */
1674 begin
1675 select transaction_quantity,
1676 primary_quantity,
1677 transaction_source_id,
1678 transaction_source_type_id,
1679 transaction_source_name
1680 into l_mmt_trx_qty,
1681 l_mmt_pri_qty,
1682 l_mmt_src_id,
1683 l_mmt_src_type_id,
1684 l_mmt_src_name
1685 from mtl_material_transactions
1686 where transaction_id = p_transaction_id;
1687 exception
1688 when others then
1689 l_mmt_src_id := l_mtln.TRANSACTION_SOURCE_ID;
1690 l_mmt_src_type_id := l_mtln.TRANSACTION_SOURCE_TYPE_ID;
1691 l_mmt_src_name := l_mtln.TRANSACTION_SOURCE_NAME;
1692 l_mmt_trx_qty := l_mtln.TRANSACTION_QUANTITY;
1693 l_mmt_pri_qty := l_mtln.PRIMARY_QUANTITY;
1694 end;
1695
1696 -- insert into mtln same as the one of parent transaction id
1697 -- with the logical intercompany issue type
1698 INSERT INTO mtl_transaction_lot_numbers
1699 ( TRANSACTION_ID
1700 ,LAST_UPDATE_DATE
1701 ,LAST_UPDATED_BY
1702 ,CREATION_DATE
1703 ,CREATED_BY
1704 ,LAST_UPDATE_LOGIN
1705 ,INVENTORY_ITEM_ID
1706 ,ORGANIZATION_ID
1707 ,TRANSACTION_DATE
1708 ,TRANSACTION_SOURCE_ID
1709 ,TRANSACTION_SOURCE_TYPE_ID
1710 ,TRANSACTION_SOURCE_NAME
1711 ,TRANSACTION_QUANTITY
1712 ,PRIMARY_QUANTITY
1713 ,LOT_NUMBER
1714 ,SERIAL_TRANSACTION_ID
1715 ,DESCRIPTION
1716 ,VENDOR_NAME
1717 ,SUPPLIER_LOT_NUMBER
1718 ,ORIGINATION_DATE
1719 ,DATE_CODE
1720 ,GRADE_CODE
1721 ,CHANGE_DATE
1722 ,MATURITY_DATE
1723 ,STATUS_ID
1724 ,RETEST_DATE
1725 ,AGE
1726 ,ITEM_SIZE
1727 ,COLOR
1728 ,VOLUME
1729 ,VOLUME_UOM
1730 ,PLACE_OF_ORIGIN
1731 ,BEST_BY_DATE
1732 ,LENGTH
1733 ,LENGTH_UOM
1734 ,WIDTH
1735 ,WIDTH_UOM
1736 ,RECYCLED_CONTENT
1737 ,THICKNESS
1738 ,THICKNESS_UOM
1739 ,CURL_WRINKLE_FOLD
1740 ,LOT_ATTRIBUTE_CATEGORY
1741 ,C_ATTRIBUTE1
1742 ,C_ATTRIBUTE2
1743 ,C_ATTRIBUTE3
1744 ,C_ATTRIBUTE4
1745 ,C_ATTRIBUTE5
1746 ,C_ATTRIBUTE6
1747 ,C_ATTRIBUTE7
1748 ,C_ATTRIBUTE8
1749 ,C_ATTRIBUTE9
1750 ,C_ATTRIBUTE10
1751 ,C_ATTRIBUTE11
1752 ,C_ATTRIBUTE12
1753 ,C_ATTRIBUTE13
1754 ,C_ATTRIBUTE14
1755 ,C_ATTRIBUTE15
1756 ,C_ATTRIBUTE16
1757 ,C_ATTRIBUTE17
1758 ,C_ATTRIBUTE18
1759 ,C_ATTRIBUTE19
1760 ,C_ATTRIBUTE20
1761 ,D_ATTRIBUTE1
1762 ,D_ATTRIBUTE2
1763 ,D_ATTRIBUTE3
1764 ,D_ATTRIBUTE4
1765 ,D_ATTRIBUTE5
1766 ,D_ATTRIBUTE6
1767 ,D_ATTRIBUTE7
1768 ,D_ATTRIBUTE8
1769 ,D_ATTRIBUTE9
1770 ,D_ATTRIBUTE10
1771 ,N_ATTRIBUTE1
1772 ,N_ATTRIBUTE2
1773 ,N_ATTRIBUTE3
1774 ,N_ATTRIBUTE4
1775 ,N_ATTRIBUTE5
1776 ,N_ATTRIBUTE6
1777 ,N_ATTRIBUTE7
1778 ,N_ATTRIBUTE8
1779 ,N_ATTRIBUTE9
1780 ,N_ATTRIBUTE10
1781 ,VENDOR_ID
1782 ,TERRITORY_CODE
1783 ,PRODUCT_CODE
1784 ,PRODUCT_TRANSACTION_ID
1785 ,ATTRIBUTE_CATEGORY
1786 ,ATTRIBUTE1
1787 ,ATTRIBUTE2
1788 ,ATTRIBUTE3
1789 ,ATTRIBUTE4
1790 ,ATTRIBUTE5
1791 ,ATTRIBUTE6
1792 ,ATTRIBUTE7
1793 ,ATTRIBUTE8
1794 ,ATTRIBUTE9
1795 ,ATTRIBUTE10
1796 ,ATTRIBUTE11
1797 ,ATTRIBUTE12
1798 ,ATTRIBUTE13
1799 ,ATTRIBUTE14
1800 ,ATTRIBUTE15
1801 )
1802 VALUES(
1803 p_transaction_id
1804 ,SYSDATE
1805 ,FND_GLOBAL.user_id
1806 ,SYSDATE
1807 ,FND_GLOBAL.user_id
1808 ,FND_GLOBAL.login_id
1809 ,l_mtln.INVENTORY_ITEM_ID
1810 ,l_mtln.ORGANIZATION_ID
1811 ,SYSDATE
1812 /* Bug 8530979 */
1813 ,l_mmt_src_id
1814 ,l_mmt_src_type_id
1815 ,l_mmt_src_name
1816 ,sign(l_mmt_trx_qty)*abs(l_mtln.TRANSACTION_QUANTITY)
1817 ,sign(l_mmt_pri_qty)*abs(l_mtln.PRIMARY_QUANTITY)
1818 /* End Bug 8530979 */
1819 ,l_mtln.LOT_NUMBER
1820 ,l_serial_transaction_id
1821 ,l_mtln.DESCRIPTION
1822 ,l_mtln.VENDOR_NAME
1823 ,l_mtln.SUPPLIER_LOT_NUMBER
1824 ,l_mtln.ORIGINATION_DATE
1825 ,l_mtln.DATE_CODE
1826 ,l_mtln.GRADE_CODE
1827 ,l_mtln.CHANGE_DATE
1828 ,l_mtln.MATURITY_DATE
1829 ,l_mtln.STATUS_ID
1830 ,l_mtln.RETEST_DATE
1831 ,l_mtln.AGE
1832 ,l_mtln.ITEM_SIZE
1833 ,l_mtln.COLOR
1834 ,l_mtln.VOLUME
1835 ,l_mtln.VOLUME_UOM
1836 ,l_mtln.PLACE_OF_ORIGIN
1837 ,l_mtln.BEST_BY_DATE
1838 ,l_mtln.LENGTH
1839 ,l_mtln.LENGTH_UOM
1840 ,l_mtln.WIDTH
1841 ,l_mtln.WIDTH_UOM
1842 ,l_mtln.RECYCLED_CONTENT
1843 ,l_mtln.THICKNESS
1844 ,l_mtln.THICKNESS_UOM
1845 ,l_mtln.CURL_WRINKLE_FOLD
1846 ,l_mtln.LOT_ATTRIBUTE_CATEGORY
1847 ,l_mtln.C_ATTRIBUTE1
1848 ,l_mtln.C_ATTRIBUTE2
1849 ,l_mtln.C_ATTRIBUTE3
1850 ,l_mtln.C_ATTRIBUTE4
1851 ,l_mtln.C_ATTRIBUTE5
1852 ,l_mtln.C_ATTRIBUTE6
1853 ,l_mtln.C_ATTRIBUTE7
1854 ,l_mtln.C_ATTRIBUTE8
1855 ,l_mtln.C_ATTRIBUTE9
1856 ,l_mtln.C_ATTRIBUTE10
1857 ,l_mtln.C_ATTRIBUTE11
1858 ,l_mtln.C_ATTRIBUTE12
1859 ,l_mtln.C_ATTRIBUTE13
1860 ,l_mtln.C_ATTRIBUTE14
1861 ,l_mtln.C_ATTRIBUTE15
1862 ,l_mtln.C_ATTRIBUTE16
1863 ,l_mtln.C_ATTRIBUTE17
1864 ,l_mtln.C_ATTRIBUTE18
1865 ,l_mtln.C_ATTRIBUTE19
1866 ,l_mtln.C_ATTRIBUTE20
1867 ,l_mtln.D_ATTRIBUTE1
1868 ,l_mtln.D_ATTRIBUTE2
1869 ,l_mtln.D_ATTRIBUTE3
1870 ,l_mtln.D_ATTRIBUTE4
1871 ,l_mtln.D_ATTRIBUTE5
1872 ,l_mtln.D_ATTRIBUTE6
1873 ,l_mtln.D_ATTRIBUTE7
1874 ,l_mtln.D_ATTRIBUTE8
1875 ,l_mtln.D_ATTRIBUTE9
1876 ,l_mtln.D_ATTRIBUTE10
1877 ,l_mtln.N_ATTRIBUTE1
1878 ,l_mtln.N_ATTRIBUTE2
1879 ,l_mtln.N_ATTRIBUTE3
1880 ,l_mtln.N_ATTRIBUTE4
1881 ,l_mtln.N_ATTRIBUTE5
1882 ,l_mtln.N_ATTRIBUTE6
1883 ,l_mtln.N_ATTRIBUTE7
1884 ,l_mtln.N_ATTRIBUTE8
1885 ,l_mtln.N_ATTRIBUTE9
1886 ,l_mtln.N_ATTRIBUTE10
1887 ,l_mtln.VENDOR_ID
1888 ,l_mtln.TERRITORY_CODE
1889 ,l_mtln.PRODUCT_CODE
1890 ,l_mtln.PRODUCT_TRANSACTION_ID
1891 ,l_mtln.ATTRIBUTE_CATEGORY
1892 ,l_mtln.ATTRIBUTE1
1893 ,l_mtln.ATTRIBUTE2
1894 ,l_mtln.ATTRIBUTE3
1895 ,l_mtln.ATTRIBUTE4
1896 ,l_mtln.ATTRIBUTE5
1897 ,l_mtln.ATTRIBUTE6
1898 ,l_mtln.ATTRIBUTE7
1899 ,l_mtln.ATTRIBUTE8
1900 ,l_mtln.ATTRIBUTE9
1901 ,l_mtln.ATTRIBUTE10
1902 ,l_mtln.ATTRIBUTE11
1903 ,l_mtln.ATTRIBUTE12
1904 ,l_mtln.ATTRIBUTE13
1905 ,l_mtln.ATTRIBUTE14
1906 ,l_mtln.ATTRIBUTE15
1907 );
1908
1909 -- If it's serial control and the serial_number_control_code is 2, 5
1910 -- then also insert into the mtl_unit_transactions
1911 -- serial_number_control_code = 2 -- Predefined serial numbers
1912 -- serial_number_control_code = 5 -- Dynamic entry at inventory receipt
1913 IF (p_serial_control_code in (2, 5)) THEN
1914 IF (l_debug = 1) THEN
1915 debug_print('Before calling inv_mut_insert', 9);
1916 debug_print('serial_transaction_id is ' || l_mtln.serial_transaction_id, 9);
1917 END IF;
1918
1919 inv_mut_insert
1920 ( x_return_status => x_return_status,
1921 x_msg_count => x_msg_count,
1922 x_msg_data => x_msg_data,
1923 x_serial_number_tbl => l_serial_number_tbl,
1924 p_parent_serial_trx_id => l_mtln.serial_transaction_id,
1925 p_serial_transaction_id => l_serial_transaction_id,
1926 p_organization_id => null,
1927 p_inventory_item_id => null,
1928 p_trx_source_type_id => null,
1929 p_receipt_issue_type => null);
1930
1931 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1932 IF (l_debug = 1) THEN
1933 debug_print('generate_serial_number returns error: ' || x_msg_data, 9);
1934 END IF;
1935 RAISE FND_API.G_EXC_ERROR;
1936 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1937 IF (l_debug = 1) THEN
1938 debug_print('inv_mut_insert returns unexpected error: ' || x_msg_data, 9);
1939 END IF;
1940 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1941 END IF;
1942
1943 ELSIF (p_serial_control_code = 6) THEN
1944 IF (l_debug = 1) THEN
1945 debug_print('serial_transaction_id is ' || l_mtln.serial_transaction_id, 9);
1946 END IF;
1947
1948 -- generate serial number with the primary lot qty
1949 generate_serial_numbers
1950 (x_return_status => x_return_status,
1951 x_msg_count => x_msg_count,
1952 x_msg_data => x_msg_data,
1953 x_ser_num_tbl => l_serial_number_tbl,
1954 p_org_id => p_organization_id,
1955 p_item_id => p_inventory_item_id,
1956 p_lot_number => l_mtln.lot_number,
1957 p_qty => p_primary_quantity,
1958 p_revision => p_revision);
1959
1960 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1961 IF (l_debug = 1) THEN
1962 debug_print('generate_serial_number returns error: ' || x_msg_data, 9);
1963 END IF;
1964 RAISE FND_API.G_EXC_ERROR;
1965 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1966 IF (l_debug = 1) THEN
1967 debug_print('generate_serial_number returns unexpected error: ' || x_msg_data, 9);
1968 END IF;
1969 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1970 END IF;
1971
1972
1973 inv_mut_insert
1974 (x_return_status => x_return_status,
1975 x_msg_count => x_msg_count,
1976 x_msg_data => x_msg_data,
1977 x_serial_number_tbl => l_serial_number_tbl,
1978 p_parent_serial_trx_id => null,
1979 p_serial_transaction_id => l_serial_transaction_id,
1980 p_organization_id => l_mtln.organization_id,
1981 p_inventory_item_id => l_mtln.inventory_item_id,
1982 p_trx_source_type_id => l_mtln.transaction_source_type_id,
1983 p_receipt_issue_type => 1);
1984
1985 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1986 IF (l_debug = 1) THEN
1987 debug_print('inv_mut_insert returns error: ' || x_msg_data, 9);
1988 END IF;
1989 RAISE FND_API.G_EXC_ERROR;
1990 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1991 IF (l_debug = 1) THEN
1992 debug_print('inv_mut_insert returns unexpected error: ' || x_msg_data, 9);
1993 END IF;
1994 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1995 END IF;
1996
1997 END IF; -- end of if serial_control_code is in (2,5)
1998 END LOOP; -- end of loop l_mtln
1999 ELSE -- not lot controll
2000 IF (l_debug = 1) THEN
2001 debug_print('It is not lot controlled', 9);
2002 END IF;
2003
2004 IF (p_serial_control_code in (2,5)) THEN
2005 IF (l_debug = 1) THEN
2006 debug_print('serial_control_code is ' || p_serial_control_code, 9);
2007 debug_print('Before calling inv_mut_insert', 9);
2008 END IF;
2009
2010 inv_mut_insert
2011 ( x_return_status => x_return_status,
2012 x_msg_count => x_msg_count,
2013 x_msg_data => x_msg_data,
2014 x_serial_number_tbl => l_serial_number_tbl,
2015 p_parent_serial_trx_id => p_parent_transaction_id,
2016 p_serial_transaction_id => p_transaction_id,
2017 p_organization_id => null,
2018 p_inventory_item_id => null,
2019 p_trx_source_type_id => null,
2020 p_receipt_issue_type => null);
2021
2022 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2023 IF (l_debug = 1) THEN
2024 debug_print('generate_serial_number returns error: ' || x_msg_data, 9);
2025 END IF;
2026 RAISE FND_API.G_EXC_ERROR;
2027 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2028 IF (l_debug = 1) THEN
2029 debug_print('inv_mut_insert returns unexpected error: ' || x_msg_data, 9);
2030 END IF;
2031 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2032 END IF;
2033
2034 ELSIF (p_serial_control_code = 6) THEN
2035 IF (l_debug = 1) THEN
2036 debug_print('Before calling generate_serial_numbers', 9);
2037 debug_print('serial_transaction_id is ' || p_transaction_id, 9);
2038 END IF;
2039
2040 -- generate serial number with the primary lot qty
2041 generate_serial_numbers
2042 (x_return_status => x_return_status,
2043 x_msg_count => x_msg_count,
2044 x_msg_data => x_msg_data,
2045 x_ser_num_tbl => l_serial_number_tbl,
2046 p_org_id => p_organization_id,
2047 p_item_id => p_inventory_item_id,
2048 p_lot_number => null,
2049 p_qty => p_primary_quantity,
2050 p_revision => p_revision);
2051
2052 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2053 IF (l_debug = 1) THEN
2054 debug_print('generate_serial_number returns error: ' || x_msg_data, 9);
2055 END IF;
2056 RAISE FND_API.G_EXC_ERROR;
2057 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2058 IF (l_debug = 1) THEN
2059 debug_print('generate_serial_number returns unexpected error: ' || x_msg_data, 9);
2060 END IF;
2061 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2062 END IF;
2063
2064 IF (l_debug = 1) THEN
2065 debug_print('generate_serial_numbers returns success', 9);
2066 debug_print('Before calling inv_mut_insert', 9);
2067 debug_print('p_serial_transaction_id = ' || p_transaction_id, 9);
2068 END IF;
2069
2070 inv_mut_insert
2071 (x_return_status => x_return_status,
2072 x_msg_count => x_msg_count,
2073 x_msg_data => x_msg_data,
2074 x_serial_number_tbl => l_serial_number_tbl,
2075 p_parent_serial_trx_id => null,
2076 p_serial_transaction_id => p_transaction_id,
2077 p_organization_id => p_organization_id,
2078 p_inventory_item_id => p_inventory_item_id,
2079 p_trx_source_type_id => p_trx_source_type_id,
2080 p_receipt_issue_type => 1);
2081
2082 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2083 IF (l_debug = 1) THEN
2084 debug_print('inv_mut_insert returns error: ' || x_msg_data, 9);
2085 END IF;
2086 RAISE FND_API.G_EXC_ERROR;
2087 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2088 IF (l_debug = 1) THEN
2089 debug_print('inv_mut_insert returns unexpected error: ' || x_msg_data, 9);
2090 END IF;
2091 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2092 END IF;
2093 END IF;
2094 END IF; -- p_lot_control_code = 2
2095
2096 IF (p_serial_control_code in (2, 5, 6)) THEN
2097 IF (l_debug = 1) THEN
2098 debug_print('serial_control_code = ' || p_serial_control_code, 9);
2099 debug_print('Before calling update_serial_numbers', 9);
2100 END IF;
2101
2102 update_serial_numbers
2103 (x_return_status => x_return_status,
2104 x_msg_count => x_msg_count,
2105 x_msg_data => x_msg_data,
2106 p_ser_num_tbl => l_serial_number_tbl,
2107 p_organization_id => p_organization_id,
2108 p_inventory_item_id => p_inventory_item_id);
2109
2110 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2111 IF (l_debug = 1) THEN
2112 debug_print('update_serial_numbers returns error: ' || x_msg_data, 9);
2113 END IF;
2114 RAISE FND_API.G_EXC_ERROR;
2115 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2116 IF (l_debug = 1) THEN
2117 debug_print('update_serial_numbers returns unexpected error: ' || x_msg_data, 9);
2118 END IF;
2119 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2120 END IF;
2121 END IF;
2122
2123 x_return_status := fnd_api.g_ret_sts_success;
2124 IF (l_debug = 1) THEN
2125 debug_print('Before returning from inv_lot_serial_insert', 9);
2126 debug_print('Return Status :' || x_return_status, 9);
2127 END IF;
2128 EXCEPTION
2129 WHEN FND_API.G_EXC_ERROR THEN
2130 x_return_status := FND_API.G_RET_STS_ERROR;
2131 IF (l_debug = 1) THEN
2132 debug_print('Expected Error', 9);
2133 debug_print('SQL Error: ' || Sqlerrm(SQLCODE),1);
2134 debug_print('Return Status :' || x_return_status);
2135 END IF;
2136 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2137
2138 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2139 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2140 IF (l_debug = 1) THEN
2141 debug_print('Expected Error', 9);
2142 debug_print('SQL Error: ' || Sqlerrm(SQLCODE),1);
2143 debug_print('Return Status :' || x_return_status);
2144 END IF;
2145 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2146
2147 WHEN OTHERS THEN
2148 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2149 IF (l_debug = 1) THEN
2150 debug_print('Error type others', 9);
2151 debug_print('SQL Error: ' || Sqlerrm(SQLCODE),1);
2152 debug_print('Return Status :' || x_return_status);
2153 END IF;
2154 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data =>
2155 x_msg_data);
2156 END inv_lot_serial_insert;
2157
2158 PROCEDURE generate_serial_numbers
2159 (
2160 x_return_status OUT NOCOPY VARCHAR2
2161 , x_msg_count OUT NOCOPY NUMBER
2162 , x_msg_data OUT NOCOPY VARCHAR2
2163 , x_ser_num_tbl OUT NOCOPY VARCHAR30_TBL
2164 , p_org_id IN NUMBER
2165 , p_item_id IN NUMBER
2166 , p_lot_number IN VARCHAR2
2167 , p_qty IN NUMBER
2168 , p_revision IN VARCHAR2
2169 )
2170 IS
2171 l_debug NUMBER := NVL(fnd_profile.value('INV_DEBUG_TRACE'),0);
2172 l_start_ser VARCHAR2(30);
2173 l_end_ser VARCHAR2(30);
2174 l_ser_prefix VARCHAR2(30);
2175 l_from_ser_num VARCHAR2(30);
2176 l_to_ser_num VARCHAR2(30);
2177 l_ser_suffix_length NUMBER;
2178 l_qty VARCHAR2(30);
2179 l_number NUMBER;
2180 l_errorcode NUMBER;
2181 l_retval NUMBER;
2182 l_msg_data VARCHAR2(2000);
2183 BEGIN
2184 IF (l_debug = 1) THEN
2185 debug_print('Enter generate_serial_numbers', 9);
2186 debug_print('p_org_id = ' || p_org_id, 9);
2187 debug_print('p_item_id = ' || p_item_id, 9);
2188 debug_print('p_lot_number = ' || p_lot_number, 9);
2189 debug_print('p_qty = ' || p_qty, 9);
2190 debug_print('p_revision = ' || p_revision, 9);
2191 debug_print('Before calling INV_SERIAL_NUMBER_PUB.generate_serials', 9);
2192 END IF;
2193
2194 -- generate serial number with the primary lot qty
2195 l_retval := INV_SERIAL_NUMBER_PUB.generate_serials
2196 (p_org_id => p_org_id,
2197 p_item_id => p_item_id,
2198 p_qty => abs(p_qty),
2199 p_wip_id => null,
2200 p_rev => p_revision,
2201 p_lot => p_lot_number,
2202 p_group_mark_id => null,
2203 p_line_mark_id => null,
2204 x_start_ser => l_start_ser,
2205 x_end_ser => l_end_ser,
2206 x_proc_msg => l_msg_data,
2207 p_skip_serial => null);
2208
2209 IF (l_debug = 1) THEN
2210 debug_print('INV_SERIAL_NUMBER_PUB.generate_serials returns l_retval = '
2211 || l_retval, 9);
2212 debug_print('l_start_ser = ' || l_start_ser, 9);
2213 debug_print('l_end_ser = ' || l_end_ser, 9);
2214 debug_print('Before calling MTL_SERIAL_CHECK.INV_SERIAL_INFO', 9);
2215 END IF;
2216
2217 -- get the prefix and from number of the start serial number
2218 IF NOT MTL_SERIAL_CHECK.INV_SERIAL_INFO
2219 (p_from_serial_number => l_start_ser,
2220 p_to_serial_number => l_end_ser,
2221 x_prefix => l_ser_prefix,
2222 x_quantity => l_qty,
2223 x_from_number => l_from_ser_num,
2224 x_to_number => l_to_ser_num,
2225 x_errorcode => l_errorcode) THEN
2226
2227 IF (l_debug = 1) THEN
2228 debug_print('MTL_SERIAL_CHECK.INV_SERIAL_INFO returns error', 9);
2229 debug_print('error code: ' || l_errorcode, 9);
2230 END IF;
2231
2232 FND_MESSAGE.SET_NAME('INV', 'INV_GET_SER_INFO_ERR');
2233 FND_MSG_PUB.ADD;
2234 RAISE FND_API.G_EXC_ERROR;
2235 END IF;
2236
2237 IF (l_debug = 1) THEN
2238 debug_print('MTL_SERIAL_CHECK.INV_SERIAL_INFO returns true', 9);
2239 debug_print('l_ser_prefix = ' || l_ser_prefix, 9);
2240 debug_print('l_qty = ' || l_qty, 9);
2241 debug_print('l_from_ser_num = ' || l_from_ser_num, 9);
2242 debug_print('l_to_ser_num = ' || l_to_ser_num, 9);
2243 END IF;
2244
2245 l_ser_suffix_length := LENGTH(l_from_ser_num);
2246 l_number := to_number(l_from_ser_num);
2247 FOR i in 1..l_qty LOOP
2248 x_ser_num_tbl(i) := l_ser_prefix || LPAD(TO_CHAR(l_number), l_ser_suffix_length, '0');
2249 l_number := l_number + 1;
2250 IF (l_debug = 1) THEN
2251 debug_print('serial number: ' || x_ser_num_tbl(i), 9);
2252 END IF;
2253 END LOOP;
2254
2255 IF (x_ser_num_tbl(l_qty) <> l_end_ser) THEN
2256 IF (l_debug = 1) THEN
2257 debug_print('x_ser_num_tbl(l_qty) is ' || x_ser_num_tbl(l_qty), 9);
2258 debug_print('l_end_ser is ' || l_end_ser, 9);
2259 END IF;
2260 END IF;
2261
2262 x_return_status := fnd_api.g_ret_sts_success;
2263 IF (l_debug = 1) THEN
2264 debug_print('Before return from generate_serial_numbers', 9);
2265 debug_print('Return Status :' || x_return_status, 9);
2266 END IF;
2267
2268 EXCEPTION
2269 WHEN FND_API.G_EXC_ERROR THEN
2270 x_return_status := FND_API.G_RET_STS_ERROR;
2271 IF (l_debug = 1) THEN
2272 debug_print('Expected Error', 9);
2273 debug_print('SQL Error: ' || Sqlerrm(SQLCODE),1);
2274 debug_print('Return Status :' || x_return_status);
2275 END IF;
2276 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2277
2278 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2279 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2280 IF (l_debug = 1) THEN
2281 debug_print('Expected Error', 9);
2282 debug_print('SQL Error: ' || Sqlerrm(SQLCODE),1);
2283 debug_print('Return Status :' || x_return_status);
2284 END IF;
2285 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2286
2287 WHEN OTHERS THEN
2288 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2289 IF (l_debug = 1) THEN
2290 debug_print('Error type others', 9);
2291 debug_print('SQL Error: ' || Sqlerrm(SQLCODE),1);
2292 debug_print('Return Status :' || x_return_status);
2293 END IF;
2294 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data =>
2295 x_msg_data);
2296 END generate_serial_numbers;
2297
2298 PROCEDURE inv_mut_insert
2299 (
2300 x_return_status OUT NOCOPY VARCHAR2
2301 , x_msg_count OUT NOCOPY NUMBER
2302 , x_msg_data OUT NOCOPY VARCHAR2
2303 , x_serial_number_tbl IN OUT NOCOPY VARCHAR30_TBL
2304 , p_parent_serial_trx_id IN NUMBER
2305 , p_serial_transaction_id IN NUMBER
2306 , p_organization_id IN NUMBER
2307 , p_inventory_item_id IN NUMBER
2308 , p_trx_source_type_id IN NUMBER
2309 , p_receipt_issue_type IN NUMBER
2310 )
2311 IS
2312 l_debug NUMBER := NVL(fnd_profile.value('INV_DEBUG_TRACE'),0);
2313 l_index NUMBER := 0;
2314 cursor mut_cur(p_transaction_id NUMBER) IS
2315 SELECT SERIAL_NUMBER,
2316 INVENTORY_ITEM_ID,
2317 ORGANIZATION_ID,
2318 SUBINVENTORY_CODE,
2319 LOCATOR_ID,
2320 TRANSACTION_SOURCE_ID,
2321 TRANSACTION_SOURCE_TYPE_ID,
2322 TRANSACTION_SOURCE_NAME,
2323 RECEIPT_ISSUE_TYPE,
2324 CUSTOMER_ID,
2325 SHIP_ID,
2326 SERIAL_ATTRIBUTE_CATEGORY,
2327 ORIGINATION_DATE,
2328 C_ATTRIBUTE1,
2329 C_ATTRIBUTE2,
2330 C_ATTRIBUTE3,
2331 C_ATTRIBUTE4,
2332 C_ATTRIBUTE5,
2333 C_ATTRIBUTE6,
2334 C_ATTRIBUTE7,
2335 C_ATTRIBUTE8,
2336 C_ATTRIBUTE9,
2337 C_ATTRIBUTE10,
2338 C_ATTRIBUTE11,
2339 C_ATTRIBUTE12,
2340 C_ATTRIBUTE13,
2341 C_ATTRIBUTE14,
2342 C_ATTRIBUTE15,
2343 C_ATTRIBUTE16,
2344 C_ATTRIBUTE17,
2345 C_ATTRIBUTE18,
2346 C_ATTRIBUTE19,
2347 C_ATTRIBUTE20,
2348 D_ATTRIBUTE1,
2349 D_ATTRIBUTE2,
2350 D_ATTRIBUTE3,
2351 D_ATTRIBUTE4,
2352 D_ATTRIBUTE5,
2353 D_ATTRIBUTE6,
2354 D_ATTRIBUTE7,
2355 D_ATTRIBUTE8,
2356 D_ATTRIBUTE9,
2357 D_ATTRIBUTE10,
2358 N_ATTRIBUTE1,
2359 N_ATTRIBUTE2,
2360 N_ATTRIBUTE3,
2361 N_ATTRIBUTE4,
2362 N_ATTRIBUTE5,
2363 N_ATTRIBUTE6,
2364 N_ATTRIBUTE7,
2365 N_ATTRIBUTE8,
2366 N_ATTRIBUTE9,
2367 N_ATTRIBUTE10,
2368 STATUS_ID,
2369 TERRITORY_CODE,
2370 TIME_SINCE_NEW,
2371 CYCLES_SINCE_NEW,
2372 TIME_SINCE_OVERHAUL,
2373 CYCLES_SINCE_OVERHAUL,
2374 TIME_SINCE_REPAIR,
2375 CYCLES_SINCE_REPAIR,
2376 TIME_SINCE_VISIT,
2377 CYCLES_SINCE_VISIT,
2378 TIME_SINCE_MARK,
2379 CYCLES_SINCE_MARK,
2380 NUMBER_OF_REPAIRS,
2381 PRODUCT_CODE,
2382 PRODUCT_TRANSACTION_ID
2383 FROM mtl_unit_transactions
2384 WHERE transaction_id = p_transaction_id;
2385 BEGIN
2386 IF (l_debug = 1) THEN
2387 debug_print('Enter inv_mut_insert', 9);
2388 debug_print('p_parent_serial_trx_id = ' || p_parent_serial_trx_id, 9);
2389 debug_print('p_serial_transaction_id = ' || p_serial_transaction_id, 9);
2390 debug_print('p_organization_id = ' || p_organization_id, 9);
2391 debug_print('p_inventory_item_id = ' || p_inventory_item_id, 9);
2392 debug_print('p_trx_source_type_id = ' || p_trx_source_type_id, 9);
2393 debug_print('p_receipt_issue_type = ' || p_receipt_issue_type, 9);
2394 END IF;
2395
2396 IF (p_parent_serial_trx_id IS NOT NULL OR p_parent_serial_trx_id > 0) THEN
2397 FOR l_mut in mut_cur(p_parent_serial_trx_id) LOOP
2398 INSERT INTO mtl_unit_transactions
2399 (
2400 TRANSACTION_ID
2401 ,LAST_UPDATE_DATE
2402 ,LAST_UPDATED_BY
2403 ,CREATION_DATE
2404 ,CREATED_BY
2405 ,LAST_UPDATE_LOGIN
2406 ,SERIAL_NUMBER
2407 ,INVENTORY_ITEM_ID
2408 ,ORGANIZATION_ID
2409 ,SUBINVENTORY_CODE
2410 ,LOCATOR_ID
2411 ,TRANSACTION_DATE
2412 ,TRANSACTION_SOURCE_ID
2413 ,TRANSACTION_SOURCE_TYPE_ID
2414 ,TRANSACTION_SOURCE_NAME
2415 ,RECEIPT_ISSUE_TYPE
2416 ,CUSTOMER_ID
2417 ,SHIP_ID
2418 ,SERIAL_ATTRIBUTE_CATEGORY
2419 ,ORIGINATION_DATE
2420 ,C_ATTRIBUTE1
2421 ,C_ATTRIBUTE2
2422 ,C_ATTRIBUTE3
2423 ,C_ATTRIBUTE4
2424 ,C_ATTRIBUTE5
2425 ,C_ATTRIBUTE6
2426 ,C_ATTRIBUTE7
2427 ,C_ATTRIBUTE8
2428 ,C_ATTRIBUTE9
2429 ,C_ATTRIBUTE10
2430 ,C_ATTRIBUTE11
2431 ,C_ATTRIBUTE12
2432 ,C_ATTRIBUTE13
2433 ,C_ATTRIBUTE14
2434 ,C_ATTRIBUTE15
2435 ,C_ATTRIBUTE16
2436 ,C_ATTRIBUTE17
2437 ,C_ATTRIBUTE18
2438 ,C_ATTRIBUTE19
2439 ,C_ATTRIBUTE20
2440 ,D_ATTRIBUTE1
2441 ,D_ATTRIBUTE2
2442 ,D_ATTRIBUTE3
2443 ,D_ATTRIBUTE4
2444 ,D_ATTRIBUTE5
2445 ,D_ATTRIBUTE6
2446 ,D_ATTRIBUTE7
2447 ,D_ATTRIBUTE8
2448 ,D_ATTRIBUTE9
2449 ,D_ATTRIBUTE10
2450 ,N_ATTRIBUTE1
2451 ,N_ATTRIBUTE2
2452 ,N_ATTRIBUTE3
2453 ,N_ATTRIBUTE4
2454 ,N_ATTRIBUTE5
2455 ,N_ATTRIBUTE6
2456 ,N_ATTRIBUTE7
2457 ,N_ATTRIBUTE8
2458 ,N_ATTRIBUTE9
2459 ,N_ATTRIBUTE10
2460 ,STATUS_ID
2461 ,TERRITORY_CODE
2462 ,TIME_SINCE_NEW
2463 ,CYCLES_SINCE_NEW
2464 ,TIME_SINCE_OVERHAUL
2465 ,CYCLES_SINCE_OVERHAUL
2466 ,TIME_SINCE_REPAIR
2467 ,CYCLES_SINCE_REPAIR
2468 ,TIME_SINCE_VISIT
2469 ,CYCLES_SINCE_VISIT
2470 ,TIME_SINCE_MARK
2471 ,CYCLES_SINCE_MARK
2472 ,NUMBER_OF_REPAIRS
2473 ,PRODUCT_CODE
2474 ,PRODUCT_TRANSACTION_ID
2475 )
2476 VALUES
2477 (
2478 p_serial_transaction_id
2479 ,SYSDATE
2480 ,FND_GLOBAL.user_id
2481 ,SYSDATE
2482 ,FND_GLOBAL.user_id
2483 ,FND_GLOBAL.login_id
2484 ,l_mut.SERIAL_NUMBER
2485 ,l_mut.INVENTORY_ITEM_ID
2486 ,l_mut.ORGANIZATION_ID
2487 ,l_mut.SUBINVENTORY_CODE
2488 ,l_mut.LOCATOR_ID
2489 ,SYSDATE
2490 ,l_mut.TRANSACTION_SOURCE_ID
2491 ,l_mut.TRANSACTION_SOURCE_TYPE_ID
2492 ,l_mut.TRANSACTION_SOURCE_NAME
2493 ,l_mut.RECEIPT_ISSUE_TYPE
2494 ,l_mut.CUSTOMER_ID
2495 ,l_mut.SHIP_ID
2496 ,l_mut.SERIAL_ATTRIBUTE_CATEGORY
2497 ,l_mut.ORIGINATION_DATE
2498 ,l_mut.C_ATTRIBUTE1
2499 ,l_mut.C_ATTRIBUTE2
2500 ,l_mut.C_ATTRIBUTE3
2501 ,l_mut.C_ATTRIBUTE4
2502 ,l_mut.C_ATTRIBUTE5
2503 ,l_mut.C_ATTRIBUTE6
2504 ,l_mut.C_ATTRIBUTE7
2505 ,l_mut.C_ATTRIBUTE8
2506 ,l_mut.C_ATTRIBUTE9
2507 ,l_mut.C_ATTRIBUTE10
2508 ,l_mut.C_ATTRIBUTE11
2509 ,l_mut.C_ATTRIBUTE12
2510 ,l_mut.C_ATTRIBUTE13
2511 ,l_mut.C_ATTRIBUTE14
2512 ,l_mut.C_ATTRIBUTE15
2513 ,l_mut.C_ATTRIBUTE16
2514 ,l_mut.C_ATTRIBUTE17
2515 ,l_mut.C_ATTRIBUTE18
2516 ,l_mut.C_ATTRIBUTE19
2517 ,l_mut.C_ATTRIBUTE20
2518 ,l_mut.D_ATTRIBUTE1
2519 ,l_mut.D_ATTRIBUTE2
2520 ,l_mut.D_ATTRIBUTE3
2521 ,l_mut.D_ATTRIBUTE4
2522 ,l_mut.D_ATTRIBUTE5
2523 ,l_mut.D_ATTRIBUTE6
2524 ,l_mut.D_ATTRIBUTE7
2525 ,l_mut.D_ATTRIBUTE8
2526 ,l_mut.D_ATTRIBUTE9
2527 ,l_mut.D_ATTRIBUTE10
2528 ,l_mut.N_ATTRIBUTE1
2529 ,l_mut.N_ATTRIBUTE2
2530 ,l_mut.N_ATTRIBUTE3
2531 ,l_mut.N_ATTRIBUTE4
2532 ,l_mut.N_ATTRIBUTE5
2533 ,l_mut.N_ATTRIBUTE6
2534 ,l_mut.N_ATTRIBUTE7
2535 ,l_mut.N_ATTRIBUTE8
2536 ,l_mut.N_ATTRIBUTE9
2537 ,l_mut.N_ATTRIBUTE10
2538 ,l_mut.STATUS_ID
2539 ,l_mut.TERRITORY_CODE
2540 ,l_mut.TIME_SINCE_NEW
2541 ,l_mut.CYCLES_SINCE_NEW
2542 ,l_mut.TIME_SINCE_OVERHAUL
2543 ,l_mut.CYCLES_SINCE_OVERHAUL
2544 ,l_mut.TIME_SINCE_REPAIR
2545 ,l_mut.CYCLES_SINCE_REPAIR
2546 ,l_mut.TIME_SINCE_VISIT
2547 ,l_mut.CYCLES_SINCE_VISIT
2548 ,l_mut.TIME_SINCE_MARK
2549 ,l_mut.CYCLES_SINCE_MARK
2550 ,l_mut.NUMBER_OF_REPAIRS
2551 ,l_mut.PRODUCT_CODE
2552 ,l_mut.PRODUCT_TRANSACTION_ID
2553 );
2554
2555 l_index := l_index + 1;
2556 x_serial_number_tbl(l_index) := l_mut.serial_number;
2557 END LOOP; -- end of loop l_mut
2558 ELSE
2559 forall i IN 1..x_serial_number_tbl.COUNT
2560 INSERT INTO mtl_unit_transactions
2561 (
2562 TRANSACTION_ID
2563 ,LAST_UPDATE_DATE
2564 ,LAST_UPDATED_BY
2565 ,CREATION_DATE
2566 ,CREATED_BY
2567 ,LAST_UPDATE_LOGIN
2568 ,SERIAL_NUMBER
2569 ,INVENTORY_ITEM_ID
2570 ,ORGANIZATION_ID
2571 ,TRANSACTION_SOURCE_TYPE_ID
2572 ,RECEIPT_ISSUE_TYPE
2573 ,TRANSACTION_DATE
2574 )
2575 VALUES
2576 (
2577 p_serial_transaction_id
2578 ,SYSDATE
2579 ,FND_GLOBAL.user_id
2580 ,SYSDATE
2581 ,FND_GLOBAL.user_id
2582 ,FND_GLOBAL.login_id
2583 ,x_serial_number_tbl(i)
2584 ,p_inventory_item_id
2585 ,p_organization_id
2586 ,p_trx_source_type_id
2587 ,p_receipt_issue_type
2588 ,SYSDATE
2589 );
2590 END IF;
2591
2592 x_return_status := fnd_api.g_ret_sts_success;
2593 IF (l_debug = 1) THEN
2594 debug_print('Before return from inv_mut_insert', 9);
2595 debug_print('Return Status :' || x_return_status, 9);
2596 END IF;
2597 EXCEPTION
2598 WHEN FND_API.G_EXC_ERROR THEN
2599 x_return_status := FND_API.G_RET_STS_ERROR;
2600 IF (l_debug = 1) THEN
2601 debug_print('Expected Error', 9);
2602 debug_print('SQL Error: ' || Sqlerrm(SQLCODE),1);
2603 debug_print('Return Status :' || x_return_status);
2604 END IF;
2605 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2606
2607 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2608 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2609 IF (l_debug = 1) THEN
2610 debug_print('Expected Error', 9);
2611 debug_print('SQL Error: ' || Sqlerrm(SQLCODE),1);
2612 debug_print('Return Status :' || x_return_status);
2613 END IF;
2614 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2615
2616 WHEN OTHERS THEN
2617 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2618 IF (l_debug = 1) THEN
2619 debug_print('Error type others', 9);
2620 debug_print('SQL Error: ' || Sqlerrm(SQLCODE),1);
2621 debug_print('Return Status :' || x_return_status);
2622 END IF;
2623 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data =>
2624 x_msg_data);
2625
2626 END inv_mut_insert;
2627
2628 PROCEDURE update_serial_numbers
2629 (
2630 x_return_status OUT NOCOPY VARCHAR2
2631 , x_msg_count OUT NOCOPY NUMBER
2632 , x_msg_data OUT NOCOPY VARCHAR2
2633 , p_ser_num_tbl IN VARCHAR30_TBL
2634 , p_organization_id IN NUMBER
2635 , p_inventory_item_id IN NUMBER
2636 )
2637 IS
2638 l_debug NUMBER := NVL(fnd_profile.value('INV_DEBUG_TRACE'),0);
2639 BEGIN
2640 IF (l_debug = 1) THEN
2641 debug_print('Enter update_serial_numbers', 9);
2642 debug_print('p_organization_id = ' || p_organization_id, 9);
2643 debug_print('p_invventory_item_id = ' || p_inventory_item_id, 9);
2644 END IF;
2645
2646 forall i in p_ser_num_tbl.FIRST..p_ser_num_tbl.LAST
2647 UPDATE mtl_serial_numbers
2648 SET current_status = 4
2649 WHERE current_organization_id = p_organization_id
2650 AND serial_number = p_ser_num_tbl(i)
2651 AND inventory_item_id = p_inventory_item_id;
2652
2653 IF (SQL%ROWCOUNT <> p_ser_num_tbl.COUNT) THEN
2654 IF (l_debug = 1) THEN
2655 debug_print('The number of rows updated in mtl_serial_numbers is not equals
2656 to the number of serial numbers that needed to be updated', 9);
2657 END IF;
2658 END IF;
2659 EXCEPTION
2660 WHEN FND_API.G_EXC_ERROR THEN
2661 x_return_status := FND_API.G_RET_STS_ERROR;
2662 IF (l_debug = 1) THEN
2663 debug_print('Expected Error', 9);
2664 debug_print('SQL Error: ' || Sqlerrm(SQLCODE),1);
2665 debug_print('Return Status :' || x_return_status);
2666 END IF;
2667 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2668
2669 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2670 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2671 IF (l_debug = 1) THEN
2672 debug_print('Expected Error', 9);
2673 debug_print('SQL Error: ' || Sqlerrm(SQLCODE),1);
2674 debug_print('Return Status :' || x_return_status);
2675 END IF;
2676 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2677
2678 WHEN OTHERS THEN
2679 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2680 IF (l_debug = 1) THEN
2681 debug_print('Error type others', 9);
2682 debug_print('SQL Error: ' || Sqlerrm(SQLCODE),1);
2683 debug_print('Return Status :' || x_return_status);
2684 END IF;
2685 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data =>
2686 x_msg_data);
2687 END update_serial_numbers;
2688
2689 END inv_logical_transactions_pvt;
2690