[Home] [Help]
PACKAGE BODY: APPS.INV_TXN_MANAGER_PUB
Source
1 PACKAGE BODY inv_txn_manager_pub AS
2 /* $Header: INVTXMGB.pls 120.33.12020000.3 2013/03/06 11:20:01 skommine 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 l_max_batch_id NUMBER; --Bug 13097146
1972 /*Patchset J:Interface Trip Stop Enhancements*/
1973 l_wip_entity_type NUMBER ; /* Pawan Added for gme- convergence*/
1974
1975 /*Bug#16229872 */
1976 l_trxuom VARCHAR2(3);
1977 l_priuom VARCHAR2(3);
1978 l_txn_qty NUMBER;
1979
1980 /*Bug 5209598.Added the following fields to perform ATT/ATR checks. */
1981 l_tree_id NUMBER;
1982 l_msg_count NUMBER;
1983 l_temp_rowid VARCHAR2(21);
1984 l_srclineid VARCHAR2(40);
1985 l_trxdate DATE;
1986 l_neg_inv_rcpt number;
1987 l_revision_control BOOLEAN;
1988 l_lot_control BOOLEAN;
1989 l_dem_hdr_id NUMBER ;
1990 l_dem_line_id NUMBER;
1991 l_translate BOOLEAN := TRUE;
1992
1993 l_qoh NUMBER;
1994 l_rqoh NUMBER;
1995 l_pqoh NUMBER;
1996 l_qr NUMBER;
1997 l_qs NUMBER;
1998 l_att NUMBER;
1999 l_atr NUMBER;
2000
2001 l_override_neg_for_backflush NUMBER := 0;
2002 l_override_rsv_for_backflush NUMBER := 2;
2003 l_item_qoh NUMBER;
2004 l_item_rqoh NUMBER;
2005 l_item_pqoh NUMBER;
2006 l_item_qr NUMBER;
2007 l_item_qs NUMBER;
2008 l_item_att NUMBER;
2009 l_item_atr NUMBER;
2010
2011 l_current_batch_failed BOOLEAN := FALSE;
2012 l_current_err_batch_id NUMBER;
2013 l_count_success NUMBER:=0;
2014
2015 /*Bug:5276191. Added the following two variables. */
2016 l_from_project_id NUMBER := null;
2017 l_serial_control NUMBER := 1;
2018
2019 /*Bug 5209598.End of variable declaration. */
2020
2021 --bug#13527319
2022 l_operation_seq_num number;
2023 l_wip_supply_type number;
2024
2025 CURSOR aa1
2026 IS
2027 SELECT transaction_interface_id
2028 , transaction_header_id
2029 , request_id
2030 , inventory_item_id
2031 , organization_id
2032 , subinventory_code
2033 , transfer_organization
2034 , transfer_subinventory
2035 , transaction_uom
2036 , transaction_date
2037 , transaction_quantity
2038 , locator_id
2039 , transfer_locator
2040 , transaction_source_id
2041 , transaction_source_type_id
2042 , transaction_action_id
2043 , transaction_type_id
2044 , distribution_account_id
2045 , NVL (shippable_flag, 'Y')
2046 , ROWID
2047 , new_average_cost
2048 , value_change
2049 , percentage_change
2050 , material_account
2051 , material_overhead_account
2052 , resource_account
2053 , outside_processing_account
2054 , overhead_account
2055 , requisition_line_id
2056 , overcompletion_transaction_qty
2057 , /* Overcompletion Transactions */
2058 end_item_unit_number
2059 , scheduled_payback_date
2060 , /* Borrow Payback */
2061 revision
2062 , /* Borrow Payback */
2063 org_cost_group_id
2064 , /* PCST */
2065 cost_type_id
2066 , /* PCST */
2067 primary_quantity
2068 , source_line_id
2069 , process_flag
2070 , transaction_source_name
2071 , trx_source_delivery_id
2072 , trx_source_line_id
2073 , parent_id
2074 , transaction_batch_id
2075 , transaction_batch_seq
2076 ,
2077 -- INVCONV start fabdi
2078 secondary_transaction_quantity
2079 , secondary_uom_code
2080 -- INVCONV end fabdi
2081 , ship_to_location_id --eIB Build; Bug# 4348541
2082 , transfer_price -- OPM INVCONV umoogala Process-Discrete Transfers
2083 , WIP_ENTITY_TYPE -- Pawan 11th july added
2084 /* Bug:5392366. Added the following two columns.*/
2085 , completion_transaction_id
2086 , move_transaction_id
2087 FROM mtl_transactions_interface
2088 WHERE transaction_header_id = p_header_id AND process_flag = 1
2089 ORDER BY transaction_batch_id
2090 , transaction_batch_seq
2091 , organization_id
2092 , inventory_item_id
2093 , revision
2094 , subinventory_code
2095 , locator_id;
2096
2097 /*Bug 5209598. Added the following cursor. */
2098 CURSOR Z1 (p_flow_sch NUMBER,p_line_rec_type inv_txn_manager_pub.line_rec_type) IS
2099 SELECT
2100 p_line_rec_type.ROWID,
2101 p_line_rec_type.INVENTORY_ITEM_ID,
2102 p_line_rec_type.REVISION,
2103 p_line_rec_type.ORGANIZATION_ID,
2104 p_line_rec_type.SUBINVENTORY_CODE,
2105 p_line_rec_type.LOCATOR_ID,
2106 ABS(p_line_rec_type.TRANSACTION_QUANTITY) TRANSACTION_QUANTITY,
2107 ABS(p_line_rec_type.PRIMARY_QUANTITY) PRIMARY_QUANTITY, --Bug#16229872
2108 p_line_rec_type.TRANSACTION_UOM, --Bug#16229872
2109 MSI.PRIMARY_UOM_CODE, --Bug#16229872
2110 NULL LOT_NUMBER,
2111 p_line_rec_type.TRANSACTION_SOURCE_TYPE_ID,
2112 p_line_rec_type.TRANSACTION_ACTION_ID,
2113 p_line_rec_type.TRANSACTION_SOURCE_ID,
2114 p_line_rec_type.TRANSACTION_SOURCE_NAME,
2115 --Jalaj Srivastava 5010595
2116 --for GME (wip_enity_type=10) select trx_source_line_id as source_line_id
2117 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,
2118 MSI.REVISION_QTY_CONTROL_CODE,
2119 decode(p_line_rec_type.transaction_source_type_id,5,1,MSI.lot_control_code) lot_control_code,--j-dev
2120 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,
2121 p_line_rec_type.TRANSFER_LOCATOR,
2122 p_line_rec_type.transaction_date,
2123 MP.NEGATIVE_INV_RECEIPT_CODE
2124 FROM MTL_PARAMETERS MP,
2125 MTL_SYSTEM_ITEMS MSI
2126 WHERE MP.ORGANIZATION_ID = p_line_rec_type.ORGANIZATION_ID
2127 -- AND MP.NEGATIVE_INV_RECEIPT_CODE = 2 'bug 3679189'
2128 AND p_line_rec_type.PROCESS_FLAG = 1
2129 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
2130 AND ( ( (p_line_rec_type.wip_entity_type <> 10)
2131 AND ( (p_flow_sch <> 1
2132 AND p_line_rec_type.TRANSACTION_ACTION_ID IN (1,2,3,21,32,34,5) )
2133 OR (p_flow_sch = 1
2134 AND p_line_rec_type.TRANSACTION_ACTION_ID IN (1, 32) )
2135 )
2136 )
2137 --Jalaj Srivastava 5232394
2138 --select all transactions for GME
2139 OR (p_line_rec_type.wip_entity_type = 10)
2140 )
2141 AND MSI.ORGANIZATION_ID = MP.ORGANIZATION_ID
2142 AND MSI.ORGANIZATION_ID = p_line_rec_type.ORGANIZATION_ID
2143 AND MSI.INVENTORY_ITEM_ID = p_line_rec_type.INVENTORY_ITEM_ID
2144 UNION
2145 SELECT
2146 p_line_rec_type.ROWID,
2147 p_line_rec_type.INVENTORY_ITEM_ID,
2148 p_line_rec_type.REVISION,
2149 p_line_rec_type.ORGANIZATION_ID,
2150 p_line_rec_type.SUBINVENTORY_CODE,
2151 p_line_rec_type.LOCATOR_ID,
2152 ABS(MTLI.TRANSACTION_QUANTITY) TRANSACTION_QUANTITY,
2153 ABS(MTLI.PRIMARY_QUANTITY) PRIMARY_QUANTITY, --Bug#16229872
2154 p_line_rec_type.TRANSACTION_UOM, --Bug#16229872
2155 MSI.PRIMARY_UOM_CODE, --Bug#16229872
2156 MTLI.lot_number LOT_NUMBER,
2157 p_line_rec_type.TRANSACTION_SOURCE_TYPE_ID,
2158 p_line_rec_type.TRANSACTION_ACTION_ID,
2159 p_line_rec_type.TRANSACTION_SOURCE_ID,
2160 p_line_rec_type.TRANSACTION_SOURCE_NAME,
2161 --Jalaj Srivastava 5010595
2162 --for GME (wip_enity_type=10) select trx_source_line_id as source_line_id
2163 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,
2164 MSI.REVISION_QTY_CONTROL_CODE,
2165 MSI.lot_control_code lot_control_code,
2166 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,
2167 p_line_rec_type.TRANSFER_LOCATOR,
2168 p_line_rec_type.transaction_date,
2169 MP.NEGATIVE_INV_RECEIPT_CODE
2170 FROM MTL_TRANSACTION_LOTS_INTERFACE MTLI,
2171 MTL_PARAMETERS MP,
2172 MTL_SYSTEM_ITEMS MSI
2173 WHERE MP.ORGANIZATION_ID = p_line_rec_type.ORGANIZATION_ID
2174 --AND MP.NEGATIVE_INV_RECEIPT_CODE = 2 'bug 3679189'
2175 AND MTLI.TRANSACTION_INTERFACE_ID = p_line_rec_type.TRANSACTION_INTERFACE_ID
2176 AND p_line_rec_type.PROCESS_FLAG = 1
2177 AND MSI.LOT_CONTROL_CODE = 2
2178 AND ( ( (p_line_rec_type.wip_entity_type <> 10)
2179 AND ( (p_flow_sch <> 1
2180 AND p_line_rec_type.TRANSACTION_ACTION_ID IN (1,2,3,21,32,34,5) )
2181 OR (p_flow_sch = 1
2182 AND p_line_rec_type.TRANSACTION_ACTION_ID = 32 )
2183 )
2184 )
2185 --Jalaj Srivastava 5232394
2186 --select all transactions for GME
2187 OR (p_line_rec_type.wip_entity_type = 10)
2188 )
2189 AND MSI.ORGANIZATION_ID = MP.ORGANIZATION_ID
2190 AND MSI.ORGANIZATION_ID = p_line_rec_type.ORGANIZATION_ID
2191 AND MSI.INVENTORY_ITEM_ID = p_line_rec_type.INVENTORY_ITEM_ID
2192 -- Pawan 11th july added this for validation of lot for GME only
2193 AND ((p_line_rec_type.transaction_source_type_id <> 5) OR
2194 (p_line_rec_type.transaction_source_type_id = 5 AND
2195 p_line_rec_type.wip_entity_type = 10 ));--J-dev verify
2196
2197 /*Bug:5276191. Added the following cursor. */
2198 CURSOR c_mmtt IS
2199 SELECT *
2200 FROM mtl_material_transactions_temp
2201 WHERE transaction_header_id = p_header_id
2202 AND NVL(transaction_status, 1) <> 2 -- don't consider suggestions
2203 AND process_flag = 'Y'
2204 ORDER BY transaction_batch_id;
2205
2206 l_index NUMBER := 0;
2207 l_previous_parent_id NUMBER := 0;
2208 l_validation_status VARCHAR2 (1) := 'Y';
2209
2210 l_rsv_wip_entity_type NUMBER := NULL; -- Bug 6454464
2211 l_rsv_wip_job_type VARCHAR2(15); -- Bug 6454464
2212
2213 BEGIN
2214 l_debug := NVL (fnd_profile.VALUE ('INV_DEBUG_TRACE'), 0);
2215 l_header_id := p_header_id;
2216
2217 --dbms_output.put_line(' came to process_trx');
2218 IF (l_debug = 1)
2219 THEN
2220 inv_log_util.TRACE
2221 ( '-----Inside process_Transactions-------.trxhdr='
2222 || p_header_id
2223 , 'INV_TXN_MANAGER_PUB'
2224 , 9
2225 );
2226
2227 END IF;
2228
2229 /* FND_MESSAGE.SET_NAME('INV', 'BAD_INPUT_ARGUMENTS');
2230 FND_MSG_PUB.ADD;
2231 RAISE FND_API.G_EXC_ERROR; */
2232
2233 /*----------------------------------------------------------+
2234 | retrieving information
2235 +----------------------------------------------------------*/
2236 poget ('LOGIN_ID', l_loginid);
2237 poget ('USER_ID', l_userid);
2238 poget ('CONC_PROGRAM_ID', l_progid);
2239 poget ('CONC_REQUEST_ID', l_reqstid);
2240 poget ('PROG_APPL_ID', l_applid);
2241
2242 IF l_loginid IS NULL
2243 THEN
2244 l_loginid := -1;
2245 END IF;
2246
2247 IF l_userid IS NULL
2248 THEN
2249 l_userid := -1;
2250 END IF;
2251
2252 /*l_loginid := 1068;
2253 l_userid := 1068;
2254 l_progid := 32321;
2255 l_reqstid := null;
2256 l_applid := 401;*/
2257 x_return_status := fnd_api.g_ret_sts_error;
2258 x_msg_count := 0;
2259 x_msg_data := '';
2260 x_trans_count := 0;
2261
2262 -- Bug 3339212. We were rolling back everything if
2263 --there is an error in process transactions. This leads o erasing all
2264 -- the save point set, which would result in cannot establishing save points
2265 -- which could have been set by other teams calling our API. So, we
2266 -- would rollback to this point if anything fails in process
2267 --transactions.
2268 -- Bug 3686000: The savepoint to be established only when the caller calls
2269 -- this API with p_commit as false. Otherwise, during an exception, we
2270 -- will not find the save point as we would have committed if p_commit
2271 -- has been set to true in downstream processing.
2272 IF NOT fnd_api.to_boolean (p_commit)
2273 THEN
2274 SAVEPOINT process_transactions_svpt;
2275 END IF;
2276
2277 --fnd_global.apps_initialize(1003593, 53466, 385);
2278 IF (p_table = 2)
2279 THEN
2280 /** Process Rows in MTL_MATERIAL_TRANSACTION_TEMP **/
2281 IF (l_debug = 1)
2282 THEN
2283 inv_log_util.TRACE ('Process Rows in MTL_MATERIAL_TRANSACTION_TEMP'
2284 , 'INV_TXN_MANAGER_PUB'
2285 , 9
2286 );
2287 END IF;
2288
2289 UPDATE mtl_material_transactions_temp
2290 SET last_update_date = SYSDATE
2291 , transaction_temp_id =
2292 NVL (transaction_temp_id, mtl_material_transactions_s.NEXTVAL)
2293 , last_updated_by = l_userid
2294 , last_update_login = l_loginid
2295 , program_application_id = l_applid
2296 , program_id = l_progid
2297 , request_id = l_reqstid
2298 , program_update_date = SYSDATE
2299 , ERROR_CODE = NULL
2300 , error_explanation = NULL
2301 WHERE process_flag = 'Y'
2302 AND NVL (transaction_status, ts_default) <> ts_save_only /* 2STEP */
2303 AND transaction_header_id = l_header_id;
2304
2305 --Bug 4586255, support 6 decimals for wip
2306 UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP
2307 SET PRIMARY_QUANTITY = ROUND(PRIMARY_QUANTITY,5),
2308 TRANSACTION_QUANTITY = ROUND(TRANSACTION_QUANTITY,5)
2309 WHERE PROCESS_FLAG = 'Y'
2310 AND NVL(TRANSACTION_STATUS,TS_DEFAULT) <> TS_SAVE_ONLY /* 2STEP */
2311 AND TRANSACTION_HEADER_ID = l_header_id
2312 AND transaction_source_type_id <> 5;
2313
2314 UPDATE mtl_transaction_lots_temp
2315 SET PRIMARY_QUANTITY = ROUND(PRIMARY_QUANTITY,5),
2316 TRANSACTION_QUANTITY = ROUND(TRANSACTION_QUANTITY,5)
2317 WHERE transaction_temp_id
2318 IN ( SELECT transaction_temp_id
2319 FROM mtl_material_transactions_temp
2320 WHERE PROCESS_FLAG = 'Y'
2321 AND NVL(TRANSACTION_STATUS,TS_DEFAULT) <> TS_SAVE_ONLY /* 2STEP */
2322 AND TRANSACTION_HEADER_ID = l_header_id
2323 AND transaction_source_type_id <> 5);
2324
2325
2326 UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP
2327 SET PRIMARY_QUANTITY = ROUND(PRIMARY_QUANTITY,6),
2328 TRANSACTION_QUANTITY = ROUND(TRANSACTION_QUANTITY,6)
2329 WHERE PROCESS_FLAG = 'Y'
2330 AND NVL(TRANSACTION_STATUS,TS_DEFAULT) <> TS_SAVE_ONLY /* 2STEP */
2331 AND TRANSACTION_HEADER_ID = l_header_id
2332 AND transaction_source_type_id = 5;
2333
2334 UPDATE mtl_transaction_lots_temp
2335 SET PRIMARY_QUANTITY = ROUND(PRIMARY_QUANTITY,6),
2336 TRANSACTION_QUANTITY = ROUND(TRANSACTION_QUANTITY,6)
2337 WHERE transaction_temp_id
2338 IN( SELECT transaction_temp_id
2339 FROM mtl_material_transactions_temp
2340 WHERE PROCESS_FLAG = 'Y'
2341 AND NVL(TRANSACTION_STATUS,TS_DEFAULT) <> TS_SAVE_ONLY /* 2STEP */
2342 AND TRANSACTION_HEADER_ID = l_header_id
2343 AND transaction_source_type_id = 5);
2344
2345 IF (l_debug = 1)
2346 THEN
2347 inv_log_util.TRACE ('Rows in MMTT ready to process '
2348 , 'INV_TXN_MANAGER_PUB'
2349 , 9
2350 );
2351 END IF;
2352
2353 SELECT COUNT (1)
2354 INTO l_process
2355 FROM mtl_material_transactions_temp
2356 WHERE transaction_header_id = l_header_id
2357 AND process_flag = 'Y'
2358 AND transaction_status = 3 /* not able to use the TS_PROCESS macro */
2359 AND ROWNUM < 2;
2360
2361 --the assumption is that default txns are
2362 --never mixed up with the 2level txns. so
2363 -- we can avoid temp validation call if there
2364 --are no rows with transaction_status = TS_PROCESS
2365 IF l_process = 1
2366 THEN
2367 IF (l_debug = 1)
2368 THEN
2369 inv_log_util.TRACE ('Calling INV_PROCESS_TEMP.processTransaction'
2370 , 'INV_TXN_MANAGER_PUB'
2371 , 9
2372 );
2373 END IF;
2374
2375 l_result :=
2376 inv_process_temp.processtransaction (l_header_id
2377 , inv_process_temp.FULL
2378 , inv_process_temp.ignore_all
2379 );
2380 END IF;
2381
2382 SELECT COUNT (*)
2383 INTO l_totrows
2384 FROM mtl_material_transactions_temp
2385 WHERE transaction_header_id = l_header_id
2386 AND process_flag = 'Y'
2387 AND NVL (transaction_status, ts_default) <> ts_save_only; /* 2STEP */
2388
2389 l_midtotrows := l_totrows;
2390 l_initotrows := l_totrows;
2391 x_trans_count := l_totrows;
2392
2393 IF (l_totrows = 0)
2394 THEN
2395 IF fnd_api.to_boolean (p_commit)
2396 THEN
2397 COMMIT WORK;
2398 END IF;
2399
2400 fnd_message.set_name ('INV', 'INV_PROC_WARN');
2401 l_disp := fnd_message.get;
2402
2403 IF (l_debug = 1)
2404 THEN
2405 inv_log_util.TRACE (l_disp, 'INV_TXN_MANAGER_PUB', 9);
2406 END IF;
2407
2408 RETURN -1;
2409 END IF;
2410
2411 /*+-----------------------------------------------------------------+
2412 | Check if we are processing WIP transactions to determine which |
2413 | to invoke to process transactions |
2414 +-----------------------------------------------------------------+*/
2415 -- Pawan added wip_entity_type for gme_inventory convergence
2416 SELECT transaction_source_type_id, wip_entity_type
2417 INTO l_srctypeid, l_wip_entity_type
2418 FROM mtl_material_transactions_temp
2419 WHERE transaction_header_id = l_header_id AND ROWNUM < 2;
2420
2421 done := FALSE;
2422 FIRST := TRUE;
2423
2424 WHILE (NOT done)
2425 LOOP
2426 IF (FIRST)
2427 THEN
2428 IF (l_debug = 1)
2429 THEN
2430 inv_log_util.TRACE ('Calling Process_lpn_trx'
2431 , 'INV_TXN_MANAGER_PUB'
2432 , 9
2433 );
2434 END IF;
2435
2436 fnd_message.set_name ('INV', 'INV_CALL_PROC');
2437 fnd_message.set_token ('token1', l_header_id);
2438 fnd_message.set_token ('token2', l_totrows);
2439 l_disp := fnd_message.get;
2440
2441 IF (l_debug = 1)
2442 THEN
2443 inv_log_util.TRACE (l_disp, 'INV_TXN_MANAGER_PUB', 9);
2444 END IF;
2445 END IF;
2446
2447 -- If transactions are of type WIP, then call the WIP API. This
2448 -- API does the WIP pre-processing before calling
2449 --process_lpn_trx
2450 /** WIP J dev condition. Add another condtion in the if
2451 /* statement below. if WIP.J is not installed call
2452 /* wip_mtlTempProc_grp()...else call process_lpn_trx()*/
2453 IF ( l_srctypeid = 5
2454 AND wip_constants.dmf_patchset_level <
2455 wip_constants.dmf_patchset_j_value
2456 )
2457 THEN
2458 wip_mtltempproc_grp.processtemp
2459 (p_initmsglist => fnd_api.g_false
2460 , p_processinv => fnd_api.g_true
2461 , -- call INV TM after WIP logic
2462 p_txnhdrid => l_header_id
2463 , x_returnstatus => l_return_status
2464 , x_errormsg => l_msg_data
2465 );
2466
2467 IF (l_return_status <> fnd_api.g_ret_sts_success)
2468 THEN
2469 IF (l_debug = 1)
2470 THEN
2471 inv_log_util.TRACE ('Failure from MMTT:WIP processTemp!!'
2472 , 'INV_TXN_MANAGER_PUB'
2473 , 1
2474 );
2475 END IF;
2476
2477 l_result := -1;
2478 END IF;
2479 ELSE
2480 l_result :=
2481 inv_lpn_trx_pub.process_lpn_trx (p_trx_hdr_id => l_header_id
2482 , p_commit => p_commit
2483 , x_proc_msg => l_msg_data
2484 , p_proc_mode => 1
2485 , p_process_trx => fnd_api.g_true
2486 , p_atomic => fnd_api.g_false
2487 );
2488 END IF;
2489
2490 IF (l_result <> 0)
2491 THEN
2492 fnd_message.set_name ('INV', 'INV_INT_PROCCODE');
2493
2494 IF (l_debug = 1)
2495 THEN
2496 inv_log_util.TRACE ('Error from PROCESS_LPN_TRX.. ' || l_msg_data
2497 , 'INV_TXN_MANAGER_PUB'
2498 , 9
2499 );
2500 END IF;
2501
2502 l_error_exp := l_msg_data;
2503 x_msg_data := l_msg_data;
2504 x_return_status := l_return_status;
2505
2506 /* No need to update MMTT after returning from process_lpn_trx as this has already
2507 been done within the Java code. - Bug 2284667 */
2508 IF fnd_api.to_boolean (p_commit)
2509 THEN
2510 COMMIT WORK;
2511 END IF;
2512
2513 RETURN -1;
2514 END IF;
2515
2516 IF (l_debug = 1)
2517 THEN
2518 inv_log_util.TRACE ('After process_lpn_trx without errors'
2519 , 'INV_TXN_MANAGER_PUB'
2520 , 9
2521 );
2522 END IF;
2523
2524 IF (FIRST)
2525 THEN
2526 fnd_message.set_name ('INV', 'INV_RETURN_PROC');
2527 l_disp := fnd_message.get;
2528
2529 IF (l_debug = 1)
2530 THEN
2531 inv_log_util.TRACE (l_disp, 'INV_TXN_MANAGER_PUB', 9);
2532 END IF;
2533 END IF;
2534
2535 IF fnd_api.to_boolean (p_commit)
2536 THEN
2537 COMMIT WORK;
2538 END IF;
2539
2540 IF (FIRST)
2541 THEN
2542 IF (NOT bflushchk (l_header_id))
2543 THEN
2544 l_error_exp := fnd_message.get;
2545
2546 IF (l_debug = 1)
2547 THEN
2548 inv_log_util.TRACE ('Error in call to bflushchk'
2549 , 'INV_TXN_MANAGER_PUB'
2550 , 9
2551 );
2552 END IF;
2553
2554 --ROLLBACK WORK;
2555 RETURN -1;
2556 END IF;
2557
2558 IF (l_header_id <> -1)
2559 THEN
2560 fnd_message.set_name ('INV', 'INV_BFLUSH_PROC');
2561 l_disp := fnd_message.get;
2562
2563 IF (l_debug = 1)
2564 THEN
2565 inv_log_util.TRACE (l_disp, 'INV_TXN_MANAGER_PUB', 9);
2566 END IF;
2567
2568 SELECT COUNT (*)
2569 INTO l_totrows
2570 FROM mtl_material_transactions_temp
2571 WHERE transaction_header_id = l_header_id AND process_flag = 'Y';
2572
2573 IF (l_totrows > 200)
2574 THEN
2575 UPDATE mtl_material_transactions_temp
2576 SET transaction_header_id = (-1) * l_header_id
2577 WHERE transaction_header_id = l_header_id
2578 AND process_flag = 'Y';
2579
2580 UPDATE mtl_material_transactions_temp
2581 SET transaction_header_id = ABS (l_header_id)
2582 WHERE transaction_header_id = (-1) * (l_header_id)
2583 AND process_flag = 'Y'
2584 AND ROWNUM < 201;
2585 END IF;
2586
2587 fnd_message.set_name ('INV', 'INV_CALL_PROC');
2588 fnd_message.set_token ('token1', l_header_id);
2589 fnd_message.set_token ('token2', l_totrows);
2590 l_disp := fnd_message.get;
2591
2592 IF (l_debug = 1)
2593 THEN
2594 inv_log_util.TRACE (l_disp, 'INV_TXN_MANAGER_PUB', 9);
2595 END IF;
2596 ELSE
2597 done := TRUE;
2598 END IF;
2599
2600 FIRST := FALSE;
2601 ELSE
2602 UPDATE mtl_material_transactions_temp
2603 SET transaction_header_id = ABS (l_header_id)
2604 WHERE transaction_header_id = (-1) * (l_header_id)
2605 AND process_flag = 'Y'
2606 AND ROWNUM < 201;
2607
2608 IF SQL%NOTFOUND
2609 THEN
2610 fnd_message.set_name ('INV', 'INV_RETURN_PROC');
2611 done := TRUE;
2612 END IF;
2613 END IF;
2614 END LOOP;
2615 ELSE
2616
2617 /** Table = 1 - MTL_TRANSACTIONS_INTERFACE **/
2618 /** Table = 1 - MTL_TRANSACTIONS_INTERFACE **/
2619 /*Patchset J:Trip Stop Interface Enhancements:setting the
2620 /*transaction batch id for Shipping transactions depending
2621 /*on the profile INV:Batch Size*/
2622 /*Bug 3947667, enabling it irrespective of patchset */
2623
2624 -- IF (INV_CONTROL.G_CURRENT_RELEASE_LEVEL >= INV_RELEASE.G_J_RELEASE_LEVEL) THEN
2625 BEGIN
2626 l_batch_size := NVL (fnd_profile.VALUE ('INV_BATCH_SIZE'), 0);
2627 EXCEPTION
2628 WHEN VALUE_ERROR
2629 THEN
2630 l_batch_size := 0;
2631 inv_log_util.TRACE
2632 ('Inv Batch size set to null for non numeric value'
2633 , 'INV_TXN_MANAGER_PUB'
2634 , 9
2635 );
2636 END;
2637
2638 IF (l_debug = 1)
2639 THEN
2640 inv_log_util.TRACE ('Inv Batch size:' || l_batch_size
2641 , 'INV_TXN_MANAGER_PUB'
2642 , 9
2643 );
2644 END IF;
2645
2646 --Bug 13097146, limiting this batch_id update only for 2,16 only
2647 UPDATE mtl_transactions_interface
2648 SET last_update_date = SYSDATE
2649 , transaction_interface_id =
2650 NVL (transaction_interface_id
2651 , mtl_material_transactions_s.NEXTVAL
2652 )
2653 , transaction_batch_id =
2654 NVL (transaction_batch_id
2655 , DECODE (transaction_source_type_id
2656 , 2, DECODE (l_batch_size
2657 , 0, transaction_batch_id
2658 , CEIL (ROWNUM / l_batch_size)
2659 )
2660 , 16, DECODE (l_batch_size
2661 , 0, transaction_batch_id
2662 , CEIL (ROWNUM / l_batch_size)
2663 )
2664 , transaction_batch_id
2665 )
2666 )
2667 , last_updated_by = l_userid
2668 , last_update_login = l_loginid
2669 , program_application_id = l_applid
2670 , program_id = l_progid
2671 , request_id = l_reqstid
2672 , program_update_date = SYSDATE
2673 , lock_flag = 1
2674 WHERE process_flag = 1 AND transaction_header_id = l_header_id;
2675
2676 SELECT max(transaction_batch_id)
2677 INTO l_max_batch_id
2678 FROM mtl_transactions_interface
2679 WHERE process_flag = 1 AND transaction_header_id = l_header_id;
2680
2681 -- Bug 13097146, stamping batch_id separately for ISO shipments
2682 UPDATE mtl_transactions_interface
2683 SET last_update_date = SYSDATE
2684 , transaction_interface_id =
2685 NVL (transaction_interface_id
2686 , mtl_material_transactions_s.NEXTVAL
2687 )
2688 , transaction_batch_id =
2689 NVL (transaction_batch_id
2690 , DECODE (transaction_source_type_id
2691 , 8, DECODE (l_batch_size
2692 , 0, transaction_batch_id
2693 , nvl(l_max_batch_id,0) + CEIL (ROWNUM / l_batch_size)
2694 )
2695 , transaction_batch_id
2696 )
2697 )
2698 , last_updated_by = l_userid
2699 , last_update_login = l_loginid
2700 , program_application_id = l_applid
2701 , program_id = l_progid
2702 , request_id = l_reqstid
2703 , program_update_date = SYSDATE
2704 , lock_flag = 1
2705 WHERE process_flag = 1 AND transaction_header_id = l_header_id;
2706
2707 /* ELSE
2708
2709 UPDATE MTL_TRANSACTIONS_INTERFACE
2710 SET LAST_UPDATE_DATE = SYSDATE,
2711 TRANSACTION_INTERFACE_ID = NVL(TRANSACTION_INTERFACE_ID,
2712 mtl_material_transactions_s.nextval),
2713 LAST_UPDATED_BY = l_userid,
2714 LAST_UPDATE_LOGIN = l_loginid,
2715 PROGRAM_APPLICATION_ID = l_applid,
2716 PROGRAM_ID = l_progid,
2717 REQUEST_ID = l_reqstid,
2718 PROGRAM_UPDATE_DATE = SYSDATE,
2719 LOCK_FLAG = 1
2720 WHERE PROCESS_FLAG = 1
2721 AND TRANSACTION_HEADER_ID = l_header_id;
2722 END IF; */
2723 l_initotrows := SQL%ROWCOUNT;
2724
2725 IF fnd_api.to_boolean (p_commit)
2726 THEN
2727 COMMIT WORK;
2728 END IF;
2729
2730 IF (l_debug = 1)
2731 THEN
2732 inv_log_util.TRACE ('MTI Rows cnt before Validation=' || l_initotrows
2733 , 'INV_TXN_MANAGER_PUB'
2734 , 9
2735 );
2736 END IF;
2737
2738 IF (l_totrows = 0)
2739 THEN
2740 fnd_message.set_name ('INV', 'INV_PROC_WARN');
2741 l_disp := fnd_message.get;
2742
2743 IF (l_debug = 1)
2744 THEN
2745 inv_log_util.TRACE (l_disp || ' totrows = 0'
2746 , 'INV_TXN_MANAGER_PUB'
2747 , 9
2748 );
2749 END IF;
2750
2751 RETURN -1;
2752 END IF;
2753
2754 /*+-----------------------------------------------------------------+
2755 | Check if we are processing WIP transactions to determine whether|
2756 | to do the derivation for flow_schedule. |
2757 +-----------------------------------------------------------------+*/
2758 -- Pawan Added the wip_entity_type
2759 SELECT NVL (validation_required, 1)
2760 , mtt.transaction_source_type_id, mti.wip_entity_type
2761 INTO l_valreq
2762 , l_srctypeid, l_wip_entity_type
2763 FROM mtl_transactions_interface mti, mtl_transaction_types mtt
2764 WHERE transaction_header_id = l_header_id
2765 AND mtt.transaction_type_id = mti.transaction_type_id
2766 AND ROWNUM < 2;
2767
2768 /*+--------------------------------------------------------------+
2769 | The global INV_TXN_MANAGER_GRP.gi_flow_schedule will be '1' (or true) for |
2770 | WIP flow schedules ONLY. |
2771 +--------------------------------------------------------------+ */
2772 IF (l_srctypeid = 5)
2773 THEN
2774 BEGIN
2775 SELECT DECODE (UPPER (flow_schedule), 'Y', 1, 0)
2776 INTO inv_txn_manager_grp.gi_flow_schedule
2777 FROM mtl_transactions_interface
2778 WHERE transaction_header_id = l_header_id
2779 AND transaction_source_type_id = 5
2780 AND transaction_action_id IN
2781 (30, 31, 32) --CFM Scrap Transactions
2782 AND process_flag = 1
2783 AND ROWNUM < 2;
2784 EXCEPTION
2785 WHEN NO_DATA_FOUND
2786 THEN
2787 inv_txn_manager_grp.gi_flow_schedule := 0;
2788 END;
2789 ELSE
2790 inv_txn_manager_grp.gi_flow_schedule := 0;
2791 END IF;
2792
2793 /** WIP J dev condition. If WIP J is not installed do as now,
2794 /*else call a new new API wip_mti_pub.preInvWIPProcessing()
2795 /* This has to be called before validate_group()
2796 /* we should retain create_flow sch for WIP I and below.*/
2797 IF ( l_srctypeid = 5
2798 AND wip_constants.dmf_patchset_level >=
2799 wip_constants.dmf_patchset_j_value
2800 )
2801 THEN
2802 -- Pawan Added following changes for gme- convergence
2803 IF l_wip_entity_type = 10 THEN
2804 IF (l_debug = 1) THEN
2805 inv_log_util.trace('in for gme pre_process','INV_TXN_MANAGER_PUB', 9);
2806 END IF;
2807 gme_api_grp.gme_pre_process_txns
2808 ( p_header_id => l_header_id,
2809 x_return_status => l_ret_sts_pre) ;
2810 IF (l_ret_sts_pre = fnd_api.g_ret_sts_success) THEN
2811 IF (l_debug = 1) THEN
2812 inv_log_util.trace('Success from:!!gme_api_grp.gme_pre_process_txns', 'INV_TXN_MANAGER_PUB',1);
2813 END IF;
2814
2815 IF FND_API.To_Boolean( p_commit ) then
2816 COMMIT WORK; /* Commit after preInvWIP all MTI records */
2817 END IF;
2818
2819 --check if all records have been failed by the wip API.
2820 BEGIN
2821 SELECT transaction_source_type_id INTO l_source_type_id
2822 FROM MTL_TRANSACTIONS_INTERFACE
2823 WHERE TRANSACTION_HEADER_ID = l_header_id
2824 AND PROCESS_FLAG = 1
2825 AND ROWNUM < 2;
2826 EXCEPTION
2827 WHEN NO_DATA_FOUND THEN
2828 x_return_status := FND_API.G_RET_STS_ERROR;
2829 x_msg_data := 'All records failed by gme_api_grp.gme_pre_process_txns';
2830 RETURN -1;
2831 END;
2832 ELSE
2833 IF (l_debug = 1) THEN
2834 inv_log_util.trace('Failure from:!!gme_api_grp.gme_pre_process_txns', 'INV_TXN_MANAGER_PUB',1);
2835 END IF;
2836 RAISE fnd_api.g_exc_unexpected_error;
2837 END IF;--check for success
2838 ELSE/*l_wip_entity_type = 10 */
2839
2840 wip_mti_pub.preinvwipprocessing (p_txnheaderid => l_header_id
2841 , x_returnstatus => l_ret_sts_pre
2842 );
2843
2844 IF (l_ret_sts_pre = fnd_api.g_ret_sts_success)
2845 THEN
2846 IF (l_debug = 1)
2847 THEN
2848 inv_log_util.TRACE ('Success from:!!preInvWIPProcessing'
2849 , 'INV_TXN_MANAGER_PUB'
2850 , 1
2851 );
2852 END IF;
2853
2854 IF fnd_api.to_boolean (p_commit)
2855 THEN
2856 COMMIT WORK; /* Commit after preInvWIP all MTI records */
2857 END IF;
2858
2859 --check if all records have been failed by the wip API.
2860 BEGIN
2861 SELECT transaction_source_type_id
2862 INTO l_source_type_id
2863 FROM mtl_transactions_interface
2864 WHERE transaction_header_id = l_header_id
2865 AND process_flag = 1
2866 AND ROWNUM < 2;
2867 EXCEPTION
2868 WHEN NO_DATA_FOUND
2869 THEN
2870 x_return_status := fnd_api.g_ret_sts_error;
2871 x_msg_data := 'All records failed by preInvWipProcessing';
2872 RETURN -1;
2873 END;
2874 ELSE
2875 IF (l_debug = 1)
2876 THEN
2877 inv_log_util.TRACE ('Failure from:!!preInvWIPProcessing'
2878 , 'INV_TXN_MANAGER_PUB'
2879 , 1
2880 );
2881 END IF;
2882
2883 RAISE fnd_api.g_exc_unexpected_error;
2884 END IF; --check for success
2885 END IF;--for l_wip_entity_type Pawan added for gme-convergence changes.
2886 ELSE
2887 IF (inv_txn_manager_grp.gi_flow_schedule <> 0)
2888 THEN
2889 wip_flow_utilities.create_flow_schedules (l_header_id);
2890 END IF;
2891 END IF; --J-dev
2892
2893 /***** Group Validation *******************************/
2894 validate_group (l_header_id
2895 , x_return_status
2896 , x_msg_count
2897 , x_msg_data
2898 , l_userid
2899 , l_loginid
2900 );
2901
2902 IF x_return_status = fnd_api.g_ret_sts_error
2903 THEN
2904 IF (l_debug = 1)
2905 THEN
2906 inv_log_util.TRACE ( 'Unexpected Error in Validate Group : '
2907 || x_msg_data
2908 , 'INV_TXN_MANAGER_PUB'
2909 , 9
2910 );
2911 END IF;
2912
2913 RAISE fnd_api.g_exc_unexpected_error;
2914 END IF;
2915
2916 /** Moved to after Validate_lines loop J-dev*/
2917 /******* Group Validation for WIP records *******************/
2918 /* This WIP API could potentially error some records in MTI. If any records
2919 /* have been errored, they would be stamped with error-code/explanation */
2920 /*IF (l_srctypeid = 5 ) THEN
2921 wip_mti_pub.postInvWIPValidation(
2922 p_txnHeaderID => l_header_id,
2923 x_returnStatus => x_return_status
2924 );
2925 END IF;*/
2926 IF fnd_api.to_boolean (p_commit)
2927 THEN
2928 COMMIT WORK; /* Commit after group validating all MTI records */
2929 END IF;
2930
2931 IF (l_debug = 1)
2932 THEN
2933 inv_log_util.TRACE ('Group validation complete '
2934 , 'INV_TXN_MANAGER_PUB'
2935 , 9
2936 );
2937 END IF;
2938
2939 batch_error := FALSE;
2940
2941 FOR l_line_rec_type IN aa1
2942 LOOP
2943 BEGIN
2944 l_trx_batch_id := l_line_rec_type.transaction_batch_id;
2945
2946 IF batch_error AND l_trx_batch_id = l_last_trx_batch_id
2947 THEN
2948 /** This group of transactions has failed move on to next **/
2949 /** UPDATE MTI row with Group Failure Message **/
2950 NULL;
2951 ELSE
2952 batch_error := FALSE;
2953 l_last_trx_batch_id := l_trx_batch_id;
2954
2955 /** Change for Lot Transactions **/
2956 IF (l_line_rec_type.transaction_source_type_id = 13)
2957 THEN
2958 IF (l_line_rec_type.transaction_action_id IN (40, 41, 42))
2959 THEN
2960 IF (l_debug = 1)
2961 THEN
2962 inv_log_util.TRACE ( 'Previous parent: '
2963 || l_previous_parent_id
2964 , 'INV_TXN_MANAGER_PUB'
2965 , 9
2966 );
2967 inv_log_util.TRACE ( 'Current parent: '
2968 || l_line_rec_type.parent_id
2969 , 'INV_TXN_MANAGER_PUB'
2970 , 9
2971 );
2972 END IF;
2973
2974 IF (NVL (l_previous_parent_id, 0) <> l_line_rec_type.parent_id
2975 )
2976 THEN
2977 /***** Its a new Batch. Before we do any validations, we have to
2978 -- check for the transaction bacth id. For all lot
2979 -- transctions, the batch id should be filled in for
2980 -- the TM to perform a complete rollback in case any
2981 -- of the records fail within a group/batch.
2982 -- Bug 2804402
2983 *******/
2984 l_batch_count := 0;
2985
2986 SELECT COUNT (1)
2987 INTO l_batch_count
2988 FROM mtl_transactions_interface
2989 WHERE parent_id = l_line_rec_type.parent_id
2990 AND transaction_batch_id IS NULL;
2991
2992 IF (l_batch_count > 0)
2993 THEN
2994 loaderrmsg ('INV_INVALID_BATCH'
2995 , 'INV_INVALID_BATCH_NUMBER'
2996 );
2997
2998 UPDATE mtl_transactions_interface
2999 SET last_update_date = SYSDATE
3000 , last_updated_by = l_userid
3001 , last_update_login = l_loginid
3002 , program_update_date = SYSDATE
3003 , process_flag = 3
3004 , lock_flag = 2
3005 , ERROR_CODE = SUBSTR (l_error_code, 1, 240)
3006 , error_explanation = SUBSTRB (l_error_exp, 1, 240)
3007 WHERE parent_id = l_line_rec_type.parent_id
3008 AND process_flag = 1;
3009
3010 RAISE rollback_line_validation;
3011 END IF;
3012
3013 l_index := 0;
3014 l_previous_parent_id := l_line_rec_type.parent_id;
3015 l_validation_status := 'Y';
3016 END IF;
3017
3018 l_index := l_index + 1;
3019 /*l_index identifies the distinct parent_id's and processes them
3020 *in one go
3021 */
3022 IF (l_index = 1)
3023 THEN
3024 IF (l_line_rec_type.transaction_action_id = 40)
3025 THEN
3026 fnd_message.set_name ('INV', 'INV_LOT_SPLIT_VALIDATIONS');
3027 l_error_code := fnd_message.get;
3028 inv_lot_trx_validation_pvt.validate_lot_split_trx
3029 (x_return_status => x_return_status
3030 , x_msg_count => x_msg_count
3031 , x_msg_data => x_msg_data
3032 , x_validation_status => l_validation_status
3033 , p_parent_id => l_line_rec_type.parent_id
3034 );
3035
3036 IF (x_return_status <> fnd_api.g_ret_sts_success)
3037 THEN
3038 -- Fetch all the error messages from the stack and log them.
3039 -- Update the MTI with last error message only, since the error messages can be redundant.
3040 FOR i IN 1 .. x_msg_count
3041 LOOP
3042 x_msg_data := fnd_msg_pub.get (i, 'F');
3043
3044 IF (l_debug = 1)
3045 THEN
3046 inv_log_util.TRACE
3047 ( 'Error in Validate_lot_Split_Trx: '
3048 || x_msg_data
3049 , 'INV_TXN_MANAGER_PUB'
3050 , 9
3051 );
3052 END IF;
3053 END LOOP;
3054
3055 UPDATE mtl_transactions_interface
3056 SET last_update_date = SYSDATE
3057 , last_updated_by = l_userid
3058 , last_update_login = l_loginid
3059 , program_update_date = SYSDATE
3060 , process_flag = 3
3061 , lock_flag = 2
3062 , ERROR_CODE = SUBSTR (l_error_code, 1, 240)
3063 , error_explanation = SUBSTRB (x_msg_data, 1, 240)
3064 WHERE ROWID = l_line_rec_type.ROWID
3065 AND process_flag = 1;
3066
3067 RAISE rollback_line_validation;
3068 END IF;
3069 ELSIF (l_line_rec_type.transaction_action_id = 41)
3070 THEN
3071 fnd_message.set_name ('INV', 'INV_LOT_MERGE_VALIDATIONS');
3072 l_error_code := fnd_message.get;
3073 inv_lot_trx_validation_pvt.validate_lot_merge_trx
3074 (x_return_status => x_return_status
3075 , x_msg_count => x_msg_count
3076 , x_msg_data => x_msg_data
3077 , x_validation_status => l_validation_status
3078 , p_parent_id => l_line_rec_type.parent_id
3079 );
3080
3081 IF (x_return_status <> fnd_api.g_ret_sts_success)
3082 THEN
3083 -- Fetch all the error messages from the stack and log them.
3084 -- Update the MTI with last error message only, since the error messages can be redundant.
3085 FOR i IN 1 .. x_msg_count
3086 LOOP
3087 x_msg_data := fnd_msg_pub.get (i, 'F');
3088
3089 IF (l_debug = 1)
3090 THEN
3091 inv_log_util.TRACE
3092 ( 'Error in Validate_lot_Merge_Trx: '
3093 || x_msg_data
3094 , 'INV_TXN_MANAGER_PUB'
3095 , 9
3096 );
3097 END IF;
3098 END LOOP;
3099
3100 UPDATE mtl_transactions_interface
3101 SET last_update_date = SYSDATE
3102 , last_updated_by = l_userid
3103 , last_update_login = l_loginid
3104 , program_update_date = SYSDATE
3105 , process_flag = 3
3106 , lock_flag = 2
3107 , ERROR_CODE = SUBSTRB (l_error_code, 1, 240)
3108 , error_explanation = SUBSTRB (x_msg_data, 1, 240)
3109 WHERE ROWID = l_line_rec_type.ROWID
3110 AND process_flag = 1;
3111
3112 RAISE rollback_line_validation;
3113 END IF;
3114 ELSIF (l_line_rec_type.transaction_action_id = 42)
3115 THEN
3116 fnd_message.set_name ('INV'
3117 , 'INV_LOT_TRANSLATE_VALIDATIONS'
3118 );
3119 l_error_code := fnd_message.get;
3120 inv_lot_trx_validation_pvt.validate_lot_translate_trx
3121 (x_return_status => x_return_status
3122 , x_msg_count => x_msg_count
3123 , x_msg_data => x_msg_data
3124 , x_validation_status => l_validation_status
3125 , p_parent_id => l_line_rec_type.parent_id
3126 );
3127
3128 IF (x_return_status <> fnd_api.g_ret_sts_success)
3129 THEN
3130 -- Fetch all the error messages from the stack and log them.
3131 -- Update the MTI with last error message only, since the error messages can be redundant.
3132 FOR i IN 1 .. x_msg_count
3133 LOOP
3134 x_msg_data := fnd_msg_pub.get (i, 'F');
3135
3136 IF (l_debug = 1)
3137 THEN
3138 inv_log_util.TRACE
3139 ( 'Error in Validate_lot_Translate_Trx: '
3140 || x_msg_data
3141 , 'INV_TXN_MANAGER_PUB'
3142 , 9
3143 );
3144 END IF;
3145 END LOOP;
3146
3147 UPDATE mtl_transactions_interface
3148 SET last_update_date = SYSDATE
3149 , last_updated_by = l_userid
3150 , last_update_login = l_loginid
3151 , program_update_date = SYSDATE
3152 , process_flag = 3
3153 , lock_flag = 2
3154 , ERROR_CODE = SUBSTR (l_error_code, 1, 240)
3155 , error_explanation = SUBSTRB (x_msg_data, 1, 240)
3156 WHERE ROWID = l_line_rec_type.ROWID
3157 AND process_flag = 1;
3158
3159 RAISE rollback_line_validation;
3160 END IF;
3161 END IF;
3162 END IF;
3163 END IF;
3164 END IF;
3165
3166 /** End of Change for Lot Transactions ***/
3167 IF ( l_line_rec_type.transaction_source_type_id = 13
3168 AND l_line_rec_type.transaction_action_id IN (40, 41, 42)
3169 AND l_index > 1
3170 AND l_validation_status <> 'Y'
3171 )
3172 THEN
3173 IF (l_line_rec_type.transaction_action_id = 40)
3174 THEN
3175 fnd_message.set_name ('INV', 'INV_LOT_SPLIT_VALIDATIONS');
3176 l_error_code := fnd_message.get;
3177 ELSIF (l_line_rec_type.transaction_action_id = 41)
3178 THEN
3179 fnd_message.set_name ('INV', 'INV_LOT_MERGE_VALIDATIONS');
3180 l_error_code := fnd_message.get;
3181 ELSIF (l_line_rec_type.transaction_action_id = 42)
3182 THEN
3183 fnd_message.set_name ('INV', 'INV_LOT_TRANSLATE_VALIDATIONS');
3184 l_error_code := fnd_message.get;
3185 END IF;
3186
3187 UPDATE mtl_transactions_interface
3188 SET last_update_date = SYSDATE
3189 , last_updated_by = l_userid
3190 , last_update_login = l_loginid
3191 , program_update_date = SYSDATE
3192 , process_flag = 3
3193 , lock_flag = 2
3194 , ERROR_CODE = SUBSTR (l_error_code, 1, 240)
3195 , error_explanation = SUBSTRB (x_msg_data, 1, 240)
3196 WHERE ROWID = l_line_rec_type.ROWID AND process_flag = 1;
3197
3198 RAISE rollback_line_validation;
3199 END IF;
3200
3201 /* bug 2807083, populate the distribution account id of lot translate txn */
3202 IF (l_debug = 1)
3203 THEN
3204 inv_log_util.TRACE
3205 ( 'l_line_rec_type.distribution_account_id is '
3206 || l_line_rec_type.distribution_account_id
3207 , 'INV_TXN_MANAGER_PUB'
3208 , 9
3209 );
3210 END IF;
3211
3212 IF (l_line_rec_type.distribution_account_id IS NULL)
3213 THEN
3214 SELECT distribution_account_id
3215 INTO l_dist_acct_id
3216 FROM mtl_transactions_interface
3217 WHERE ROWID = l_line_rec_type.ROWID;
3218
3219 l_line_rec_type.distribution_account_id := l_dist_acct_id;
3220 END IF;
3221
3222 IF (l_debug = 1)
3223 THEN
3224 inv_log_util.TRACE ('l_dist_acct_id is ' || l_dist_acct_id
3225 , 'INV_TXN_MANAGER_PUB'
3226 , 9
3227 );
3228 END IF;
3229
3230 validate_lines (p_line_rec_type => l_line_rec_type
3231 , p_error_flag => line_vldn_error_flag
3232 , p_userid => l_userid
3233 , p_loginid => l_loginid
3234 , p_applid => l_applid
3235 , p_progid => l_progid
3236 );
3237
3238
3239 IF (line_vldn_error_flag = 'Y')
3240 THEN
3241 IF (l_debug = 1)
3242 THEN
3243 inv_log_util.TRACE ('Error in Line Validatin'
3244 , 'INV_TXN_MANAGER_PUB'
3245 , 9
3246 );
3247 END IF;
3248
3249 RAISE rollback_line_validation;
3250 END IF;
3251
3252 /*Bug:5209598. Start of code.Following Code has been added to perform ATT/ATR high/low level
3253 checks for WIP transactions. In VALIDATE_TRANSACTIONS() of pkg INV_TXN_MANAGER_GRP, high/low level
3254 reservation checks are performed for WIP transactions.But for those WIP transactions that do not
3255 go through the validate_transactions() procedure, the following code does the reservation checks
3256 before inserting record into MMTT.
3257 */
3258 IF ( l_srctypeid = 5 ) THEN
3259 IF ( l_current_err_batch_id IS NULL
3260 OR l_Line_rec_Type.transaction_batch_id IS NULL
3261 OR l_current_err_batch_id <> l_Line_rec_Type.transaction_batch_id )THEN --050
3262 l_current_batch_failed := FALSE;
3263 FOR z1_rec IN
3264 Z1(inv_txn_manager_grp.gi_flow_schedule,l_Line_rec_type) LOOP
3265
3266 --bug#13527319 to skip validation for phantom component which has op_seq_num negative and wip_supply_type = 6
3267 select nvl(operation_seq_num,1 ),nvl(wip_supply_type,1)
3268 into l_operation_seq_num, l_wip_supply_type
3269 from mtl_transactions_interface
3270 where ROWID = l_line_rec_type.ROWID;
3271 IF (l_debug = 1) THEN
3272 inv_log_util.trace('operation_seq_num '||l_operation_seq_num||' l_wip_supply_type '||l_wip_supply_type, 'INV_TXN_MANAGER_PUB', 9);
3273 END IF;
3274 if l_operation_seq_num < 0 and l_wip_supply_type =6 then
3275 goto endofz1loop;
3276 end if;
3277
3278 tree_exists := FALSE;
3279
3280 IF (l_debug = 1) THEN
3281 inv_log_util.trace('Getting values from Z1 cursor', 'INV_TXN_MANAGER_PUB', 9);
3282 END IF;
3283
3284 l_temp_rowid :=z1_rec.ROWID;
3285 l_item_id:=z1_rec.inventory_item_id;
3286 l_rev:=z1_rec.revision;
3287 l_org_id:=z1_rec.organization_id;
3288 l_sub_code:=z1_rec.subinventory_code;
3289 l_locid :=z1_rec.locator_id;
3290 --Bug#16229872 Start
3291 /*convert transaction quantity to primary quantity incase it is passed as null */
3292 l_txn_qty := z1_rec.transaction_quantity;
3293 l_trxuom := z1_rec.transaction_uom;
3294 l_priuom := z1_rec.primary_uom_code;
3295 IF (l_debug = 1) THEN
3296 inv_log_util.trace('l_item_id is:'||l_item_id, 'INV_TXN_MANAGER_PUB', 9);
3297 inv_log_util.trace('l_txn_qty is:'||l_txn_qty, 'INV_TXN_MANAGER_PUB', 9);
3298 inv_log_util.trace('l_trxuom is:'||l_trxuom, 'INV_TXN_MANAGER_PUB', 9);
3299 inv_log_util.trace('l_priuom is:'||l_priuom, 'INV_TXN_MANAGER_PUB', 9);
3300
3301 END IF;
3302
3303 IF z1_rec.primary_quantity IS NULL THEN
3304
3305 IF z1_rec.transaction_source_type_id = 5 then
3306 l_trx_qty := inv_convert.inv_um_convert(l_item_id,6,l_txn_qty,
3307 l_trxuom,l_priuom,'','');
3308 ELSE
3309 l_trx_qty := inv_convert.inv_um_convert(l_item_id,5,l_txn_qty,
3310 l_trxuom,l_priuom,'','');
3311 END IF;
3312
3313 IF (l_trx_qty = -99999) THEN
3314 IF (l_debug = 1) THEN
3315 inv_log_util.trace('Validate_Transactions: INV_CONVERT.INV_UM_CONVERT error while calculating primary qty', 'INV_TXN_MANAGER_GRP', 9);
3316 END IF;
3317 FND_MESSAGE.set_name('INV', 'INV_NO_CONVERSION_ERR');
3318 l_error_code:= fnd_message.get;
3319
3320 UPDATE MTL_TRANSACTIONS_INTERFACE
3321 SET LAST_UPDATE_DATE = SYSDATE,
3322 LAST_UPDATED_BY = l_userid,
3323 LAST_UPDATE_LOGIN = l_loginid,
3324 PROGRAM_UPDATE_DATE = SYSDATE,
3325 PROCESS_FLAG = 3,
3326 LOCK_FLAG = 2,
3327 ERROR_CODE = substrb(l_error_code,1,240),
3328 ERROR_EXPLANATION = substrb(l_error_exp,1,240)
3329 WHERE ROWID = l_temp_rowid
3330 AND PROCESS_FLAG = 1;
3331
3332 --check for batch error
3333 loaderrmsg('INV_GROUP_ERROR','INV_GROUP_ERROR');
3334
3335 UPDATE MTL_TRANSACTIONS_INTERFACE MTI
3336 SET LAST_UPDATE_DATE = SYSDATE,
3337 LAST_UPDATED_BY = l_userid,
3338 LAST_UPDATE_LOGIN = l_loginid,
3339 PROGRAM_UPDATE_DATE = SYSDATE,
3340 PROCESS_FLAG = 3,
3341 LOCK_FLAG = 2,
3342 ERROR_CODE = substrb(l_error_code,1,240)
3343 WHERE TRANSACTION_HEADER_ID = l_header_id
3344 AND PROCESS_FLAG = 1
3345 AND TRANSACTION_BATCH_ID = l_Line_rec_Type.transaction_batch_id;
3346 -- group error changes.
3347 l_current_batch_failed := TRUE;--Bug#5075521
3348 l_current_err_batch_id := l_Line_rec_Type.transaction_batch_id;--Bug#5075521
3349 END IF;
3350 ELSE
3351 l_trx_qty:=z1_rec.primary_quantity;
3352 END IF;
3353 --Bug#16229872 End
3354 l_lotnum:=z1_rec.lot_number;
3355 l_srctypeid:=z1_rec.transaction_source_type_id;
3356 l_actid:=z1_rec.transaction_action_id;
3357 l_srcid:=z1_rec.transaction_source_id;
3358 l_src_code:=z1_rec.transaction_source_name;
3359 l_srclineid:=z1_rec.source_line_id;
3360 l_rctrl:=z1_rec.revision_qty_control_code;
3361 l_lctrl:=z1_rec.lot_control_code;
3362 l_xfrsub:=z1_rec.transfer_subinventory;
3363 l_xlocid:=z1_rec.transfer_locator;
3364 l_trxdate:=z1_rec.TRANSACTION_DATE;
3365 l_neg_inv_rcpt:=z1_rec.negative_inv_receipt_code;
3366
3367 --Bug 6454464, we should not call available qty validation for CMRO job type
3368 inv_reservation_pvt.get_wip_entity_type
3369 ( p_api_version_number => 1.0
3370 , p_init_msg_lst => fnd_api.g_false
3371 , x_return_status => l_return_status
3372 , x_msg_count => l_msg_count
3373 , x_msg_data => l_msg_data
3374 , p_organization_id => null
3375 , p_item_id => null
3376 , p_source_type_id => null
3377 , p_source_header_id => l_srcid
3378 , p_source_line_id => null
3379 , p_source_line_detail => null
3380 , x_wip_entity_type => l_rsv_wip_entity_type
3381 , x_wip_job_type => l_rsv_wip_job_type
3382 );
3383
3384 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
3385 inv_log_util.TRACE ('Return status from get wip entity. '||l_return_status, 'INV_TXN_MANAGER_PUB', 9);
3386 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
3387 inv_log_util.TRACE ('Return status from get wip entity. '||l_return_status, 'INV_TXN_MANAGER_PUB', 9);
3388 END IF;
3389
3390 IF (l_debug = 1) THEN
3391 inv_log_util.TRACE ('Wip entity type ' || l_rsv_wip_entity_type, 'INV_TXN_MANAGER_PUB', 9);
3392 END IF;
3393
3394 --Bug 6454464, we should not call available qty validation for CMRO job type
3395 IF (l_rsv_wip_entity_type <> inv_reservation_global.g_wip_source_type_cmro) THEN
3396
3397 IF l_rctrl = 1 THEN
3398 l_revision_control := FALSE;
3399 ELSE
3400 l_revision_control := TRUE;
3401 END IF;
3402
3403 IF l_lctrl = 1 THEN
3404 l_lot_control := FALSE;
3405 ELSE
3406 l_lot_control := TRUE;
3407 END IF;
3408
3409 IF (l_debug = 1) THEN
3410 inv_log_util.trace('Calling Create tree', 'INV_TXN_MANAGER_PUB', 9);
3411 END IF;
3412
3413 -- Bug 4194323 WIP Assembly Return transactions need to look for Available Quantity
3414 --against the Sales Order if it's linked to job
3415
3416 IF ( NOT l_current_batch_failed) THEN --350
3417 BEGIN
3418 SELECT demand_source_header_id , demand_source_line
3419 INTO l_dem_hdr_id,l_dem_line_id
3420 FROM mtl_transactions_interface
3421 WHERE
3422 ROWID = l_temp_rowid ;
3423 EXCEPTION
3424 WHEN OTHERS THEN
3425 IF (l_debug = 1) THEN
3426 inv_log_util.trace('Error in getting Demand Info : '
3427 || x_msg_data,'INV_TXN_MANAGER_PUB', 9);
3428 END IF;
3429 l_error_code := 'Error in getting Demand Info';
3430 l_error_exp := 'Error in getting Demand Info';
3431
3432 UPDATE MTL_TRANSACTIONS_INTERFACE
3433 SET LAST_UPDATE_DATE = SYSDATE,
3434 LAST_UPDATED_BY = l_userid,
3435 LAST_UPDATE_LOGIN = l_loginid,
3436 PROGRAM_UPDATE_DATE = SYSDATE,
3437 PROCESS_FLAG = 3,
3438 LOCK_FLAG = 2,
3439 ERROR_CODE = substrb(l_error_code,1,240),
3440 ERROR_EXPLANATION = substrb(l_error_exp,1,240)
3441 WHERE ROWID = l_temp_rowid
3442 AND PROCESS_FLAG = 1;
3443 --check for batch error
3444 loaderrmsg('INV_GROUP_ERROR','INV_GROUP_ERROR');
3445
3446 UPDATE MTL_TRANSACTIONS_INTERFACE MTI
3447 SET LAST_UPDATE_DATE = SYSDATE,
3448 LAST_UPDATED_BY = l_userid,
3449 LAST_UPDATE_LOGIN = l_loginid,
3450 PROGRAM_UPDATE_DATE = SYSDATE,
3451 PROCESS_FLAG = 3,
3452 LOCK_FLAG = 2,
3453 ERROR_CODE = substrb(l_error_code,1,240)
3454 WHERE TRANSACTION_HEADER_ID = l_header_id
3455 AND PROCESS_FLAG = 1
3456 AND TRANSACTION_BATCH_ID =l_Line_rec_Type.transaction_batch_id;
3457
3458 l_current_batch_failed := TRUE;--Bug#5075521
3459 l_current_err_batch_id := l_Line_rec_Type.transaction_batch_id;--Bug#5075521
3460 END ;
3461
3462 IF ( NOT l_current_batch_failed) THEN --400
3463 IF ( l_srctypeid = INv_GLOBALS.G_SOURCETYPE_WIP AND
3464 l_actid = INV_GLOBALS.G_ACTION_ASSYRETURN AND l_dem_hdr_id IS NOT NULL ) then
3465
3466
3467 INV_QUANTITY_TREE_PVT.create_tree
3468 ( p_api_version_number => 1.0
3469 , p_init_msg_lst => fnd_api.g_false
3470 , x_return_status => l_return_status
3471 , x_msg_count => l_msg_count
3472 , x_msg_data => l_msg_data
3473 , p_organization_id => l_org_id
3474 , p_inventory_item_id => l_item_id
3475 , p_tree_mode => 2
3476 , p_is_revision_control => l_revision_control
3477 , p_is_lot_control => l_lot_control
3478 , p_is_serial_control => FALSE
3479 , p_include_suggestion => FALSE
3480 , p_demand_source_type_id => 2
3481 , p_demand_source_header_id => nvl(l_dem_hdr_id,-9999)
3482 , p_demand_source_line_id => nvl(l_dem_line_id,-9999)
3483 , p_demand_source_name => l_src_code
3484 , p_demand_source_delivery => NULL
3485 , p_lot_expiration_date => NULL
3486 , x_tree_id => l_tree_id
3487 , p_onhand_source => 3 --g_all_subs
3488 , p_exclusive => 0 --g_non_exclusive
3489 , p_pick_release => 0 --g_pick_release_no
3490 ) ;
3491
3492
3493 ELSE
3494
3495 INV_QUANTITY_TREE_PVT.create_tree
3496 ( p_api_version_number => 1.0
3497 , p_init_msg_lst => fnd_api.g_false
3498 , x_return_status => l_return_status
3499 , x_msg_count => l_msg_count
3500 , x_msg_data => l_msg_data
3501 , p_organization_id => l_org_id
3502 , p_inventory_item_id => l_item_id
3503 , p_tree_mode => 2
3504 , p_is_revision_control => l_revision_control
3505 , p_is_lot_control => l_lot_control
3506 , p_is_serial_control => FALSE
3507 , p_include_suggestion => FALSE
3508 , p_demand_source_type_id => nvl(l_srctypeid,-9999)
3509 , p_demand_source_header_id => nvl(l_srcid,-9999)
3510 , p_demand_source_line_id => nvl(l_srclineid,-9999)
3511 , p_demand_source_name => l_src_code
3512 , p_demand_source_delivery => NULL
3513 , p_lot_expiration_date => NULL
3514 , x_tree_id => l_tree_id
3515 , p_onhand_source => 3 --g_all_subs
3516 , p_exclusive => 0 --g_non_exclusive
3517 , p_pick_release => 0 --g_pick_release_no
3518 ) ;
3519 END IF;
3520 -- Bug 4194323 Ends
3521
3522 IF (l_debug = 1) THEN
3523 inv_log_util.trace('After create tree tree : ' || l_msg_data,'INV_TXN_MANAGER_PUB', 9);
3524 END IF;
3525
3526 IF l_return_status IN (fnd_api.g_ret_sts_error, fnd_api.g_ret_sts_unexp_error) THEN
3527 IF (l_debug = 1) THEN
3528 inv_log_util.trace('Error while creating tree : x_msg_data = ' || l_msg_data,'INV_TXN_MANAGER_PUB', 9);
3529 END IF;
3530 FND_MESSAGE.set_name('INV','INV_ERR_CREATETREE');
3531 FND_MESSAGE.set_token('ROUTINE','UE:AVAIL_TO_TRX');
3532 l_error_code := FND_MESSAGE.get;
3533 l_error_exp := l_msg_data;
3534 x_msg_data := l_msg_data;
3535 UPDATE MTL_TRANSACTIONS_INTERFACE
3536 SET LAST_UPDATE_DATE = SYSDATE,
3537 LAST_UPDATED_BY = l_userid,
3538 LAST_UPDATE_LOGIN = l_loginid,
3539 PROGRAM_UPDATE_DATE = SYSDATE,
3540 PROCESS_FLAG = 3,
3541 LOCK_FLAG = 2,
3542 ERROR_CODE = substrb(l_error_code,1,240),
3543 ERROR_EXPLANATION = substrb(l_error_exp,1,240)
3544 WHERE ROWID = l_temp_rowid
3545 AND PROCESS_FLAG = 1;
3546
3547 --check for batch error
3548 loaderrmsg('INV_GROUP_ERROR','INV_GROUP_ERROR');
3549
3550 UPDATE MTL_TRANSACTIONS_INTERFACE MTI
3551 SET LAST_UPDATE_DATE = SYSDATE,
3552 LAST_UPDATED_BY = l_userid,
3553 LAST_UPDATE_LOGIN = l_loginid,
3554 PROGRAM_UPDATE_DATE = SYSDATE,
3555 PROCESS_FLAG = 3,
3556 LOCK_FLAG = 2,
3557 ERROR_CODE = substrb(l_error_code,1,240)
3558 WHERE TRANSACTION_HEADER_ID = l_header_id
3559 AND PROCESS_FLAG = 1
3560 AND TRANSACTION_BATCH_ID = l_Line_rec_Type.transaction_batch_id; --Bug#5075521
3561 -- group error changes.
3562
3563 l_current_batch_failed := TRUE;--Bug#5075521
3564 l_current_err_batch_id := l_Line_rec_Type.transaction_batch_id;--Bug#5075521
3565 END IF ;
3566 END IF; --400
3567 END IF;--350
3568
3569 IF ( NOT l_current_batch_failed) THEN --100
3570 INV_QUANTITY_TREE_PVT.query_tree
3571 ( p_api_version_number => 1.0
3572 , p_init_msg_lst => fnd_api.g_false
3573 , x_return_status => l_return_status
3574 , x_msg_count => l_msg_count
3575 , x_msg_data => l_msg_data
3576 , p_tree_id => l_tree_id
3577 , p_revision => l_rev
3578 , p_lot_number => l_lotnum
3579 , p_subinventory_code => l_sub_code
3580 , p_transfer_subinventory_code => l_xfrsub
3581 , p_locator_id => l_locid
3582 , x_qoh => l_qoh
3583 , x_rqoh => l_rqoh
3584 , x_pqoh => l_pqoh
3585 , x_qr => l_qr
3586 , x_qs => l_qs
3587 , x_att => l_att
3588 , x_atr => l_atr
3589 );
3590
3591 IF l_return_status = fnd_api.g_ret_sts_error THEN
3592 IF (l_debug = 1) THEN
3593 inv_log_util.trace('Expected Error while querying tree : ' || l_msg_data,'INV_TXN_MANAGER_PUB', 9);
3594 END IF;
3595
3596 FND_MESSAGE.set_name('INV','INV_INTERNAL_ERROR');
3597 FND_MESSAGE.set_token('token1','XACT_QTY1');
3598 l_error_code := FND_MESSAGE.get;
3599 l_error_exp := l_msg_data;
3600 x_msg_data := l_msg_data;
3601 UPDATE MTL_TRANSACTIONS_INTERFACE
3602 SET LAST_UPDATE_DATE = SYSDATE,
3603 LAST_UPDATED_BY = l_userid,
3604 LAST_UPDATE_LOGIN = l_loginid,
3605 PROGRAM_UPDATE_DATE = SYSDATE,
3606 PROCESS_FLAG = 3,
3607 LOCK_FLAG = 2,
3608 ERROR_CODE = substrb(l_error_code,1,240),
3609 ERROR_EXPLANATION = substrb(l_error_exp,1,240)
3610 WHERE TRANSACTION_interface_id = l_temp_rowid
3611 AND PROCESS_FLAG = 1;
3612 --check for batch error
3613 loaderrmsg('INV_GROUP_ERROR','INV_GROUP_ERROR');
3614
3615 UPDATE MTL_TRANSACTIONS_INTERFACE MTI
3616 SET LAST_UPDATE_DATE = SYSDATE,
3617 LAST_UPDATED_BY = l_userid,
3618 LAST_UPDATE_LOGIN = l_loginid,
3619 PROGRAM_UPDATE_DATE = SYSDATE,
3620 PROCESS_FLAG = 3,
3621 LOCK_FLAG = 2,
3622 ERROR_CODE = substrb(l_error_code,1,240)
3623 WHERE TRANSACTION_HEADER_ID = l_header_id
3624 AND PROCESS_FLAG = 1
3625 AND TRANSACTION_BATCH_ID = l_Line_rec_Type.transaction_batch_id; --Bug#5075521
3626 -- group error changes.
3627 l_current_batch_failed := TRUE;--Bug#5075521
3628 l_current_err_batch_id := l_Line_rec_Type.transaction_batch_id;--Bug#5075521
3629
3630 END IF ;
3631
3632 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
3633 IF (l_debug = 1) THEN
3634 inv_log_util.trace('UnExpected Error while querying tree : ' || l_msg_data,'INV_TXN_MANAGER_PUB', 9);
3635 END IF;
3636
3637 FND_MESSAGE.set_name('INV','INV_INTERNAL_ERROR');
3638 FND_MESSAGE.set_token('token1','XACT_QTY1');
3639 l_error_code := FND_MESSAGE.get;
3640 l_error_exp := l_msg_data;
3641 x_msg_data := l_msg_data;
3642 UPDATE MTL_TRANSACTIONS_INTERFACE
3643 SET LAST_UPDATE_DATE = SYSDATE,
3644 LAST_UPDATED_BY = l_userid,
3645 LAST_UPDATE_LOGIN = l_loginid,
3646 PROGRAM_UPDATE_DATE = SYSDATE,
3647 PROCESS_FLAG = 3,
3648 LOCK_FLAG = 2,
3649 ERROR_CODE = substrb(l_error_code,1,240),
3650 ERROR_EXPLANATION = substrb(l_error_exp,1,240)
3651 WHERE ROWID = l_temp_rowid
3652 AND PROCESS_FLAG = 1;
3653 --check for batch error
3654 loaderrmsg('INV_GROUP_ERROR','INV_GROUP_ERROR');
3655
3656 UPDATE MTL_TRANSACTIONS_INTERFACE MTI
3657 SET LAST_UPDATE_DATE = SYSDATE,
3658 LAST_UPDATED_BY = l_userid,
3659 LAST_UPDATE_LOGIN = l_loginid,
3660 PROGRAM_UPDATE_DATE = SYSDATE,
3661 PROCESS_FLAG = 3,
3662 LOCK_FLAG = 2,
3663 ERROR_CODE = substrb(l_error_code,1,240)
3664 WHERE TRANSACTION_HEADER_ID = l_header_id
3665 AND PROCESS_FLAG = 1
3666 AND TRANSACTION_BATCH_ID = l_Line_rec_Type.transaction_batch_id;--Bug#5075521
3667 --group error changes.
3668 l_current_batch_failed := TRUE;--Bug#5075521
3669 l_current_err_batch_id := l_Line_rec_Type.transaction_batch_id;--Bug#5075521
3670 END IF;
3671
3672 IF (l_debug = 1) THEN
3673 inv_log_util.trace('L_QOH : ' || l_qoh,'INV_TXN_MANAGER_PUB', 9);
3674 inv_log_util.trace('L_RQOH : ' || l_rqoh,'INV_TXN_MANAGER_PUB', 9);
3675 inv_log_util.trace('L_PQOH : ' || l_pqoh,'INV_TXN_MANAGER_PUB', 9);
3676 inv_log_util.trace('L_QR : ' || l_qr,'INV_TXN_MANAGER_PUB', 9);
3677 inv_log_util.trace('L_QS : ' || l_qs,'INV_TXN_MANAGER_PUB', 9);
3678 inv_log_util.trace('L_ATT : ' || l_att,'INV_TXN_MANAGER_PUB', 9);
3679 inv_log_util.trace('L_ATR : ' || l_atr,'INV_TXN_MANAGER_PUB', 9);
3680 END IF;
3681 END IF;--100
3682
3683 -- Bug 3427817: For WIP backflush transactions, we should not
3684 -- check for negative availability. If it is
3685 -- a backflush transaction, then get the
3686 -- profile value and do not check for
3687 -- availability if the profile is set to
3688 -- YES.
3689 IF ( NOT l_current_batch_failed ) THEN--150
3690 /*Bug:5392366. Modified the following condition to also check
3691 completion_transaction_id and move_transaction_id to make sure it
3692 is a backflush transaction. If both these values are null then
3693 it is is not a backflush transaction.
3694 */
3695 IF ((l_line_rec_Type.transaction_source_type_id = inv_globals.G_SOURCETYPE_WIP) AND
3696 (l_line_rec_Type.transaction_action_id
3697 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
3698 -- It is a backflush transaction. Get the
3699 -- override flag.
3700 l_override_neg_for_backflush :=
3701 fnd_profile.value('INV_OVERRIDE_NEG_FOR_BACKFLUSH');
3702 /*Bug 4764343 Base Bug:4645686. Introducing a new profile 'INV_OVERRIDE_RSV_FOR_BACKFLUSH'
3703 for a specific customer.If set to 'Yes', backflush transaction can drive inventory negative,
3704 even if any reservations exist for the item*/
3705 l_override_rsv_for_backflush := NVL(fnd_profile.value('INV_OVERRIDE_RSV_FOR_BACKFLUSH'), 2);
3706 ELSE
3707 l_override_neg_for_backflush := 0;
3708 l_override_rsv_for_backflush := 2;
3709 END IF;
3710 IF (l_debug = 1) THEN
3711 inv_log_util.trace('l_override_neg_for_backflush ' || l_override_neg_for_backflush,'INV_TXN_MANAGER_PUB', 9);
3712 inv_log_util.trace('l_override_rsv_for_backflush ' || l_override_rsv_for_backflush,'INV_TXN_MANAGER_PUB', 9);
3713 END IF;
3714
3715 --Bug 3487453: Added and set the variable l_translate
3716 -- to true for the token to be translated.
3717 /* Bug 5444209 No check for gme txns adding back to inventory */
3718 IF ((l_Line_rec_Type.wip_entity_type <> 10) OR
3719 (l_Line_rec_Type.wip_entity_type = 10 AND l_line_rec_Type.transaction_type_id NOT IN (43, 44, 1002))) THEN
3720 IF (l_att < l_trx_qty) THEN
3721 IF (l_neg_inv_rcpt = 1 OR l_override_neg_for_backflush = 1) THEN
3722
3723 IF (l_qr >l_trx_qty OR l_qs >0) THEN
3724 /*Bug 4764343 base Bug::4645686. This condition is added for a specific customer by introducing
3725 a new profile 'INV_OVERRIDE_RSV_FOR_BACKFLUSH' . If this profile is not set to 'Yes'
3726 then the backflush transaction can not consume existing reservations.Else it can consume
3727 existing reservation and can drive inventory go negative.
3728 */
3729 IF (l_override_rsv_for_backflush <> 1 ) THEN
3730 inv_log_util.trace('Transaction quantity must be less than or equal to available quantity','INV_TXN_MANAGER_PUB', 9);
3731 FND_MESSAGE.set_name('INV','INV_INT_PROCCODE');
3732 l_error_code := FND_MESSAGE.get;
3733 FND_MESSAGE.set_name('INV','INV_QTY_LESS_OR_EQUAL');
3734 l_error_exp := FND_MESSAGE.get;
3735 x_msg_data := l_error_exp;
3736 UPDATE MTL_TRANSACTIONS_INTERFACE
3737 SET LAST_UPDATE_DATE = SYSDATE,
3738 LAST_UPDATED_BY = l_userid,
3739 LAST_UPDATE_LOGIN = l_loginid,
3740 PROGRAM_UPDATE_DATE = SYSDATE,
3741 PROCESS_FLAG = 3,
3742 LOCK_FLAG = 2,
3743 ERROR_CODE = substrb(l_error_code,1,240),
3744 ERROR_EXPLANATION = substrb(l_error_exp,1,240)
3745 WHERE ROWID = l_temp_rowid
3746 AND PROCESS_FLAG = 1;
3747 --check for batch error
3748 loaderrmsg('INV_GROUP_ERROR','INV_GROUP_ERROR');
3749
3750 UPDATE MTL_TRANSACTIONS_INTERFACE MTI
3751 SET LAST_UPDATE_DATE = SYSDATE,
3752 LAST_UPDATED_BY = l_userid,
3753 LAST_UPDATE_LOGIN = l_loginid,
3754 PROGRAM_UPDATE_DATE = SYSDATE,
3755 PROCESS_FLAG = 3,
3756 LOCK_FLAG = 2,
3757 ERROR_CODE = substrb(l_error_code,1,240)
3758 WHERE TRANSACTION_HEADER_ID = l_header_id
3759 AND PROCESS_FLAG = 1
3760 AND TRANSACTION_BATCH_ID = l_Line_rec_Type.transaction_batch_id;
3761 -- group error changes.
3762 l_current_batch_failed := TRUE;--Bug#5075521
3763 l_current_err_batch_id := l_Line_rec_Type.transaction_batch_id;--Bug#5075521
3764 END IF; --override_rsv_for_backflush
3765 END IF;
3766
3767 IF (NOT l_current_batch_failed ) THEN --200
3768 INV_QUANTITY_TREE_PVT.query_tree
3769 ( p_api_version_number => 1.0
3770 , p_init_msg_lst => fnd_api.g_false
3771 , x_return_status => l_return_status
3772 , x_msg_count => l_msg_count
3773 , x_msg_data => l_msg_data
3774 , p_tree_id => l_tree_id
3775 , p_revision => NULL
3776 , p_lot_number => NULL
3777 , p_subinventory_code => NULL
3778 , p_locator_id => NULL
3779 , x_qoh => l_item_qoh
3780 , x_rqoh => l_item_rqoh
3781 , x_pqoh => l_item_pqoh
3782 , x_qr => l_item_qr
3783 , x_qs => l_item_qs
3784 , x_att => l_item_att
3785 , x_atr => l_item_atr
3786 );
3787
3788 IF l_return_status = fnd_api.g_ret_sts_error THEN
3789 IF (l_debug = 1) THEN
3790 inv_log_util.trace('Expected Error while querying tree : ' || l_msg_data,'INV_TXN_MANAGER_PUB', 9);
3791 END IF;
3792
3793 FND_MESSAGE.set_name('INV','INV_INTERNAL_ERROR');
3794 FND_MESSAGE.set_token('token1','XACT_QTY1');
3795 l_error_code := FND_MESSAGE.get;
3796 l_error_exp := l_msg_data;
3797 x_msg_data := l_msg_data;
3798 UPDATE MTL_TRANSACTIONS_INTERFACE
3799 SET LAST_UPDATE_DATE = SYSDATE,
3800 LAST_UPDATED_BY = l_userid,
3801 LAST_UPDATE_LOGIN = l_loginid,
3802 PROGRAM_UPDATE_DATE = SYSDATE,
3803 PROCESS_FLAG = 3,
3804 LOCK_FLAG = 2,
3805 ERROR_CODE = substrb(l_error_code,1,240),
3806 ERROR_EXPLANATION = substrb(l_error_exp,1,240)
3807 WHERE TRANSACTION_interface_id = l_temp_rowid
3808 AND PROCESS_FLAG = 1;
3809 --check for batch error
3810 loaderrmsg('INV_GROUP_ERROR','INV_GROUP_ERROR');
3811
3812 UPDATE MTL_TRANSACTIONS_INTERFACE MTI
3813 SET LAST_UPDATE_DATE = SYSDATE,
3814 LAST_UPDATED_BY = l_userid,
3815 LAST_UPDATE_LOGIN = l_loginid,
3816 PROGRAM_UPDATE_DATE = SYSDATE,
3817 PROCESS_FLAG = 3,
3818 LOCK_FLAG = 2,
3819 ERROR_CODE = substrb(l_error_code,1,240)
3820 WHERE TRANSACTION_HEADER_ID = l_header_id
3821 AND PROCESS_FLAG = 1
3822 AND TRANSACTION_BATCH_ID = l_Line_rec_Type.transaction_batch_id;
3823 -- group error changes.
3824 l_current_batch_failed := TRUE;--Bug#5075521
3825 l_current_err_batch_id := l_Line_rec_Type.transaction_batch_id;--Bug#5075521
3826 END IF ;
3827
3828 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
3829 IF (l_debug = 1) THEN
3830 inv_log_util.trace('UnExpected Error while querying tree : ' || l_msg_data,'INV_TXN_MANAGER_PUB', 9);
3831 END IF;
3832
3833 FND_MESSAGE.set_name('INV','INV_INTERNAL_ERROR');
3834 FND_MESSAGE.set_token('token1','XACT_QTY1');
3835 l_error_code := FND_MESSAGE.get;
3836 l_error_exp := l_msg_data;
3837 x_msg_data := l_msg_data;
3838 UPDATE MTL_TRANSACTIONS_INTERFACE
3839 SET LAST_UPDATE_DATE = SYSDATE,
3840 LAST_UPDATED_BY = l_userid,
3841 LAST_UPDATE_LOGIN = l_loginid,
3842 PROGRAM_UPDATE_DATE = SYSDATE,
3843 PROCESS_FLAG = 3,
3844 LOCK_FLAG = 2,
3845 ERROR_CODE = substrb(l_error_code,1,240),
3846 ERROR_EXPLANATION = substrb(l_error_exp,1,240)
3847 WHERE ROWID = l_temp_rowid
3848 AND PROCESS_FLAG = 1;
3849 --check for batch error
3850 loaderrmsg('INV_GROUP_ERROR','INV_GROUP_ERROR');
3851
3852 UPDATE MTL_TRANSACTIONS_INTERFACE MTI
3853 SET LAST_UPDATE_DATE = SYSDATE,
3854 LAST_UPDATED_BY = l_userid,
3855 LAST_UPDATE_LOGIN = l_loginid,
3856 PROGRAM_UPDATE_DATE = SYSDATE,
3857 PROCESS_FLAG = 3,
3858 LOCK_FLAG = 2,
3859 ERROR_CODE = substrb(l_error_code,1,240)
3860 WHERE TRANSACTION_HEADER_ID = l_header_id
3861 AND PROCESS_FLAG = 1
3862 AND TRANSACTION_BATCH_ID = l_Line_rec_Type.transaction_batch_id;
3863 --group error changes.
3864 l_current_batch_failed := TRUE;--Bug#5075521
3865 l_current_err_batch_id := l_Line_rec_Type.transaction_batch_id;--Bug#5075521
3866 END IF;
3867 END IF; --200
3868 inv_log_util.trace('L_ITEM_QOH : ' || l_item_qoh,'INV_TXN_MANAGER_PUB', 9);
3869 inv_log_util.trace('L_ITEM_RQOH : ' || l_item_rqoh,'INV_TXN_MANAGER_PUB', 9);
3870 inv_log_util.trace('L_ITEM_PQOH : ' || l_item_pqoh,'INV_TXN_MANAGER_PUB', 9);
3871 inv_log_util.trace('L_ITEM_QR : ' || l_item_qr,'INV_TXN_MANAGER_PUB', 9);
3872 inv_log_util.trace('L_ITEM_QS : ' || l_item_qs,'INV_TXN_MANAGER_PUB', 9);
3873 inv_log_util.trace('L_ITEM_ATT : ' || l_item_att,'INV_TXN_MANAGER_PUB', 9);
3874 inv_log_util.trace('L_ITEM_ATR : ' || l_item_atr,'INV_TXN_MANAGER_PUB', 9);
3875 inv_log_util.trace('L_TRX_QTY : ' || l_trx_qty,'INV_TXN_MANAGER_PUB', 9);
3876
3877 IF ( NOT l_current_batch_failed) THEN --250
3878 IF (l_item_qoh <> l_item_att) THEN -- Higher Level Reservations
3879 IF (l_item_att < l_trx_qty AND l_item_qr > 0) THEN
3880 /*Bug 4764343 Base Bug::4645686. This condition is added for a specific
3881 customer by introducing a new profile 'INV_OVERRIDE_RSV_FOR_BACKFLUSH' .
3882 If this profile is not set to 'Yes'then the backflush transaction can not
3883 consume existing reservations.Else it can consume existing reservation and can
3884 drive inventory negative. */
3885 IF (l_override_rsv_for_backflush <> 1 ) THEN
3886 inv_log_util.trace('Total Org quantity cannot become negative when there are reservations present','INV_TXN_MANAGER_PUB', 9);
3887 FND_MESSAGE.set_name('INV','INV_INT_PROCCODE');
3888 l_error_code := FND_MESSAGE.get;
3889 FND_MESSAGE.set_name('INV','INV_ORG_QUANTITY');
3890 l_error_exp := FND_MESSAGE.get;
3891 FND_MESSAGE.set_name('INV','INV_INTERNAL_ERROR');
3892 x_msg_data := l_error_exp;
3893 UPDATE MTL_TRANSACTIONS_INTERFACE
3894 SET LAST_UPDATE_DATE = SYSDATE,
3895 LAST_UPDATED_BY = l_userid,
3896 LAST_UPDATE_LOGIN = l_loginid,
3897 PROGRAM_UPDATE_DATE = SYSDATE,
3898 PROCESS_FLAG = 3,
3899 LOCK_FLAG = 2,
3900 ERROR_CODE = substrb(l_error_code,1,240),
3901 ERROR_EXPLANATION = substrb(l_error_exp,1,240)
3902 WHERE ROWID = l_temp_rowid
3903 AND PROCESS_FLAG = 1;
3904 --check for batch error
3905 loaderrmsg('INV_GROUP_ERROR','INV_GROUP_ERROR');
3906
3907 UPDATE MTL_TRANSACTIONS_INTERFACE MTI
3908 SET LAST_UPDATE_DATE = SYSDATE,
3909 LAST_UPDATED_BY = l_userid,
3910 LAST_UPDATE_LOGIN = l_loginid,
3911 PROGRAM_UPDATE_DATE = SYSDATE,
3912 PROCESS_FLAG = 3,
3913 LOCK_FLAG = 2,
3914 ERROR_CODE = substrb(l_error_code,1,240)
3915 WHERE TRANSACTION_HEADER_ID = l_header_id
3916 AND PROCESS_FLAG = 1
3917 AND TRANSACTION_BATCH_ID = l_Line_rec_Type.transaction_batch_id;
3918 --group error changes.
3919 l_current_batch_failed := TRUE;--Bug#5075521
3920 l_current_err_batch_id := l_Line_rec_Type.transaction_batch_id;--Bug#5075521
3921
3922 END IF;-- override_rsv_for_backflush
3923 END IF;--total org quantity
3924 END IF;--high level
3925 END IF; --250
3926
3927 ELSE --(neg_inv_rcpt = 1)
3928 IF (l_debug = 1) THEN
3929 inv_log_util.trace('Not Enough Qty: l_att,l_trx_qty:' || l_att||','||l_trx_qty,'INV_TXN_MANAGER_PUB', 9);
3930 END IF;
3931 FND_MESSAGE.set_name('INV','INV_NO_NEG_BALANCES');
3932 l_error_code := FND_MESSAGE.get;
3933 FND_MESSAGE.set_name('INV','INV_LESS_OR_EQUAL');
3934 FND_MESSAGE.set_token('ENTITY1','INV_QUANTITY',l_translate);
3935 FND_MESSAGE.set_token('ENTITY2','AVAIL_TO_TRANSACT',l_translate);
3936 l_error_exp := FND_MESSAGE.get;
3937 x_msg_data := l_error_exp;
3938 UPDATE MTL_TRANSACTIONS_INTERFACE
3939 SET LAST_UPDATE_DATE = SYSDATE,
3940 LAST_UPDATED_BY = l_userid,
3941 LAST_UPDATE_LOGIN = l_loginid,
3942 PROGRAM_UPDATE_DATE = SYSDATE,
3943 PROCESS_FLAG = 3,
3944 LOCK_FLAG = 2,
3945 ERROR_CODE = substrb(l_error_code,1,240),
3946 ERROR_EXPLANATION = substrb(l_error_exp,1,240)
3947 WHERE ROWID = l_temp_rowid
3948 AND PROCESS_FLAG = 1;
3949 --check for batch error
3950 loaderrmsg('INV_GROUP_ERROR','INV_GROUP_ERROR');
3951
3952 UPDATE MTL_TRANSACTIONS_INTERFACE MTI
3953 SET LAST_UPDATE_DATE = SYSDATE,
3954 LAST_UPDATED_BY = l_userid,
3955 LAST_UPDATE_LOGIN = l_loginid,
3956 PROGRAM_UPDATE_DATE = SYSDATE,
3957 PROCESS_FLAG = 3,
3958 LOCK_FLAG = 2,
3959 ERROR_CODE = substrb(l_error_code,1,240)
3960 WHERE TRANSACTION_HEADER_ID = l_header_id
3961 AND PROCESS_FLAG = 1
3962 AND TRANSACTION_BATCH_ID = l_Line_rec_Type.transaction_batch_id;
3963 -- group error changes.
3964 l_current_batch_failed := TRUE;--Bug#5075521
3965 l_current_err_batch_id := l_Line_rec_Type.transaction_batch_id;--Bug#5075521
3966 END IF;
3967 END IF;--check for att and trx qty
3968 END IF;--IF ((l_Line_rec_Type.wip_entity_type <> 10) OR
3969 -- update the qty
3970 -- Pawan 11th july Added - GME does not have transfer subinventory
3971 IF ( NOT l_current_batch_failed ) THEN --300
3972 IF (l_actid in (2,28)) then
3973 inv_quantity_tree_pub.update_quantities
3974 (p_api_version_number => 1.0,
3975 p_init_msg_lst => fnd_api.g_false,
3976 x_return_status => l_return_status,
3977 x_msg_count => l_msg_count,
3978 x_msg_data => l_msg_data,
3979 p_organization_id => l_org_id,
3980 p_inventory_item_id => l_item_id,
3981 p_tree_mode => 2,
3982 p_is_revision_control => l_revision_control,
3983 p_is_lot_control => l_lot_control,
3984 p_is_serial_control => FALSE,
3985 p_demand_source_type_id => nvl(l_srctypeid,-9999),
3986 p_demand_source_header_id => nvl(l_srcid,-9999),
3987 p_demand_source_line_id => nvl(l_srclineid,-9999),
3988 p_revision => l_rev,
3989 p_lot_number => l_lotnum,
3990 p_subinventory_code => l_xfrsub,
3991 p_locator_id => l_xlocid,
3992 p_primary_quantity => l_trx_qty,
3993 p_quantity_type => inv_quantity_tree_pvt.g_qoh,
3994 p_onhand_source => inv_quantity_tree_pvt.g_all_subs,
3995 x_qoh => l_qoh,
3996 x_rqoh => l_rqoh,
3997 x_qr => l_qr,
3998 x_qs => l_qs,
3999 x_att => l_att,
4000 x_atr => l_atr);
4001
4002
4003 IF l_return_status <> fnd_api.g_ret_sts_success THEN
4004 FND_MESSAGE.set_name('INV', 'INV_ERR_CREATETREE');
4005 FND_MESSAGE.set_token('ROUTINE','INV_QUANTITY_TREE_PUB.UPDATE_QUANTITIES');
4006 l_error_code:= fnd_message.get;
4007 l_error_exp :=l_msg_data;
4008 x_msg_data := l_msg_data;
4009 UPDATE MTL_TRANSACTIONS_INTERFACE
4010 SET LAST_UPDATE_DATE = SYSDATE,
4011 LAST_UPDATED_BY = l_userid,
4012 LAST_UPDATE_LOGIN = l_loginid,
4013 PROGRAM_UPDATE_DATE = SYSDATE,
4014 PROCESS_FLAG = 3,
4015 LOCK_FLAG = 2,
4016 ERROR_CODE = substrb(l_error_code,1,240),
4017 ERROR_EXPLANATION = substrb(l_error_exp,1,240)
4018 WHERE ROWID = l_temp_rowid
4019 AND PROCESS_FLAG = 1;
4020 --check for batch error
4021 loaderrmsg('INV_GROUP_ERROR','INV_GROUP_ERROR');
4022
4023 UPDATE MTL_TRANSACTIONS_INTERFACE MTI
4024 SET LAST_UPDATE_DATE = SYSDATE,
4025 LAST_UPDATED_BY = l_userid,
4026 LAST_UPDATE_LOGIN = l_loginid,
4027 PROGRAM_UPDATE_DATE = SYSDATE,
4028 PROCESS_FLAG = 3,
4029 LOCK_FLAG = 2,
4030 ERROR_CODE = substrb(l_error_code,1,240)
4031 WHERE TRANSACTION_HEADER_ID = l_header_id
4032 AND PROCESS_FLAG = 1
4033 AND TRANSACTION_BATCH_ID = l_Line_rec_Type.transaction_batch_id;
4034 -- group error changes.
4035 l_current_batch_failed := TRUE;--Bug#5075521
4036 l_current_err_batch_id := l_Line_rec_Type.transaction_batch_id;--Bug#5075521
4037 END IF;
4038 ELSE
4039 /* Jalaj Srivastava Bug 5232394
4040 update tree with correct sign.
4041 sign is derived from transaction_quantity */
4042
4043 inv_quantity_tree_pub.update_quantities
4044 (p_api_version_number => 1.0,
4045 p_init_msg_lst => fnd_api.g_false,
4046 x_return_status => l_return_status,
4047 x_msg_count => l_msg_count,
4048 x_msg_data => l_msg_data,
4049 p_organization_id => l_org_id,
4050 p_inventory_item_id => l_item_id,
4051 p_tree_mode => 2,
4052 p_is_revision_control => l_revision_control,
4053 p_is_lot_control => l_lot_control,
4054 p_is_serial_control => FALSE,
4055 p_demand_source_type_id => nvl(l_srctypeid,-9999),
4056 p_demand_source_header_id => nvl(l_srcid,-9999),
4057 p_demand_source_line_id => nvl(l_srclineid,-9999),
4058 p_revision => l_rev,
4059 p_lot_number => l_lotnum,
4060 p_subinventory_code => l_sub_code,
4061 p_locator_id => l_locid,
4062 p_primary_quantity => (sign(l_line_rec_type.transaction_quantity)*(l_trx_qty)),
4063 p_quantity_type => inv_quantity_tree_pvt.g_qoh,
4064 p_onhand_source => inv_quantity_tree_pvt.g_all_subs,
4065 x_qoh => l_qoh,
4066 x_rqoh => l_rqoh,
4067 x_qr => l_qr,
4068 x_qs => l_qs,
4069 x_att => l_att,
4070 x_atr => l_atr);
4071
4072 IF l_return_status <> fnd_api.g_ret_sts_success THEN
4073 FND_MESSAGE.set_name('INV', 'INV_ERR_CREATETREE');
4074 FND_MESSAGE.set_token('ROUTINE','INV_QUANTITY_TREE_PUB.UPDATE_QUANTITIES');
4075 l_error_code:= fnd_message.get;
4076 l_error_exp :=l_msg_data;
4077 x_msg_data := l_msg_data;
4078 UPDATE MTL_TRANSACTIONS_INTERFACE
4079 SET LAST_UPDATE_DATE = SYSDATE,
4080 LAST_UPDATED_BY = l_userid,
4081 LAST_UPDATE_LOGIN = l_loginid,
4082 PROGRAM_UPDATE_DATE = SYSDATE,
4083 PROCESS_FLAG = 3,
4084 LOCK_FLAG = 2,
4085 ERROR_CODE = substrb(l_error_code,1,240),
4086 ERROR_EXPLANATION = substrb(l_error_exp,1,240)
4087 WHERE ROWID = l_temp_rowid
4088 AND PROCESS_FLAG = 1;
4089 --check for batch error
4090 loaderrmsg('INV_GROUP_ERROR','INV_GROUP_ERROR');
4091
4092 UPDATE MTL_TRANSACTIONS_INTERFACE MTI
4093 SET LAST_UPDATE_DATE = SYSDATE,
4094 LAST_UPDATED_BY = l_userid,
4095 LAST_UPDATE_LOGIN = l_loginid,
4096 PROGRAM_UPDATE_DATE = SYSDATE,
4097 PROCESS_FLAG = 3,
4098 LOCK_FLAG = 2,
4099 ERROR_CODE = substrb(l_error_code,1,240)
4100 WHERE TRANSACTION_HEADER_ID = l_header_id
4101 AND PROCESS_FLAG = 1
4102 AND TRANSACTION_BATCH_ID = l_Line_rec_Type.transaction_batch_id;
4103 -- group error changes.
4104 l_current_batch_failed := TRUE;--Bug#5075521
4105 l_current_err_batch_id := l_Line_rec_Type.transaction_batch_id;--Bug#5075521
4106 ELSE
4107 --qty tree update was successful
4108 IF (l_debug = 1) THEN
4109 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);
4110 END IF;
4111 END IF;
4112 END IF;-- Pawan Added for IF (l_actid in (2,28))
4113 END IF; --300
4114 END IF; --150
4115 END IF; --If l_rsv_wip_entity_type <> inv_reservation_global.g_wip_source_type_cmroA
4116 --bug#13527319
4117 <<endofz1loop>>
4118 null;
4119 END LOOP;-- Loop Z1
4120 END IF;--l_current_err_batch_id is NULL..
4121 END IF; --l_srctyped =5
4122
4123 /*Bug:5209598. End of code*/
4124
4125
4126
4127 --Start of new code added as per the eIB TDD; Bug# 4348541
4128 DECLARE
4129 l_location_required_flag mtl_transaction_types.location_required_flag%TYPE;
4130 BEGIN
4131 l_location_required_flag := 'N';
4132 -- Call the inv_validate.check_location_required_setup procedure
4133 -- and pass l_line_rec_type.transaction_type_id as parameter to
4134 -- check if the Location has to be made mandatory. If this procedure
4135 -- returns 'Y' then check if the Location Code is specified or not.
4136 -- If it is not specified then error out the interface record.
4137 inv_validate.check_location_required_setup(
4138 p_transaction_type_id => l_line_rec_type.transaction_type_id,
4139 p_required_flag => l_location_required_flag);
4140
4141 IF l_location_required_flag = 'Y' AND
4142 l_line_rec_type.ship_to_location_id IS NULL THEN
4143
4144 FND_MESSAGE.SET_NAME('INV','INV_LOCATION_MANDATORY');
4145 l_error_code := FND_MESSAGE.GET;
4146
4147 UPDATE MTL_TRANSACTIONS_INTERFACE
4148 SET LAST_UPDATE_DATE = SYSDATE,
4149 LAST_UPDATED_BY = l_userid,
4150 LAST_UPDATE_LOGIN = l_loginid,
4151 PROGRAM_UPDATE_DATE = SYSDATE,
4152 PROCESS_FLAG = 3,
4153 LOCK_FLAG = 2,
4154 ERROR_CODE = SUBSTR (l_error_code, 1, 240),
4155 ERROR_EXPLANATION = SUBSTR (l_error_code, 1, 240)
4156 WHERE ROWID = l_line_rec_type.rowid
4157 AND PROCESS_FLAG = 1;
4158
4159 RAISE rollback_line_validation;
4160 END IF;
4161 END;
4162 --End of new code added as per the eIB TDD; Bug# 4348541
4163
4164 SAVEPOINT line_validation_svpt;
4165 fnd_message.set_name ('INV', 'INV_MOVE_TO_TEMP');
4166 fnd_message.set_token ('token', l_header_id);
4167 l_disp := fnd_message.get;
4168
4169 IF (l_debug = 1)
4170 THEN
4171 inv_log_util.TRACE (l_disp, 'INV_TXN_MANAGER_PUB', 9);
4172 END IF;
4173
4174 /* Insert into MMTT */
4175 /** Change for lOt Transactions **/
4176
4177 IF l_line_rec_type.transaction_source_type_id = 13
4178 AND l_line_rec_type.transaction_action_id IN (40, 41, 42)
4179 THEN
4180 IF (l_line_rec_type.transaction_action_id = 40)
4181 THEN
4182 IF (l_debug = 1)
4183 THEN
4184 inv_log_util.TRACE ('Checking for lot partial split'
4185 , 'INV_TXN_MANAGER_PUB'
4186 , 9
4187 );
4188 END IF;
4189 IF (NOT check_partial_split (l_line_rec_type.parent_id
4190 , l_index
4191 )
4192 )
4193 THEN
4194 l_error_exp := fnd_message.get;
4195
4196 IF (l_debug = 1)
4197 THEN
4198 inv_log_util.TRACE ( 'Error in Check_Partial_Split= '
4199 || l_error_exp
4200 , 'INV_TXN_MANAGER_PUB'
4201 , 9
4202 );
4203 END IF;
4204
4205 fnd_message.set_name ('INV', 'INV_INT_TMPXFRCODE');
4206 l_error_code := fnd_message.get;
4207 ROLLBACK TO line_validation_svpt;
4208
4209 UPDATE mtl_transactions_interface
4210 SET last_update_date = SYSDATE
4211 , last_updated_by = l_userid
4212 , last_update_login = l_loginid
4213 , program_update_date = SYSDATE
4214 , process_flag = 3
4215 , lock_flag = 2
4216 , ERROR_CODE = SUBSTR (l_error_code, 1, 240)
4217 , error_explanation = SUBSTR (l_error_exp, 1, 240)
4218 WHERE ROWID = l_line_rec_type.ROWID AND process_flag = 1;
4219
4220 RAISE rollback_line_validation;
4221 END IF;
4222 END IF;
4223 END IF; --J-dev
4224
4225
4226 /** end of changes for lot transactions **/
4227 --J dev, done as a bulk insert now. outside the
4228 --level loop.
4229 END IF;
4230 EXCEPTION
4231 WHEN rollback_line_validation
4232 THEN
4233 IF (l_debug = 1)
4234 THEN
4235 inv_log_util.TRACE
4236 ( 'Failed Interface ID : '
4237 || l_line_rec_type.transaction_interface_id
4238 || ' Item: '
4239 || l_line_rec_type.inventory_item_id
4240 || 'Org : '
4241 || l_line_rec_type.organization_id
4242 , 'INV_TXN_MANAGER_PUB'
4243 , 9
4244 );
4245 END IF;
4246
4247 batch_error := TRUE;
4248 WHEN OTHERS
4249 THEN
4250 batch_error := TRUE;
4251
4252 IF (l_debug = 1)
4253 THEN
4254 inv_log_util.TRACE
4255 ( 'Error in INV_TXN_MANAGER_PUB LOOP - rollback last transaction Interface ID '
4256 || l_line_rec_type.transaction_interface_id
4257 , 'INV_TXN_MANAGER_PUB'
4258 , 9
4259 );
4260 END IF;
4261
4262 ROLLBACK TO line_validation_svpt;
4263 END;
4264 END LOOP; -- endloop for AA1 (MTI)
4265
4266 /*Bug:5209598. Freeing the Tree created for reservation checks.*/
4267 IF (l_tree_id IS NOT NULL) THEN
4268 INV_QUANTITY_TREE_PVT.free_tree
4269 ( p_api_version_number => 1.0
4270 , p_init_msg_lst => fnd_api.g_false
4271 , x_return_status => l_return_status
4272 , x_msg_count => l_msg_count
4273 , x_msg_data => l_msg_data
4274 , p_tree_id => l_tree_id );
4275 END IF;
4276
4277 --J-dev check that all records for line validation are failed here.
4278
4279 --check for batch error at line validation
4280 loaderrmsg ('INV_GROUP_ERROR', 'INV_GROUP_ERROR');
4281
4282 UPDATE mtl_transactions_interface mti
4283 SET last_update_date = SYSDATE
4284 , last_updated_by = l_userid
4285 , last_update_login = l_loginid
4286 , program_update_date = SYSDATE
4287 , process_flag = 3
4288 , lock_flag = 2
4289 , ERROR_CODE = SUBSTRB (l_error_code, 1, 240)
4290 WHERE transaction_header_id = l_header_id
4291 AND process_flag = 1
4292 AND EXISTS
4293 (SELECT 'Y'
4294 FROM mtl_transactions_interface mti2
4295 WHERE mti2.transaction_header_id = l_header_id
4296 AND mti2.process_flag = 3
4297 AND mti2.error_code IS NOT NULL
4298 AND mti2.transaction_batch_id = mti.transaction_batch_id);
4299
4300 /* Commented following and added EXISTS clause above for bug 8444982
4301 AND transaction_batch_id IN (
4302 SELECT DISTINCT mti2.transaction_batch_id
4303 FROM mtl_transactions_interface mti2
4304 WHERE mti2.transaction_header_id = l_header_id
4305 AND mti2.process_flag = 3
4306 AND mti2.ERROR_CODE IS NOT NULL);
4307 */
4308 -- group error changes.
4309 IF fnd_api.to_boolean (p_commit)
4310 THEN
4311 COMMIT WORK; /* Commit after LineValidation all MTI records */
4312 END IF;
4313
4314 --check if all records have been failed by the Line Validation
4315 BEGIN
4316 SELECT transaction_source_type_id
4317 INTO l_source_type_id
4318 FROM mtl_transactions_interface
4319 WHERE transaction_header_id = l_header_id
4320 AND process_flag = 1
4321 AND ROWNUM < 2;
4322 EXCEPTION
4323 WHEN NO_DATA_FOUND
4324 THEN
4325 x_return_status := fnd_api.g_ret_sts_error;
4326 x_msg_data := 'All records failed after line validation';
4327
4328 IF (l_debug = 1)
4329 THEN
4330 inv_log_util.TRACE ('All records failed after line validation'
4331 , 'INV_TXN_MANAGER_PUB'
4332 , 1
4333 );
4334 END IF;
4335
4336 RETURN -1;
4337 END;
4338
4339 --J-dev
4340 /******* Group Validation for WIP records *******************/
4341 /* This WIP API could potentially error some records in MTI. If any records
4342 /* have been errored, they would be stamped with error-code/explanation */
4343 -- Bug 6996032. Added NVL condition to l_wip_entity_type
4344 IF (l_srctypeid = 5) and (NVL(l_wip_entity_type,-1) <> 10 )-- Pawan added for l_wip_entity_type
4345 THEN
4346 wip_mti_pub.postinvwipvalidation (p_txnheaderid => l_header_id
4347 , x_returnstatus => x_return_status
4348 );
4349
4350 IF (x_return_status = fnd_api.g_ret_sts_success)
4351 THEN
4352 IF (l_debug = 1)
4353 THEN
4354 inv_log_util.TRACE ('Success from:!!postInvWIPValid'
4355 , 'INV_TXN_MANAGER_PUB'
4356 , 1
4357 );
4358 END IF;
4359
4360 --J-dev check that all records for line validation are failed here.
4361 --bug 3727791
4362 --check for batch error at line validation
4363 loaderrmsg ('INV_GROUP_ERROR', 'INV_GROUP_ERROR');
4364
4365 UPDATE mtl_transactions_interface mti
4366 SET last_update_date = SYSDATE
4367 , last_updated_by = l_userid
4368 , last_update_login = l_loginid
4369 , program_update_date = SYSDATE
4370 , process_flag = 3
4371 , lock_flag = 2
4372 , ERROR_CODE = SUBSTRB (l_error_code, 1, 240)
4373 WHERE transaction_header_id = l_header_id
4374 AND process_flag = 1
4375 AND EXISTS
4376 (SELECT 'Y'
4377 FROM mtl_transactions_interface mti2
4378 WHERE mti2.transaction_header_id = l_header_id
4379 AND mti2.process_flag = 3
4380 AND mti2.error_code IS NOT NULL
4381 AND mti2.transaction_batch_id = mti.transaction_batch_id);
4382
4383 /* Commented following and added EXISTS clause above for bug 8444982
4384 AND transaction_batch_id IN (
4385 SELECT DISTINCT mti2.transaction_batch_id
4386 FROM mtl_transactions_interface mti2
4387 WHERE mti2.transaction_header_id = l_header_id
4388 AND mti2.process_flag = 3
4389 AND mti2.ERROR_CODE IS NOT NULL);
4390 */
4391 --group error changes.
4392 IF fnd_api.to_boolean (p_commit)
4393 THEN
4394 COMMIT WORK; /* Commit after PostInvWip all MTI records */
4395 END IF;
4396
4397 --check if all records have been failed by the wip API.
4398 BEGIN
4399 SELECT transaction_source_type_id
4400 INTO l_source_type_id
4401 FROM mtl_transactions_interface
4402 WHERE transaction_header_id = l_header_id
4403 AND process_flag = 1
4404 AND ROWNUM < 2;
4405 EXCEPTION
4406 WHEN NO_DATA_FOUND
4407 THEN
4408 x_return_status := fnd_api.g_ret_sts_error;
4409
4410 /* Bug 3656824
4411 Replaced the hard coded message with the last message in the error stack from WIP validation*/
4412 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
4413 RETURN -1;
4414 END;
4415 ELSE
4416 IF (l_debug = 1)
4417 THEN
4418 inv_log_util.TRACE ('Failure from:!!postInvWIPProcessing'
4419 , 'INV_TXN_MANAGER_PUB'
4420 , 1
4421 );
4422 END IF;
4423
4424 RAISE fnd_api.g_exc_unexpected_error;
4425 END IF; --check for success
4426 END IF; --l_srctypeid = 5
4427
4428 -- ADD tmp Insert here. In case of an error raise an exception.
4429 --J-dev
4430
4431 /*Change for supporting the lot transactions for a lot serial item
4432 *tmpinsert will insert the data into the temp tables for transactions
4433 *other than split/merge/translate. Only if it is a success we move on to
4434 *tmpinsert2 whihc handles the three transactions
4435 */
4436 IF (l_debug = 1)
4437 THEN
4438 inv_log_util.TRACE ('Calling tmpinsert'
4439 , 'INV_TXN_MANAGER_PUB'
4440 , 9
4441 );
4442 END IF;
4443
4444 IF (NOT tmpinsert (l_header_id))
4445 THEN
4446 l_error_exp := fnd_message.get;
4447
4448 IF (l_debug = 1)
4449 THEN
4450 inv_log_util.TRACE ('Error in tmpinsert=' || l_error_exp
4451 , 'INV_TXN_MANAGER_PUB'
4452 , 9
4453 );
4454 END IF;
4455
4456 fnd_message.set_name ('INV', 'INV_INT_TMPXFRCODE');
4457 l_error_code := fnd_message.get;
4458 RAISE fnd_api.g_exc_unexpected_error;
4459 ELSE
4460 BEGIN
4461 IF(l_debug = 1) THEN
4462 inv_log_util.TRACE ( 'Calling tmpinsert2'
4463 , 'INV_TXN_MANAGER_PUB'
4464 , 9
4465 );
4466 END IF;
4467 inv_txn_manager_grp.tmpinsert2(
4468 x_return_status => x_return_status
4469 , x_msg_count => x_msg_count
4470 , x_msg_data => x_msg_data
4471 , x_validation_status => l_validation_status
4472 , p_header_id => l_header_id);
4473 IF(l_debug = 1) THEN
4474 inv_log_util.TRACE ( 'After tmpinsert2'
4475 , 'INV_TXN_MANAGER_PUB'
4476 , 9
4477 );
4478 END IF;
4479 EXCEPTION
4480 WHEN OTHERS THEN
4481 inv_log_util.TRACE ( 'tmpinsert2 raised exception '
4482 , 'INV_TXN_MANAGER_PUB'
4483 , 9
4484 );
4485 fnd_message.set_name ('INV', 'INV_INT_TMPXFRCODE');
4486 l_error_code := fnd_message.get;
4487 RAISE fnd_api.g_exc_unexpected_error;
4488 END;
4489
4490 IF(x_return_status <> fnd_api.g_ret_sts_success OR
4491 l_validation_status <> 'Y') THEN
4492 inv_log_util.TRACE ( 'tmpinsert2 failed..returned with error '
4493 , 'INV_TXN_MANAGER_PUB'
4494 , 9
4495 );
4496 RAISE fnd_api.g_exc_error;
4497 END IF;
4498 END IF;
4499
4500 /*Bug:5276191.Start of code changes. */
4501
4502 FOR p_mmtt IN c_mmtt LOOP
4503
4504 IF (p_mmtt.transaction_action_id in (40,42) )then
4505
4506 BEGIN
4507 SELECT serial_number_control_code
4508 INTO l_serial_control
4509 FROM MTL_SYSTEM_ITEMS
4510 WHERE inventory_item_id= p_mmtt.inventory_item_id
4511 AND organization_id = p_mmtt.organization_id;
4512 EXCEPTION
4513 WHEN OTHERS THEN
4514 inv_log_util.TRACE ('Exception in getting serial control code'||Sqlerrm,'INV_TXN_MANAGER_PUB ', 9);
4515 RAISE fnd_api.g_exc_unexpected_error;
4516 END;
4517
4518 IF (l_serial_control IN (2,5)) THEN
4519
4520 IF(p_mmtt.locator_id IS NOT NULL) then
4521 BEGIN
4522 SELECT project_id INTO l_from_project_id
4523 FROM mtl_item_locations
4524 WHERE inventory_location_id = p_mmtt.locator_id
4525 AND organization_id = p_mmtt.organization_id;
4526 EXCEPTION
4527 WHEN OTHERS THEN
4528 IF (l_debug = 1) THEN
4529 inv_log_util.TRACE ('exception in getting from project: ' || Sqlerrm, 'INV_TXN_MANAGER_PUB', 9);
4530 END IF;
4531 RAISE fnd_api.g_exc_unexpected_error;
4532 END;
4533 END IF;
4534
4535 inv_cost_group_update.cost_group_update
4536 (p_transaction_rec => p_mmtt,
4537 p_fob_point => null,
4538 p_transfer_wms_org => FALSE,
4539 p_tfr_primary_cost_method => null,
4540 p_tfr_org_cost_group_id => null,
4541 p_from_project_id => l_from_project_id,
4542 p_to_project_id => null,
4543 x_return_status => x_return_status,
4544 x_msg_count => x_msg_count,
4545 x_msg_data => x_msg_data);
4546
4547 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) then
4548 l_error_exp := x_msg_data;
4549 RAISE fnd_api.g_exc_error;
4550 END IF;
4551
4552 END IF; --l_serial_control_code <>1
4553 END IF;--action_id in (40,42)
4554
4555 END LOOP; --c_mmtt loop
4556 /*Bug:5276191.End of code changes. */
4557
4558
4559
4560 --- End J dev
4561 IF fnd_api.to_boolean (p_commit)
4562 THEN
4563 COMMIT WORK; /* Commit after validating all MTI records */
4564 END IF;
4565
4566 /* Delete the errored out flow schedules */
4567 IF (inv_txn_manager_grp.gi_flow_schedule <> 0)
4568 THEN
4569 wip_flow_utilities.delete_flow_schedules (l_header_id);
4570 END IF;
4571
4572 SELECT COUNT (*)
4573 INTO l_midtotrows
4574 FROM mtl_material_transactions_temp
4575 WHERE transaction_header_id = l_header_id AND process_flag = 'Y';
4576
4577 DELETE FROM mtl_material_transactions_temp
4578 WHERE transaction_header_id = l_header_id
4579 AND shippable_flag = 'N'
4580 AND process_flag = 'Y';
4581
4582 IF (l_debug = 1)
4583 THEN
4584 inv_log_util.TRACE ( 'Goint for rows in MMTT. rcnt = '
4585 || l_midtotrows
4586 || ',hdrid='
4587 || l_header_id
4588 , 'INV_TXN_MANAGER_PUB'
4589 , 9
4590 );
4591 END IF;
4592
4593 done := FALSE;
4594 FIRST := TRUE;
4595
4596 WHILE (NOT done)
4597 LOOP
4598 SAVEPOINT process_trx_save;
4599
4600 IF (FIRST)
4601 THEN
4602 fnd_message.set_name ('INV', 'INV_CALL_PROC');
4603 fnd_message.set_token ('token1', l_header_id);
4604 fnd_message.set_token ('token2', l_totrows);
4605 l_disp := fnd_message.get;
4606
4607 IF (l_debug = 1)
4608 THEN
4609 inv_log_util.TRACE (l_disp, 'INV_TXN_MANAGER_PUB', 9);
4610 END IF;
4611
4612 --FND_MESSAGE.set_name('INV','INV_RETURN_PROC');
4613 --l_disp := FND_MESSAGE.get;
4614 --inv_log_util.trace(l_disp, 'INV_TXN_MANAGER_PUB',9);
4615 SELECT COUNT (*)
4616 INTO l_totrows
4617 FROM mtl_material_transactions_temp
4618 WHERE transaction_header_id = l_header_id AND process_flag = 'Y';
4619
4620 x_trans_count := l_totrows;
4621
4622 IF (l_totrows = 0)
4623 THEN
4624 fnd_message.set_name ('INV', 'INV_PROC_WARN');
4625 l_disp := fnd_message.get;
4626
4627 IF (l_debug = 1)
4628 THEN
4629 inv_log_util.TRACE (l_disp || ' totrows = 0'
4630 , 'INV_TXN_MANAGER_PUB'
4631 , 9
4632 );
4633 END IF;
4634
4635 RETURN -1;
4636 END IF;
4637
4638 IF fnd_api.to_boolean (p_commit)
4639 THEN
4640 COMMIT WORK;
4641 ELSE
4642 SAVEPOINT process_trx_save;
4643 END IF;
4644 END IF;
4645
4646 /*WIP J-dev Add another condtion in the if
4647 /* statement below. if WIP.J is not installed call
4648 /* wip_mtlTempProc_grp()...else call process_lpn_trx()*/
4649 -- If transactions are of type WIP, then call the WIP API. This
4650 -- API does the WIP pre-processing before calling process_lpn_trx
4651 IF ( l_srctypeid = 5
4652 AND wip_constants.dmf_patchset_level <
4653 wip_constants.dmf_patchset_j_value
4654 )
4655 THEN
4656 wip_mtltempproc_grp.processtemp
4657 (p_initmsglist => fnd_api.g_false
4658 , p_processinv => fnd_api.g_true
4659 , -- call INV TM after WIP logic
4660 p_txnhdrid => l_header_id
4661 , x_returnstatus => l_return_status
4662 , x_errormsg => l_msg_data
4663 );
4664
4665 IF (l_return_status <> fnd_api.g_ret_sts_success)
4666 THEN
4667 IF (l_debug = 1)
4668 THEN
4669 inv_log_util.TRACE ('Failure from WIP processTemp!!'
4670 , 'INV_TXN_MANAGER_PUB'
4671 , 1
4672 );
4673 END IF;
4674
4675 l_result := -1;
4676 END IF;
4677 ELSE
4678 --Bug #4338316
4679 --Pass the p_commit value to the TM
4680 l_result :=
4681 inv_lpn_trx_pub.process_lpn_trx (p_trx_hdr_id => l_header_id
4682 , p_commit => p_commit
4683 , x_proc_msg => l_msg_data
4684 , p_proc_mode => 1
4685 , p_process_trx => fnd_api.g_true
4686 , p_atomic => fnd_api.g_false
4687 );
4688
4689
4690 END IF;
4691
4692 IF (l_result <> 0)
4693 THEN
4694 l_error_exp := l_msg_data;
4695 x_msg_data := l_msg_data;
4696 x_return_status := l_return_status;
4697 fnd_message.set_name ('INV', 'INV_INT_PROCCODE');
4698 l_error_code := fnd_message.get;
4699
4700 IF (l_debug = 1)
4701 THEN
4702 inv_log_util.TRACE ( 'PROCESS_LPN_TRX failed for header_id '
4703 || l_header_id
4704 , 'INV_TXN_MANAGER_PUB'
4705 , 1
4706 );
4707 inv_log_util.TRACE ('Error.... ' || l_error_exp
4708 , 'INV_TXN_MANAGER_PUB'
4709 , 9
4710 );
4711 END IF;
4712
4713 -- Bug 5748351: Deleting MSNT/MTLT/MMTT for the headerId, in case they are still present and did not
4714 -- get deleted in TM.
4715 delete from mtl_serial_numbers_temp
4716 where transaction_temp_id in (
4717 select mmtt.transaction_temp_id
4718 from mtl_material_transactions_temp mmtt
4719 where mmtt.transaction_header_id = l_header_id );
4720
4721 delete from mtl_serial_numbers_temp
4722 where transaction_temp_id in (
4723 select mtlt.serial_transaction_temp_id
4724 from mtl_transaction_lots_temp mtlt
4725 where mtlt.transaction_temp_id in (
4726 select mmtt.transaction_temp_id
4727 from mtl_material_transactions_temp mmtt
4728 where mmtt.transaction_header_id = l_header_id));
4729
4730 DELETE from mtl_transaction_lots_temp
4731 where transaction_temp_id in
4732 (select mmtt.transaction_temp_id
4733 from MTL_MATERIAL_TRANSACTIONS_TEMP mmtt
4734 WHERE mmtt.TRANSACTION_HEADER_ID = l_header_id );
4735
4736 DELETE FROM MTL_MATERIAL_TRANSACTIONS_TEMP
4737 WHERE TRANSACTION_HEADER_ID = l_header_id;
4738
4739 IF (l_debug = 1) THEN
4740 inv_log_util.trace('Deleted MSNT/MTLT/MMTT for header_id ' || l_header_id, 'INV_TXN_MANAGER_PUB',1);
4741 END IF;
4742
4743 -- End of change for bug 5748351
4744
4745 IF fnd_api.to_boolean (p_commit)
4746 THEN
4747 COMMIT WORK;
4748 END IF;
4749
4750 RETURN -1;
4751 END IF;
4752
4753 IF (l_debug = 1)
4754 THEN
4755 inv_log_util.TRACE ('After process_lpn_trx without errors'
4756 , 'INV_TXN_MANAGER_PUB'
4757 , 9
4758 );
4759 END IF;
4760
4761 IF fnd_api.to_boolean (p_commit)
4762 THEN
4763 COMMIT WORK;
4764 END IF;
4765
4766 IF (FIRST)
4767 THEN
4768 IF (l_debug = 1)
4769 THEN
4770 inv_log_util.TRACE ('Calling bflushchk', 'INV_TXN_MANAGER_PUB'
4771 , 9);
4772 END IF;
4773
4774 IF (NOT bflushchk (l_header_id))
4775 THEN
4776 l_error_code := fnd_message.get;
4777
4778 IF (l_debug = 1)
4779 THEN
4780 inv_log_util.TRACE ( 'Error in bflushchk header_id:'
4781 || l_header_id
4782 || ' - '
4783 || l_error_code
4784 , 'INV_TXN_MANAGER_PUB'
4785 , 9
4786 );
4787 END IF;
4788
4789 --ROLLBACK TO process_trx_save;
4790 RETURN -1;
4791 END IF;
4792
4793 IF (l_header_id <> -1)
4794 THEN
4795 fnd_message.set_name ('INV', 'INV_BFLUSH_PROC');
4796 l_disp := fnd_message.get;
4797
4798 IF (l_debug = 1)
4799 THEN
4800 inv_log_util.TRACE (l_disp, 'INV_TXN_MANAGER_PUB', 9);
4801 END IF;
4802
4803 SELECT COUNT (*)
4804 INTO l_totrows
4805 FROM mtl_material_transactions_temp
4806 WHERE transaction_header_id = l_header_id AND process_flag = 'Y';
4807
4808 IF (l_debug = 1)
4809 THEN
4810 inv_log_util.TRACE ('totrows is ' || l_totrows
4811 , 'INV_TXN_MANAGER_PUB'
4812 , 9
4813 );
4814 END IF;
4815
4816 IF (l_totrows > 200)
4817 THEN
4818 UPDATE mtl_material_transactions_temp
4819 SET transaction_header_id = (-1) * l_header_id
4820 WHERE transaction_header_id = l_header_id
4821 AND process_flag = 'Y';
4822
4823 UPDATE mtl_material_transactions_temp
4824 SET transaction_header_id = ABS (l_header_id)
4825 WHERE transaction_header_id = (-1) * (l_header_id)
4826 AND process_flag = 'Y'
4827 AND ROWNUM < 201;
4828 END IF;
4829
4830 fnd_message.set_name ('INV', 'INV_CALL_PROC');
4831 fnd_message.set_token ('token1', l_header_id);
4832 fnd_message.set_token ('token2', l_totrows);
4833 l_disp := fnd_message.get;
4834
4835 IF (l_debug = 1)
4836 THEN
4837 inv_log_util.TRACE (l_disp, 'INV_TXN_MANAGER_PUB', 9);
4838 END IF;
4839 ELSE
4840 done := TRUE;
4841 FIRST := FALSE;
4842 END IF;
4843 ELSE
4844 UPDATE mtl_material_transactions_temp
4845 SET transaction_header_id = ABS (l_header_id)
4846 WHERE transaction_header_id = (-1) * (l_header_id)
4847 AND process_flag = 'Y'
4848 AND ROWNUM < 201;
4849
4850 IF SQL%NOTFOUND
4851 THEN
4852 fnd_message.set_name ('INV', 'INV_RETURN_PROC');
4853 l_disp := fnd_message.get;
4854
4855 IF (l_debug = 1)
4856 THEN
4857 inv_log_util.TRACE (l_disp, 'INV_TXN_MANAGER_PUB', 9);
4858 END IF;
4859
4860 done := TRUE;
4861 END IF;
4862 END IF;
4863 END LOOP;
4864
4865 IF (l_initotrows > l_midtotrows)
4866 THEN
4867 fnd_message.set_name ('INV', 'INV_MGR_WARN');
4868 l_disp := fnd_message.get;
4869
4870 IF (l_debug = 1)
4871 THEN
4872 inv_log_util.TRACE (l_disp, 'INV_TXN_MANAGER_PUB', 9);
4873 inv_log_util.TRACE ( l_initotrows
4874 - l_midtotrows
4875 || ' Transactions did not pass validation'
4876 , 'INV_TXN_MANAGER_PUB'
4877 , 9
4878 );
4879 END IF;
4880
4881 RETURN -1;
4882 ELSE
4883 RETURN 0;
4884 END IF;
4885 END IF;
4886
4887 RETURN 0;
4888 EXCEPTION
4889 WHEN OTHERS
4890 THEN
4891 IF (l_debug = 1)
4892 THEN
4893 inv_log_util.TRACE ('*** SQL error ' || SUBSTR (SQLERRM, 1, 200)
4894 , 'INV_TXN_MANAGER_PUB'
4895 , 9
4896 );
4897 END IF;
4898
4899
4900 fnd_message.set_name ('INV', 'INV_INT_SQLCODE');
4901 l_error_code := fnd_message.get;
4902
4903 IF NOT fnd_api.to_boolean (p_commit)
4904 THEN
4905 ROLLBACK TO process_transactions_svpt;
4906 ELSE
4907 ROLLBACK WORK;
4908 END IF;
4909
4910 UPDATE mtl_transactions_interface
4911 SET last_update_date = SYSDATE
4912 , last_updated_by = l_userid
4913 , last_update_login = l_loginid
4914 , program_update_date = SYSDATE
4915 , process_flag = 3
4916 , lock_flag = 2
4917 , ERROR_CODE = SUBSTRB (l_error_code, 1, 240)
4918 , error_explanation = SUBSTRB (l_error_exp, 1, 240)
4919 WHERE transaction_header_id = l_header_id AND process_flag = 1;
4920
4921 IF fnd_api.to_boolean (p_commit)
4922 THEN
4923 COMMIT WORK;
4924 END IF;
4925
4926 RETURN -1;
4927 END process_transactions;
4928
4929 /******************************************************************
4930 *
4931 * Name: insert_relief
4932 * Description:
4933 * Creates a row in MRP_RELIEF_INTERFACE with the values it's passed.
4934 * This process was taken from mrlpr1.ppc to facilitate PLtion of PL/SQL TM API
4935 *
4936 ******************************************************************/
4937 FUNCTION insert_relief (
4938 p_new_order_qty NUMBER
4939 , p_new_order_date DATE
4940 , p_old_order_qty NUMBER
4941 , p_old_order_date DATE
4942 , p_item_id NUMBER
4943 , p_org_id NUMBER
4944 , p_disposition_id NUMBER
4945 , p_user_id NUMBER
4946 , p_line_num VARCHAR2
4947 , p_relief_type NUMBER
4948 , p_disposition VARCHAR2
4949 , p_demand_class VARCHAR2
4950 )
4951 RETURN BOOLEAN
4952 IS
4953 BEGIN
4954 IF (p_relief_type = mds_relief)
4955 THEN
4956 IF (p_disposition <> r_sales_order)
4957 THEN
4958 fnd_message.set_name ('MRP', 'GEN-invalid entity');
4959 fnd_message.set_token ('ENTITY', 'disposition');
4960 fnd_message.set_token ('VALUE', p_disposition);
4961 RETURN (FALSE);
4962 END IF;
4963 ELSE
4964 IF (p_relief_type = mps_relief)
4965 THEN
4966 IF (p_disposition <> r_work_order)
4967 AND (p_disposition <> r_purch_order)
4968 THEN
4969 fnd_message.set_name ('MRP', 'GEN-invalid entity');
4970 fnd_message.set_token ('ENTITY', 'disposition');
4971 fnd_message.set_token ('VALUE', p_disposition);
4972 RETURN (FALSE);
4973 END IF;
4974 ELSE
4975 fnd_message.set_name ('MRP', 'GEN-invalid entity');
4976 fnd_message.set_token ('ENTITY', 'relief_type');
4977 fnd_message.set_token ('VALUE', p_relief_type);
4978 RETURN (FALSE);
4979 END IF;
4980 END IF;
4981
4982 INSERT INTO mrp_relief_interface
4983 (transaction_id
4984 , inventory_item_id
4985 , organization_id
4986 , relief_type
4987 , disposition_type
4988 , last_update_date
4989 , last_updated_by
4990 , creation_date
4991 , created_by
4992 , last_update_login
4993 , new_order_quantity
4994 , new_order_date
4995 , old_order_quantity
4996 , old_order_date
4997 , disposition_id
4998 , demand_class
4999 , process_status
5000 , line_num
5001 )
5002 VALUES (mrp_relief_interface_s.NEXTVAL
5003 , p_item_id
5004 , p_org_id
5005 , p_relief_type
5006 , p_disposition
5007 , SYSDATE
5008 , p_user_id
5009 , SYSDATE
5010 , p_user_id
5011 , -1
5012 , p_new_order_qty
5013 , p_new_order_date
5014 , p_old_order_qty
5015 , p_old_order_date
5016 , p_disposition_id
5017 , p_demand_class
5018 , to_be_processed
5019 , p_line_num
5020 );
5021
5022 RETURN (TRUE);
5023 EXCEPTION
5024 WHEN OTHERS
5025 THEN
5026 IF (l_debug = 1)
5027 THEN
5028 inv_log_util.TRACE ('Error in insert_relief'
5029 , 'INV_TXN_MANAGER_PUB'
5030 , 9
5031 );
5032 inv_log_util.TRACE ('SQL : ' || SUBSTR (SQLERRM, 1, 200)
5033 , 'INV_TXN_MANAGER_PUB'
5034 , '9'
5035 );
5036 END IF;
5037
5038 RETURN (FALSE);
5039 END insert_relief;
5040
5041 /******************************************************************
5042 | Name: mrp_ship_order
5043 | Description:
5044 | Creates a row in MRP_RELIEF_INTERFACE with the values it's passed.
5045 ******************************************************************/
5046 FUNCTION mrp_ship_order (
5047 p_disposition_id NUMBER
5048 , p_inv_item_id NUMBER
5049 , p_quantity NUMBER
5050 , p_last_updated_by NUMBER
5051 , p_org_id NUMBER
5052 , p_line_num VARCHAR2
5053 , p_shipment_date DATE
5054 , p_demand_class VARCHAR2
5055 )
5056 RETURN BOOLEAN
5057 AS
5058 BEGIN
5059 IF (NOT insert_relief (p_quantity
5060 , p_shipment_date
5061 , 0
5062 , NULL
5063 , p_inv_item_id
5064 , p_org_id
5065 , p_disposition_id
5066 , p_last_updated_by
5067 , p_line_num
5068 , mds_relief
5069 , r_sales_order
5070 , p_demand_class
5071 )
5072 )
5073 THEN
5074 RETURN (FALSE);
5075 ELSE
5076 RETURN (TRUE);
5077 END IF;
5078 EXCEPTION
5079 WHEN OTHERS
5080 THEN
5081 IF (l_debug = 1)
5082 THEN
5083 inv_log_util.TRACE ('Error in mrp_ship_order'
5084 , 'INV_TXN_MANAGER_PUB'
5085 , 9
5086 );
5087 inv_log_util.TRACE ('SQL : ' || SUBSTR (SQLERRM, 1, 200)
5088 , 'INV_TXN_MANAGER_PUB'
5089 , '9'
5090 );
5091 END IF;
5092
5093 RETURN (FALSE);
5094 END mrp_ship_order;
5095
5096 /******************************************************************
5097 *
5098 * post_temp_validation()
5099 *
5100 ******************************************************************/
5101 FUNCTION post_temp_validation (
5102 p_line_rec_type line_rec_type
5103 , p_val_req NUMBER
5104 , p_userid NUMBER
5105 , p_flow_schedule NUMBER
5106 , p_lot_number VARCHAR2 -- Added for 4377625
5107 )
5108 RETURN BOOLEAN
5109 IS
5110 CURSOR z1 (p_flow_sch NUMBER)
5111 IS
5112 SELECT p_line_rec_type.ROWID
5113 , p_line_rec_type.inventory_item_id
5114 , p_line_rec_type.revision
5115 , p_line_rec_type.organization_id
5116 , p_line_rec_type.subinventory_code
5117 , p_line_rec_type.locator_id
5118 , ABS (p_line_rec_type.primary_quantity)
5119 , NULL
5120 , p_line_rec_type.transaction_source_type_id
5121 , p_line_rec_type.transaction_action_id
5122 ,p_line_rec_type.TRANSACTION_TYPE_ID /*Bug:4866991*/
5123 , p_line_rec_type.transaction_source_id
5124 , p_line_rec_type.transaction_source_name
5125 , TO_CHAR (p_line_rec_type.source_line_id)
5126 , msi.revision_qty_control_code
5127 , msi.lot_control_code
5128 , DECODE (p_line_rec_type.transaction_action_id
5129 , 2, p_line_rec_type.transfer_subinventory
5130 , 28, p_line_rec_type.transfer_subinventory
5131 , NULL
5132 )
5133 , p_line_rec_type.transfer_locator
5134 , p_line_rec_type.transaction_date
5135 , mp.negative_inv_receipt_code
5136 FROM mtl_parameters mp, mtl_system_items msi
5137 WHERE mp.organization_id = p_line_rec_type.organization_id
5138 -- AND MP.NEGATIVE_INV_RECEIPT_CODE = 2
5139 AND p_line_rec_type.process_flag = 1
5140 -- AND p_line_rec_type.SHIPPABLE_FLAG='Y'
5141 AND msi.lot_control_code = 1
5142 AND ( ( p_flow_sch <> 1
5143 AND p_line_rec_type.transaction_action_id IN
5144 (1, 2, 3, 21, 32, 34, 5)
5145 )
5146 OR (p_flow_sch = 1
5147 AND p_line_rec_type.transaction_action_id = 32
5148 )
5149 )
5150 AND msi.organization_id = mp.organization_id
5151 AND msi.organization_id = p_line_rec_type.organization_id
5152 AND msi.inventory_item_id = p_line_rec_type.inventory_item_id
5153 UNION
5154 SELECT p_line_rec_type.ROWID
5155 , p_line_rec_type.inventory_item_id
5156 , p_line_rec_type.revision
5157 , p_line_rec_type.organization_id
5158 , p_line_rec_type.subinventory_code
5159 , p_line_rec_type.locator_id
5160 , ABS (mtli.primary_quantity)
5161 , mtli.lot_number
5162 , p_line_rec_type.transaction_source_type_id
5163 , p_line_rec_type.transaction_action_id
5164 ,p_line_rec_type.TRANSACTION_TYPE_ID /*Bug:4866991*/
5165 , p_line_rec_type.transaction_source_id
5166 , p_line_rec_type.transaction_source_name
5167 , TO_CHAR (p_line_rec_type.source_line_id)
5168 , msi.revision_qty_control_code
5169 , msi.lot_control_code
5170 , DECODE (p_line_rec_type.transaction_action_id
5171 , 2, p_line_rec_type.transfer_subinventory
5172 , 28, p_line_rec_type.transfer_subinventory
5173 , 5, p_line_rec_type.transfer_subinventory
5174 , NULL
5175 )
5176 , p_line_rec_type.transfer_locator
5177 , p_line_rec_type.transaction_date
5178 , mp.negative_inv_receipt_code
5179 FROM mtl_transaction_lots_interface mtli
5180 , mtl_parameters mp
5181 , mtl_system_items msi
5182 WHERE mp.organization_id = p_line_rec_type.organization_id
5183 -- AND MP.NEGATIVE_INV_RECEIPT_CODE = 2
5184 -- AND p_line_rec_type.SHIPPABLE_FLAG='Y'
5185 AND mtli.transaction_interface_id =
5186 p_line_rec_type.transaction_interface_id
5187 AND p_line_rec_type.process_flag = 1
5188 AND ts_default <> ts_save_only
5189 AND msi.lot_control_code = 2
5190 AND ( ( p_flow_sch <> 1
5191 AND p_line_rec_type.transaction_action_id IN
5192 (1, 2, 3, 21, 32, 34, 5)
5193 )
5194 OR (p_flow_sch = 1
5195 AND p_line_rec_type.transaction_action_id = 32
5196 )
5197 )
5198 AND msi.organization_id = mp.organization_id
5199 AND msi.organization_id = p_line_rec_type.organization_id
5200 AND msi.inventory_item_id = p_line_rec_type.inventory_item_id
5201 AND MTLI.LOT_NUMBER = NVL(p_lot_number, MTLI.LOT_NUMBER); -- Added for 4377625
5202
5203 CURSOR c1
5204 IS
5205 SELECT a.organization_id
5206 , a.inventory_item_id
5207 , NVL (a.transaction_source_id, 0)
5208 , a.transaction_source_type_id
5209 , a.trx_source_delivery_id
5210 , a.trx_source_line_id
5211 , a.revision
5212 , DECODE (c.lot_control_code, 2, b.lot_number, a.lot_number)
5213 , a.subinventory_code
5214 , a.locator_id
5215 , DECODE (c.lot_control_code
5216 , 2, ABS (NVL (b.primary_quantity, 0))
5217 , a.primary_quantity * (-1)
5218 )
5219 , a.transaction_source_name
5220 , a.transaction_date
5221 , a.content_lpn_id
5222 FROM mtl_system_items c
5223 , mtl_transaction_lots_temp b
5224 , mtl_material_transactions_temp a
5225 WHERE a.transaction_header_id = p_line_rec_type.transaction_header_id
5226 AND a.transaction_temp_id =
5227 p_line_rec_type.transaction_interface_id
5228 AND a.organization_id = c.organization_id
5229 AND a.inventory_item_id = c.inventory_item_id
5230 AND b.transaction_temp_id(+) = a.transaction_temp_id
5231 AND a.primary_quantity < 0
5232 ORDER BY a.transaction_source_type_id
5233 , a.transaction_source_id
5234 , a.transaction_source_name
5235 , a.trx_source_line_id
5236 , a.trx_source_delivery_id
5237 , a.inventory_item_id
5238 , a.organization_id;
5239
5240 l_tempid NUMBER;
5241 l_item_id NUMBER;
5242 l_org_id NUMBER;
5243 l_locid NUMBER;
5244 l_srctypeid NUMBER;
5245 l_actid NUMBER;
5246 l_trxtypeid NUMBER; --Bug:4866991
5247 l_srcid NUMBER;
5248 l_xlocid NUMBER;
5249 l_temp_rowid VARCHAR2 (21);
5250 l_sub_code VARCHAR2 (11);
5251 l_lotnum VARCHAR2 (80); -- changed lot_number to 80, inconv
5252 --Bug #5086940
5253 --Changed the length to correspond to transaction_source_name
5254 l_src_code mtl_transactions_interface.transaction_source_name%TYPE;
5255 l_xfrsub VARCHAR2 (11);
5256 l_rev VARCHAR2 (4);
5257 l_srclineid VARCHAR2 (40);
5258 l_trxdate DATE;
5259 l_qoh NUMBER;
5260 l_rqoh NUMBER;
5261 l_pqoh NUMBER;
5262 l_qr NUMBER;
5263 l_qs NUMBER;
5264 l_att NUMBER;
5265 l_atr NUMBER;
5266 l_rctrl NUMBER;
5267 l_lctrl NUMBER;
5268 l_flow_schedule NUMBER;
5269 l_trx_qty NUMBER;
5270 l_qty NUMBER := 0;
5271 tree_exists BOOLEAN;
5272 l_revision_control BOOLEAN;
5273 l_lot_control BOOLEAN;
5274 l_disp VARCHAR2 (3000);
5275 l_msg_count NUMBER;
5276 l_msg_data VARCHAR2 (2000);
5277 l_return_status VARCHAR2 (1);
5278 l_tree_id NUMBER;
5279 /* Added the following variables for Bug 3462946 */
5280 l_neg_inv_rcpt NUMBER;
5281 l_cnt_res NUMBER;
5282 l_item_qoh NUMBER;
5283 l_item_rqoh NUMBER;
5284 l_item_pqoh NUMBER;
5285 l_item_qr NUMBER;
5286 l_item_qs NUMBER;
5287 l_item_att NUMBER;
5288 l_item_atr NUMBER;
5289 /* Additional Variables needed to handle TrxRsvRelief code */
5290 l_ship_qty NUMBER;
5291 l_userline VARCHAR2 (40);
5292 l_demand_class VARCHAR2 (30);
5293 l_mps_flag NUMBER;
5294 l_deliveryid NUMBER;
5295 l_lpnid NUMBER;
5296 targetnode NUMBER;
5297 x_errd_int_id NUMBER;
5298
5299 l_procedure_name VARCHAR2(60) := g_pkg_name || '.' ||'POST_TEMP_VALIDATION';
5300 l_progress_indicator VARCHAR2(30) := '0';
5301 --Bug 8571657
5302 l_override_rsv NUMBER := 2;
5303
5304 BEGIN
5305 IF (l_debug IS NULL) THEN
5306 l_debug := NVL (fnd_profile.VALUE ('INV_DEBUG_TRACE'), 0);
5307 END IF;
5308
5309 IF (l_debug = 1) THEN
5310 inv_log_util.TRACE ('$Header: INVTXMGB.pls 120.33.12020000.3 2013/03/06 11:20:01 skommine ship $' , l_procedure_name , 9);
5311 END IF;
5312
5313 l_progress_indicator := '10';
5314
5315 /**********************************************/
5316 /* the reservation was successfully releived. */
5317 /* now if we did ship a +ve qty for a intord */
5318 /* or a sales order, then we need to notify */
5319 /* mrp about this shipment */
5320 /**********************************************/
5321 IF (p_val_req = 1) THEN
5322
5323 l_progress_indicator := '20';
5324 OPEN z1 (p_flow_schedule);
5325 tree_exists := FALSE;
5326
5327 WHILE (TRUE) LOOP
5328
5329 l_progress_indicator := '30';
5330 FETCH z1 INTO l_temp_rowid
5331 , l_item_id
5332 , l_rev
5333 , l_org_id
5334 , l_sub_code
5335 , l_locid
5336 , l_trx_qty
5337 , l_lotnum
5338 , l_srctypeid
5339 , l_actid
5340 , l_trxtypeid /*Bug:4866991*/
5341 , l_srcid
5342 , l_src_code
5343 , l_srclineid
5344 , l_rctrl
5345 , l_lctrl
5346 , l_xfrsub
5347 , l_xlocid
5348 , l_trxdate
5349 , l_neg_inv_rcpt;
5350
5351 IF z1%NOTFOUND THEN
5352 l_progress_indicator := '40';
5353 IF (l_debug = 1) THEN
5354 inv_log_util.TRACE ('No more rows to validate quantity'
5355 , l_procedure_name
5356 , 9
5357 );
5358 END IF;
5359 EXIT;
5360 END IF;
5361
5362 l_progress_indicator := '50';
5363 IF l_rctrl = 1 THEN
5364 l_revision_control := FALSE;
5365 ELSE
5366 l_revision_control := TRUE;
5367 END IF;
5368
5369 IF l_lctrl = 1 THEN
5370 l_lot_control := FALSE;
5371 ELSE
5372 l_lot_control := TRUE;
5373 END IF;
5374
5375 tree_exists := TRUE;
5376 -- Bug 2399354 The tree to be cleared prior to creating a tree to avoid
5377 -- using existing trees
5378
5379 /*** free cache ***/
5380 IF p_line_rec_type.transaction_interface_id IS NULL THEN
5381
5382 IF (l_debug = 1) THEN
5383 inv_log_util.TRACE ('Interface Id is NULL'
5384 , l_procedure_name
5385 , 9
5386 );
5387 END IF;
5388 l_progress_indicator := '60';
5389 inv_quantity_tree_pvt.clear_quantity_cache;
5390
5391 --Bug #5086940
5392 --demand_source_name cannot be greater than 30 characters
5393 IF (LENGTH(l_src_code) > 30) THEN
5394 l_src_code := NULL;
5395 END IF;
5396
5397 l_progress_indicator := '70';
5398 inv_quantity_tree_pvt.create_tree (
5399 p_api_version_number => 1.0
5400 , p_init_msg_lst => fnd_api.g_false
5401 , x_return_status => l_return_status
5402 , x_msg_count => l_msg_count
5403 , x_msg_data => l_msg_data
5404 , p_organization_id => l_org_id
5405 , p_inventory_item_id => l_item_id
5406 , p_tree_mode => 2
5407 , p_is_revision_control => l_revision_control
5408 , p_is_lot_control => l_lot_control
5409 , p_is_serial_control => FALSE
5410 , p_include_suggestion => FALSE
5411 , p_demand_source_type_id => NVL
5412 (l_srctypeid
5413 , -9999
5414 )
5415 , p_demand_source_header_id => NVL
5416 (l_srcid
5417 , -9999
5418 )
5419 , p_demand_source_line_id => NVL
5420 (l_srclineid
5421 , -9999
5422 )
5423 , p_demand_source_name => l_src_code
5424 , p_demand_source_delivery => NULL
5425 , p_lot_expiration_date => NULL
5426 , x_tree_id => l_tree_id
5427 , p_onhand_source => 3
5428 --g_all_subs
5429 , p_exclusive => 0
5430 --g_non_exclusive
5431 , p_pick_release => 0
5432 --g_pick_release_no
5433 );
5434
5435 IF l_return_status = fnd_api.g_ret_sts_error THEN
5436 inv_log_util.TRACE
5437 ( 'Error while creating tree : x_msg_data = '
5438 || l_msg_data
5439 , l_procedure_name
5440 , 9
5441 );
5442 fnd_message.set_name ('INV', 'INV_ERR_CREATETREE');
5443 fnd_message.set_token ('ROUTINE', 'UE:AVAIL_TO_TRX');
5444 l_error_code := fnd_message.get;
5445 l_error_exp := l_msg_data;
5446 RAISE fnd_api.g_exc_error;
5447 END IF;
5448
5449 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
5450 inv_log_util.TRACE ( 'Unexpected Error while creating tree : '
5451 || l_msg_data
5452 , l_procedure_name
5453 , 9
5454 );
5455 l_error_exp := l_msg_data;
5456 RAISE fnd_api.g_exc_unexpected_error;
5457 END IF;
5458
5459 l_progress_indicator := '80';
5460 g_tree_id := l_tree_id;
5461 tree_exists := true;
5462
5463 ELSE
5464
5465 l_progress_indicator := '90';
5466 l_tree_id := g_tree_id;
5467 tree_exists := false;
5468
5469 END IF;
5470
5471 IF (l_debug = 1) THEN
5472 inv_log_util.TRACE ('tree id : '||l_tree_id , l_procedure_name , 9);
5473 inv_log_util.TRACE ('Revision is : '||l_rev , l_procedure_name , 9);
5474 inv_log_util.TRACE ('Lot is : '||l_lotnum , l_procedure_name , 9);
5475 inv_log_util.TRACE ('Sub is : '||l_sub_code , l_procedure_name , 9);
5476 inv_log_util.TRACE ('Xfr Sub is : '||l_xfrsub , l_procedure_name , 9);
5477 inv_log_util.TRACE ('Locator is : '||l_locid , l_procedure_name , 9);
5478 END IF;
5479
5480 l_progress_indicator := '100';
5481 inv_quantity_tree_pvt.query_tree
5482 (p_api_version_number => 1.0
5483 , p_init_msg_lst => fnd_api.g_false
5484 , x_return_status => l_return_status
5485 , x_msg_count => l_msg_count
5486 , x_msg_data => l_msg_data
5487 , p_tree_id => l_tree_id
5488 , p_revision => l_rev
5489 , p_lot_number => l_lotnum
5490 , p_subinventory_code => l_sub_code
5491 , p_transfer_subinventory_code => l_xfrsub
5492 , p_locator_id => l_locid
5493 , x_qoh => l_qoh
5494 , x_rqoh => l_rqoh
5495 , x_pqoh => l_pqoh
5496 , x_qr => l_qr
5497 , x_qs => l_qs
5498 , x_att => l_att
5499 , x_atr => l_atr
5500 );
5501
5502 IF l_return_status = fnd_api.g_ret_sts_error THEN
5503 inv_log_util.TRACE ( 'Expected Error while querying tree : '
5504 || l_msg_data
5505 , l_procedure_name
5506 , 9
5507 );
5508 l_error_code := fnd_message.get;
5509 l_error_exp := l_msg_data;
5510 fnd_message.set_name ('INV', 'INV_INTERNAL_ERROR');
5511 fnd_message.set_token ('token1', 'XACT_QTY1');
5512 RAISE fnd_api.g_exc_error;
5513 END IF;
5514
5515 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
5516 inv_log_util.TRACE ( 'UnExpected Error while querying tree : '
5517 || l_msg_data
5518 , l_procedure_name
5519 , 9
5520 );
5521 l_error_code := fnd_message.get;
5522 l_error_exp := l_msg_data;
5523 fnd_message.set_name ('INV', 'INV_INTERNAL_ERROR');
5524 fnd_message.set_token ('token1', 'XACT_QTY1');
5525 RAISE fnd_api.g_exc_unexpected_error;
5526 END IF;
5527
5528 l_progress_indicator := '110';
5529 IF (l_debug = 1) THEN
5530 inv_log_util.TRACE ('L_QOH : ' || l_qoh, l_procedure_name, 9);
5531 inv_log_util.TRACE ('L_RQOH : ' || l_rqoh, l_procedure_name, 9);
5532 inv_log_util.TRACE ('L_PQOH : ' || l_pqoh, l_procedure_name, 9);
5533 inv_log_util.TRACE ('L_QR : ' || l_qr, l_procedure_name, 9);
5534 inv_log_util.TRACE ('L_QS : ' || l_qs, l_procedure_name, 9);
5535 inv_log_util.TRACE ('L_ATT : ' || l_att, l_procedure_name, 9);
5536 inv_log_util.TRACE ('L_ATR : ' || l_atr, l_procedure_name, 9);
5537 END IF;
5538
5539 /* Bug: 3462946 : Added the code below to check for Negative Balances for a Negative Balances Allowed Org */
5540 --Bug 8571657
5541 l_override_rsv := NVL(fnd_profile.value('INV_OVERRIDE_RSV_FOR_BACKFLUSH'), 2);
5542 IF l_att < 0 THEN
5543 l_progress_indicator := '120';
5544 inv_log_util.TRACE ('l_att is than zero', l_procedure_name, 9);
5545
5546 IF (l_neg_inv_rcpt = 1) THEN
5547 l_progress_indicator := '130';
5548 inv_log_util.TRACE ('Negative Balance Allowed Org '
5549 , l_procedure_name
5550 , 9
5551 );
5552
5553 IF (l_qr > 0 OR l_qs > 0) THEN
5554 IF (l_override_rsv = 1) THEN
5555 IF (l_debug = 1) THEN
5556 inv_log_util.trace('Do not check low level reservations',l_procedure_name, 9);
5557 END IF;
5558 ELSE
5559 inv_log_util.TRACE (
5560 'Transaction quantity must be less than or equal to available quantity'
5561 , l_procedure_name
5562 , 9
5563 );
5564 fnd_message.set_name ('INV', 'INV_INT_PROCCODE');
5565 l_error_code := fnd_message.get;
5566 fnd_message.set_name ('INV', 'INV_QTY_LESS_OR_EQUAL');
5567 l_error_exp := fnd_message.get;
5568 RAISE fnd_api.g_exc_error;
5569 END IF;
5570 END IF;
5571
5572 l_progress_indicator := '140';
5573 inv_quantity_tree_pvt.query_tree
5574 (p_api_version_number => 1.0
5575 , p_init_msg_lst => fnd_api.g_false
5576 , x_return_status => l_return_status
5577 , x_msg_count => l_msg_count
5578 , x_msg_data => l_msg_data
5579 , p_tree_id => l_tree_id
5580 , p_revision => NULL
5581 , p_lot_number => NULL
5582 , p_subinventory_code => NULL
5583 , p_locator_id => NULL
5584 , x_qoh => l_item_qoh
5585 , x_rqoh => l_item_rqoh
5586 , x_pqoh => l_item_pqoh
5587 , x_qr => l_item_qr
5588 , x_qs => l_item_qs
5589 , x_att => l_item_att
5590 , x_atr => l_item_atr
5591 );
5592
5593 IF l_return_status = fnd_api.g_ret_sts_error THEN
5594 inv_log_util.TRACE
5595 ( 'Expected Error while querying tree : '
5596 || l_msg_data
5597 , l_procedure_name
5598 , 9
5599 );
5600 l_error_code := fnd_message.get;
5601 l_error_exp := l_msg_data;
5602 fnd_message.set_name ('INV', 'INV_INTERNAL_ERROR');
5603 fnd_message.set_token ('token1', 'XACT_QTY1');
5604 RAISE fnd_api.g_exc_error;
5605 END IF;
5606
5607 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
5608 inv_log_util.TRACE
5609 ( 'UnExpected Error while querying tree : '
5610 || l_msg_data
5611 , l_procedure_name
5612 , 9
5613 );
5614
5615 l_error_code := fnd_message.get;
5616 l_error_exp := l_msg_data;
5617 fnd_message.set_name ('INV', 'INV_INTERNAL_ERROR');
5618 fnd_message.set_token ('token1', 'XACT_QTY1');
5619 RAISE fnd_api.g_exc_unexpected_error;
5620 END IF;
5621
5622 l_progress_indicator := '150';
5623 IF (l_debug = 1) THEN
5624 inv_log_util.TRACE ('L_ITEM_QOH : ' || l_item_qoh
5625 , l_procedure_name
5626 , 9
5627 );
5628 inv_log_util.TRACE ('L_ITEM_RQOH : ' || l_item_rqoh
5629 , l_procedure_name
5630 , 9
5631 );
5632 inv_log_util.TRACE ('L_ITEM_PQOH : ' || l_item_pqoh
5633 , l_procedure_name
5634 , 9
5635 );
5636 inv_log_util.TRACE ('L_ITEM_QR : ' || l_item_qr
5637 , l_procedure_name
5638 , 9
5639 );
5640 inv_log_util.TRACE ('L_ITEM_QS : ' || l_item_qs
5641 , l_procedure_name
5642 , 9
5643 );
5644 inv_log_util.TRACE ('L_ITEM_ATT : ' || l_item_att
5645 , l_procedure_name
5646 , 9
5647 );
5648 inv_log_util.TRACE ('L_ITEM_ATR : ' || l_item_atr
5649 , l_procedure_name
5650 , 9
5651 );
5652 inv_log_util.TRACE ('L_TRX_QTY : ' || l_trx_qty
5653 , l_procedure_name
5654 , 9
5655 );
5656 END IF;
5657
5658 IF (l_item_qoh <> l_item_att) THEN -- Higher Level Reservations
5659 l_progress_indicator := '160';
5660
5661 IF (l_item_att < 0 AND l_item_qr > 0) THEN
5662 /*
5663 * ------------------------------------------------------------
5664 * Enhancement made for the customer: (BUG: 8571657)
5665 * ------------------------------------------------------------
5666 * Description:
5667 * ------------
5668 * This fix will allow the TM to process the transactions posted in MTI by bypassing
5669 * the reservation validation. This feature is achieved by using the profile option.
5670 * Profile Used: INV_OVERRIDE_RSV_FOR_BACKFLUSH
5671 *
5672 * For the existing customers, the DEFAULT behavior is that the transactions
5673 * will go through the reservation validation. The transaction error out if any
5674 * reservations exist for that item thererby not allowing the inventory to be
5675 * driven negative.
5676 *
5677 * The above default behavior can be overridden by setting the profile to 'YES'
5678 * thereby immitating the functionality that existed in 11.5.8
5679 * Note:
5680 * -----
5681 * Kindly refer the BUG for an eloborate problem description.
5682 */
5683
5684 IF (l_override_rsv = 1) THEN
5685 IF (l_debug = 1) THEN
5686 inv_log_util.trace('Do not check high level reservations',l_procedure_name, 9);
5687 END IF;
5688 ELSE
5689 l_progress_indicator := '180';
5690 IF (l_debug = 1) THEN
5691 inv_log_util.trace('Total Org quantity cannot become negative when there are reservations present',l_procedure_name, 9);
5692 END IF;
5693 FND_MESSAGE.set_name('INV','INV_INT_PROCCODE');
5694 l_error_code := FND_MESSAGE.get;
5695 FND_MESSAGE.set_name('INV','INV_ORG_QUANTITY');
5696 FND_MSG_PUB.add;
5697 l_error_exp := FND_MESSAGE.get;
5698 RAISE fnd_api.g_exc_error;
5699 END IF;
5700
5701 /*
5702 * The following immediate code is commented inorder to immitate the
5703 * 11.5.8 functionality (The re-engineered code can be viewed above).
5704 * Now, the 1158 and R120 functionality co-exist and the expected behavior
5705 * can be chosen by setting the profile mentioned in the description above.
5706 *
5707 *
5708 * Bug:4866991. For subinventory and backflush transfers high level
5709 * reservations should not be checked
5710 */
5711 l_progress_indicator := '170';
5712 /*IF ( l_srctypeid = 13 AND l_actid = 2 AND l_trxtypeid not in (66,67,68) ) THEN
5713 inv_log_util.trace(
5714 'Do not check high level reservations for subinventory and backflush transfers'
5715 ,l_procedure_name
5716 ,9
5717 );
5718 ELSE
5719 inv_log_util.TRACE (
5720 'Total Org quantity cannot become negative when there are reservations present'
5721 ,l_procedure_name
5722 ,9
5723 );
5724 fnd_message.set_name ('INV', 'INV_INT_PROCCODE');
5725 l_error_code := fnd_message.get;
5726 fnd_message.set_name ('INV', 'INV_ORG_QUANTITY');
5727 l_error_exp := fnd_message.get;
5728 RAISE fnd_api.g_exc_error;
5729 END IF;*/
5730 END IF;
5731 END IF;
5732 ELSE --if (neg_inv_rcpt = 1)
5733 l_progress_indicator := '180';
5734 fnd_message.set_name ('INV', 'INV_NO_NEG_BALANCES');
5735 l_error_code := fnd_message.get;
5736 fnd_message.set_name ('INV', 'INV_LESS_OR_EQUAL');
5737 fnd_message.set_token ('ENTITY1', 'INV_QUANTITY');
5738 fnd_message.set_token ('ENTITY2', 'AVAIL_TO_TRANSACT');
5739 l_error_exp := fnd_message.get;
5740 RAISE fnd_api.g_exc_error;
5741 --exit;
5742 END IF; -- neg_inv_rcpt
5743 END IF; -- l_att
5744 /* End of changes for Bug 3462946 */
5745 END LOOP;
5746
5747 /* This should be for any error other than not found */
5748 l_progress_indicator := '190';
5749 CLOSE z1;
5750
5751 IF (tree_exists) THEN
5752 l_progress_indicator := '200';
5753 inv_quantity_tree_pvt.free_all (p_api_version_number => 1.0
5754 , p_init_msg_lst => fnd_api.g_false
5755 , x_return_status => l_return_status
5756 , x_msg_count => l_msg_count
5757 , x_msg_data => l_msg_data
5758 );
5759 END IF;
5760
5761 END IF; -- p_val_req
5762
5763 x_errd_int_id := -9876;
5764 RETURN TRUE;
5765
5766 EXCEPTION
5767 WHEN OTHERS THEN
5768 inv_log_util.TRACE ('At indicator : ' || l_progress_indicator, l_procedure_name, 9);
5769 inv_log_util.TRACE ('Error in post_temp_validation : ' || l_error_code
5770 , l_procedure_name
5771 , '1'
5772 );
5773 inv_log_util.TRACE ('SQL : ' || SUBSTR (SQLERRM, 1, 200)
5774 , l_procedure_name
5775 , '1'
5776 );
5777 x_errd_int_id := -9876;
5778 RETURN FALSE;
5779 END post_temp_validation;
5780
5781 -- Bug 4764790: passing the transaction id for relieving
5782 -- reservations along with the serial numbers
5783 PROCEDURE rel_reservations_mrp_update
5784 (p_header_id IN NUMBER
5785 , p_transaction_temp_id IN NUMBER
5786 , p_transaction_id IN NUMBER DEFAULT NULL
5787 , p_res_sts OUT NOCOPY VARCHAR2
5788 , p_res_msg OUT NOCOPY VARCHAR2
5789 , p_res_count OUT NOCOPY NUMBER
5790 , p_mrp_status OUT NOCOPY VARCHAR2
5791 )
5792 IS
5793 CURSOR c1
5794 IS
5795 SELECT a.organization_id
5796 , a.inventory_item_id
5797 , NVL (a.transaction_source_id, 0)
5798 , a.transaction_source_type_id
5799 , a.trx_source_delivery_id
5800 , a.trx_source_line_id
5801 , a.revision
5802 , DECODE (c.lot_control_code, 2, b.lot_number, a.lot_number)
5803 , a.subinventory_code
5804 , a.locator_id
5805 , DECODE (c.lot_control_code
5806 , 2, ABS (NVL (b.primary_quantity, 0))
5807 , a.primary_quantity * (-1)
5808 )
5809 , a.transaction_source_name
5810 , a.transaction_date
5811 , NVL(a.content_lpn_id,a.lpn_id) --bug#8650417.Added NVL
5812 , a.primary_quantity
5813 , --
5814 a.transaction_action_id
5815 , A.transaction_type_id /*Bug:4866991*/
5816 , a.transfer_subinventory
5817 , a.transfer_to_location
5818 , DECODE (a.process_flag, 'Y', 1, 'N', 2, 'E', 3, 3)
5819 , a.shippable_flag
5820 , b.transaction_temp_id --lot record identifier in MTLT
5821 , a.relieve_high_level_rsv_flag /*** {{ R12 Enhanced reservations code changes ***/
5822 FROM mtl_system_items c
5823 , mtl_transaction_lots_temp b
5824 , mtl_material_transactions_temp a
5825 WHERE a.transaction_header_id = p_header_id
5826 AND a.transaction_temp_id = p_transaction_temp_id
5827 AND a.organization_id = c.organization_id
5828 AND a.inventory_item_id = c.inventory_item_id
5829 AND b.transaction_temp_id(+) = a.transaction_temp_id
5830 -- AND A.PRIMARY_QUANTITY < 0 /* Bug: 3462946: This clause is commented as BaseTransaction.java already does this validation */
5831 ORDER BY a.transaction_source_type_id
5832 , a.transaction_source_id
5833 , a.transaction_source_name
5834 , a.trx_source_line_id
5835 , a.trx_source_delivery_id
5836 , a.inventory_item_id
5837 , a.organization_id;
5838
5839 l_return_status VARCHAR2 (1);
5840 l_msg_count NUMBER;
5841 l_msg_data VARCHAR2 (2000);
5842 l_ship_qty NUMBER;
5843 l_userline VARCHAR2 (40);
5844 l_demand_class VARCHAR2 (30);
5845 l_mps_flag NUMBER;
5846 l_org_id NUMBER;
5847 l_item_id NUMBER;
5848 l_sub_code VARCHAR2 (11);
5849 l_locid NUMBER;
5850 l_lotnum VARCHAR2 (80); -- changed lot_number to 80, inconv
5851 l_rev VARCHAR2 (4);
5852 l_srctypeid NUMBER;
5853 l_srcid NUMBER;
5854 --Bug #5086940
5855 --Changed the length to correspond to transaction_source_name
5856 l_src_code mtl_transactions_interface.transaction_source_name%TYPE;
5857 l_srclineid VARCHAR2 (40);
5858 l_deliveryid NUMBER;
5859 l_trx_qty NUMBER;
5860 l_trxdate DATE;
5861 l_userid NUMBER;
5862 l_lpnid NUMBER;
5863 l_line_rec_type line_rec_type;
5864 l_loginid NUMBER;
5865 -- INVCONV fabdi start
5866 l_secondary_ship_qty NUMBER;
5867 l_qty_at_suom NUMBER;
5868 -- INVCONV fabdi end
5869
5870 /*** {{ R12 Enhanced reservations code changes ***/
5871 l_relieve_high_level_rsv_flag VARCHAR2(1);
5872 l_total_prim_qty_to_relieve NUMBER := 0;
5873 l_rel_lpn_id NUMBER := null;
5874 l_rel_loc_id NUMBER := null;
5875 l_rel_sub_code VARCHAR2(11) := null;
5876 l_rel_lot_num VARCHAR2(80) := null;
5877 l_rel_revision VARCHAR2(4) := null;
5878 -- Bug 4764790: passing the transaction id for relieving
5879 -- reservations along with the serial numbers
5880 l_transaction_id NUMBER := NULL;
5881 l_wip_entity_type NUMBER := NULL; -- Bug 4764790
5882 l_wip_job_type VARCHAR2(15); -- Bug 4764790
5883 l_loop_exit NUMBER := 0;
5884 /*** End R12 }} ***/
5885
5886 tree_exists BOOLEAN := false;
5887 l_tree_id NUMBER;
5888 l_lctrl NUMBER;
5889 l_rctrl NUMBER;
5890 l_revision_control BOOLEAN := FALSE;
5891 l_lot_control BOOLEAN := FALSE;
5892
5893 l_procedure_name VARCHAR2(60) := g_pkg_name || '.' || 'REL_RESERVATIONS_MRP_UPDATE';
5894 l_progress_indicator VARCHAR2(20) := '0';
5895 BEGIN
5896
5897 IF (l_debug IS NULL) THEN
5898 l_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
5899 END IF;
5900
5901 IF (l_debug = 1) THEN
5902 inv_log_util.TRACE ('$Header: INVTXMGB.pls 120.33.12020000.3 2013/03/06 11:20:01 skommine ship $', l_procedure_name,9);
5903 END IF;
5904
5905
5906 IF (g_userid IS NULL) THEN
5907 g_userid := NVL (fnd_profile.VALUE ('USER_ID'), -1);
5908 END IF;
5909
5910 l_userid := g_userid;
5911 l_loginid := NVL(fnd_global.login_id, -1);
5912
5913 IF (l_debug = 1) THEN
5914 inv_log_util.TRACE ('USERID :' || l_userid
5915 , l_procedure_name
5916 , 9
5917 );
5918 inv_log_util.TRACE ('LoginId :' || l_loginid
5919 , l_procedure_name
5920 , 9
5921 );
5922 END IF;
5923
5924 p_mrp_status := 'S';
5925 p_res_sts := 'S';
5926 p_res_msg := '';
5927 p_res_count := 0;
5928
5929 l_progress_indicator := '10';
5930 OPEN c1;
5931 LOOP
5932 l_progress_indicator := '20';
5933 FETCH c1 INTO l_org_id
5934 , l_item_id
5935 , l_srcid
5936 , l_srctypeid
5937 , l_deliveryid
5938 , l_srclineid
5939 , l_rev
5940 , l_lotnum
5941 , l_sub_code
5942 , l_locid
5943 , l_trx_qty
5944 , l_src_code
5945 , l_trxdate
5946 , l_lpnid
5947 , l_line_rec_type.primary_quantity
5948 , l_line_rec_type.transaction_action_id
5949 , l_line_rec_type.TRANSACTION_TYPE_ID /*Bug:4866991*/
5950 , l_line_rec_type.transfer_subinventory
5951 , l_line_rec_type.transfer_locator
5952 , l_line_rec_type.process_flag
5953 , l_line_rec_type.shippable_flag
5954 , l_line_rec_type.transaction_interface_id
5955 , l_relieve_high_level_rsv_flag;
5956
5957 IF c1%NOTFOUND THEN
5958 l_progress_indicator := '30';
5959 IF (l_debug = 1) THEN
5960 inv_log_util.TRACE ('No more rows to relieve'
5961 , l_procedure_name
5962 , 9
5963 );
5964 END IF;
5965 p_res_sts := 'S';
5966 p_res_msg := '';
5967 p_res_count := 0;
5968 EXIT;
5969 END IF;
5970
5971 l_progress_indicator := '40';
5972 -- Bug 4764790: passing the transaction id for relieving
5973 -- reservations along with the serial numbers
5974 l_transaction_id := p_transaction_id;
5975
5976 IF (l_srctypeid = job_schedule) THEN
5977 l_progress_indicator := '50';
5978 -- call get_wip_entity API
5979 inv_reservation_pvt.get_wip_entity_type
5980 ( p_api_version_number => 1.0
5981 , p_init_msg_lst => fnd_api.g_false
5982 , x_return_status => l_return_status
5983 , x_msg_count => l_msg_count
5984 , x_msg_data => l_msg_data
5985 , p_organization_id => null
5986 , p_item_id => null
5987 , p_source_type_id => null
5988 , p_source_header_id => l_srcid
5989 , p_source_line_id => null
5990 , p_source_line_detail => null
5991 , x_wip_entity_type => l_wip_entity_type
5992 , x_wip_job_type => l_wip_job_type
5993 );
5994
5995 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
5996 inv_log_util.TRACE ('Return status from get wip entity. ' ||l_return_status, l_procedure_name, 9);
5997 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
5998 inv_log_util.TRACE ('Return status from get wip entity. ' ||l_return_status, l_procedure_name, 9);
5999 END IF;
6000
6001 END IF; -- job_schedule
6002
6003 l_progress_indicator := '60';
6004 IF (l_debug = 1) THEN
6005 inv_log_util.TRACE ('Wip entity type ' || l_wip_entity_type, l_procedure_name, 9);
6006 inv_log_util.TRACE ('l_srctypeid ' || l_srctypeid, l_procedure_name, 9);
6007 END IF;
6008
6009 IF ((l_srctypeid = job_schedule) AND (l_wip_entity_type =
6010 inv_reservation_global.g_wip_source_type_cmro)) THEN
6011 l_src_code := fnd_api.g_miss_char;
6012 END IF;
6013
6014 --Bug #5086940
6015 --demand_source_name cannot be greater than 30 characters
6016 IF (LENGTH(l_src_code) > 30) THEN
6017 l_src_code := fnd_api.g_miss_char;
6018 END IF;
6019
6020 IF (l_debug = 1) THEN
6021 inv_log_util.trace('l_src_code is: ' || l_src_code, l_procedure_name, 9);
6022 END IF;
6023
6024 l_progress_indicator := '70';
6025
6026 -- SRSRIRAN Bug 4437767
6027 -- Removed inline code branching related to INCONV/ K release
6028 IF ( NOT ((l_srctypeid = job_schedule) AND (l_wip_entity_type <> inv_reservation_global.g_wip_source_type_cmro))) THEN
6029 l_progress_indicator := '80';
6030 IF (l_debug = 1) THEN
6031 inv_log_util.TRACE ('Inside rsv_relief', l_procedure_name, 9);
6032 END IF;
6033 -- End changes for Bug 4764790
6034 inv_trx_relief_c_pvt.rsv_relief
6035 (x_return_status => l_return_status
6036 , x_msg_count => l_msg_count
6037 , x_msg_data => l_msg_data
6038 , x_ship_qty => l_ship_qty --it will be the quantity relieved FROM this api
6039 , x_secondary_ship_qty => l_secondary_ship_qty --INVCONV fabdi
6040 , x_userline => l_userline
6041 , x_demand_class => l_demand_class
6042 , x_mps_flag => l_mps_flag
6043 , p_organization_id => l_org_id
6044 , p_inventory_item_id => l_item_id
6045 , p_subinv => l_sub_code
6046 , p_locator => l_locid
6047 , p_lotnumber => l_lotnum
6048 , p_revision => l_rev
6049 , p_dsrc_type => l_srctypeid
6050 , p_header_id => l_srcid
6051 , p_dsrc_name => l_src_code
6052 , p_dsrc_line => l_srclineid
6053 , p_dsrc_delivery => NULL --l_deliveryid bug2745896
6054 , p_qty_at_puom => ABS (l_trx_qty)
6055 , p_qty_at_suom => l_qty_at_suom -- INVCONV fabdi
6056 , p_lpn_id => l_lpnid
6057 , p_transaction_id => l_transaction_id --Bug 4764790
6058 );
6059
6060 l_progress_indicator := '90';
6061 /*** {{ R12 Enhanced reservations code changes ***/
6062 l_total_prim_qty_to_relieve := l_trx_qty - l_ship_qty;
6063
6064 --Set the default as 'Y'
6065 l_relieve_high_level_rsv_flag := Nvl(l_relieve_high_level_rsv_flag,'Y');
6066 IF (l_relieve_high_level_rsv_flag = 'Y' and l_total_prim_qty_to_relieve > 0) THEN
6067 -- start to relieve reservation with higher level
6068 l_progress_indicator := '100';
6069 l_rel_lpn_id := l_lpnid;
6070 l_rel_loc_id := l_locid;
6071 l_rel_sub_code := l_sub_code;
6072 l_rel_lot_num := l_lotnum;
6073 l_rel_revision := l_rev;
6074
6075 WHILE (l_total_prim_qty_to_relieve > 0) AND (l_loop_exit = 0)
6076 LOOP
6077 l_progress_indicator := '110';
6078 IF (l_debug = 1) THEN
6079 inv_log_util.trace('l_rel_lpn_id : ' || l_rel_lpn_id, l_procedure_name, 9);
6080 inv_log_util.trace('l_rel_loc_id : ' || l_rel_loc_id, l_procedure_name, 9);
6081 inv_log_util.trace('l_rel_sub_code : ' || l_rel_sub_code, l_procedure_name, 9);
6082 inv_log_util.trace('l_rel_lot_num : ' || l_rel_lot_num, l_procedure_name, 9);
6083 inv_log_util.trace('l_rel_revision : ' || l_rel_revision, l_procedure_name, 9);
6084 inv_log_util.trace('l_loop_exit. before call : ' || l_loop_exit, l_procedure_name, 9);
6085 END IF;
6086
6087 IF (l_rel_lpn_id is not null) THEN
6088 l_rel_lpn_id := null;
6089 ELSIF (l_rel_loc_id is not null) THEN
6090 l_rel_loc_id := null;
6091 ELSIF (l_rel_sub_code is not null) THEN
6092 l_rel_sub_code := null;
6093 ELSIF (l_rel_lot_num is not null) THEN
6094 l_rel_lot_num := null;
6095 ELSIF (l_rel_revision is not null) THEN
6096 l_rel_revision := null;
6097 l_loop_exit := 1;
6098 inv_log_util.trace('Setting revision to null : ' || l_loop_exit, l_procedure_name, 9);
6099 END IF;
6100
6101 l_progress_indicator := '120';
6102 inv_trx_relief_c_pvt.rsv_relief
6103 (x_return_status => l_return_status
6104 , x_msg_count => l_msg_count
6105 , x_msg_data => l_msg_data
6106 , x_ship_qty => l_ship_qty --it will be the quantity relieved FROM this api
6107 , x_secondary_ship_qty => l_secondary_ship_qty --INVCONV fabdi
6108 , x_userline => l_userline
6109 , x_demand_class => l_demand_class
6110 , x_mps_flag => l_mps_flag
6111 , p_organization_id => l_org_id
6112 , p_inventory_item_id => l_item_id
6113 , p_subinv => l_rel_sub_code
6114 , p_locator => l_rel_loc_id
6115 , p_lotnumber => l_rel_lot_num
6116 , p_revision => l_rel_revision
6117 , p_dsrc_type => l_srctypeid
6118 , p_header_id => l_srcid
6119 , p_dsrc_name => l_src_code
6120 , p_dsrc_line => l_srclineid
6121 , p_dsrc_delivery => NULL --l_deliveryid bug2745896
6122 , p_qty_at_puom => ABS (l_total_prim_qty_to_relieve)
6123 , p_qty_at_suom => l_qty_at_suom -- INVCONV fabdi
6124 , p_lpn_id => l_rel_lpn_id
6125 , p_transaction_id => l_transaction_id --Bug 4764790
6126 );
6127
6128 l_total_prim_qty_to_relieve := l_total_prim_qty_to_relieve - l_ship_qty;
6129 IF (l_rel_lpn_id IS NULL AND l_rel_loc_id IS NULL AND
6130 l_rel_sub_code IS NULL AND l_rel_lot_num IS NULL AND
6131 l_rel_revision IS NULL) THEN
6132 l_loop_exit := 1;
6133 END IF;
6134
6135 inv_log_util.trace('l_loop_exit. After call ' || l_loop_exit, l_procedure_name, 9);
6136 END LOOP;
6137 l_ship_qty := l_trx_qty - l_total_prim_qty_to_relieve;
6138 END IF; -- relieve_reservations flag..
6139 /*** End R12 }} ***/
6140 END IF;
6141
6142 IF (l_debug = 1) THEN
6143 inv_log_util.TRACE ('l_return_status : ' || l_return_status
6144 , l_procedure_name
6145 , 9
6146 );
6147 inv_log_util.TRACE ('l_ship_qty : ' || l_ship_qty
6148 , l_procedure_name
6149 , 9
6150 );
6151 inv_log_util.TRACE ('l_userline : ' || l_userline
6152 , l_procedure_name
6153 , 9
6154 );
6155 inv_log_util.TRACE ('l_demand_class : ' || l_demand_class
6156 , l_procedure_name
6157 , 9
6158 );
6159 inv_log_util.TRACE ('l_mps_flag : ' || l_mps_flag
6160 , l_procedure_name
6161 , 9
6162 );
6163 inv_log_util.TRACE ('l_org_id : ' || l_org_id
6164 , l_procedure_name
6165 , 9
6166 );
6167 inv_log_util.TRACE ('l_item_id : ' || l_item_id
6168 , l_procedure_name
6169 , 9
6170 );
6171 inv_log_util.TRACE ('l_sub_code: ' || l_sub_code
6172 , l_procedure_name
6173 , 9
6174 );
6175 inv_log_util.TRACE ('l_locid : ' || l_locid, l_procedure_name, 9);
6176 inv_log_util.TRACE ('l_lotnum : ' || l_lotnum
6177 , l_procedure_name
6178 , 9
6179 );
6180 inv_log_util.TRACE ('l_rev : ' || l_rev, l_procedure_name, 9);
6181 inv_log_util.TRACE ('l_srctypeid : ' || l_srctypeid
6182 , l_procedure_name
6183 , 9
6184 );
6185 inv_log_util.TRACE ('l_header_id ' || l_srcid
6186 , l_procedure_name
6187 , 9
6188 );
6189 inv_log_util.TRACE ('l_dsrc_name : ' || l_src_code
6190 , l_procedure_name
6191 , 9
6192 );
6193 inv_log_util.TRACE ('l_dsrc_line : ' || l_srclineid
6194 , l_procedure_name
6195 , 9
6196 );
6197 inv_log_util.TRACE ('l_dsrc_delivery :' || l_deliveryid
6198 , l_procedure_name
6199 , 9
6200 );
6201 inv_log_util.TRACE ('l_dsrc_delivery :' || l_deliveryid
6202 , l_procedure_name
6203 , 9
6204 );
6205 inv_log_util.TRACE ('l_trx_qty : ' || l_trx_qty
6206 , l_procedure_name
6207 , 9
6208 );
6209 inv_log_util.TRACE ('l_lpnid : ' || l_lpnid, l_procedure_name, 9);
6210 END IF;
6211
6212 p_res_sts := l_return_status;
6213 p_res_msg := l_msg_data;
6214 p_res_count := l_msg_count;
6215
6216 IF l_return_status <> fnd_api.g_ret_sts_success THEN
6217 IF (l_debug = 1) THEN
6218 inv_log_util.TRACE ('x_msg_data = ' || l_msg_data
6219 , l_procedure_name
6220 , 9
6221 );
6222 inv_log_util.TRACE ('Before error return in TrxRsvRelief'
6223 , l_procedure_name
6224 , 9
6225 );
6226 END IF;
6227 RETURN;
6228 ELSE -- return success
6229 IF (l_debug = 1) THEN
6230 inv_log_util.TRACE ('Reservation was successfully relieved'
6231 , l_procedure_name
6232 , 9
6233 );
6234 END IF;
6235
6236 IF (ABS (l_trx_qty) <> 0) AND (l_srctypeid = salorder OR l_srctypeid = intorder)
6237 AND (l_mps_flag <> 0) THEN
6238 IF (l_debug = 1) THEN
6239 inv_log_util.TRACE ('Calling mrp_ship_order'
6240 , l_procedure_name
6241 , 9
6242 );
6243 END IF;
6244 IF (NOT mrp_ship_order (l_srclineid
6245 , l_item_id
6246 , ABS (l_trx_qty)
6247 , l_userid
6248 , l_org_id
6249 , l_userline
6250 , l_trxdate
6251 , l_demand_class
6252 )
6253 ) THEN
6254 IF (l_debug = 1) THEN
6255 inv_log_util.TRACE ('mrp_ship_order failure'
6256 , l_procedure_name
6257 , 9
6258 );
6259 END IF;
6260 p_mrp_status := 'E';
6261 RETURN;
6262 END IF; -- return success
6263
6264 IF (l_debug = 1) THEN
6265 inv_log_util.TRACE ('After mrp__order', l_procedure_name, 9);
6266 END IF;
6267 END IF; -- ABS(l_trx..)
6268
6269 END IF; -- return success
6270
6271 IF l_ship_qty <> ABS (l_trx_qty) THEN --in this case there
6272
6273 IF (l_debug = 1) THEN
6274 inv_log_util.TRACE ( 'l_PRIMARY_QUANTITY: '
6275 || l_line_rec_type.primary_quantity
6276 , l_procedure_name
6277 , 9
6278 );
6279 inv_log_util.TRACE ( 'l_transaction_action_id: '
6280 || l_line_rec_type.transaction_action_id
6281 , l_procedure_name
6282 , 9
6283 );
6284 inv_log_util.TRACE ( 'l_process_flag :'
6285 || l_line_rec_type.process_flag
6286 , l_procedure_name
6287 , 9
6288 );
6289 inv_log_util.TRACE ( 'l_shippable_flag : '
6290 || l_line_rec_type.shippable_flag
6291 , l_procedure_name
6292 , 9
6293 );
6294 END IF;
6295
6296 l_line_rec_type.inventory_item_id := l_item_id;
6297 l_line_rec_type.revision := l_rev;
6298 l_line_rec_type.organization_id := l_org_id;
6299 l_line_rec_type.subinventory_code := l_sub_code;
6300 l_line_rec_type.locator_id := l_locid;
6301 l_line_rec_type.transaction_source_type_id := l_srctypeid;
6302 l_line_rec_type.transaction_source_id := l_srcid;
6303 l_line_rec_type.transaction_source_name := l_src_code;
6304 l_line_rec_type.source_line_id := l_srclineid;
6305 l_line_rec_type.transaction_date := l_trxdate;
6306
6307 BEGIN
6308 SELECT lot_control_code,
6309 revision_qty_control_code
6310 INTO l_lctrl,
6311 l_rctrl
6312 FROM mtl_system_items_b
6313 WHERE organization_id = l_org_id
6314 AND inventory_item_id = l_item_id;
6315 EXCEPTION
6316 WHEN NO_DATA_FOUND THEN
6317 l_lctrl := 0;
6318 l_rctrl := 0;
6319 END;
6320
6321 l_progress_indicator := '1305';
6322 IF l_rctrl = 1 THEN
6323 l_revision_control := FALSE;
6324 ELSE
6325 l_revision_control := TRUE;
6326 END IF;
6327
6328 IF l_lctrl = 1 THEN
6329 l_lot_control := FALSE;
6330 ELSE
6331 l_lot_control := TRUE;
6332 END IF;
6333
6334 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
6335
6336 l_progress_indicator := '135';
6337 INV_QUANTITY_TREE_PVT.clear_quantity_cache;
6338
6339 l_progress_indicator := '1351';
6340 INV_QUANTITY_TREE_PVT.create_tree
6341 ( p_api_version_number => 1.0
6342 , p_init_msg_lst => fnd_api.g_false
6343 , x_return_status => l_return_status
6344 , x_msg_count => l_msg_count
6345 , x_msg_data => l_msg_data
6346 , p_organization_id => l_org_id
6347 , p_inventory_item_id => l_item_id
6348 , p_tree_mode => 2
6349 , p_is_revision_control => l_revision_control
6350 , p_is_lot_control => l_lot_control
6351 , p_is_serial_control => FALSE
6352 , p_include_suggestion => FALSE
6353 , p_demand_source_type_id => nvl(l_srctypeid,-9999)
6354 , p_demand_source_header_id => nvl(l_srcid,-9999)
6355 , p_demand_source_line_id => nvl(l_srclineid,-9999)
6356 , p_demand_source_name => l_src_code
6357 , p_demand_source_delivery => NULL
6358 , p_lot_expiration_date => NULL
6359 , x_tree_id => l_tree_id
6360 , p_onhand_source => 3 --g_all_subs
6361 , p_exclusive => 0 --g_non_exclusive
6362 , p_pick_release => 0 --g_pick_release_no
6363 ) ;
6364
6365 IF l_return_status = fnd_api.g_ret_sts_error THEN
6366 IF (l_debug = 1) THEN
6367 inv_log_util.trace('Error while creating tree : x_msg_data = ' || l_msg_data,l_procedure_name, 9);
6368 END IF;
6369 FND_MESSAGE.set_name('INV','INV_ERR_CREATETREE');
6370 FND_MESSAGE.set_token('ROUTINE','UE:AVAIL_TO_TRX');
6371
6372 l_error_code := FND_MESSAGE.get;
6373 l_error_exp := l_msg_data;
6374 RAISE fnd_api.g_exc_error;
6375 END IF ;
6376
6377 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
6378 IF (l_debug = 1) THEN
6379 inv_log_util.trace('Unexpected Error while creating tree : ' || l_msg_data,l_procedure_name, 9);
6380 END IF;
6381 l_error_exp := l_msg_data;
6382 RAISE fnd_api.g_exc_unexpected_error;
6383 END IF;
6384
6385 g_interface_id := l_line_rec_type.transaction_interface_id;
6386 tree_exists := TRUE;
6387 g_tree_id := l_tree_id;
6388 IF (l_debug = 1) THEN
6389 inv_log_util.trace('Tree id is '||g_tree_id, l_procedure_name, 9);
6390 END IF;
6391
6392 END IF; /* interface id has changed */
6393 --qty-tree validation
6394 IF ((NOT post_temp_validation (l_line_rec_type
6395 , 1 --always validate it
6396 , l_userid
6397 , inv_txn_manager_grp.gi_flow_schedule
6398 , l_lotnum -- Added for 4377625
6399 )
6400 )
6401 )
6402 THEN
6403
6404 l_error_code := fnd_message.get;
6405
6406 UPDATE mtl_transactions_interface
6407 SET last_update_date = SYSDATE
6408 , last_updated_by = l_userid
6409 , last_update_login = l_loginid
6410 , program_update_date = SYSDATE
6411 , process_flag = 3
6412 , lock_flag = 2
6413 , ERROR_CODE = SUBSTRB (l_error_code, 1, 240)
6414 , error_explanation = SUBSTRB (l_error_exp, 1, 240)
6415 --WHERE ROWID = l_Line_rec_type.rowid
6416 WHERE transaction_interface_id = p_transaction_temp_id
6417 AND process_flag = 1
6418 AND organization_id = l_org_id
6419 AND inventory_item_id = l_item_id
6420 AND NVL (subinventory_code, '@@@@') = NVL (l_sub_code, '@@@@');
6421
6422 UPDATE mtl_transactions_interface
6423 SET last_update_date = SYSDATE
6424 , last_updated_by = l_userid
6425 , last_update_login = l_loginid
6426 , program_update_date = SYSDATE
6427 , process_flag = 3
6428 , lock_flag = 2
6429 , ERROR_CODE = SUBSTRB (l_error_code, 1, 240)
6430 --WHERE TRANSACTION_HEADER_ID = l_header_id
6431 WHERE transaction_interface_id = p_transaction_temp_id
6432 AND process_flag = 1;
6433
6434 IF (l_debug = 1)
6435 THEN
6436 inv_log_util.TRACE
6437 ('After Error in post_temp_validation continue...'
6438 , l_procedure_name
6439 , 9
6440 );
6441 END IF;
6442
6443 RAISE fnd_api.g_exc_error;
6444 END IF;
6445 END IF;
6446 END LOOP;
6447
6448 CLOSE c1;
6449 l_progress_indicator := '180';
6450 IF (tree_exists) THEN
6451 l_progress_indicator := '190';
6452 INV_QUANTITY_TREE_PVT.free_All
6453 ( p_api_version_number => 1.0
6454 , p_init_msg_lst => fnd_api.g_false
6455 , x_return_status => l_return_status
6456 , x_msg_count => l_msg_count
6457 , x_msg_data => l_msg_data);
6458 END IF;
6459 EXCEPTION
6460 WHEN OTHERS THEN
6461 inv_log_util.TRACE ( '***Undef Error Ex..rel_res : '
6462 || SUBSTR (SQLERRM, 1, 200)
6463 , l_procedure_name
6464 , '9'
6465 );
6466 inv_log_util.TRACE ( 'When others Ex..rel_reservations_mrp_update '
6467 || l_error_code
6468 , l_procedure_name
6469 , '1'
6470 );
6471 p_res_sts := 'E';
6472 p_mrp_status := 'E';
6473 END rel_reservations_mrp_update;
6474 END inv_txn_manager_pub;