[Home] [Help]
PACKAGE BODY: APPS.INV_TXN_MANAGER_PUB
Source
1 PACKAGE BODY inv_txn_manager_pub AS
2 /* $Header: INVTXMGB.pls 120.28 2007/11/12 22:43:39 yssingh ship $ */
3
4 g_pkg_name VARCHAR2(30) := 'INV_TXN_MANAGER_PUB';
5 g_interface_id NUMBER;
6 g_tree_id NUMBER;
7 --------------------------------------------------
8 -- Private Procedures and Functions
9 --------------------------------------------------
10
11 /** Following portion of the code is the common objects DECLARATION/DEFINITION
12 that are used in the Package **/
13 l_error_code VARCHAR2 (3000);
14 l_error_exp VARCHAR2 (3000);
15 l_debug NUMBER;
16
17 TYPE seg_rec_type IS RECORD (
18 colname VARCHAR2 (30)
19 , colvalue VARCHAR2 (150)
20 );
21
22 TYPE bool_array IS TABLE OF NUMBER
23 INDEX BY BINARY_INTEGER;
24
25 --client_info_org_id NUMBER := -1;
26 --pjm_installed NUMBER := -1;
27 ts_default NUMBER := 1;
28 ts_save_only NUMBER := 2;
29 ts_process NUMBER := 3;
30 salorder NUMBER := 2;
31 intorder NUMBER := 8;
32 job_schedule NUMBER := 5;
33 mds_relief NUMBER := 1;
34 mps_relief NUMBER := 2;
35 r_work_order NUMBER := 1;
36 r_purch_order NUMBER := 2;
37 r_sales_order NUMBER := 3;
38 to_be_processed NUMBER := 2;
39 not_to_be_processed NUMBER := 1;
40 --moved this to INVTXGGS.pls.
41 --gi_flow_schedule NUMBER := 0 ;
42 g_true NUMBER := 1;
43 g_false NUMBER := 0;
44 g_userid NUMBER;
45
46 /*FUNCTION getitemid( itemid OUT NUMBER, orgid IN NUMBER, rowid VARCHAR2);
47 FUNCTION getacctid( acct OUT nocopy NUMBER, orgid IN NUMBER, rowid VARCHAR2);
48 FUNCTION setorgclientinfo(orgid IN NUMBER);
49 FUNCTION getlocid(locid OUT nocopy NUMBER, orgid IN NUMBER, subinv NUMBER,
50 rowid VARCHAR2, locctrl NUMBER);
51 FUNCTION getxlocid(locid OUT nocopy NUMBER, orgid IN NUMBER, subinv IN VARCHAR2,
52 rowid IN VARCHAR2, locctrl IN NUMBER);
53 FUNCTION getsrcid(trxsrc OUT nocopy NUMBER, srctype IN NUMBER, orgid IN NUMBER,
54 rowid IN VARCHAR2);
55 PROCEDURE errupdate(rowid IN VARCHAR2);
56 FUNCTION lotcheck(rowid IN VARCHAR2, orgid IN NUMBER, itemid IN NUMBER, intid IN NUMBER,
57 priuom IN VARCHAR2, trxuom VARCHAR2, lotuniq IN NUMBER,
58 shlfcode IN NUMBER, shlfdays IN NUMBER, serctrl IN NUMBER,
59 srctype IN NUMBER, acttype IN NUMBER);
60 FUNCTION validate_loc_for_project(ltv_locid IN NUMBER, ltv_orgid IN NUMBER,
61 ltv_srctype IN NUMBER, ltv_trxact IN NUMBER,
62 ltv_trx_src_id IN NUMBER, tev_flow_schedule IN NUMBER);
63 FUNCTION validate_unit_number(unit_number IN NUMBER, orgid IN NUMBER,
64 itemid IN NUMBER, srctype IN NUMBER, acttype IN NUMBER);
65 */
66 TYPE seg_arr_type IS TABLE OF seg_rec_type
67 INDEX BY BINARY_INTEGER;
68
69 --TYPE segment_array IS TABLE OF segment_rec_type INDEX BY BINARY_INTEGER;
70 TYPE segment_array IS TABLE OF VARCHAR2 (200);
71
72
73
74 /******************************************************************
75 *
76 * loaderrmsg
77 *
78 ******************************************************************/
79 PROCEDURE loaderrmsg (mesg1 IN VARCHAR2, mesg2 IN VARCHAR2)
80 IS
81 BEGIN
82 fnd_message.set_name ('INV', mesg1);
83 l_error_code := fnd_message.get;
84 fnd_message.set_name ('INV', mesg2);
85 l_error_exp := fnd_message.get;
86 END;
87
88 /*******************************************************************
89 * LotTrxInsert(p_transaction_interface_id IN NUMBER)
90 * Added this function to process lot split, merge and translate.
91 * As part of J-dev, we will bypass this API.
92 * This API has been onbsoleted.
93 * we will use tmpinsert() to move records.
94 *******************************************************************/
95
96 /** end of lot transactions changes **/
97
98 /******************************************************************
99 * Check_Partial_Split - private procedure to check if the lot split
100 * transaction is a partial split, i.e., there are remaining qty
101 * in the parent lots. In this case, we need to insert additional
102 * record in mmtt for the remaining qty
103 * This procedure assumes that the primary qty is already calculated
104 * and the qty comparison is done with the primary qty.
105 * This procedure is called after calling LotTrxInsert
106 * As part of J-dev, we will use tmpInsert to
107 * move lot transaction records from MTI to MMTT. (also for I)
108 * Some changes have been made in this API for I + J, to enable bulk
109 * insert. do not re-insert the parent transaction.
110 * do not insert into MMTT here, but into MTI only, if we are
111 * creating a new record for this transaction
112 *
113 * CHANGES FOR OSFM SUPPORT FOR SERIALIZED LOT ITEMS:
114 * Store all the resulting serials into l_rs_serial_tbl.
115 * Loop through the source Serials. If the serial is not present in
116 * l_rs_serial_tbl then add the serial to p_rem_serial_tbl.
117 * End Loop
118 * Loop through the p_rem_serial_tbl
119 * insert MSNI for that serial
120 * End Loop
121 * Update the MTLI with serial_txn_temp_id for the serials inserted
122 * into MSNI.
123 *******************************************************************/
124
125
126 FUNCTION check_partial_split (p_parent_id IN NUMBER, p_current_index IN NUMBER)
127 RETURN BOOLEAN
128 IS
129 CURSOR mti_csr (p_interface_id NUMBER)
130 IS
131 SELECT mti.transaction_header_id
132 , mti.acct_period_id
133 , mti.distribution_account_id
134 , mti.transaction_interface_id
135 , mti.transaction_type_id
136 , mti.source_code
137 , mti.source_line_id
138 , mti.source_header_id
139 , mti.inventory_item_id
140 , mti.revision
141 , mti.organization_id
142 , mti.subinventory_code
143 , mti.locator_id
144 , mti.transaction_quantity
145 , mti.primary_quantity
146 , mti.transaction_uom
147 , mti.lpn_id
148 , mti.transfer_lpn_id
149 , mti.cost_group_id
150 , mti.transaction_source_type_id
151 , mti.transaction_action_id
152 , mti.parent_id
153 , mti.created_by
154 , mtli.lot_number
155 , mtli.lot_expiration_date
156 , mtli.description
157 , mtli.vendor_id
158 , mtli.supplier_lot_number
159 , mtli.territory_code
160 , mtli.grade_code
161 , mtli.origination_date
162 , mtli.date_code
163 , mtli.status_id
164 , mtli.change_date
165 , mtli.age
166 , mtli.retest_date
167 , mtli.maturity_date
168 , mtli.lot_attribute_category
169 , mtli.item_size
170 , mtli.color
171 , mtli.volume
172 , mtli.volume_uom
173 , mtli.place_of_origin
174 , mtli.best_by_date
175 , mtli.LENGTH
176 , mtli.length_uom
177 , mtli.recycled_content
178 , mtli.thickness
179 , mtli.thickness_uom
180 , mtli.width
181 , mtli.width_uom
182 , mtli.curl_wrinkle_fold
183 , mtli.c_attribute1
184 , mtli.c_attribute2
185 , mtli.c_attribute3
186 , mtli.c_attribute4
187 , mtli.c_attribute5
188 , mtli.c_attribute6
189 , mtli.c_attribute7
190 , mtli.c_attribute8
191 , mtli.c_attribute9
192 , mtli.c_attribute10
193 , mtli.c_attribute11
194 , mtli.c_attribute12
195 , mtli.c_attribute13
196 , mtli.c_attribute14
197 , mtli.c_attribute15
198 , mtli.c_attribute16
199 , mtli.c_attribute17
200 , mtli.c_attribute18
201 , mtli.c_attribute19
202 , mtli.c_attribute20
203 , mtli.d_attribute1
204 , mtli.d_attribute2
205 , mtli.d_attribute3
206 , mtli.d_attribute4
207 , mtli.d_attribute5
208 , mtli.d_attribute6
209 , mtli.d_attribute7
210 , mtli.d_attribute8
211 , mtli.d_attribute9
212 , mtli.d_attribute10
213 , mtli.n_attribute1
214 , mtli.n_attribute2
215 , mtli.n_attribute3
216 , mtli.n_attribute4
217 , mtli.n_attribute5
218 , mtli.n_attribute6
219 , mtli.n_attribute7
220 , mtli.n_attribute8
221 , mtli.n_attribute9
222 , mtli.n_attribute10
223 , mtli.attribute1
224 , mtli.attribute2
225 , mtli.attribute3
226 , mtli.attribute4
227 , mtli.attribute5
228 , mtli.attribute6
229 , mtli.attribute7
230 , mtli.attribute8
231 , mtli.attribute9
232 , mtli.attribute10
233 , mtli.attribute11
234 , mtli.attribute12
235 , mtli.attribute13
236 , mtli.attribute14
237 , mtli.attribute15
238 , mtli.attribute_category
239 , mtli.parent_object_type --R12 Genealogy enhancements
240 , mtli.parent_object_id --R12 Genealogy enhancements
241 , mtli.parent_object_number --R12 Genealogy enhancements
242 , mtli.parent_item_id --R12 Genealogy enhancements
243 , mtli.parent_object_type2 --R12 Genealogy enhancements
244 , mtli.parent_object_id2 --R12 Genealogy enhancements
245 , mtli.parent_object_number2 --R12 Genealogy enhancements
246 , msi.description item_description
247 , msi.location_control_code
248 , msi.restrict_subinventories_code
249 , msi.restrict_locators_code
250 , msi.revision_qty_control_code
251 , msi.primary_uom_code
252 , msi.shelf_life_code
253 , msi.shelf_life_days
254 , msi.allowed_units_lookup_code
255 , mti.transaction_batch_id
256 , mti.transaction_batch_seq
257 , mti.kanban_card_id
258 , mti.transaction_mode --J-dev
259 FROM mtl_transactions_interface mti
260 , mtl_transaction_lots_interface mtli
261 , mtl_system_items_b msi
262 WHERE mti.transaction_interface_id = p_interface_id
263 AND mti.transaction_interface_id = mtli.transaction_interface_id
264 AND mti.organization_id = msi.organization_id
265 AND mti.inventory_item_id = msi.inventory_item_id
266 AND mti.process_flag = 1;
267
268 CURSOR msni_csr (p_serial_number VARCHAR2, p_parent_id NUMBER)
269 IS
270 SELECT transaction_interface_id
271 , last_update_login
272 , created_by
273 , last_updated_by
274 , request_id
275 , program_application_id
276 , program_id
277 , program_update_date
278 , vendor_serial_number
279 , vendor_lot_number
280 , fm_serial_number
281 , to_serial_number
282 , parent_serial_number
283 , serial_attribute_category
284 , c_attribute1
285 , c_attribute2
286 , c_attribute3
287 , c_attribute4
288 , c_attribute5
289 , c_attribute6
290 , c_attribute7
291 , c_attribute8
292 , c_attribute9
293 , c_attribute10
294 , c_attribute11
295 , c_attribute12
296 , c_attribute13
297 , c_attribute14
298 , c_attribute15
299 , c_attribute16
300 , c_attribute17
301 , c_attribute18
302 , c_attribute19
303 , c_attribute20
304 , d_attribute1
305 , d_attribute2
306 , d_attribute3
307 , d_attribute4
308 , d_attribute5
309 , d_attribute6
310 , d_attribute7
311 , d_attribute8
312 , d_attribute9
313 , d_attribute10
314 , n_attribute1
315 , n_attribute2
316 , n_attribute3
317 , n_attribute4
318 , n_attribute5
319 , n_attribute6
320 , n_attribute7
321 , n_attribute8
322 , n_attribute9
323 , n_attribute10
324 , attribute_category
325 , attribute1
326 , attribute2
327 , attribute3
328 , attribute4
329 , attribute5
330 , attribute6
331 , attribute7
332 , attribute8
333 , attribute9
334 , attribute10
335 , attribute11
336 , attribute12
337 , attribute13
338 , attribute14
339 , attribute15
340 , status_id
341 , territory_code
342 , time_since_new
343 , cycles_since_new
344 , time_since_overhaul
345 , cycles_since_overhaul
346 , time_since_repair
347 , cycles_since_repair
348 , time_since_visit
349 , cycles_since_visit
350 , time_since_mark
351 , cycles_since_mark
352 , number_of_repairs
353 , parent_object_type --R12 Genealogy enhancements
354 , parent_object_id --R12 Genealogy enhancements
355 , parent_object_number --R12 Genealogy enhancements
356 , parent_item_id --R12 Genealogy enhancements
357 , parent_object_type2 --R12 Genealogy enhancements
358 , parent_object_id2 --R12 Genealogy enhancements
359 , parent_object_number2 --R12 Genealogy enhancements
360 FROM mtl_serial_numbers_interface msni
361 WHERE msni.transaction_interface_id =
362 (SELECT serial_transaction_temp_id
363 FROM mtl_transaction_lots_interface mtli
364 WHERE mtli.transaction_interface_id = p_parent_id)
365 AND inv_serial_number_pub.get_serial_diff (msni.fm_serial_number
366 , p_serial_number
367 ) <> -1
368 AND inv_serial_number_pub.get_serial_diff (msni.fm_serial_number
369 , NVL (msni.to_serial_number
370 , msni.fm_serial_number
371 )
372 ) >=
373 inv_serial_number_pub.get_serial_diff (msni.fm_serial_number
374 , p_serial_number
375 );
376 l_msni_csr msni_csr%ROWTYPE;
377
378 /*This cursor will get the serials for the resulting lots*/
379 CURSOR msni_rs_serials_csr (p_parent_id NUMBER)
380 IS
381 SELECT fm_serial_number
382 , NVL (to_serial_number, fm_serial_number) to_serial_number
383 FROM mtl_serial_numbers_interface msni
384 WHERE msni.transaction_interface_id IN (
385 SELECT serial_transaction_temp_id
386 FROM mtl_transaction_lots_interface mtli
387 WHERE mtli.transaction_interface_id IN (
388 SELECT transaction_interface_id
389 FROM mtl_transactions_interface mti
390 WHERE mti.parent_id = p_parent_id
391 AND mti.transaction_interface_id <> mti.parent_id));
392
393 l_msni_rs_serials_csr msni_rs_serials_csr%ROWTYPE;
394
395 /*This cursor will get the serials for the starting lots*/
396 CURSOR msni_st_serials_csr (p_parent_id NUMBER)
397 IS
398 SELECT fm_serial_number
399 , NVL (to_serial_number, fm_serial_number) to_serial_number
400 FROM mtl_serial_numbers_interface msni
401 WHERE msni.transaction_interface_id =
402 (SELECT serial_transaction_temp_id
403 FROM mtl_transaction_lots_interface mtli
404 WHERE mtli.transaction_interface_id =
405 (SELECT transaction_interface_id
406 FROM mtl_transactions_interface mti
407 WHERE mti.parent_id = p_parent_id
408 AND mti.transaction_interface_id = mti.parent_id));
409
410 l_msni_st_serials_csr msni_st_serials_csr%ROWTYPE;
411 l_frm_serial VARCHAR2 (30);
412 l_to_serial VARCHAR2 (30);
413 l_st_serial_tbl inv_lot_trx_validation_pub.serial_number_table;
414 l_rs_serial_tbl inv_lot_trx_validation_pub.serial_number_table;
415 l_rem_serial_tbl inv_lot_trx_validation_pub.serial_number_table;
416 l_count NUMBER := 0;
417 l_partial_total_qty NUMBER := 0;
418 l_remaining_qty NUMBER := 0;
419 l_split_qty NUMBER := 0;
420 l_split_uom VARCHAR2 (3);
421 l_transaction_interface_id NUMBER; --J-dev
422 l_serial_code NUMBER;
423 l_serial_diff NUMBER;
424 l_sysdate DATE;
425 l_rem_var_index mtl_serial_numbers.serial_number%TYPE;
426 l_next_serial VARCHAR2(30);
427 l_old_serial VARCHAR2(30);
428 l_sequence NUMBER;
429 BEGIN
430 IF (l_debug = 1)
431 THEN
432 inv_log_util.TRACE ('l_breadcrumb 10', 'INV_TXN_MANAGER_PUB', '9');
433 END IF;
434 l_sysdate := SYSDATE;
435 SELECT COUNT (parent_id)
436 INTO l_count
437 FROM mtl_transactions_interface
438 WHERE parent_id = p_parent_id;
439
440 SELECT ABS (primary_quantity)
441 INTO l_split_qty
442 FROM mtl_transactions_interface
443 WHERE transaction_interface_id = p_parent_id;
444
445 SELECT SUM (ABS (primary_quantity))
446 INTO l_partial_total_qty
447 FROM mtl_transactions_interface
448 WHERE parent_id = p_parent_id AND transaction_interface_id <> p_parent_id;
449
450 l_remaining_qty := l_split_qty - l_partial_total_qty;
451
452 IF (l_debug = 1)
453 THEN
454 inv_log_util.TRACE ('l_remaining_qty '|| l_remaining_qty, 'INV_TXN_MANAGER_PUB', '9');
455 inv_log_util.TRACE ('l_partial_total_qty '|| l_partial_total_qty, 'INV_TXN_MANAGER_PUB', '9');
456 inv_log_util.TRACE ('l_split_qty '|| l_split_qty, 'INV_TXN_MANAGER_PUB', '9');
457 inv_log_util.TRACE ('p_current_index '|| p_current_index, 'INV_TXN_MANAGER_PUB', '9');
458 inv_log_util.TRACE ('l_count '|| l_count, 'INV_TXN_MANAGER_PUB', '9');
459 END IF;
460
461 IF (p_current_index = l_count AND l_remaining_qty > 0)
462 THEN
463 SELECT mtl_material_transactions_s.NEXTVAL
464 INTO l_transaction_interface_id --J-dev
465 FROM DUAL;
466
467 --shuld execute only once
468 IF (l_debug = 1)
469 THEN
470 inv_log_util.TRACE ('l_breadcrumb 20', 'INV_TXN_MANAGER_PUB', '9');
471 END IF;
472
473 FOR l_mti_csr IN mti_csr (p_parent_id)
474 LOOP
475 IF (l_debug = 1)
476 THEN
477 inv_log_util.TRACE ('l_breadcrumb 30', 'INV_TXN_MANAGER_PUB', '9');
478 END IF;
479
480 INSERT INTO mtl_transactions_interface
481 (transaction_header_id
482 , transaction_interface_id
483 , transaction_mode
484 , lock_flag
485 , source_code
486 , source_line_id
487 , source_header_id
488 , process_flag
489 , last_update_date
490 , last_updated_by
491 , creation_date
492 , created_by
493 , last_update_login
494 , request_id
495 , program_application_id
496 , program_id
497 , program_update_date
498 , inventory_item_id
499 , revision
500 , organization_id
501 , subinventory_code
502 , locator_id
503 , transaction_quantity
504 , primary_quantity
505 , transaction_uom
506 , transaction_type_id
507 , transaction_action_id
508 , transaction_source_type_id
509 , transaction_date
510 , acct_period_id
511 , distribution_account_id
512 ,
513 /*item_description ,
514 item_location_control_code ,
515 item_restrict_subinv_code
516 ,item_restrict_locators_code ,
517 item_revision_qty_control_code ,
518 item_primary_uom_code
519 ,item_shelf_life_code ,
520 item_shelf_life_days ,
521 item_lot_control_code
522 ,item_serial_control_code ,
523 allowed_units_lookup_code,*/--J-dev not in MTI
524 parent_id
525 , --J-dev
526 lpn_id
527 , transfer_lpn_id
528 , cost_group_id
529 , transaction_batch_id
530 , transaction_batch_seq
531 , kanban_card_id
532 )
533 VALUES (l_mti_csr.transaction_header_id
534 , l_transaction_interface_id
535 , --J-dev
536 l_mti_csr.transaction_mode /*2722754 */
537 , 2
538 , l_mti_csr.source_code
539 , l_mti_csr.source_line_id
540 , l_mti_csr.source_header_id
541 , --J-dev
542 1
543 , --J-dev
544 l_sysdate
545 , l_mti_csr.created_by
546 , l_sysdate
547 , l_mti_csr.created_by
548 , l_mti_csr.created_by
549 , NULL
550 , NULL
551 , NULL
552 , NULL
553 , l_mti_csr.inventory_item_id
554 , l_mti_csr.revision
555 , l_mti_csr.organization_id
556 , l_mti_csr.subinventory_code
557 , l_mti_csr.locator_id
558 , l_remaining_qty
559 , l_remaining_qty
560 , l_mti_csr.primary_uom_code
561 , l_mti_csr.transaction_type_id
562 , l_mti_csr.transaction_action_id
563 , l_mti_csr.transaction_source_type_id
564 , l_sysdate
565 , l_mti_csr.acct_period_id
566 , l_mti_csr.distribution_account_id
567 ,
568 /*l_mti_csr.item_description,
569 l_mti_csr.location_control_code,
570 l_mti_csr.restrict_subinventories_code,
571 l_mti_csr.restrict_locators_code,
572 l_mti_csr.revision_qty_control_code,
573 l_mti_csr.primary_uom_code,
574 l_mti_csr.shelf_life_code,
575 l_mti_csr.shelf_life_days,
576 2,
577 1,
578 l_mti_csr.allowed_units_lookup_code,*/--J-dev Not in MTI
579 l_mti_csr.parent_id
580 , null--l_mti_csr.lpn_id
581 , l_mti_csr.lpn_id
582 , l_mti_csr.cost_group_id
583 , l_mti_csr.transaction_batch_id
584 , l_mti_csr.transaction_batch_seq
585 , l_mti_csr.kanban_card_id
586 );
587
588 IF (l_debug = 1)
589 THEN
590 inv_log_util.TRACE ('l_breadcrumb 40', 'INV_TXN_MANAGER_PUB', '9');
591 END IF;
592
593 INSERT INTO mtl_transaction_lots_interface
594 (transaction_interface_id --J-dev
595 , last_update_date
596 , last_updated_by
597 , creation_date
598 , created_by
599 , last_update_login
600 , request_id
601 , program_application_id
602 , program_id
603 , program_update_date
604 , transaction_quantity
605 , primary_quantity
606 , lot_number
607 , lot_expiration_date
608 , description
609 , vendor_id
610 , supplier_lot_number
611 , territory_code
612 , grade_code
613 , origination_date
614 , date_code
615 , status_id
616 , change_date
617 , age
618 , retest_date
619 , maturity_date
620 , lot_attribute_category
621 , item_size
622 , color
623 , volume
624 , volume_uom
625 , place_of_origin
626 , best_by_date
627 , LENGTH
628 , length_uom
629 , recycled_content
630 , thickness
631 , thickness_uom
632 , width
633 , width_uom
634 , curl_wrinkle_fold
635 , c_attribute1
636 , c_attribute2
637 , c_attribute3
638 , c_attribute4
639 , c_attribute5
640 , c_attribute6
641 , c_attribute7
642 , c_attribute8
643 , c_attribute9
644 , c_attribute10
645 , c_attribute11
646 , c_attribute12
647 , c_attribute13
648 , c_attribute14
649 , c_attribute15
650 , c_attribute16
651 , c_attribute17
652 , c_attribute18
653 , c_attribute19
654 , c_attribute20
655 , d_attribute1
656 , d_attribute2
657 , d_attribute3
658 , d_attribute4
659 , d_attribute5
660 , d_attribute6
661 , d_attribute7
662 , d_attribute8
663 , d_attribute9
664 , d_attribute10
665 , n_attribute1
666 , n_attribute2
667 , n_attribute3
668 , n_attribute4
669 , n_attribute5
670 , n_attribute6
671 , n_attribute7
672 , n_attribute8
673 , n_attribute9
674 , n_attribute10
675 , attribute1
676 , attribute2
677 , attribute3
678 , attribute4
679 , attribute5
680 , attribute6
681 , attribute7
682 , attribute8
683 , attribute9
684 , attribute10
685 , attribute11
686 , attribute12
687 , attribute13
688 , attribute14
689 , attribute15
690 , attribute_category
691 , parent_object_type --R12 Genealogy enhancements
692 , parent_object_id --R12 Genealogy enhancements
693 , parent_object_number --R12 Genealogy enhancements
694 , parent_item_id --R12 Genealogy enhancements
695 , parent_object_type2 --R12 Genealogy enhancements
696 , parent_object_id2 --R12 Genealogy enhancements
697 , parent_object_number2 --R12 Genealogy enhancements
698 )
699 VALUES (l_transaction_interface_id
700 , l_sysdate
701 , l_mti_csr.created_by
702 , l_sysdate
703 , l_mti_csr.created_by
704 , l_mti_csr.created_by
705 , NULL
706 , NULL
707 , NULL
708 , NULL
709 , l_remaining_qty
710 , l_remaining_qty
711 , l_mti_csr.lot_number
712 , l_mti_csr.lot_expiration_date
713 , l_mti_csr.description
714 , l_mti_csr.vendor_id
715 , l_mti_csr.supplier_lot_number
716 , l_mti_csr.territory_code
717 , l_mti_csr.grade_code
718 , l_mti_csr.origination_date
719 , l_mti_csr.date_code
720 , l_mti_csr.status_id
721 , l_mti_csr.change_date
722 , l_mti_csr.age
723 , l_mti_csr.retest_date
724 , l_mti_csr.maturity_date
725 , l_mti_csr.lot_attribute_category
726 , l_mti_csr.item_size
727 , l_mti_csr.color
728 , l_mti_csr.volume
729 , l_mti_csr.volume_uom
730 , l_mti_csr.place_of_origin
731 , l_mti_csr.best_by_date
732 , l_mti_csr.LENGTH
733 , l_mti_csr.length_uom
734 , l_mti_csr.recycled_content
735 , l_mti_csr.thickness
736 , l_mti_csr.thickness_uom
737 , l_mti_csr.width
738 , l_mti_csr.width_uom
739 , l_mti_csr.curl_wrinkle_fold
740 , l_mti_csr.c_attribute1
741 , l_mti_csr.c_attribute2
742 , l_mti_csr.c_attribute3
743 , l_mti_csr.c_attribute4
744 , l_mti_csr.c_attribute5
745 , l_mti_csr.c_attribute6
746 , l_mti_csr.c_attribute7
747 , l_mti_csr.c_attribute8
748 , l_mti_csr.c_attribute9
749 , l_mti_csr.c_attribute10
750 , l_mti_csr.c_attribute11
751 , l_mti_csr.c_attribute12
752 , l_mti_csr.c_attribute13
753 , l_mti_csr.c_attribute14
754 , l_mti_csr.c_attribute15
755 , l_mti_csr.c_attribute16
756 , l_mti_csr.c_attribute17
757 , l_mti_csr.c_attribute18
758 , l_mti_csr.c_attribute19
759 , l_mti_csr.c_attribute20
760 , l_mti_csr.d_attribute1
761 , l_mti_csr.d_attribute2
762 , l_mti_csr.d_attribute3
763 , l_mti_csr.d_attribute4
764 , l_mti_csr.d_attribute5
765 , l_mti_csr.d_attribute6
766 , l_mti_csr.d_attribute7
767 , l_mti_csr.d_attribute8
768 , l_mti_csr.d_attribute9
769 , l_mti_csr.d_attribute10
770 , l_mti_csr.n_attribute1
771 , l_mti_csr.n_attribute2
772 , l_mti_csr.n_attribute3
773 , l_mti_csr.n_attribute4
774 , l_mti_csr.n_attribute5
775 , l_mti_csr.n_attribute6
776 , l_mti_csr.n_attribute7
777 , l_mti_csr.n_attribute8
778 , l_mti_csr.n_attribute9
779 , l_mti_csr.n_attribute10
780 , l_mti_csr.attribute1
781 , l_mti_csr.attribute2
782 , l_mti_csr.attribute3
783 , l_mti_csr.attribute4
784 , l_mti_csr.attribute5
785 , l_mti_csr.attribute6
786 , l_mti_csr.attribute7
787 , l_mti_csr.attribute8
788 , l_mti_csr.attribute9
789 , l_mti_csr.attribute10
790 , l_mti_csr.attribute11
791 , l_mti_csr.attribute12
792 , l_mti_csr.attribute13
793 , l_mti_csr.attribute14
794 , l_mti_csr.attribute15
795 , l_mti_csr.attribute_category
796 , l_mti_csr.parent_object_type --R12 Genealogy enhancements
797 , l_mti_csr.parent_object_id --R12 Genealogy enhancements
798 , l_mti_csr.parent_object_number --R12 Genealogy enhancements
799 , l_mti_csr.parent_item_id --R12 Genealogy enhancements
800 , l_mti_csr.parent_object_type2 --R12 Genealogy enhancements
801 , l_mti_csr.parent_object_id2 --R12 Genealogy enhancements
802 , l_mti_csr.parent_object_number2 --R12 Genealogy enhancements
803 );
804
805 IF (l_debug = 1)
806 THEN
807 inv_log_util.TRACE ('l_breadcrumb 50', 'INV_TXN_MANAGER_PUB', '9');
808 END IF;
809
810 /*loop through the serials that have been left out ....
811 try copying everything from the parent MSNIs*/
812 BEGIN
813 SELECT serial_number_control_code
814 INTO l_serial_code
815 FROM mtl_system_items
816 WHERE inventory_item_id = l_mti_csr.inventory_item_id
817 AND organization_id = l_mti_csr.organization_id;
818
819 IF (l_debug = 1)
820 THEN
821 inv_log_util.TRACE ('Serial control code => ' || l_serial_code
822 , 'INV_TXN_MANAGER_PUB'
823 , '9'
824 );
825 inv_log_util.TRACE ('l_breadcrumb 60', 'INV_TXN_MANAGER_PUB', '9');
826 END IF;
827
828 IF (l_serial_code IN (2, 5))
829 THEN
830 IF (l_debug = 1)
831 THEN
832 inv_log_util.TRACE ('l_breadcrumb 70', 'INV_TXN_MANAGER_PUB'
833 , '9');
834 END IF;
835
836 FOR l_msni_rs_serials_csr IN msni_rs_serials_csr (p_parent_id)
837 LOOP
838 IF (l_debug = 1)
839 THEN
840 inv_log_util.TRACE ('l_breadcrumb 80'
841 , 'INV_TXN_MANAGER_PUB'
842 , '9'
843 );
844 END IF;
845
846 l_serial_diff :=
847 inv_serial_number_pub.get_serial_diff
848 (l_msni_rs_serials_csr.fm_serial_number
849 , l_msni_rs_serials_csr.to_serial_number
850 );
851
852 IF (l_debug = 1)
853 THEN
854 inv_log_util.TRACE ('Serial diff => ' || l_serial_diff
855 , 'INV_TXN_MANAGER_PUB'
856 , '9'
857 );
858 END IF;
859
860 IF (l_serial_diff = -1)
861 THEN
862 IF (l_debug = 1)
863 THEN
864 inv_log_util.TRACE ('Error in get_serial_diff '
865 , 'INV_TXN_MANAGER_PUB'
866 , '9'
867 );
868 END IF;
869
870 fnd_message.set_name ('INV', 'INV_INVALID_SERIAL_RANGE');
871 fnd_msg_pub.ADD;
872 RAISE fnd_api.g_exc_unexpected_error;
873 END IF;
874
875 l_next_serial := l_msni_rs_serials_csr.fm_serial_number;
876
877 FOR i IN 1 .. l_serial_diff
878 LOOP
879 IF (l_debug = 1)
880 THEN
881 inv_log_util.TRACE ('l_breadcrumb 90'
882 , 'INV_TXN_MANAGER_PUB'
883 , '9'
884 );
885 END IF;
886
887 l_rs_serial_tbl (l_next_serial) := l_next_serial;
888 l_old_serial := l_next_serial;
889 l_next_serial :=
890 inv_serial_number_pub.increment_ser_num (l_old_serial
891 , 1);
892
893 IF (l_debug = 1)
894 THEN
895 inv_log_util.TRACE ('l_next_serial => ' || l_next_serial
896 , 'INV_TXN_MANAGER_PUB'
897 , '9'
898 );
899 END IF;
900
901 IF (l_old_serial = l_next_serial)
902 THEN
903 IF (l_debug = 1)
904 THEN
905 inv_log_util.TRACE ('Error in increment_serial_number'
906 , 'INV_TXN_MANAGER_PUB'
907 , '9'
908 );
909 END IF;
910
911 fnd_message.set_name ('INV', 'INVALID_SERIAL_NUMBER');
912 fnd_msg_pub.ADD;
913 RAISE fnd_api.g_exc_unexpected_error;
914 END IF;
915 END LOOP;
916 END LOOP;
917
918 /*Get the serials in the source lot and see if they are present in l_rs_serial_tbl
919 *If not then add them to l_rem_serial_tbl
920 */
921 FOR l_msni_st_serials_csr IN msni_st_serials_csr (p_parent_id)
922 LOOP
923 IF (l_debug = 1)
924 THEN
925 inv_log_util.TRACE ('l_breadcrumb 100'
926 , 'INV_TXN_MANAGER_PUB'
927 , '9'
928 );
929 END IF;
930
931 l_serial_diff :=
932 inv_serial_number_pub.get_serial_diff
933 (l_msni_st_serials_csr.fm_serial_number
934 , l_msni_st_serials_csr.to_serial_number
935 );
936
937 IF (l_debug = 1)
938 THEN
939 inv_log_util.TRACE ('Serial diff => ' || l_serial_diff
940 , 'INV_TXN_MANAGER_PUB'
941 , '9'
942 );
943 END IF;
944
945 IF (l_serial_diff = -1)
946 THEN
947 IF (l_debug = 1)
948 THEN
949 inv_log_util.TRACE ('Error in get_serial_diff '
950 , 'INV_TXN_MANAGER_PUB'
951 , '9'
952 );
953 END IF;
954
955 fnd_message.set_name ('INV', 'INV_INVALID_SERIAL_RANGE');
956 fnd_msg_pub.ADD;
957 RAISE fnd_api.g_exc_unexpected_error;
958 END IF;
959
960 l_next_serial := l_msni_st_serials_csr.fm_serial_number;
961
962 FOR i IN 1 .. l_serial_diff
963 LOOP
964 IF (l_debug = 1)
965 THEN
966 inv_log_util.TRACE ('l_breadcrumb 110'
967 , 'INV_TXN_MANAGER_PUB'
968 , '9'
969 );
970 inv_log_util.TRACE ('l_next_serial => '|| l_next_serial
971 , 'INV_TXN_MANAGER_PUB'
972 , '9'
973 );
974 END IF;
975
976 BEGIN
977 IF (l_rs_serial_tbl (l_next_serial) IS NULL)
978 THEN
979 l_rem_serial_tbl (l_next_serial) := l_next_serial;
980 END IF;
981 EXCEPTION
982 WHEN NO_DATA_FOUND THEN
983 l_rem_serial_tbl (l_next_serial) := l_next_serial;
984 IF (l_debug = 1)
985 THEN
986 inv_log_util.TRACE ( 'Serial => '
987 || l_next_serial
988 || ' is not present'
989 , 'INV_TXN_MANAGER_PUB'
990 , '9'
991 );
992 END IF;
993 END;
994 l_old_serial := l_next_serial;
995 l_next_serial :=
996 inv_serial_number_pub.increment_ser_num (l_old_serial
997 , 1);
998
999 IF (l_old_serial = l_next_serial)
1000 THEN
1001 IF (l_debug = 1)
1002 THEN
1003 inv_log_util.TRACE ('Error in increment_serial_number '
1004 , 'INV_TXN_MANAGER_PUB'
1005 , '9'
1006 );
1007 END IF;
1008
1009 fnd_message.set_name ('INV', 'INVALID_SERIAL_NUMBER');
1010 fnd_msg_pub.ADD;
1011 RAISE fnd_api.g_exc_unexpected_error;
1012 END IF;
1013 END LOOP;
1014 END LOOP;
1015
1016 l_rem_var_index := l_rem_serial_tbl.FIRST;
1017
1018 SELECT mtl_material_transactions_s.NEXTVAL
1019 INTO l_sequence
1020 FROM DUAL;
1021
1022 IF (l_debug = 1)
1023 THEN
1024 inv_log_util.TRACE ('l_breadcrumb 120'
1025 , 'INV_TXN_MANAGER_PUB'
1026 , '9'
1027 );
1028 END IF;
1029
1030 FOR i IN 1 .. l_rem_serial_tbl.COUNT
1031 LOOP
1032 OPEN msni_csr (l_rem_serial_tbl (l_rem_var_index), p_parent_id);
1033
1034 FETCH msni_csr
1035 INTO l_msni_csr;
1036
1037 IF (msni_csr%ROWCOUNT = 0)
1038 THEN
1039 IF (l_debug = 1)
1040 THEN
1041 inv_log_util.TRACE ('No data found in msni_csr '
1042 , 'INV_TXN_MANAGER_PUB'
1043 , '9'
1044 );
1045 END IF;
1046
1047 RAISE fnd_api.g_exc_unexpected_error;
1048 END IF;
1049
1050 IF (l_debug = 1)
1051 THEN
1052 inv_log_util.TRACE ('Inseritng into MSNI '
1053 , 'INV_TXN_MANAGER_PUB'
1054 , '9'
1055 );
1056 END IF;
1057
1058 INSERT INTO mtl_serial_numbers_interface
1059 (transaction_interface_id
1060 , last_update_date
1061 , last_updated_by
1062 , creation_date
1063 , created_by
1064 , last_update_login
1065 , request_id
1066 , program_application_id
1067 , program_id
1068 , program_update_date
1069 , vendor_serial_number
1070 , vendor_lot_number
1071 , fm_serial_number
1072 , to_serial_number
1073 , parent_serial_number
1074 , serial_attribute_category
1075 , c_attribute1
1076 , c_attribute2
1077 , c_attribute3
1078 , c_attribute4
1079 , c_attribute5
1080 , c_attribute6
1081 , c_attribute7
1082 , c_attribute8
1083 , c_attribute9
1084 , c_attribute10
1085 , c_attribute11
1086 , c_attribute12
1087 , c_attribute13
1088 , c_attribute14
1089 , c_attribute15
1090 , c_attribute16
1091 , c_attribute17
1092 , c_attribute18
1093 , c_attribute19
1094 , c_attribute20
1095 , d_attribute1
1096 , d_attribute2
1097 , d_attribute3
1098 , d_attribute4
1099 , d_attribute5
1100 , d_attribute6
1101 , d_attribute7
1102 , d_attribute8
1103 , d_attribute9
1104 , d_attribute10
1105 , n_attribute1
1106 , n_attribute2
1107 , n_attribute3
1108 , n_attribute4
1109 , n_attribute5
1110 , n_attribute6
1111 , n_attribute7
1112 , n_attribute8
1113 , n_attribute9
1114 , n_attribute10
1115 , attribute_category
1116 , attribute1
1117 , attribute2
1118 , attribute3
1119 , attribute4
1120 , attribute5
1121 , attribute6
1122 , attribute7
1123 , attribute8
1124 , attribute9
1125 , attribute10
1126 , attribute11
1127 , attribute12
1128 , attribute13
1129 , attribute14
1130 , attribute15
1131 , status_id
1132 , territory_code
1133 , time_since_new
1134 , cycles_since_new
1135 , time_since_overhaul
1136 , cycles_since_overhaul
1137 , time_since_repair
1138 , cycles_since_repair
1139 , time_since_visit
1140 , cycles_since_visit
1141 , time_since_mark
1142 , cycles_since_mark
1143 , number_of_repairs
1144 , parent_object_type --R12 Genealogy enhancements
1145 , parent_object_id --R12 Genealogy enhancements
1146 , parent_object_number --R12 Genealogy enhancements
1147 , parent_item_id --R12 Genealogy enhancements
1148 , parent_object_type2 --R12 Genealogy enhancements
1149 , parent_object_id2 --R12 Genealogy enhancements
1150 , parent_object_number2 --R12 Genealogy enhancements
1151 )
1152 VALUES (l_sequence
1153 , l_sysdate
1154 , l_msni_csr.last_updated_by
1155 , l_sysdate
1156 , l_msni_csr.created_by
1157 , l_msni_csr.last_update_login
1158 , l_msni_csr.request_id
1159 , l_msni_csr.program_application_id
1160 , l_msni_csr.program_id
1161 , l_msni_csr.program_update_date
1162 , l_msni_csr.vendor_serial_number
1163 , l_msni_csr.vendor_lot_number
1164 --Serial remaining
1165 , l_rem_serial_tbl (l_rem_var_index)
1166 , l_rem_serial_tbl (l_rem_var_index)
1167 , l_msni_csr.parent_serial_number
1168 , l_msni_csr.serial_attribute_category
1169 , l_msni_csr.c_attribute1
1170 , l_msni_csr.c_attribute2
1171 , l_msni_csr.c_attribute3
1172 , l_msni_csr.c_attribute4
1173 , l_msni_csr.c_attribute5
1174 , l_msni_csr.c_attribute6
1175 , l_msni_csr.c_attribute7
1176 , l_msni_csr.c_attribute8
1177 , l_msni_csr.c_attribute9
1178 , l_msni_csr.c_attribute10
1179 , l_msni_csr.c_attribute11
1180 , l_msni_csr.c_attribute12
1181 , l_msni_csr.c_attribute13
1182 , l_msni_csr.c_attribute14
1183 , l_msni_csr.c_attribute15
1184 , l_msni_csr.c_attribute16
1185 , l_msni_csr.c_attribute17
1186 , l_msni_csr.c_attribute18
1187 , l_msni_csr.c_attribute19
1188 , l_msni_csr.c_attribute20
1189 , l_msni_csr.d_attribute1
1190 , l_msni_csr.d_attribute2
1191 , l_msni_csr.d_attribute3
1192 , l_msni_csr.d_attribute4
1193 , l_msni_csr.d_attribute5
1194 , l_msni_csr.d_attribute6
1195 , l_msni_csr.d_attribute7
1196 , l_msni_csr.d_attribute8
1197 , l_msni_csr.d_attribute9
1198 , l_msni_csr.d_attribute10
1199 , l_msni_csr.n_attribute1
1200 , l_msni_csr.n_attribute2
1201 , l_msni_csr.n_attribute3
1202 , l_msni_csr.n_attribute4
1203 , l_msni_csr.n_attribute5
1204 , l_msni_csr.n_attribute6
1205 , l_msni_csr.n_attribute7
1206 , l_msni_csr.n_attribute8
1207 , l_msni_csr.n_attribute9
1208 , l_msni_csr.n_attribute10
1209 , l_msni_csr.attribute_category
1210 , l_msni_csr.attribute1
1211 , l_msni_csr.attribute2
1212 , l_msni_csr.attribute3
1213 , l_msni_csr.attribute4
1214 , l_msni_csr.attribute5
1215 , l_msni_csr.attribute6
1216 , l_msni_csr.attribute7
1217 , l_msni_csr.attribute8
1218 , l_msni_csr.attribute9
1219 , l_msni_csr.attribute10
1220 , l_msni_csr.attribute11
1221 , l_msni_csr.attribute12
1222 , l_msni_csr.attribute13
1223 , l_msni_csr.attribute14
1224 , l_msni_csr.attribute15
1225 , l_msni_csr.status_id
1226 , l_msni_csr.territory_code
1227 , l_msni_csr.time_since_new
1228 , l_msni_csr.cycles_since_new
1229 , l_msni_csr.time_since_overhaul
1230 , l_msni_csr.cycles_since_overhaul
1231 , l_msni_csr.time_since_repair
1232 , l_msni_csr.cycles_since_repair
1233 , l_msni_csr.time_since_visit
1234 , l_msni_csr.cycles_since_visit
1235 , l_msni_csr.time_since_mark
1236 , l_msni_csr.cycles_since_mark
1237 , l_msni_csr.number_of_repairs
1238 , l_msni_csr.parent_object_type --R12 Genealogy enhancements
1239 , l_msni_csr.parent_object_id --R12 Genealogy enhancements
1240 , l_msni_csr.parent_object_number --R12 Genealogy enhancements
1241 , l_msni_csr.parent_item_id --R12 Genealogy enhancements
1242 , l_msni_csr.parent_object_type2 --R12 Genealogy enhancements
1243 , l_msni_csr.parent_object_id2 --R12 Genealogy enhancements
1244 , l_msni_csr.parent_object_number2 --R12 Genealogy enhancements
1245 );
1246
1247 IF(msni_csr%ISOPEN) THEN
1248 CLOSE msni_csr;
1249 END IF;
1250
1251 l_rem_var_index := l_rem_serial_tbl.NEXT (l_rem_var_index);
1252 END LOOP;
1253
1254 /*Need to update the MTLI with serial_txn_temP_id to connect with the MSNIs*/
1255 UPDATE mtl_transaction_lots_interface
1256 SET serial_transaction_temp_id = l_sequence
1257 WHERE transaction_interface_id = l_transaction_interface_id;
1258 END IF; --end of is_serial_controlled
1259 EXCEPTION
1260 WHEN OTHERS
1261 THEN
1262 IF (l_debug = 1)
1263 THEN
1264 inv_log_util.TRACE
1265 ('Error while inserting in MSNI check_partial_split '
1266 , 'INV_TXN_MANAGER_PUB'
1267 , '9'
1268 );
1269 END IF;
1270
1271 RETURN FALSE;
1272 END;
1273 END LOOP; --End MTI loop
1274 END IF;
1275
1276 RETURN TRUE;
1277 EXCEPTION
1278 WHEN fnd_api.g_exc_error
1279 THEN
1280 IF (l_debug = 1)
1281 THEN
1282 inv_log_util.TRACE ('SQL : ' || SUBSTR (SQLERRM, 1, 200)
1283 , 'INV_TXN_MANAGER_PUB'
1284 , '9'
1285 );
1286 inv_log_util.TRACE ('Error in check_partial_split : ' || l_error_exp
1287 , 'INV_TXN_MANAGER_PUB'
1288 , '9'
1289 );
1290 END IF;
1291
1292 RETURN FALSE;
1293 WHEN OTHERS
1294 THEN
1295 IF (l_debug = 1)
1296 THEN
1297 inv_log_util.TRACE ('SQL : ' || SUBSTR (SQLERRM, 1, 200)
1298 , 'INV_TXN_MANAGER_PUB'
1299 , '9'
1300 );
1301 inv_log_util.TRACE ('Error in check_partial_split : ' || l_error_exp
1302 , 'INV_TXN_MANAGER_PUB'
1303 , '9'
1304 );
1305 END IF;
1306
1307 RETURN FALSE;
1308 END check_partial_split;
1309
1310 /* getacctid()
1311 * moved to group API INV_TXN_MANAGER_GRP()
1312 ******************************************************************/
1313
1314 /******************************************************************
1315 -- Procedure moved to group api INV_TXN_MANAGER_GRP
1316 -- getitemid
1317 -- Description
1318 -- find the item_id using the flex field segments
1319 -- Output Parameters
1320 -- x_item_id locator or null if error occurred
1321 ******************************************************************/
1322
1323 /******************************************************************
1324 -- Procedure moved to group api INV_TXN_MANAGER_GRP
1325 -- getsrcid
1326 -- Description
1327 -- find the Source ID using the flex field segments
1328 -- Output Parameters
1329 -- x_trxsrc transaction source id or null if error occurred
1330 ******************************************************************/
1331
1332 /******************************************************************
1333 *
1334 * errupdate()
1335 *
1336 ******************************************************************/
1337 PROCEDURE errupdate (p_rowid IN VARCHAR2)
1338 IS
1339 l_userid NUMBER := -1; -- = prg_info.userid;
1340 l_reqstid NUMBER := -1; -- = prg_info.reqstid;
1341 l_applid NUMBER := -1; -- = prg_info.appid;
1342 l_progid NUMBER := -1; -- = prg_info.progid;
1343 l_loginid NUMBER := -1; --= prg_info.loginid;
1344 BEGIN
1345 -- WHENEVER NOT FOUND CONTINUE;
1346 UPDATE mtl_transactions_interface
1347 SET ERROR_CODE = SUBSTRB (l_error_code, 1, 240)
1348 , error_explanation = SUBSTRB (l_error_exp, 1, 240)
1349 , last_update_date = SYSDATE
1350 , last_updated_by = l_userid
1351 , last_update_login = l_loginid
1352 , program_update_date = SYSDATE
1353 , process_flag = 3
1354 , lock_flag = 2
1355 WHERE ROWID = p_rowid;
1356
1357 RETURN;
1358 EXCEPTION
1359 WHEN OTHERS
1360 THEN
1361 RETURN;
1362 END errupdate;
1363
1364 /******************************************************************
1365 -- Procedure (moved to group api INV_TXN_MANAGER_GRP)
1366 -- derive_segment_ids
1367 -- Description
1368 -- derive segment-ids based on segment values
1369 -- Output Parameters
1370 --
1371 ******************************************************************/
1372
1373 /******************************************************************
1374 *
1375 * validate_group
1376 * Validate a group of MTI records in a batch together
1377
1378 * J-dev (WIP related validations)
1379 * Actual implemetation is mved to INV_TXN_MANAGER_GRP(INVTXGGB.pls)
1380 * The public spec here, does not accept p_validation_level.
1381 * if p_validation_level is to be used, the group api has to be invoked.
1382 ******************************************************************/
1383 PROCEDURE validate_group (
1384 p_header_id NUMBER
1385 , x_return_status OUT NOCOPY VARCHAR2
1386 , x_msg_count OUT NOCOPY NUMBER
1387 , x_msg_data OUT NOCOPY VARCHAR2
1388 , p_userid NUMBER
1389 , p_loginid NUMBER
1390 )
1391 IS
1392 srctypeid NUMBER;
1393 tvu_flow_schedule VARCHAR2 (50);
1394 tev_scheduled_flag NUMBER;
1395 flow_schedule_children VARCHAR2 (50);
1396 l_count NUMBER;
1397 l_profile VARCHAR2 (100);
1398 exp_to_ast_allowed NUMBER;
1399 exp_type_required NUMBER;
1400 numhold NUMBER := 0;
1401 l_return_status VARCHAR2 (10) := fnd_api.g_ret_sts_success;
1402 l_msg_count NUMBER;
1403 l_msg_data VARCHAR2 (2000);
1404 BEGIN
1405 IF (l_debug IS NULL)
1406 THEN
1407 l_debug := NVL (fnd_profile.VALUE ('INV_DEBUG_TRACE'), 0);
1408 END IF;
1409
1410 inv_txn_manager_grp.validate_group
1411 (p_header_id => p_header_id
1412 , x_return_status => l_return_status
1413 , x_msg_count => l_msg_count
1414 , x_msg_data => l_msg_data
1415 , p_userid => p_userid
1416 , p_loginid => p_loginid
1417 , p_validation_level => fnd_api.g_valid_level_full
1418 );
1419 x_return_status := l_return_status;
1420 x_msg_count := l_msg_count;
1421 x_msg_data := l_msg_data;
1422
1423 IF (l_return_status = fnd_api.g_ret_sts_success)
1424 THEN
1425 x_return_status := fnd_api.g_ret_sts_success;
1426 --Bug: 3559328: Performance bug fix. The fnd API to clear is
1427 --already called in the private API. Since this is just a wrapper,
1428 --we do not need to call it here as it would alreday have been cleared
1429 --FND_MESSAGE.clear;
1430 END IF;
1431 EXCEPTION
1432 WHEN OTHERS
1433 THEN
1434 IF (l_debug = 1)
1435 THEN
1436 inv_log_util.TRACE ('Error in validate_group : ' || l_error_exp
1437 , 'INV_TXN_MANAGER_PUB'
1438 , '1'
1439 );
1440 inv_log_util.TRACE ('Error:' || SUBSTR (SQLERRM, 1, 250)
1441 , 'INV_TXN_MANAGER_PUB'
1442 , 1
1443 );
1444 END IF;
1445
1446 x_return_status := fnd_api.g_ret_sts_error;
1447 fnd_message.CLEAR;
1448 END validate_group;
1449
1450 /******* LINE VALIDATION OBJECTS ***************/
1451
1452 /******************************************************************
1453 *
1454 * lotcheck moved to group API INV_TXN_MANAGER_GRP
1455 *
1456 ******************************************************************/
1457
1458 /******************************************************************
1459 *
1460 * setorgclientinfo() moved to group API INV_TXN_MANAGER_GRP
1461 *
1462 ******************************************************************/
1463
1464 /******************************************************************
1465 -- Function moved to INV_TXN_MANAGER_GRP
1466 -- getloc
1467 -- Description
1468 -- Private function to get Locator id using Flex API's
1469 -- Uses FND_FLEX_KEY_API (AFFFKAIS/B.pls) and
1470 -- FND_FLEX_EXT (AFFFEXTS/B.pls)
1471 --
1472 -- Assumes that only Id's are populated in the MTI segments
1473 --
1474 -- Returns
1475 -- Returns false if any error occurs
1476 -- Output Parameters
1477 -- x_locid locator or null if error occurred
1478 ******************************************************************/
1479
1480 /******************************************************************
1481 -- Function moved to INV_TXN_MANAGER_GRP
1482 -- getlocid
1483 -- Description
1484 -- find the locator using the flex field segments
1485 -- Calls private function getLoc to do the work
1486 -- Output Parameters
1487 -- x_locator locator or null if error occurred
1488 ******************************************************************/
1489
1490 /******************************************************************
1491 -- Function moved to INV_TXN_MANAGER_GRP
1492 -- getxlocid
1493 -- Description
1494 -- find the locator using the flex field segments
1495 -- Calls private function getLoc to do the work
1496 -- Output Parameters
1497 -- x_locator locator or null if error occurred
1498 ******************************************************************/
1499
1500 /******************************************************************
1501 *
1502 * validate_loc_for_project()
1503 * moved to INV_TXN_MANAGER_GRP
1504 *
1505 ******************************************************************/
1506
1507 /******************************************************************
1508 *
1509 * validate_unit_number()
1510 * moved to INV_TXN_MANAGER_GRP
1511 *
1512 ******************************************************************/
1513
1514 /******************************************************************
1515 *
1516 * validate_lines() : Outer
1517 *
1518 ******************************************************************/
1519 PROCEDURE validate_lines (
1520 p_header_id NUMBER
1521 , p_commit VARCHAR2 := fnd_api.g_false
1522 , p_validation_level NUMBER := fnd_api.g_valid_level_full
1523 , x_return_status OUT NOCOPY VARCHAR2
1524 , x_msg_count OUT NOCOPY NUMBER
1525 , x_msg_data OUT NOCOPY VARCHAR2
1526 , p_userid NUMBER
1527 , p_loginid NUMBER
1528 , p_applid NUMBER
1529 , p_progid NUMBER
1530 )
1531 AS
1532 CURSOR aa1
1533 IS
1534 SELECT transaction_interface_id
1535 , transaction_header_id
1536 , request_id
1537 , inventory_item_id
1538 , organization_id
1539 , subinventory_code
1540 , transfer_organization
1541 , transfer_subinventory
1542 , transaction_uom
1543 , transaction_date
1544 , transaction_quantity
1545 , locator_id
1546 , transfer_locator
1547 , transaction_source_id
1548 , transaction_source_type_id
1549 , transaction_action_id
1550 , transaction_type_id
1551 , distribution_account_id
1552 , NVL (shippable_flag, 'Y')
1553 , ROWID
1554 , new_average_cost
1555 , value_change
1556 , percentage_change
1557 , material_account
1558 , material_overhead_account
1559 , resource_account
1560 , outside_processing_account
1561 , overhead_account
1562 , requisition_line_id
1563 , overcompletion_transaction_qty
1564 , /* Overcompletion Transactions */
1565 end_item_unit_number
1566 , scheduled_payback_date
1567 , /* Borrow Payback */
1568 revision
1569 , /* Borrow Payback */
1570 org_cost_group_id
1571 , /* PCST */
1572 cost_type_id
1573 , /* PCST */
1574 primary_quantity
1575 , source_line_id
1576 , process_flag
1577 , transaction_source_name
1578 , trx_source_delivery_id
1579 , trx_source_line_id
1580 , parent_id
1581 , transaction_batch_id
1582 , transaction_batch_seq
1583 ,
1584 -- INVCONV start fabdi
1585 secondary_transaction_quantity
1586 , secondary_uom_code
1587 -- INVCONV end fabdi
1588 , SHIP_TO_LOCATION_ID --eIB Build; Bug# 4348541
1589 , transfer_price -- OPM INVCONV umoogala Process-Discrete Transfers
1590 , WIP_ENTITY_TYPE -- Pawan 11th july
1591 /* Bug:5392366. Added the following two columns.*/
1592 , completion_transaction_id
1593 , move_transaction_id
1594 FROM mtl_transactions_interface
1595 WHERE transaction_header_id = p_header_id AND process_flag = 1
1596 ORDER BY organization_id
1597 , inventory_item_id
1598 , revision
1599 , subinventory_code
1600 , locator_id;
1601
1602 line_vldn_error_flag VARCHAR (1);
1603 l_line_rec_type line_rec_type;
1604 l_count NUMBER;
1605 BEGIN
1606 IF (l_debug IS NULL)
1607 THEN
1608 l_debug := NVL (fnd_profile.VALUE ('INV_DEBUG_TRACE'), 0);
1609 END IF;
1610
1611 fnd_flex_key_api.set_session_mode ('seed_data');
1612
1613 FOR l_line_rec_type IN aa1
1614 LOOP
1615 BEGIN
1616 SAVEPOINT line_validation_svpt;
1617 validate_lines (p_line_rec_type => l_line_rec_type
1618 , p_commit => p_commit
1619 , p_validation_level => p_validation_level
1620 , p_error_flag => line_vldn_error_flag
1621 , p_userid => p_userid
1622 , p_loginid => p_loginid
1623 , p_applid => p_applid
1624 , p_progid => p_progid
1625 );
1626
1627 IF (line_vldn_error_flag = 'Y')
1628 THEN
1629 IF (l_debug = 1)
1630 THEN
1631 inv_log_util.TRACE ('Error in Line Validatin'
1632 , 'INV_TXN_MANAGER_PUB'
1633 , 9
1634 );
1635 END IF;
1636 END IF;
1637 END;
1638 END LOOP;
1639
1640 x_return_status := fnd_api.g_ret_sts_success;
1641 EXCEPTION
1642 WHEN OTHERS
1643 THEN
1644 IF (l_debug = 1)
1645 THEN
1646 inv_log_util.TRACE ( 'Error in outer validate_lines'
1647 || SUBSTR (SQLERRM, 1, 240)
1648 , 'INV_TXN_MANAGER_PUB'
1649 , 1
1650 );
1651 END IF;
1652
1653 x_return_status := fnd_api.g_ret_sts_error;
1654 END validate_lines;
1655
1656 /******************************************************************
1657 *
1658 * validate_lines()
1659 * Validate one transaction record in MTL_TRANSACTIONS_INTERFACE
1660 *
1661 ******************************************************************/
1662 PROCEDURE validate_lines (
1663 p_line_rec_type line_rec_type
1664 , p_commit VARCHAR2 := fnd_api.g_false
1665 , p_validation_level NUMBER := fnd_api.g_valid_level_full
1666 , p_error_flag OUT NOCOPY VARCHAR2
1667 , p_userid NUMBER
1668 , p_loginid NUMBER
1669 , p_applid NUMBER
1670 , p_progid NUMBER
1671 )
1672 AS
1673 l_error_flag VARCHAR2 (2);
1674 BEGIN
1675 IF (l_debug IS NULL)
1676 THEN
1677 l_debug := NVL (fnd_profile.VALUE ('INV_DEBUG_TRACE'), 0);
1678 END IF;
1679
1680 inv_txn_manager_grp.validate_lines
1681 (p_line_rec_type => p_line_rec_type
1682 , p_error_flag => l_error_flag
1683 , p_validation_level => p_validation_level
1684 , p_userid => p_userid
1685 , p_loginid => p_loginid
1686 , p_applid => p_applid
1687 , p_progid => p_progid
1688 );
1689
1690 IF (l_debug = 1)
1691 THEN
1692 inv_log_util.TRACE ('Returned from inv_txn_manager_grp.validate_lines'
1693 , 'INV_TXN_MANAGER_PUB'
1694 , 9
1695 );
1696 END IF;
1697 p_error_flag := l_error_flag;
1698
1699 IF (l_error_flag = 'Y')
1700 THEN
1701 IF (l_debug = 1)
1702 THEN
1703 inv_log_util.TRACE ('Error in Line Validatin'
1704 , 'INV_TXN_MANAGER_PUB'
1705 , 9
1706 );
1707 END IF;
1708 END IF;
1709 EXCEPTION
1710 WHEN OTHERS
1711 THEN
1712 p_error_flag := 'Y';
1713
1714 IF (l_debug = 1)
1715 THEN
1716 inv_log_util.TRACE ('Error in validate_line : ' || l_error_exp
1717 , 'INV_TXN_MANAGER_PUB'
1718 , '1'
1719 );
1720 inv_log_util.TRACE ('Error:' || SUBSTR (SQLERRM, 1, 250)
1721 , 'INV_TXN_MANAGER_PUB'
1722 , 1
1723 );
1724 END IF;
1725 END validate_lines;
1726
1727 /******************************************************************
1728 *
1729 * get_open_period()
1730 *
1731 ******************************************************************/
1732 FUNCTION get_open_period (
1733 p_org_id NUMBER
1734 , p_trans_date DATE
1735 , p_chk_date NUMBER
1736 )
1737 RETURN NUMBER
1738 IS
1739 chk_date NUMBER;
1740 /* 0 ignore date,1-return 0 if date doesn't fall in current
1741 period, -1 if Oracle error, otherwise period id*/
1742 trans_date DATE; /* transaction_date */
1743 acct_period_id NUMBER; /* period_close_id of current period */
1744 BEGIN
1745 IF (l_debug IS NULL)
1746 THEN
1747 l_debug := NVL (fnd_profile.VALUE ('INV_DEBUG_TRACE'), 0);
1748 END IF;
1749
1750 acct_period_id := 0; /* default value */
1751
1752 IF (chk_date = 1)
1753 THEN
1754 SELECT acct_period_id
1755 INTO acct_period_id
1756 FROM org_acct_periods
1757 WHERE period_close_date IS NULL
1758 AND organization_id = p_org_id
1759 AND NVL (p_trans_date, SYSDATE) BETWEEN period_start_date
1760 AND schedule_close_date
1761 ORDER BY period_start_date DESC, schedule_close_date ASC;
1762 ELSE
1763 SELECT acct_period_id
1764 INTO acct_period_id
1765 FROM org_acct_periods
1766 WHERE period_close_date IS NULL
1767 AND organization_id = p_org_id
1768 AND TRUNC (schedule_close_date) >=
1769 TRUNC (NVL (p_trans_date, SYSDATE))
1770 AND TRUNC (period_start_date) <= TRUNC (NVL (p_trans_date, SYSDATE));
1771 END IF;
1772
1773 RETURN (acct_period_id);
1774 EXCEPTION
1775 WHEN NO_DATA_FOUND
1776 THEN
1777 acct_period_id := 0;
1778 RETURN (acct_period_id);
1779 WHEN OTHERS
1780 THEN
1781 acct_period_id := -1;
1782 RETURN (acct_period_id);
1783 END get_open_period;
1784
1785 /******************************************************************
1786 *
1787 * tmpinsert() moved to INV_TXN_MANAGER_GRP
1788 *
1789 ******************************************************************/
1790 FUNCTION tmpinsert (p_header_id IN NUMBER)
1791 RETURN BOOLEAN
1792 IS
1793 l_lt_flow_schedule NUMBER;
1794 l_return BOOLEAN := TRUE;
1795 BEGIN
1796 IF (l_debug IS NULL)
1797 THEN
1798 l_debug := NVL (fnd_profile.VALUE ('INV_DEBUG_TRACE'), 0);
1799 END IF;
1800
1801 l_return := inv_txn_manager_grp.tmpinsert (p_header_id => p_header_id);
1802
1803 IF (l_return)
1804 THEN
1805
1806 RETURN TRUE;
1807 ELSE
1808 RETURN FALSE;
1809 END IF;
1810 EXCEPTION
1811 WHEN OTHERS
1812 THEN
1813 IF (l_debug = 1)
1814 THEN
1815 inv_log_util.TRACE ( 'Error in tmpinsert: sqlerrm : '
1816 || SUBSTR (SQLERRM, 1, 200)
1817 , 'INV_TXN_MANAGER_PUB'
1818 , '9'
1819 );
1820 END IF;
1821
1822 RETURN FALSE;
1823 END tmpinsert;
1824
1825 /******************************************************************
1826 *
1827 * bflushchk()
1828 *
1829 ******************************************************************/
1830 FUNCTION bflushchk (p_txn_hdr_id IN OUT NOCOPY NUMBER)
1831 RETURN BOOLEAN
1832 IS
1833 l_new_hdr_id NUMBER; /* New Assy Backflush Header ID */
1834 l_old_hdr_id NUMBER;
1835 BEGIN
1836 l_old_hdr_id := p_txn_hdr_id;
1837
1838 SELECT mtl_material_transactions_s.NEXTVAL
1839 INTO l_new_hdr_id
1840 FROM DUAL;
1841
1842 p_txn_hdr_id := l_new_hdr_id;
1843
1844 UPDATE mtl_material_transactions_temp
1845 SET transaction_header_id = l_new_hdr_id
1846 , lock_flag = 'Y'
1847 WHERE process_flag = 'Y'
1848 AND NVL (lock_flag, 'N') = 'N'
1849 AND transaction_header_id IN (
1850 SELECT mmtt.transaction_header_id
1851 FROM mtl_material_transactions mmt
1852 , mtl_material_transactions_temp mmtt
1853 WHERE mmt.transaction_set_id = l_old_hdr_id
1854 AND mmt.completion_transaction_id =
1855 mmtt.completion_transaction_id);
1856
1857 IF SQL%NOTFOUND
1858 THEN
1859 p_txn_hdr_id := -1;
1860 END IF;
1861
1862 RETURN TRUE;
1863 EXCEPTION
1864 WHEN OTHERS
1865 THEN
1866 IF (l_debug = 1)
1867 THEN
1868 inv_log_util.TRACE ('*** SQL error ' || SUBSTR (SQLERRM, 1, 200)
1869 , 'INV_TXN_MANAGER_GRP'
1870 , 9
1871 );
1872 END IF;
1873
1874 RETURN FALSE;
1875 END bflushchk;
1876
1877 /******************************************************************
1878 *
1879 * poget()
1880 *
1881 ******************************************************************/
1882 PROCEDURE poget (p_prof IN VARCHAR2, x_ret OUT NOCOPY VARCHAR2)
1883 IS
1884 BEGIN
1885 SELECT fnd_profile.VALUE (p_prof)
1886 INTO x_ret
1887 FROM DUAL;
1888 END poget;
1889
1890 /******************************************************************
1891 *
1892 * process_Transactions()
1893 *
1894 ******************************************************************/
1895 FUNCTION process_transactions (
1896 p_api_version IN NUMBER
1897 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
1898 , p_commit IN VARCHAR2 := fnd_api.g_false
1899 , p_validation_level IN NUMBER := fnd_api.g_valid_level_full
1900 , x_return_status OUT NOCOPY VARCHAR2
1901 , x_msg_count OUT NOCOPY NUMBER
1902 , x_msg_data OUT NOCOPY VARCHAR2
1903 , x_trans_count OUT NOCOPY NUMBER
1904 , p_table IN NUMBER := 1
1905 , p_header_id IN NUMBER
1906 )
1907 RETURN NUMBER
1908 IS
1909 l_header_id NUMBER;
1910 l_source_header_id NUMBER;
1911 l_totrows NUMBER;
1912 l_initotrows NUMBER;
1913 l_midtotrows NUMBER;
1914 l_userid NUMBER;
1915 l_loginid NUMBER;
1916 l_progid NUMBER;
1917 l_applid NUMBER;
1918 l_reqstid NUMBER;
1919 l_valreq NUMBER;
1920 l_errd_int_id NUMBER;
1921 l_trx_type NUMBER;
1922 l_item_id NUMBER;
1923 l_org_id NUMBER;
1924 l_srctypeid NUMBER;
1925 l_tempid NUMBER;
1926 l_actid NUMBER;
1927 l_srcid NUMBER;
1928 l_locid NUMBER;
1929 l_xlocid NUMBER;
1930 l_rctrl NUMBER;
1931 l_lctrl NUMBER;
1932 l_trx_qty NUMBER;
1933 l_qty NUMBER := 0;
1934 l_aqty NUMBER := 0;
1935 l_oqty NUMBER := 0;
1936 l_src_code VARCHAR2 (30);
1937 l_rowid VARCHAR2 (21);
1938 l_sub_code VARCHAR2 (11);
1939 l_xfrsub VARCHAR2 (11);
1940 l_lotnum VARCHAR2 (80);
1941 -- changed lot_number to 80, inconv
1942 l_rev VARCHAR2 (4);
1943 l_disp VARCHAR2 (3000);
1944 l_message VARCHAR2 (100);
1945 l_source_code VARCHAR2 (30);
1946 l_profval VARCHAR2 (256);
1947 l_expbuf VARCHAR2 (241);
1948 l_prfvalue VARCHAR2 (10);
1949 done BOOLEAN;
1950 FIRST BOOLEAN;
1951 tree_exists BOOLEAN;
1952 l_result NUMBER;
1953 l_msg_data VARCHAR2 (2000);
1954 line_vldn_error_flag VARCHAR (1);
1955 l_line_rec_type line_rec_type;
1956 rollback_line_validation EXCEPTION;
1957 l_trx_batch_id NUMBER;
1958 l_last_trx_batch_id NUMBER;
1959 batch_error BOOLEAN;
1960 l_process NUMBER;
1961 l_return_status VARCHAR2 (30);
1962 l_ret_sts_pre VARCHAR2 (30);
1963 --J-dev for return status if preInvWipProcessing
1964 l_ret_sts_post VARCHAR2 (30);
1965 --J-dev for return status ifpreInvWipProcessing
1966 l_source_type_id NUMBER; --J-dev used to check if WIP returned
1967 --successful rows.
1968 l_batch_count NUMBER;
1969 l_dist_acct_id NUMBER;
1970 l_batch_size NUMBER;
1971 /*Patchset J:Interface Trip Stop Enhancements*/
1972 l_wip_entity_type NUMBER ; /* Pawan Added for gme- convergence*/
1973
1974 /*Bug 5209598.Added the following fields to perform ATT/ATR checks. */
1975 l_tree_id NUMBER;
1976 l_msg_count NUMBER;
1977 l_temp_rowid VARCHAR2(21);
1978 l_srclineid VARCHAR2(40);
1979 l_trxdate DATE;
1980 l_neg_inv_rcpt number;
1981 l_revision_control BOOLEAN;
1982 l_lot_control BOOLEAN;
1983 l_dem_hdr_id NUMBER ;
1984 l_dem_line_id NUMBER;
1985 l_translate BOOLEAN := TRUE;
1986
1987 l_qoh NUMBER;
1988 l_rqoh NUMBER;
1989 l_pqoh NUMBER;
1990 l_qr NUMBER;
1991 l_qs NUMBER;
1992 l_att NUMBER;
1993 l_atr NUMBER;
1994
1995 l_override_neg_for_backflush NUMBER := 0;
1996 l_override_rsv_for_backflush NUMBER := 2;
1997 l_item_qoh NUMBER;
1998 l_item_rqoh NUMBER;
1999 l_item_pqoh NUMBER;
2000 l_item_qr NUMBER;
2001 l_item_qs NUMBER;
2002 l_item_att NUMBER;
2003 l_item_atr NUMBER;
2004
2005 l_current_batch_failed BOOLEAN := FALSE;
2006 l_current_err_batch_id NUMBER;
2007 l_count_success NUMBER:=0;
2008
2009 /*Bug:5276191. Added the following two variables. */
2010 l_from_project_id NUMBER := null;
2011 l_serial_control NUMBER := 1;
2012
2013 /*Bug 5209598.End of variable declaration. */
2014
2015 CURSOR aa1
2016 IS
2017 SELECT transaction_interface_id
2018 , transaction_header_id
2019 , request_id
2020 , inventory_item_id
2021 , organization_id
2022 , subinventory_code
2023 , transfer_organization
2024 , transfer_subinventory
2025 , transaction_uom
2026 , transaction_date
2027 , transaction_quantity
2028 , locator_id
2029 , transfer_locator
2030 , transaction_source_id
2031 , transaction_source_type_id
2032 , transaction_action_id
2033 , transaction_type_id
2034 , distribution_account_id
2035 , NVL (shippable_flag, 'Y')
2036 , ROWID
2037 , new_average_cost
2038 , value_change
2039 , percentage_change
2040 , material_account
2041 , material_overhead_account
2042 , resource_account
2043 , outside_processing_account
2044 , overhead_account
2045 , requisition_line_id
2046 , overcompletion_transaction_qty
2047 , /* Overcompletion Transactions */
2048 end_item_unit_number
2049 , scheduled_payback_date
2050 , /* Borrow Payback */
2051 revision
2052 , /* Borrow Payback */
2053 org_cost_group_id
2054 , /* PCST */
2055 cost_type_id
2056 , /* PCST */
2057 primary_quantity
2058 , source_line_id
2059 , process_flag
2060 , transaction_source_name
2061 , trx_source_delivery_id
2062 , trx_source_line_id
2063 , parent_id
2064 , transaction_batch_id
2065 , transaction_batch_seq
2066 ,
2067 -- INVCONV start fabdi
2068 secondary_transaction_quantity
2069 , secondary_uom_code
2070 -- INVCONV end fabdi
2071 , ship_to_location_id --eIB Build; Bug# 4348541
2072 , transfer_price -- OPM INVCONV umoogala Process-Discrete Transfers
2073 , WIP_ENTITY_TYPE -- Pawan 11th july added
2074 /* Bug:5392366. Added the following two columns.*/
2075 , completion_transaction_id
2076 , move_transaction_id
2077 FROM mtl_transactions_interface
2078 WHERE transaction_header_id = p_header_id AND process_flag = 1
2079 ORDER BY transaction_batch_id
2080 , transaction_batch_seq
2081 , organization_id
2082 , inventory_item_id
2083 , revision
2084 , subinventory_code
2085 , locator_id;
2086
2087 /*Bug 5209598. Added the following cursor. */
2088 CURSOR Z1 (p_flow_sch NUMBER,p_line_rec_type inv_txn_manager_pub.line_rec_type) IS
2089 SELECT
2090 p_line_rec_type.ROWID,
2091 p_line_rec_type.INVENTORY_ITEM_ID,
2092 p_line_rec_type.REVISION,
2093 p_line_rec_type.ORGANIZATION_ID,
2094 p_line_rec_type.SUBINVENTORY_CODE,
2095 p_line_rec_type.LOCATOR_ID,
2096 ABS(p_line_rec_type.PRIMARY_QUANTITY) PRIMARY_QUANTITY,
2097 NULL LOT_NUMBER,
2098 p_line_rec_type.TRANSACTION_SOURCE_TYPE_ID,
2099 p_line_rec_type.TRANSACTION_ACTION_ID,
2100 p_line_rec_type.TRANSACTION_SOURCE_ID,
2101 p_line_rec_type.TRANSACTION_SOURCE_NAME,
2102 --Jalaj Srivastava 5010595
2103 --for GME (wip_enity_type=10) select trx_source_line_id as source_line_id
2104 decode(p_line_rec_type.transaction_source_type_id,5,decode(p_line_rec_type.wip_entity_type,10,p_line_rec_type.TRX_SOURCE_LINE_ID,p_line_rec_type.SOURCE_LINE_ID),p_line_rec_type.SOURCE_LINE_ID) SOURCE_LINE_ID,
2105 MSI.REVISION_QTY_CONTROL_CODE,
2106 decode(p_line_rec_type.transaction_source_type_id,5,1,MSI.lot_control_code) lot_control_code,--j-dev
2107 decode(p_line_rec_type.TRANSACTION_ACTION_ID,2,p_line_rec_type.TRANSFER_SUBINVENTORY,28,p_line_rec_type.TRANSFER_SUBINVENTORY,null) TRANSFER_SUBINVENTORY,
2108 p_line_rec_type.TRANSFER_LOCATOR,
2109 p_line_rec_type.transaction_date,
2110 MP.NEGATIVE_INV_RECEIPT_CODE
2111 FROM MTL_PARAMETERS MP,
2112 MTL_SYSTEM_ITEMS MSI
2113 WHERE MP.ORGANIZATION_ID = p_line_rec_type.ORGANIZATION_ID
2114 -- AND MP.NEGATIVE_INV_RECEIPT_CODE = 2 'bug 3679189'
2115 AND p_line_rec_type.PROCESS_FLAG = 1
2116 AND ((MSI.LOT_CONTROL_CODE = 1) OR (p_line_rec_type.transaction_source_type_id=5 and p_line_rec_type.wip_entity_type <> 10))--J-dev--verify this
2117 AND ( ( (p_line_rec_type.wip_entity_type <> 10)
2118 AND ( (p_flow_sch <> 1
2119 AND p_line_rec_type.TRANSACTION_ACTION_ID IN (1,2,3,21,32,34,5) )
2120 OR (p_flow_sch = 1
2121 AND p_line_rec_type.TRANSACTION_ACTION_ID IN (1, 32) )
2122 )
2123 )
2124 --Jalaj Srivastava 5232394
2125 --select all transactions for GME
2126 OR (p_line_rec_type.wip_entity_type = 10)
2127 )
2128 AND MSI.ORGANIZATION_ID = MP.ORGANIZATION_ID
2129 AND MSI.ORGANIZATION_ID = p_line_rec_type.ORGANIZATION_ID
2130 AND MSI.INVENTORY_ITEM_ID = p_line_rec_type.INVENTORY_ITEM_ID
2131 UNION
2132 SELECT
2133 p_line_rec_type.ROWID,
2134 p_line_rec_type.INVENTORY_ITEM_ID,
2135 p_line_rec_type.REVISION,
2136 p_line_rec_type.ORGANIZATION_ID,
2137 p_line_rec_type.SUBINVENTORY_CODE,
2138 p_line_rec_type.LOCATOR_ID,
2139 ABS(MTLI.PRIMARY_QUANTITY) PRIMARY_QUANTITY,
2140 MTLI.lot_number LOT_NUMBER,
2141 p_line_rec_type.TRANSACTION_SOURCE_TYPE_ID,
2142 p_line_rec_type.TRANSACTION_ACTION_ID,
2143 p_line_rec_type.TRANSACTION_SOURCE_ID,
2144 p_line_rec_type.TRANSACTION_SOURCE_NAME,
2145 --Jalaj Srivastava 5010595
2146 --for GME (wip_enity_type=10) select trx_source_line_id as source_line_id
2147 decode(p_line_rec_type.wip_entity_type,10,p_line_rec_type.TRX_SOURCE_LINE_ID,p_line_rec_type.SOURCE_LINE_ID) SOURCE_LINE_ID,
2148 MSI.REVISION_QTY_CONTROL_CODE,
2149 MSI.lot_control_code lot_control_code,
2150 decode(p_line_rec_type.TRANSACTION_ACTION_ID,2,p_line_rec_type.TRANSFER_SUBINVENTORY,28,p_line_rec_type.TRANSFER_SUBINVENTORY,5,p_line_rec_type.transfer_subinventory,null) TRANSFER_SUBINVENTORY,
2151 p_line_rec_type.TRANSFER_LOCATOR,
2152 p_line_rec_type.transaction_date,
2153 MP.NEGATIVE_INV_RECEIPT_CODE
2154 FROM MTL_TRANSACTION_LOTS_INTERFACE MTLI,
2155 MTL_PARAMETERS MP,
2156 MTL_SYSTEM_ITEMS MSI
2157 WHERE MP.ORGANIZATION_ID = p_line_rec_type.ORGANIZATION_ID
2158 --AND MP.NEGATIVE_INV_RECEIPT_CODE = 2 'bug 3679189'
2159 AND MTLI.TRANSACTION_INTERFACE_ID = p_line_rec_type.TRANSACTION_INTERFACE_ID
2160 AND p_line_rec_type.PROCESS_FLAG = 1
2161 AND MSI.LOT_CONTROL_CODE = 2
2162 AND ( ( (p_line_rec_type.wip_entity_type <> 10)
2163 AND ( (p_flow_sch <> 1
2164 AND p_line_rec_type.TRANSACTION_ACTION_ID IN (1,2,3,21,32,34,5) )
2165 OR (p_flow_sch = 1
2166 AND p_line_rec_type.TRANSACTION_ACTION_ID = 32 )
2167 )
2168 )
2169 --Jalaj Srivastava 5232394
2170 --select all transactions for GME
2171 OR (p_line_rec_type.wip_entity_type = 10)
2172 )
2173 AND MSI.ORGANIZATION_ID = MP.ORGANIZATION_ID
2174 AND MSI.ORGANIZATION_ID = p_line_rec_type.ORGANIZATION_ID
2175 AND MSI.INVENTORY_ITEM_ID = p_line_rec_type.INVENTORY_ITEM_ID
2176 -- Pawan 11th july added this for validation of lot for GME only
2177 AND ((p_line_rec_type.transaction_source_type_id <> 5) OR
2178 (p_line_rec_type.transaction_source_type_id = 5 AND
2179 p_line_rec_type.wip_entity_type = 10 ));--J-dev verify
2180
2181 /*Bug:5276191. Added the following cursor. */
2182 CURSOR c_mmtt IS
2183 SELECT *
2184 FROM mtl_material_transactions_temp
2185 WHERE transaction_header_id = p_header_id
2186 AND NVL(transaction_status, 1) <> 2 -- don't consider suggestions
2187 AND process_flag = 'Y'
2188 ORDER BY transaction_batch_id;
2189
2190 l_index NUMBER := 0;
2191 l_previous_parent_id NUMBER := 0;
2192 l_validation_status VARCHAR2 (1) := 'Y';
2193
2194 l_rsv_wip_entity_type NUMBER := NULL; -- Bug 6454464
2195 l_rsv_wip_job_type VARCHAR2(15); -- Bug 6454464
2196
2197 BEGIN
2198 l_debug := NVL (fnd_profile.VALUE ('INV_DEBUG_TRACE'), 0);
2199 l_header_id := p_header_id;
2200
2201 --dbms_output.put_line(' came to process_trx');
2202 IF (l_debug = 1)
2203 THEN
2204 inv_log_util.TRACE
2205 ( '-----Inside process_Transactions-------.trxhdr='
2206 || p_header_id
2207 , 'INV_TXN_MANAGER_PUB'
2208 , 9
2209 );
2210
2211 END IF;
2212
2213 /* FND_MESSAGE.SET_NAME('INV', 'BAD_INPUT_ARGUMENTS');
2214 FND_MSG_PUB.ADD;
2215 RAISE FND_API.G_EXC_ERROR; */
2216
2217 /*----------------------------------------------------------+
2218 | retrieving information
2219 +----------------------------------------------------------*/
2220 poget ('LOGIN_ID', l_loginid);
2221 poget ('USER_ID', l_userid);
2222 poget ('CONC_PROGRAM_ID', l_progid);
2223 poget ('CONC_REQUEST_ID', l_reqstid);
2224 poget ('PROG_APPL_ID', l_applid);
2225
2226 IF l_loginid IS NULL
2227 THEN
2228 l_loginid := -1;
2229 END IF;
2230
2231 IF l_userid IS NULL
2232 THEN
2233 l_userid := -1;
2234 END IF;
2235
2236 /*l_loginid := 1068;
2237 l_userid := 1068;
2238 l_progid := 32321;
2239 l_reqstid := null;
2240 l_applid := 401;*/
2241 x_return_status := fnd_api.g_ret_sts_error;
2242 x_msg_count := 0;
2243 x_msg_data := '';
2244 x_trans_count := 0;
2245
2246 -- Bug 3339212. We were rolling back everything if
2247 --there is an error in process transactions. This leads o erasing all
2248 -- the save point set, which would result in cannot establishing save points
2249 -- which could have been set by other teams calling our API. So, we
2250 -- would rollback to this point if anything fails in process
2251 --transactions.
2252 -- Bug 3686000: The savepoint to be established only when the caller calls
2253 -- this API with p_commit as false. Otherwise, during an exception, we
2254 -- will not find the save point as we would have committed if p_commit
2255 -- has been set to true in downstream processing.
2256 IF NOT fnd_api.to_boolean (p_commit)
2257 THEN
2258 SAVEPOINT process_transactions_svpt;
2259 END IF;
2260
2261 --fnd_global.apps_initialize(1003593, 53466, 385);
2262 IF (p_table = 2)
2263 THEN
2264 /** Process Rows in MTL_MATERIAL_TRANSACTION_TEMP **/
2265 IF (l_debug = 1)
2266 THEN
2267 inv_log_util.TRACE ('Process Rows in MTL_MATERIAL_TRANSACTION_TEMP'
2268 , 'INV_TXN_MANAGER_PUB'
2269 , 9
2270 );
2271 END IF;
2272
2273 UPDATE mtl_material_transactions_temp
2274 SET last_update_date = SYSDATE
2275 , transaction_temp_id =
2276 NVL (transaction_temp_id, mtl_material_transactions_s.NEXTVAL)
2277 , last_updated_by = l_userid
2278 , last_update_login = l_loginid
2279 , program_application_id = l_applid
2280 , program_id = l_progid
2281 , request_id = l_reqstid
2282 , program_update_date = SYSDATE
2283 , ERROR_CODE = NULL
2284 , error_explanation = NULL
2285 WHERE process_flag = 'Y'
2286 AND NVL (transaction_status, ts_default) <> ts_save_only /* 2STEP */
2287 AND transaction_header_id = l_header_id;
2288
2289 --Bug 4586255, support 6 decimals for wip
2290 UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP
2291 SET PRIMARY_QUANTITY = ROUND(PRIMARY_QUANTITY,5),
2292 TRANSACTION_QUANTITY = ROUND(TRANSACTION_QUANTITY,5)
2293 WHERE PROCESS_FLAG = 'Y'
2294 AND NVL(TRANSACTION_STATUS,TS_DEFAULT) <> TS_SAVE_ONLY /* 2STEP */
2295 AND TRANSACTION_HEADER_ID = l_header_id
2296 AND transaction_source_type_id <> 5;
2297
2298 UPDATE mtl_transaction_lots_temp
2299 SET PRIMARY_QUANTITY = ROUND(PRIMARY_QUANTITY,5),
2300 TRANSACTION_QUANTITY = ROUND(TRANSACTION_QUANTITY,5)
2301 WHERE transaction_temp_id
2302 IN ( SELECT transaction_temp_id
2303 FROM mtl_material_transactions_temp
2304 WHERE PROCESS_FLAG = 'Y'
2305 AND NVL(TRANSACTION_STATUS,TS_DEFAULT) <> TS_SAVE_ONLY /* 2STEP */
2306 AND TRANSACTION_HEADER_ID = l_header_id
2307 AND transaction_source_type_id <> 5);
2308
2309
2310 UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP
2311 SET PRIMARY_QUANTITY = ROUND(PRIMARY_QUANTITY,6),
2312 TRANSACTION_QUANTITY = ROUND(TRANSACTION_QUANTITY,6)
2313 WHERE PROCESS_FLAG = 'Y'
2314 AND NVL(TRANSACTION_STATUS,TS_DEFAULT) <> TS_SAVE_ONLY /* 2STEP */
2315 AND TRANSACTION_HEADER_ID = l_header_id
2316 AND transaction_source_type_id = 5;
2317
2318 UPDATE mtl_transaction_lots_temp
2319 SET PRIMARY_QUANTITY = ROUND(PRIMARY_QUANTITY,6),
2320 TRANSACTION_QUANTITY = ROUND(TRANSACTION_QUANTITY,6)
2321 WHERE transaction_temp_id
2322 IN( SELECT transaction_temp_id
2323 FROM mtl_material_transactions_temp
2324 WHERE PROCESS_FLAG = 'Y'
2325 AND NVL(TRANSACTION_STATUS,TS_DEFAULT) <> TS_SAVE_ONLY /* 2STEP */
2326 AND TRANSACTION_HEADER_ID = l_header_id
2327 AND transaction_source_type_id = 5);
2328
2329 IF (l_debug = 1)
2330 THEN
2331 inv_log_util.TRACE ('Rows in MMTT ready to process '
2332 , 'INV_TXN_MANAGER_PUB'
2333 , 9
2334 );
2335 END IF;
2336
2337 SELECT COUNT (1)
2338 INTO l_process
2339 FROM mtl_material_transactions_temp
2340 WHERE transaction_header_id = l_header_id
2341 AND process_flag = 'Y'
2342 AND transaction_status = 3 /* not able to use the TS_PROCESS macro */
2343 AND ROWNUM < 2;
2344
2345 --the assumption is that default txns are
2346 --never mixed up with the 2level txns. so
2347 -- we can avoid temp validation call if there
2348 --are no rows with transaction_status = TS_PROCESS
2349 IF l_process = 1
2350 THEN
2351 IF (l_debug = 1)
2352 THEN
2353 inv_log_util.TRACE ('Calling INV_PROCESS_TEMP.processTransaction'
2354 , 'INV_TXN_MANAGER_PUB'
2355 , 9
2356 );
2357 END IF;
2358
2359 l_result :=
2360 inv_process_temp.processtransaction (l_header_id
2361 , inv_process_temp.FULL
2362 , inv_process_temp.ignore_all
2363 );
2364 END IF;
2365
2366 SELECT COUNT (*)
2367 INTO l_totrows
2368 FROM mtl_material_transactions_temp
2369 WHERE transaction_header_id = l_header_id
2370 AND process_flag = 'Y'
2371 AND NVL (transaction_status, ts_default) <> ts_save_only; /* 2STEP */
2372
2373 l_midtotrows := l_totrows;
2374 l_initotrows := l_totrows;
2375 x_trans_count := l_totrows;
2376
2377 IF (l_totrows = 0)
2378 THEN
2379 IF fnd_api.to_boolean (p_commit)
2380 THEN
2381 COMMIT WORK;
2382 END IF;
2383
2384 fnd_message.set_name ('INV', 'INV_PROC_WARN');
2385 l_disp := fnd_message.get;
2386
2387 IF (l_debug = 1)
2388 THEN
2389 inv_log_util.TRACE (l_disp, 'INV_TXN_MANAGER_PUB', 9);
2390 END IF;
2391
2392 RETURN -1;
2393 END IF;
2394
2395 /*+-----------------------------------------------------------------+
2396 | Check if we are processing WIP transactions to determine which |
2397 | to invoke to process transactions |
2398 +-----------------------------------------------------------------+*/
2399 -- Pawan added wip_entity_type for gme_inventory convergence
2400 SELECT transaction_source_type_id, wip_entity_type
2401 INTO l_srctypeid, l_wip_entity_type
2402 FROM mtl_material_transactions_temp
2403 WHERE transaction_header_id = l_header_id AND ROWNUM < 2;
2404
2405 done := FALSE;
2406 FIRST := TRUE;
2407
2408 WHILE (NOT done)
2409 LOOP
2410 IF (FIRST)
2411 THEN
2412 IF (l_debug = 1)
2413 THEN
2414 inv_log_util.TRACE ('Calling Process_lpn_trx'
2415 , 'INV_TXN_MANAGER_PUB'
2416 , 9
2417 );
2418 END IF;
2419
2420 fnd_message.set_name ('INV', 'INV_CALL_PROC');
2421 fnd_message.set_token ('token1', l_header_id);
2422 fnd_message.set_token ('token2', l_totrows);
2423 l_disp := fnd_message.get;
2424
2425 IF (l_debug = 1)
2426 THEN
2427 inv_log_util.TRACE (l_disp, 'INV_TXN_MANAGER_PUB', 9);
2428 END IF;
2429 END IF;
2430
2431 -- If transactions are of type WIP, then call the WIP API. This
2432 -- API does the WIP pre-processing before calling
2433 --process_lpn_trx
2434 /** WIP J dev condition. Add another condtion in the if
2435 /* statement below. if WIP.J is not installed call
2436 /* wip_mtlTempProc_grp()...else call process_lpn_trx()*/
2437 IF ( l_srctypeid = 5
2438 AND wip_constants.dmf_patchset_level <
2439 wip_constants.dmf_patchset_j_value
2440 )
2441 THEN
2442 wip_mtltempproc_grp.processtemp
2443 (p_initmsglist => fnd_api.g_false
2444 , p_processinv => fnd_api.g_true
2445 , -- call INV TM after WIP logic
2446 p_txnhdrid => l_header_id
2447 , x_returnstatus => l_return_status
2448 , x_errormsg => l_msg_data
2449 );
2450
2451 IF (l_return_status <> fnd_api.g_ret_sts_success)
2452 THEN
2453 IF (l_debug = 1)
2454 THEN
2455 inv_log_util.TRACE ('Failure from MMTT:WIP processTemp!!'
2456 , 'INV_TXN_MANAGER_PUB'
2457 , 1
2458 );
2459 END IF;
2460
2461 l_result := -1;
2462 END IF;
2463 ELSE
2464 l_result :=
2465 inv_lpn_trx_pub.process_lpn_trx (p_trx_hdr_id => l_header_id
2466 , p_commit => p_commit
2467 , x_proc_msg => l_msg_data
2468 , p_proc_mode => 1
2469 , p_process_trx => fnd_api.g_true
2470 , p_atomic => fnd_api.g_false
2471 );
2472 END IF;
2473
2474 IF (l_result <> 0)
2475 THEN
2476 fnd_message.set_name ('INV', 'INV_INT_PROCCODE');
2477
2478 IF (l_debug = 1)
2479 THEN
2480 inv_log_util.TRACE ('Error from PROCESS_LPN_TRX.. ' || l_msg_data
2481 , 'INV_TXN_MANAGER_PUB'
2482 , 9
2483 );
2484 END IF;
2485
2486 l_error_exp := l_msg_data;
2487 x_msg_data := l_msg_data;
2488 x_return_status := l_return_status;
2489
2490 /* No need to update MMTT after returning from process_lpn_trx as this has already
2491 been done within the Java code. - Bug 2284667 */
2492 IF fnd_api.to_boolean (p_commit)
2493 THEN
2494 COMMIT WORK;
2495 END IF;
2496
2497 RETURN -1;
2498 END IF;
2499
2500 IF (l_debug = 1)
2501 THEN
2502 inv_log_util.TRACE ('After process_lpn_trx without errors'
2503 , 'INV_TXN_MANAGER_PUB'
2504 , 9
2505 );
2506 END IF;
2507
2508 IF (FIRST)
2509 THEN
2510 fnd_message.set_name ('INV', 'INV_RETURN_PROC');
2511 l_disp := fnd_message.get;
2512
2513 IF (l_debug = 1)
2514 THEN
2515 inv_log_util.TRACE (l_disp, 'INV_TXN_MANAGER_PUB', 9);
2516 END IF;
2517 END IF;
2518
2519 IF fnd_api.to_boolean (p_commit)
2520 THEN
2521 COMMIT WORK;
2522 END IF;
2523
2524 IF (FIRST)
2525 THEN
2526 IF (NOT bflushchk (l_header_id))
2527 THEN
2528 l_error_exp := fnd_message.get;
2529
2530 IF (l_debug = 1)
2531 THEN
2532 inv_log_util.TRACE ('Error in call to bflushchk'
2533 , 'INV_TXN_MANAGER_PUB'
2534 , 9
2535 );
2536 END IF;
2537
2538 --ROLLBACK WORK;
2539 RETURN -1;
2540 END IF;
2541
2542 IF (l_header_id <> -1)
2543 THEN
2544 fnd_message.set_name ('INV', 'INV_BFLUSH_PROC');
2545 l_disp := fnd_message.get;
2546
2547 IF (l_debug = 1)
2548 THEN
2549 inv_log_util.TRACE (l_disp, 'INV_TXN_MANAGER_PUB', 9);
2550 END IF;
2551
2552 SELECT COUNT (*)
2553 INTO l_totrows
2554 FROM mtl_material_transactions_temp
2555 WHERE transaction_header_id = l_header_id AND process_flag = 'Y';
2556
2557 IF (l_totrows > 200)
2558 THEN
2559 UPDATE mtl_material_transactions_temp
2560 SET transaction_header_id = (-1) * l_header_id
2561 WHERE transaction_header_id = l_header_id
2562 AND process_flag = 'Y';
2563
2564 UPDATE mtl_material_transactions_temp
2565 SET transaction_header_id = ABS (l_header_id)
2566 WHERE transaction_header_id = (-1) * (l_header_id)
2567 AND process_flag = 'Y'
2568 AND ROWNUM < 201;
2569 END IF;
2570
2571 fnd_message.set_name ('INV', 'INV_CALL_PROC');
2572 fnd_message.set_token ('token1', l_header_id);
2573 fnd_message.set_token ('token2', l_totrows);
2574 l_disp := fnd_message.get;
2575
2576 IF (l_debug = 1)
2577 THEN
2578 inv_log_util.TRACE (l_disp, 'INV_TXN_MANAGER_PUB', 9);
2579 END IF;
2580 ELSE
2581 done := TRUE;
2582 END IF;
2583
2584 FIRST := FALSE;
2585 ELSE
2586 UPDATE mtl_material_transactions_temp
2587 SET transaction_header_id = ABS (l_header_id)
2588 WHERE transaction_header_id = (-1) * (l_header_id)
2589 AND process_flag = 'Y'
2590 AND ROWNUM < 201;
2591
2592 IF SQL%NOTFOUND
2593 THEN
2594 fnd_message.set_name ('INV', 'INV_RETURN_PROC');
2595 done := TRUE;
2596 END IF;
2597 END IF;
2598 END LOOP;
2599 ELSE
2600
2601 /** Table = 1 - MTL_TRANSACTIONS_INTERFACE **/
2602 /** Table = 1 - MTL_TRANSACTIONS_INTERFACE **/
2603 /*Patchset J:Trip Stop Interface Enhancements:setting the
2604 /*transaction batch id for Shipping transactions depending
2605 /*on the profile INV:Batch Size*/
2606 /*Bug 3947667, enabling it irrespective of patchset */
2607
2608 -- IF (INV_CONTROL.G_CURRENT_RELEASE_LEVEL >= INV_RELEASE.G_J_RELEASE_LEVEL) THEN
2609 BEGIN
2610 l_batch_size := NVL (fnd_profile.VALUE ('INV_BATCH_SIZE'), 0);
2611 EXCEPTION
2612 WHEN VALUE_ERROR
2613 THEN
2614 l_batch_size := 0;
2615 inv_log_util.TRACE
2616 ('Inv Batch size set to null for non numeric value'
2617 , 'INV_TXN_MANAGER_PUB'
2618 , 9
2619 );
2620 END;
2621
2622 IF (l_debug = 1)
2623 THEN
2624 inv_log_util.TRACE ('Inv Batch size:' || l_batch_size
2625 , 'INV_TXN_MANAGER_PUB'
2626 , 9
2627 );
2628 END IF;
2629
2630 UPDATE mtl_transactions_interface
2631 SET last_update_date = SYSDATE
2632 , transaction_interface_id =
2633 NVL (transaction_interface_id
2634 , mtl_material_transactions_s.NEXTVAL
2635 )
2636 , transaction_batch_id =
2637 NVL (transaction_batch_id
2638 , DECODE (transaction_source_type_id
2639 , 2, DECODE (l_batch_size
2640 , 0, transaction_batch_id
2641 , CEIL (ROWNUM / l_batch_size)
2642 )
2643 , 8, DECODE (l_batch_size
2644 , 0, transaction_batch_id
2645 , CEIL (ROWNUM / l_batch_size)
2646 )
2647 , 16, DECODE (l_batch_size
2648 , 0, transaction_batch_id
2649 , CEIL (ROWNUM / l_batch_size)
2650 )
2651 , transaction_batch_id
2652 )
2653 )
2654 , last_updated_by = l_userid
2655 , last_update_login = l_loginid
2656 , program_application_id = l_applid
2657 , program_id = l_progid
2658 , request_id = l_reqstid
2659 , program_update_date = SYSDATE
2660 , lock_flag = 1
2661 WHERE process_flag = 1 AND transaction_header_id = l_header_id;
2662
2663 /* ELSE
2664
2665 UPDATE MTL_TRANSACTIONS_INTERFACE
2666 SET LAST_UPDATE_DATE = SYSDATE,
2667 TRANSACTION_INTERFACE_ID = NVL(TRANSACTION_INTERFACE_ID,
2668 mtl_material_transactions_s.nextval),
2669 LAST_UPDATED_BY = l_userid,
2670 LAST_UPDATE_LOGIN = l_loginid,
2671 PROGRAM_APPLICATION_ID = l_applid,
2672 PROGRAM_ID = l_progid,
2673 REQUEST_ID = l_reqstid,
2674 PROGRAM_UPDATE_DATE = SYSDATE,
2675 LOCK_FLAG = 1
2676 WHERE PROCESS_FLAG = 1
2677 AND TRANSACTION_HEADER_ID = l_header_id;
2678 END IF; */
2679 l_initotrows := SQL%ROWCOUNT;
2680
2681 IF fnd_api.to_boolean (p_commit)
2682 THEN
2683 COMMIT WORK;
2684 END IF;
2685
2686 IF (l_debug = 1)
2687 THEN
2688 inv_log_util.TRACE ('MTI Rows cnt before Validation=' || l_initotrows
2689 , 'INV_TXN_MANAGER_PUB'
2690 , 9
2691 );
2692 END IF;
2693
2694 IF (l_totrows = 0)
2695 THEN
2696 fnd_message.set_name ('INV', 'INV_PROC_WARN');
2697 l_disp := fnd_message.get;
2698
2699 IF (l_debug = 1)
2700 THEN
2701 inv_log_util.TRACE (l_disp || ' totrows = 0'
2702 , 'INV_TXN_MANAGER_PUB'
2703 , 9
2704 );
2705 END IF;
2706
2707 RETURN -1;
2708 END IF;
2709
2710 /*+-----------------------------------------------------------------+
2711 | Check if we are processing WIP transactions to determine whether|
2712 | to do the derivation for flow_schedule. |
2713 +-----------------------------------------------------------------+*/
2714 -- Pawan Added the wip_entity_type
2715 SELECT NVL (validation_required, 1)
2716 , mtt.transaction_source_type_id, mti.wip_entity_type
2717 INTO l_valreq
2718 , l_srctypeid, l_wip_entity_type
2719 FROM mtl_transactions_interface mti, mtl_transaction_types mtt
2720 WHERE transaction_header_id = l_header_id
2721 AND mtt.transaction_type_id = mti.transaction_type_id
2722 AND ROWNUM < 2;
2723
2724 /*+--------------------------------------------------------------+
2725 | The global INV_TXN_MANAGER_GRP.gi_flow_schedule will be '1' (or true) for |
2726 | WIP flow schedules ONLY. |
2727 +--------------------------------------------------------------+ */
2728 IF (l_srctypeid = 5)
2729 THEN
2730 BEGIN
2731 SELECT DECODE (UPPER (flow_schedule), 'Y', 1, 0)
2732 INTO inv_txn_manager_grp.gi_flow_schedule
2733 FROM mtl_transactions_interface
2734 WHERE transaction_header_id = l_header_id
2735 AND transaction_source_type_id = 5
2736 AND transaction_action_id IN
2737 (30, 31, 32) --CFM Scrap Transactions
2738 AND process_flag = 1
2739 AND ROWNUM < 2;
2740 EXCEPTION
2741 WHEN NO_DATA_FOUND
2742 THEN
2743 inv_txn_manager_grp.gi_flow_schedule := 0;
2744 END;
2745 ELSE
2746 inv_txn_manager_grp.gi_flow_schedule := 0;
2747 END IF;
2748
2749 /** WIP J dev condition. If WIP J is not installed do as now,
2750 /*else call a new new API wip_mti_pub.preInvWIPProcessing()
2751 /* This has to be called before validate_group()
2752 /* we should retain create_flow sch for WIP I and below.*/
2753 IF ( l_srctypeid = 5
2754 AND wip_constants.dmf_patchset_level >=
2755 wip_constants.dmf_patchset_j_value
2756 )
2757 THEN
2758 -- Pawan Added following changes for gme- convergence
2759 IF l_wip_entity_type = 10 THEN
2760 IF (l_debug = 1) THEN
2761 inv_log_util.trace('in for gme pre_process','INV_TXN_MANAGER_PUB', 9);
2762 END IF;
2763 gme_api_grp.gme_pre_process_txns
2764 ( p_header_id => l_header_id,
2765 x_return_status => l_ret_sts_pre) ;
2766 IF (l_ret_sts_pre = fnd_api.g_ret_sts_success) THEN
2767 IF (l_debug = 1) THEN
2768 inv_log_util.trace('Success from:!!gme_api_grp.gme_pre_process_txns', 'INV_TXN_MANAGER_PUB',1);
2769 END IF;
2770
2771 IF FND_API.To_Boolean( p_commit ) then
2772 COMMIT WORK; /* Commit after preInvWIP all MTI records */
2773 END IF;
2774
2775 --check if all records have been failed by the wip API.
2776 BEGIN
2777 SELECT transaction_source_type_id INTO l_source_type_id
2778 FROM MTL_TRANSACTIONS_INTERFACE
2779 WHERE TRANSACTION_HEADER_ID = l_header_id
2780 AND PROCESS_FLAG = 1
2781 AND ROWNUM < 2;
2782 EXCEPTION
2783 WHEN NO_DATA_FOUND THEN
2784 x_return_status := FND_API.G_RET_STS_ERROR;
2785 x_msg_data := 'All records failed by gme_api_grp.gme_pre_process_txns';
2786 RETURN -1;
2787 END;
2788 ELSE
2789 IF (l_debug = 1) THEN
2790 inv_log_util.trace('Failure from:!!gme_api_grp.gme_pre_process_txns', 'INV_TXN_MANAGER_PUB',1);
2791 END IF;
2792 RAISE fnd_api.g_exc_unexpected_error;
2793 END IF;--check for success
2794 ELSE/*l_wip_entity_type = 10 */
2795
2796 wip_mti_pub.preinvwipprocessing (p_txnheaderid => l_header_id
2797 , x_returnstatus => l_ret_sts_pre
2798 );
2799
2800 IF (l_ret_sts_pre = fnd_api.g_ret_sts_success)
2801 THEN
2802 IF (l_debug = 1)
2803 THEN
2804 inv_log_util.TRACE ('Success from:!!preInvWIPProcessing'
2805 , 'INV_TXN_MANAGER_PUB'
2806 , 1
2807 );
2808 END IF;
2809
2810 IF fnd_api.to_boolean (p_commit)
2811 THEN
2812 COMMIT WORK; /* Commit after preInvWIP all MTI records */
2813 END IF;
2814
2815 --check if all records have been failed by the wip API.
2816 BEGIN
2817 SELECT transaction_source_type_id
2818 INTO l_source_type_id
2819 FROM mtl_transactions_interface
2820 WHERE transaction_header_id = l_header_id
2821 AND process_flag = 1
2822 AND ROWNUM < 2;
2823 EXCEPTION
2824 WHEN NO_DATA_FOUND
2825 THEN
2826 x_return_status := fnd_api.g_ret_sts_error;
2827 x_msg_data := 'All records failed by preInvWipProcessing';
2828 RETURN -1;
2829 END;
2830 ELSE
2831 IF (l_debug = 1)
2832 THEN
2833 inv_log_util.TRACE ('Failure from:!!preInvWIPProcessing'
2834 , 'INV_TXN_MANAGER_PUB'
2835 , 1
2836 );
2837 END IF;
2838
2839 RAISE fnd_api.g_exc_unexpected_error;
2840 END IF; --check for success
2841 END IF;--for l_wip_entity_type Pawan added for gme-convergence changes.
2842 ELSE
2843 IF (inv_txn_manager_grp.gi_flow_schedule <> 0)
2844 THEN
2845 wip_flow_utilities.create_flow_schedules (l_header_id);
2846 END IF;
2847 END IF; --J-dev
2848
2849 /***** Group Validation *******************************/
2850 validate_group (l_header_id
2851 , x_return_status
2852 , x_msg_count
2853 , x_msg_data
2854 , l_userid
2855 , l_loginid
2856 );
2857
2858 IF x_return_status = fnd_api.g_ret_sts_error
2859 THEN
2860 IF (l_debug = 1)
2861 THEN
2862 inv_log_util.TRACE ( 'Unexpected Error in Validate Group : '
2863 || x_msg_data
2864 , 'INV_TXN_MANAGER_PUB'
2865 , 9
2866 );
2867 END IF;
2868
2869 RAISE fnd_api.g_exc_unexpected_error;
2870 END IF;
2871
2872 /** Moved to after Validate_lines loop J-dev*/
2873 /******* Group Validation for WIP records *******************/
2874 /* This WIP API could potentially error some records in MTI. If any records
2875 /* have been errored, they would be stamped with error-code/explanation */
2876 /*IF (l_srctypeid = 5 ) THEN
2877 wip_mti_pub.postInvWIPValidation(
2878 p_txnHeaderID => l_header_id,
2879 x_returnStatus => x_return_status
2880 );
2881 END IF;*/
2882 IF fnd_api.to_boolean (p_commit)
2883 THEN
2884 COMMIT WORK; /* Commit after group validating all MTI records */
2885 END IF;
2886
2887 IF (l_debug = 1)
2888 THEN
2889 inv_log_util.TRACE ('Group validation complete '
2890 , 'INV_TXN_MANAGER_PUB'
2891 , 9
2892 );
2893 END IF;
2894
2895 batch_error := FALSE;
2896
2897 FOR l_line_rec_type IN aa1
2898 LOOP
2899 BEGIN
2900 l_trx_batch_id := l_line_rec_type.transaction_batch_id;
2901
2902 IF batch_error AND l_trx_batch_id = l_last_trx_batch_id
2903 THEN
2904 /** This group of transactions has failed move on to next **/
2905 /** UPDATE MTI row with Group Failure Message **/
2906 NULL;
2907 ELSE
2908 batch_error := FALSE;
2909 l_last_trx_batch_id := l_trx_batch_id;
2910
2911 /** Change for Lot Transactions **/
2912 IF (l_line_rec_type.transaction_source_type_id = 13)
2913 THEN
2914 IF (l_line_rec_type.transaction_action_id IN (40, 41, 42))
2915 THEN
2916 IF (l_debug = 1)
2917 THEN
2918 inv_log_util.TRACE ( 'Previous parent: '
2919 || l_previous_parent_id
2920 , 'INV_TXN_MANAGER_PUB'
2921 , 9
2922 );
2923 inv_log_util.TRACE ( 'Current parent: '
2924 || l_line_rec_type.parent_id
2925 , 'INV_TXN_MANAGER_PUB'
2926 , 9
2927 );
2928 END IF;
2929
2930 IF (NVL (l_previous_parent_id, 0) <> l_line_rec_type.parent_id
2931 )
2932 THEN
2933 /***** Its a new Batch. Before we do any validations, we have to
2934 -- check for the transaction bacth id. For all lot
2935 -- transctions, the batch id should be filled in for
2936 -- the TM to perform a complete rollback in case any
2937 -- of the records fail within a group/batch.
2938 -- Bug 2804402
2939 *******/
2940 l_batch_count := 0;
2941
2942 SELECT COUNT (1)
2943 INTO l_batch_count
2944 FROM mtl_transactions_interface
2945 WHERE parent_id = l_line_rec_type.parent_id
2946 AND transaction_batch_id IS NULL;
2947
2948 IF (l_batch_count > 0)
2949 THEN
2950 loaderrmsg ('INV_INVALID_BATCH'
2951 , 'INV_INVALID_BATCH_NUMBER'
2952 );
2953
2954 UPDATE mtl_transactions_interface
2955 SET last_update_date = SYSDATE
2956 , last_updated_by = l_userid
2957 , last_update_login = l_loginid
2958 , program_update_date = SYSDATE
2959 , process_flag = 3
2960 , lock_flag = 2
2961 , ERROR_CODE = SUBSTR (l_error_code, 1, 240)
2962 , error_explanation = SUBSTRB (l_error_exp, 1, 240)
2963 WHERE parent_id = l_line_rec_type.parent_id
2964 AND process_flag = 1;
2965
2966 RAISE rollback_line_validation;
2967 END IF;
2968
2969 l_index := 0;
2970 l_previous_parent_id := l_line_rec_type.parent_id;
2971 l_validation_status := 'Y';
2972 END IF;
2973
2974 l_index := l_index + 1;
2975 /*l_index identifies the distinct parent_id's and processes them
2976 *in one go
2977 */
2978 IF (l_index = 1)
2979 THEN
2980 IF (l_line_rec_type.transaction_action_id = 40)
2981 THEN
2982 fnd_message.set_name ('INV', 'INV_LOT_SPLIT_VALIDATIONS');
2983 l_error_code := fnd_message.get;
2984 inv_lot_trx_validation_pvt.validate_lot_split_trx
2985 (x_return_status => x_return_status
2986 , x_msg_count => x_msg_count
2987 , x_msg_data => x_msg_data
2988 , x_validation_status => l_validation_status
2989 , p_parent_id => l_line_rec_type.parent_id
2990 );
2991
2992 IF (x_return_status <> fnd_api.g_ret_sts_success)
2993 THEN
2994 -- Fetch all the error messages from the stack and log them.
2995 -- Update the MTI with last error message only, since the error messages can be redundant.
2996 FOR i IN 1 .. x_msg_count
2997 LOOP
2998 x_msg_data := fnd_msg_pub.get (i, 'F');
2999
3000 IF (l_debug = 1)
3001 THEN
3002 inv_log_util.TRACE
3003 ( 'Error in Validate_lot_Split_Trx: '
3004 || x_msg_data
3005 , 'INV_TXN_MANAGER_PUB'
3006 , 9
3007 );
3008 END IF;
3009 END LOOP;
3010
3011 UPDATE mtl_transactions_interface
3012 SET last_update_date = SYSDATE
3013 , last_updated_by = l_userid
3014 , last_update_login = l_loginid
3015 , program_update_date = SYSDATE
3016 , process_flag = 3
3017 , lock_flag = 2
3018 , ERROR_CODE = SUBSTR (l_error_code, 1, 240)
3019 , error_explanation = SUBSTRB (x_msg_data, 1, 240)
3020 WHERE ROWID = l_line_rec_type.ROWID
3021 AND process_flag = 1;
3022
3023 RAISE rollback_line_validation;
3024 END IF;
3025 ELSIF (l_line_rec_type.transaction_action_id = 41)
3026 THEN
3027 fnd_message.set_name ('INV', 'INV_LOT_MERGE_VALIDATIONS');
3028 l_error_code := fnd_message.get;
3029 inv_lot_trx_validation_pvt.validate_lot_merge_trx
3030 (x_return_status => x_return_status
3031 , x_msg_count => x_msg_count
3032 , x_msg_data => x_msg_data
3033 , x_validation_status => l_validation_status
3034 , p_parent_id => l_line_rec_type.parent_id
3035 );
3036
3037 IF (x_return_status <> fnd_api.g_ret_sts_success)
3038 THEN
3039 -- Fetch all the error messages from the stack and log them.
3040 -- Update the MTI with last error message only, since the error messages can be redundant.
3041 FOR i IN 1 .. x_msg_count
3042 LOOP
3043 x_msg_data := fnd_msg_pub.get (i, 'F');
3044
3045 IF (l_debug = 1)
3046 THEN
3047 inv_log_util.TRACE
3048 ( 'Error in Validate_lot_Merge_Trx: '
3049 || x_msg_data
3050 , 'INV_TXN_MANAGER_PUB'
3051 , 9
3052 );
3053 END IF;
3054 END LOOP;
3055
3056 UPDATE mtl_transactions_interface
3057 SET last_update_date = SYSDATE
3058 , last_updated_by = l_userid
3059 , last_update_login = l_loginid
3060 , program_update_date = SYSDATE
3061 , process_flag = 3
3062 , lock_flag = 2
3063 , ERROR_CODE = SUBSTRB (l_error_code, 1, 240)
3064 , error_explanation = SUBSTRB (x_msg_data, 1, 240)
3065 WHERE ROWID = l_line_rec_type.ROWID
3066 AND process_flag = 1;
3067
3068 RAISE rollback_line_validation;
3069 END IF;
3070 ELSIF (l_line_rec_type.transaction_action_id = 42)
3071 THEN
3072 fnd_message.set_name ('INV'
3073 , 'INV_LOT_TRANSLATE_VALIDATIONS'
3074 );
3075 l_error_code := fnd_message.get;
3076 inv_lot_trx_validation_pvt.validate_lot_translate_trx
3077 (x_return_status => x_return_status
3078 , x_msg_count => x_msg_count
3079 , x_msg_data => x_msg_data
3080 , x_validation_status => l_validation_status
3081 , p_parent_id => l_line_rec_type.parent_id
3082 );
3083
3084 IF (x_return_status <> fnd_api.g_ret_sts_success)
3085 THEN
3086 -- Fetch all the error messages from the stack and log them.
3087 -- Update the MTI with last error message only, since the error messages can be redundant.
3088 FOR i IN 1 .. x_msg_count
3089 LOOP
3090 x_msg_data := fnd_msg_pub.get (i, 'F');
3091
3092 IF (l_debug = 1)
3093 THEN
3094 inv_log_util.TRACE
3095 ( 'Error in Validate_lot_Translate_Trx: '
3096 || x_msg_data
3097 , 'INV_TXN_MANAGER_PUB'
3098 , 9
3099 );
3100 END IF;
3101 END LOOP;
3102
3103 UPDATE mtl_transactions_interface
3104 SET last_update_date = SYSDATE
3105 , last_updated_by = l_userid
3106 , last_update_login = l_loginid
3107 , program_update_date = SYSDATE
3108 , process_flag = 3
3109 , lock_flag = 2
3110 , ERROR_CODE = SUBSTR (l_error_code, 1, 240)
3111 , error_explanation = SUBSTRB (x_msg_data, 1, 240)
3112 WHERE ROWID = l_line_rec_type.ROWID
3113 AND process_flag = 1;
3114
3115 RAISE rollback_line_validation;
3116 END IF;
3117 END IF;
3118 END IF;
3119 END IF;
3120 END IF;
3121
3122 /** End of Change for Lot Transactions ***/
3123 IF ( l_line_rec_type.transaction_source_type_id = 13
3124 AND l_line_rec_type.transaction_action_id IN (40, 41, 42)
3125 AND l_index > 1
3126 AND l_validation_status <> 'Y'
3127 )
3128 THEN
3129 IF (l_line_rec_type.transaction_action_id = 40)
3130 THEN
3131 fnd_message.set_name ('INV', 'INV_LOT_SPLIT_VALIDATIONS');
3132 l_error_code := fnd_message.get;
3133 ELSIF (l_line_rec_type.transaction_action_id = 41)
3134 THEN
3135 fnd_message.set_name ('INV', 'INV_LOT_MERGE_VALIDATIONS');
3136 l_error_code := fnd_message.get;
3137 ELSIF (l_line_rec_type.transaction_action_id = 42)
3138 THEN
3139 fnd_message.set_name ('INV', 'INV_LOT_TRANSLATE_VALIDATIONS');
3140 l_error_code := fnd_message.get;
3141 END IF;
3142
3143 UPDATE mtl_transactions_interface
3144 SET last_update_date = SYSDATE
3145 , last_updated_by = l_userid
3146 , last_update_login = l_loginid
3147 , program_update_date = SYSDATE
3148 , process_flag = 3
3149 , lock_flag = 2
3150 , ERROR_CODE = SUBSTR (l_error_code, 1, 240)
3151 , error_explanation = SUBSTRB (x_msg_data, 1, 240)
3152 WHERE ROWID = l_line_rec_type.ROWID AND process_flag = 1;
3153
3154 RAISE rollback_line_validation;
3155 END IF;
3156
3157 /* bug 2807083, populate the distribution account id of lot translate txn */
3158 IF (l_debug = 1)
3159 THEN
3160 inv_log_util.TRACE
3161 ( 'l_line_rec_type.distribution_account_id is '
3162 || l_line_rec_type.distribution_account_id
3163 , 'INV_TXN_MANAGER_PUB'
3164 , 9
3165 );
3166 END IF;
3167
3168 IF (l_line_rec_type.distribution_account_id IS NULL)
3169 THEN
3170 SELECT distribution_account_id
3171 INTO l_dist_acct_id
3172 FROM mtl_transactions_interface
3173 WHERE ROWID = l_line_rec_type.ROWID;
3174
3175 l_line_rec_type.distribution_account_id := l_dist_acct_id;
3176 END IF;
3177
3178 IF (l_debug = 1)
3179 THEN
3180 inv_log_util.TRACE ('l_dist_acct_id is ' || l_dist_acct_id
3181 , 'INV_TXN_MANAGER_PUB'
3182 , 9
3183 );
3184 END IF;
3185
3186 validate_lines (p_line_rec_type => l_line_rec_type
3187 , p_error_flag => line_vldn_error_flag
3188 , p_userid => l_userid
3189 , p_loginid => l_loginid
3190 , p_applid => l_applid
3191 , p_progid => l_progid
3192 );
3193
3194
3195 IF (line_vldn_error_flag = 'Y')
3196 THEN
3197 IF (l_debug = 1)
3198 THEN
3199 inv_log_util.TRACE ('Error in Line Validatin'
3200 , 'INV_TXN_MANAGER_PUB'
3201 , 9
3202 );
3203 END IF;
3204
3205 RAISE rollback_line_validation;
3206 END IF;
3207
3208 /*Bug:5209598. Start of code.Following Code has been added to perform ATT/ATR high/low level
3209 checks for WIP transactions. In VALIDATE_TRANSACTIONS() of pkg INV_TXN_MANAGER_GRP, high/low level
3210 reservation checks are performed for WIP transactions.But for those WIP transactions that do not
3211 go through the validate_transactions() procedure, the following code does the reservation checks
3212 before inserting record into MMTT.
3213 */
3214 IF ( l_srctypeid = 5 ) THEN
3215 IF ( l_current_err_batch_id IS NULL
3216 OR l_Line_rec_Type.transaction_batch_id IS NULL
3217 OR l_current_err_batch_id <> l_Line_rec_Type.transaction_batch_id )THEN --050
3218 l_current_batch_failed := FALSE;
3219 FOR z1_rec IN
3220 Z1(inv_txn_manager_grp.gi_flow_schedule,l_Line_rec_type) LOOP
3221
3222 tree_exists := FALSE;
3223
3224 IF (l_debug = 1) THEN
3225 inv_log_util.trace('Getting values from Z1 cursor', 'INV_TXN_MANAGER_PUB', 9);
3226 END IF;
3227
3228 l_temp_rowid :=z1_rec.ROWID;
3229 l_item_id:=z1_rec.inventory_item_id;
3230 l_rev:=z1_rec.revision;
3231 l_org_id:=z1_rec.organization_id;
3232 l_sub_code:=z1_rec.subinventory_code;
3233 l_locid :=z1_rec.locator_id;
3234 l_trx_qty:=z1_rec.primary_quantity;
3235 l_lotnum:=z1_rec.lot_number;
3236 l_srctypeid:=z1_rec.transaction_source_type_id;
3237 l_actid:=z1_rec.transaction_action_id;
3238 l_srcid:=z1_rec.transaction_source_id;
3239 l_src_code:=z1_rec.transaction_source_name;
3240 l_srclineid:=z1_rec.source_line_id;
3241 l_rctrl:=z1_rec.revision_qty_control_code;
3242 l_lctrl:=z1_rec.lot_control_code;
3243 l_xfrsub:=z1_rec.transfer_subinventory;
3244 l_xlocid:=z1_rec.transfer_locator;
3245 l_trxdate:=z1_rec.TRANSACTION_DATE;
3246 l_neg_inv_rcpt:=z1_rec.negative_inv_receipt_code;
3247
3248 --Bug 6454464, we should not call available qty validation for CMRO job type
3249 inv_reservation_pvt.get_wip_entity_type
3250 ( p_api_version_number => 1.0
3251 , p_init_msg_lst => fnd_api.g_false
3252 , x_return_status => l_return_status
3253 , x_msg_count => l_msg_count
3254 , x_msg_data => l_msg_data
3255 , p_organization_id => null
3256 , p_item_id => null
3257 , p_source_type_id => null
3258 , p_source_header_id => l_srcid
3259 , p_source_line_id => null
3260 , p_source_line_detail => null
3261 , x_wip_entity_type => l_rsv_wip_entity_type
3262 , x_wip_job_type => l_rsv_wip_job_type
3263 );
3264
3265 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
3266 inv_log_util.TRACE ('Return status from get wip entity. '||l_return_status, 'INV_TXN_MANAGER_PUB', 9);
3267 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
3268 inv_log_util.TRACE ('Return status from get wip entity. '||l_return_status, 'INV_TXN_MANAGER_PUB', 9);
3269 END IF;
3270
3271 IF (l_debug = 1) THEN
3272 inv_log_util.TRACE ('Wip entity type ' || l_rsv_wip_entity_type, 'INV_TXN_MANAGER_PUB', 9);
3273 END IF;
3274
3275 --Bug 6454464, we should not call available qty validation for CMRO job type
3276 IF (l_rsv_wip_entity_type <> inv_reservation_global.g_wip_source_type_cmro) THEN
3277
3278 IF l_rctrl = 1 THEN
3279 l_revision_control := FALSE;
3280 ELSE
3281 l_revision_control := TRUE;
3282 END IF;
3283
3284 IF l_lctrl = 1 THEN
3285 l_lot_control := FALSE;
3286 ELSE
3287 l_lot_control := TRUE;
3288 END IF;
3289
3290 IF (l_debug = 1) THEN
3291 inv_log_util.trace('Calling Create tree', 'INV_TXN_MANAGER_PUB', 9);
3292 END IF;
3293
3294 -- Bug 4194323 WIP Assembly Return transactions need to look for Available Quantity
3295 --against the Sales Order if it's linked to job
3296
3297 IF ( NOT l_current_batch_failed) THEN --350
3298 BEGIN
3299 SELECT demand_source_header_id , demand_source_line
3300 INTO l_dem_hdr_id,l_dem_line_id
3301 FROM mtl_transactions_interface
3302 WHERE
3303 ROWID = l_temp_rowid ;
3304 EXCEPTION
3305 WHEN OTHERS THEN
3306 IF (l_debug = 1) THEN
3307 inv_log_util.trace('Error in getting Demand Info : '
3308 || x_msg_data,'INV_TXN_MANAGER_PUB', 9);
3309 END IF;
3310 l_error_code := 'Error in getting Demand Info';
3311 l_error_exp := 'Error in getting Demand Info';
3312
3313 UPDATE MTL_TRANSACTIONS_INTERFACE
3314 SET LAST_UPDATE_DATE = SYSDATE,
3315 LAST_UPDATED_BY = l_userid,
3316 LAST_UPDATE_LOGIN = l_loginid,
3317 PROGRAM_UPDATE_DATE = SYSDATE,
3318 PROCESS_FLAG = 3,
3319 LOCK_FLAG = 2,
3320 ERROR_CODE = substrb(l_error_code,1,240),
3321 ERROR_EXPLANATION = substrb(l_error_exp,1,240)
3322 WHERE ROWID = l_temp_rowid
3323 AND PROCESS_FLAG = 1;
3324 --check for batch error
3325 loaderrmsg('INV_GROUP_ERROR','INV_GROUP_ERROR');
3326
3327 UPDATE MTL_TRANSACTIONS_INTERFACE MTI
3328 SET LAST_UPDATE_DATE = SYSDATE,
3329 LAST_UPDATED_BY = l_userid,
3330 LAST_UPDATE_LOGIN = l_loginid,
3331 PROGRAM_UPDATE_DATE = SYSDATE,
3332 PROCESS_FLAG = 3,
3333 LOCK_FLAG = 2,
3334 ERROR_CODE = substrb(l_error_code,1,240)
3335 WHERE TRANSACTION_HEADER_ID = l_header_id
3336 AND PROCESS_FLAG = 1
3337 AND TRANSACTION_BATCH_ID =l_Line_rec_Type.transaction_batch_id;
3338
3339 l_current_batch_failed := TRUE;--Bug#5075521
3340 l_current_err_batch_id := l_Line_rec_Type.transaction_batch_id;--Bug#5075521
3341 END ;
3342
3343 IF ( NOT l_current_batch_failed) THEN --400
3344 IF ( l_srctypeid = INv_GLOBALS.G_SOURCETYPE_WIP AND
3345 l_actid = INV_GLOBALS.G_ACTION_ASSYRETURN AND l_dem_hdr_id IS NOT NULL ) then
3346
3347
3348 INV_QUANTITY_TREE_PVT.create_tree
3349 ( p_api_version_number => 1.0
3350 , p_init_msg_lst => fnd_api.g_false
3351 , x_return_status => l_return_status
3352 , x_msg_count => l_msg_count
3353 , x_msg_data => l_msg_data
3354 , p_organization_id => l_org_id
3355 , p_inventory_item_id => l_item_id
3356 , p_tree_mode => 2
3357 , p_is_revision_control => l_revision_control
3358 , p_is_lot_control => l_lot_control
3359 , p_is_serial_control => FALSE
3360 , p_include_suggestion => FALSE
3361 , p_demand_source_type_id => 2
3362 , p_demand_source_header_id => nvl(l_dem_hdr_id,-9999)
3363 , p_demand_source_line_id => nvl(l_dem_line_id,-9999)
3364 , p_demand_source_name => l_src_code
3365 , p_demand_source_delivery => NULL
3366 , p_lot_expiration_date => NULL
3367 , x_tree_id => l_tree_id
3368 , p_onhand_source => 3 --g_all_subs
3369 , p_exclusive => 0 --g_non_exclusive
3370 , p_pick_release => 0 --g_pick_release_no
3371 ) ;
3372
3373
3374 ELSE
3375
3376 INV_QUANTITY_TREE_PVT.create_tree
3377 ( p_api_version_number => 1.0
3378 , p_init_msg_lst => fnd_api.g_false
3379 , x_return_status => l_return_status
3380 , x_msg_count => l_msg_count
3381 , x_msg_data => l_msg_data
3382 , p_organization_id => l_org_id
3383 , p_inventory_item_id => l_item_id
3384 , p_tree_mode => 2
3385 , p_is_revision_control => l_revision_control
3386 , p_is_lot_control => l_lot_control
3387 , p_is_serial_control => FALSE
3388 , p_include_suggestion => FALSE
3389 , p_demand_source_type_id => nvl(l_srctypeid,-9999)
3390 , p_demand_source_header_id => nvl(l_srcid,-9999)
3391 , p_demand_source_line_id => nvl(l_srclineid,-9999)
3392 , p_demand_source_name => l_src_code
3393 , p_demand_source_delivery => NULL
3394 , p_lot_expiration_date => NULL
3395 , x_tree_id => l_tree_id
3396 , p_onhand_source => 3 --g_all_subs
3397 , p_exclusive => 0 --g_non_exclusive
3398 , p_pick_release => 0 --g_pick_release_no
3399 ) ;
3400 END IF;
3401 -- Bug 4194323 Ends
3402
3403 IF (l_debug = 1) THEN
3404 inv_log_util.trace('After create tree tree : ' || l_msg_data,'INV_TXN_MANAGER_PUB', 9);
3405 END IF;
3406
3407 IF l_return_status IN (fnd_api.g_ret_sts_error, fnd_api.g_ret_sts_unexp_error) THEN
3408 IF (l_debug = 1) THEN
3409 inv_log_util.trace('Error while creating tree : x_msg_data = ' || l_msg_data,'INV_TXN_MANAGER_PUB', 9);
3410 END IF;
3411 FND_MESSAGE.set_name('INV','INV_ERR_CREATETREE');
3412 FND_MESSAGE.set_token('ROUTINE','UE:AVAIL_TO_TRX');
3413 l_error_code := FND_MESSAGE.get;
3414 l_error_exp := l_msg_data;
3415 x_msg_data := l_msg_data;
3416 UPDATE MTL_TRANSACTIONS_INTERFACE
3417 SET LAST_UPDATE_DATE = SYSDATE,
3418 LAST_UPDATED_BY = l_userid,
3419 LAST_UPDATE_LOGIN = l_loginid,
3420 PROGRAM_UPDATE_DATE = SYSDATE,
3421 PROCESS_FLAG = 3,
3422 LOCK_FLAG = 2,
3423 ERROR_CODE = substrb(l_error_code,1,240),
3424 ERROR_EXPLANATION = substrb(l_error_exp,1,240)
3425 WHERE ROWID = l_temp_rowid
3426 AND PROCESS_FLAG = 1;
3427
3428 --check for batch error
3429 loaderrmsg('INV_GROUP_ERROR','INV_GROUP_ERROR');
3430
3431 UPDATE MTL_TRANSACTIONS_INTERFACE MTI
3432 SET LAST_UPDATE_DATE = SYSDATE,
3433 LAST_UPDATED_BY = l_userid,
3434 LAST_UPDATE_LOGIN = l_loginid,
3435 PROGRAM_UPDATE_DATE = SYSDATE,
3436 PROCESS_FLAG = 3,
3437 LOCK_FLAG = 2,
3438 ERROR_CODE = substrb(l_error_code,1,240)
3439 WHERE TRANSACTION_HEADER_ID = l_header_id
3440 AND PROCESS_FLAG = 1
3441 AND TRANSACTION_BATCH_ID = l_Line_rec_Type.transaction_batch_id; --Bug#5075521
3442 -- group error changes.
3443
3444 l_current_batch_failed := TRUE;--Bug#5075521
3445 l_current_err_batch_id := l_Line_rec_Type.transaction_batch_id;--Bug#5075521
3446 END IF ;
3447 END IF; --400
3448 END IF;--350
3449
3450 IF ( NOT l_current_batch_failed) THEN --100
3451 INV_QUANTITY_TREE_PVT.query_tree
3452 ( p_api_version_number => 1.0
3453 , p_init_msg_lst => fnd_api.g_false
3454 , x_return_status => l_return_status
3455 , x_msg_count => l_msg_count
3456 , x_msg_data => l_msg_data
3457 , p_tree_id => l_tree_id
3458 , p_revision => l_rev
3459 , p_lot_number => l_lotnum
3460 , p_subinventory_code => l_sub_code
3461 , p_transfer_subinventory_code => l_xfrsub
3462 , p_locator_id => l_locid
3463 , x_qoh => l_qoh
3464 , x_rqoh => l_rqoh
3465 , x_pqoh => l_pqoh
3466 , x_qr => l_qr
3467 , x_qs => l_qs
3468 , x_att => l_att
3469 , x_atr => l_atr
3470 );
3471
3472 IF l_return_status = fnd_api.g_ret_sts_error THEN
3473 IF (l_debug = 1) THEN
3474 inv_log_util.trace('Expected Error while querying tree : ' || l_msg_data,'INV_TXN_MANAGER_PUB', 9);
3475 END IF;
3476
3477 FND_MESSAGE.set_name('INV','INV_INTERNAL_ERROR');
3478 FND_MESSAGE.set_token('token1','XACT_QTY1');
3479 l_error_code := FND_MESSAGE.get;
3480 l_error_exp := l_msg_data;
3481 x_msg_data := l_msg_data;
3482 UPDATE MTL_TRANSACTIONS_INTERFACE
3483 SET LAST_UPDATE_DATE = SYSDATE,
3484 LAST_UPDATED_BY = l_userid,
3485 LAST_UPDATE_LOGIN = l_loginid,
3486 PROGRAM_UPDATE_DATE = SYSDATE,
3487 PROCESS_FLAG = 3,
3488 LOCK_FLAG = 2,
3489 ERROR_CODE = substrb(l_error_code,1,240),
3490 ERROR_EXPLANATION = substrb(l_error_exp,1,240)
3491 WHERE TRANSACTION_interface_id = l_temp_rowid
3492 AND PROCESS_FLAG = 1;
3493 --check for batch error
3494 loaderrmsg('INV_GROUP_ERROR','INV_GROUP_ERROR');
3495
3496 UPDATE MTL_TRANSACTIONS_INTERFACE MTI
3497 SET LAST_UPDATE_DATE = SYSDATE,
3498 LAST_UPDATED_BY = l_userid,
3499 LAST_UPDATE_LOGIN = l_loginid,
3500 PROGRAM_UPDATE_DATE = SYSDATE,
3501 PROCESS_FLAG = 3,
3502 LOCK_FLAG = 2,
3503 ERROR_CODE = substrb(l_error_code,1,240)
3504 WHERE TRANSACTION_HEADER_ID = l_header_id
3505 AND PROCESS_FLAG = 1
3506 AND TRANSACTION_BATCH_ID = l_Line_rec_Type.transaction_batch_id; --Bug#5075521
3507 -- group error changes.
3508 l_current_batch_failed := TRUE;--Bug#5075521
3509 l_current_err_batch_id := l_Line_rec_Type.transaction_batch_id;--Bug#5075521
3510
3511 END IF ;
3512
3513 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
3514 IF (l_debug = 1) THEN
3515 inv_log_util.trace('UnExpected Error while querying tree : ' || l_msg_data,'INV_TXN_MANAGER_PUB', 9);
3516 END IF;
3517
3518 FND_MESSAGE.set_name('INV','INV_INTERNAL_ERROR');
3519 FND_MESSAGE.set_token('token1','XACT_QTY1');
3520 l_error_code := FND_MESSAGE.get;
3521 l_error_exp := l_msg_data;
3522 x_msg_data := l_msg_data;
3523 UPDATE MTL_TRANSACTIONS_INTERFACE
3524 SET LAST_UPDATE_DATE = SYSDATE,
3525 LAST_UPDATED_BY = l_userid,
3526 LAST_UPDATE_LOGIN = l_loginid,
3527 PROGRAM_UPDATE_DATE = SYSDATE,
3528 PROCESS_FLAG = 3,
3529 LOCK_FLAG = 2,
3530 ERROR_CODE = substrb(l_error_code,1,240),
3531 ERROR_EXPLANATION = substrb(l_error_exp,1,240)
3532 WHERE ROWID = l_temp_rowid
3533 AND PROCESS_FLAG = 1;
3534 --check for batch error
3535 loaderrmsg('INV_GROUP_ERROR','INV_GROUP_ERROR');
3536
3537 UPDATE MTL_TRANSACTIONS_INTERFACE MTI
3538 SET LAST_UPDATE_DATE = SYSDATE,
3539 LAST_UPDATED_BY = l_userid,
3540 LAST_UPDATE_LOGIN = l_loginid,
3541 PROGRAM_UPDATE_DATE = SYSDATE,
3542 PROCESS_FLAG = 3,
3543 LOCK_FLAG = 2,
3544 ERROR_CODE = substrb(l_error_code,1,240)
3545 WHERE TRANSACTION_HEADER_ID = l_header_id
3546 AND PROCESS_FLAG = 1
3547 AND TRANSACTION_BATCH_ID = l_Line_rec_Type.transaction_batch_id;--Bug#5075521
3548 --group error changes.
3549 l_current_batch_failed := TRUE;--Bug#5075521
3550 l_current_err_batch_id := l_Line_rec_Type.transaction_batch_id;--Bug#5075521
3551 END IF;
3552
3553 IF (l_debug = 1) THEN
3554 inv_log_util.trace('L_QOH : ' || l_qoh,'INV_TXN_MANAGER_PUB', 9);
3555 inv_log_util.trace('L_RQOH : ' || l_rqoh,'INV_TXN_MANAGER_PUB', 9);
3556 inv_log_util.trace('L_PQOH : ' || l_pqoh,'INV_TXN_MANAGER_PUB', 9);
3557 inv_log_util.trace('L_QR : ' || l_qr,'INV_TXN_MANAGER_PUB', 9);
3558 inv_log_util.trace('L_QS : ' || l_qs,'INV_TXN_MANAGER_PUB', 9);
3559 inv_log_util.trace('L_ATT : ' || l_att,'INV_TXN_MANAGER_PUB', 9);
3560 inv_log_util.trace('L_ATR : ' || l_atr,'INV_TXN_MANAGER_PUB', 9);
3561 END IF;
3562 END IF;--100
3563
3564 -- Bug 3427817: For WIP backflush transactions, we should not
3565 -- check for negative availability. If it is
3566 -- a backflush transaction, then get the
3567 -- profile value and do not check for
3568 -- availability if the profile is set to
3569 -- YES.
3570 IF ( NOT l_current_batch_failed ) THEN--150
3571 /*Bug:5392366. Modified the following condition to also check
3572 completion_transaction_id and move_transaction_id to make sure it
3573 is a backflush transaction. If both these values are null then
3574 it is is not a backflush transaction.
3575 */
3576 IF ((l_line_rec_Type.transaction_source_type_id = inv_globals.G_SOURCETYPE_WIP) AND
3577 (l_line_rec_Type.transaction_action_id
3578 IN (inv_globals.G_ACTION_ISSUE, inv_globals.G_ACTION_NEGCOMPRETURN)AND (l_line_rec_type.completion_transaction_id is not null OR l_line_rec_type.move_transaction_id is not null))) THEN
3579 -- It is a backflush transaction. Get the
3580 -- override flag.
3581 l_override_neg_for_backflush :=
3582 fnd_profile.value('INV_OVERRIDE_NEG_FOR_BACKFLUSH');
3583 /*Bug 4764343 Base Bug:4645686. Introducing a new profile 'INV_OVERRIDE_RSV_FOR_BACKFLUSH'
3584 for a specific customer.If set to 'Yes', backflush transaction can drive inventory negative,
3585 even if any reservations exist for the item*/
3586 l_override_rsv_for_backflush := NVL(fnd_profile.value('INV_OVERRIDE_RSV_FOR_BACKFLUSH'), 2);
3587 ELSE
3588 l_override_neg_for_backflush := 0;
3589 l_override_rsv_for_backflush := 2;
3590 END IF;
3591 IF (l_debug = 1) THEN
3592 inv_log_util.trace('l_override_neg_for_backflush ' || l_override_neg_for_backflush,'INV_TXN_MANAGER_PUB', 9);
3593 inv_log_util.trace('l_override_rsv_for_backflush ' || l_override_rsv_for_backflush,'INV_TXN_MANAGER_PUB', 9);
3594 END IF;
3595
3596 --Bug 3487453: Added and set the variable l_translate
3597 -- to true for the token to be translated.
3598 /* Bug 5444209 No check for gme txns adding back to inventory */
3599 IF ((l_Line_rec_Type.wip_entity_type <> 10) OR
3600 (l_Line_rec_Type.wip_entity_type = 10 AND l_line_rec_Type.transaction_type_id NOT IN (43, 44, 1002))) THEN
3601 IF (l_att < l_trx_qty) THEN
3602 IF (l_neg_inv_rcpt = 1 OR l_override_neg_for_backflush = 1) THEN
3603
3604 IF (l_qr >l_trx_qty OR l_qs >0) THEN
3605 /*Bug 4764343 base Bug::4645686. This condition is added for a specific customer by introducing
3606 a new profile 'INV_OVERRIDE_RSV_FOR_BACKFLUSH' . If this profile is not set to 'Yes'
3607 then the backflush transaction can not consume existing reservations.Else it can consume
3608 existing reservation and can drive inventory go negative.
3609 */
3610 IF (l_override_rsv_for_backflush <> 1 ) THEN
3611 inv_log_util.trace('Transaction quantity must be less than or equal to available quantity','INV_TXN_MANAGER_PUB', 9);
3612 FND_MESSAGE.set_name('INV','INV_INT_PROCCODE');
3613 l_error_code := FND_MESSAGE.get;
3614 FND_MESSAGE.set_name('INV','INV_QTY_LESS_OR_EQUAL');
3615 l_error_exp := FND_MESSAGE.get;
3616 x_msg_data := l_error_exp;
3617 UPDATE MTL_TRANSACTIONS_INTERFACE
3618 SET LAST_UPDATE_DATE = SYSDATE,
3619 LAST_UPDATED_BY = l_userid,
3620 LAST_UPDATE_LOGIN = l_loginid,
3621 PROGRAM_UPDATE_DATE = SYSDATE,
3622 PROCESS_FLAG = 3,
3623 LOCK_FLAG = 2,
3624 ERROR_CODE = substrb(l_error_code,1,240),
3625 ERROR_EXPLANATION = substrb(l_error_exp,1,240)
3626 WHERE ROWID = l_temp_rowid
3627 AND PROCESS_FLAG = 1;
3628 --check for batch error
3629 loaderrmsg('INV_GROUP_ERROR','INV_GROUP_ERROR');
3630
3631 UPDATE MTL_TRANSACTIONS_INTERFACE MTI
3632 SET LAST_UPDATE_DATE = SYSDATE,
3633 LAST_UPDATED_BY = l_userid,
3634 LAST_UPDATE_LOGIN = l_loginid,
3635 PROGRAM_UPDATE_DATE = SYSDATE,
3636 PROCESS_FLAG = 3,
3637 LOCK_FLAG = 2,
3638 ERROR_CODE = substrb(l_error_code,1,240)
3639 WHERE TRANSACTION_HEADER_ID = l_header_id
3640 AND PROCESS_FLAG = 1
3641 AND TRANSACTION_BATCH_ID = l_Line_rec_Type.transaction_batch_id;
3642 -- group error changes.
3643 l_current_batch_failed := TRUE;--Bug#5075521
3644 l_current_err_batch_id := l_Line_rec_Type.transaction_batch_id;--Bug#5075521
3645 END IF; --override_rsv_for_backflush
3646 END IF;
3647
3648 IF (NOT l_current_batch_failed ) THEN --200
3649 INV_QUANTITY_TREE_PVT.query_tree
3650 ( p_api_version_number => 1.0
3651 , p_init_msg_lst => fnd_api.g_false
3652 , x_return_status => l_return_status
3653 , x_msg_count => l_msg_count
3654 , x_msg_data => l_msg_data
3655 , p_tree_id => l_tree_id
3656 , p_revision => NULL
3657 , p_lot_number => NULL
3658 , p_subinventory_code => NULL
3659 , p_locator_id => NULL
3660 , x_qoh => l_item_qoh
3661 , x_rqoh => l_item_rqoh
3662 , x_pqoh => l_item_pqoh
3663 , x_qr => l_item_qr
3664 , x_qs => l_item_qs
3665 , x_att => l_item_att
3666 , x_atr => l_item_atr
3667 );
3668
3669 IF l_return_status = fnd_api.g_ret_sts_error THEN
3670 IF (l_debug = 1) THEN
3671 inv_log_util.trace('Expected Error while querying tree : ' || l_msg_data,'INV_TXN_MANAGER_PUB', 9);
3672 END IF;
3673
3674 FND_MESSAGE.set_name('INV','INV_INTERNAL_ERROR');
3675 FND_MESSAGE.set_token('token1','XACT_QTY1');
3676 l_error_code := FND_MESSAGE.get;
3677 l_error_exp := l_msg_data;
3678 x_msg_data := l_msg_data;
3679 UPDATE MTL_TRANSACTIONS_INTERFACE
3680 SET LAST_UPDATE_DATE = SYSDATE,
3681 LAST_UPDATED_BY = l_userid,
3682 LAST_UPDATE_LOGIN = l_loginid,
3683 PROGRAM_UPDATE_DATE = SYSDATE,
3684 PROCESS_FLAG = 3,
3685 LOCK_FLAG = 2,
3686 ERROR_CODE = substrb(l_error_code,1,240),
3687 ERROR_EXPLANATION = substrb(l_error_exp,1,240)
3688 WHERE TRANSACTION_interface_id = l_temp_rowid
3689 AND PROCESS_FLAG = 1;
3690 --check for batch error
3691 loaderrmsg('INV_GROUP_ERROR','INV_GROUP_ERROR');
3692
3693 UPDATE MTL_TRANSACTIONS_INTERFACE MTI
3694 SET LAST_UPDATE_DATE = SYSDATE,
3695 LAST_UPDATED_BY = l_userid,
3696 LAST_UPDATE_LOGIN = l_loginid,
3697 PROGRAM_UPDATE_DATE = SYSDATE,
3698 PROCESS_FLAG = 3,
3699 LOCK_FLAG = 2,
3700 ERROR_CODE = substrb(l_error_code,1,240)
3701 WHERE TRANSACTION_HEADER_ID = l_header_id
3702 AND PROCESS_FLAG = 1
3703 AND TRANSACTION_BATCH_ID = l_Line_rec_Type.transaction_batch_id;
3704 -- group error changes.
3705 l_current_batch_failed := TRUE;--Bug#5075521
3706 l_current_err_batch_id := l_Line_rec_Type.transaction_batch_id;--Bug#5075521
3707 END IF ;
3708
3709 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
3710 IF (l_debug = 1) THEN
3711 inv_log_util.trace('UnExpected Error while querying tree : ' || l_msg_data,'INV_TXN_MANAGER_PUB', 9);
3712 END IF;
3713
3714 FND_MESSAGE.set_name('INV','INV_INTERNAL_ERROR');
3715 FND_MESSAGE.set_token('token1','XACT_QTY1');
3716 l_error_code := FND_MESSAGE.get;
3717 l_error_exp := l_msg_data;
3718 x_msg_data := l_msg_data;
3719 UPDATE MTL_TRANSACTIONS_INTERFACE
3720 SET LAST_UPDATE_DATE = SYSDATE,
3721 LAST_UPDATED_BY = l_userid,
3722 LAST_UPDATE_LOGIN = l_loginid,
3723 PROGRAM_UPDATE_DATE = SYSDATE,
3724 PROCESS_FLAG = 3,
3725 LOCK_FLAG = 2,
3726 ERROR_CODE = substrb(l_error_code,1,240),
3727 ERROR_EXPLANATION = substrb(l_error_exp,1,240)
3728 WHERE ROWID = l_temp_rowid
3729 AND PROCESS_FLAG = 1;
3730 --check for batch error
3731 loaderrmsg('INV_GROUP_ERROR','INV_GROUP_ERROR');
3732
3733 UPDATE MTL_TRANSACTIONS_INTERFACE MTI
3734 SET LAST_UPDATE_DATE = SYSDATE,
3735 LAST_UPDATED_BY = l_userid,
3736 LAST_UPDATE_LOGIN = l_loginid,
3737 PROGRAM_UPDATE_DATE = SYSDATE,
3738 PROCESS_FLAG = 3,
3739 LOCK_FLAG = 2,
3740 ERROR_CODE = substrb(l_error_code,1,240)
3741 WHERE TRANSACTION_HEADER_ID = l_header_id
3742 AND PROCESS_FLAG = 1
3743 AND TRANSACTION_BATCH_ID = l_Line_rec_Type.transaction_batch_id;
3744 --group error changes.
3745 l_current_batch_failed := TRUE;--Bug#5075521
3746 l_current_err_batch_id := l_Line_rec_Type.transaction_batch_id;--Bug#5075521
3747 END IF;
3748 END IF; --200
3749 inv_log_util.trace('L_ITEM_QOH : ' || l_item_qoh,'INV_TXN_MANAGER_PUB', 9);
3750 inv_log_util.trace('L_ITEM_RQOH : ' || l_item_rqoh,'INV_TXN_MANAGER_PUB', 9);
3751 inv_log_util.trace('L_ITEM_PQOH : ' || l_item_pqoh,'INV_TXN_MANAGER_PUB', 9);
3752 inv_log_util.trace('L_ITEM_QR : ' || l_item_qr,'INV_TXN_MANAGER_PUB', 9);
3753 inv_log_util.trace('L_ITEM_QS : ' || l_item_qs,'INV_TXN_MANAGER_PUB', 9);
3754 inv_log_util.trace('L_ITEM_ATT : ' || l_item_att,'INV_TXN_MANAGER_PUB', 9);
3755 inv_log_util.trace('L_ITEM_ATR : ' || l_item_atr,'INV_TXN_MANAGER_PUB', 9);
3756 inv_log_util.trace('L_TRX_QTY : ' || l_trx_qty,'INV_TXN_MANAGER_PUB', 9);
3757
3758 IF ( NOT l_current_batch_failed) THEN --250
3759 IF (l_item_qoh <> l_item_att) THEN -- Higher Level Reservations
3760 IF (l_item_att < l_trx_qty AND l_item_qr > 0) THEN
3761 /*Bug 4764343 Base Bug::4645686. This condition is added for a specific
3762 customer by introducing a new profile 'INV_OVERRIDE_RSV_FOR_BACKFLUSH' .
3763 If this profile is not set to 'Yes'then the backflush transaction can not
3764 consume existing reservations.Else it can consume existing reservation and can
3765 drive inventory negative. */
3766 IF (l_override_rsv_for_backflush <> 1 ) THEN
3767 inv_log_util.trace('Total Org quantity cannot become negative when there are reservations present','INV_TXN_MANAGER_PUB', 9);
3768 FND_MESSAGE.set_name('INV','INV_INT_PROCCODE');
3769 l_error_code := FND_MESSAGE.get;
3770 FND_MESSAGE.set_name('INV','INV_ORG_QUANTITY');
3771 l_error_exp := FND_MESSAGE.get;
3772 FND_MESSAGE.set_name('INV','INV_INTERNAL_ERROR');
3773 x_msg_data := l_error_exp;
3774 UPDATE MTL_TRANSACTIONS_INTERFACE
3775 SET LAST_UPDATE_DATE = SYSDATE,
3776 LAST_UPDATED_BY = l_userid,
3777 LAST_UPDATE_LOGIN = l_loginid,
3778 PROGRAM_UPDATE_DATE = SYSDATE,
3779 PROCESS_FLAG = 3,
3780 LOCK_FLAG = 2,
3781 ERROR_CODE = substrb(l_error_code,1,240),
3782 ERROR_EXPLANATION = substrb(l_error_exp,1,240)
3783 WHERE ROWID = l_temp_rowid
3784 AND PROCESS_FLAG = 1;
3785 --check for batch error
3786 loaderrmsg('INV_GROUP_ERROR','INV_GROUP_ERROR');
3787
3788 UPDATE MTL_TRANSACTIONS_INTERFACE MTI
3789 SET LAST_UPDATE_DATE = SYSDATE,
3790 LAST_UPDATED_BY = l_userid,
3791 LAST_UPDATE_LOGIN = l_loginid,
3792 PROGRAM_UPDATE_DATE = SYSDATE,
3793 PROCESS_FLAG = 3,
3794 LOCK_FLAG = 2,
3795 ERROR_CODE = substrb(l_error_code,1,240)
3796 WHERE TRANSACTION_HEADER_ID = l_header_id
3797 AND PROCESS_FLAG = 1
3798 AND TRANSACTION_BATCH_ID = l_Line_rec_Type.transaction_batch_id;
3799 --group error changes.
3800 l_current_batch_failed := TRUE;--Bug#5075521
3801 l_current_err_batch_id := l_Line_rec_Type.transaction_batch_id;--Bug#5075521
3802
3803 END IF;-- override_rsv_for_backflush
3804 END IF;--total org quantity
3805 END IF;--high level
3806 END IF; --250
3807
3808 ELSE --(neg_inv_rcpt = 1)
3809 IF (l_debug = 1) THEN
3810 inv_log_util.trace('Not Enough Qty: l_att,l_trx_qty:' || l_att||','||l_trx_qty,'INV_TXN_MANAGER_PUB', 9);
3811 END IF;
3812 FND_MESSAGE.set_name('INV','INV_NO_NEG_BALANCES');
3813 l_error_code := FND_MESSAGE.get;
3814 FND_MESSAGE.set_name('INV','INV_LESS_OR_EQUAL');
3815 FND_MESSAGE.set_token('ENTITY1','INV_QUANTITY',l_translate);
3816 FND_MESSAGE.set_token('ENTITY2','AVAIL_TO_TRANSACT',l_translate);
3817 l_error_exp := FND_MESSAGE.get;
3818 x_msg_data := l_error_exp;
3819 UPDATE MTL_TRANSACTIONS_INTERFACE
3820 SET LAST_UPDATE_DATE = SYSDATE,
3821 LAST_UPDATED_BY = l_userid,
3822 LAST_UPDATE_LOGIN = l_loginid,
3823 PROGRAM_UPDATE_DATE = SYSDATE,
3824 PROCESS_FLAG = 3,
3825 LOCK_FLAG = 2,
3826 ERROR_CODE = substrb(l_error_code,1,240),
3827 ERROR_EXPLANATION = substrb(l_error_exp,1,240)
3828 WHERE ROWID = l_temp_rowid
3829 AND PROCESS_FLAG = 1;
3830 --check for batch error
3831 loaderrmsg('INV_GROUP_ERROR','INV_GROUP_ERROR');
3832
3833 UPDATE MTL_TRANSACTIONS_INTERFACE MTI
3834 SET LAST_UPDATE_DATE = SYSDATE,
3835 LAST_UPDATED_BY = l_userid,
3836 LAST_UPDATE_LOGIN = l_loginid,
3837 PROGRAM_UPDATE_DATE = SYSDATE,
3838 PROCESS_FLAG = 3,
3839 LOCK_FLAG = 2,
3840 ERROR_CODE = substrb(l_error_code,1,240)
3841 WHERE TRANSACTION_HEADER_ID = l_header_id
3842 AND PROCESS_FLAG = 1
3843 AND TRANSACTION_BATCH_ID = l_Line_rec_Type.transaction_batch_id;
3844 -- group error changes.
3845 l_current_batch_failed := TRUE;--Bug#5075521
3846 l_current_err_batch_id := l_Line_rec_Type.transaction_batch_id;--Bug#5075521
3847 END IF;
3848 END IF;--check for att and trx qty
3849 END IF;--IF ((l_Line_rec_Type.wip_entity_type <> 10) OR
3850 -- update the qty
3851 -- Pawan 11th july Added - GME does not have transfer subinventory
3852 IF ( NOT l_current_batch_failed ) THEN --300
3853 IF (l_actid in (2,28)) then
3854 inv_quantity_tree_pub.update_quantities
3855 (p_api_version_number => 1.0,
3856 p_init_msg_lst => fnd_api.g_false,
3857 x_return_status => l_return_status,
3858 x_msg_count => l_msg_count,
3859 x_msg_data => l_msg_data,
3860 p_organization_id => l_org_id,
3861 p_inventory_item_id => l_item_id,
3862 p_tree_mode => 2,
3863 p_is_revision_control => l_revision_control,
3864 p_is_lot_control => l_lot_control,
3865 p_is_serial_control => FALSE,
3866 p_demand_source_type_id => nvl(l_srctypeid,-9999),
3867 p_demand_source_header_id => nvl(l_srcid,-9999),
3868 p_demand_source_line_id => nvl(l_srclineid,-9999),
3869 p_revision => l_rev,
3870 p_lot_number => l_lotnum,
3871 p_subinventory_code => l_xfrsub,
3872 p_locator_id => l_xlocid,
3873 p_primary_quantity => l_trx_qty,
3874 p_quantity_type => inv_quantity_tree_pvt.g_qoh,
3875 p_onhand_source => inv_quantity_tree_pvt.g_all_subs,
3876 x_qoh => l_qoh,
3877 x_rqoh => l_rqoh,
3878 x_qr => l_qr,
3879 x_qs => l_qs,
3880 x_att => l_att,
3881 x_atr => l_atr);
3882
3883
3884 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3885 FND_MESSAGE.set_name('INV', 'INV_ERR_CREATETREE');
3886 FND_MESSAGE.set_token('ROUTINE','INV_QUANTITY_TREE_PUB.UPDATE_QUANTITIES');
3887 l_error_code:= fnd_message.get;
3888 l_error_exp :=l_msg_data;
3889 x_msg_data := l_msg_data;
3890 UPDATE MTL_TRANSACTIONS_INTERFACE
3891 SET LAST_UPDATE_DATE = SYSDATE,
3892 LAST_UPDATED_BY = l_userid,
3893 LAST_UPDATE_LOGIN = l_loginid,
3894 PROGRAM_UPDATE_DATE = SYSDATE,
3895 PROCESS_FLAG = 3,
3896 LOCK_FLAG = 2,
3897 ERROR_CODE = substrb(l_error_code,1,240),
3898 ERROR_EXPLANATION = substrb(l_error_exp,1,240)
3899 WHERE ROWID = l_temp_rowid
3900 AND PROCESS_FLAG = 1;
3901 --check for batch error
3902 loaderrmsg('INV_GROUP_ERROR','INV_GROUP_ERROR');
3903
3904 UPDATE MTL_TRANSACTIONS_INTERFACE MTI
3905 SET LAST_UPDATE_DATE = SYSDATE,
3906 LAST_UPDATED_BY = l_userid,
3907 LAST_UPDATE_LOGIN = l_loginid,
3908 PROGRAM_UPDATE_DATE = SYSDATE,
3909 PROCESS_FLAG = 3,
3910 LOCK_FLAG = 2,
3911 ERROR_CODE = substrb(l_error_code,1,240)
3912 WHERE TRANSACTION_HEADER_ID = l_header_id
3913 AND PROCESS_FLAG = 1
3914 AND TRANSACTION_BATCH_ID = l_Line_rec_Type.transaction_batch_id;
3915 -- group error changes.
3916 l_current_batch_failed := TRUE;--Bug#5075521
3917 l_current_err_batch_id := l_Line_rec_Type.transaction_batch_id;--Bug#5075521
3918 END IF;
3919 ELSE
3920 /* Jalaj Srivastava Bug 5232394
3921 update tree with correct sign.
3922 sign is derived from transaction_quantity */
3923
3924 inv_quantity_tree_pub.update_quantities
3925 (p_api_version_number => 1.0,
3926 p_init_msg_lst => fnd_api.g_false,
3927 x_return_status => l_return_status,
3928 x_msg_count => l_msg_count,
3929 x_msg_data => l_msg_data,
3930 p_organization_id => l_org_id,
3931 p_inventory_item_id => l_item_id,
3932 p_tree_mode => 2,
3933 p_is_revision_control => l_revision_control,
3934 p_is_lot_control => l_lot_control,
3935 p_is_serial_control => FALSE,
3936 p_demand_source_type_id => nvl(l_srctypeid,-9999),
3937 p_demand_source_header_id => nvl(l_srcid,-9999),
3938 p_demand_source_line_id => nvl(l_srclineid,-9999),
3939 p_revision => l_rev,
3940 p_lot_number => l_lotnum,
3941 p_subinventory_code => l_sub_code,
3942 p_locator_id => l_locid,
3943 p_primary_quantity => (sign(l_line_rec_type.transaction_quantity)*(l_trx_qty)),
3944 p_quantity_type => inv_quantity_tree_pvt.g_qoh,
3945 p_onhand_source => inv_quantity_tree_pvt.g_all_subs,
3946 x_qoh => l_qoh,
3947 x_rqoh => l_rqoh,
3948 x_qr => l_qr,
3949 x_qs => l_qs,
3950 x_att => l_att,
3951 x_atr => l_atr);
3952
3953 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3954 FND_MESSAGE.set_name('INV', 'INV_ERR_CREATETREE');
3955 FND_MESSAGE.set_token('ROUTINE','INV_QUANTITY_TREE_PUB.UPDATE_QUANTITIES');
3956 l_error_code:= fnd_message.get;
3957 l_error_exp :=l_msg_data;
3958 x_msg_data := l_msg_data;
3959 UPDATE MTL_TRANSACTIONS_INTERFACE
3960 SET LAST_UPDATE_DATE = SYSDATE,
3961 LAST_UPDATED_BY = l_userid,
3962 LAST_UPDATE_LOGIN = l_loginid,
3963 PROGRAM_UPDATE_DATE = SYSDATE,
3964 PROCESS_FLAG = 3,
3965 LOCK_FLAG = 2,
3966 ERROR_CODE = substrb(l_error_code,1,240),
3967 ERROR_EXPLANATION = substrb(l_error_exp,1,240)
3968 WHERE ROWID = l_temp_rowid
3969 AND PROCESS_FLAG = 1;
3970 --check for batch error
3971 loaderrmsg('INV_GROUP_ERROR','INV_GROUP_ERROR');
3972
3973 UPDATE MTL_TRANSACTIONS_INTERFACE MTI
3974 SET LAST_UPDATE_DATE = SYSDATE,
3975 LAST_UPDATED_BY = l_userid,
3976 LAST_UPDATE_LOGIN = l_loginid,
3977 PROGRAM_UPDATE_DATE = SYSDATE,
3978 PROCESS_FLAG = 3,
3979 LOCK_FLAG = 2,
3980 ERROR_CODE = substrb(l_error_code,1,240)
3981 WHERE TRANSACTION_HEADER_ID = l_header_id
3982 AND PROCESS_FLAG = 1
3983 AND TRANSACTION_BATCH_ID = l_Line_rec_Type.transaction_batch_id;
3984 -- group error changes.
3985 l_current_batch_failed := TRUE;--Bug#5075521
3986 l_current_err_batch_id := l_Line_rec_Type.transaction_batch_id;--Bug#5075521
3987 ELSE
3988 --qty tree update was successful
3989 IF (l_debug = 1) THEN
3990 inv_log_util.trace('after update of quantity tree qoh='||l_qoh||' l_att='||l_att||' l_atr='||l_atr,'INV_TXN_MANAGER_PUB', 9);
3991 END IF;
3992 END IF;
3993 END IF;-- Pawan Added for IF (l_actid in (2,28))
3994 END IF; --300
3995 END IF; --150
3996 END IF; --If l_rsv_wip_entity_type <> inv_reservation_global.g_wip_source_type_cmro
3997 END LOOP;-- Loop Z1
3998 END IF;--l_current_err_batch_id is NULL..
3999 END IF; --l_srctyped =5
4000
4001 /*Bug:5209598. End of code*/
4002
4003
4004
4005 --Start of new code added as per the eIB TDD; Bug# 4348541
4006 DECLARE
4007 l_location_required_flag mtl_transaction_types.location_required_flag%TYPE;
4008 BEGIN
4009 l_location_required_flag := 'N';
4010 -- Call the inv_validate.check_location_required_setup procedure
4011 -- and pass l_line_rec_type.transaction_type_id as parameter to
4012 -- check if the Location has to be made mandatory. If this procedure
4013 -- returns 'Y' then check if the Location Code is specified or not.
4014 -- If it is not specified then error out the interface record.
4015 inv_validate.check_location_required_setup(
4016 p_transaction_type_id => l_line_rec_type.transaction_type_id,
4017 p_required_flag => l_location_required_flag);
4018
4019 IF l_location_required_flag = 'Y' AND
4020 l_line_rec_type.ship_to_location_id IS NULL THEN
4021
4022 FND_MESSAGE.SET_NAME('INV','INV_LOCATION_MANDATORY');
4023 l_error_code := FND_MESSAGE.GET;
4024
4025 UPDATE MTL_TRANSACTIONS_INTERFACE
4026 SET LAST_UPDATE_DATE = SYSDATE,
4027 LAST_UPDATED_BY = l_userid,
4028 LAST_UPDATE_LOGIN = l_loginid,
4029 PROGRAM_UPDATE_DATE = SYSDATE,
4030 PROCESS_FLAG = 3,
4031 LOCK_FLAG = 2,
4032 ERROR_CODE = SUBSTR (l_error_code, 1, 240),
4033 ERROR_EXPLANATION = SUBSTR (l_error_code, 1, 240)
4034 WHERE ROWID = l_line_rec_type.rowid
4035 AND PROCESS_FLAG = 1;
4036
4037 RAISE rollback_line_validation;
4038 END IF;
4039 END;
4040 --End of new code added as per the eIB TDD; Bug# 4348541
4041
4042 SAVEPOINT line_validation_svpt;
4043 fnd_message.set_name ('INV', 'INV_MOVE_TO_TEMP');
4044 fnd_message.set_token ('token', l_header_id);
4045 l_disp := fnd_message.get;
4046
4047 IF (l_debug = 1)
4048 THEN
4049 inv_log_util.TRACE (l_disp, 'INV_TXN_MANAGER_PUB', 9);
4050 END IF;
4051
4052 /* Insert into MMTT */
4053 /** Change for lOt Transactions **/
4054
4055 IF l_line_rec_type.transaction_source_type_id = 13
4056 AND l_line_rec_type.transaction_action_id IN (40, 41, 42)
4057 THEN
4058 IF (l_line_rec_type.transaction_action_id = 40)
4059 THEN
4060 IF (l_debug = 1)
4061 THEN
4062 inv_log_util.TRACE ('Checking for lot partial split'
4063 , 'INV_TXN_MANAGER_PUB'
4064 , 9
4065 );
4066 END IF;
4067 IF (NOT check_partial_split (l_line_rec_type.parent_id
4068 , l_index
4069 )
4070 )
4071 THEN
4072 l_error_exp := fnd_message.get;
4073
4074 IF (l_debug = 1)
4075 THEN
4076 inv_log_util.TRACE ( 'Error in Check_Partial_Split= '
4077 || l_error_exp
4078 , 'INV_TXN_MANAGER_PUB'
4079 , 9
4080 );
4081 END IF;
4082
4083 fnd_message.set_name ('INV', 'INV_INT_TMPXFRCODE');
4084 l_error_code := fnd_message.get;
4085 ROLLBACK TO line_validation_svpt;
4086
4087 UPDATE mtl_transactions_interface
4088 SET last_update_date = SYSDATE
4089 , last_updated_by = l_userid
4090 , last_update_login = l_loginid
4091 , program_update_date = SYSDATE
4092 , process_flag = 3
4093 , lock_flag = 2
4094 , ERROR_CODE = SUBSTR (l_error_code, 1, 240)
4095 , error_explanation = SUBSTR (l_error_exp, 1, 240)
4096 WHERE ROWID = l_line_rec_type.ROWID AND process_flag = 1;
4097
4098 RAISE rollback_line_validation;
4099 END IF;
4100 END IF;
4101 END IF; --J-dev
4102
4103
4104 /** end of changes for lot transactions **/
4105 --J dev, done as a bulk insert now. outside the
4106 --level loop.
4107 END IF;
4108 EXCEPTION
4109 WHEN rollback_line_validation
4110 THEN
4111 IF (l_debug = 1)
4112 THEN
4113 inv_log_util.TRACE
4114 ( 'Failed Interface ID : '
4115 || l_line_rec_type.transaction_interface_id
4116 || ' Item: '
4117 || l_line_rec_type.inventory_item_id
4118 || 'Org : '
4119 || l_line_rec_type.organization_id
4120 , 'INV_TXN_MANAGER_PUB'
4121 , 9
4122 );
4123 END IF;
4124
4125 batch_error := TRUE;
4126 WHEN OTHERS
4127 THEN
4128 batch_error := TRUE;
4129
4130 IF (l_debug = 1)
4131 THEN
4132 inv_log_util.TRACE
4133 ( 'Error in INV_TXN_MANAGER_PUB LOOP - rollback last transaction Interface ID '
4134 || l_line_rec_type.transaction_interface_id
4135 , 'INV_TXN_MANAGER_PUB'
4136 , 9
4137 );
4138 END IF;
4139
4140 ROLLBACK TO line_validation_svpt;
4141 END;
4142 END LOOP; -- endloop for AA1 (MTI)
4143
4144 /*Bug:5209598. Freeing the Tree created for reservation checks.*/
4145 IF (l_tree_id IS NOT NULL) THEN
4146 INV_QUANTITY_TREE_PVT.free_tree
4147 ( p_api_version_number => 1.0
4148 , p_init_msg_lst => fnd_api.g_false
4149 , x_return_status => l_return_status
4150 , x_msg_count => l_msg_count
4151 , x_msg_data => l_msg_data
4152 , p_tree_id => l_tree_id );
4153 END IF;
4154
4155 --J-dev check that all records for line validation are failed here.
4156
4157 --check for batch error at line validation
4158 loaderrmsg ('INV_GROUP_ERROR', 'INV_GROUP_ERROR');
4159
4160 UPDATE mtl_transactions_interface mti
4161 SET last_update_date = SYSDATE
4162 , last_updated_by = l_userid
4163 , last_update_login = l_loginid
4164 , program_update_date = SYSDATE
4165 , process_flag = 3
4166 , lock_flag = 2
4167 , ERROR_CODE = SUBSTRB (l_error_code, 1, 240)
4168 WHERE transaction_header_id = l_header_id
4169 AND process_flag = 1
4170 AND transaction_batch_id IN (
4171 SELECT DISTINCT mti2.transaction_batch_id
4172 FROM mtl_transactions_interface mti2
4173 WHERE mti2.transaction_header_id = l_header_id
4174 AND mti2.process_flag = 3
4175 AND mti2.ERROR_CODE IS NOT NULL);
4176
4177 -- group error changes.
4178 IF fnd_api.to_boolean (p_commit)
4179 THEN
4180 COMMIT WORK; /* Commit after LineValidation all MTI records */
4181 END IF;
4182
4183 --check if all records have been failed by the Line Validation
4184 BEGIN
4185 SELECT transaction_source_type_id
4186 INTO l_source_type_id
4187 FROM mtl_transactions_interface
4188 WHERE transaction_header_id = l_header_id
4189 AND process_flag = 1
4190 AND ROWNUM < 2;
4191 EXCEPTION
4192 WHEN NO_DATA_FOUND
4193 THEN
4194 x_return_status := fnd_api.g_ret_sts_error;
4195 x_msg_data := 'All records failed after line validation';
4196
4197 IF (l_debug = 1)
4198 THEN
4199 inv_log_util.TRACE ('All records failed after line validation'
4200 , 'INV_TXN_MANAGER_PUB'
4201 , 1
4202 );
4203 END IF;
4204
4205 RETURN -1;
4206 END;
4207
4208 --J-dev
4209 /******* Group Validation for WIP records *******************/
4210 /* This WIP API could potentially error some records in MTI. If any records
4211 /* have been errored, they would be stamped with error-code/explanation */
4212 IF (l_srctypeid = 5) and (l_wip_entity_type <> 10 )-- Pawan added for l_wip_entity_type
4213 THEN
4214 wip_mti_pub.postinvwipvalidation (p_txnheaderid => l_header_id
4215 , x_returnstatus => x_return_status
4216 );
4217
4218 IF (x_return_status = fnd_api.g_ret_sts_success)
4219 THEN
4220 IF (l_debug = 1)
4221 THEN
4222 inv_log_util.TRACE ('Success from:!!postInvWIPValid'
4223 , 'INV_TXN_MANAGER_PUB'
4224 , 1
4225 );
4226 END IF;
4227
4228 --J-dev check that all records for line validation are failed here.
4229 --bug 3727791
4230 --check for batch error at line validation
4231 loaderrmsg ('INV_GROUP_ERROR', 'INV_GROUP_ERROR');
4232
4233 UPDATE mtl_transactions_interface mti
4234 SET last_update_date = SYSDATE
4235 , last_updated_by = l_userid
4236 , last_update_login = l_loginid
4237 , program_update_date = SYSDATE
4238 , process_flag = 3
4239 , lock_flag = 2
4240 , ERROR_CODE = SUBSTRB (l_error_code, 1, 240)
4241 WHERE transaction_header_id = l_header_id
4242 AND process_flag = 1
4243 AND transaction_batch_id IN (
4244 SELECT DISTINCT mti2.transaction_batch_id
4245 FROM mtl_transactions_interface mti2
4246 WHERE mti2.transaction_header_id = l_header_id
4247 AND mti2.process_flag = 3
4248 AND mti2.ERROR_CODE IS NOT NULL);
4249
4250 --group error changes.
4251 IF fnd_api.to_boolean (p_commit)
4252 THEN
4253 COMMIT WORK; /* Commit after PostInvWip all MTI records */
4254 END IF;
4255
4256 --check if all records have been failed by the wip API.
4257 BEGIN
4258 SELECT transaction_source_type_id
4259 INTO l_source_type_id
4260 FROM mtl_transactions_interface
4261 WHERE transaction_header_id = l_header_id
4262 AND process_flag = 1
4263 AND ROWNUM < 2;
4264 EXCEPTION
4265 WHEN NO_DATA_FOUND
4266 THEN
4267 x_return_status := fnd_api.g_ret_sts_error;
4268
4269 /* Bug 3656824
4270 Replaced the hard coded message with the last message in the error stack from WIP validation*/
4271 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
4272 RETURN -1;
4273 END;
4274 ELSE
4275 IF (l_debug = 1)
4276 THEN
4277 inv_log_util.TRACE ('Failure from:!!postInvWIPProcessing'
4278 , 'INV_TXN_MANAGER_PUB'
4279 , 1
4280 );
4281 END IF;
4282
4283 RAISE fnd_api.g_exc_unexpected_error;
4284 END IF; --check for success
4285 END IF; --l_srctypeid = 5
4286
4287 -- ADD tmp Insert here. In case of an error raise an exception.
4288 --J-dev
4289
4290 /*Change for supporting the lot transactions for a lot serial item
4291 *tmpinsert will insert the data into the temp tables for transactions
4292 *other than split/merge/translate. Only if it is a success we move on to
4293 *tmpinsert2 whihc handles the three transactions
4294 */
4295 IF (l_debug = 1)
4296 THEN
4297 inv_log_util.TRACE ('Calling tmpinsert'
4298 , 'INV_TXN_MANAGER_PUB'
4299 , 9
4300 );
4301 END IF;
4302
4303 IF (NOT tmpinsert (l_header_id))
4304 THEN
4305 l_error_exp := fnd_message.get;
4306
4307 IF (l_debug = 1)
4308 THEN
4309 inv_log_util.TRACE ('Error in tmpinsert=' || l_error_exp
4310 , 'INV_TXN_MANAGER_PUB'
4311 , 9
4312 );
4313 END IF;
4314
4315 fnd_message.set_name ('INV', 'INV_INT_TMPXFRCODE');
4316 l_error_code := fnd_message.get;
4317 RAISE fnd_api.g_exc_unexpected_error;
4318 ELSE
4319 BEGIN
4320 IF(l_debug = 1) THEN
4321 inv_log_util.TRACE ( 'Calling tmpinsert2'
4322 , 'INV_TXN_MANAGER_PUB'
4323 , 9
4324 );
4325 END IF;
4326 inv_txn_manager_grp.tmpinsert2(
4327 x_return_status => x_return_status
4328 , x_msg_count => x_msg_count
4329 , x_msg_data => x_msg_data
4330 , x_validation_status => l_validation_status
4331 , p_header_id => l_header_id);
4332 IF(l_debug = 1) THEN
4333 inv_log_util.TRACE ( 'After tmpinsert2'
4334 , 'INV_TXN_MANAGER_PUB'
4335 , 9
4336 );
4337 END IF;
4338 EXCEPTION
4339 WHEN OTHERS THEN
4340 inv_log_util.TRACE ( 'tmpinsert2 raised exception '
4341 , 'INV_TXN_MANAGER_PUB'
4342 , 9
4343 );
4344 fnd_message.set_name ('INV', 'INV_INT_TMPXFRCODE');
4345 l_error_code := fnd_message.get;
4346 RAISE fnd_api.g_exc_unexpected_error;
4347 END;
4348
4349 IF(x_return_status <> fnd_api.g_ret_sts_success OR
4350 l_validation_status <> 'Y') THEN
4351 inv_log_util.TRACE ( 'tmpinsert2 failed..returned with error '
4352 , 'INV_TXN_MANAGER_PUB'
4353 , 9
4354 );
4355 RAISE fnd_api.g_exc_error;
4356 END IF;
4357 END IF;
4358
4359 /*Bug:5276191.Start of code changes. */
4360
4361 FOR p_mmtt IN c_mmtt LOOP
4362
4363 IF (p_mmtt.transaction_action_id in (40,42) )then
4364
4365 BEGIN
4366 SELECT serial_number_control_code
4367 INTO l_serial_control
4368 FROM MTL_SYSTEM_ITEMS
4369 WHERE inventory_item_id= p_mmtt.inventory_item_id
4370 AND organization_id = p_mmtt.organization_id;
4371 EXCEPTION
4372 WHEN OTHERS THEN
4373 inv_log_util.TRACE ('Exception in getting serial control code'||Sqlerrm,'INV_TXN_MANAGER_PUB ', 9);
4374 RAISE fnd_api.g_exc_unexpected_error;
4375 END;
4376
4377 IF (l_serial_control IN (2,5)) THEN
4378
4379 IF(p_mmtt.locator_id IS NOT NULL) then
4380 BEGIN
4381 SELECT project_id INTO l_from_project_id
4382 FROM mtl_item_locations
4383 WHERE inventory_location_id = p_mmtt.locator_id
4384 AND organization_id = p_mmtt.organization_id;
4385 EXCEPTION
4386 WHEN OTHERS THEN
4387 IF (l_debug = 1) THEN
4388 inv_log_util.TRACE ('exception in getting from project: ' || Sqlerrm, 'INV_TXN_MANAGER_PUB', 9);
4389 END IF;
4390 RAISE fnd_api.g_exc_unexpected_error;
4391 END;
4392 END IF;
4393
4394 inv_cost_group_update.cost_group_update
4395 (p_transaction_rec => p_mmtt,
4396 p_fob_point => null,
4397 p_transfer_wms_org => FALSE,
4398 p_tfr_primary_cost_method => null,
4399 p_tfr_org_cost_group_id => null,
4400 p_from_project_id => l_from_project_id,
4401 p_to_project_id => null,
4402 x_return_status => x_return_status,
4403 x_msg_count => x_msg_count,
4404 x_msg_data => x_msg_data);
4405
4406 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) then
4407 l_error_exp := x_msg_data;
4408 RAISE fnd_api.g_exc_error;
4409 END IF;
4410
4411 END IF; --l_serial_control_code <>1
4412 END IF;--action_id in (40,42)
4413
4414 END LOOP; --c_mmtt loop
4415 /*Bug:5276191.End of code changes. */
4416
4417
4418
4419 --- End J dev
4420 IF fnd_api.to_boolean (p_commit)
4421 THEN
4422 COMMIT WORK; /* Commit after validating all MTI records */
4423 END IF;
4424
4425 /* Delete the errored out flow schedules */
4426 IF (inv_txn_manager_grp.gi_flow_schedule <> 0)
4427 THEN
4428 wip_flow_utilities.delete_flow_schedules (l_header_id);
4429 END IF;
4430
4431 SELECT COUNT (*)
4432 INTO l_midtotrows
4433 FROM mtl_material_transactions_temp
4434 WHERE transaction_header_id = l_header_id AND process_flag = 'Y';
4435
4436 DELETE FROM mtl_material_transactions_temp
4437 WHERE transaction_header_id = l_header_id
4438 AND shippable_flag = 'N'
4439 AND process_flag = 'Y';
4440
4441 IF (l_debug = 1)
4442 THEN
4443 inv_log_util.TRACE ( 'Goint for rows in MMTT. rcnt = '
4444 || l_midtotrows
4445 || ',hdrid='
4446 || l_header_id
4447 , 'INV_TXN_MANAGER_PUB'
4448 , 9
4449 );
4450 END IF;
4451
4452 done := FALSE;
4453 FIRST := TRUE;
4454
4455 WHILE (NOT done)
4456 LOOP
4457 SAVEPOINT process_trx_save;
4458
4459 IF (FIRST)
4460 THEN
4461 fnd_message.set_name ('INV', 'INV_CALL_PROC');
4462 fnd_message.set_token ('token1', l_header_id);
4463 fnd_message.set_token ('token2', l_totrows);
4464 l_disp := fnd_message.get;
4465
4466 IF (l_debug = 1)
4467 THEN
4468 inv_log_util.TRACE (l_disp, 'INV_TXN_MANAGER_PUB', 9);
4469 END IF;
4470
4471 --FND_MESSAGE.set_name('INV','INV_RETURN_PROC');
4472 --l_disp := FND_MESSAGE.get;
4473 --inv_log_util.trace(l_disp, 'INV_TXN_MANAGER_PUB',9);
4474 SELECT COUNT (*)
4475 INTO l_totrows
4476 FROM mtl_material_transactions_temp
4477 WHERE transaction_header_id = l_header_id AND process_flag = 'Y';
4478
4479 x_trans_count := l_totrows;
4480
4481 IF (l_totrows = 0)
4482 THEN
4483 fnd_message.set_name ('INV', 'INV_PROC_WARN');
4484 l_disp := fnd_message.get;
4485
4486 IF (l_debug = 1)
4487 THEN
4488 inv_log_util.TRACE (l_disp || ' totrows = 0'
4489 , 'INV_TXN_MANAGER_PUB'
4490 , 9
4491 );
4492 END IF;
4493
4494 RETURN -1;
4495 END IF;
4496
4497 IF fnd_api.to_boolean (p_commit)
4498 THEN
4499 COMMIT WORK;
4500 ELSE
4501 SAVEPOINT process_trx_save;
4502 END IF;
4503 END IF;
4504
4505 /*WIP J-dev Add another condtion in the if
4506 /* statement below. if WIP.J is not installed call
4507 /* wip_mtlTempProc_grp()...else call process_lpn_trx()*/
4508 -- If transactions are of type WIP, then call the WIP API. This
4509 -- API does the WIP pre-processing before calling process_lpn_trx
4510 IF ( l_srctypeid = 5
4511 AND wip_constants.dmf_patchset_level <
4512 wip_constants.dmf_patchset_j_value
4513 )
4514 THEN
4515 wip_mtltempproc_grp.processtemp
4516 (p_initmsglist => fnd_api.g_false
4517 , p_processinv => fnd_api.g_true
4518 , -- call INV TM after WIP logic
4519 p_txnhdrid => l_header_id
4520 , x_returnstatus => l_return_status
4521 , x_errormsg => l_msg_data
4522 );
4523
4524 IF (l_return_status <> fnd_api.g_ret_sts_success)
4525 THEN
4526 IF (l_debug = 1)
4527 THEN
4528 inv_log_util.TRACE ('Failure from WIP processTemp!!'
4529 , 'INV_TXN_MANAGER_PUB'
4530 , 1
4531 );
4532 END IF;
4533
4534 l_result := -1;
4535 END IF;
4536 ELSE
4537 --Bug #4338316
4538 --Pass the p_commit value to the TM
4539 l_result :=
4540 inv_lpn_trx_pub.process_lpn_trx (p_trx_hdr_id => l_header_id
4541 , p_commit => p_commit
4542 , x_proc_msg => l_msg_data
4543 , p_proc_mode => 1
4544 , p_process_trx => fnd_api.g_true
4545 , p_atomic => fnd_api.g_false
4546 );
4547
4548
4549 END IF;
4550
4551 IF (l_result <> 0)
4552 THEN
4553 l_error_exp := l_msg_data;
4554 x_msg_data := l_msg_data;
4555 x_return_status := l_return_status;
4556 fnd_message.set_name ('INV', 'INV_INT_PROCCODE');
4557 l_error_code := fnd_message.get;
4558
4559 IF (l_debug = 1)
4560 THEN
4561 inv_log_util.TRACE ( 'PROCESS_LPN_TRX failed for header_id '
4562 || l_header_id
4563 , 'INV_TXN_MANAGER_PUB'
4564 , 1
4565 );
4566 inv_log_util.TRACE ('Error.... ' || l_error_exp
4567 , 'INV_TXN_MANAGER_PUB'
4568 , 9
4569 );
4570 END IF;
4571
4572 -- Bug 5748351: Deleting MSNT/MTLT/MMTT for the headerId, in case they are still present and did not
4573 -- get deleted in TM.
4574 delete from mtl_serial_numbers_temp
4575 where transaction_temp_id in (
4576 select mmtt.transaction_temp_id
4577 from mtl_material_transactions_temp mmtt
4578 where mmtt.transaction_header_id = l_header_id );
4579
4580 delete from mtl_serial_numbers_temp
4581 where transaction_temp_id in (
4582 select mtlt.serial_transaction_temp_id
4583 from mtl_transaction_lots_temp mtlt
4584 where mtlt.transaction_temp_id in (
4585 select mmtt.transaction_temp_id
4586 from mtl_material_transactions_temp mmtt
4587 where mmtt.transaction_header_id = l_header_id));
4588
4589 DELETE from mtl_transaction_lots_temp
4590 where transaction_temp_id in
4591 (select mmtt.transaction_temp_id
4592 from MTL_MATERIAL_TRANSACTIONS_TEMP mmtt
4593 WHERE mmtt.TRANSACTION_HEADER_ID = l_header_id );
4594
4595 DELETE FROM MTL_MATERIAL_TRANSACTIONS_TEMP
4596 WHERE TRANSACTION_HEADER_ID = l_header_id;
4597
4598 IF (l_debug = 1) THEN
4599 inv_log_util.trace('Deleted MSNT/MTLT/MMTT for header_id ' || l_header_id, 'INV_TXN_MANAGER_PUB',1);
4600 END IF;
4601
4602 -- End of change for bug 5748351
4603
4604 IF fnd_api.to_boolean (p_commit)
4605 THEN
4606 COMMIT WORK;
4607 END IF;
4608
4609 RETURN -1;
4610 END IF;
4611
4612 IF (l_debug = 1)
4613 THEN
4614 inv_log_util.TRACE ('After process_lpn_trx without errors'
4615 , 'INV_TXN_MANAGER_PUB'
4616 , 9
4617 );
4618 END IF;
4619
4620 IF fnd_api.to_boolean (p_commit)
4621 THEN
4622 COMMIT WORK;
4623 END IF;
4624
4625 IF (FIRST)
4626 THEN
4627 IF (l_debug = 1)
4628 THEN
4629 inv_log_util.TRACE ('Calling bflushchk', 'INV_TXN_MANAGER_PUB'
4630 , 9);
4631 END IF;
4632
4633 IF (NOT bflushchk (l_header_id))
4634 THEN
4635 l_error_code := fnd_message.get;
4636
4637 IF (l_debug = 1)
4638 THEN
4639 inv_log_util.TRACE ( 'Error in bflushchk header_id:'
4640 || l_header_id
4641 || ' - '
4642 || l_error_code
4643 , 'INV_TXN_MANAGER_PUB'
4644 , 9
4645 );
4646 END IF;
4647
4648 --ROLLBACK TO process_trx_save;
4649 RETURN -1;
4650 END IF;
4651
4652 IF (l_header_id <> -1)
4653 THEN
4654 fnd_message.set_name ('INV', 'INV_BFLUSH_PROC');
4655 l_disp := fnd_message.get;
4656
4657 IF (l_debug = 1)
4658 THEN
4659 inv_log_util.TRACE (l_disp, 'INV_TXN_MANAGER_PUB', 9);
4660 END IF;
4661
4662 SELECT COUNT (*)
4663 INTO l_totrows
4664 FROM mtl_material_transactions_temp
4665 WHERE transaction_header_id = l_header_id AND process_flag = 'Y';
4666
4667 IF (l_debug = 1)
4668 THEN
4669 inv_log_util.TRACE ('totrows is ' || l_totrows
4670 , 'INV_TXN_MANAGER_PUB'
4671 , 9
4672 );
4673 END IF;
4674
4675 IF (l_totrows > 200)
4676 THEN
4677 UPDATE mtl_material_transactions_temp
4678 SET transaction_header_id = (-1) * l_header_id
4679 WHERE transaction_header_id = l_header_id
4680 AND process_flag = 'Y';
4681
4682 UPDATE mtl_material_transactions_temp
4683 SET transaction_header_id = ABS (l_header_id)
4684 WHERE transaction_header_id = (-1) * (l_header_id)
4685 AND process_flag = 'Y'
4686 AND ROWNUM < 201;
4687 END IF;
4688
4689 fnd_message.set_name ('INV', 'INV_CALL_PROC');
4690 fnd_message.set_token ('token1', l_header_id);
4691 fnd_message.set_token ('token2', l_totrows);
4692 l_disp := fnd_message.get;
4693
4694 IF (l_debug = 1)
4695 THEN
4696 inv_log_util.TRACE (l_disp, 'INV_TXN_MANAGER_PUB', 9);
4697 END IF;
4698 ELSE
4699 done := TRUE;
4700 FIRST := FALSE;
4701 END IF;
4702 ELSE
4703 UPDATE mtl_material_transactions_temp
4704 SET transaction_header_id = ABS (l_header_id)
4705 WHERE transaction_header_id = (-1) * (l_header_id)
4706 AND process_flag = 'Y'
4707 AND ROWNUM < 201;
4708
4709 IF SQL%NOTFOUND
4710 THEN
4711 fnd_message.set_name ('INV', 'INV_RETURN_PROC');
4712 l_disp := fnd_message.get;
4713
4714 IF (l_debug = 1)
4715 THEN
4716 inv_log_util.TRACE (l_disp, 'INV_TXN_MANAGER_PUB', 9);
4717 END IF;
4718
4719 done := TRUE;
4720 END IF;
4721 END IF;
4722 END LOOP;
4723
4724 IF (l_initotrows > l_midtotrows)
4725 THEN
4726 fnd_message.set_name ('INV', 'INV_MGR_WARN');
4727 l_disp := fnd_message.get;
4728
4729 IF (l_debug = 1)
4730 THEN
4731 inv_log_util.TRACE (l_disp, 'INV_TXN_MANAGER_PUB', 9);
4732 inv_log_util.TRACE ( l_initotrows
4733 - l_midtotrows
4734 || ' Transactions did not pass validation'
4735 , 'INV_TXN_MANAGER_PUB'
4736 , 9
4737 );
4738 END IF;
4739
4740 RETURN -1;
4741 ELSE
4742 RETURN 0;
4743 END IF;
4744 END IF;
4745
4746 RETURN 0;
4747 EXCEPTION
4748 WHEN OTHERS
4749 THEN
4750 IF (l_debug = 1)
4751 THEN
4752 inv_log_util.TRACE ('*** SQL error ' || SUBSTR (SQLERRM, 1, 200)
4753 , 'INV_TXN_MANAGER_PUB'
4754 , 9
4755 );
4756 END IF;
4757
4758
4759 fnd_message.set_name ('INV', 'INV_INT_SQLCODE');
4760 l_error_code := fnd_message.get;
4761
4762 IF NOT fnd_api.to_boolean (p_commit)
4763 THEN
4764 ROLLBACK TO process_transactions_svpt;
4765 ELSE
4766 ROLLBACK WORK;
4767 END IF;
4768
4769 UPDATE mtl_transactions_interface
4770 SET last_update_date = SYSDATE
4771 , last_updated_by = l_userid
4772 , last_update_login = l_loginid
4773 , program_update_date = SYSDATE
4774 , process_flag = 3
4775 , lock_flag = 2
4776 , ERROR_CODE = SUBSTRB (l_error_code, 1, 240)
4777 , error_explanation = SUBSTRB (l_error_exp, 1, 240)
4778 WHERE transaction_header_id = l_header_id AND process_flag = 1;
4779
4780 IF fnd_api.to_boolean (p_commit)
4781 THEN
4782 COMMIT WORK;
4783 END IF;
4784
4785 RETURN -1;
4786 END process_transactions;
4787
4788 /******************************************************************
4789 *
4790 * Name: insert_relief
4791 * Description:
4792 * Creates a row in MRP_RELIEF_INTERFACE with the values it's passed.
4793 * This process was taken from mrlpr1.ppc to facilitate PLtion of PL/SQL TM API
4794 *
4795 ******************************************************************/
4796 FUNCTION insert_relief (
4797 p_new_order_qty NUMBER
4798 , p_new_order_date DATE
4799 , p_old_order_qty NUMBER
4800 , p_old_order_date DATE
4801 , p_item_id NUMBER
4802 , p_org_id NUMBER
4803 , p_disposition_id NUMBER
4804 , p_user_id NUMBER
4805 , p_line_num VARCHAR2
4806 , p_relief_type NUMBER
4807 , p_disposition VARCHAR2
4808 , p_demand_class VARCHAR2
4809 )
4810 RETURN BOOLEAN
4811 IS
4812 BEGIN
4813 IF (p_relief_type = mds_relief)
4814 THEN
4815 IF (p_disposition <> r_sales_order)
4816 THEN
4817 fnd_message.set_name ('MRP', 'GEN-invalid entity');
4818 fnd_message.set_token ('ENTITY', 'disposition');
4819 fnd_message.set_token ('VALUE', p_disposition);
4820 RETURN (FALSE);
4821 END IF;
4822 ELSE
4823 IF (p_relief_type = mps_relief)
4824 THEN
4825 IF (p_disposition <> r_work_order)
4826 AND (p_disposition <> r_purch_order)
4827 THEN
4828 fnd_message.set_name ('MRP', 'GEN-invalid entity');
4829 fnd_message.set_token ('ENTITY', 'disposition');
4830 fnd_message.set_token ('VALUE', p_disposition);
4831 RETURN (FALSE);
4832 END IF;
4833 ELSE
4834 fnd_message.set_name ('MRP', 'GEN-invalid entity');
4835 fnd_message.set_token ('ENTITY', 'relief_type');
4836 fnd_message.set_token ('VALUE', p_relief_type);
4837 RETURN (FALSE);
4838 END IF;
4839 END IF;
4840
4841 INSERT INTO mrp_relief_interface
4842 (transaction_id
4843 , inventory_item_id
4844 , organization_id
4845 , relief_type
4846 , disposition_type
4847 , last_update_date
4848 , last_updated_by
4849 , creation_date
4850 , created_by
4851 , last_update_login
4852 , new_order_quantity
4853 , new_order_date
4854 , old_order_quantity
4855 , old_order_date
4856 , disposition_id
4857 , demand_class
4858 , process_status
4859 , line_num
4860 )
4861 VALUES (mrp_relief_interface_s.NEXTVAL
4862 , p_item_id
4863 , p_org_id
4864 , p_relief_type
4865 , p_disposition
4866 , SYSDATE
4867 , p_user_id
4868 , SYSDATE
4869 , p_user_id
4870 , -1
4871 , p_new_order_qty
4872 , p_new_order_date
4873 , p_old_order_qty
4874 , p_old_order_date
4875 , p_disposition_id
4876 , p_demand_class
4877 , to_be_processed
4878 , p_line_num
4879 );
4880
4881 RETURN (TRUE);
4882 EXCEPTION
4883 WHEN OTHERS
4884 THEN
4885 IF (l_debug = 1)
4886 THEN
4887 inv_log_util.TRACE ('Error in insert_relief'
4888 , 'INV_TXN_MANAGER_PUB'
4889 , 9
4890 );
4891 inv_log_util.TRACE ('SQL : ' || SUBSTR (SQLERRM, 1, 200)
4892 , 'INV_TXN_MANAGER_PUB'
4893 , '9'
4894 );
4895 END IF;
4896
4897 RETURN (FALSE);
4898 END insert_relief;
4899
4900 /******************************************************************
4901 | Name: mrp_ship_order
4902 | Description:
4903 | Creates a row in MRP_RELIEF_INTERFACE with the values it's passed.
4904 ******************************************************************/
4905 FUNCTION mrp_ship_order (
4906 p_disposition_id NUMBER
4907 , p_inv_item_id NUMBER
4908 , p_quantity NUMBER
4909 , p_last_updated_by NUMBER
4910 , p_org_id NUMBER
4911 , p_line_num VARCHAR2
4912 , p_shipment_date DATE
4913 , p_demand_class VARCHAR2
4914 )
4915 RETURN BOOLEAN
4916 AS
4917 BEGIN
4918 IF (NOT insert_relief (p_quantity
4919 , p_shipment_date
4920 , 0
4921 , NULL
4922 , p_inv_item_id
4923 , p_org_id
4924 , p_disposition_id
4925 , p_last_updated_by
4926 , p_line_num
4927 , mds_relief
4928 , r_sales_order
4929 , p_demand_class
4930 )
4931 )
4932 THEN
4933 RETURN (FALSE);
4934 ELSE
4935 RETURN (TRUE);
4936 END IF;
4937 EXCEPTION
4938 WHEN OTHERS
4939 THEN
4940 IF (l_debug = 1)
4941 THEN
4942 inv_log_util.TRACE ('Error in mrp_ship_order'
4943 , 'INV_TXN_MANAGER_PUB'
4944 , 9
4945 );
4946 inv_log_util.TRACE ('SQL : ' || SUBSTR (SQLERRM, 1, 200)
4947 , 'INV_TXN_MANAGER_PUB'
4948 , '9'
4949 );
4950 END IF;
4951
4952 RETURN (FALSE);
4953 END mrp_ship_order;
4954
4955 /******************************************************************
4956 *
4957 * post_temp_validation()
4958 *
4959 ******************************************************************/
4960 FUNCTION post_temp_validation (
4961 p_line_rec_type line_rec_type
4962 , p_val_req NUMBER
4963 , p_userid NUMBER
4964 , p_flow_schedule NUMBER
4965 , p_lot_number VARCHAR2 -- Added for 4377625
4966 )
4967 RETURN BOOLEAN
4968 IS
4969 CURSOR z1 (p_flow_sch NUMBER)
4970 IS
4971 SELECT p_line_rec_type.ROWID
4972 , p_line_rec_type.inventory_item_id
4973 , p_line_rec_type.revision
4974 , p_line_rec_type.organization_id
4975 , p_line_rec_type.subinventory_code
4976 , p_line_rec_type.locator_id
4977 , ABS (p_line_rec_type.primary_quantity)
4978 , NULL
4979 , p_line_rec_type.transaction_source_type_id
4980 , p_line_rec_type.transaction_action_id
4981 ,p_line_rec_type.TRANSACTION_TYPE_ID /*Bug:4866991*/
4982 , p_line_rec_type.transaction_source_id
4983 , p_line_rec_type.transaction_source_name
4984 , TO_CHAR (p_line_rec_type.source_line_id)
4985 , msi.revision_qty_control_code
4986 , msi.lot_control_code
4987 , DECODE (p_line_rec_type.transaction_action_id
4988 , 2, p_line_rec_type.transfer_subinventory
4989 , 28, p_line_rec_type.transfer_subinventory
4990 , NULL
4991 )
4992 , p_line_rec_type.transfer_locator
4993 , p_line_rec_type.transaction_date
4994 , mp.negative_inv_receipt_code
4995 FROM mtl_parameters mp, mtl_system_items msi
4996 WHERE mp.organization_id = p_line_rec_type.organization_id
4997 -- AND MP.NEGATIVE_INV_RECEIPT_CODE = 2
4998 AND p_line_rec_type.process_flag = 1
4999 -- AND p_line_rec_type.SHIPPABLE_FLAG='Y'
5000 AND msi.lot_control_code = 1
5001 AND ( ( p_flow_sch <> 1
5002 AND p_line_rec_type.transaction_action_id IN
5003 (1, 2, 3, 21, 32, 34, 5)
5004 )
5005 OR (p_flow_sch = 1
5006 AND p_line_rec_type.transaction_action_id = 32
5007 )
5008 )
5009 AND msi.organization_id = mp.organization_id
5010 AND msi.organization_id = p_line_rec_type.organization_id
5011 AND msi.inventory_item_id = p_line_rec_type.inventory_item_id
5012 UNION
5013 SELECT p_line_rec_type.ROWID
5014 , p_line_rec_type.inventory_item_id
5015 , p_line_rec_type.revision
5016 , p_line_rec_type.organization_id
5017 , p_line_rec_type.subinventory_code
5018 , p_line_rec_type.locator_id
5019 , ABS (mtli.primary_quantity)
5020 , mtli.lot_number
5021 , p_line_rec_type.transaction_source_type_id
5022 , p_line_rec_type.transaction_action_id
5023 ,p_line_rec_type.TRANSACTION_TYPE_ID /*Bug:4866991*/
5024 , p_line_rec_type.transaction_source_id
5025 , p_line_rec_type.transaction_source_name
5026 , TO_CHAR (p_line_rec_type.source_line_id)
5027 , msi.revision_qty_control_code
5028 , msi.lot_control_code
5029 , DECODE (p_line_rec_type.transaction_action_id
5030 , 2, p_line_rec_type.transfer_subinventory
5031 , 28, p_line_rec_type.transfer_subinventory
5032 , 5, p_line_rec_type.transfer_subinventory
5033 , NULL
5034 )
5035 , p_line_rec_type.transfer_locator
5036 , p_line_rec_type.transaction_date
5037 , mp.negative_inv_receipt_code
5038 FROM mtl_transaction_lots_interface mtli
5039 , mtl_parameters mp
5040 , mtl_system_items msi
5041 WHERE mp.organization_id = p_line_rec_type.organization_id
5042 -- AND MP.NEGATIVE_INV_RECEIPT_CODE = 2
5043 -- AND p_line_rec_type.SHIPPABLE_FLAG='Y'
5044 AND mtli.transaction_interface_id =
5045 p_line_rec_type.transaction_interface_id
5046 AND p_line_rec_type.process_flag = 1
5047 AND ts_default <> ts_save_only
5048 AND msi.lot_control_code = 2
5049 AND ( ( p_flow_sch <> 1
5050 AND p_line_rec_type.transaction_action_id IN
5051 (1, 2, 3, 21, 32, 34, 5)
5052 )
5053 OR (p_flow_sch = 1
5054 AND p_line_rec_type.transaction_action_id = 32
5055 )
5056 )
5057 AND msi.organization_id = mp.organization_id
5058 AND msi.organization_id = p_line_rec_type.organization_id
5059 AND msi.inventory_item_id = p_line_rec_type.inventory_item_id
5060 AND MTLI.LOT_NUMBER = NVL(p_lot_number, MTLI.LOT_NUMBER); -- Added for 4377625
5061
5062 CURSOR c1
5063 IS
5064 SELECT a.organization_id
5065 , a.inventory_item_id
5066 , NVL (a.transaction_source_id, 0)
5067 , a.transaction_source_type_id
5068 , a.trx_source_delivery_id
5069 , a.trx_source_line_id
5070 , a.revision
5071 , DECODE (c.lot_control_code, 2, b.lot_number, a.lot_number)
5072 , a.subinventory_code
5073 , a.locator_id
5074 , DECODE (c.lot_control_code
5075 , 2, ABS (NVL (b.primary_quantity, 0))
5076 , a.primary_quantity * (-1)
5077 )
5078 , a.transaction_source_name
5079 , a.transaction_date
5080 , a.content_lpn_id
5081 FROM mtl_system_items c
5082 , mtl_transaction_lots_temp b
5083 , mtl_material_transactions_temp a
5084 WHERE a.transaction_header_id = p_line_rec_type.transaction_header_id
5085 AND a.transaction_temp_id =
5086 p_line_rec_type.transaction_interface_id
5087 AND a.organization_id = c.organization_id
5088 AND a.inventory_item_id = c.inventory_item_id
5089 AND b.transaction_temp_id(+) = a.transaction_temp_id
5090 AND a.primary_quantity < 0
5091 ORDER BY a.transaction_source_type_id
5092 , a.transaction_source_id
5093 , a.transaction_source_name
5094 , a.trx_source_line_id
5095 , a.trx_source_delivery_id
5096 , a.inventory_item_id
5097 , a.organization_id;
5098
5099 l_tempid NUMBER;
5100 l_item_id NUMBER;
5101 l_org_id NUMBER;
5102 l_locid NUMBER;
5103 l_srctypeid NUMBER;
5104 l_actid NUMBER;
5105 l_trxtypeid NUMBER; --Bug:4866991
5106 l_srcid NUMBER;
5107 l_xlocid NUMBER;
5108 l_temp_rowid VARCHAR2 (21);
5109 l_sub_code VARCHAR2 (11);
5110 l_lotnum VARCHAR2 (80); -- changed lot_number to 80, inconv
5111 --Bug #5086940
5112 --Changed the length to correspond to transaction_source_name
5113 l_src_code mtl_transactions_interface.transaction_source_name%TYPE;
5114 l_xfrsub VARCHAR2 (11);
5115 l_rev VARCHAR2 (4);
5116 l_srclineid VARCHAR2 (40);
5117 l_trxdate DATE;
5118 l_qoh NUMBER;
5119 l_rqoh NUMBER;
5120 l_pqoh NUMBER;
5121 l_qr NUMBER;
5122 l_qs NUMBER;
5123 l_att NUMBER;
5124 l_atr NUMBER;
5125 l_rctrl NUMBER;
5126 l_lctrl NUMBER;
5127 l_flow_schedule NUMBER;
5128 l_trx_qty NUMBER;
5129 l_qty NUMBER := 0;
5130 tree_exists BOOLEAN;
5131 l_revision_control BOOLEAN;
5132 l_lot_control BOOLEAN;
5133 l_disp VARCHAR2 (3000);
5134 l_msg_count NUMBER;
5135 l_msg_data VARCHAR2 (2000);
5136 l_return_status VARCHAR2 (1);
5137 l_tree_id NUMBER;
5138 /* Added the following variables for Bug 3462946 */
5139 l_neg_inv_rcpt NUMBER;
5140 l_cnt_res NUMBER;
5141 l_item_qoh NUMBER;
5142 l_item_rqoh NUMBER;
5143 l_item_pqoh NUMBER;
5144 l_item_qr NUMBER;
5145 l_item_qs NUMBER;
5146 l_item_att NUMBER;
5147 l_item_atr NUMBER;
5148 /* Additional Variables needed to handle TrxRsvRelief code */
5149 l_ship_qty NUMBER;
5150 l_userline VARCHAR2 (40);
5151 l_demand_class VARCHAR2 (30);
5152 l_mps_flag NUMBER;
5153 l_deliveryid NUMBER;
5154 l_lpnid NUMBER;
5155 targetnode NUMBER;
5156 x_errd_int_id NUMBER;
5157
5158 l_procedure_name VARCHAR2(60) := g_pkg_name || '.' ||'POST_TEMP_VALIDATION';
5159 l_progress_indicator VARCHAR2(30) := '0';
5160
5161 BEGIN
5162 IF (l_debug IS NULL) THEN
5163 l_debug := NVL (fnd_profile.VALUE ('INV_DEBUG_TRACE'), 0);
5164 END IF;
5165
5166 IF (l_debug = 1) THEN
5167 inv_log_util.TRACE ('$Header: INVTXMGB.pls 120.28 2007/11/12 22:43:39 yssingh ship $' , l_procedure_name , 9);
5168 END IF;
5169
5170 l_progress_indicator := '10';
5171
5172 /**********************************************/
5173 /* the reservation was successfully releived. */
5174 /* now if we did ship a +ve qty for a intord */
5175 /* or a sales order, then we need to notify */
5176 /* mrp about this shipment */
5177 /**********************************************/
5178 IF (p_val_req = 1) THEN
5179
5180 l_progress_indicator := '20';
5181 OPEN z1 (p_flow_schedule);
5182 tree_exists := FALSE;
5183
5184 WHILE (TRUE) LOOP
5185
5186 l_progress_indicator := '30';
5187 FETCH z1 INTO l_temp_rowid
5188 , l_item_id
5189 , l_rev
5190 , l_org_id
5191 , l_sub_code
5192 , l_locid
5193 , l_trx_qty
5194 , l_lotnum
5195 , l_srctypeid
5196 , l_actid
5197 , l_trxtypeid /*Bug:4866991*/
5198 , l_srcid
5199 , l_src_code
5200 , l_srclineid
5201 , l_rctrl
5202 , l_lctrl
5203 , l_xfrsub
5204 , l_xlocid
5205 , l_trxdate
5206 , l_neg_inv_rcpt;
5207
5208 IF z1%NOTFOUND THEN
5209 l_progress_indicator := '40';
5210 IF (l_debug = 1) THEN
5211 inv_log_util.TRACE ('No more rows to validate quantity'
5212 , l_procedure_name
5213 , 9
5214 );
5215 END IF;
5216 EXIT;
5217 END IF;
5218
5219 l_progress_indicator := '50';
5220 IF l_rctrl = 1 THEN
5221 l_revision_control := FALSE;
5222 ELSE
5223 l_revision_control := TRUE;
5224 END IF;
5225
5226 IF l_lctrl = 1 THEN
5227 l_lot_control := FALSE;
5228 ELSE
5229 l_lot_control := TRUE;
5230 END IF;
5231
5232 tree_exists := TRUE;
5233 -- Bug 2399354 The tree to be cleared prior to creating a tree to avoid
5234 -- using existing trees
5235
5236 /*** free cache ***/
5237 IF p_line_rec_type.transaction_interface_id IS NULL THEN
5238
5239 IF (l_debug = 1) THEN
5240 inv_log_util.TRACE ('Interface Id is NULL'
5241 , l_procedure_name
5242 , 9
5243 );
5244 END IF;
5245 l_progress_indicator := '60';
5246 inv_quantity_tree_pvt.clear_quantity_cache;
5247
5248 --Bug #5086940
5249 --demand_source_name cannot be greater than 30 characters
5250 IF (LENGTH(l_src_code) > 30) THEN
5251 l_src_code := NULL;
5252 END IF;
5253
5254 l_progress_indicator := '70';
5255 inv_quantity_tree_pvt.create_tree (
5256 p_api_version_number => 1.0
5257 , p_init_msg_lst => fnd_api.g_false
5258 , x_return_status => l_return_status
5259 , x_msg_count => l_msg_count
5260 , x_msg_data => l_msg_data
5261 , p_organization_id => l_org_id
5262 , p_inventory_item_id => l_item_id
5263 , p_tree_mode => 2
5264 , p_is_revision_control => l_revision_control
5265 , p_is_lot_control => l_lot_control
5266 , p_is_serial_control => FALSE
5267 , p_include_suggestion => FALSE
5268 , p_demand_source_type_id => NVL
5269 (l_srctypeid
5270 , -9999
5271 )
5272 , p_demand_source_header_id => NVL
5273 (l_srcid
5274 , -9999
5275 )
5276 , p_demand_source_line_id => NVL
5277 (l_srclineid
5278 , -9999
5279 )
5280 , p_demand_source_name => l_src_code
5281 , p_demand_source_delivery => NULL
5282 , p_lot_expiration_date => NULL
5283 , x_tree_id => l_tree_id
5284 , p_onhand_source => 3
5285 --g_all_subs
5286 , p_exclusive => 0
5287 --g_non_exclusive
5288 , p_pick_release => 0
5289 --g_pick_release_no
5290 );
5291
5292 IF l_return_status = fnd_api.g_ret_sts_error THEN
5293 inv_log_util.TRACE
5294 ( 'Error while creating tree : x_msg_data = '
5295 || l_msg_data
5296 , l_procedure_name
5297 , 9
5298 );
5299 fnd_message.set_name ('INV', 'INV_ERR_CREATETREE');
5300 fnd_message.set_token ('ROUTINE', 'UE:AVAIL_TO_TRX');
5301 l_error_code := fnd_message.get;
5302 l_error_exp := l_msg_data;
5303 RAISE fnd_api.g_exc_error;
5304 END IF;
5305
5306 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
5307 inv_log_util.TRACE ( 'Unexpected Error while creating tree : '
5308 || l_msg_data
5309 , l_procedure_name
5310 , 9
5311 );
5312 l_error_exp := l_msg_data;
5313 RAISE fnd_api.g_exc_unexpected_error;
5314 END IF;
5315
5316 l_progress_indicator := '80';
5317 g_tree_id := l_tree_id;
5318 tree_exists := true;
5319
5320 ELSE
5321
5322 l_progress_indicator := '90';
5323 l_tree_id := g_tree_id;
5324 tree_exists := false;
5325
5326 END IF;
5327
5328 IF (l_debug = 1) THEN
5329 inv_log_util.TRACE ('tree id : '||l_tree_id , l_procedure_name , 9);
5330 inv_log_util.TRACE ('Revision is : '||l_rev , l_procedure_name , 9);
5331 inv_log_util.TRACE ('Lot is : '||l_lotnum , l_procedure_name , 9);
5332 inv_log_util.TRACE ('Sub is : '||l_sub_code , l_procedure_name , 9);
5333 inv_log_util.TRACE ('Xfr Sub is : '||l_xfrsub , l_procedure_name , 9);
5334 inv_log_util.TRACE ('Locator is : '||l_locid , l_procedure_name , 9);
5335 END IF;
5336
5337 l_progress_indicator := '100';
5338 inv_quantity_tree_pvt.query_tree
5339 (p_api_version_number => 1.0
5340 , p_init_msg_lst => fnd_api.g_false
5341 , x_return_status => l_return_status
5342 , x_msg_count => l_msg_count
5343 , x_msg_data => l_msg_data
5344 , p_tree_id => l_tree_id
5345 , p_revision => l_rev
5346 , p_lot_number => l_lotnum
5347 , p_subinventory_code => l_sub_code
5348 , p_transfer_subinventory_code => l_xfrsub
5349 , p_locator_id => l_locid
5350 , x_qoh => l_qoh
5351 , x_rqoh => l_rqoh
5352 , x_pqoh => l_pqoh
5353 , x_qr => l_qr
5354 , x_qs => l_qs
5355 , x_att => l_att
5356 , x_atr => l_atr
5357 );
5358
5359 IF l_return_status = fnd_api.g_ret_sts_error THEN
5360 inv_log_util.TRACE ( 'Expected Error while querying tree : '
5361 || l_msg_data
5362 , l_procedure_name
5363 , 9
5364 );
5365 l_error_code := fnd_message.get;
5366 l_error_exp := l_msg_data;
5367 fnd_message.set_name ('INV', 'INV_INTERNAL_ERROR');
5368 fnd_message.set_token ('token1', 'XACT_QTY1');
5369 RAISE fnd_api.g_exc_error;
5370 END IF;
5371
5372 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
5373 inv_log_util.TRACE ( 'UnExpected Error while querying tree : '
5374 || l_msg_data
5375 , l_procedure_name
5376 , 9
5377 );
5378 l_error_code := fnd_message.get;
5379 l_error_exp := l_msg_data;
5380 fnd_message.set_name ('INV', 'INV_INTERNAL_ERROR');
5381 fnd_message.set_token ('token1', 'XACT_QTY1');
5382 RAISE fnd_api.g_exc_unexpected_error;
5383 END IF;
5384
5385 l_progress_indicator := '110';
5386 IF (l_debug = 1) THEN
5387 inv_log_util.TRACE ('L_QOH : ' || l_qoh, l_procedure_name, 9);
5388 inv_log_util.TRACE ('L_RQOH : ' || l_rqoh, l_procedure_name, 9);
5389 inv_log_util.TRACE ('L_PQOH : ' || l_pqoh, l_procedure_name, 9);
5390 inv_log_util.TRACE ('L_QR : ' || l_qr, l_procedure_name, 9);
5391 inv_log_util.TRACE ('L_QS : ' || l_qs, l_procedure_name, 9);
5392 inv_log_util.TRACE ('L_ATT : ' || l_att, l_procedure_name, 9);
5393 inv_log_util.TRACE ('L_ATR : ' || l_atr, l_procedure_name, 9);
5394 END IF;
5395
5396 /* Bug: 3462946 : Added the code below to check for Negative Balances for a Negative Balances Allowed Org */
5397 IF l_att < 0 THEN
5398 l_progress_indicator := '120';
5399 inv_log_util.TRACE ('l_att is than zero', l_procedure_name, 9);
5400
5401 IF (l_neg_inv_rcpt = 1) THEN
5402 l_progress_indicator := '130';
5403 inv_log_util.TRACE ('Negative Balance Allowed Org '
5404 , l_procedure_name
5405 , 9
5406 );
5407
5408 IF (l_qr > 0 OR l_qs > 0) THEN
5409 inv_log_util.TRACE (
5410 'Transaction quantity must be less than or equal to available quantity'
5411 , l_procedure_name
5412 , 9
5413 );
5414 fnd_message.set_name ('INV', 'INV_INT_PROCCODE');
5415 l_error_code := fnd_message.get;
5416 fnd_message.set_name ('INV', 'INV_QTY_LESS_OR_EQUAL');
5417 l_error_exp := fnd_message.get;
5418 RAISE fnd_api.g_exc_error;
5419 END IF;
5420
5421 l_progress_indicator := '140';
5422 inv_quantity_tree_pvt.query_tree
5423 (p_api_version_number => 1.0
5424 , p_init_msg_lst => fnd_api.g_false
5425 , x_return_status => l_return_status
5426 , x_msg_count => l_msg_count
5427 , x_msg_data => l_msg_data
5428 , p_tree_id => l_tree_id
5429 , p_revision => NULL
5430 , p_lot_number => NULL
5431 , p_subinventory_code => NULL
5432 , p_locator_id => NULL
5433 , x_qoh => l_item_qoh
5434 , x_rqoh => l_item_rqoh
5435 , x_pqoh => l_item_pqoh
5436 , x_qr => l_item_qr
5437 , x_qs => l_item_qs
5438 , x_att => l_item_att
5439 , x_atr => l_item_atr
5440 );
5441
5442 IF l_return_status = fnd_api.g_ret_sts_error THEN
5443 inv_log_util.TRACE
5444 ( 'Expected Error while querying tree : '
5445 || l_msg_data
5446 , l_procedure_name
5447 , 9
5448 );
5449 l_error_code := fnd_message.get;
5450 l_error_exp := l_msg_data;
5451 fnd_message.set_name ('INV', 'INV_INTERNAL_ERROR');
5452 fnd_message.set_token ('token1', 'XACT_QTY1');
5453 RAISE fnd_api.g_exc_error;
5454 END IF;
5455
5456 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
5457 inv_log_util.TRACE
5458 ( 'UnExpected Error while querying tree : '
5459 || l_msg_data
5460 , l_procedure_name
5461 , 9
5462 );
5463
5464 l_error_code := fnd_message.get;
5465 l_error_exp := l_msg_data;
5466 fnd_message.set_name ('INV', 'INV_INTERNAL_ERROR');
5467 fnd_message.set_token ('token1', 'XACT_QTY1');
5468 RAISE fnd_api.g_exc_unexpected_error;
5469 END IF;
5470
5471 l_progress_indicator := '150';
5472 IF (l_debug = 1) THEN
5473 inv_log_util.TRACE ('L_ITEM_QOH : ' || l_item_qoh
5474 , l_procedure_name
5475 , 9
5476 );
5477 inv_log_util.TRACE ('L_ITEM_RQOH : ' || l_item_rqoh
5478 , l_procedure_name
5479 , 9
5480 );
5481 inv_log_util.TRACE ('L_ITEM_PQOH : ' || l_item_pqoh
5482 , l_procedure_name
5483 , 9
5484 );
5485 inv_log_util.TRACE ('L_ITEM_QR : ' || l_item_qr
5486 , l_procedure_name
5487 , 9
5488 );
5489 inv_log_util.TRACE ('L_ITEM_QS : ' || l_item_qs
5490 , l_procedure_name
5491 , 9
5492 );
5493 inv_log_util.TRACE ('L_ITEM_ATT : ' || l_item_att
5494 , l_procedure_name
5495 , 9
5496 );
5497 inv_log_util.TRACE ('L_ITEM_ATR : ' || l_item_atr
5498 , l_procedure_name
5499 , 9
5500 );
5501 inv_log_util.TRACE ('L_TRX_QTY : ' || l_trx_qty
5502 , l_procedure_name
5503 , 9
5504 );
5505 END IF;
5506
5507 IF (l_item_qoh <> l_item_att) THEN -- Higher Level Reservations
5508 l_progress_indicator := '160';
5509
5510 IF (l_item_att < 0 AND l_item_qr > 0) THEN
5511 /*
5512 * Bug:4866991. For subinventory and backflush transfers high level
5513 * reservations should not be checked
5514 */
5515 l_progress_indicator := '170';
5516 IF ( l_srctypeid = 13 AND l_actid = 2 AND l_trxtypeid not in (66,67,68) ) THEN
5517 inv_log_util.trace(
5518 'Do not check high level reservations for subinventory and backflush transfers'
5519 ,l_procedure_name
5520 ,9
5521 );
5522 ELSE
5523 inv_log_util.TRACE (
5524 'Total Org quantity cannot become negative when there are reservations present'
5525 ,l_procedure_name
5526 ,9
5527 );
5528 fnd_message.set_name ('INV', 'INV_INT_PROCCODE');
5529 l_error_code := fnd_message.get;
5530 fnd_message.set_name ('INV', 'INV_ORG_QUANTITY');
5531 l_error_exp := fnd_message.get;
5532 RAISE fnd_api.g_exc_error;
5533 END IF;
5534 END IF;
5535 END IF;
5536 ELSE --if (neg_inv_rcpt = 1)
5537 l_progress_indicator := '180';
5538 fnd_message.set_name ('INV', 'INV_NO_NEG_BALANCES');
5539 l_error_code := fnd_message.get;
5540 fnd_message.set_name ('INV', 'INV_LESS_OR_EQUAL');
5541 fnd_message.set_token ('ENTITY1', 'INV_QUANTITY');
5542 fnd_message.set_token ('ENTITY2', 'AVAIL_TO_TRANSACT');
5543 l_error_exp := fnd_message.get;
5544 RAISE fnd_api.g_exc_error;
5545 --exit;
5546 END IF; -- neg_inv_rcpt
5547 END IF; -- l_att
5548 /* End of changes for Bug 3462946 */
5549 END LOOP;
5550
5551 /* This should be for any error other than not found */
5552 l_progress_indicator := '190';
5553 CLOSE z1;
5554
5555 IF (tree_exists) THEN
5556 l_progress_indicator := '200';
5557 inv_quantity_tree_pvt.free_all (p_api_version_number => 1.0
5558 , p_init_msg_lst => fnd_api.g_false
5559 , x_return_status => l_return_status
5560 , x_msg_count => l_msg_count
5561 , x_msg_data => l_msg_data
5562 );
5563 END IF;
5564
5565 END IF; -- p_val_req
5566
5567 x_errd_int_id := -9876;
5568 RETURN TRUE;
5569
5570 EXCEPTION
5571 WHEN OTHERS THEN
5572 inv_log_util.TRACE ('At indicator : ' || l_progress_indicator, l_procedure_name, 9);
5573 inv_log_util.TRACE ('Error in post_temp_validation : ' || l_error_code
5574 , l_procedure_name
5575 , '1'
5576 );
5577 inv_log_util.TRACE ('SQL : ' || SUBSTR (SQLERRM, 1, 200)
5578 , l_procedure_name
5579 , '1'
5580 );
5581 x_errd_int_id := -9876;
5582 RETURN FALSE;
5583 END post_temp_validation;
5584
5585 -- Bug 4764790: passing the transaction id for relieving
5586 -- reservations along with the serial numbers
5587 PROCEDURE rel_reservations_mrp_update
5588 (p_header_id IN NUMBER
5589 , p_transaction_temp_id IN NUMBER
5590 , p_transaction_id IN NUMBER DEFAULT NULL
5591 , p_res_sts OUT NOCOPY VARCHAR2
5592 , p_res_msg OUT NOCOPY VARCHAR2
5593 , p_res_count OUT NOCOPY NUMBER
5594 , p_mrp_status OUT NOCOPY VARCHAR2
5595 )
5596 IS
5597 CURSOR c1
5598 IS
5599 SELECT a.organization_id
5600 , a.inventory_item_id
5601 , NVL (a.transaction_source_id, 0)
5602 , a.transaction_source_type_id
5603 , a.trx_source_delivery_id
5604 , a.trx_source_line_id
5605 , a.revision
5606 , DECODE (c.lot_control_code, 2, b.lot_number, a.lot_number)
5607 , a.subinventory_code
5608 , a.locator_id
5609 , DECODE (c.lot_control_code
5610 , 2, ABS (NVL (b.primary_quantity, 0))
5611 , a.primary_quantity * (-1)
5612 )
5613 , a.transaction_source_name
5614 , a.transaction_date
5615 , a.content_lpn_id
5616 , a.primary_quantity
5617 , --
5618 a.transaction_action_id
5619 , A.transaction_type_id /*Bug:4866991*/
5620 , a.transfer_subinventory
5621 , a.transfer_to_location
5622 , DECODE (a.process_flag, 'Y', 1, 'N', 2, 'E', 3, 3)
5623 , a.shippable_flag
5624 , b.transaction_temp_id --lot record identifier in MTLT
5625 , a.relieve_high_level_rsv_flag /*** {{ R12 Enhanced reservations code changes ***/
5626 FROM mtl_system_items c
5627 , mtl_transaction_lots_temp b
5628 , mtl_material_transactions_temp a
5629 WHERE a.transaction_header_id = p_header_id
5630 AND a.transaction_temp_id = p_transaction_temp_id
5631 AND a.organization_id = c.organization_id
5632 AND a.inventory_item_id = c.inventory_item_id
5633 AND b.transaction_temp_id(+) = a.transaction_temp_id
5634 -- AND A.PRIMARY_QUANTITY < 0 /* Bug: 3462946: This clause is commented as BaseTransaction.java already does this validation */
5635 ORDER BY a.transaction_source_type_id
5636 , a.transaction_source_id
5637 , a.transaction_source_name
5638 , a.trx_source_line_id
5639 , a.trx_source_delivery_id
5640 , a.inventory_item_id
5641 , a.organization_id;
5642
5643 l_return_status VARCHAR2 (1);
5644 l_msg_count NUMBER;
5645 l_msg_data VARCHAR2 (2000);
5646 l_ship_qty NUMBER;
5647 l_userline VARCHAR2 (40);
5648 l_demand_class VARCHAR2 (30);
5649 l_mps_flag NUMBER;
5650 l_org_id NUMBER;
5651 l_item_id NUMBER;
5652 l_sub_code VARCHAR2 (11);
5653 l_locid NUMBER;
5654 l_lotnum VARCHAR2 (80); -- changed lot_number to 80, inconv
5655 l_rev VARCHAR2 (4);
5656 l_srctypeid NUMBER;
5657 l_srcid NUMBER;
5658 --Bug #5086940
5659 --Changed the length to correspond to transaction_source_name
5660 l_src_code mtl_transactions_interface.transaction_source_name%TYPE;
5661 l_srclineid VARCHAR2 (40);
5662 l_deliveryid NUMBER;
5663 l_trx_qty NUMBER;
5664 l_trxdate DATE;
5665 l_userid NUMBER;
5666 l_lpnid NUMBER;
5667 l_line_rec_type line_rec_type;
5668 l_loginid NUMBER;
5669 -- INVCONV fabdi start
5670 l_secondary_ship_qty NUMBER;
5671 l_qty_at_suom NUMBER;
5672 -- INVCONV fabdi end
5673
5674 /*** {{ R12 Enhanced reservations code changes ***/
5675 l_relieve_high_level_rsv_flag VARCHAR2(1);
5676 l_total_prim_qty_to_relieve NUMBER := 0;
5677 l_rel_lpn_id NUMBER := null;
5678 l_rel_loc_id NUMBER := null;
5679 l_rel_sub_code VARCHAR2(11) := null;
5680 l_rel_lot_num VARCHAR2(80) := null;
5681 l_rel_revision VARCHAR2(4) := null;
5682 -- Bug 4764790: passing the transaction id for relieving
5683 -- reservations along with the serial numbers
5684 l_transaction_id NUMBER := NULL;
5685 l_wip_entity_type NUMBER := NULL; -- Bug 4764790
5686 l_wip_job_type VARCHAR2(15); -- Bug 4764790
5687 l_loop_exit NUMBER := 0;
5688 /*** End R12 }} ***/
5689
5690 tree_exists BOOLEAN := false;
5691 l_tree_id NUMBER;
5692 l_lctrl NUMBER;
5693 l_rctrl NUMBER;
5694 l_revision_control BOOLEAN := FALSE;
5695 l_lot_control BOOLEAN := FALSE;
5696
5697 l_procedure_name VARCHAR2(60) := g_pkg_name || '.' || 'REL_RESERVATIONS_MRP_UPDATE';
5698 l_progress_indicator VARCHAR2(20) := '0';
5699 BEGIN
5700
5701 IF (l_debug IS NULL) THEN
5702 l_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
5703 END IF;
5704
5705 IF (l_debug = 1) THEN
5706 inv_log_util.TRACE ('$Header: INVTXMGB.pls 120.28 2007/11/12 22:43:39 yssingh ship $', l_procedure_name,9);
5707 END IF;
5708
5709
5710 IF (g_userid IS NULL) THEN
5711 g_userid := NVL (fnd_profile.VALUE ('USER_ID'), -1);
5712 END IF;
5713
5714 l_userid := g_userid;
5715 l_loginid := NVL(fnd_global.login_id, -1);
5716
5717 IF (l_debug = 1) THEN
5718 inv_log_util.TRACE ('USERID :' || l_userid
5719 , l_procedure_name
5720 , 9
5721 );
5722 inv_log_util.TRACE ('LoginId :' || l_loginid
5723 , l_procedure_name
5724 , 9
5725 );
5726 END IF;
5727
5728 p_mrp_status := 'S';
5729 p_res_sts := 'S';
5730 p_res_msg := '';
5731 p_res_count := 0;
5732
5733 l_progress_indicator := '10';
5734 OPEN c1;
5735 LOOP
5736 l_progress_indicator := '20';
5737 FETCH c1 INTO l_org_id
5738 , l_item_id
5739 , l_srcid
5740 , l_srctypeid
5741 , l_deliveryid
5742 , l_srclineid
5743 , l_rev
5744 , l_lotnum
5745 , l_sub_code
5746 , l_locid
5747 , l_trx_qty
5748 , l_src_code
5749 , l_trxdate
5750 , l_lpnid
5751 , l_line_rec_type.primary_quantity
5752 , l_line_rec_type.transaction_action_id
5753 , l_line_rec_type.TRANSACTION_TYPE_ID /*Bug:4866991*/
5754 , l_line_rec_type.transfer_subinventory
5755 , l_line_rec_type.transfer_locator
5756 , l_line_rec_type.process_flag
5757 , l_line_rec_type.shippable_flag
5758 , l_line_rec_type.transaction_interface_id
5759 , l_relieve_high_level_rsv_flag;
5760
5761 IF c1%NOTFOUND THEN
5762 l_progress_indicator := '30';
5763 IF (l_debug = 1) THEN
5764 inv_log_util.TRACE ('No more rows to relieve'
5765 , l_procedure_name
5766 , 9
5767 );
5768 END IF;
5769 p_res_sts := 'S';
5770 p_res_msg := '';
5771 p_res_count := 0;
5772 EXIT;
5773 END IF;
5774
5775 l_progress_indicator := '40';
5776 -- Bug 4764790: passing the transaction id for relieving
5777 -- reservations along with the serial numbers
5778 l_transaction_id := p_transaction_id;
5779
5780 IF (l_srctypeid = job_schedule) THEN
5781 l_progress_indicator := '50';
5782 -- call get_wip_entity API
5783 inv_reservation_pvt.get_wip_entity_type
5784 ( p_api_version_number => 1.0
5785 , p_init_msg_lst => fnd_api.g_false
5786 , x_return_status => l_return_status
5787 , x_msg_count => l_msg_count
5788 , x_msg_data => l_msg_data
5789 , p_organization_id => null
5790 , p_item_id => null
5791 , p_source_type_id => null
5792 , p_source_header_id => l_srcid
5793 , p_source_line_id => null
5794 , p_source_line_detail => null
5795 , x_wip_entity_type => l_wip_entity_type
5796 , x_wip_job_type => l_wip_job_type
5797 );
5798
5799 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
5800 inv_log_util.TRACE ('Return status from get wip entity. ' ||l_return_status, l_procedure_name, 9);
5801 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
5802 inv_log_util.TRACE ('Return status from get wip entity. ' ||l_return_status, l_procedure_name, 9);
5803 END IF;
5804
5805 END IF; -- job_schedule
5806
5807 l_progress_indicator := '60';
5808 IF (l_debug = 1) THEN
5809 inv_log_util.TRACE ('Wip entity type ' || l_wip_entity_type, l_procedure_name, 9);
5810 inv_log_util.TRACE ('l_srctypeid ' || l_srctypeid, l_procedure_name, 9);
5811 END IF;
5812
5813 IF ((l_srctypeid = job_schedule) AND (l_wip_entity_type =
5814 inv_reservation_global.g_wip_source_type_cmro)) THEN
5815 l_src_code := fnd_api.g_miss_char;
5816 END IF;
5817
5818 --Bug #5086940
5819 --demand_source_name cannot be greater than 30 characters
5820 IF (LENGTH(l_src_code) > 30) THEN
5821 l_src_code := fnd_api.g_miss_char;
5822 END IF;
5823
5824 IF (l_debug = 1) THEN
5825 inv_log_util.trace('l_src_code is: ' || l_src_code, l_procedure_name, 9);
5826 END IF;
5827
5828 l_progress_indicator := '70';
5829
5830 -- SRSRIRAN Bug 4437767
5831 -- Removed inline code branching related to INCONV/ K release
5832 IF ( NOT ((l_srctypeid = job_schedule) AND (l_wip_entity_type <> inv_reservation_global.g_wip_source_type_cmro))) THEN
5833 l_progress_indicator := '80';
5834 IF (l_debug = 1) THEN
5835 inv_log_util.TRACE ('Inside rsv_relief', l_procedure_name, 9);
5836 END IF;
5837 -- End changes for Bug 4764790
5838 inv_trx_relief_c_pvt.rsv_relief
5839 (x_return_status => l_return_status
5840 , x_msg_count => l_msg_count
5841 , x_msg_data => l_msg_data
5842 , x_ship_qty => l_ship_qty --it will be the quantity relieved FROM this api
5843 , x_secondary_ship_qty => l_secondary_ship_qty --INVCONV fabdi
5844 , x_userline => l_userline
5845 , x_demand_class => l_demand_class
5846 , x_mps_flag => l_mps_flag
5847 , p_organization_id => l_org_id
5848 , p_inventory_item_id => l_item_id
5849 , p_subinv => l_sub_code
5850 , p_locator => l_locid
5851 , p_lotnumber => l_lotnum
5852 , p_revision => l_rev
5853 , p_dsrc_type => l_srctypeid
5854 , p_header_id => l_srcid
5855 , p_dsrc_name => l_src_code
5856 , p_dsrc_line => l_srclineid
5857 , p_dsrc_delivery => NULL --l_deliveryid bug2745896
5858 , p_qty_at_puom => ABS (l_trx_qty)
5859 , p_qty_at_suom => l_qty_at_suom -- INVCONV fabdi
5860 , p_lpn_id => l_lpnid
5861 , p_transaction_id => l_transaction_id --Bug 4764790
5862 );
5863
5864 l_progress_indicator := '90';
5865 /*** {{ R12 Enhanced reservations code changes ***/
5866 l_total_prim_qty_to_relieve := l_trx_qty - l_ship_qty;
5867
5868 --Set the default as 'Y'
5869 l_relieve_high_level_rsv_flag := Nvl(l_relieve_high_level_rsv_flag,'Y');
5870 IF (l_relieve_high_level_rsv_flag = 'Y' and l_total_prim_qty_to_relieve > 0) THEN
5871 -- start to relieve reservation with higher level
5872 l_progress_indicator := '100';
5873 l_rel_lpn_id := l_lpnid;
5874 l_rel_loc_id := l_locid;
5875 l_rel_sub_code := l_sub_code;
5876 l_rel_lot_num := l_lotnum;
5877 l_rel_revision := l_rev;
5878
5879 WHILE (l_total_prim_qty_to_relieve > 0) AND (l_loop_exit = 0)
5880 LOOP
5881 l_progress_indicator := '110';
5882 IF (l_debug = 1) THEN
5883 inv_log_util.trace('l_rel_lpn_id : ' || l_rel_lpn_id, l_procedure_name, 9);
5884 inv_log_util.trace('l_rel_loc_id : ' || l_rel_loc_id, l_procedure_name, 9);
5885 inv_log_util.trace('l_rel_sub_code : ' || l_rel_sub_code, l_procedure_name, 9);
5886 inv_log_util.trace('l_rel_lot_num : ' || l_rel_lot_num, l_procedure_name, 9);
5887 inv_log_util.trace('l_rel_revision : ' || l_rel_revision, l_procedure_name, 9);
5888 inv_log_util.trace('l_loop_exit. before call : ' || l_loop_exit, l_procedure_name, 9);
5889 END IF;
5890
5891 IF (l_rel_lpn_id is not null) THEN
5892 l_rel_lpn_id := null;
5893 ELSIF (l_rel_loc_id is not null) THEN
5894 l_rel_loc_id := null;
5895 ELSIF (l_rel_sub_code is not null) THEN
5896 l_rel_sub_code := null;
5897 ELSIF (l_rel_lot_num is not null) THEN
5898 l_rel_lot_num := null;
5899 ELSIF (l_rel_revision is not null) THEN
5900 l_rel_revision := null;
5901 l_loop_exit := 1;
5902 inv_log_util.trace('Setting revision to null : ' || l_loop_exit, l_procedure_name, 9);
5903 END IF;
5904
5905 l_progress_indicator := '120';
5906 inv_trx_relief_c_pvt.rsv_relief
5907 (x_return_status => l_return_status
5908 , x_msg_count => l_msg_count
5909 , x_msg_data => l_msg_data
5910 , x_ship_qty => l_ship_qty --it will be the quantity relieved FROM this api
5911 , x_secondary_ship_qty => l_secondary_ship_qty --INVCONV fabdi
5912 , x_userline => l_userline
5913 , x_demand_class => l_demand_class
5914 , x_mps_flag => l_mps_flag
5915 , p_organization_id => l_org_id
5916 , p_inventory_item_id => l_item_id
5917 , p_subinv => l_rel_sub_code
5918 , p_locator => l_rel_loc_id
5919 , p_lotnumber => l_rel_lot_num
5920 , p_revision => l_rel_revision
5921 , p_dsrc_type => l_srctypeid
5922 , p_header_id => l_srcid
5923 , p_dsrc_name => l_src_code
5924 , p_dsrc_line => l_srclineid
5925 , p_dsrc_delivery => NULL --l_deliveryid bug2745896
5926 , p_qty_at_puom => ABS (l_total_prim_qty_to_relieve)
5927 , p_qty_at_suom => l_qty_at_suom -- INVCONV fabdi
5928 , p_lpn_id => l_rel_lpn_id
5929 , p_transaction_id => l_transaction_id --Bug 4764790
5930 );
5931
5932 l_total_prim_qty_to_relieve := l_total_prim_qty_to_relieve - l_ship_qty;
5933 IF (l_rel_lpn_id IS NULL AND l_rel_loc_id IS NULL AND
5934 l_rel_sub_code IS NULL AND l_rel_lot_num IS NULL AND
5935 l_rel_revision IS NULL) THEN
5936 l_loop_exit := 1;
5937 END IF;
5938
5939 inv_log_util.trace('l_loop_exit. After call ' || l_loop_exit, l_procedure_name, 9);
5940 END LOOP;
5941 l_ship_qty := l_trx_qty - l_total_prim_qty_to_relieve;
5942 END IF; -- relieve_reservations flag..
5943 /*** End R12 }} ***/
5944 END IF;
5945
5946 IF (l_debug = 1) THEN
5947 inv_log_util.TRACE ('l_return_status : ' || l_return_status
5948 , l_procedure_name
5949 , 9
5950 );
5951 inv_log_util.TRACE ('l_ship_qty : ' || l_ship_qty
5952 , l_procedure_name
5953 , 9
5954 );
5955 inv_log_util.TRACE ('l_userline : ' || l_userline
5956 , l_procedure_name
5957 , 9
5958 );
5959 inv_log_util.TRACE ('l_demand_class : ' || l_demand_class
5960 , l_procedure_name
5961 , 9
5962 );
5963 inv_log_util.TRACE ('l_mps_flag : ' || l_mps_flag
5964 , l_procedure_name
5965 , 9
5966 );
5967 inv_log_util.TRACE ('l_org_id : ' || l_org_id
5968 , l_procedure_name
5969 , 9
5970 );
5971 inv_log_util.TRACE ('l_item_id : ' || l_item_id
5972 , l_procedure_name
5973 , 9
5974 );
5975 inv_log_util.TRACE ('l_sub_code: ' || l_sub_code
5976 , l_procedure_name
5977 , 9
5978 );
5979 inv_log_util.TRACE ('l_locid : ' || l_locid, l_procedure_name, 9);
5980 inv_log_util.TRACE ('l_lotnum : ' || l_lotnum
5981 , l_procedure_name
5982 , 9
5983 );
5984 inv_log_util.TRACE ('l_rev : ' || l_rev, l_procedure_name, 9);
5985 inv_log_util.TRACE ('l_srctypeid : ' || l_srctypeid
5986 , l_procedure_name
5987 , 9
5988 );
5989 inv_log_util.TRACE ('l_header_id ' || l_srcid
5990 , l_procedure_name
5991 , 9
5992 );
5993 inv_log_util.TRACE ('l_dsrc_name : ' || l_src_code
5994 , l_procedure_name
5995 , 9
5996 );
5997 inv_log_util.TRACE ('l_dsrc_line : ' || l_srclineid
5998 , l_procedure_name
5999 , 9
6000 );
6001 inv_log_util.TRACE ('l_dsrc_delivery :' || l_deliveryid
6002 , l_procedure_name
6003 , 9
6004 );
6005 inv_log_util.TRACE ('l_dsrc_delivery :' || l_deliveryid
6006 , l_procedure_name
6007 , 9
6008 );
6009 inv_log_util.TRACE ('l_trx_qty : ' || l_trx_qty
6010 , l_procedure_name
6011 , 9
6012 );
6013 inv_log_util.TRACE ('l_lpnid : ' || l_lpnid, l_procedure_name, 9);
6014 END IF;
6015
6016 p_res_sts := l_return_status;
6017 p_res_msg := l_msg_data;
6018 p_res_count := l_msg_count;
6019
6020 IF l_return_status <> fnd_api.g_ret_sts_success THEN
6021 IF (l_debug = 1) THEN
6022 inv_log_util.TRACE ('x_msg_data = ' || l_msg_data
6023 , l_procedure_name
6024 , 9
6025 );
6026 inv_log_util.TRACE ('Before error return in TrxRsvRelief'
6027 , l_procedure_name
6028 , 9
6029 );
6030 END IF;
6031 RETURN;
6032 ELSE -- return success
6033 IF (l_debug = 1) THEN
6034 inv_log_util.TRACE ('Reservation was successfully relieved'
6035 , l_procedure_name
6036 , 9
6037 );
6038 END IF;
6039
6040 IF (ABS (l_trx_qty) <> 0) AND (l_srctypeid = salorder OR l_srctypeid = intorder)
6041 AND (l_mps_flag <> 0) THEN
6042 IF (l_debug = 1) THEN
6043 inv_log_util.TRACE ('Calling mrp_ship_order'
6044 , l_procedure_name
6045 , 9
6046 );
6047 END IF;
6048 IF (NOT mrp_ship_order (l_srclineid
6049 , l_item_id
6050 , ABS (l_trx_qty)
6051 , l_userid
6052 , l_org_id
6053 , l_userline
6054 , l_trxdate
6055 , l_demand_class
6056 )
6057 ) THEN
6058 IF (l_debug = 1) THEN
6059 inv_log_util.TRACE ('mrp_ship_order failure'
6060 , l_procedure_name
6061 , 9
6062 );
6063 END IF;
6064 p_mrp_status := 'E';
6065 RETURN;
6066 END IF; -- return success
6067
6068 IF (l_debug = 1) THEN
6069 inv_log_util.TRACE ('After mrp__order', l_procedure_name, 9);
6070 END IF;
6071 END IF; -- ABS(l_trx..)
6072
6073 END IF; -- return success
6074
6075 IF l_ship_qty <> ABS (l_trx_qty) THEN --in this case there
6076
6077 IF (l_debug = 1) THEN
6078 inv_log_util.TRACE ( 'l_PRIMARY_QUANTITY: '
6079 || l_line_rec_type.primary_quantity
6080 , l_procedure_name
6081 , 9
6082 );
6083 inv_log_util.TRACE ( 'l_transaction_action_id: '
6084 || l_line_rec_type.transaction_action_id
6085 , l_procedure_name
6086 , 9
6087 );
6088 inv_log_util.TRACE ( 'l_process_flag :'
6089 || l_line_rec_type.process_flag
6090 , l_procedure_name
6091 , 9
6092 );
6093 inv_log_util.TRACE ( 'l_shippable_flag : '
6094 || l_line_rec_type.shippable_flag
6095 , l_procedure_name
6096 , 9
6097 );
6098 END IF;
6099
6100 l_line_rec_type.inventory_item_id := l_item_id;
6101 l_line_rec_type.revision := l_rev;
6102 l_line_rec_type.organization_id := l_org_id;
6103 l_line_rec_type.subinventory_code := l_sub_code;
6104 l_line_rec_type.locator_id := l_locid;
6105 l_line_rec_type.transaction_source_type_id := l_srctypeid;
6106 l_line_rec_type.transaction_source_id := l_srcid;
6107 l_line_rec_type.transaction_source_name := l_src_code;
6108 l_line_rec_type.source_line_id := l_srclineid;
6109 l_line_rec_type.transaction_date := l_trxdate;
6110
6111 BEGIN
6112 SELECT lot_control_code,
6113 revision_qty_control_code
6114 INTO l_lctrl,
6115 l_rctrl
6116 FROM mtl_system_items_b
6117 WHERE organization_id = l_org_id
6118 AND inventory_item_id = l_item_id;
6119 EXCEPTION
6120 WHEN NO_DATA_FOUND THEN
6121 l_lctrl := 0;
6122 l_rctrl := 0;
6123 END;
6124
6125 l_progress_indicator := '1305';
6126 IF l_rctrl = 1 THEN
6127 l_revision_control := FALSE;
6128 ELSE
6129 l_revision_control := TRUE;
6130 END IF;
6131
6132 IF l_lctrl = 1 THEN
6133 l_lot_control := FALSE;
6134 ELSE
6135 l_lot_control := TRUE;
6136 END IF;
6137
6138 IF (l_line_rec_type.transaction_interface_id IS NOT NULL ) AND ( g_interface_id IS NULL OR g_interface_id <> l_line_rec_type.transaction_interface_id ) THEN
6139
6140 l_progress_indicator := '135';
6141 INV_QUANTITY_TREE_PVT.clear_quantity_cache;
6142
6143 l_progress_indicator := '1351';
6144 INV_QUANTITY_TREE_PVT.create_tree
6145 ( p_api_version_number => 1.0
6146 , p_init_msg_lst => fnd_api.g_false
6147 , x_return_status => l_return_status
6148 , x_msg_count => l_msg_count
6149 , x_msg_data => l_msg_data
6150 , p_organization_id => l_org_id
6151 , p_inventory_item_id => l_item_id
6152 , p_tree_mode => 2
6153 , p_is_revision_control => l_revision_control
6154 , p_is_lot_control => l_lot_control
6155 , p_is_serial_control => FALSE
6156 , p_include_suggestion => FALSE
6157 , p_demand_source_type_id => nvl(l_srctypeid,-9999)
6158 , p_demand_source_header_id => nvl(l_srcid,-9999)
6159 , p_demand_source_line_id => nvl(l_srclineid,-9999)
6160 , p_demand_source_name => l_src_code
6161 , p_demand_source_delivery => NULL
6162 , p_lot_expiration_date => NULL
6163 , x_tree_id => l_tree_id
6164 , p_onhand_source => 3 --g_all_subs
6165 , p_exclusive => 0 --g_non_exclusive
6166 , p_pick_release => 0 --g_pick_release_no
6167 ) ;
6168
6169 IF l_return_status = fnd_api.g_ret_sts_error THEN
6170 IF (l_debug = 1) THEN
6171 inv_log_util.trace('Error while creating tree : x_msg_data = ' || l_msg_data,l_procedure_name, 9);
6172 END IF;
6173 FND_MESSAGE.set_name('INV','INV_ERR_CREATETREE');
6174 FND_MESSAGE.set_token('ROUTINE','UE:AVAIL_TO_TRX');
6175
6176 l_error_code := FND_MESSAGE.get;
6177 l_error_exp := l_msg_data;
6178 RAISE fnd_api.g_exc_error;
6179 END IF ;
6180
6181 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
6182 IF (l_debug = 1) THEN
6183 inv_log_util.trace('Unexpected Error while creating tree : ' || l_msg_data,l_procedure_name, 9);
6184 END IF;
6185 l_error_exp := l_msg_data;
6186 RAISE fnd_api.g_exc_unexpected_error;
6187 END IF;
6188
6189 g_interface_id := l_line_rec_type.transaction_interface_id;
6190 tree_exists := TRUE;
6191 g_tree_id := l_tree_id;
6192 IF (l_debug = 1) THEN
6193 inv_log_util.trace('Tree id is '||g_tree_id, l_procedure_name, 9);
6194 END IF;
6195
6196 END IF; /* interface id has changed */
6197 --qty-tree validation
6198 IF ((NOT post_temp_validation (l_line_rec_type
6199 , 1 --always validate it
6200 , l_userid
6201 , inv_txn_manager_grp.gi_flow_schedule
6202 , l_lotnum -- Added for 4377625
6203 )
6204 )
6205 )
6206 THEN
6207
6208 l_error_code := fnd_message.get;
6209
6210 UPDATE mtl_transactions_interface
6211 SET last_update_date = SYSDATE
6212 , last_updated_by = l_userid
6213 , last_update_login = l_loginid
6214 , program_update_date = SYSDATE
6215 , process_flag = 3
6216 , lock_flag = 2
6217 , ERROR_CODE = SUBSTRB (l_error_code, 1, 240)
6218 , error_explanation = SUBSTRB (l_error_exp, 1, 240)
6219 --WHERE ROWID = l_Line_rec_type.rowid
6220 WHERE transaction_interface_id = p_transaction_temp_id
6221 AND process_flag = 1
6222 AND organization_id = l_org_id
6223 AND inventory_item_id = l_item_id
6224 AND NVL (subinventory_code, '@@@@') = NVL (l_sub_code, '@@@@');
6225
6226 UPDATE mtl_transactions_interface
6227 SET last_update_date = SYSDATE
6228 , last_updated_by = l_userid
6229 , last_update_login = l_loginid
6230 , program_update_date = SYSDATE
6231 , process_flag = 3
6232 , lock_flag = 2
6233 , ERROR_CODE = SUBSTRB (l_error_code, 1, 240)
6234 --WHERE TRANSACTION_HEADER_ID = l_header_id
6235 WHERE transaction_interface_id = p_transaction_temp_id
6236 AND process_flag = 1;
6237
6238 IF (l_debug = 1)
6239 THEN
6240 inv_log_util.TRACE
6241 ('After Error in post_temp_validation continue...'
6242 , l_procedure_name
6243 , 9
6244 );
6245 END IF;
6246
6247 RAISE fnd_api.g_exc_error;
6248 END IF;
6249 END IF;
6250 END LOOP;
6251
6252 CLOSE c1;
6253 l_progress_indicator := '180';
6254 IF (tree_exists) THEN
6255 l_progress_indicator := '190';
6256 INV_QUANTITY_TREE_PVT.free_All
6257 ( p_api_version_number => 1.0
6258 , p_init_msg_lst => fnd_api.g_false
6259 , x_return_status => l_return_status
6260 , x_msg_count => l_msg_count
6261 , x_msg_data => l_msg_data);
6262 END IF;
6263 EXCEPTION
6264 WHEN OTHERS THEN
6265 inv_log_util.TRACE ( '***Undef Error Ex..rel_res : '
6266 || SUBSTR (SQLERRM, 1, 200)
6267 , l_procedure_name
6268 , '9'
6269 );
6270 inv_log_util.TRACE ( 'When others Ex..rel_reservations_mrp_update '
6271 || l_error_code
6272 , l_procedure_name
6273 , '1'
6274 );
6275 p_res_sts := 'E';
6276 p_mrp_status := 'E';
6277 END rel_reservations_mrp_update;
6278 END inv_txn_manager_pub;