[Home] [Help]
PACKAGE BODY: APPS.INVTTMTX
Source
1 PACKAGE BODY INVTTMTX as
2 /* $Header: INVTTMTB.pls 120.6 2006/09/14 18:39:21 jsrivast ship $ */
3
4 PROCEDURE tdatechk(org_id IN INTEGER,
5 transaction_date IN DATE,
6 period_id OUT nocopy INTEGER,
7 open_past_period IN OUT nocopy BOOLEAN) IS
8 v_transaction_period_id INTEGER;
9 v_current_period_id INTEGER;
10 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
11 v_scheduled_close_date DATE;
12 begin
13 if( l_debug = 1 ) then
14 inv_log_util.trace('tdatechk ' || nvl(G_ORG_ID, -1) || ' ' || nvl(G_TRANSACTION_DATE, trunc(sysdate)), 'tdatechk', 9);
15 inv_log_util.trace('org_id = ' || org_id || ' transaction_date = ' || transaction_date, 'tdatechk', 9);
16 end if;
17 if( nvl(G_ORG_ID, -1) <> org_id OR nvl(G_TRANSACTION_DATE, trunc(sysdate)) <> trunc(nvl(transaction_date, sysdate)) OR G_PERIOD_STATUS <> 1) THEN
18 if( l_debug = 1 ) then
19 inv_log_util.trace('will query database', 'tdatechk', 9);
20 end if;
21 begin
22 period_id := 0;
23
24 -- Bug 4737520 (Base bugfix 4721230) move caching to the end to make sure current_period_id gets populated correctly.
25 --G_ORG_ID := org_id;
26
27 G_TRANSACTION_DATE := trunc(nvl(transaction_date, sysdate));
28
29 SELECT ACCT_PERIOD_ID, TRUNC(SCHEDULE_CLOSE_DATE)
30 INTO v_transaction_period_id,v_scheduled_close_date
31 FROM ORG_ACCT_PERIODS
32 WHERE PERIOD_CLOSE_DATE IS NULL
33 AND ORGANIZATION_ID = org_id
34 AND TRUNC(SCHEDULE_CLOSE_DATE) >=
35 TRUNC(INV_LE_TIMEZONE_PUB.get_le_day_for_inv_org(Nvl(transaction_date,Sysdate),org_id))
36 AND TRUNC(PERIOD_START_DATE) <=
37 TRUNC(INV_LE_TIMEZONE_PUB.get_le_day_for_inv_org(Nvl(transaction_date,Sysdate),org_id));
38 period_id := v_transaction_period_id;
39 G_TRANSACTION_PERIOD_ID := v_transaction_period_id;
40 G_PERIOD_STATUS := 1;
41
42 EXCEPTION
43 when NO_DATA_FOUND then
44 G_TRANSACTION_PERIOD_ID := 0;
45 period_id := 0;
46 G_PERIOD_STATUS := 0;
47 when OTHERS then
48 G_TRANSACTION_PERIOD_ID := -1;
49 period_id := -1;
50 G_PERIOD_STATUS := 0;
51 end;
52 else
53 if( l_debug = 1 ) then
54 inv_log_util.trace('get the value from global package var ' || g_transaction_period_id, 'tdatechk', 9);
55 end if;
56 v_transaction_period_id := G_TRANSACTION_PERIOD_ID;
57 period_id := G_TRANSACTION_PERIOD_ID;
58 end if;
59 /* Check to see if the selected period id falls within the current
60 period or is in a past period.
61 */
62
63 begin
64 if (open_past_period) then
65 if( l_debug = 1 ) then
66 inv_log_util.trace('open_past_period is true', 'tdatechk', 9);
67 inv_log_util.trace('G_CURRENT_DATE is ' || trunc(nvl(G_CURRENT_DATE, sysdate)), 'tdatechk', 9);
68 end if;
69
70 -- Bug 4737520 base bugfix 4721230 should still query the current_period_id, if cached g_current_period_id is null
71
72 if( nvl(G_ORG_ID, -1) <> org_id OR trunc(nvl(G_CURRENT_DATE, sysdate)) <> trunc(sysdate) OR G_CURRENT_PERIOD_ID is NULL ) THEN
73 if l_debug = 1 then
74 inv_log_util.trace('going to query db', 'tdatechk', 9);
75 end if;
76 G_CURRENT_DATE := trunc(sysdate);
77
78 SELECT ACCT_PERIOD_ID
79 INTO v_current_period_id
80 FROM ORG_ACCT_PERIODS
81 WHERE PERIOD_CLOSE_DATE IS NULL
82 AND ORGANIZATION_ID = org_id
83 AND TRUNC(INV_LE_TIMEZONE_PUB.get_le_day_for_inv_org(Sysdate,org_id))
84 BETWEEN TRUNC(PERIOD_START_DATE) and
85 TRUNC(SCHEDULE_CLOSE_DATE);
86 G_CURRENT_PERIOD_ID := v_current_period_id;
87
88 if v_transaction_period_id <> v_current_period_id then
89 open_past_period := FALSE;
90 end if;
91 else
92 if l_debug = 1 then
93 inv_log_util.trace('getting from cache ' || g_current_period_id, 'tdatechk', 9);
94 end if;
95 if v_transaction_period_id <> G_CURRENT_PERIOD_ID THEN
96 open_past_period := FALSE;
97 end if;
98 end if;
99
100 end if;
101 EXCEPTION
102 when NO_DATA_FOUND then
103 G_CURRENT_PERIOD_ID := -1;
104 open_past_period := FALSE;
105 when OTHERS then
106 G_CURRENT_PERIOD_ID := -1;
107 period_id := -1;
108 end;
109 G_ORG_ID := org_id; --bugfix 4721230
110 end tdatechk;
111
112 FUNCTION ship_number_validation(shipment_number IN VARCHAR2) RETURN NUMBER IS
113 counter NUMBER := 0;
114 found_row VARCHAR2(100);
115 vall varchar2(30);
116 BEGIN
117 vall := shipment_number;
118 /* Bug:5154903. For the following two select statements added
119 conditions to check transaction_type_id and transaction_action_id
120 to query for only Transaction Type Inter-Org Transfer*/
121 SELECT shipment_number
122 INTO found_row
123 FROM mtl_transactions_interface m
124 WHERE m.shipment_number = vall
125 AND m.transaction_type_id = INV_GLOBALS.G_SOURCETYPE_INVENTORY
126 AND m.transaction_action_id= INV_GLOBALS.G_ACTION_INTRANSITSHIPMENT
127 AND ROWNUM = 1 ;
128
129 return 0;
130
131 EXCEPTION WHEN NO_DATA_FOUND then
132 BEGIN
133 SELECT shipment_number
134 INTO found_row
135 FROM mtl_material_transactions_temp m
136 WHERE m.shipment_number = vall
137 AND m.transaction_type_id = INV_GLOBALS.G_SOURCETYPE_INVENTORY
138 AND m.transaction_action_id= INV_GLOBALS.G_ACTION_INTRANSITSHIPMENT
139 AND ROWNUM = 1 ;
140
141 return 0;
142
143 EXCEPTION WHEN NO_DATA_FOUND then
144 BEGIN
145 /* Bug:5154903.Added condition to check for receipt_source_code to
146 query for only Transaction Type Inter-Org Transfer*/
147 SELECT shipment_num
148 INTO found_row
149 FROM rcv_shipment_headers m
150 WHERE m.shipment_num = vall
151 AND m.receipt_source_code = 'INVENTORY'
152 AND ROWNUM = 1 ;
153
154 return 0;
155
156 EXCEPTION WHEN NO_DATA_FOUND then
157 return 1;
158 END;
159 END;
160 end ship_number_validation;
161
162 procedure post_query(
163 p_org_id in number,
164 p_inventory_item_id in number,
165 p_subinv in varchar2,
166 p_to_subinv in varchar2,
167 p_reason_id in number,
168 p_trx_type in varchar2,
169 p_transaction_action_id in number,
170 p_from_uom in varchar2,
171 p_to_uom in varchar2,
172 p_sub_qty_tracked out nocopy number,
173 p_sub_asset_inv out nocopy number,
174 p_sub_locator_type out nocopy number,
175 p_sub_material_acct out nocopy number,
176 p_to_sub_qty_tracked out nocopy number,
177 p_to_sub_asset_inv out nocopy number,
178 p_to_sub_locator_type out nocopy number,
179 p_to_sub_material_acct out nocopy number,
180 p_reason_name out nocopy varchar2,
181 p_transaction_type out nocopy varchar2,
182 p_conversion_rate out nocopy number) is
183
184 cursor get_sub_info(
185 c_org_id number,
186 c_item_id number,
187 c_subinventory varchar2) is
188 select quantity_tracked,
189 asset_inventory,
190 locator_type,
191 material_account
192 from mtl_subinventories_all_v
193 where organization_id = c_org_id
194 and secondary_inventory_name = c_subinventory;
195
196 cursor get_transaction_type(c_transaction_action_id number) is
197 select meaning
198 from mfg_lookups
199 where lookup_type = 'WIP_TRANSACTION_DIRECTION'
200 and lookup_code = decode(c_transaction_action_id,
201 1, 1 /* return */,
202 2 /* otherwise, issue */);
203
204 cursor get_reason(c_reason_id number) is
205 select reason_name
206 from mtl_transaction_reasons
207 where reason_id = c_reason_id;
208
209 begin
210 -- get subinv information
211 if (p_subinv is NOT NULL) then
212 open get_sub_info(
213 c_org_id => p_org_id,
214 c_item_id => p_inventory_item_id,
215 c_subinventory => p_subinv);
216 fetch get_sub_info into
217 p_sub_qty_tracked,
218 p_sub_asset_inv,
219 p_sub_locator_type,
220 p_sub_material_acct;
221 if (get_sub_info%NOTFOUND) then
222 p_sub_qty_tracked := NULL;
223 p_sub_asset_inv := NULL;
224 p_sub_locator_type := NULL;
225 p_sub_material_acct := NULL;
226 end if;
227 close get_sub_info;
228 end if;
229
230 -- get to subinv information
231 if (p_to_subinv is NOT NULL) then
232 open get_sub_info(
233 c_org_id => p_org_id,
234 c_item_id => p_inventory_item_id,
235 c_subinventory => p_to_subinv);
236 fetch get_sub_info into
237 p_to_sub_qty_tracked,
238 p_to_sub_asset_inv,
239 p_to_sub_locator_type,
240 p_to_sub_material_acct;
241 if (get_sub_info%NOTFOUND) then
242 p_to_sub_qty_tracked := NULL;
243 p_to_sub_asset_inv := NULL;
244 p_to_sub_locator_type := NULL;
245 p_to_sub_material_acct := NULL;
246 end if;
247 close get_sub_info;
248 end if;
249
250 -- get transaction type for backflush
251 if (p_trx_type = 'WIP_BACKFLUSH') then
252 open get_transaction_type(
253 c_transaction_action_id => p_transaction_action_id);
254 fetch get_transaction_type into p_transaction_type;
255 if (get_transaction_type%NOTFOUND) then
256 p_transaction_type := NULL;
257 end if;
258 close get_transaction_type;
259 end if;
260
261 -- get reason name
262 if (p_reason_id is NOT NULL) then
263 open get_reason(c_reason_id => p_reason_id);
264 fetch get_reason into p_reason_name;
265 if (get_reason%NOTFOUND) then
266 p_reason_name := NULL;
267 end if;
268 close get_reason;
269 end if;
270
271 -- get conversion rate
272 p_conversion_rate :=
273 inv_convert.inv_um_convert(
274 item_id => p_inventory_item_id,
275 precision => 38,
276 from_quantity => 1,
277 from_unit => p_from_uom,
278 to_unit => p_to_uom,
279 from_name => NULL,
280 to_name => NULL);
281 end post_query;
282
283
284
285
286 Procedure RPC_FAILURE_ROLLBACK(trx_header_id number,
287 cleanup_success in out nocopy boolean) IS
288 v_trx_header_id NUMBER ;
289
290 BEGIN
291 cleanup_success := TRUE;
292 v_trx_header_id := trx_header_id ;
293
294 DECLARE
295 -- Delete predefined serial numbers
296 cursor c1 is select group_mark_id from
297 mtl_serial_numbers
298 where group_mark_id = v_trx_header_id
299 and current_status = 6
300 for update of group_mark_id nowait;
301 BEGIN
302 open c1 ;
303 delete mtl_serial_numbers
304 where group_mark_id = v_trx_header_id
305 and current_status = 6;
306 close c1 ;
307 EXCEPTION
308 WHEN OTHERS then
309 NULL;
310 END ;
311
312 DECLARE
313 -- Unmark serial numbers
314 cursor c2 is select group_mark_id from
315 mtl_serial_numbers
316 where group_mark_id = v_trx_header_id
317 for update of group_mark_id nowait;
318 BEGIN
319 open c2 ;
320 update mtl_serial_numbers
321 set group_mark_id = null,
322 line_mark_id = null,
323 lot_line_mark_id = null
324 where group_mark_id = v_trx_header_id;
325 close c2 ;
326 EXCEPTION
327 WHEN OTHERS then
328 NULL;
329 END ;
330
331 DECLARE
332 -- Delete lot and serial records from temp tables
333 cursor c3 is select group_header_id from
334 mtl_serial_numbers_temp
335 where group_header_id = v_trx_header_id
336 for update of group_header_id nowait;
337 BEGIN
338 open c3 ;
339 delete mtl_serial_numbers_temp
340 where group_header_id = v_trx_header_id;
341 close c3 ;
342 EXCEPTION
343 WHEN OTHERS then
344 NULL;
345 END ;
346
347 DECLARE
348 cursor c4 is select group_header_id from
349 mtl_transaction_lots_temp
350 where group_header_id = v_trx_header_id
351 for update of group_header_id nowait;
352 BEGIN
353 open c4 ;
354 delete mtl_transaction_lots_temp
355 where group_header_id = v_trx_header_id;
356 close c4 ;
357 EXCEPTION
358 WHEN OTHERS then
359 NULL;
360 END ;
361
362 delete mtl_material_transactions_temp
363 where transaction_header_id = trx_header_id;
364 commit;
365
366
367 EXCEPTION
368 WHEN NO_DATA_FOUND then
369 null;
370 WHEN OTHERS then
371 cleanup_success := FALSE;
372 end rpc_failure_rollback;
373
374 Procedure lot_handling(hdr_id NUMBER, lot_success IN OUT nocopy VARCHAR2) IS
375 completed NUMBER := 100;
376
377 /** INVCONV Anand Thiyagarajan 02-Nov-2004 Start **/
378
379 /* Jalaj Srivastava Bug 5527373
380 varibales no longer needed are commented */
381
382 --l_transaction_Action_id mtl_material_transactions_temp.transaction_action_id%TYPE;
383 --l_transaction_source_type_id mtl_material_transactions_temp.transaction_source_type_id%TYPE;
384 --l_origination_type mtl_lot_numbers.origination_type%TYPE;
385 --l_lot_rec_type mtl_lot_numbers%ROWTYPE;
386 --l_organization_id mtl_lot_numbers.organization_id%TYPE;
387 --l_inventory_item_id mtl_lot_numbers.inventory_item_id%TYPE;
388 --l_transaction_date DATE;
389 --l_expiration_date DATE;
390 --l_return_status VARCHAR2(1) ;
391 --l_msg_data VARCHAR2(3000) ;
392 --l_msg_count NUMBER;
393
394
395 /** INVCONV Anand Thiyagarajan 02-Nov-2004 End **/
396
397 BEGIN
398 if ( lot_success = 'FULL_LOT_PROCESSING' OR lot_success = 'KILL_ORPHANS' ) then
399 BEGIN
400 if ( (hdr_id IS NULL) OR (hdr_id < 0) ) then
401 completed := 1;
402 else
403 BEGIN
404 DELETE FROM mtl_transaction_lots_temp
405 WHERE group_header_id = hdr_id AND
406 transaction_temp_id NOT IN
407 (SELECT mmtt.transaction_temp_id FROM
408 mtl_material_transactions_temp mmtt
409 WHERE mmtt.transaction_header_id = hdr_id AND mmtt.transaction_temp_id
410 IS NOT NULL AND mmtt.transaction_header_id IS NOT NULL);
411
412
413 DELETE FROM mtl_serial_numbers_temp
414 WHERE group_header_id = hdr_id AND
415 transaction_temp_id NOT IN
416 (SELECT mmtt.transaction_temp_id FROM
417 mtl_material_transactions_temp mmtt
418 WHERE mmtt.transaction_header_id = hdr_id AND mmtt.transaction_temp_id
419 IS NOT NULL) AND transaction_temp_id NOT IN
420 ( SELECT mtlt.serial_transaction_temp_id
421 FROM mtl_transaction_lots_temp mtlt
422 WHERE mtlt.group_header_id = hdr_id
423 AND mtlt.serial_transaction_temp_id IS NOT NULL);
424
428 completed := -1 ;
425 completed := 1;
426 EXCEPTION
427 WHEN OTHERS then
429 END;
430 end if;
431 END;
432 end if;
433 if ( (lot_success='FULL_LOT_PROCESSING' OR
434 lot_success='MOVE_MMTT_LOTS_TO_MTLT') AND (completed > 0) ) then
435 BEGIN
436 if ( (hdr_id IS NULL) OR ( hdr_id < 0) ) then
437 completed := 2;
438 elsif ( completed > 0 ) then
439
440 /** INVCONV Anand Thiyagarajan 02-Nov-2004 Start **/
441
442 /* Jalaj Srivastava Bug 5527373
443 commenting out the code below as
444 1. there may not be lots in mmtt OR
445 2. there will be multiple rows with different items
446 in mmtt so we cannot pick up attributes of just one record */
447
448 /* *************************************************************************************
449
450 BEGIN
451 select mmtt.transaction_action_id, mmtt.transaction_source_type_id,
452 mmtt.organization_id, mmtt.inventory_item_id,
453 mmtt.transaction_date, mmtt.lot_expiration_date
454 into l_transaction_Action_id, l_transaction_source_type_id,
455 l_organization_id, l_inventory_item_id,
456 l_transaction_date, l_expiration_date
457 from mtl_material_transactions_temp mmtt
458 where mmtt.transaction_header_id = hdr_id
459 AND mmtt.lot_number IS NOT NULL
460 AND mmtt.transaction_header_id IS NOT NULL
461 AND rownum = 1;
462 EXCEPTION
463 when no_data_found then
464 l_transaction_Action_id := null;
465 l_transaction_source_type_id := null;
466 END;
467
468 IF l_transaction_source_type_id IN ('1','7') THEN
469 l_origination_type := 3;
470 ELSIF l_transaction_source_type_id IN ('13','6','12') THEN
471 l_origination_type := 4;
472 ELSIF l_transaction_source_type_id = '31' THEN
473 l_origination_type := 1;
474 END IF;
475
476 l_lot_rec_type.organization_id := l_organization_id;
477 l_lot_rec_type.inventory_item_id := l_inventory_item_id;
478 l_lot_rec_type.origination_Date := l_transaction_date;
479 l_lot_rec_type.expiration_Date := l_expiration_date;
480 inv_lot_api_pkg.Set_Msi_Default_Attr (
481 p_lot_rec => l_lot_rec_type
482 , x_return_status => l_return_status
483 , x_msg_count => l_msg_count
484 , x_msg_data => l_msg_data
485 ) ;
486 IF l_return_status <> 'S' THEN
487 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => l_msg_count, p_data => l_msg_data);
488 if( l_msg_count > 1 ) then
489 l_msg_data := fnd_msg_pub.get(l_msg_count, FND_API.G_FALSE);
490 end if;
491 END IF;
492 *************************************************************************************************************** */
493
494 /** INVCONV Anand Thiyagarajan 02-Nov-2004 End **/
495
496 /* INSERT INTO MTL_TRANSACTION_LOTS_TEMP
497 ( transaction_temp_id, last_update_date, last_updated_by, creation_date,
498 created_by, last_update_login, request_id, program_application_id,
499 program_id, program_update_date, transaction_quantity, primary_quantity,
500 lot_number, lot_expiration_date, group_header_id,
501 serial_transaction_temp_id, status_id)
502 (select mmtt.transaction_temp_id, mmtt.last_update_date,
503 mmtt.last_updated_by, mmtt.creation_date,
504 mmtt.created_by, mmtt.last_update_login, mmtt.request_id,
505 mmtt.program_application_id,
506 mmtt.program_id, mmtt.program_update_date,
507 mmtt.transaction_quantity, mmtt.primary_quantity, mmtt.lot_number,
508 mmtt.lot_expiration_date, mmtt.transaction_header_id,
509 mmtt.transaction_temp_id, msi.default_lot_status_id
510 FROM mtl_material_transactions_temp mmtt,
511 mtl_system_items msi
512 WHERE mmtt.transaction_header_id = hdr_id AND mmtt.lot_number IS
513 NOT NULL AND mmtt.transaction_header_id IS NOT NULL
514 AND msi.inventory_item_id = mmtt.inventory_item_id
515 AND msi.organization_id = mmtt.organization_id) ; */
516
517
518 INSERT INTO MTL_TRANSACTION_LOTS_TEMP
519 ( transaction_temp_id, last_update_date, last_updated_by, creation_date,
520 created_by, last_update_login, request_id, program_application_id,
521 program_id, program_update_date, transaction_quantity, primary_quantity,
522 lot_number, lot_expiration_date, group_header_id,
523 serial_transaction_temp_id, status_id
524 , lot_attribute_category
525 , attribute_category
526 , attribute1
527 , attribute2
528 , attribute3
529 , attribute4
530 , attribute5
531 , attribute6
532 , attribute7
533 , attribute8
534 , attribute9
535 , attribute10
536 , attribute11
540 , attribute15
537 , attribute12
538 , attribute13
539 , attribute14
541 , c_attribute1
542 , c_attribute2
543 , c_attribute3
544 , c_attribute4
545 , c_attribute5
546 , c_attribute6
547 , c_attribute7
548 , c_attribute8
549 , c_attribute9
550 , c_attribute10
551 , c_attribute11
552 , c_attribute12
553 , c_attribute13
554 , c_attribute14
555 , c_attribute15
556 , c_attribute16
557 , c_attribute17
558 , c_attribute18
559 , c_attribute19
560 , c_attribute20
561 , n_attribute1
562 , n_attribute2
563 , n_attribute3
564 , n_attribute4
565 , n_attribute5
566 , n_attribute6
567 , n_attribute7
568 , n_attribute8
569 , n_attribute9
570 , n_attribute10
571 , d_attribute1
572 , d_attribute2
573 , d_attribute3
574 , d_attribute4
575 , d_attribute5
576 , d_attribute6
577 , d_attribute7
578 , d_attribute8
579 , d_attribute9
580 , d_attribute10
581 , grade_code
582 , origination_date
583 , date_code
584 , change_date
585 , age
586 , retest_date
587 , maturity_date
588 , item_size
589 , color
590 , volume
591 , volume_uom
592 , place_of_origin
593 , best_by_date
594 , length
595 , length_uom
596 , recycled_content
597 , thickness
598 , thickness_uom
599 , width
600 , width_uom
601 , territory_code
602 , supplier_lot_number
603 , vendor_name
604 /* INVCONV Anand Thiyagarajan 22-Oct-2004 Start */
605 , secondary_quantity
606 , parent_lot_number
607 , origination_type
608 , expiration_action_code
609 , expiration_action_date
610 , hold_date
611 , reason_id
612 /* INVCONV Anand Thiyagarajan 22-Oct-2004 End */
613 )
614 (select mmtt.transaction_temp_id, mmtt.last_update_date,
615 mmtt.last_updated_by, mmtt.creation_date,
616 mmtt.created_by, mmtt.last_update_login, mmtt.request_id,
617 mmtt.program_application_id,
618 mmtt.program_id, mmtt.program_update_date,
619 mmtt.transaction_quantity, mmtt.primary_quantity, mmtt.lot_number,
620 nvl(mmtt.lot_expiration_date,decode(msi.shelf_life_code,2,NVL(mln.origination_date, mmtt.transaction_date) + shelf_life_days,null)), /* Jalaj Srivastava Bug 5527373*/
621 mmtt.transaction_header_id,
622 mmtt.transaction_temp_id, NVL(mln.status_id, msi.default_lot_status_id)
623 ,mln.lot_attribute_category
624 ,mln.attribute_category
625 ,mln.attribute1
626 ,mln.attribute2
627 ,mln.attribute3
628 ,mln.attribute4
629 ,mln.attribute5
630 ,mln.attribute6
631 ,mln.attribute7
632 ,mln.attribute8
633 ,mln.attribute9
634 ,mln.attribute10
635 ,mln.attribute11
636 ,mln.attribute12
637 ,mln.attribute13
638 ,mln.attribute14
639 ,mln.attribute15
640 ,mln.c_attribute1
641 ,mln.c_attribute2
642 ,mln.c_attribute3
643 ,mln.c_attribute4
644 ,mln.c_attribute5
645 ,mln.c_attribute6
646 ,mln.c_attribute7
647 ,mln.c_attribute8
648 ,mln.c_attribute9
649 ,mln.c_attribute10
650 ,mln.c_attribute11
651 ,mln.c_attribute12
652 ,mln.c_attribute13
653 ,mln.c_attribute14
654 ,mln.c_attribute15
655 ,mln.c_attribute16
656 ,mln.c_attribute17
657 ,mln.c_attribute18
658 ,mln.c_attribute19
659 ,mln.c_attribute20
660 ,mln.n_attribute1
661 ,mln.n_attribute2
662 ,mln.n_attribute3
663 ,mln.n_attribute4
664 ,mln.n_attribute5
665 ,mln.n_attribute6
666 ,mln.n_attribute7
667 ,mln.n_attribute8
668 ,mln.n_attribute9
669 ,mln.n_attribute10
670 ,mln.d_attribute1
671 ,mln.d_attribute2
672 ,mln.d_attribute3
673 ,mln.d_attribute4
674 ,mln.d_attribute5
675 ,mln.d_attribute6
676 ,mln.d_attribute7
677 ,mln.d_attribute8
678 ,mln.d_attribute9
679 ,mln.d_attribute10
680 , nvl(mln.grade_code, decode(msi.grade_control_flag,'Y',msi.default_grade,null)) /* Jalaj Srivastava Bug 5527373*/ /* INVCONV Anand Thiyagarajan 22-Oct-2004 Start */
681 , NVL(mln.origination_date, mmtt.transaction_date) /* Jalaj Srivastava Bug 5527373*/
682 , mln.date_code
683 , mln.change_date
684 , mln.age
685 , nvl(mln.retest_date, NVL(mln.origination_date, mmtt.transaction_date) + msi.retest_interval) /* Jalaj Srivastava Bug 5527373*/
686 , nvl(mln.maturity_date, NVL(mln.origination_date, mmtt.transaction_date) + msi.maturity_days) /* Jalaj Srivastava Bug 5527373*/
687 , mln.item_size
688 , mln.color
689 , mln.volume
690 , mln.volume_uom
691 , mln.place_of_origin
692 , mln.best_by_date
693 , mln.length
694 , mln.length_uom
695 , mln.recycled_content
696 , mln.thickness
700 , mln.territory_code
697 , mln.thickness_uom
698 , mln.width
699 , mln.width_uom
701 , mln.supplier_lot_number
702 , mln.vendor_name
703 /* INVCONV Anand Thiyagarajan 22-Oct-2004 Start */
704 , mmtt.secondary_transaction_quantity
705 , mln.parent_lot_number
706 , NVL(mln.origination_type, decode(mmtt.transaction_source_type_id,1,3,7,3,13,4,6,4,12,4,31,1,6)) /* Jalaj Srivastava Bug 5527373*/
707 , NVL(mln.expiration_action_code, decode(msi.shelf_life_code,1,null,msi.expiration_action_code)) /* Jalaj Srivastava Bug 5527373*/
708 , NVL(mln.expiration_action_date,
709 decode(msi.shelf_life_code,1,null,nvl(mmtt.lot_expiration_date,
710 decode(msi.shelf_life_code,2,NVL(mln.origination_date, mmtt.transaction_date) + shelf_life_days,null)) + msi.expiration_action_interval)) /* Jalaj Srivastava Bug 5527373*/
711 , NVL(mln.hold_date, NVL(mln.origination_date, mmtt.transaction_date) + hold_days) /* Jalaj Srivastava Bug 5527373*/
712 , mmtt.reason_id
713 /* INVCONV Anand Thiyagarajan 22-Oct-2004 End */
714 FROM mtl_material_transactions_temp mmtt,
715 mtl_system_items msi,
716 mtl_lot_numbers mln
717 WHERE mmtt.transaction_header_id = hdr_id AND mmtt.lot_number IS
718 NOT NULL AND mmtt.transaction_header_id IS NOT NULL
719 AND msi.inventory_item_id = mmtt.inventory_item_id
720 AND msi.organization_id = mmtt.organization_id
721 and mln.inventory_item_id(+) = mmtt.inventory_item_id
722 and mln.organization_id(+) = mmtt.organization_id
723 and mln.lot_number(+) =mmtt.lot_number);
724
725 -- The quantity in mtlt should always be positive
726 UPDATE mtl_transaction_lots_temp
727 SET primary_quantity = -1 * primary_quantity ,
728 transaction_quantity = -1 * transaction_quantity ,
729 secondary_quantity = -1 * secondary_quantity /* INVCONV Anand Thiyagarajan 22-Oct-2004*/
730 WHERE transaction_temp_id in
731 (select mmtt.transaction_temp_id
732 FROM mtl_material_transactions_temp mmtt
733 WHERE mmtt.transaction_header_id = hdr_id AND mmtt.lot_number IS
734 NOT NULL AND mmtt.transaction_header_id IS NOT NULL)
735 AND ( primary_quantity < 0 OR transaction_quantity < 0 OR secondary_quantity < 0); /* INVCONV Anand Thiyagarajan 22-Oct-2004 */
736
737 completed := 2;
738 end if;
739 EXCEPTION WHEN OTHERS then
740 completed := -2;
741 END;
742
743 BEGIN
744 if ( ((hdr_id IS NULL) OR (hdr_id < 0 )) AND (completed > 0) ) then
745 completed := 3;
746 elsif ( completed > 0 ) then
747 UPDATE mtl_material_transactions_temp
748 SET lot_number = NULL, lot_expiration_date = NULL
749 WHERE transaction_header_id = hdr_id AND process_flag = 'Y';
750 completed := 3;
751 end if;
752 EXCEPTION WHEN OTHERS then
753 completed := -3;
754 END;
755 end if;
756 if ( ((lot_success = 'FULL_LOT_PROCESSING') AND (completed > 0 )) OR
757 ( (lot_success = 'KILL_ORPHANS') AND (completed >0)) ) then
758 BEGIN
759 if ( ( hdr_id is NULL ) or (hdr_id < 0 )) then
760 null;
761 completed := 4;
762 else
763 -- Bug 4062450 performance change.
764 DELETE /*+ INDEX(MSN MTL_SERIAL_NUMBERS_N2) */
765 FROM mtl_serial_numbers MSN
766 WHERE current_status = 6
767 AND group_mark_id = -1
768 AND (MSN.inventory_item_id, MSN.current_organization_id) in
769 (select inventory_item_id,ORGANIZATION_ID
770 FROM mtl_material_transactions_temp
771 WHERE transaction_header_id = hdr_id);
772 --2101601
773 completed := 4 ;
774 end if;
775 EXCEPTION when others then
776 completed := -4 ;
777 END;
778 end if;
779 lot_success := to_char(completed) ;
780 end lot_handling;
781
782 end INVTTMTX;