[Home] [Help]
PACKAGE BODY: APPS.INV_TRX_UTIL_PUB
Source
1 PACKAGE BODY inv_trx_util_pub AS
2 /* $Header: INVTRXUB.pls 120.21.12020000.2 2012/07/04 08:11:12 raminoch ship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'INV_TRX_UTIL_PUB';
5
6 PROCEDURE TRACE(p_mesg VARCHAR2, p_mod VARCHAR2, p_level NUMBER := 9) IS
7 BEGIN
8 inv_log_util.TRACE(p_mesg, p_mod, p_level);
9 END;
10
11 --
12 -- Name: INSERT_LINE_TRX
13 --
14 -- Functions: This API inserts a row into MTL_MATERIAL_TRANSACTIONS_TEMP
15 -- The function returns the transaction_temp_id which is unique for this
16 -- record, and could be used for coupling Lot and Serial Transaction
17 -- records associated with this transaction.
18 --
19 FUNCTION insert_line_trx(
20 p_trx_hdr_id IN NUMBER
21 , p_item_id IN NUMBER
22 , p_revision IN VARCHAR2 := NULL
23 , p_org_id IN NUMBER
24 , p_trx_action_id IN NUMBER
25 , p_subinv_code IN VARCHAR2
26 , p_tosubinv_code IN VARCHAR2 := NULL
27 , p_locator_id IN NUMBER := NULL
28 , p_tolocator_id IN NUMBER := NULL
29 , p_xfr_org_id IN NUMBER := NULL
30 , p_trx_type_id IN NUMBER
31 , p_trx_src_type_id IN NUMBER
32 , p_trx_qty IN NUMBER
33 , p_pri_qty IN NUMBER
34 , p_uom IN VARCHAR2
35 , p_date IN DATE := SYSDATE
36 , p_reason_id IN NUMBER := NULL
37 , p_user_id IN NUMBER
38 , p_frt_code IN VARCHAR2 := NULL
39 , p_ship_num IN VARCHAR2 := NULL
40 , p_dist_id IN NUMBER := NULL
41 , p_way_bill IN VARCHAR2 := NULL
42 , p_exp_arr IN DATE := NULL
43 , p_cost_group IN NUMBER := NULL
44 , p_from_lpn_id IN NUMBER := NULL
45 , p_cnt_lpn_id IN NUMBER := NULL
46 , p_xfr_lpn_id IN NUMBER := NULL
47 , p_trx_src_id IN NUMBER := NULL
48 , x_trx_tmp_id OUT NOCOPY NUMBER
49 , x_proc_msg OUT NOCOPY VARCHAR2
50 , p_xfr_cost_group IN NUMBER := NULL
51 , p_completion_trx_id IN NUMBER := NULL
52 , p_flow_schedule IN VARCHAR2 := NULL
53 , p_trx_cost IN NUMBER := NULL
54 , p_project_id IN NUMBER := NULL
55 , p_task_id IN NUMBER := NULL
56 , p_cost_of_transfer IN NUMBER := NULL
57 , p_cost_of_transportation IN NUMBER := NULL
58 , p_transfer_percentage IN NUMBER := NULL
59 , p_transportation_cost_account IN NUMBER := NULL
60 , p_planning_org_id IN NUMBER
61 , p_planning_tp_type IN NUMBER
62 , p_owning_org_id IN NUMBER
63 , p_owning_tp_type IN NUMBER
64 , p_trx_src_line_id IN NUMBER := NULL
65 , p_secondary_trx_qty IN NUMBER := NULL
66 , p_secondary_uom IN VARCHAR2 := NULL
67 , p_move_order_line_id IN NUMBER := NULL
68 , p_posting_flag IN VARCHAR2 := NULL
69 , p_move_order_header_id IN NUMBER
70 , p_serial_allocated_flag IN VARCHAR2
71 , p_transaction_status IN NUMBER
72 , p_process_flag IN VARCHAR2 := NULL
73 , p_ship_to_location_id IN NUMBER --eIB Build; Bug# 4348541
74 , p_relieve_reservations_flag IN VARCHAR2 := NULL -- Bug 6310875
75 , p_opm_org_in_xfer IN VARCHAR2 -- Bug 8939057
76 )
77 RETURN NUMBER IS
78 v_trxqty NUMBER := p_trx_qty;
79 v_priqty NUMBER := p_pri_qty;
80 v_acct_period_id NUMBER;
81 v_open_past_period BOOLEAN := FALSE;
82 v_trx_hdr_id NUMBER := p_trx_hdr_id;
83 v_item_id NUMBER := p_item_id;
84 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
85
86 --8939057
87 l_api_name VARCHAR2(30) := 'INSERT_LINE_TRX';
88 x_transfer_price NUMBER;
89 x_currency_code VARCHAR2(31);
90 x_transfer_price_priuom NUMBER;
91 x_incr_transfer_price NUMBER;
92 x_incr_currency_code VARCHAR2(31);
93 x_return_status VARCHAR2(1);
94 x_msg_data VARCHAR2(2000);
95 x_msg_count NUMBER;
96
97 --bug 9022750
98 l_lot_ctrl_code NUMBER;
99 l_ser_ctrl_code NUMBER;
100 l_rev_ctrl_code NUMBER;
101 l_loc_ctrl_code NUMBER;
102
103 --bug 12588822
104 l_xfr_project_id NUMBER := NULL;
105 l_xfr_task_id NUMBER := NULL;
106
107 BEGIN
108
109 -- get the account period ID
110 invttmtx.tdatechk(p_org_id, p_date, v_acct_period_id, v_open_past_period);
111
112 IF (v_acct_period_id = 0)
113 OR(v_acct_period_id = -1) THEN
114 fnd_message.set_name('INV', 'INV_NO_OPEN_PERIOD');
115 fnd_msg_pub.ADD;
116 RAISE fnd_api.g_exc_error;
117 END IF;
118
119 -- Start Bug 8939057
120 inv_log_util.trace('p_process_flag is:' || p_process_flag, g_pkg_name || '.' || l_api_name, 5);
121 inv_log_util.trace('p_relieve_reservations_flag is:' || p_relieve_reservations_flag, g_pkg_name || '.' || l_api_name, 5);
122 inv_log_util.trace('p_opm_org_in_xfer is:' || p_opm_org_in_xfer, g_pkg_name || '.' || l_api_name, 5);
123 inv_log_util.trace('p_item_id is:' || p_item_id, g_pkg_name || '.' || l_api_name, 5);
124 inv_log_util.trace('p_trx_qty is:' || p_trx_qty, g_pkg_name || '.' || l_api_name, 5);
125 inv_log_util.trace('p_uom is:' || p_uom, g_pkg_name || '.' || l_api_name, 5);
126 inv_log_util.trace('p_from_org_id is:' || p_org_id, g_pkg_name || '.' || l_api_name, 5);
127 inv_log_util.trace('p_to_org_id is :' || p_xfr_org_id, g_pkg_name || '.' || l_api_name, 5);
128
129 -- Call GMF get transfer price only if FROM or TO org is process enabled.
130 IF (p_opm_org_in_xfer = 'Y') THEN
131
132 inv_log_util.trace('Calling GMF_get_transfer_price_PUB.get_transfer_price', g_pkg_name || '.' || l_api_name, 5);
133
134 GMF_get_transfer_price_PUB.get_transfer_price (
135 p_api_version => 1.0
136 , p_init_msg_list => 'F'
137
138 , p_inventory_item_id => p_item_id
139 , p_transaction_qty => p_trx_qty
140 , p_transaction_uom => p_uom
141
142 , p_transaction_id => NULL -- mtl_trx_line.transaction_id ***
143 , p_global_procurement_flag => 'N'
144 , p_drop_ship_flag => 'N'
145
146 , p_from_organization_id => p_org_id
147 , p_from_ou => 1 -- Passing dummy value as this is fetched again in GMF.
148 , p_to_organization_id => p_xfr_org_id
149 , p_to_ou => 1 -- Passing dummy value as this is fetched again in GMF.
150
151 , p_transfer_type => 'INTORG'
152 , p_transfer_source => 'INTORG'
153
154 , x_return_status => x_return_status
155 , x_msg_data => x_msg_data
156 , x_msg_count => x_msg_count
157
158 , x_transfer_price => x_transfer_price
159 , x_transfer_price_priuom => x_transfer_price_priuom /* Store Transfer Price in pri uom */
160 , x_currency_code => x_currency_code
161 , x_incr_transfer_price => x_incr_transfer_price
162 , x_incr_currency_code => x_incr_currency_code
163 );
164
165 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
166 THEN
167 inv_log_util.trace('X_return status is <> S',g_pkg_name || '.' || l_api_name, 5);
168 x_transfer_price := 0;
169 END IF;
170
171 inv_log_util.trace('x_transfer_price is:' || x_transfer_price, g_pkg_name || '.' || l_api_name, 5);
172 inv_log_util.trace('x_currency_code is :' || x_currency_code, g_pkg_name || '.' || l_api_name, 5);
173 END IF;
174 -- End Bug 8939057
175
176 IF (p_trx_action_id = inv_globals.g_action_issue)
177 OR(p_trx_action_id = inv_globals.g_action_intransitshipment) THEN
178 v_trxqty := -1 * p_trx_qty;
179 v_priqty := -1 * p_pri_qty;
180 END IF;
181
182 /* SELECT mtl_material_transactions_s.NEXTVAL
183 INTO x_trx_tmp_id
184 FROM DUAL; */
185
186 -- If content Item Id is not NULL then set ItemId = -1;
187 IF (p_cnt_lpn_id IS NOT NULL) THEN
188 v_item_id := -1;
189 END IF;
190
191 -- If the user passes NULL for p_trx_hdr_id insert into MMTT.TRX_HDR_ID
192 -- same value as TRX_TEMP_ID
193 /* IF (v_trx_hdr_id IS NULL) THEN
194 v_trx_hdr_id := x_trx_tmp_id;
195 END IF; */
196
197 --bug 9022750
198 BEGIN
199
200 SELECT lot_control_code, serial_number_control_code, revision_qty_control_code, location_control_code
201 INTO l_lot_ctrl_code, l_ser_ctrl_code, l_rev_ctrl_code, l_loc_ctrl_code
202 FROM mtl_system_items
203 WHERE organization_id = p_org_id
204 AND inventory_item_id = p_item_id;
205
206 IF (l_debug = 1) THEN
207 TRACE('lot ctrl:'||l_lot_ctrl_code||' ser ctrl:'||l_ser_ctrl_code||' rev ctrl:'||l_rev_ctrl_code||' loc ctrl:'||l_loc_ctrl_code, 'INVTRXUB', 9);
208 END IF;
209
210 EXCEPTION
211 WHEN OTHERS THEN
212 l_lot_ctrl_code := 0;
213 l_ser_ctrl_code := 0;
214 l_rev_ctrl_code := 0;
215 l_loc_ctrl_code := 0;
216 IF (l_debug = 1) THEN
217 TRACE('Exception:'||SQLERRM, 'INVTRXUB', 9);
218 END IF;
219
220 END;
221
222 --bug 12588822
223 IF ( (p_trx_action_id = inv_globals.G_ACTION_CONTAINERPACK OR p_trx_action_id = inv_globals.G_ACTION_CONTAINERUNPACK OR p_trx_action_id = inv_globals.G_ACTION_CONTAINERSPLIT)
224 AND p_trx_src_type_id = inv_globals.G_SOURCETYPE_INVENTORY) THEN
225 l_xfr_project_id := p_project_id;
226 l_xfr_task_id := p_task_id;
227 END IF;
228
229 INSERT INTO mtl_material_transactions_temp
230 (
231 transaction_header_id
232 , transaction_temp_id
233 , process_flag
234 , creation_date
235 , created_by
236 , last_update_date
237 , last_updated_by
238 , last_update_login
239 , inventory_item_id
240 , organization_id
241 , subinventory_code
242 , locator_id
243 , transfer_to_location
244 , transaction_quantity
245 , primary_quantity
246 , transaction_uom
247 , secondary_transaction_quantity
248 , secondary_uom_code
249 , transaction_type_id
250 , transaction_action_id
251 , transaction_source_type_id
252 , transaction_date
253 , acct_period_id
254 , transfer_organization
255 , transfer_subinventory
256 , reason_id
257 , shipment_number
258 , distribution_account_id
259 , waybill_airbill
260 , expected_arrival_date
261 , freight_code
262 , revision
263 , lpn_id
264 , content_lpn_id
265 , transfer_lpn_id
266 , cost_group_id
267 , transaction_source_id
268 , trx_source_line_id
269 , transfer_cost_group_id
270 , completion_transaction_id
271 , flow_schedule
272 , transaction_cost
273 , project_id
274 , task_id
275 , planning_organization_id
276 , planning_tp_type
277 , owning_organization_id
278 , owning_tp_type
279 , posting_flag
280 , transfer_cost
281 , transportation_cost
282 , transfer_percentage
283 , transportation_account
284 , move_order_header_id
285 , move_order_line_id
286 , serial_allocated_flag
287 , transaction_status
288 , ship_to_location --eIB Build; Bug# 4348541
289 , relieve_reservations_flag -- Bug 6310875
290 , transfer_price -- Bug 8939057
291 --bug9022750
292 , item_lot_control_code
293 , item_serial_control_code
294 , item_revision_qty_control_code
295 , item_location_control_code
296 , to_project_id --bug 12588822
297 , to_task_id --bug 12588822
298 )
299 VALUES (
300 nvl(v_trx_hdr_id,mtl_material_transactions_s.NEXTVAL)
301 -- , x_trx_tmp_id
302 , mtl_material_transactions_s.NEXTVAL -- Bug 5535030
303 , nvl(p_process_flag,'Y')
304 , SYSDATE
305 , p_user_id
306 , SYSDATE
307 , p_user_id
308 , p_user_id
309 , v_item_id
310 , p_org_id
311 , p_subinv_code
312 , p_locator_id
313 , p_tolocator_id
314 , v_trxqty
315 , v_priqty
316 , p_uom
317 , p_secondary_trx_qty
318 , p_secondary_uom
319 , p_trx_type_id
320 , p_trx_action_id
321 , p_trx_src_type_id
322 , p_date
323 , v_acct_period_id
324 , p_xfr_org_id
325 , p_tosubinv_code
326 , p_reason_id
327 , p_ship_num
328 , p_dist_id
329 , p_way_bill
330 , p_exp_arr
331 , p_frt_code
332 , p_revision
333 , p_from_lpn_id
334 , p_cnt_lpn_id
335 , p_xfr_lpn_id
336 , p_cost_group
337 , p_trx_src_id
338 , p_trx_src_line_id
339 , p_xfr_cost_group
340 , p_completion_trx_id
341 , p_flow_schedule
342 , p_trx_cost
343 , p_project_id
344 , p_task_id
345 , p_planning_org_id
346 , p_planning_tp_type
347 , p_owning_org_id
348 , p_owning_tp_type
349 , nvl(p_posting_flag,'Y')
350 , p_cost_of_transfer
351 , p_cost_of_transportation
352 , p_transfer_percentage
353 , p_transportation_cost_account
354 , p_move_order_header_id
355 , p_move_order_line_id
356 , p_serial_allocated_flag
357 , p_transaction_status
358 , p_ship_to_location_id --eIB Build; Bug# 4348541
359 , p_relieve_reservations_flag -- Bug 6310875
360 , x_transfer_price -- Bug 8939057
361 --bug9022750
362 , l_lot_ctrl_code
363 , l_ser_ctrl_code
364 , l_rev_ctrl_code
365 , l_loc_ctrl_code
366 , l_xfr_project_id --bug 12588822
367 , l_xfr_task_id --bug 12588822
368 ) RETURNING transaction_temp_id INTO x_trx_tmp_id;
369
370
371 RETURN 0;
372 EXCEPTION
373 WHEN fnd_api.g_exc_error THEN
374 x_proc_msg := fnd_msg_pub.get(1, 'F');
375 RETURN -1;
376 WHEN OTHERS THEN
377 x_proc_msg := SUBSTR(SQLERRM, 1, 200);
378 RETURN -1;
379 END;
380
381 --
382 -- Name: INSERT_LOT_TRX
383 --
384 -- Functions: This function inserts a Lot Transaction record into
385 -- MTL_TRANSACTION_LOT_NUMBERS. The argument p_trx_tmp_id is
386 -- used to couple this record with a transaction-line in
387 -- MTL_MATERIAL_TRANSACTIONS_TEMP
388 --
389 FUNCTION insert_lot_trx(
390 p_trx_tmp_id IN NUMBER
391 , p_user_id IN NUMBER
392 , p_lot_number IN VARCHAR2
393 , p_trx_qty IN NUMBER
394 , p_pri_qty IN NUMBER
395 , p_exp_date IN DATE := NULL
396 , p_description IN VARCHAR2 := NULL
397 , p_vendor_name IN VARCHAR2 := NULL
398 , p_supplier_lot_number IN VARCHAR2 := NULL
399 , p_origination_date IN DATE := NULL
400 , p_date_code IN VARCHAR2 := NULL
401 , p_grade_code IN VARCHAR2 := NULL
402 , p_change_date IN DATE := NULL
403 , p_maturity_date IN DATE := NULL
404 , p_status_id IN NUMBER := NULL
405 , p_retest_date IN DATE := NULL
406 , p_age IN NUMBER := NULL
407 , p_item_size IN NUMBER := NULL
408 , p_color IN VARCHAR2 := NULL
409 , p_volume IN NUMBER := NULL
410 , p_volume_uom IN VARCHAR2 := NULL
411 , p_place_of_origin IN VARCHAR2 := NULL
412 , p_best_by_date IN DATE := NULL
413 , p_length IN NUMBER := NULL
414 , p_length_uom IN VARCHAR2 := NULL
415 , p_recycled_content IN NUMBER := NULL
416 , p_thickness IN NUMBER := NULL
417 , p_thickness_uom IN VARCHAR2 := NULL
418 , p_width IN NUMBER := NULL
419 , p_width_uom IN VARCHAR2 := NULL
420 , p_curl_wrinkle_fold IN VARCHAR2 := NULL
421 , p_lot_attribute_category IN VARCHAR2 := NULL
422 , p_c_attribute1 IN VARCHAR2 := NULL
423 , p_c_attribute2 IN VARCHAR2 := NULL
424 , p_c_attribute3 IN VARCHAR2 := NULL
425 , p_c_attribute4 IN VARCHAR2 := NULL
426 , p_c_attribute5 IN VARCHAR2 := NULL
427 , p_c_attribute6 IN VARCHAR2 := NULL
428 , p_c_attribute7 IN VARCHAR2 := NULL
429 , p_c_attribute8 IN VARCHAR2 := NULL
430 , p_c_attribute9 IN VARCHAR2 := NULL
431 , p_c_attribute10 IN VARCHAR2 := NULL
432 , p_c_attribute11 IN VARCHAR2 := NULL
433 , p_c_attribute12 IN VARCHAR2 := NULL
434 , p_c_attribute13 IN VARCHAR2 := NULL
435 , p_c_attribute14 IN VARCHAR2 := NULL
436 , p_c_attribute15 IN VARCHAR2 := NULL
437 , p_c_attribute16 IN VARCHAR2 := NULL
438 , p_c_attribute17 IN VARCHAR2 := NULL
439 , p_c_attribute18 IN VARCHAR2 := NULL
440 , p_c_attribute19 IN VARCHAR2 := NULL
441 , p_c_attribute20 IN VARCHAR2 := NULL
442 , p_d_attribute1 IN DATE := NULL
443 , p_d_attribute2 IN DATE := NULL
444 , p_d_attribute3 IN DATE := NULL
445 , p_d_attribute4 IN DATE := NULL
446 , p_d_attribute5 IN DATE := NULL
447 , p_d_attribute6 IN DATE := NULL
448 , p_d_attribute7 IN DATE := NULL
449 , p_d_attribute8 IN DATE := NULL
450 , p_d_attribute9 IN DATE := NULL
451 , p_d_attribute10 IN DATE := NULL
452 , p_n_attribute1 IN NUMBER := NULL
453 , p_n_attribute2 IN NUMBER := NULL
454 , p_n_attribute3 IN NUMBER := NULL
455 , p_n_attribute4 IN NUMBER := NULL
456 , p_n_attribute5 IN NUMBER := NULL
457 , p_n_attribute6 IN NUMBER := NULL
458 , p_n_attribute7 IN NUMBER := NULL
459 , p_n_attribute8 IN NUMBER := NULL
460 , p_n_attribute9 IN NUMBER := NULL
461 , p_n_attribute10 IN NUMBER := NULL
462 , x_ser_trx_id OUT NOCOPY NUMBER
463 , x_proc_msg OUT NOCOPY VARCHAR2
464 , p_territory_code IN VARCHAR2 := NULL
465 , p_vendor_id IN VARCHAR2 := NULL
466 , p_secondary_qty IN NUMBER --Bug# 8204534,we shouldn't assign it to NULL
467 , p_secondary_uom IN VARCHAR2 --Bug# 8204534,we shouldn't assign it to NULL
468
469 --Bug No 3952081
470 --Add arguments to intake new OPM attributes of the lot
471 , p_parent_lot_number IN MTL_LOT_NUMBERS.PARENT_LOT_NUMBER%TYPE := NULL
472 , p_origination_type IN MTL_LOT_NUMBERS.ORIGINATION_TYPE%TYPE := NULL
473 , p_expriration_action_date IN MTL_LOT_NUMBERS.EXPIRATION_ACTION_DATE%TYPE := NULL
474 , p_expriration_action_code IN MTL_LOT_NUMBERS.EXPIRATION_ACTION_CODE%TYPE := NULL
475 , p_hold_date IN MTL_LOT_NUMBERS.HOLD_DATE%TYPE := NULL
476 )
477 RETURN NUMBER IS
478 -- Bug# 2032659 Beginning
479 l_description VARCHAR(250) := NULL;
480 l_vendor_name VARCHAR(250) := NULL;
481 l_supplier_lot_number VARCHAR(250) := NULL;
482 l_origination_date DATE := NULL;
483 l_date_code VARCHAR(250) := NULL;
484 l_grade_code VARCHAR(250) := NULL;
485 l_change_date DATE := NULL;
486 l_maturity_date DATE := NULL;
487 l_retest_date DATE := NULL;
488 l_age NUMBER := NULL;
489 l_item_size NUMBER := NULL;
490 l_color VARCHAR(250) := NULL;
491 l_volume NUMBER := NULL;
492 l_volume_uom VARCHAR(250) := NULL;
493 l_place_of_origin VARCHAR(250) := NULL;
494 l_best_by_date DATE := NULL;
495 l_length NUMBER := NULL;
496 l_length_uom VARCHAR(250) := NULL;
497 l_recycled_content NUMBER := NULL;
498 l_thickness NUMBER := NULL;
499 l_thickness_uom VARCHAR(250) := NULL;
500 l_width NUMBER := NULL;
501 l_width_uom VARCHAR(250) := NULL;
502 l_curl_wrinkle_fold VARCHAR(250) := NULL;
503 l_lot_attribute_category VARCHAR(250) := NULL;
504 l_c_attribute1 VARCHAR(250) := NULL;
505 l_c_attribute2 VARCHAR(250) := NULL;
506 l_c_attribute3 VARCHAR(250) := NULL;
507 l_c_attribute4 VARCHAR(250) := NULL;
508 l_c_attribute5 VARCHAR(250) := NULL;
509 l_c_attribute6 VARCHAR(250) := NULL;
510 l_c_attribute7 VARCHAR(250) := NULL;
511 l_c_attribute8 VARCHAR(250) := NULL;
512 l_c_attribute9 VARCHAR(250) := NULL;
513 l_c_attribute10 VARCHAR(250) := NULL;
514 l_c_attribute11 VARCHAR(250) := NULL;
515 l_c_attribute12 VARCHAR(250) := NULL;
516 l_c_attribute13 VARCHAR(250) := NULL;
517 l_c_attribute14 VARCHAR(250) := NULL;
518 l_c_attribute15 VARCHAR(250) := NULL;
519 l_c_attribute16 VARCHAR(250) := NULL;
520 l_c_attribute17 VARCHAR(250) := NULL;
521 l_c_attribute18 VARCHAR(250) := NULL;
522 l_c_attribute19 VARCHAR(250) := NULL;
523 l_c_attribute20 VARCHAR(250) := NULL;
524 l_d_attribute1 DATE := NULL;
525 l_d_attribute2 DATE := NULL;
526 l_d_attribute3 DATE := NULL;
527 l_d_attribute4 DATE := NULL;
528 l_d_attribute5 DATE := NULL;
529 l_d_attribute6 DATE := NULL;
530 l_d_attribute7 DATE := NULL;
531 l_d_attribute8 DATE := NULL;
532 l_d_attribute9 DATE := NULL;
533 l_d_attribute10 DATE := NULL;
534 l_n_attribute1 NUMBER := NULL;
535 l_n_attribute2 NUMBER := NULL;
536 l_n_attribute3 NUMBER := NULL;
537 l_n_attribute4 NUMBER := NULL;
538 l_n_attribute5 NUMBER := NULL;
539 l_n_attribute6 NUMBER := NULL;
540 l_n_attribute7 NUMBER := NULL;
541 l_n_attribute8 NUMBER := NULL;
542 l_n_attribute9 NUMBER := NULL;
543 l_n_attribute10 NUMBER := NULL;
544 l_vendor_id VARCHAR(250) := NULL;
545 l_territory_code VARCHAR(250) := NULL;
546 -- Bug# 2032659 End
547 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
548
549 --Bug No 3952081
550 --Add variables to hold existent OPM attributes of the lot
551 l_parent_lot_number MTL_LOT_NUMBERS.PARENT_LOT_NUMBER%TYPE := NULL;
552 l_origination_type MTL_LOT_NUMBERS.ORIGINATION_TYPE%TYPE := NULL;
553 l_expriration_action_date MTL_LOT_NUMBERS.EXPIRATION_ACTION_DATE%TYPE := NULL;
554 l_expriration_action_code MTL_LOT_NUMBERS.EXPIRATION_ACTION_CODE%TYPE := NULL;
555 l_hold_date MTL_LOT_NUMBERS.HOLD_DATE%TYPE := NULL;
556 BEGIN
557
558
559 -- Bug# 2032659
560 -- If Lot exists already, take the attributes from MTL_LOT_NUMBERS, else
561 -- take the input attribute values. This has to be done, because, in mobile
562 -- transactions, Lot Attribute page is not visited if the lot transaction
563 -- involves already exisiting lot and because of this MTLT will get populated without attribute
564 -- values. Later, MTL_TRANSACTION_LOT_NUMBERS will also get populated without attribute
565 -- values. Form 'Material Transactions' is built on a view with base table
566 -- MTL_TRANSACTION_LOT_NUMBERS. And for transactions that were done with existing
567 -- lot, lot attributes weren't visible in this form. This problem is solved
568 -- right in the beginning of transaction life, ie., by populating MTLT attribute values
569 -- from MTL_LOT_NUMBERS if Lot already exists.
570 BEGIN
571 SELECT description
572 , vendor_name
573 , supplier_lot_number
574 , origination_date
575 , date_code
576 , grade_code
577 , change_date
578 , maturity_date
579 , retest_date
580 , age
581 , item_size
582 , color
583 , volume
584 , volume_uom
585 , place_of_origin
586 , best_by_date
587 , LENGTH
588 , length_uom
589 , recycled_content
590 , thickness
591 , thickness_uom
592 , width
593 , width_uom
594 , curl_wrinkle_fold
595 , lot_attribute_category
596 , c_attribute1
597 , c_attribute2
598 , c_attribute3
599 , c_attribute4
600 , c_attribute5
601 , c_attribute6
602 , c_attribute7
603 , c_attribute8
604 , c_attribute9
605 , c_attribute10
606 , c_attribute11
607 , c_attribute12
608 , c_attribute13
609 , c_attribute14
610 , c_attribute15
611 , c_attribute16
612 , c_attribute17
613 , c_attribute18
614 , c_attribute19
615 , c_attribute20
616 , d_attribute1
617 , d_attribute2
618 , d_attribute3
619 , d_attribute4
620 , d_attribute5
621 , d_attribute6
622 , d_attribute7
623 , d_attribute8
624 , d_attribute9
625 , d_attribute10
626 , n_attribute1
627 , n_attribute2
628 , n_attribute3
629 , n_attribute4
630 , n_attribute5
631 , n_attribute6
632 , n_attribute7
633 , n_attribute8
634 , n_attribute9
635 , n_attribute10
636 , vendor_id
637 , territory_code
638 INTO l_description
639 , l_vendor_name
640 , l_supplier_lot_number
641 , l_origination_date
642 , l_date_code
643 , l_grade_code
644 , l_change_date
645 , l_maturity_date
646 , l_retest_date
647 , l_age
648 , l_item_size
649 , l_color
650 , l_volume
651 , l_volume_uom
652 , l_place_of_origin
653 , l_best_by_date
654 , l_length
655 , l_length_uom
656 , l_recycled_content
657 , l_thickness
658 , l_thickness_uom
659 , l_width
660 , l_width_uom
661 , l_curl_wrinkle_fold
662 , l_lot_attribute_category
663 , l_c_attribute1
664 , l_c_attribute2
665 , l_c_attribute3
666 , l_c_attribute4
667 , l_c_attribute5
668 , l_c_attribute6
669 , l_c_attribute7
670 , l_c_attribute8
671 , l_c_attribute9
672 , l_c_attribute10
673 , l_c_attribute11
674 , l_c_attribute12
675 , l_c_attribute13
676 , l_c_attribute14
677 , l_c_attribute15
678 , l_c_attribute16
679 , l_c_attribute17
680 , l_c_attribute18
681 , l_c_attribute19
682 , l_c_attribute20
683 , l_d_attribute1
684 , l_d_attribute2
685 , l_d_attribute3
686 , l_d_attribute4
687 , l_d_attribute5
688 , l_d_attribute6
689 , l_d_attribute7
690 , l_d_attribute8
691 , l_d_attribute9
692 , l_d_attribute10
693 , l_n_attribute1
694 , l_n_attribute2
695 , l_n_attribute3
696 , l_n_attribute4
697 , l_n_attribute5
698 , l_n_attribute6
699 , l_n_attribute7
700 , l_n_attribute8
701 , l_n_attribute9
702 , l_n_attribute10
703 , l_vendor_id
704 , l_territory_code
705 FROM mtl_lot_numbers mln, mtl_material_transactions_temp mmtt
706 WHERE mln.lot_number = LTRIM(RTRIM(p_lot_number))
707 AND mmtt.transaction_temp_id = p_trx_tmp_id
708 AND mln.organization_id = mmtt.organization_id
709 AND mln.inventory_item_id = mmtt.inventory_item_id;
710 EXCEPTION
711 WHEN NO_DATA_FOUND THEN
712 NULL;
713 END;
714
715 INSERT INTO mtl_transaction_lots_temp
716 (
717 transaction_temp_id
718 , last_update_date
719 , last_updated_by
720 , creation_date
721 , created_by
722 , transaction_quantity
723 , primary_quantity
724 , secondary_quantity
725 , secondary_unit_of_measure
726 , lot_number
727 , lot_expiration_date
728 , serial_transaction_temp_id
729 , description
730 , vendor_name
731 , supplier_lot_number
732 , origination_date
733 , date_code
734 , grade_code
735 , change_date
736 , maturity_date
737 , status_id
738 , retest_date
739 , age
740 , item_size
741 , color
742 , volume
743 , volume_uom
744 , place_of_origin
745 , best_by_date
746 , LENGTH
747 , length_uom
748 , recycled_content
749 , thickness
750 , thickness_uom
751 , width
752 , width_uom
753 , curl_wrinkle_fold
754 , lot_attribute_category
755 , c_attribute1
756 , c_attribute2
757 , c_attribute3
758 , c_attribute4
759 , c_attribute5
760 , c_attribute6
761 , c_attribute7
762 , c_attribute8
763 , c_attribute9
764 , c_attribute10
765 , c_attribute11
766 , c_attribute12
767 , c_attribute13
768 , c_attribute14
769 , c_attribute15
770 , c_attribute16
771 , c_attribute17
772 , c_attribute18
773 , c_attribute19
774 , c_attribute20
775 , d_attribute1
776 , d_attribute2
777 , d_attribute3
778 , d_attribute4
779 , d_attribute5
780 , d_attribute6
781 , d_attribute7
782 , d_attribute8
783 , d_attribute9
784 , d_attribute10
785 , n_attribute1
786 , n_attribute2
787 , n_attribute3
788 , n_attribute4
789 , n_attribute5
790 , n_attribute6
791 , n_attribute7
792 , n_attribute8
793 , n_attribute9
794 , n_attribute10
795 , vendor_id
796 , territory_code
797 --Bug No 3952081
798 --Insert OPM attributes
799 , PARENT_LOT_NUMBER
800 , ORIGINATION_TYPE
801 , EXPIRATION_ACTION_DATE
802 , EXPIRATION_ACTION_CODE
803 , HOLD_DATE
804 )
805 VALUES (
806 p_trx_tmp_id
807 , SYSDATE
808 , p_user_id
809 , SYSDATE
810 , p_user_id
811 , p_trx_qty
812 , p_pri_qty
813 , p_secondary_qty
814 , p_secondary_uom
815 , LTRIM(RTRIM(p_lot_number))
816 , p_exp_date
817 -- , x_ser_trx_id
818 , mtl_material_transactions_s.NEXTVAL
819 , NVL(p_description, l_description)
820 , NVL(p_vendor_name, l_vendor_name)
821 , NVL(p_supplier_lot_number, l_supplier_lot_number)
822 , NVL(p_origination_date, l_origination_date)
823 , NVL(p_date_code, l_date_code)
824 , NVL(p_grade_code, l_grade_code)
825 , NVL(p_change_date, l_change_date)
826 , NVL(p_maturity_date, l_maturity_date)
827 , p_status_id -- This is not attribute column
828 , NVL(p_retest_date, l_retest_date)
829 , NVL(p_age, l_age)
830 , NVL(p_item_size, l_item_size)
831 , NVL(p_color, l_color)
832 , NVL(p_volume, l_volume)
833 , NVL(p_volume_uom, l_volume_uom)
834 , NVL(p_place_of_origin, l_place_of_origin)
835 , NVL(p_best_by_date, l_best_by_date)
836 , NVL(p_length, l_length)
837 , NVL(p_length_uom, l_length_uom)
838 , NVL(p_recycled_content, l_recycled_content)
839 , NVL(p_thickness, l_thickness)
840 , NVL(p_thickness_uom, l_thickness_uom)
841 , NVL(p_width, l_width)
842 , NVL(p_width_uom, l_width_uom)
843 , NVL(p_curl_wrinkle_fold, l_curl_wrinkle_fold)
844 , NVL(p_lot_attribute_category, l_lot_attribute_category)
845 , NVL(p_c_attribute1, l_c_attribute1)
846 , NVL(p_c_attribute2, l_c_attribute2)
847 , NVL(p_c_attribute3, l_c_attribute3)
848 , NVL(p_c_attribute4, l_c_attribute4)
849 , NVL(p_c_attribute5, l_c_attribute5)
850 , NVL(p_c_attribute6, l_c_attribute6)
851 , NVL(p_c_attribute7, l_c_attribute7)
852 , NVL(p_c_attribute8, l_c_attribute8)
853 , NVL(p_c_attribute9, l_c_attribute9)
854 , NVL(p_c_attribute10, l_c_attribute10)
855 , NVL(p_c_attribute11, l_c_attribute11)
856 , NVL(p_c_attribute12, l_c_attribute12)
857 , NVL(p_c_attribute13, l_c_attribute13)
858 , NVL(p_c_attribute14, l_c_attribute14)
859 , NVL(p_c_attribute15, l_c_attribute15)
860 , NVL(p_c_attribute16, l_c_attribute16)
861 , NVL(p_c_attribute17, l_c_attribute17)
862 , NVL(p_c_attribute18, l_c_attribute18)
863 , NVL(p_c_attribute19, l_c_attribute19)
864 , NVL(p_c_attribute20, l_c_attribute20)
865 , NVL(p_d_attribute1, l_d_attribute1)
866 , NVL(p_d_attribute2, l_d_attribute2)
867 , NVL(p_d_attribute3, l_d_attribute3)
868 , NVL(p_d_attribute4, l_d_attribute4)
869 , NVL(p_d_attribute5, l_d_attribute5)
870 , NVL(p_d_attribute6, l_d_attribute6)
871 , NVL(p_d_attribute7, l_d_attribute7)
872 , NVL(p_d_attribute8, l_d_attribute8)
873 , NVL(p_d_attribute9, l_d_attribute9)
874 , NVL(p_d_attribute10, l_d_attribute10)
875 , NVL(p_n_attribute1, l_n_attribute1)
876 , NVL(p_n_attribute2, l_n_attribute2)
877 , NVL(p_n_attribute3, l_n_attribute3)
878 , NVL(p_n_attribute4, l_n_attribute4)
879 , NVL(p_n_attribute5, l_n_attribute5)
880 , NVL(p_n_attribute6, l_n_attribute6)
881 , NVL(p_n_attribute7, l_n_attribute7)
882 , NVL(p_n_attribute8, l_n_attribute8)
883 , NVL(p_n_attribute9, l_n_attribute9)
884 , NVL(p_n_attribute10, l_n_attribute10)
885 , NVL(p_vendor_id, l_vendor_id)
886 , NVL(p_territory_code, l_territory_code)
887 --Bug 3952081
888 --Use tha passed arguments directly to populate MTLT.
889 , p_parent_lot_number
890 , p_origination_type
891 , p_expriration_action_date
892 , p_expriration_action_code
893 , p_hold_date
894 ) RETURNING serial_transaction_temp_id INTO x_ser_trx_id;
895
896 -- Bug# 2032659 Change done till here
897 RETURN 0;
898 EXCEPTION
899 WHEN OTHERS THEN
900 x_proc_msg := SUBSTR(SQLERRM, 1, 200);
901 RETURN -1;
902 END;
903
904 --
905 -- Name: INSERT_SER_TRX
906 --
907 --
908 -- Functions: This API inserts a Serial Transaction record into
909 -- MTL_SERIAL_NUMBERS_TEMP. The argument p_trx_tmp_id is
910 -- used to couple this record with a transaction-line in
911 -- MTL_MATERIAL_TRANSACTIONS_TEMP
912 --
913 FUNCTION insert_ser_trx(
914 p_trx_tmp_id IN NUMBER
915 , p_user_id IN NUMBER
916 , p_fm_ser_num IN VARCHAR2
917 , p_to_ser_num IN VARCHAR2
918 , p_ven_ser_num IN VARCHAR2 := NULL
919 , p_vet_lot_num IN VARCHAR2 := NULL
920 , p_parent_ser_num IN VARCHAR2 := NULL
921 , p_end_item_unit_num IN VARCHAR2 := NULL
922 , p_serial_attribute_category IN VARCHAR2 := NULL
923 , p_orgination_date IN DATE := NULL
924 , p_c_attribute1 IN VARCHAR2 := NULL
925 , p_c_attribute2 IN VARCHAR2 := NULL
926 , p_c_attribute3 IN VARCHAR2 := NULL
927 , p_c_attribute4 IN VARCHAR2 := NULL
928 , p_c_attribute5 IN VARCHAR2 := NULL
929 , p_c_attribute6 IN VARCHAR2 := NULL
930 , p_c_attribute7 IN VARCHAR2 := NULL
931 , p_c_attribute8 IN VARCHAR2 := NULL
932 , p_c_attribute9 IN VARCHAR2 := NULL
933 , p_c_attribute10 IN VARCHAR2 := NULL
934 , p_c_attribute11 IN VARCHAR2 := NULL
935 , p_c_attribute12 IN VARCHAR2 := NULL
936 , p_c_attribute13 IN VARCHAR2 := NULL
937 , p_c_attribute14 IN VARCHAR2 := NULL
938 , p_c_attribute15 IN VARCHAR2 := NULL
939 , p_c_attribute16 IN VARCHAR2 := NULL
940 , p_c_attribute17 IN VARCHAR2 := NULL
941 , p_c_attribute18 IN VARCHAR2 := NULL
942 , p_c_attribute19 IN VARCHAR2 := NULL
943 , p_c_attribute20 IN VARCHAR2 := NULL
944 , p_d_attribute1 IN DATE := NULL
945 , p_d_attribute2 IN DATE := NULL
946 , p_d_attribute3 IN DATE := NULL
947 , p_d_attribute4 IN DATE := NULL
948 , p_d_attribute5 IN DATE := NULL
949 , p_d_attribute6 IN DATE := NULL
950 , p_d_attribute7 IN DATE := NULL
951 , p_d_attribute8 IN DATE := NULL
952 , p_d_attribute9 IN DATE := NULL
953 , p_d_attribute10 IN DATE := NULL
954 , p_n_attribute1 IN NUMBER := NULL
955 , p_n_attribute2 IN NUMBER := NULL
956 , p_n_attribute3 IN NUMBER := NULL
957 , p_n_attribute4 IN NUMBER := NULL
958 , p_n_attribute5 IN NUMBER := NULL
959 , p_n_attribute6 IN NUMBER := NULL
960 , p_n_attribute7 IN NUMBER := NULL
961 , p_n_attribute8 IN NUMBER := NULL
962 , p_n_attribute9 IN NUMBER := NULL
963 , p_n_attribute10 IN NUMBER := NULL
964 , p_status_id IN NUMBER := NULL
965 , p_territory_code IN VARCHAR2 := NULL
966 , p_time_since_new IN NUMBER := NULL
967 , p_cycles_since_new IN NUMBER := NULL
968 , p_time_since_overhaul IN NUMBER := NULL
969 , p_cycles_since_overhaul IN NUMBER := NULL
970 , p_time_since_repair IN NUMBER := NULL
971 , p_cycles_since_repair IN NUMBER := NULL
972 , p_time_since_visit IN NUMBER := NULL
973 , p_cycles_since_visit IN NUMBER := NULL
974 , p_time_since_mark IN NUMBER := NULL
975 , p_cycles_since_mark IN NUMBER := NULL
976 , p_number_of_repairs IN NUMBER := NULL
977 , p_validation_level IN NUMBER := NULL
978 , p_wms_installed IN VARCHAR2 := NULL
979 , p_quantity IN NUMBER := NULL -- Number of Serials between FROM and TO
980 , x_proc_msg OUT NOCOPY VARCHAR2
981 , p_attribute_category IN VARCHAR2 := NULL
982 , p_attribute1 IN VARCHAR2 := NULL
983 , p_attribute2 IN VARCHAR2 := NULL
984 , p_attribute3 IN VARCHAR2 := NULL
985 , p_attribute4 IN VARCHAR2 := NULL
986 , p_attribute5 IN VARCHAR2 := NULL
987 , p_attribute6 IN VARCHAR2 := NULL
988 , p_attribute7 IN VARCHAR2 := NULL
989 , p_attribute8 IN VARCHAR2 := NULL
990 , p_attribute9 IN VARCHAR2 := NULL
991 , p_attribute10 IN VARCHAR2 := NULL
992 , p_attribute11 IN VARCHAR2 := NULL
993 , p_attribute12 IN VARCHAR2 := NULL
994 , p_attribute13 IN VARCHAR2 := NULL
995 , p_attribute14 IN VARCHAR2 := NULL
996 , p_attribute15 IN VARCHAR2 := NULL
997 , p_dffupdatedflag IN VARCHAR2 := NULL
998 )
999 RETURN NUMBER IS
1000 l_serial_prefix NUMBER;
1001 l_real_serial_prefix VARCHAR2(30);
1002 l_serial_numeric_frm NUMBER;
1003 l_serial_numeric_to NUMBER;
1004 l_number_of_serial_numbers NUMBER;
1005 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
1006 l_lot_number VARCHAR2(80);
1007 l_transaction_temp_id NUMBER; -- transaction temp id of parent row in MMTT
1008 l_item_id NUMBER;
1009 l_org_id NUMBER;
1010 l_trx_header_id NUMBER;
1011 l_serial_trx_tmp_id NUMBER := NULL;
1012 l_err_code NUMBER;
1013 l_trx_type_id NUMBER;
1014 l_subinventory_code VARCHAR2(10);
1015 l_locator_id NUMBER;
1016 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
1017 BEGIN
1018 /*Fixed Bug#6758460 regardless of validation level
1019 txn temp id should be validated and serial number should be marked
1020 If customer does not pass validation leverl as full then this API
1021 does not derive txn temp id and pass null to API serial_check.inv_mark_serial
1022 This cause serial number to me unmarked and it makes ITS fails with error
1023 missing serial number error.
1024 */
1025
1026 /* Uncommented following IF condition for bug 7322274 */
1027 IF (p_validation_level = fnd_api.g_valid_level_full) THEN
1028 BEGIN
1029 SELECT mmtt.transaction_temp_id
1030 , mmtt.transaction_header_id
1031 , mmtt.inventory_item_id
1032 , mmtt.organization_id
1033 , mmtt.transaction_type_id
1034 , mmtt.subinventory_code
1035 , mmtt.locator_id
1036 INTO l_transaction_temp_id
1037 , l_trx_header_id
1038 , l_item_id
1039 , l_org_id
1040 , l_trx_type_id
1041 , l_subinventory_code
1042 , l_locator_id
1043 FROM mtl_material_transactions_temp mmtt
1044 WHERE mmtt.transaction_temp_id = p_trx_tmp_id;
1045 EXCEPTION
1046 WHEN NO_DATA_FOUND THEN
1047 -- both lot and serial controlled, then p_trx_tmp_id is coupled
1048 -- with a row in MTLT
1049 BEGIN
1050 SELECT mmtt.transaction_temp_id
1051 , mmtt.transaction_header_id
1052 , mmtt.inventory_item_id
1053 , mmtt.organization_id
1054 , mmtt.transaction_type_id
1055 , mmtt.subinventory_code
1056 , mmtt.locator_id
1057 , mtlt.lot_number
1058 INTO l_transaction_temp_id
1059 , l_trx_header_id
1060 , l_item_id
1061 , l_org_id
1062 , l_trx_type_id
1063 , l_subinventory_code
1064 , l_locator_id
1065 , l_lot_number
1066 FROM mtl_transaction_lots_temp mtlt, mtl_material_transactions_temp mmtt
1067 WHERE mtlt.serial_transaction_temp_id = p_trx_tmp_id
1068 AND mtlt.transaction_temp_id = mmtt.transaction_temp_id;
1069
1070 l_serial_trx_tmp_id := p_trx_tmp_id;
1071 EXCEPTION
1072 WHEN NO_DATA_FOUND THEN
1073 IF (l_debug = 1) THEN
1074 TRACE('INVALID p_trx_tmp_id', 'INVTRXUB', 9);
1075 END IF;
1076
1077 x_proc_msg := SUBSTR(SQLERRM, 1, 200);
1078 RETURN -1;
1079 END;
1080 END;
1081
1082 IF (l_debug = 1) THEN
1083 TRACE('CALCULATED TRX TEMP ID IS :' || l_transaction_temp_id, 'INVTRXUB', 9);
1084 TRACE('SERIAL TRX TEMP ID IS :' || l_serial_trx_tmp_id, 'INVTRXUB', 9);
1085 END IF;
1086
1087 SELECT COUNT(msn.serial_number)
1088 INTO l_number_of_serial_numbers
1089 FROM mtl_serial_numbers msn
1090 WHERE msn.inventory_item_id = l_item_id
1091 AND msn.serial_number BETWEEN p_fm_ser_num AND p_to_ser_num
1092 AND LENGTH(msn.serial_number) = LENGTH(p_fm_ser_num)
1093 AND current_status = 3
1094 AND msn.current_organization_id = l_org_id
1095 AND(msn.group_mark_id IS NULL OR msn.group_mark_id <= 0)
1096 AND msn.current_subinventory_code = l_subinventory_code
1097 /*Fixed for bug#6758460
1098 Condition modified to handle the null locator id
1099 if item is non locator controlled then this condition
1100 fails and cause group mark id not marked in MSN
1101 */
1102 /*AND msn.current_locator_id = l_locator_id*/
1103 AND nvl(msn.current_locator_id,-999999) = nvl(l_locator_id,-999999)
1104 AND(l_lot_number IS NULL OR msn.lot_number = l_lot_number)
1105 AND(
1106 inv_material_status_grp.is_status_applicable(
1107 p_wms_installed
1108 , NULL -- p_trx_status_enabled
1109 , l_trx_type_id
1110 , NULL -- p_lot_status_enabled
1111 , NULL -- p_serial_status_enabled
1112 , l_org_id
1113 , l_item_id
1114 , l_subinventory_code
1115 , l_locator_id
1116 , l_lot_number
1117 , msn.serial_number
1118 , 'A'
1119 ) = 'Y'
1120 );
1121
1122 IF (l_debug = 1) THEN
1123 TRACE('NUMBER OF VALID SERIAL NUMBERS FOUND IS :' || l_number_of_serial_numbers, 'INVTRXUB', 9);
1124 END IF;
1125
1126 IF (l_number_of_serial_numbers <> p_quantity) THEN
1127 IF (l_debug = 1) THEN
1128 TRACE('validation error: valid serial number quantity does not match', 'INVTRXUB', 9);
1129 END IF;
1130
1131 x_proc_msg := 'valid serial number quantity does not match';
1132 RETURN -1;
1133 END IF;
1134 END IF; /* Uncommented for bug 7322274 */
1135
1136 /* added as part of bug fix 2527211 */
1137 l_real_serial_prefix := RTRIM(p_fm_ser_num, '0123456789');
1138 l_serial_numeric_frm := TO_NUMBER(SUBSTR(p_fm_ser_num, NVL(LENGTH(l_real_serial_prefix), 0) + 1));
1139 l_serial_numeric_to := TO_NUMBER(SUBSTR(p_to_ser_num, NVL(LENGTH(l_real_serial_prefix), 0) + 1));
1140 l_serial_prefix := (l_serial_numeric_to - l_serial_numeric_frm) + 1;
1141
1142 IF (l_debug = 1) THEN
1143 TRACE('SERIAL_PREFIX IS :' || l_serial_prefix, 'INVTRXUB', 9);
1144 END IF;
1145
1146 /* end of bug fix 2527211 */
1147 INSERT INTO mtl_serial_numbers_temp
1148 (
1149 transaction_temp_id
1150 , last_update_date
1151 , last_updated_by
1152 , creation_date
1153 , created_by
1154 , last_update_login
1155 , vendor_serial_number
1156 , vendor_lot_number
1157 , fm_serial_number
1158 , to_serial_number
1159 , serial_prefix -- Bug#2527211
1160 , parent_serial_number
1161 , end_item_unit_number
1162 , serial_attribute_category
1163 , origination_date
1164 , c_attribute1
1165 , c_attribute2
1166 , c_attribute3
1167 , c_attribute4
1168 , c_attribute5
1169 , c_attribute6
1170 , c_attribute7
1171 , c_attribute8
1172 , c_attribute9
1173 , c_attribute10
1174 , c_attribute11
1175 , c_attribute12
1176 , c_attribute13
1177 , c_attribute14
1178 , c_attribute15
1179 , c_attribute16
1180 , c_attribute17
1181 , c_attribute18
1182 , c_attribute19
1183 , c_attribute20
1184 , d_attribute1
1185 , d_attribute2
1186 , d_attribute3
1187 , d_attribute4
1188 , d_attribute5
1189 , d_attribute6
1190 , d_attribute7
1191 , d_attribute8
1192 , d_attribute9
1193 , d_attribute10
1194 , n_attribute1
1195 , n_attribute2
1196 , n_attribute3
1197 , n_attribute4
1198 , n_attribute5
1199 , n_attribute6
1200 , n_attribute7
1201 , n_attribute8
1202 , n_attribute9
1203 , n_attribute10
1204 , status_id
1205 , territory_code
1206 , time_since_new
1207 , cycles_since_new
1208 , time_since_overhaul
1209 , cycles_since_overhaul
1210 , time_since_repair
1211 , cycles_since_repair
1212 , time_since_visit
1213 , cycles_since_visit
1214 , time_since_mark
1215 , cycles_since_mark
1216 , number_of_repairs
1217 , attribute_category
1218 , attribute1
1219 , attribute2
1220 , attribute3
1221 , attribute4
1222 , attribute5
1223 , attribute6
1224 , attribute7
1225 , attribute8
1226 , attribute9
1227 , attribute10
1228 , attribute11
1229 , attribute12
1230 , attribute13
1231 , attribute14
1232 , attribute15
1233 , dff_updated_flag
1234 )
1235 VALUES (
1236 p_trx_tmp_id
1237 , SYSDATE
1238 , p_user_id
1239 , SYSDATE
1240 , p_user_id
1241 , p_user_id
1242 , p_ven_ser_num
1243 , p_vet_lot_num
1244 , p_fm_ser_num
1245 , p_to_ser_num
1246 , NVL(l_serial_prefix, 1) -- Bug#2527211
1247 , p_parent_ser_num
1248 , p_end_item_unit_num
1249 , p_serial_attribute_category
1250 , p_orgination_date
1251 , p_c_attribute1
1252 , p_c_attribute2
1253 , p_c_attribute3
1254 , p_c_attribute4
1255 , p_c_attribute5
1256 , p_c_attribute6
1257 , p_c_attribute7
1258 , p_c_attribute8
1259 , p_c_attribute9
1260 , p_c_attribute10
1261 , p_c_attribute11
1262 , p_c_attribute12
1263 , p_c_attribute13
1264 , p_c_attribute14
1265 , p_c_attribute15
1266 , p_c_attribute16
1267 , p_c_attribute17
1268 , p_c_attribute18
1269 , p_c_attribute19
1270 , p_c_attribute20
1271 , p_d_attribute1
1272 , p_d_attribute2
1273 , p_d_attribute3
1274 , p_d_attribute4
1275 , p_d_attribute5
1276 , p_d_attribute6
1277 , p_d_attribute7
1278 , p_d_attribute8
1279 , p_d_attribute9
1280 , p_d_attribute10
1281 , p_n_attribute1
1282 , p_n_attribute2
1283 , p_n_attribute3
1284 , p_n_attribute4
1285 , p_n_attribute5
1286 , p_n_attribute6
1287 , p_n_attribute7
1288 , p_n_attribute8
1289 , p_n_attribute9
1290 , p_n_attribute10
1291 , p_status_id
1292 , p_territory_code
1293 , p_time_since_new
1294 , p_cycles_since_new
1295 , p_time_since_overhaul
1296 , p_cycles_since_overhaul
1297 , p_time_since_repair
1298 , p_cycles_since_repair
1299 , p_time_since_visit
1300 , p_cycles_since_visit
1301 , p_time_since_mark
1302 , p_cycles_since_mark
1303 , p_number_of_repairs
1304 , p_attribute_category
1305 , p_attribute1
1306 , p_attribute2
1307 , p_attribute3
1308 , p_attribute4
1309 , p_attribute5
1310 , p_attribute6
1311 , p_attribute7
1312 , p_attribute8
1313 , p_attribute9
1314 , p_attribute10
1315 , p_attribute11
1316 , p_attribute12
1317 , p_attribute13
1318 , p_attribute14
1319 , p_attribute15
1320 , p_dffupdatedflag
1321 );
1322
1323 -- Populate group_mark_id in MSN for the range of serial passed
1324 serial_check.inv_mark_serial(
1325 from_serial_number => p_fm_ser_num
1326 , to_serial_number => p_to_ser_num
1327 , item_id => l_item_id
1328 , org_id => l_org_id
1329 , hdr_id => l_trx_header_id
1330 , temp_id => l_transaction_temp_id
1331 , lot_temp_id => l_serial_trx_tmp_id
1332 , success => l_err_code
1333 );
1334
1335 IF (l_err_code >= 0) THEN
1336 RETURN 0;
1337 ELSE
1338 RETURN -1;
1339 END IF;
1340 EXCEPTION
1341 WHEN OTHERS THEN
1342 x_proc_msg := SUBSTR(SQLERRM, 1, 200);
1343 RETURN -1;
1344 END;
1345
1346 /**
1347 * Creates a New MMTT by copying column values from an Existing MMTT.
1348 */
1349 PROCEDURE copy_insert_line_trx(
1350 x_return_status OUT NOCOPY VARCHAR2
1351 , x_msg_data OUT NOCOPY VARCHAR2
1352 , x_msg_count OUT NOCOPY NUMBER
1353 , x_new_txn_temp_id OUT NOCOPY NUMBER
1354 , p_transaction_temp_id IN NUMBER
1355 , p_transaction_header_id IN NUMBER
1356 , p_inventory_item_id IN NUMBER
1357 , p_revision IN VARCHAR2
1358 , p_organization_id IN NUMBER
1359 , p_subinventory_code IN VARCHAR2
1360 , p_locator_id IN NUMBER
1361 , p_cost_group_id IN NUMBER
1362 , p_to_organization_id IN NUMBER
1363 , p_to_subinventory_code IN VARCHAR2
1364 , p_to_locator_id IN NUMBER
1365 , p_to_cost_group_id IN NUMBER
1366 , p_txn_qty IN NUMBER
1367 , p_primary_qty IN NUMBER
1368 , p_sec_txn_qty IN NUMBER --INVCONV KKILLAMS
1369 , p_transaction_uom IN VARCHAR2
1370 , p_lpn_id IN NUMBER
1371 , p_transfer_lpn_id IN NUMBER
1372 , p_content_lpn_id IN NUMBER
1373 , p_txn_type_id IN NUMBER
1374 , p_txn_action_id IN NUMBER
1375 , p_txn_source_type_id IN NUMBER
1376 , p_transaction_date IN DATE
1377 , p_transaction_source_id IN NUMBER
1378 , p_trx_source_line_id IN NUMBER
1379 , p_move_order_line_id IN NUMBER
1380 , p_reservation_id IN NUMBER
1381 , p_parent_line_id IN NUMBER
1382 , p_pick_slip_number IN NUMBER
1383 , p_wms_task_type IN NUMBER
1384 , p_user_id IN NUMBER
1385 , p_move_order_header_id IN NUMBER
1386 , p_serial_allocated_flag IN VARCHAR2
1387 , p_operation_plan_id IN NUMBER --lezhang
1388 , p_transaction_status IN NUMBER
1389 ) IS
1390 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'), 0);
1391 l_api_name VARCHAR2(30) := 'COPY_INSERT_LINE_TRX';
1392 l_acct_period_id NUMBER;
1393 l_open_past_period BOOLEAN;
1394 l_transaction_date DATE;
1395 l_organization_id NUMBER;
1396 l_inventory_item_id NUMBER;
1397 l_txn_qty NUMBER;
1398 l_primary_qty NUMBER;
1399 l_new_txn_temp_id NUMBER;
1400 l_primary_uom mtl_system_items.primary_uom_code%TYPE;
1401 l_transaction_uom mtl_system_items.primary_uom_code%TYPE;
1402 l_secondary_uom varchar2(3);
1403
1404 CURSOR c_mmtt_info IS
1405 SELECT mmtt.organization_id, mmtt.inventory_item_id, mmtt.transaction_uom
1406 FROM mtl_material_transactions_temp mmtt
1407 WHERE mmtt.transaction_temp_id = p_transaction_temp_id;
1408
1409 CURSOR c_item_info IS
1410 SELECT primary_uom_code
1411 FROM mtl_system_items msi
1412 WHERE msi.inventory_item_id = l_inventory_item_id
1413 AND msi.organization_id = l_organization_id;
1414 BEGIN
1415 x_return_status := fnd_api.g_ret_sts_success;
1416
1417 IF l_debug = 1 THEN
1418 inv_log_util.trace('Creating a new record in MMTT from TxnTempID = ' || p_transaction_temp_id, g_pkg_name || '.' || l_api_name, 5);
1419 END IF;
1420
1421 -- Transaction Temp ID has to be passed with a valid value.
1422 IF p_transaction_temp_id IS NULL OR p_transaction_temp_id = fnd_api.g_miss_num THEN
1423 IF l_debug = 1 THEN
1424 inv_log_util.trace('Error: Transaction Temp ID has to be passed', g_pkg_name || '.' || l_api_name, 3);
1425 END IF;
1426 fnd_message.set_name('INV', 'INV_MISSING_REQUIRED_PARAMETER');
1427 fnd_msg_pub.ADD;
1428 RAISE fnd_api.g_exc_error;
1429 END IF;
1430
1431 -- Not Nullable columns should not be passed as MissNum or MissChar or MissDate
1432 IF ( (p_inventory_item_id = fnd_api.g_miss_num OR p_organization_id = fnd_api.g_miss_num)
1433 OR (p_txn_qty = fnd_api.g_miss_num OR p_primary_qty = fnd_api.g_miss_num OR p_transaction_uom = fnd_api.g_miss_char)
1434 OR (p_txn_type_id = fnd_api.g_miss_num OR p_txn_action_id = fnd_api.g_miss_num OR p_txn_source_type_id = fnd_api.g_miss_num)
1435 OR (p_transaction_date = fnd_api.g_miss_date) )
1436 THEN
1437 IF l_debug = 1 THEN
1438 inv_log_util.trace('Error: ItemID, OrgID, PriQty, TxnQty, TxnUOM, TxnTypeID, TxnActionID, TxnSourceTypeID or TxnDate is invalid', g_pkg_name || '.' || l_api_name, 3);
1439 inv_log_util.trace('Error: The passed value will make the Not NULLABLE column NULL', g_pkg_name || '.' || l_api_name, 3);
1440 END IF;
1441 fnd_message.set_name('INV','INV_DATA_ERROR');
1442 fnd_message.set_token('ENTITY',l_api_name);
1443 fnd_msg_pub.ADD;
1444 RAISE fnd_api.g_exc_error;
1445 END IF;
1446
1447 -- Querying MMTT to get some required values.
1448 IF (p_organization_id IS NULL
1449 OR (((p_primary_qty IS NULL AND p_txn_qty IS NOT NULL)
1450 OR (p_primary_qty IS NOT NULL AND p_txn_qty IS NULL))
1451 AND (p_inventory_item_id IS NULL OR p_transaction_uom IS NULL)))
1452 THEN
1453 OPEN c_mmtt_info;
1454 FETCH c_mmtt_info INTO l_organization_id, l_inventory_item_id, l_transaction_uom;
1455 IF c_mmtt_info%NOTFOUND THEN
1456 CLOSE c_mmtt_info;
1457 IF l_debug = 1 THEN
1458 inv_log_util.trace('Error: No Record found for the given Transaction Temp ID', g_pkg_name || '.' || l_api_name, 3);
1459 END IF;
1460 RAISE fnd_api.g_exc_error;
1461 END IF;
1462 CLOSE c_mmtt_info;
1463 END IF;
1464
1465 l_organization_id := nvl(p_organization_id, l_organization_id);
1466 l_inventory_item_id := nvl(p_inventory_item_id, l_inventory_item_id);
1467 l_transaction_date := nvl(p_transaction_date, SYSDATE);
1468 l_transaction_uom := nvl(p_transaction_uom, l_transaction_uom);
1469 l_txn_qty := p_txn_qty;
1470 l_primary_qty := p_primary_qty;
1471
1472 -- Open Period Check
1473 invttmtx.tdatechk(l_organization_id, l_transaction_date, l_acct_period_id, l_open_past_period);
1474 IF l_acct_period_id = -1 OR l_acct_period_id = 0 THEN
1475 inv_log_util.trace('Error: Period is not open for the Organization', g_pkg_name || '.' || l_api_name, 3);
1476 fnd_message.set_name('INV', 'INV_NO_OPEN_PERIOD');
1477 fnd_msg_pub.ADD;
1478 RAISE fnd_api.g_exc_error;
1479 END IF;
1480
1481 -- Conversion between Primary Qty and Transaction Qty
1482 IF (p_txn_qty IS NOT NULL AND p_primary_qty IS NULL) OR (p_txn_qty IS NULL AND p_primary_qty IS NOT NULL) THEN
1483 OPEN c_item_info;
1484 FETCH c_item_info INTO l_primary_uom;
1485 IF c_item_info%NOTFOUND THEN
1486 CLOSE c_item_info;
1487 fnd_message.set_name('INV','INV_INVALID_ITEM_ORG');
1488 fnd_msg_pub.ADD;
1489 RAISE fnd_api.g_exc_error;
1490 END IF;
1491 CLOSE c_item_info;
1492
1493 IF l_primary_qty IS NULL THEN
1494 l_primary_qty := inv_convert.inv_um_convert(
1495 item_id => l_inventory_item_id
1496 , precision => NULL
1497 , from_quantity => l_txn_qty
1498 , from_unit => l_transaction_uom
1499 , to_unit => l_primary_uom
1500 , from_name => NULL
1501 , to_name => NULL
1502 );
1503 IF l_primary_qty <= -99999 THEN
1504 fnd_message.set_name('INV','INV_UOM_CONVERSION_ERROR');
1505 fnd_message.set_token('UOM1',l_transaction_uom);
1506 fnd_message.set_token('UOM2',l_primary_uom);
1507 fnd_message.set_token('MODULE',l_api_name);
1508 fnd_msg_pub.ADD;
1509 RAISE fnd_api.g_exc_error;
1510 END IF;
1511 ELSIF l_txn_qty IS NULL AND l_primary_qty IS NOT NULL THEN
1512 l_txn_qty := inv_convert.inv_um_convert(
1513 item_id => l_inventory_item_id
1514 , precision => NULL
1515 , from_quantity => l_primary_qty
1516 , from_unit => l_primary_uom
1517 , to_unit => l_transaction_uom
1518 , from_name => NULL
1519 , to_name => NULL
1520 );
1521 IF l_txn_qty <= -99999 THEN
1522 fnd_message.set_name('INV','INV_UOM_CONVERSION_ERROR');
1523 fnd_message.set_token('UOM1',l_primary_uom);
1524 fnd_message.set_token('UOM2',l_transaction_uom);
1525 fnd_message.set_token('MODULE',l_api_name);
1526 fnd_msg_pub.ADD;
1527 RAISE fnd_api.g_exc_error;
1528 END IF;
1529 END IF;
1530 END IF;
1531
1532 /*Bug#7716563,To fetch secondary_uom_code so as to update child MMTT with secondary_uom_code */
1533 SELECT secondary_uom_code
1534 INTO l_secondary_uom
1535 FROM mtl_system_items msi
1536 WHERE msi.inventory_item_id =
1537 (SELECT decode(l_inventory_item_id, NULL, inventory_item_id, l_inventory_item_id)
1538 FROM mtl_material_transactions_temp
1539 WHERE transaction_temp_id = p_transaction_temp_id)
1540 AND msi.organization_id = l_organization_id;
1541 SELECT mtl_material_transactions_s.NEXTVAL INTO x_new_txn_temp_id FROM DUAL;
1542
1543 INSERT INTO mtl_material_transactions_temp(
1544 transaction_header_id
1545 , transaction_temp_id
1546 , inventory_item_id
1547 , revision
1548 , organization_id
1549 , subinventory_code
1550 , locator_id
1551 , cost_group_id
1552 , transfer_organization
1553 , transfer_subinventory
1554 , transfer_to_location
1555 , transfer_cost_group_id
1556 , transaction_quantity
1557 , primary_quantity
1558 , transaction_uom
1559 , move_order_header_id
1560 , move_order_line_id
1561 , serial_allocated_flag
1562 , reservation_id
1563 , lpn_id
1564 , transfer_lpn_id
1565 , content_lpn_id
1566 , transaction_type_id
1567 , transaction_action_id
1568 , transaction_source_type_id
1569 , transaction_source_name
1570 , transaction_source_id
1571 , trx_source_line_id
1572 , trx_source_delivery_id
1573 , demand_source_header_id
1574 , demand_source_line
1575 , demand_source_delivery
1576 , transaction_cost
1577 , transaction_date
1578 , acct_period_id
1579 , distribution_account_id
1580 , parent_line_id
1581 , parent_transaction_temp_id
1582 , pick_slip_number
1583 , container_item_id
1584 , cartonization_id
1585 , standard_operation_id
1586 , operation_plan_id
1587 , wms_task_type
1588 , wms_task_status
1589 , task_priority
1590 , task_group_id
1591 , transaction_reference
1592 , requisition_line_id
1593 , requisition_distribution_id
1594 , reason_id
1595 , lot_number
1596 , lot_expiration_date
1597 , serial_number
1598 , receiving_document
1599 , demand_id
1600 , rcv_transaction_id
1601 , move_transaction_id
1602 , completion_transaction_id
1603 , schedule_id
1604 , repetitive_line_id
1605 , employee_code
1606 , primary_switch
1607 , schedule_update_code
1608 , setup_teardown_code
1609 , item_ordering
1610 , negative_req_flag
1611 , operation_seq_num
1612 , picking_line_id
1613 , physical_adjustment_id
1614 , cycle_count_id
1615 , rma_line_id
1616 , customer_ship_id
1617 , currency_code
1618 , currency_conversion_rate
1619 , currency_conversion_type
1620 , currency_conversion_date
1621 , ussgl_transaction_code
1622 , vendor_lot_number
1623 , encumbrance_account
1624 , encumbrance_amount
1625 , ship_to_location
1626 , shipment_number
1627 , transfer_cost
1628 , transportation_cost
1629 , transportation_account
1630 , freight_code
1631 , containers
1632 , waybill_airbill
1633 , expected_arrival_date
1634 , new_average_cost
1635 , value_change
1636 , percentage_change
1637 , material_allocation_temp_id
1638 , allowed_units_lookup_code
1639 , wip_entity_type
1640 , department_id
1641 , department_code
1642 , wip_supply_type
1643 , supply_subinventory
1644 , supply_locator_id
1645 , valid_subinventory_flag
1646 , valid_locator_flag
1647 , wip_commit_flag
1648 , shippable_flag
1649 , posting_flag
1650 , required_flag
1651 , process_flag
1652 , item_segments
1653 , item_description
1654 , item_trx_enabled_flag
1655 , item_location_control_code
1656 , item_restrict_subinv_code
1657 , item_restrict_locators_code
1658 , item_revision_qty_control_code
1659 , item_primary_uom_code
1660 , item_uom_class
1661 , item_shelf_life_code
1662 , item_shelf_life_days
1663 , item_lot_control_code
1664 , item_serial_control_code
1665 , item_inventory_asset_flag
1666 , error_code
1667 , error_explanation
1668 , attribute_category
1669 , attribute1
1670 , attribute2
1671 , attribute3
1672 , attribute4
1673 , attribute5
1674 , attribute6
1675 , attribute7
1676 , attribute8
1677 , attribute9
1678 , attribute10
1679 , attribute11
1680 , attribute12
1681 , attribute13
1682 , attribute14
1683 , attribute15
1684 , movement_id
1685 , reservation_quantity
1686 , shipped_quantity
1687 , transaction_line_number
1688 , task_id
1689 , to_task_id
1690 , source_task_id
1691 , project_id
1692 , source_project_id
1693 , pa_expenditure_org_id
1694 , to_project_id
1695 , expenditure_type
1696 , final_completion_flag
1697 , transfer_percentage
1698 , transaction_sequence_id
1699 , material_account
1700 , material_overhead_account
1701 , resource_account
1702 , outside_processing_account
1703 , overhead_account
1704 , flow_schedule
1705 , demand_class
1706 , qa_collection_id
1707 , kanban_card_id
1708 , overcompletion_transaction_id
1709 , overcompletion_primary_qty
1710 , overcompletion_transaction_qty
1711 , end_item_unit_number
1712 , scheduled_payback_date
1713 , line_type_code
1714 , put_away_strategy_id
1715 , put_away_rule_id
1716 , pick_strategy_id
1717 , pick_rule_id
1718 , common_bom_seq_id
1719 , common_routing_seq_id
1720 , cost_type_id
1721 , org_cost_group_id
1722 , source_code
1723 , source_line_id
1724 , transaction_mode
1725 , lock_flag
1726 , transaction_status
1727 , last_update_date
1728 , last_updated_by
1729 , creation_date
1730 , created_by
1731 , last_update_login
1732 , request_id
1733 , program_application_id
1734 , program_id
1735 , program_update_date
1736 , secondary_transaction_quantity --INVCONV kkillams
1737 , secondary_uom_code -- Bug#7716563
1738 )
1739 SELECT decode(p_transaction_header_id, fnd_api.g_miss_num, NULL, NULL, transaction_header_id, p_transaction_header_id)
1740 , x_new_txn_temp_id
1741 , decode(l_inventory_item_id, NULL, inventory_item_id, l_inventory_item_id)
1742 , decode(p_revision, fnd_api.g_miss_char, NULL, NULL, revision, p_revision)
1743 , decode(l_organization_id, NULL, organization_id, l_organization_id)
1744 , decode(p_subinventory_code, fnd_api.g_miss_char, NULL, NULL, subinventory_code, p_subinventory_code)
1745 , decode(p_locator_id, fnd_api.g_miss_num, NULL, NULL, locator_id, p_locator_id)
1746 , decode(p_cost_group_id, fnd_api.g_miss_num, NULL, NULL, cost_group_id, p_cost_group_id)
1747 , decode(p_to_organization_id, fnd_api.g_miss_num, NULL, NULL, transfer_organization, p_to_organization_id)
1748 , decode(p_to_subinventory_code, fnd_api.g_miss_char, NULL, NULL, transfer_subinventory, p_to_subinventory_code)
1749 , decode(p_to_locator_id, fnd_api.g_miss_num, NULL, NULL, transfer_to_location, p_to_locator_id)
1750 , decode(p_to_cost_group_id, fnd_api.g_miss_num, NULL, NULL, transfer_cost_group_id, p_to_cost_group_id)
1751 , decode(l_txn_qty, NULL, transaction_quantity, l_txn_qty)
1752 , decode(l_primary_qty, NULL, primary_quantity, l_primary_qty)
1753 , decode(l_transaction_uom, NULL, transaction_uom, l_transaction_uom)
1754 , decode(p_move_order_header_id, fnd_api.g_miss_num, NULL, NULL, move_order_header_id, p_move_order_header_id)
1755 , decode(p_move_order_line_id, fnd_api.g_miss_num, NULL, NULL, move_order_line_id, p_move_order_line_id)
1756 , decode(p_serial_allocated_flag, fnd_api.g_miss_char, NULL, NULL, serial_allocated_flag, p_serial_allocated_flag)
1757 , decode(p_reservation_id, fnd_api.g_miss_num, NULL, NULL, reservation_id, p_reservation_id)
1758 , decode(p_lpn_id, fnd_api.g_miss_num, NULL, NULL, lpn_id, p_lpn_id)
1759 , decode(p_transfer_lpn_id, fnd_api.g_miss_num, NULL, NULL, transfer_lpn_id, p_transfer_lpn_id)
1760 , decode(p_content_lpn_id, fnd_api.g_miss_num, NULL, NULL, content_lpn_id, p_content_lpn_id)
1761 , decode(p_txn_type_id, NULL, transaction_type_id, p_txn_type_id)
1762 , decode(p_txn_action_id, NULL, transaction_action_id, p_txn_action_id)
1763 , decode(p_txn_source_type_id, NULL, transaction_source_type_id, p_txn_source_type_id)
1764 , transaction_source_name
1765 , decode(p_transaction_source_id, fnd_api.g_miss_num, NULL, NULL, transaction_source_id, p_transaction_source_id)
1766 , decode(p_trx_source_line_id, fnd_api.g_miss_num, NULL, NULL, trx_source_line_id, p_trx_source_line_id)
1767 , trx_source_delivery_id
1768 , decode(p_transaction_source_id, fnd_api.g_miss_num, NULL, NULL, demand_source_header_id, p_transaction_source_id)
1769 , decode(p_trx_source_line_id, fnd_api.g_miss_num, NULL, NULL, demand_source_line, p_trx_source_line_id)
1770 , demand_source_delivery
1771 , transaction_cost
1772 , l_transaction_date
1773 , l_acct_period_id
1774 , distribution_account_id
1775 , decode(p_parent_line_id, fnd_api.g_miss_num, NULL, NULL, parent_line_id, p_parent_line_id)
1776 , parent_transaction_temp_id
1777 , decode(p_pick_slip_number, fnd_api.g_miss_num, NULL, NULL, pick_slip_number, p_pick_slip_number)
1778 , container_item_id
1779 , cartonization_id
1780 , standard_operation_id
1781 , decode(p_operation_plan_id, fnd_api.g_miss_num, NULL, NULL, operation_plan_id, p_operation_plan_id) --lezhang
1782 , decode(p_wms_task_type, fnd_api.g_miss_num, NULL, NULL, wms_task_type, p_wms_task_type)
1783 , wms_task_status
1784 , task_priority
1785 , task_group_id
1786 , transaction_reference
1787 , requisition_line_id
1788 , requisition_distribution_id
1789 , reason_id
1790 , lot_number
1791 , lot_expiration_date
1792 , serial_number
1793 , receiving_document
1794 , demand_id
1795 , rcv_transaction_id
1796 , move_transaction_id
1797 , completion_transaction_id
1798 , schedule_id
1799 , repetitive_line_id
1800 , employee_code
1801 , primary_switch
1802 , schedule_update_code
1803 , setup_teardown_code
1804 , item_ordering
1805 , negative_req_flag
1806 , operation_seq_num
1807 , picking_line_id
1808 , physical_adjustment_id
1809 , cycle_count_id
1810 , rma_line_id
1811 , customer_ship_id
1812 , currency_code
1813 , currency_conversion_rate
1814 , currency_conversion_type
1815 , currency_conversion_date
1816 , ussgl_transaction_code
1817 , vendor_lot_number
1818 , encumbrance_account
1819 , encumbrance_amount
1820 , ship_to_location
1821 , shipment_number
1822 , transfer_cost
1823 , transportation_cost
1824 , transportation_account
1825 , freight_code
1826 , containers
1827 , waybill_airbill
1828 , expected_arrival_date
1829 , new_average_cost
1830 , value_change
1831 , percentage_change
1832 , material_allocation_temp_id
1833 , allowed_units_lookup_code
1834 , wip_entity_type
1835 , department_id
1836 , department_code
1837 , wip_supply_type
1838 , supply_subinventory
1839 , supply_locator_id
1840 , valid_subinventory_flag
1841 , valid_locator_flag
1842 , wip_commit_flag
1843 , shippable_flag
1844 , posting_flag
1845 , required_flag
1846 , process_flag
1847 , item_segments
1848 , item_description
1849 , item_trx_enabled_flag
1850 , item_location_control_code
1851 , item_restrict_subinv_code
1852 , item_restrict_locators_code
1853 , item_revision_qty_control_code
1854 , item_primary_uom_code
1855 , item_uom_class
1856 , item_shelf_life_code
1857 , item_shelf_life_days
1858 , item_lot_control_code
1859 , item_serial_control_code
1860 , item_inventory_asset_flag
1861 , error_code
1862 , error_explanation
1863 , attribute_category
1864 , attribute1
1865 , attribute2
1866 , attribute3
1867 , attribute4
1868 , attribute5
1869 , attribute6
1870 , attribute7
1871 , attribute8
1872 , attribute9
1873 , attribute10
1874 , attribute11
1875 , attribute12
1876 , attribute13
1877 , attribute14
1878 , attribute15
1879 , movement_id
1880 , reservation_quantity
1881 , shipped_quantity
1882 , transaction_line_number
1883 , task_id
1884 , to_task_id
1885 , source_task_id
1886 , project_id
1887 , source_project_id
1888 , pa_expenditure_org_id
1889 , to_project_id
1890 , expenditure_type
1891 , final_completion_flag
1892 , transfer_percentage
1893 , transaction_sequence_id
1894 , material_account
1895 , material_overhead_account
1896 , resource_account
1897 , outside_processing_account
1898 , overhead_account
1899 , flow_schedule
1900 , demand_class
1901 , qa_collection_id
1902 , kanban_card_id
1903 , overcompletion_transaction_id
1904 , overcompletion_primary_qty
1905 , overcompletion_transaction_qty
1906 , end_item_unit_number
1907 , scheduled_payback_date
1908 , line_type_code
1909 , put_away_strategy_id
1910 , put_away_rule_id
1911 , pick_strategy_id
1912 , pick_rule_id
1913 , common_bom_seq_id
1914 , common_routing_seq_id
1915 , cost_type_id
1916 , org_cost_group_id
1917 , source_code
1918 , source_line_id
1919 , transaction_mode
1920 , lock_flag
1921 , NVL(p_transaction_status, transaction_status)
1922 , SYSDATE
1923 , nvl(p_user_id, fnd_global.user_id)
1924 , SYSDATE
1925 , nvl(p_user_id, fnd_global.user_id)
1926 , last_update_login
1927 , request_id
1928 , program_application_id
1929 , program_id
1930 , program_update_date
1931 , decode(p_sec_txn_qty, fnd_api.g_miss_num, NULL, NULL, secondary_transaction_quantity, p_sec_txn_qty) --INVCONV KKILLAMS
1932 ,decode(l_secondary_uom, NULL, secondary_uom_code, l_secondary_uom) --Bug#7716563
1933 FROM mtl_material_transactions_temp
1934 WHERE transaction_temp_id = p_transaction_temp_id;
1935
1936 IF l_debug = 1 THEN
1937 inv_log_util.trace('Inserted a new record into MMTT with TxnTempID = ' || x_new_txn_temp_id, g_pkg_name || '.' || l_api_name, 5);
1938 END IF;
1939 EXCEPTION
1940 WHEN fnd_api.g_exc_error THEN
1941 x_return_status := fnd_api.g_ret_sts_error;
1942 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1943 WHEN fnd_api.g_exc_unexpected_error THEN
1944 x_return_status := fnd_api.g_ret_sts_unexp_error;
1945 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1946 WHEN OTHERS THEN
1947 x_return_status := fnd_api.g_ret_sts_unexp_error;
1948 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1949 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1950 END IF;
1951 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1952 END copy_insert_line_trx;
1953
1954
1955 -- Name: DELETE_SER_TRX
1956 --
1957 -- Functions: This API deletes all records with the input transaction
1958 -- temp id from MTL_SERIAL_NUMBERS_TEMP.
1959 -- It also unmarks these serial numbers in MSN.
1960 FUNCTION delete_ser_trx(
1961 p_trx_header_id IN NUMBER
1962 , p_trx_tmp_id IN NUMBER
1963 , p_serial_trx_tmp_id IN NUMBER
1964 , p_serial_control_code IN NUMBER
1965 , p_user_id IN NUMBER
1966 , x_proc_msg OUT NOCOPY VARCHAR2
1967 )
1968 RETURN NUMBER IS
1969 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
1970 BEGIN
1971 DELETE FROM mtl_serial_numbers_temp
1972 WHERE transaction_temp_id = NVL(p_serial_trx_tmp_id, p_trx_tmp_id);
1973
1974 serial_check.inv_unmark_serial(
1975 from_serial_number => NULL
1976 , to_serial_number => NULL
1977 , serial_code => p_serial_control_code
1978 , hdr_id => p_trx_header_id
1979 , temp_id => p_trx_tmp_id
1980 , lot_temp_id => p_serial_trx_tmp_id
1981 );
1982 RETURN 0;
1983 EXCEPTION
1984 WHEN OTHERS THEN
1985 x_proc_msg := SUBSTR(SQLERRM, 1, 200);
1986 RETURN -1;
1987 END;
1988
1989 /*
1990 * Procedure: DELETE_TRANSACTION
1991 * 1. Deletes a MMTT record given the Transaction Temp ID
1992 * 2. If it is a Lot Controlled Item, cascades the Delete till MTLT
1993 * 3. If it is a Serial Controlled Item , cascades the Delete till MSNT. Unmarks the Serial.
1994 * 4. Cascades the delete till WDT. Care should be taked to call the API if the Task is Loaded.
1995 */
1996 PROCEDURE delete_transaction(
1997 x_return_status OUT NOCOPY VARCHAR2
1998 , x_msg_data OUT NOCOPY VARCHAR2
1999 , x_msg_count OUT NOCOPY NUMBER
2000 , p_transaction_temp_id NUMBER
2001 , p_update_parent BOOLEAN
2002 ) IS
2003 l_inventory_item_id NUMBER;
2004 l_lot_control_code NUMBER;
2005 l_serial_control_code NUMBER;
2006 l_fm_serial_number VARCHAR2(30);
2007 l_to_serial_number VARCHAR2(30);
2008 l_unmarked_count NUMBER := 0;
2009 l_parent_line_id NUMBER;
2010 l_child_txn_qty NUMBER;
2011 l_child_pri_qty NUMBER;
2012 l_child_uom VARCHAR2(3);
2013 l_txn_hdr_id NUMBER ; --Bug#6211912
2014 l_organization_id NUMBER; -- 14259665 Performance Issue
2015
2016 CURSOR c_item_info IS
2017 SELECT msi.inventory_item_id, msi.lot_control_code, msi.serial_number_control_code, mmtt.parent_line_id
2018 ,mmtt.transaction_header_id --Bug#6211912
2019 ,mmtt.organization_id -- 14259665 Performance Issue
2020 FROM mtl_system_items msi, mtl_material_transactions_temp mmtt
2021 WHERE mmtt.transaction_temp_id = p_transaction_temp_id
2022 AND msi.inventory_item_id = mmtt.inventory_item_id
2023 AND msi.organization_id = mmtt.organization_id;
2024
2025 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
2026 BEGIN
2027 x_return_status := fnd_api.g_ret_sts_success;
2028
2029 IF l_debug = 1 THEN
2030 TRACE('Cleaning up MMTT, MTLT and MSNT for Txn Temp ID = ' || p_transaction_temp_id, 'INV_TRX_UTIL_PUB.DELETE_TRANSACTION');
2031 END IF;
2032
2033 OPEN c_item_info;
2034 FETCH c_item_info INTO l_inventory_item_id, l_lot_control_code, l_serial_control_code, l_parent_line_id,l_txn_hdr_id, l_organization_id;-- 14259665 Performance Issue
2035 CLOSE c_item_info;
2036
2037 IF l_debug = 1 THEN
2038 TRACE('Item ID = ' || l_inventory_item_id, 'INV_TRX_UTIL_PUB.DELETE_TRANSACTION');
2039 TRACE('Lot Control = ' || l_lot_control_code, 'INV_TRX_UTIL_PUB.DELETE_TRANSACTION');
2040 TRACE('Serial Control = ' || l_serial_control_code, 'INV_TRX_UTIL_PUB.DELETE_TRANSACTION');
2041 TRACE('Parent Line ID = ' || l_parent_line_id, 'INV_TRX_UTIL_PUB.DELETE_TRANSACTION');
2042 END IF;
2043
2044
2045 IF l_parent_line_id IS NOT NULL AND p_update_parent THEN
2046 IF l_debug = 1 THEN
2047 TRACE('Child Record... Updating the Parent: TxnTempID = ' || l_parent_line_id, 'INV_TRX_UTIL_PUB.DELETE_TRANSACTION');
2048 END IF;
2049
2050 update_parent_mmtt(
2051 x_return_status => x_return_status
2052 , p_parent_line_id => l_parent_line_id
2053 , p_child_line_id => p_transaction_temp_id
2054 , p_lot_control_code => l_lot_control_code
2055 , p_serial_control_code => l_serial_control_code
2056 );
2057
2058 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2059 IF l_debug = 1 THEN
2060 TRACE('Error occurred while updating the Parent Record', 'INV_TRX_UTIL_PUB.DELETE_TRANSACTION');
2061 END IF;
2062
2063 RAISE fnd_api.g_exc_error;
2064 END IF;
2065 END IF;
2066
2067 -- Unmarking and Deleting all the Serials associated with the Transaction
2068 IF l_serial_control_code IN(2, 5) THEN --If serial controlled
2069 IF l_lot_control_code = 2 THEN -- If lot controlled also
2070
2071 UPDATE mtl_serial_numbers
2072 SET group_mark_id = NULL,
2073 line_mark_id = NULL,
2074 lot_line_mark_id = NULL
2075 WHERE inventory_item_id = l_inventory_item_id -- 14259665 Performance Issue
2076 AND current_organization_id = l_organization_id -- 14259665 Performance Issue
2077 AND group_mark_id IN (SELECT serial_transaction_temp_id
2078 FROM mtl_transaction_lots_temp
2079 WHERE transaction_temp_id = p_transaction_temp_id
2080 UNION ALL
2081 SELECT l_txn_hdr_id
2082 FROM dual --Bug#6157372 -- 12419592
2083 UNION ALL
2084 SELECT p_transaction_temp_id
2085 FROM dual );-- 14259665 Added to select temp id since for unallocated lots the MSNTs are inserted with
2086 -- mmtt transaction_temp_id and group_mark_id is stamped as transaction_temp_id.
2087 l_unmarked_count := SQL%ROWCOUNT;
2088
2089 DELETE mtl_serial_numbers_temp
2090 WHERE transaction_temp_id IN (SELECT serial_transaction_temp_id
2091 FROM mtl_transaction_lots_temp
2092 WHERE transaction_temp_id = p_transaction_temp_id
2093 UNION ALL
2094 SELECT p_transaction_temp_id
2095 FROM dual );-- 14259665 Added to select temp id since for unallocated lots the MSNTs are inserted with
2096 -- mmtt transaction_temp_id. The MTLT is inserted later and then the serial_transaction_temp_id
2097 -- is back updated.
2098
2099 ELSE -- only serial controlled but not lot controlled.
2100
2101 UPDATE mtl_serial_numbers
2102 SET group_mark_id = NULL, line_mark_id = NULL, lot_line_mark_id = NULL
2103 WHERE group_mark_id in (p_transaction_temp_id,l_txn_hdr_id); --Bug#12419592
2104 --Bug#6157372
2105
2106 l_unmarked_count := SQL%ROWCOUNT;
2107
2108 DELETE mtl_serial_numbers_temp
2109 WHERE transaction_temp_id = p_transaction_temp_id;
2110
2111 END IF;
2112
2113 IF l_debug = 1 THEN
2114 TRACE('Serials unmarked in MSN = ' || l_unmarked_count, 'INV_TRX_UTIL_PUB.DELETE_TRANSACTION');
2115 TRACE('Records deleted in MSNT = ' || SQL%ROWCOUNT, 'INV_TRX_UTIL_PUB.DELETE_TRANSACTION');
2116 END IF;
2117 END IF;
2118
2119 -- Deleting all the Lots associated with the Transaction
2120 IF l_lot_control_code = 2 THEN
2121 DELETE mtl_transaction_lots_temp
2122 WHERE transaction_temp_id = p_transaction_temp_id;
2123
2124 IF l_debug = 1 THEN
2125 TRACE('Records deleted in MTLT = ' || SQL%ROWCOUNT, 'INV_TRX_UTIL_PUB.DELETE_TRANSACTION');
2126 END IF;
2127 END IF;
2128
2129 -- Deleting the Task
2130 DELETE wms_dispatched_tasks
2131 WHERE transaction_temp_id = p_transaction_temp_id;
2132
2133 IF l_debug = 1 THEN
2134 TRACE('Records deleted in WDT = ' || SQL%ROWCOUNT, 'INV_TRX_UTIL_PUB.DELETE_TRANSACTION');
2135 END IF;
2136
2137 -- Deleting the Transaction
2138 DELETE mtl_material_transactions_temp
2139 WHERE transaction_temp_id = p_transaction_temp_id;
2140
2141 IF l_debug = 1 THEN
2142 TRACE('Records deleted in MMTT = ' || SQL%ROWCOUNT, 'INV_TRX_UTIL_PUB.DELETE_TRANSACTION');
2143 END IF;
2144 EXCEPTION
2145 WHEN OTHERS THEN
2146 x_return_status := fnd_api.g_ret_sts_error;
2147 TRACE('Exception Occurred = ' || SQLERRM, 'INV_TRX_UTIL_PUB.DELETE_TRANSACTION');
2148 END delete_transaction;
2149
2150 PROCEDURE delete_lot_ser_trx(
2151 p_trx_tmp_id IN NUMBER
2152 , p_org_id IN NUMBER
2153 , p_item_id IN NUMBER
2154 , p_lotctrl IN NUMBER
2155 , p_serctrl IN NUMBER
2156 , x_return_status OUT NOCOPY VARCHAR2
2157 ) IS
2158 CURSOR c_serial(l_txn_tmp_id IN NUMBER) IS
2159 SELECT fm_serial_number, NVL(to_serial_number, fm_serial_number) to_serial_number
2160 FROM mtl_serial_numbers_temp
2161 WHERE transaction_temp_id = l_txn_tmp_id;
2162
2163 CURSOR c_lot(l_txn_tmp_id IN NUMBER) IS
2164 SELECT serial_transaction_temp_id
2165 FROM mtl_transaction_lots_temp
2166 WHERE transaction_temp_id = l_txn_tmp_id;
2167 BEGIN
2168 x_return_status := fnd_api.g_ret_sts_success;
2169 TRACE('parameters passed to delete_lot_ser_trx', 'INVTRXUB', 9);
2170 TRACE('p_trx_tmp_id = '|| p_trx_tmp_id|| 'p_org_id = '|| p_org_id|| 'p_item_id = '|| p_item_id|| 'p_lotctrl = '|| p_lotctrl|| 'p_serctrl = '|| p_serctrl, 'INVTRXUB', 9);
2171
2172 IF (p_trx_tmp_id IS NULL) OR(p_org_id IS NULL) OR(p_item_id IS NULL) OR(p_lotctrl IS NULL) OR(p_serctrl IS NULL) THEN
2173 TRACE('Parameter passed is null...', 'INVTRXUB', 9);
2174 RAISE fnd_api.g_exc_error;
2175 END IF;
2176
2177 IF ((p_serctrl <> 1) AND(p_lotctrl <> 2)) THEN
2178 --Item is only serial controlled
2179 FOR c_serial_rec IN c_serial(p_trx_tmp_id) LOOP
2180 --Now Call inv_unmark_serial
2181 serial_check.inv_unmark_serial(
2182 from_serial_number => c_serial_rec.fm_serial_number
2183 , to_serial_number => c_serial_rec.to_serial_number
2184 , serial_code => p_serctrl
2185 , hdr_id => NULL
2186 , temp_id => NULL
2187 , lot_temp_id => NULL
2188 , p_inventory_item_id => p_item_id
2189 );
2190 END LOOP;
2191 DELETE FROM mtl_serial_numbers_temp msnt WHERE msnt.transaction_temp_id = p_trx_tmp_id;
2192 ELSIF((p_serctrl <> 1) AND(p_lotctrl = 2)) THEN
2193 --Item is lot controlled and serial controlled
2194
2195 FOR c_lot_rec IN c_lot(p_trx_tmp_id) LOOP
2196 --Now get the serial txn temp ids
2197
2198 FOR c_serial_rec IN c_serial(c_lot_rec.serial_transaction_temp_id) LOOP
2199 --Now call inv_unmark_serial
2200 serial_check.inv_unmark_serial(
2201 from_serial_number => c_serial_rec.fm_serial_number
2202 , to_serial_number => c_serial_rec.to_serial_number
2203 , serial_code => p_serctrl
2204 , hdr_id => NULL
2205 , temp_id => NULL
2206 , lot_temp_id => NULL
2207 , p_inventory_item_id => p_item_id
2208 );
2209 END LOOP;
2210 END LOOP;
2211
2212 --Delete records from MSNT and MTLT
2213 DELETE FROM mtl_serial_numbers_temp msnt
2214 WHERE msnt.transaction_temp_id IN(
2215 SELECT mtlt.serial_transaction_temp_id
2216 FROM mtl_transaction_lots_temp mtlt
2217 WHERE mtlt.serial_transaction_temp_id = msnt.transaction_temp_id
2218 AND mtlt.transaction_temp_id = p_trx_tmp_id);
2219
2220 DELETE FROM mtl_transaction_lots_temp WHERE transaction_temp_id = p_trx_tmp_id;
2221
2222 --Item is only lot controlled. Not serial controlled.
2223 ELSIF(p_serctrl = 1 AND p_lotctrl = 2) THEN
2224
2225 DELETE mtl_transaction_lots_temp
2226 WHERE transaction_temp_id = p_trx_tmp_id;
2227
2228
2229 TRACE('Records deleted in MTLT = ' || SQL%ROWCOUNT, 'INV_TRX_UTIL_PUB.delete_lot_ser_trx');
2230
2231 END IF;
2232
2233
2234
2235 x_return_status := fnd_api.g_ret_sts_success;
2236 EXCEPTION
2237 WHEN fnd_api.g_exc_error THEN
2238 TRACE('Expected error has occured...', 'INVTRXUB', 9);
2239 x_return_status := fnd_api.g_ret_sts_error;
2240 WHEN OTHERS THEN
2241 TRACE('Unexpected error has occured...', 'INVTRXUB', 9);
2242 TRACE('SQLERRM...' || SUBSTR(SQLERRM, 1, 100), 'INVTRXUB', 9);
2243 x_return_status := fnd_api.g_ret_sts_unexp_error;
2244 END delete_lot_ser_trx;
2245
2246 /*
2247 * Procedure: UPDATE_PARENT_MMTT
2248 * This procedure updates or deletes the parent task when one of the child tasks
2249 * is deleted. Generally this procedure is called before deleting a Child Record.
2250 * 1. Parent MMTT Qty is updated if there will be more than one MMTT even after
2251 * the deletion of the child record.
2252 * 2. Parent MMTT is deleted along with the Task when there will be only one MMTT
2253 * after the deletion of the child record. Child Tasks will not be dispatched
2254 * or Queued.
2255 */
2256 PROCEDURE update_parent_mmtt(
2257 x_return_status OUT NOCOPY VARCHAR2
2258 , p_parent_line_id IN NUMBER
2259 , p_child_line_id IN NUMBER
2260 , p_lot_control_code IN NUMBER
2261 , p_serial_control_code IN NUMBER
2262 ) IS
2263 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
2264
2265 l_api_return_status VARCHAR2(1);
2266 l_item_id NUMBER;
2267 l_child_pri_qty NUMBER;
2268 l_child_txn_qty NUMBER;
2269 l_child_sec_txn_qty NUMBER; --BUG12753174
2270
2271 l_child_uom VARCHAR2(3);
2272
2273 l_parent_pri_qty NUMBER;
2274 l_parent_uom VARCHAR2(3);
2275
2276 l_serials_tbl inv_globals.varchar_tbl_type;
2277
2278 l_lot_number mtl_transaction_lots_temp.lot_number%TYPE; -- added for Bug 11931654
2279
2280
2281 CURSOR c_child_details IS
2282 SELECT c.inventory_item_id, c.primary_quantity, c.transaction_quantity, c.secondary_transaction_quantity, c.transaction_uom, p.transaction_uom --BUG12753174
2283 FROM mtl_material_transactions_temp c, mtl_material_transactions_temp p
2284 WHERE c.transaction_temp_id = p_child_line_id
2285 AND p.transaction_temp_id = p_parent_line_id;
2286
2287 -- Start 1 of Fix for Bug 11931654
2288 -- adding a cursor to store the lot_number for child record with p_child_line_id as transaction_temp_id in mtl_transaction_lots_temp
2289
2290 CURSOR c_child_lot_details IS
2291 SELECT c.lot_number
2292 FROM mtl_transaction_lots_temp c
2293 WHERE c.transaction_temp_id = p_child_line_id;
2294
2295 -- End 1 of Fix of Bug 11931654
2296
2297 BEGIN
2298 x_return_status := fnd_api.g_ret_sts_success;
2299
2300 OPEN c_child_details;
2301 FETCH c_child_details INTO l_item_id, l_child_pri_qty, l_child_txn_qty, l_child_sec_txn_qty, l_child_uom, l_parent_uom; --BUG12753174
2302 IF c_child_details%NOTFOUND THEN
2303 TRACE('Either Parent TxnTempID or Child TxnTempID is invalid', 'INV_TRX_UTIL_PUB.UPDATE_PARENT_MMTT');
2304 x_return_status := fnd_api.g_ret_sts_error;
2305 RETURN;
2306 END IF;
2307
2308 -- Delete the Serials
2309 IF p_serial_control_code NOT IN (1,6) THEN
2310 IF p_lot_control_code = 2 THEN
2311 DELETE mtl_serial_numbers_temp
2312 WHERE transaction_temp_id IN (SELECT serial_transaction_temp_id FROM mtl_transaction_lots_temp
2313 WHERE transaction_temp_id = p_parent_line_id)
2314 AND fm_serial_number IN (SELECT msnt.fm_serial_number
2315 FROM mtl_serial_numbers_temp msnt, mtl_transaction_lots_temp mtlt
2316 WHERE mtlt.transaction_temp_id = p_child_line_id
2317 AND msnt.transaction_temp_id = mtlt.serial_transaction_temp_id)
2318 RETURNING fm_serial_number BULK COLLECT INTO l_serials_tbl;
2319
2320 IF SQL%ROWCOUNT = 0 THEN
2321 DELETE mtl_serial_numbers_temp
2322 WHERE transaction_temp_id IN (SELECT serial_transaction_temp_id FROM mtl_transaction_lots_temp
2323 WHERE transaction_temp_id = p_parent_line_id)
2324 AND ROWNUM <= l_child_pri_qty
2325 RETURNING fm_serial_number BULK COLLECT INTO l_serials_tbl;
2326 END IF;
2327 ELSE
2328 DELETE mtl_serial_numbers_temp
2329 WHERE transaction_temp_id = p_parent_line_id
2330 AND fm_serial_number IN (SELECT msnt.fm_serial_number FROM mtl_serial_numbers_temp msnt
2331 WHERE msnt.transaction_temp_id = p_child_line_id)
2332 RETURNING fm_serial_number BULK COLLECT INTO l_serials_tbl;
2333
2334 IF SQL%ROWCOUNT = 0 THEN
2335 DELETE mtl_serial_numbers_temp
2336 WHERE transaction_temp_id = p_parent_line_id
2337 AND ROWNUM <= l_child_pri_qty
2338 RETURNING fm_serial_number BULK COLLECT INTO l_serials_tbl;
2339 END IF;
2340 END IF;
2341
2342 IF l_serials_tbl.COUNT > 0 THEN
2343 FORALL i IN l_serials_tbl.FIRST..l_serials_tbl.LAST
2344 UPDATE mtl_serial_numbers
2345 SET group_mark_id = NULL, line_mark_id = NULL, lot_line_mark_id = NULL
2346 WHERE inventory_item_id = l_item_id
2347 AND serial_number = l_serials_tbl(i);
2348 END IF;
2349 END IF;
2350
2351 -- Delete the Lots
2352 IF p_lot_control_code = 2 THEN
2353
2354 -- Start 2 of fix for Bug 11931654
2355 OPEN c_child_lot_details;
2356 LOOP
2357 FETCH c_child_lot_details INTO l_lot_number;
2358 EXIT WHEN c_child_lot_details%NOTFOUND;
2359
2360 UPDATE mtl_transaction_lots_temp p
2361 SET (p.primary_quantity, p.transaction_quantity, p.secondary_quantity) =
2362 (SELECT p.primary_quantity - SUM(c.primary_quantity)
2363 , p.transaction_quantity - inv_convert.inv_um_convert(l_item_id, NULL, SUM(c.transaction_quantity), l_child_uom, l_parent_uom, NULL, NULL)
2364 , p.secondary_quantity - SUM(c.secondary_quantity) --BUG12753174
2365 FROM mtl_transaction_lots_temp c
2366 WHERE c.transaction_temp_id = p_child_line_id
2367 AND c.lot_number = l_lot_number
2368 GROUP BY c.lot_number)
2369 WHERE p.transaction_temp_id = p_parent_line_id AND p.lot_number = l_lot_number;
2370
2371 END LOOP;
2372 CLOSE c_child_lot_details;
2373 -- End 2 of fix for Bug 11931654
2374
2375 /*
2376 UPDATE mtl_transaction_lots_temp p
2377 SET (p.primary_quantity, p.transaction_quantity) =
2378 (SELECT p.primary_quantity - SUM(c.primary_quantity)
2379 , p.transaction_quantity - inv_convert.inv_um_convert(l_item_id, NULL, SUM(c.transaction_quantity), l_child_uom, l_parent_uom, NULL, NULL)
2380 FROM mtl_transaction_lots_temp c
2381 WHERE c.transaction_temp_id = p_child_line_id
2382 AND c.lot_number = p.lot_number
2383 GROUP BY c.lot_number)
2384 WHERE p.transaction_temp_id = p_parent_line_id;
2385 */
2386
2387 DELETE mtl_transaction_lots_temp
2388 WHERE transaction_temp_id = p_parent_line_id
2389 AND primary_quantity <= 0;
2390 END IF;
2391
2392 IF l_debug = 1 THEN
2393 TRACE('Updating the Parent Task with Txn Temp ID = ' || p_parent_line_id, 'INV_TRX_UTIL_PUB.UPDATE_PARENT_MMTT');
2394 END IF;
2395
2396 UPDATE mtl_material_transactions_temp
2397 SET transaction_quantity = transaction_quantity - inv_convert.inv_um_convert(inventory_item_id, NULL, l_child_txn_qty, l_child_uom, transaction_uom, NULL, NULL)
2398 , primary_quantity = primary_quantity - l_child_pri_qty
2399 , secondary_transaction_quantity = secondary_transaction_quantity - l_child_sec_txn_qty --BUG12753174
2400 WHERE transaction_temp_id = p_parent_line_id
2401 RETURNING primary_quantity INTO l_parent_pri_qty;
2402
2403 IF l_parent_pri_qty <= 0 THEN
2404 IF inv_control.get_current_release_level >=
2405 inv_release.get_j_release_level
2406 THEN
2407 IF l_debug = 1 THEN
2408 TRACE('Checking if parent should be archived: Txn Temp ID = ' || p_parent_line_id
2409 , 'INV_TRX_UTIL_PUB.UPDATE_PARENT_MMTT');
2410 END IF;
2411
2412 l_api_return_status := fnd_api.g_ret_sts_success;
2413 inv_parent_mmtt_pvt.process_parent
2414 ( x_return_status => l_api_return_status
2415 , p_parent_temp_id => p_parent_line_id
2416 );
2417
2418 IF l_api_return_status <> fnd_api.g_ret_sts_success
2419 THEN
2420 IF l_debug = 1 THEN
2421 TRACE('Error from inv_parent_mmtt_pvt.process_parent'
2422 , 'INV_TRX_UTIL_PUB.UPDATE_PARENT_MMTT');
2423 END IF;
2424 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2425 END IF;
2426 ELSE
2427 IF l_debug = 1 THEN
2428 TRACE('Deleting the Parent Task with Txn Temp ID = ' || p_parent_line_id, 'INV_TRX_UTIL_PUB.UPDATE_PARENT_MMTT');
2429 END IF;
2430
2431 DELETE wms_dispatched_tasks WHERE transaction_temp_id = p_parent_line_id;
2432 DELETE mtl_material_transactions_temp WHERE transaction_temp_id = p_parent_line_id;
2433 END IF;
2434 END IF;
2435 EXCEPTION
2436 WHEN OTHERS THEN
2437 x_return_status := fnd_api.g_ret_sts_unexp_error;
2438
2439 IF (l_debug = 1) THEN
2440 TRACE('Unexpected Error occurred - ' || SQLERRM, 'INV_TRX_UTIL_PUB.UPDATE_PARENT_MMTT');
2441 END IF;
2442 END update_parent_mmtt;
2443
2444 /* Bug 13020024
2445 * Procedure: call_rcv_manager
2446 * Input Parameters:
2447 * p_trx_header_id : Transaction Header Id
2448 * Output Parameters:
2449 * x_return_status : Return value of calling RCV TM
2450 * x_outcome : Outcome of calling RCV TM
2451 * x_msg : Error message
2452 * This procedure is to call RCVTM for IOT in online mode.
2453 * 1. Fetches MMTs with p_trx_header_id, and update the related RTIs with a new group_id.
2454 * 2. Make a call to RCVTM with this group_id
2455 */
2456
2457 PROCEDURE call_rcv_manager
2458 ( x_return_value OUT NOCOPY NUMBER,
2459 x_outcome OUT NOCOPY VARCHAR2,
2460 x_msg OUT NOCOPY VARCHAR2,
2461 p_trx_header_id IN NUMBER
2462 ) IS
2463
2464 PRAGMA AUTONOMOUS_TRANSACTION;
2465 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
2466 l_group_id NUMBER;
2467 l_rpc_timeout NUMBER := 172800;
2468 l_count NUMBER;
2469
2470 BEGIN
2471
2472 x_return_value := 0;
2473 x_outcome:= 'SUCCESS';
2474 x_msg := NULL;
2475
2476 IF (l_debug = 1) THEN
2477 TRACE('*** Entering inv_trx_util_pub.call_rcv_manager. TrxHeaderId='||p_trx_header_id, 'INV_TRX_UTIL_PUB.CALL_RCV_MANAGER');
2478 END IF;
2479
2480 IF (p_trx_header_id IS NOT NULL) AND (p_trx_header_id <> -1 ) THEN
2481
2482 SELECT Count(rti.interface_transaction_id)
2483 INTO l_count
2484 FROM rcv_transactions_interface rti
2485 WHERE rti.processing_status_code = 'RUNNING'
2486 AND rti.processing_mode_code = 'ONLINE'
2487 AND rti.transaction_status_code = 'PENDING'
2488 AND EXISTS
2489 (SELECT 1
2490 FROM mtl_material_transactions mmt
2491 WHERE mmt.transaction_id = rti.inv_transaction_id
2492 AND mmt.transaction_set_id = p_trx_header_id );
2493
2494 IF l_count > 0 THEN
2495
2496 SELECT rcv_interface_groups_s.NEXTVAL
2497 INTO l_group_id
2498 FROM dual;
2499
2500 UPDATE rcv_transactions_interface rti
2501 SET group_id = l_group_id
2502 WHERE rti.processing_status_code = 'RUNNING'
2503 AND rti.processing_mode_code = 'ONLINE'
2504 AND rti.transaction_status_code = 'PENDING'
2505 AND EXISTS
2506 (SELECT 1
2507 FROM mtl_material_transactions mmt
2508 WHERE mmt.transaction_id = rti.inv_transaction_id
2509 AND mmt.transaction_set_id = p_trx_header_id );
2510
2511 COMMIT;
2512
2513 x_return_value := fnd_transaction.synchronous(l_rpc_timeout,x_outcome,x_msg,'PO','RCVTPO','ONLINE',l_group_id, NULL,
2514 NULL, NULL, NULL, NULL, NULL,NULL, NULL,
2515 NULL, NULL, NULL, NULL, NULL, NULL,NULL,
2516 NULL, NULL, NULL);
2517 COMMIT;
2518
2519
2520 IF l_debug = 1 THEN
2521 TRACE('After call rcv manager. GrpId=' || l_group_id ||',retval:'||x_return_value||',outcome:'||x_outcome||',message:'|| x_msg
2522 , 'INV_TRX_UTIL_PUB.CALL_RCV_MANAGER');
2523 END IF;
2524
2525 END IF;
2526
2527 END IF;
2528
2529 EXCEPTION
2530 WHEN OTHERS THEN
2531 x_return_value := -1;
2532 x_outcome := 'ERROR';
2533 x_msg := 'Failed to call Receiving Transaction Processor!';
2534 IF (l_debug = 1) THEN
2535 TRACE('Unexpected Error occurred - ' || SQLERRM, 'INV_TRX_UTIL_PUB.CALL_RCV_MANAGER');
2536 END IF;
2537 END call_rcv_manager;
2538
2539 END inv_trx_util_pub;