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