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