[Home] [Help]
PACKAGE BODY: APPS.INV_RCV_STD_INSPECT_APIS
Source
1 PACKAGE BODY inv_rcv_std_inspect_apis AS
2 /* $Header: INVSTDIB.pls 120.9.12010000.4 2009/01/07 06:07:09 vssrivat ship $ */
3
4 /*
5 ** -------------------------------------------------------------------------
6 ** Function: main_process
7 ** Description:
8 ** Output:
9 ** x_return_status
10 ** return status indicating success, error, unexpected error
11 ** x_msg_count
12 ** number of messages in message list
13 ** x_msg_data
14 ** if the number of messages in message list is 1, contains
15 ** message text
16 ** Input:
17 **
18 ** Returns:
19 ** --------------------------------------------------------------------------
20 */
21 g_pkg_name CONSTANT VARCHAR2(30) := 'INV_RCV_STD_INSPECT_APIS';
22
23 g_to_be_inspected CONSTANT NUMBER := 1;
24 g_accept CONSTANT NUMBER := 2;
25 g_reject CONSTANT NUMBER := 3;
26
27 g_inspection_routing CONSTANT NUMBER := 2;
28
29 -- From mfg_lookups,
30 -- lookup_type = WMS_LPN_CONTEXT
31 -- lookup_code = 3
32 -- meaning = Resides in Receiving
33 g_resides_in_receiving CONSTANT NUMBER := 3;
34
35 g_interface_transaction_id NUMBER;
36
37 PROCEDURE print_debug(p_err_msg VARCHAR2,
38 p_level NUMBER)
39 IS
40 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
41 BEGIN
42
43 IF (l_debug = 1) THEN
44 inv_mobile_helper_functions.tracelog
45 (p_err_msg => p_err_msg,
46 p_module => g_pkg_name||'($Revision: 120.9.12010000.4 $)',
47 p_level => p_level);
48 END IF;
49
50 -- dbms_output.put_line(p_err_msg);
51 END print_debug;
52
53 /* FP-J Lot/Serial Support Enhancement
54 * Helper routine to create interface records for the inspected lot number
55 * (in MTL_TRANSACTION_LOT_NUMBERS) and/or the inspected serial number (in
56 * MTL_SERIAL_NUMBERS_INTERFACE) if the item is lot and/or serial controlled.
57 * The interface records created here would be used by the receiving TM to
58 * update the receiving onhand for the lots and serials (RCV_LOTS_SUPPLY and
59 * RCV_SERIALS_SUPPLY)
60 */
61 PROCEDURE process_lot_serial_intf(
62 x_return_status OUT NOCOPY VARCHAR2
63 , x_msg_count OUT NOCOPY NUMBER
64 , x_msg_data OUT NOCOPY VARCHAR2
65 , p_organization_id IN NUMBER
66 , p_inventory_item_id IN NUMBER
67 , p_lot_control_code IN NUMBER
68 , p_serial_control_code IN NUMBER
69 , p_lot_number IN VARCHAR2
70 , p_txn_qty IN NUMBER
71 , p_primary_qty IN NUMBER
72 , p_serial_number IN VARCHAR2
73 , p_product_transaction_id IN NUMBER
74 , p_lpn_id IN NUMBER
75 , p_sec_txn_qty IN NUMBER --OPM Convergence
76 ) IS
77
78 l_txn_if_id NUMBER;
79 l_serial_temp_id NUMBER;
80 l_lot_status_id NUMBER;
81 l_serial_status_id NUMBER;
82 l_lot_expiration_date DATE;
83 l_prod_code VARCHAR2(5) := inv_rcv_integration_apis.G_PROD_CODE;
84 l_product_txn_id NUMBER;
85 l_yes VARCHAR2(1) := inv_rcv_integration_apis.G_YES;
86 l_no VARCHAR2(1) := inv_rcv_integration_apis.G_NO;
87 l_false VARCHAR2(1) := inv_rcv_integration_apis.G_FALSE;
88 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
89 l_is_rma NUMBER;
90
91 l_origination_type number;--OPM Convergence
92 l_expiration_action_code VARCHAR2(32);--OPM Convergence
93 l_expiration_action_date DATE;--OPM Convergence
94 l_hold_date DATE;--OPM Convergence
95 l_reason_id number;--OPM Convergence
96 BEGIN
97
98 --Initialize the return status
99 x_return_status := fnd_api.g_ret_sts_success;
100
101 l_product_txn_id := p_product_transaction_id;
102
103 --First create the MTLI record for the lot that was inspected
104 IF (p_lot_control_code > 1 AND p_lot_number IS NOT NULL) THEN
105 SELECT expiration_date
106 , status_id
107 , origination_type --OPM Convergence
108 , expiration_action_code --OPM Convergence
109 , expiration_action_date --OPM Convergence
110 , hold_date --OPM Convergence
111 INTO l_lot_expiration_date
112 , l_lot_status_id
113 , l_origination_type --OPM Convergence
114 , l_expiration_action_code --OPM Convergence
115 , l_expiration_action_date --OPM Convergence
116 , l_hold_date --OPM Convergence
117 FROM mtl_lot_numbers
118 WHERE lot_number = p_lot_number
119 AND inventory_item_id = p_inventory_item_id
120 AND organization_id = p_organization_id;
121
122 --Call the insert_mtli API
123 inv_rcv_integration_apis.insert_mtli(
124 p_api_version => 1.0
125 , p_init_msg_lst => l_false
126 , x_return_status => x_return_status
127 , x_msg_count => x_msg_count
128 , x_msg_data => x_msg_data
129 , p_transaction_interface_id => l_txn_if_id
130 , p_lot_number => p_lot_number
131 , p_transaction_quantity => p_txn_qty
132 , p_primary_quantity => p_primary_qty
133 , p_organization_id => p_organization_id
134 , p_inventory_item_id => p_inventory_item_id
135 , p_expiration_date => l_lot_expiration_date
136 , p_status_id => l_lot_status_id
137 , x_serial_transaction_temp_id => l_serial_temp_id
138 , p_product_transaction_id => l_product_txn_id
139 , p_product_code => l_prod_code
140 , p_att_exist => l_yes
141 , p_update_mln => l_no
142 , p_origination_type => l_origination_type--OPM Convergence
143 , p_expiration_action_code => l_expiration_action_code--OPM Convergence
144 , p_expiration_action_date => l_expiration_action_date--OPM Convergence
145 , p_hold_date => l_hold_date);--OPM Convergence
146
147
148
149 IF x_return_status = fnd_api.g_ret_sts_error THEN
150 RAISE fnd_api.g_exc_error;
151 END IF;
152
153 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
154 RAISE fnd_api.g_exc_unexpected_error;
155 END IF;
156
157 IF (l_debug = 1) THEN
158 print_debug('process_lot_serial_intf 1.1: Created MTLI record for lot: txn if: ' || l_txn_if_id || ', serial_temp_id: ' ||
159 l_serial_temp_id || ', prod_txn_id: ' || l_product_txn_id , 4);
160 END IF;
161
162 --Bug #3405320
163 --For items that are serial controlled at SO Issue, need to NULL out
164 --serial_transaction_temp_id in the MTLI just generated (for split_lot_serial).
165 --However, if there is a serial to be inspected, in which case the serial
166 --number is passed, do not NULL out the serial_transaction_temp_id
167 IF (p_serial_control_code = 6 AND p_serial_number IS NULL) THEN
168 IF (l_debug = 1) THEN
169 print_debug('process_lot_serial_intf 1.2: serial_control_code IS 6, need TO NULL OUT mtli', 4);
170 END IF;
171
172 UPDATE mtl_transaction_lots_interface
173 SET serial_transaction_temp_id = NULL
174 WHERE product_transaction_id = l_product_txn_id
175 AND product_code = 'RCV';
176 END IF; -- IF (l_is_rma = 1)
177 END IF; --END IF for a lot controlled item
178
179 IF (p_serial_control_code > 1 AND p_serial_number IS NOT NULL) THEN
180 --Get the serial status
181 SELECT status_id
182 INTO l_serial_status_id
183 FROM mtl_serial_numbers
184 WHERE serial_number = p_serial_number
185 AND inventory_item_id = p_inventory_item_id;
186
187 --If the item is also lot controlled then set use the serial_transaction_temp_id
188 --of the MTLI record to create the MSNI record
189 IF (p_lot_control_code > 1 AND p_lot_number IS NOT NULL) THEN
190 l_txn_if_id := l_serial_temp_id;
191 END IF;
192
193 --Call the insert_msni API
194 inv_rcv_integration_apis.insert_msni(
195 p_api_version => 1.0
196 , p_init_msg_lst => l_false
197 , x_return_status => x_return_status
198 , x_msg_count => x_msg_count
199 , x_msg_data => x_msg_data
200 , p_transaction_interface_id => l_txn_if_id
201 , p_fm_serial_number => p_serial_number
202 , p_to_serial_number => p_serial_number
203 , p_organization_id => p_organization_id
204 , p_inventory_item_id => p_inventory_item_id
205 , p_status_id => l_serial_status_id
206 , p_product_transaction_id => l_product_txn_id
207 , p_product_code => l_prod_code
208 , p_att_exist => l_yes
209 , p_update_msn => l_no);
210
211 IF x_return_status = fnd_api.g_ret_sts_error THEN
212 RAISE fnd_api.g_exc_error;
213 END IF;
214
215 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
216 RAISE fnd_api.g_exc_unexpected_error;
217 END IF;
218
219 IF (l_debug = 1) THEN
220 print_debug('process_lot_serial_intf 1.2: Created MSNI record for serial: ' || p_serial_number || ' with txn_if_id: '
221 || l_txn_if_id || ', prod_txn_id: ' || l_product_txn_id , 4);
222 END IF;
223 END IF; --END IF for a serial controlled item
224
225 EXCEPTION
226 WHEN fnd_api.g_exc_error THEN
227 x_return_status := fnd_api.g_ret_sts_error;
228 -- Get message count and data
229 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
230 WHEN fnd_api.g_exc_unexpected_error THEN
231 x_return_status := fnd_api.g_ret_sts_unexp_error;
232 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
233 WHEN OTHERS THEN
234 x_return_status := fnd_api.g_ret_sts_unexp_error;
235 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
236 fnd_msg_pub.add_exc_msg(g_pkg_name, 'process_lot_serial_intf');
237 END IF;
238 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
239 END process_lot_serial_intf;
240
241 PROCEDURE main_process(
242 x_return_status OUT NOCOPY VARCHAR2
243 , x_msg_count OUT NOCOPY NUMBER
244 , x_msg_data OUT NOCOPY VARCHAR2
245 , p_inventory_item_id IN NUMBER
246 , p_organization_id IN NUMBER
247 , p_lpn_id IN NUMBER
248 , p_revision IN VARCHAR2
249 , p_lot_number IN VARCHAR2
250 , p_uom_code IN VARCHAR2
251 , p_quantity IN NUMBER
252 , p_inspection_code IN VARCHAR2
253 , p_quality_code IN VARCHAR2
254 , p_transaction_type IN VARCHAR2
255 , p_reason_id IN NUMBER
256 , p_serial_number IN VARCHAR2
257 , p_accept_lpn_id IN NUMBER
258 , p_reject_lpn_id IN NUMBER
259 , p_transaction_date IN DATE DEFAULT SYSDATE
260 , p_qa_collection_id IN NUMBER DEFAULT NULL
261 , p_vendor_lot IN VARCHAR2 DEFAULT NULL
262 , p_comments IN VARCHAR2 DEFAULT NULL
263 , p_attribute_category IN VARCHAR2 DEFAULT NULL
264 , p_attribute1 IN VARCHAR2 DEFAULT NULL
265 , p_attribute2 IN VARCHAR2 DEFAULT NULL
266 , p_attribute3 IN VARCHAR2 DEFAULT NULL
267 , p_attribute4 IN VARCHAR2 DEFAULT NULL
268 , p_attribute5 IN VARCHAR2 DEFAULT NULL
269 , p_attribute6 IN VARCHAR2 DEFAULT NULL
270 , p_attribute7 IN VARCHAR2 DEFAULT NULL
271 , p_attribute8 IN VARCHAR2 DEFAULT NULL
272 , p_attribute9 IN VARCHAR2 DEFAULT NULL
273 , p_attribute10 IN VARCHAR2 DEFAULT NULL
274 , p_attribute11 IN VARCHAR2 DEFAULT NULL
275 , p_attribute12 IN VARCHAR2 DEFAULT NULL
276 , p_attribute13 IN VARCHAR2 DEFAULT NULL
277 , p_attribute14 IN VARCHAR2 DEFAULT NULL
278 , p_attribute15 IN VARCHAR2 DEFAULT NULL
279 , p_secondary_qty IN NUMBER DEFAULT NULL) --OPM Convergence
280 IS
281 l_inventory_item_id NUMBER := p_inventory_item_id;
282 l_organization_id NUMBER := p_organization_id;
283 l_lpn_id NUMBER := p_lpn_id;
284 l_revision VARCHAR2(10) := p_revision;
285 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
286 l_lot_number VARCHAR2(80) := p_lot_number;
287 l_uom_code VARCHAR2(5) := p_uom_code;
288 l_uom VARCHAR2(30);
289 l_quantity NUMBER := p_quantity;
290 l_serial_number VARCHAR2(30) := p_serial_number;
291 l_accept_lpn_id NUMBER := p_accept_lpn_id;
292 l_reject_lpn_id NUMBER := p_reject_lpn_id;
293 l_rti_lpn_id NUMBER;
294 l_rti_transfer_lpn_id NUMBER;
295 l_inspection_code VARCHAR2(25) := p_inspection_code;
296 l_quality_code VARCHAR2(25) := p_quality_code;
297 l_transaction_date DATE := p_transaction_date;
298 l_comments VARCHAR2(240) := p_comments;
299 l_attribute_category VARCHAR2(30) := p_attribute_category;
300 l_attribute1 VARCHAR2(150) := p_attribute1;
301 l_attribute2 VARCHAR2(150) := p_attribute2;
302 l_attribute3 VARCHAR2(150) := p_attribute3;
303 l_attribute4 VARCHAR2(150) := p_attribute4;
304 l_attribute5 VARCHAR2(150) := p_attribute5;
305 l_attribute6 VARCHAR2(150) := p_attribute6;
306 l_attribute7 VARCHAR2(150) := p_attribute7;
307 l_attribute8 VARCHAR2(150) := p_attribute8;
308 l_attribute9 VARCHAR2(150) := p_attribute9;
309 l_attribute10 VARCHAR2(150) := p_attribute10;
310 l_attribute11 VARCHAR2(150) := p_attribute11;
311 l_attribute12 VARCHAR2(150) := p_attribute12;
312 l_attribute13 VARCHAR2(150) := p_attribute13;
313 l_attribute14 VARCHAR2(150) := p_attribute14;
314 l_attribute15 VARCHAR2(150) := p_attribute15;
315 l_transaction_type VARCHAR2(30) := p_transaction_type;
316 l_vendor_lot VARCHAR2(30) := p_vendor_lot;
317 l_reason_id NUMBER := p_reason_id;
318 l_qa_collection_id NUMBER := p_qa_collection_id;
319 l_primary_qty NUMBER;
320 l_primary_uom_code VARCHAR2(5);
321 l_mol_line_id NUMBER;
322 l_mol_new_line_id NUMBER;
323 l_mol_header_id NUMBER;
324 l_mol_uom_code VARCHAR2(5);
325 l_mol_qty NUMBER;
326 l_rcv_transaction_id NUMBER;
327 l_rtv_qty NUMBER;
328 l_rls_qty NUMBER;
329 l_cnv_rls_qty NUMBER; -- Added for bug 6688055
330 l_rtv_uom VARCHAR2(25); /* Each */
331 l_rtv_uom_code VARCHAR2(5); /* Ea */
332 l_receipt_source_code VARCHAR2(25);
333 l_tolerable_qty NUMBER;
334 l_remaining_qty NUMBER;
335 l_remaining_mol_qty NUMBER;
336 l_inspection_status NUMBER;
337 l_return_status VARCHAR2(5);
338 l_msg_count NUMBER;
339 l_msg_data VARCHAR2(1000);
340 l_rec_count NUMBER;
341
342 l_secondary_qty NUMBER := p_secondary_qty; --OPM COnvergence
343 l_remaining_sec_qty NUMBER; --OPM Convergence
344 l_rtv_sec_uom VARCHAR2(25);--OPM Convergence
345 l_sec_uom_code VARCHAR2(3);--OPM Convergence
346 l_sec_uom VARCHAR2(25);--OPM Convergence
347 l_sec_mol_qty NUMBER;--OPM COnvergence
348 l_sec_remaining_mol_qty NUMBER;--OPM Convergence
349 l_sec_remaining_qty NUMBER; --OPM Convergence
350 l_rtv_sec_qty NUMBER;--OPM Convergence
351 l_processed_lot_prim_qty NUMBER;
352
353 TYPE number_tb_tp IS TABLE OF NUMBER
354 INDEX BY BINARY_INTEGER;
355
356 l_mmtt_ids number_tb_tp;
357 l_primary_quantities number_tb_tp;
358 l_transaction_quantities number_tb_tp;
359 L_SECONDARY_TXN_QUANTITIES number_tb_tp; --OPM Convergence
360
361
362 /* FP-J Lot/Serial Support Enhancement
363 * If WMS and PO J are installed, then the move order line quantity updates
364 * will be handled by the receiving TM. The logic for MO handling would be:
365 * If MOL quantity > Inspection Quantity Then
366 * Do not update quantity. Set the process_flag to 2 so that
367 * this line does not get picked up again.
368 * Split the move order line to create one for the uninspected quantity
369 * Else
370 * Do not update quantity. Set the process_flag to 2 so that this line
371 * does not get picked up again.
372 * End If
373 * If either WMS or PO J are not installed, retain the original processing
374 * So am opening the cursor with a new parameter k_wms_po_j_higher.
375 * If this flag is set, then filter the move order lines on process_flag (=1)
376 * If this flag is not set, then filter lines on quantity
377 */
378 CURSOR mol_cursor(
379 k_inventory_item_id NUMBER
380 , k_organization_id NUMBER
381 , k_lpn_id NUMBER
382 , k_revision VARCHAR2
383 , k_lot_number VARCHAR2
384 ) IS
385 SELECT line_id
386 , header_id
387 , uom_code
388 , quantity - NVL(quantity_delivered,0)
389 , secondary_quantity - NVL(secondary_quantity_delivered,0) --OPM Convergence
390 FROM mtl_txn_request_lines
391 WHERE inventory_item_id = k_inventory_item_id
392 AND organization_id = k_organization_id
393 AND lpn_id = k_lpn_id
394 AND (revision = k_revision
395 OR revision IS NULL
396 AND p_revision IS NULL)
397 AND (lot_number = k_lot_number
398 OR lot_number IS NULL
399 AND p_lot_number IS NULL)
400 AND inspection_status = g_to_be_inspected
401 AND line_status = 7
402 AND quantity - Nvl(quantity_delivered,0) > 0
403 AND wms_process_flag = 1
404 ;
405
406 -- MOLCON
407 CURSOR rtv_van_cursor(k_item_id NUMBER,
408 k_item_revision VARCHAR2,
409 k_lpn_id NUMBER) IS
410 SELECT rs.rcv_transaction_id
411 , rsh.receipt_source_code
412 , rs.unit_of_measure
413 , rs.secondary_unit_of_measure --OPM Convergence
414 FROM rcv_supply rs, rcv_transactions rt, rcv_shipment_headers rsh
415 WHERE rs.rcv_transaction_id = rt.transaction_id
416 AND rsh.shipment_header_id = rs.shipment_header_id
417 AND rt.inspection_status_code = 'NOT INSPECTED'
418 AND rs.supply_type_code = 'RECEIVING'
419 AND rt.transaction_type <> 'UNORDERED'
420 AND rt.routing_header_id = g_inspection_routing
421 AND rs.item_id = k_item_id
422 AND (k_item_revision IS NULL -- Bug : 6139900
423 OR nvl(rs.item_revision,'@#*') = nvl(k_item_revision,'@#*'))
424 AND rs.lpn_id = k_lpn_id; --l_lpn_id should always be NOT NULL
425
426 CURSOR rtv_lot_cursor(k_item_id NUMBER,
427 k_item_revision VARCHAR2,
428 k_lpn_id NUMBER,
429 k_lot_number VARCHAR2) IS
430 SELECT rs.rcv_transaction_id
431 , rsh.receipt_source_code
432 , rs.unit_of_measure
433 , rs.secondary_unit_of_measure --OPM Convergence
434 , rls.quantity quantity
435 FROM rcv_supply rs, rcv_lots_supply rls, rcv_transactions rt, rcv_shipment_headers rsh
436 WHERE rs.rcv_transaction_id = rt.transaction_id
437 AND rsh.shipment_header_id = rs.shipment_header_id
438 AND rt.inspection_status_code = 'NOT INSPECTED'
439 AND rs.supply_type_code = 'RECEIVING'
440 AND rt.transaction_type <> 'UNORDERED'
441 AND rt.routing_header_id = g_inspection_routing
442 AND rs.item_id = k_item_id
443 AND (k_item_revision IS NULL -- Bug : 6139900
444 OR nvl(rs.item_revision,'@#*') = nvl(k_item_revision,'@#*'))
445 AND rs.lpn_id = k_lpn_id --l_lpn_id should always be NOT NULL
446 AND rls.transaction_id = rs.rcv_transaction_id
447 AND rls.lot_num = k_lot_number;
448
449 CURSOR rtv_serial_cursor(k_item_id NUMBER,
450 k_item_revision VARCHAR2,
451 k_lpn_id NUMBER,
452 k_serial_number VARCHAR2,
453 k_lot_number VARCHAR2) IS
454 SELECT rs.rcv_transaction_id
455 , rsh.receipt_source_code
456 , rs.unit_of_measure
457 , rs.secondary_unit_of_measure --OPM Convergence
458 FROM rcv_supply rs, rcv_serials_supply rss, rcv_transactions rt, rcv_shipment_headers rsh
459 WHERE rs.rcv_transaction_id = rt.transaction_id
460 AND rsh.shipment_header_id = rs.shipment_header_id
461 AND rt.inspection_status_code = 'NOT INSPECTED'
462 AND rs.supply_type_code = 'RECEIVING'
463 AND rt.transaction_type <> 'UNORDERED'
464 AND rt.routing_header_id = g_inspection_routing
465 AND rs.item_id = k_item_id
466 AND (k_item_revision IS NULL -- Bug : 6139900
467 OR nvl(rs.item_revision,'@#*') = nvl(k_item_revision,'@#*'))
468 AND rs.lpn_id = k_lpn_id --l_lpn_id should always be NOT NULL
469 AND rss.transaction_id = rs.rcv_transaction_id
470 AND rss.serial_num = k_serial_number
471 AND rss.supply_type_code = 'RECEIVING'
472 AND Nvl(rss.lot_num,'@#@') = Nvl(k_lot_number,'@#@');
473 -- MOLCON
474
475 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
476 --New variables for Lot/Serial Support
477 l_lot_control_code NUMBER;
478 l_serial_control_code NUMBER;
479 l_mo_splt_tb inv_rcv_integration_apis.mo_in_tb_tp;
480 l_txn_qty_to_split NUMBER;
481 l_primary_qty_to_split NUMBER;
482 l_new_mol_id NUMBER;
483 l_split_line_id NUMBER; --for debug
484 l_progress VARCHAR2(10) := '0';
485
486 BEGIN
487 x_return_status := fnd_api.g_ret_sts_success;
488
489 IF (l_debug = 1) THEN
490 print_debug('main_process: Just entering main_process', 4);
491 print_debug('p_inventory_item_id => '||p_inventory_item_id,4);
492 print_debug('p_organization_id => '||p_organization_id,4);
493 print_debug('p_lpn_id => '||p_lpn_id,4);
494 print_debug('p_revision => '||p_revision,4);
495 print_debug('p_lot_number => '||p_lot_number,4);
496 print_debug('p_uom_code => '||p_uom_code,4);
497 print_debug('p_quantity => '||p_quantity,4);
498 print_debug('p_serial_number => '||p_serial_number,4);
499 print_debug('p_inspection_code => '||p_inspection_code,4);
500 END IF;
501
502 --First check if the transaction date satisfies the validation.
503 --If the transaction date is invalid then error out the transaction
504 IF inv_rcv_common_apis.g_po_startup_value.sob_id IS NULL THEN
505 --BUG 3444196: Used the HR view instead for performance reasons
506 SELECT TO_NUMBER(hoi.org_information1)
507 INTO inv_rcv_common_apis.g_po_startup_value.sob_id
508 FROM hr_organization_information hoi
509 WHERE hoi.organization_id = p_organization_id
510 AND (hoi.org_information_context || '') = 'Accounting Information' ;
511 END IF;
512
513 inv_rcv_common_apis.validate_trx_date(
514 p_trx_date => SYSDATE
515 , p_organization_id => p_organization_id
516 , p_sob_id => inv_rcv_common_apis.g_po_startup_value.sob_id
517 , x_return_status => x_return_status
518 , x_error_code => x_msg_data
519 );
520
521 IF x_return_status <> fnd_api.g_ret_sts_success THEN
522 RETURN;
523 END IF;
524
525 SAVEPOINT inspect_main_sp;
526
527 -- Quantity entered on form
528 l_remaining_qty := l_quantity;
529
530 -- Mapping of Inspection code values from Receiving to WMS/Inventory.
531 -- Wrong habit to hardcode but that's what receiving
532 -- transactions interface expects.
533 -- Inspection Status values:
534 -- 1 - Yet to be inspected
535 -- 2 - Accepted
536 -- 3 - Rejected
537
538
539 IF l_inspection_code = 'ACCEPT' THEN
540 l_inspection_status := g_accept; /* Accept */
541 ELSE
542 l_inspection_status := g_reject; /* Reject */
543 END IF;
544
545 l_primary_uom_code := inv_rcv_cache.get_primary_uom_code(l_organization_id,l_inventory_item_id);
546 l_sec_uom_code := inv_rcv_cache.get_secondary_uom_code(l_organization_id,l_inventory_item_id);
547
548 -- Purchasing/receiving uses unit of measure (Each)
549 -- rather than uom code(Ea) and hence the following..
550 -- This will be used later while inserting into interface table
551
552 SELECT unit_of_measure
553 INTO l_uom
554 FROM mtl_units_of_measure
555 WHERE uom_code = l_uom_code;
556
557 /*OPM Convergence */
558 IF l_sec_uom_code IS NOT NULL THEN
559
560 SELECT unit_of_measure
561 INTO l_sec_uom
562 FROM mtl_units_of_measure
563 WHERE uom_code = l_sec_uom_code;
564
565 END IF;
566
567 -- Open Move Order Line cursor
568 OPEN mol_cursor(
569 l_inventory_item_id
570 , l_organization_id
571 , l_lpn_id
572 , l_revision
573 , l_lot_number
574 );
575
576 WHILE(l_remaining_qty > 0) LOOP
577
578 IF (l_debug = 1) THEN
579 print_debug('Main process l_remaining_qty : ' || TO_CHAR(l_remaining_qty), 4);
580 END IF;
581
582 -- MOLCON
583 FETCH mol_cursor
584 INTO l_mol_line_id
585 , l_mol_header_id
586 , l_mol_uom_code
587 , l_mol_qty
588 , l_sec_mol_qty; --OPM Convergence
589 -- MOLCON
590
591 IF mol_cursor%NOTFOUND THEN
592 EXIT;
593 END IF;
594
595 IF (l_debug = 1) THEN
596 print_debug(' l_mol_line_id :'||l_mol_line_id,4);
597 print_debug(' l_mol_header_id:'||l_mol_header_id,4);
598 print_debug(' l_mol_qty :'||l_mol_qty,4);
599 print_debug(' l_mol_uom_code :'||l_mol_uom_code,4);
600 print_debug(' l_uom_code :'||l_uom_code,4);
601 print_debug(' l_sec_mol_qty :'||l_sec_mol_qty,4);
602 END IF;
603
604 -- If inspection uom is not same as move order uom, we convert
605 IF (l_uom_code <> l_mol_uom_code) THEN
606 l_mol_qty := inv_rcv_cache.convert_qty
607 (p_inventory_item_id => l_inventory_item_id
608 ,p_from_qty => l_mol_qty
609 ,p_from_uom_code => l_mol_uom_code
610 ,p_to_uom_code => l_uom_code);
611 END IF;
612
613 IF (l_debug = 1) THEN
614 print_debug('main process l_mol_qty ' || TO_CHAR(l_mol_qty), 4);
615 print_debug('main process l_mol_line_id ' || l_mol_line_id, 4);
616 END IF;
617
618 -- l_remaing_mol_qty := min(l_remaining_qty, l_mol_qty)
619 IF (l_mol_qty >= l_remaining_qty) THEN
620 l_remaining_mol_qty := l_remaining_qty;
621 l_sec_remaining_mol_qty := l_sec_remaining_qty; --OPM Convergence
622 l_remaining_qty := 0;
623 l_sec_remaining_qty := 0; --OPM Convergence
624 ELSE
625 l_remaining_mol_qty := l_mol_qty;
626 l_sec_remaining_mol_qty := l_sec_mol_qty; --OPM Convergence
627 l_remaining_qty := l_remaining_qty - l_mol_qty;
628 l_sec_remaining_qty := l_sec_remaining_qty - l_sec_mol_qty; --OPM Convergence
629 END IF;
630
631 IF (l_debug = 1) THEN
632 print_debug('main process: l_remaining_mol_qty = min(l_mol_qty, l_remaining_qty) = ' || l_remaining_mol_qty, 4);
633 END IF;
634
635 -- Open Rcv Transactions cursor
636 -- MOLCON
637 IF (l_serial_number IS NOT NULL) THEN
638 OPEN rtv_serial_cursor(
639 l_inventory_item_id
640 , l_revision
641 , l_lpn_id
642 , l_serial_number
643 , l_lot_number);
644 ELSIF (l_lot_number IS NOT NULL) THEN
645 OPEN rtv_lot_cursor(
646 l_inventory_item_id
647 , l_revision
648 , l_lpn_id
649 , l_lot_number);
650 ELSE
651 OPEN rtv_van_cursor(
652 l_inventory_item_id
653 , l_revision
654 , l_lpn_id);
655 END IF;
656
657 -- One MOL can only be tied to 1 RT, which can only has 1 RS
658 -- So, at least for J or higher, assume that this loop
659 -- will only be executed once
660
661 WHILE(l_remaining_mol_qty > 0) LOOP
662 IF (l_debug = 1) THEN
663 print_debug('Main process l_remaining_mol_qty : ' || TO_CHAR(l_remaining_mol_qty), 4);
664 END IF;
665
666 -- MOLCON
667 -- LOOP FROM THE FETCH HERE FOR RTV CURSOR
668 -- AS THERE MAY BE MULTIPLE RT ROWS FOR SINGLE MOL LINE NOW
669 -- RTV_CURSOR IF NOTHING IS FOUND AND STILL REMAINING QTY EXISTS FAIL THE TXN
670 -- ALSO FOR THE CONDITION for L_RTV_QTY > 0 , THE ELSE PART IS NOT NEEDED
671 -- AS IF THERE ARE MULTIPLE RT's FETCHED THEN THERE ARE PARENT RECEIPT TXN's
672 -- FOR WHICH SOME RTI IS ALREADY CREATED FOR INSPECTION TXN.
673 -- MOLCON
674
675 IF (l_serial_number IS NOT NULL) THEN
676 FETCH rtv_serial_cursor
677 INTO l_rcv_transaction_id
678 , l_receipt_source_code
679 , l_rtv_uom
680 , l_rtv_sec_uom; --OPM Convergence
681
682 IF rtv_serial_cursor%NOTFOUND THEN
683 -- MOLCON
684 -- CHECK FOR ERROR HERE
685 IF l_remaining_mol_qty > 0 then
686 fnd_message.set_name('INV', 'INV_RCV_NO_ROWS');
687 fnd_msg_pub.ADD;
688 RAISE fnd_api.g_exc_error;
689 END IF;
690 -- MOLCON
691 EXIT;
692 END IF;
693
694 ELSIF (l_lot_number IS NOT NULL) THEN
695 FETCH rtv_lot_cursor
696 INTO l_rcv_transaction_id
697 , l_receipt_source_code
698 , l_rtv_uom
699 , l_rtv_sec_uom--OPM Convergence
700 , l_rls_qty;
701
702 IF rtv_lot_cursor%NOTFOUND THEN
703 -- MOLCON
704 -- CHECK FOR ERROR HERE
705 IF l_remaining_mol_qty > 0 then
706 fnd_message.set_name('INV', 'INV_RCV_NO_ROWS');
707 fnd_msg_pub.ADD;
708 RAISE fnd_api.g_exc_error;
709 END IF;
710 -- MOLCON
711 EXIT;
712 END IF;
713
714 ELSE
715 FETCH rtv_van_cursor
716 INTO l_rcv_transaction_id
717 , l_receipt_source_code
718 , l_rtv_uom
719 , l_rtv_sec_uom; --OPM Convergence
720
721 IF rtv_van_cursor%NOTFOUND THEN
722 -- MOLCON
723 -- CHECK FOR ERROR HERE
724 IF l_remaining_mol_qty > 0 then
725 fnd_message.set_name('INV', 'INV_RCV_NO_ROWS');
726 fnd_msg_pub.ADD;
727 RAISE fnd_api.g_exc_error;
728 END IF;
729 -- MOLCON
730 EXIT;
731 END IF;
732 END IF; --END IF (l_serial_number IS NOT NULL) THEN
733
734 IF (l_debug = 1) THEN
735 print_debug('l_rcv_transaction_id:'||l_rcv_transaction_id||
736 ' l_receipt_source_code:'||l_receipt_source_code||
737 ' l_rtv_uom:'||l_rtv_uom||
738 ' l_rtv_sec_uom:'||l_rtv_sec_uom||
739 ' l_rls_qty:'||l_rls_qty,4);
740 END IF;
741
742 IF (l_serial_number IS NOT NULL) THEN
743 l_rtv_qty := 1;
744 ELSIF (l_lot_number IS NOT NULL) THEN
745 BEGIN
746 SELECT SUM(Nvl(mtli.primary_quantity,0))
747 INTO l_processed_lot_prim_qty
748 FROM mtl_transaction_lots_interface mtli
749 , rcv_transactions_interface rti
750 WHERE mtli.product_code = 'RCV'
751 AND mtli.product_transaction_id = rti.interface_transaction_id
752 AND mtli.lot_number = l_lot_number
753 AND rti.parent_transaction_id = l_rcv_transaction_id
754 AND rti.transaction_status_code = 'PENDING'
755 AND rti.processing_status_code <> 'ERROR';
756
757 IF (l_processed_lot_prim_qty IS NULL) THEN
758 l_processed_lot_prim_qty := 0;
759 END IF;
760 EXCEPTION
761 WHEN OTHERS THEN
762 l_processed_lot_prim_qty := 0;
763 END;
764
765 IF (l_debug = 1) THEN
766 print_debug('l_processed_lot_prim_qty: '||l_processed_lot_prim_qty,4);
767 END IF;
768
769 -- Modified for bug 6688055
770 -- Convert l_rtv_uom(Each) into l_rtv_uom_code(Ea)
771 SELECT uom_code
772 INTO l_rtv_uom_code
773 FROM mtl_units_of_measure
774 WHERE unit_of_measure = l_rtv_uom;
775
776 IF (l_debug = 1) THEN
777 print_debug('l_rtv_uom_code: '||l_rtv_uom_code||' l_uom_code : '||l_uom_code,4);
778 END IF;
779
780 IF (l_uom_code <> l_rtv_uom_code) THEN
781 l_cnv_rls_qty := inv_rcv_cache.convert_qty
782 (p_inventory_item_id => l_inventory_item_id
783 ,p_from_qty => l_rls_qty
784 ,p_from_uom_code => l_rtv_uom_code
785 ,p_to_uom_code => l_uom_code);
786 ELSE
787 l_cnv_rls_qty := l_rls_qty;
788 END IF;
789
790 IF (l_debug = 1) THEN
791 print_debug('l_cnv_rls_qty : '||l_cnv_rls_qty,4);
792 END IF;
793
794
795 -- If inspection uom is not same as receipt uom, convert
796 /*
797 IF (l_primary_uom_code <> l_uom_code) THEN
798 l_rtv_qty := l_rls_qty - inv_rcv_cache.convert_qty
799 (p_inventory_item_id => l_inventory_item_id
800 ,p_from_qty => l_processed_lot_prim_qty
801 ,p_from_uom_code => l_primary_uom_code
802 ,p_to_uom_code => l_uom_code);
803 ELSE
804 l_rtv_qty := l_rls_qty - l_processed_lot_prim_qty;
805 END IF;
806 */
807 IF (l_primary_uom_code <> l_uom_code) THEN
808 l_rtv_qty := l_cnv_rls_qty - inv_rcv_cache.convert_qty
809 (p_inventory_item_id => l_inventory_item_id
810 ,p_from_qty => l_processed_lot_prim_qty
811 ,p_from_uom_code => l_primary_uom_code
812 ,p_to_uom_code => l_uom_code);
813 ELSE
814 l_rtv_qty := l_cnv_rls_qty - l_processed_lot_prim_qty;
815 END IF;
816
817
818 -- Modification for bug 6688055 ended
819 ELSE
820 rcv_quantities_s.get_available_quantity(
821 'INSPECT'
822 , l_rcv_transaction_id
823 , l_receipt_source_code
824 , NULL
825 , l_rcv_transaction_id
826 , NULL
827 , l_rtv_qty
828 , l_tolerable_qty
829 , l_rtv_uom);
830
831 IF (l_debug = 1) THEN
832 print_debug('main process l_rtv_qty : ' || TO_CHAR(l_rtv_qty), 4);
833 END IF;
834
835 IF (l_rtv_qty > 0) THEN
836 -- Purchasing/receiving uses unit of measure (Each)
837 -- rather than uom code(Ea) and hence the following..
838 -- Convert l_rtv_uom(Each) into l_rtv_uom_code(Ea)
839 SELECT uom_code
840 INTO l_rtv_uom_code
841 FROM mtl_units_of_measure
842 WHERE unit_of_measure = l_rtv_uom;
843
844 -- If inspection uom is not same as receipt uom, convert
845
846 IF (l_uom_code <> l_rtv_uom_code) THEN
847 l_rtv_qty := inv_rcv_cache.convert_qty
848 (p_inventory_item_id => l_inventory_item_id
849 ,p_from_qty => l_rtv_qty
850 ,p_from_uom_code => l_rtv_uom_code
851 ,p_to_uom_code => l_uom_code);
852 END IF;
853 END IF;
854 END IF;
855
856 IF (l_rtv_qty > 0) THEN
857 IF l_rtv_qty >= l_remaining_mol_qty THEN
858 IF (l_debug = 1) THEN
859 print_debug('main_process: l_rtv >= l_remaining_mol_qty', 4);
860 END IF;
861 l_rtv_qty := l_remaining_mol_qty;
862 l_remaining_mol_qty := 0;
863 ELSE
864 IF (l_debug = 1) THEN
865 print_debug('main_process: l_rtv < l_remaining_mol_qty', 4);
866 END IF;
867 l_remaining_mol_qty := l_remaining_mol_qty - l_rtv_qty;
868 END IF;
869
870 IF (l_debug = 1) THEN
871 print_debug('main_process: l_rtv_qty = min(available qty, l_remaining_mol_qty) = ' || l_rtv_qty, 4);
872 END IF;
873
874 -- If required convert into primary unit of measure
875 IF (l_uom_code <> l_primary_uom_code) THEN
876 l_primary_qty := inv_rcv_cache.convert_qty
877 (p_inventory_item_id => l_inventory_item_id
878 ,p_from_qty => l_rtv_qty
879 ,p_from_uom_code => l_uom_code
880 ,p_to_uom_code => l_primary_uom_code);
881 ELSE
882 l_primary_qty := l_rtv_qty;
883 END IF;
884
885 IF l_inspection_status = g_accept THEN
886 IF (l_accept_lpn_id > 0) THEN
887 l_rti_lpn_id := l_lpn_id;
888 l_rti_transfer_lpn_id := l_accept_lpn_id;
889 ELSE
890 l_rti_lpn_id := l_lpn_id;
891 END IF;
892 ELSE
893 IF (l_reject_lpn_id > 0) THEN
894 l_rti_lpn_id := l_lpn_id;
895 l_rti_transfer_lpn_id := l_reject_lpn_id;
896 ELSE
897 l_rti_lpn_id := l_lpn_id;
898 END IF;
899 END IF;
900
901 -- If l_rtv_quantity < l_mol_qty
902 -- Split MOL
903 -- Create new RTI for each MMTT in the new MOL
904 -- Insert Lot/Serials Interface record for each of these RTI
905 -- Call ATF API
906 IF (l_debug = 1) THEN
907 print_debug('main_process : inside RTV cursor Loop, before split_mo', 4);
908 print_debug(' l_rtv_qty =======> ' || l_rtv_qty, 4);
909 print_debug(' l_remaining_mol_qty => ' || l_remaining_mol_qty, 4);
910 print_debug(' l_primary_qty ===> ' || l_primary_qty, 4);
911 print_debug(' l_mol_qty =======> ' || l_mol_qty, 4);
912 print_debug(' l_remaining_qty => ' || l_remaining_qty, 4);
913 print_debug(' l_mol_line_id ===> ' || l_mol_line_id, 4);
914 END IF;
915
916 IF (l_rtv_qty < l_mol_qty) THEN
917 l_mo_splt_tb(1).prim_qty := l_primary_qty;
918
919 IF (l_debug = 1) THEN
920 print_debug('main_process : Calling split_mo: ' || l_return_status, 4);
921 print_debug(' p_orig_mol_id ============> ' || l_mol_line_id, 4);
922 print_debug(' p_mo_splt_tb(1).prim_qty => ' || l_mo_splt_tb(1).prim_qty, 4);
923 END IF;
924
925 inv_rcv_integration_apis.split_mo(
926 p_orig_mol_id => l_mol_line_id
927 , p_mo_splt_tb => l_mo_splt_tb
928 , x_return_status => l_return_status
929 , x_msg_count => l_msg_count
930 , x_msg_data => l_msg_data);
931
932 IF (l_debug = 1) THEN
933 print_debug('main_process : Call to split_mo returns: ' || l_return_status, 4);
934 END IF;
935
936 IF l_return_status = fnd_api.g_ret_sts_error THEN
937 RAISE fnd_api.g_exc_error;
938 END IF;
939
940 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
941 RAISE fnd_api.g_exc_unexpected_error;
942 END IF;
943 l_new_mol_id := l_mo_splt_tb(1).line_id;
944 ELSE
945 l_new_mol_id := l_mol_line_id;
946 END IF; -- IF (l_remaining_mol_qty < l_mol_qty) THEN
947
948 l_progress := '50';
949 -- clear records before bulk collecting
950 IF (l_mmtt_ids.COUNT > 0) THEN
951 l_mmtt_ids.DELETE;
952 END IF;
953
954 l_progress := '60';
955 IF (l_transaction_quantities.COUNT > 0) THEN
956 l_transaction_quantities.DELETE;
957 END IF;
958
959 /* OPM Convergence */
960 IF (l_secondary_txn_quantities.COUNT > 0) THEN
961 l_secondary_txn_quantities.DELETE;
962 END IF;
963 l_progress := '70';
964 IF (l_primary_quantities.COUNT > 0) THEN
965 l_primary_quantities.DELETE;
966 END IF;
967
968 l_progress := '80';
969 BEGIN
970 print_debug('Select mmtt records based on move_order_line_id order by transaction_temp_id', 4); --6160359,6189438
971 SELECT transaction_temp_id
972 , primary_quantity
973 , DECODE(transaction_uom
974 , l_uom_code
975 , transaction_quantity /*Bug6133345*/
976 , inv_rcv_cache.convert_qty
977 (l_inventory_item_id
978 ,transaction_quantity
979 ,transaction_uom
980 ,l_uom_code
981 ,NULL)
982 ) quantity
983 , secondary_transaction_quantity --OPM Convergence
984 BULK COLLECT INTO
985 l_mmtt_ids
986 , l_primary_quantities
987 , l_transaction_quantities
988 , l_secondary_txn_quantities --OPM Convergence
989 FROM mtl_material_transactions_temp
990 WHERE move_order_line_id = l_new_mol_id;
991 EXCEPTION
992 WHEN OTHERS THEN
993 l_mmtt_ids(1) := NULL;
994 l_primary_quantities(1) := l_primary_qty;
995 l_transaction_quantities(1) := l_rtv_qty;
996 l_secondary_txn_quantities(1) := l_rtv_sec_qty; --OPM Convergence
997 END;
998
999 l_progress := '90';
1000 -- IF there are no mmtts, then insert RTI with no MMTT id
1001 -- with l_rtv_qty and l_primary_qty
1002 IF (l_mmtt_ids.COUNT = 0) THEN
1003 l_mmtt_ids(1) := NULL;
1004 l_primary_quantities(1) := l_primary_qty;
1005 l_transaction_quantities(1) := l_rtv_qty;
1006 l_secondary_txn_quantities(1) := l_rtv_sec_qty; --OPM Convergence
1007 END IF;
1008
1009 l_progress := '100';
1010
1011 FOR i IN 1 .. l_mmtt_ids.COUNT LOOP
1012 IF (l_debug = 1) THEN
1013 print_debug('Main process inserting RTI for MMTT:' || NVL(l_mmtt_ids(i), -1)
1014 || ' quantity:' || l_transaction_quantities(i) || ' uom:' || l_uom, 4);
1015 END IF;
1016
1017 l_progress := '110';
1018 insert_inspect_rec_rti(
1019 x_return_status => l_return_status
1020 , x_msg_count => l_msg_count
1021 , x_msg_data => l_msg_data
1022 , p_rcv_transaction_id => l_rcv_transaction_id
1023 , p_quantity => l_transaction_quantities(i)
1024 , p_uom => l_uom
1025 , p_inspection_code => l_inspection_code
1026 , p_quality_code => l_quality_code
1027 , p_transaction_date => l_transaction_date
1028 , p_transaction_type => l_transaction_type
1029 , p_vendor_lot => l_vendor_lot
1030 , p_reason_id => l_reason_id
1031 , p_primary_qty => l_primary_quantities(i)
1032 , p_organization_id => l_organization_id
1033 , p_comments => l_comments
1034 , p_attribute_category => l_attribute_category
1035 , p_attribute1 => l_attribute1
1036 , p_attribute2 => l_attribute2
1037 , p_attribute3 => l_attribute3
1038 , p_attribute4 => l_attribute4
1039 , p_attribute5 => l_attribute5
1040 , p_attribute6 => l_attribute6
1041 , p_attribute7 => l_attribute7
1042 , p_attribute8 => l_attribute8
1043 , p_attribute9 => l_attribute9
1044 , p_attribute10 => l_attribute10
1045 , p_attribute11 => l_attribute11
1046 , p_attribute12 => l_attribute12
1047 , p_attribute13 => l_attribute13
1048 , p_attribute14 => l_attribute14
1049 , p_attribute15 => l_attribute15
1050 , p_qa_collection_id => l_qa_collection_id
1051 , p_lpn_id => l_rti_lpn_id
1052 , p_transfer_lpn_id => l_rti_transfer_lpn_id
1053 , p_mmtt_temp_id => l_mmtt_ids(i)
1054 , p_sec_uom => l_sec_uom --OPM Convergence
1055 , p_secondary_qty => l_rtv_sec_qty); --OPM Convergence
1056
1057 IF l_return_status = fnd_api.g_ret_sts_error THEN
1058 RAISE fnd_api.g_exc_error;
1059 END IF;
1060
1061 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1062 RAISE fnd_api.g_exc_unexpected_error;
1063 END IF;
1064
1065 /* FP-J Lot/Serial Support Enhancement
1066 * Process the lot numbers and serial numbers corresponding to the RTI
1067 * that was just created.
1068 * Since the lots and serials are stored by receiving tables, the
1069 * changes to RTI must be reflected in RCV_LOTS_SUPPLY (lot controlled item)
1070 * and RCV_SERIALS_SUPPLY (serial controlled item).
1071 * We would be creating the interface records in MTLI and MSNI corresponding
1072 * to the inspected quantity, lot number and the serial numbers inspected
1073 * Do this only if WMS and PO patch levels are J or higher
1074 */
1075 SELECT lot_control_code
1076 , serial_number_control_code
1077 INTO l_lot_control_code
1078 , l_serial_control_code
1079 FROM mtl_system_items
1080 WHERE inventory_item_id = p_inventory_item_id
1081 AND organization_id = p_organization_id;
1082
1083 IF (l_lot_control_code > 1 OR l_serial_control_code > 1) THEN
1084 IF (l_debug = 1) THEN
1085 print_debug('creating lots and/or serials interface records with product_transaction_id : '
1086 || g_interface_transaction_id, 4);
1087 END IF;
1088
1089 process_lot_serial_intf(
1090 x_return_status => l_return_status
1091 , x_msg_count => l_msg_count
1092 , x_msg_data => l_msg_data
1093 , p_organization_id => p_organization_id
1094 , p_inventory_item_id => p_inventory_item_id
1095 , p_lot_control_code => l_lot_control_code
1096 , p_serial_control_code => l_serial_control_code
1097 , p_lot_number => p_lot_number
1098 , p_txn_qty => l_transaction_quantities(i)
1099 , p_primary_qty => l_primary_quantities(i)
1100 , p_serial_number => p_serial_number
1101 , p_product_transaction_id => g_interface_transaction_id
1102 , p_lpn_id => p_lpn_id
1103 , p_sec_txn_qty => l_secondary_txn_quantities(i) ); --OPM Convergence
1104
1105 IF (l_debug = 1) THEN
1106 print_debug('main_process: process_lot_serial_intf returns: ' || l_return_status, 4);
1107 END IF;
1108
1109 IF l_return_status = fnd_api.g_ret_sts_error THEN
1110 RAISE fnd_api.g_exc_error;
1111 END IF;
1112
1113 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1114 RAISE fnd_api.g_exc_unexpected_error;
1115 END IF;
1116 END IF; --END IF check lot and serial controls
1117 END LOOP; -- End MMTT Loop
1118
1119 -- Activate the INSPECT operation
1120 l_rec_count := wms_putaway_utils.activate_plan_for_inspect(
1121 x_return_status => x_return_status
1122 , x_msg_count => x_msg_count
1123 , x_msg_data => x_msg_data
1124 , p_org_id => l_organization_id
1125 , p_mo_line_id => l_new_mol_id);
1126
1127 IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
1128 IF (l_debug = 1) THEN
1129 print_debug(' Error in Activate_Plan_For_Load ' || x_msg_data, 1);
1130 END IF;
1131 RAISE fnd_api.g_exc_error;
1132 ELSE
1133 IF (l_debug = 1) THEN
1134 print_debug('Successfully called Activate_Plan_For_Load for ' || l_rec_count || ' row(s)', 9);
1135 END IF;
1136 END IF;
1137
1138 -- Activate the INSPECT operation
1139 --Update the wms_process_flag for the current MOL so that one else
1140 --messes with it
1141 UPDATE mtl_txn_request_lines
1142 SET wms_process_flag = 2
1143 WHERE line_id = l_new_mol_id;
1144 ELSE
1145
1146 IF (l_debug = 1) THEN
1147 print_debug('main_process: There is no quantity available to Inspect: ', 4);
1148 END IF;
1149
1150 -- MOLCON COMMENTED THIS CALL HERE
1151 -- THIS HAS TO BE TRACKED ABOVE
1152 -- fnd_message.set_name('INV', 'INV_RCV_NO_ROWS');
1153 -- fnd_msg_pub.ADD;
1154 -- RAISE fnd_api.g_exc_error;
1155
1156 END IF; --END IF IF (l_rtv_qty > 0)
1157 END LOOP;
1158
1159 IF (rtv_van_cursor%ISOPEN) THEN
1160 CLOSE rtv_van_cursor;
1161 END IF;
1162
1163 IF (rtv_lot_cursor%isopen) THEN
1164 CLOSE rtv_lot_cursor;
1165 END IF;
1166
1167 IF (rtv_serial_cursor%isopen) THEN
1168 CLOSE rtv_serial_cursor;
1169 END IF;
1170 END LOOP; -- WHILE(l_remaining_qty > 0) LOOP
1171
1172 CLOSE mol_cursor;
1173
1174 IF (l_remaining_qty > 0) THEN
1175 IF (l_debug = 1) THEN
1176 print_debug('main_process: No more MOL, but remaining qty still exists', 4);
1177 END IF;
1178 RAISE fnd_api.g_exc_error;
1179 END IF;
1180
1181 EXCEPTION
1182 WHEN fnd_api.g_exc_error THEN
1183 IF (l_debug = 1) THEN
1184 print_debug('Exception raised in main_process at progress: ' || l_progress, 4);
1185 END IF;
1186
1187 ROLLBACK TO inspect_main_sp;
1188 x_return_status := fnd_api.g_ret_sts_error;
1189 -- Get message count and data
1190 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1191
1192 IF (mol_cursor%ISOPEN) THEN
1193 CLOSE mol_cursor;
1194 END IF;
1195
1196 IF (rtv_van_cursor%ISOPEN) THEN
1197 CLOSE rtv_van_cursor;
1198 END IF;
1199
1200 IF (rtv_lot_cursor%isopen) THEN
1201 CLOSE rtv_lot_cursor;
1202 END IF;
1203
1204 IF (rtv_serial_cursor%isopen) THEN
1205 CLOSE rtv_serial_cursor;
1206 END IF;
1207
1208 WHEN fnd_api.g_exc_unexpected_error THEN
1209 IF (l_debug = 1) THEN
1210 print_debug('Exception raised in main_process at progress: ' || l_progress, 4);
1211 END IF;
1212
1213 ROLLBACK TO inspect_main_sp;
1214 x_return_status := fnd_api.g_ret_sts_unexp_error;
1215 -- Get message count and data
1216 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1217
1218 IF (mol_cursor%ISOPEN) THEN
1219 CLOSE mol_cursor;
1220 END IF;
1221
1222
1223 IF (rtv_van_cursor%ISOPEN) THEN
1224 CLOSE rtv_van_cursor;
1225 END IF;
1226
1227 IF (rtv_lot_cursor%isopen) THEN
1228 CLOSE rtv_lot_cursor;
1229 END IF;
1230
1231 IF (rtv_serial_cursor%isopen) THEN
1232 CLOSE rtv_serial_cursor;
1233 END IF;
1234 WHEN OTHERS THEN
1235 IF (l_debug = 1) THEN
1236 print_debug('Exception raised in main_process at progress: ' || l_progress, 4);
1237 END IF;
1238
1239 ROLLBACK TO inspect_main_sp;
1240 x_return_status := fnd_api.g_ret_sts_unexp_error;
1241
1242 --
1243 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1244 fnd_msg_pub.add_exc_msg(g_pkg_name, 'main_process');
1245 END IF;
1246
1247 -- Get message count and data
1248 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1249
1250 IF (mol_cursor%ISOPEN) THEN
1251 CLOSE mol_cursor;
1252 END IF;
1253
1254 IF (rtv_van_cursor%ISOPEN) THEN
1255 CLOSE rtv_van_cursor;
1256 END IF;
1257
1258 IF (rtv_lot_cursor%isopen) THEN
1259 CLOSE rtv_lot_cursor;
1260 END IF;
1261
1262 IF (rtv_serial_cursor%isopen) THEN
1263 CLOSE rtv_serial_cursor;
1264 END IF;
1265 END main_process;
1266
1267 procedure range_serial_process(
1268 x_return_status OUT NOCOPY VARCHAR2
1269 , x_msg_count OUT NOCOPY NUMBER
1270 , x_msg_data OUT NOCOPY VARCHAR2
1271 , p_inventory_item_id IN NUMBER
1272 , p_organization_id IN NUMBER
1273 , p_lpn_id IN NUMBER
1274 , p_revision IN VARCHAR2
1275 , p_lot_number IN VARCHAR2
1276 , p_inspection_code IN VARCHAR2
1277 , p_quality_code IN VARCHAR2
1278 , p_transaction_type IN VARCHAR2
1279 , p_reason_id IN NUMBER
1280 , p_from_serial_number IN VARCHAR2
1281 , p_to_serial_number IN VARCHAR2
1282 , p_accept_lpn_id IN NUMBER
1283 , p_reject_lpn_id IN NUMBER
1284 , p_transaction_date IN DATE DEFAULT SYSDATE
1285 , p_vendor_lot IN VARCHAR2 DEFAULT NULL
1286 , p_comments IN VARCHAR2 DEFAULT NULL
1287 , p_attribute_category IN VARCHAR2 DEFAULT NULL
1288 , p_attribute1 IN VARCHAR2 DEFAULT NULL
1289 , p_attribute2 IN VARCHAR2 DEFAULT NULL
1290 , p_attribute3 IN VARCHAR2 DEFAULT NULL
1291 , p_attribute4 IN VARCHAR2 DEFAULT NULL
1292 , p_attribute5 IN VARCHAR2 DEFAULT NULL
1293 , p_attribute6 IN VARCHAR2 DEFAULT NULL
1294 , p_attribute7 IN VARCHAR2 DEFAULT NULL
1295 , p_attribute8 IN VARCHAR2 DEFAULT NULL
1296 , p_attribute9 IN VARCHAR2 DEFAULT NULL
1297 , p_attribute10 IN VARCHAR2 DEFAULT NULL
1298 , p_attribute11 IN VARCHAR2 DEFAULT NULL
1299 , p_attribute12 IN VARCHAR2 DEFAULT NULL
1300 , p_attribute13 IN VARCHAR2 DEFAULT NULL
1301 , p_attribute14 IN VARCHAR2 DEFAULT NULL
1302 , p_attribute15 IN VARCHAR2 DEFAULT NULL)
1303 is
1304 l_temp_prefix varchar2(30);
1305
1306 l_from_ser_number number;
1307 l_to_ser_number number;
1308 l_range_numbers number;
1309 l_cur_ser_number number;
1310 l_cur_serial_number varchar2(30);
1311
1312 l_primary_uom_code varchar2(5);
1313
1314 l_return_status varchar2(5);
1315 l_msg_count number;
1316 l_msg_data varchar2(1000);
1317
1318 -- Increased lot size to 80 Char - 3ercy Thomas - B4625329
1319 l_lot_number varchar2(80);
1320
1321 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1322 begin
1323 x_return_status := fnd_api.g_ret_sts_success;
1324
1325 savepoint process_sl_sp;
1326
1327 --
1328 -- Get the primary uom of item. If we are here the item should be serial
1329 -- controlled. Most probably the uom should be 'Ea' but then we can't assume
1330 -- this. This would also not be a right assumption for non English cases
1331 --
1332 select primary_uom_code
1333 into l_primary_uom_code
1334 from mtl_system_items
1335 where organization_id = p_organization_id
1336 and inventory_item_id = p_inventory_item_id
1337 and serial_number_control_code in (2,5,6);
1338
1339 -- get the number part of the from serial
1340 inv_validate.number_from_sequence(p_from_serial_number,
1341 l_temp_prefix,
1342 l_from_ser_number);
1343
1344 -- get the number part of the to serial
1345 inv_validate.number_from_sequence(p_to_serial_number,
1346 l_temp_prefix,
1347 l_to_ser_number);
1348
1349 -- total number of serials
1350 l_range_numbers := l_to_ser_number - l_from_ser_number + 1;
1351
1352 FOR i IN 1..l_range_numbers LOOP
1353 -- Number part of serial number like 123
1354 l_cur_ser_number := l_from_ser_number + i -1;
1355
1356 -- concatenate the serial number to be inserted like XYZ123
1357 -- l_cur_serial_number := l_temp_prefix || l_cur_ser_number;
1358
1359 l_cur_serial_number := Substr(p_from_serial_number, 1,
1360 Length(p_from_serial_number) - Length(l_cur_ser_number))
1361 || l_cur_ser_number;
1362
1363 -- dbms_output.put_line('Curr Sl No:' || l_cur_serial_number || 'ZZZ');
1364
1365 -- We cannot assume that the serial number range belong to the samelot..
1366 select lot_number
1367 into l_lot_number
1368 from mtl_serial_numbers
1369 where inventory_item_id = p_inventory_item_id
1370 and serial_number = l_cur_serial_number;
1371
1372 -- Call processing for each serial number
1373 -- A new parameter qa_collection_id has been added to
1374 -- main process for QA, but QA will not inspect range of
1375 -- sl. nos. Hence passing it a value of NULL
1376 main_process(
1377 x_return_status => l_return_status
1378 , x_msg_count => l_msg_count
1379 , x_msg_data => l_msg_data
1380 , p_inventory_item_id => p_inventory_item_id
1381 , p_organization_id => p_organization_id
1382 , p_lpn_id => p_lpn_id
1383 , p_revision => p_revision
1384 , p_lot_number => l_lot_number
1385 , p_uom_code => l_primary_uom_code
1386 , p_quantity => 1 -- 1 Primary UOM
1387 , p_inspection_code => p_inspection_code
1388 , p_quality_code => p_quality_code
1389 , p_transaction_type => p_transaction_type
1390 , p_reason_id => p_reason_id
1391 , p_serial_number => l_cur_serial_number
1392 , p_accept_lpn_id => p_accept_lpn_id
1393 , p_reject_lpn_id => p_reject_lpn_id
1394 , p_transaction_date => p_transaction_date
1395 , p_qa_collection_id => NULL
1396 , p_vendor_lot => p_vendor_lot
1397 , p_comments => p_comments
1398 , p_attribute_category => p_attribute_category
1399 , p_attribute1 => p_attribute1
1400 , p_attribute2 => p_attribute2
1401 , p_attribute3 => p_attribute3
1402 , p_attribute4 => p_attribute4
1403 , p_attribute5 => p_attribute5
1404 , p_attribute6 => p_attribute6
1405 , p_attribute7 => p_attribute7
1406 , p_attribute8 => p_attribute8
1407 , p_attribute9 => p_attribute9
1408 , p_attribute10 => p_attribute10
1409 , p_attribute11 => p_attribute11
1410 , p_attribute12 => p_attribute12
1411 , p_attribute13 => p_attribute13
1412 , p_attribute14 => p_attribute14
1413 , p_attribute15 => p_attribute15);
1414
1415 IF l_return_status = fnd_api.g_ret_sts_error THEN
1416 RAISE fnd_api.g_exc_error;
1417 END IF ;
1418
1419 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1420 RAISE fnd_api.g_exc_unexpected_error;
1421 END IF;
1422
1423 END LOOP;
1424
1425 exception
1426 when fnd_api.g_exc_error THEN
1427 rollback to process_sl_sp;
1428
1429 x_return_status := fnd_api.g_ret_sts_error;
1430
1431 -- Get message count and data
1432 fnd_msg_pub.count_and_get
1433 ( p_count => x_msg_count
1434 , p_data => x_msg_data
1435 );
1436
1437 when fnd_api.g_exc_unexpected_error THEN
1438 rollback to process_sl_sp;
1439
1440 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1441
1442 -- Get message count and data
1443 fnd_msg_pub.count_and_get
1444 ( p_count => x_msg_count
1445 , p_data => x_msg_data
1446 );
1447
1448 when others THEN
1449 rollback to process_sl_sp;
1450
1451 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1452 --
1453 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
1454 THEN
1455 fnd_msg_pub.add_exc_msg
1456 ( g_pkg_name
1457 , 'range_serial_process'
1458 );
1459 END IF;
1460
1461 -- Get message count and data
1462 fnd_msg_pub.count_and_get
1463 ( p_count => x_msg_count
1464 , p_data => x_msg_data
1465 );
1466 end range_serial_process;
1467
1468 procedure main_process_po(
1469 x_return_status OUT NOCOPY VARCHAR2
1470 , x_msg_count OUT NOCOPY NUMBER
1471 , x_msg_data OUT NOCOPY VARCHAR2
1472 , p_inventory_item_id IN NUMBER
1473 , p_organization_id IN NUMBER
1474 , p_po_header_id IN NUMBER
1475 , p_revision IN VARCHAR2
1476 , p_uom_code IN VARCHAR2
1477 , p_quantity IN NUMBER
1478 , p_inspection_code IN VARCHAR2
1479 , p_quality_code IN VARCHAR2
1480 , p_transaction_type IN VARCHAR2
1481 , p_reason_id IN NUMBER
1482 , p_transaction_date IN DATE DEFAULT SYSDATE
1483 , p_qa_collection_id IN NUMBER DEFAULT NULL
1484 , p_vendor_lot IN VARCHAR2 DEFAULT NULL
1485 , p_comments IN VARCHAR2 DEFAULT NULL
1486 , p_attribute_category IN VARCHAR2 DEFAULT NULL
1487 , p_attribute1 IN VARCHAR2 DEFAULT NULL
1488 , p_attribute2 IN VARCHAR2 DEFAULT NULL
1489 , p_attribute3 IN VARCHAR2 DEFAULT NULL
1490 , p_attribute4 IN VARCHAR2 DEFAULT NULL
1491 , p_attribute5 IN VARCHAR2 DEFAULT NULL
1492 , p_attribute6 IN VARCHAR2 DEFAULT NULL
1493 , p_attribute7 IN VARCHAR2 DEFAULT NULL
1494 , p_attribute8 IN VARCHAR2 DEFAULT NULL
1495 , p_attribute9 IN VARCHAR2 DEFAULT NULL
1496 , p_attribute10 IN VARCHAR2 DEFAULT NULL
1497 , p_attribute11 IN VARCHAR2 DEFAULT NULL
1498 , p_attribute12 IN VARCHAR2 DEFAULT NULL
1499 , p_attribute13 IN VARCHAR2 DEFAULT NULL
1500 , p_attribute14 IN VARCHAR2 DEFAULT NULL
1501 , p_attribute15 IN VARCHAR2 DEFAULT NULL
1502 , p_secondary_qty IN NUMBER DEFAULT NULL) --OPM Convergence
1503 is
1504 l_inventory_item_id NUMBER := p_inventory_item_id;
1505 l_organization_id NUMBER := p_organization_id;
1506 l_revision VARCHAR2(10) := p_revision;
1507 l_revision_control NUMBER; -- Added for bug 3134272
1508 l_uom_code VARCHAR2(5) := p_uom_code;
1509 l_uom VARCHAR2(30);
1510 l_quantity NUMBER := p_quantity;
1511 l_po_header_id NUMBER := p_po_header_id;
1512
1513 l_inspection_code VARCHAR2(25) := p_inspection_code;
1514 l_quality_code VARCHAR2(25) := p_quality_code;
1515 l_transaction_date DATE := p_transaction_date;
1516 l_comments VARCHAR2(240) := p_comments;
1517 l_attribute_category VARCHAR2(30) := p_attribute_category;
1518 l_attribute1 VARCHAR2(150) := p_attribute1;
1519 l_attribute2 VARCHAR2(150) := p_attribute2;
1520 l_attribute3 VARCHAR2(150) := p_attribute3;
1521 l_attribute4 VARCHAR2(150) := p_attribute4;
1522 l_attribute5 VARCHAR2(150) := p_attribute5;
1523 l_attribute6 VARCHAR2(150) := p_attribute6;
1524 l_attribute7 VARCHAR2(150) := p_attribute7;
1525 l_attribute8 VARCHAR2(150) := p_attribute8;
1526 l_attribute9 VARCHAR2(150) := p_attribute9;
1527 l_attribute10 VARCHAR2(150) := p_attribute10;
1528 l_attribute11 VARCHAR2(150) := p_attribute11;
1529 l_attribute12 VARCHAR2(150) := p_attribute12;
1530 l_attribute13 VARCHAR2(150) := p_attribute13;
1531 l_attribute14 VARCHAR2(150) := p_attribute14;
1532 l_attribute15 VARCHAR2(150) := p_attribute15;
1533 l_transaction_type VARCHAR2(30) := p_transaction_type;
1534 l_vendor_lot VARCHAR2(30) := p_vendor_lot;
1535 l_reason_id NUMBER := p_reason_id;
1536
1537 l_qa_collection_id NUMBER := p_qa_collection_id;
1538
1539 l_primary_qty NUMBER;
1540 l_primary_uom_code varchar2(5);
1541
1542 l_rcv_transaction_id number;
1543 l_rtv_qty number;
1544 l_rtv_uom varchar2(25); /* Each */
1545 l_rtv_uom_code varchar2(5); /* Ea */
1546 l_receipt_source_code varchar2(25);
1547 l_tolerable_qty number;
1548
1549 l_remaining_qty number;
1550 l_transacted_qty number;
1551
1552 l_return_status varchar2(5);
1553 l_msg_count number;
1554 l_msg_data varchar2(1000);
1555
1556 l_secondary_qty NUMBER := p_secondary_qty; --OPM COnvergence
1557 l_remaining_sec_qty NUMBER; --OPM Convergence
1558 l_rtv_sec_uom VARCHAR2(25);--OPM Convergence
1559 l_sec_uom_code VARCHAR2(3);--OPM Convergence
1560 l_sec_uom VARCHAR2(25);--OPM Convergence
1561 l_rtv_sec_qty NUMBER;--OPM COnvergence
1562 L_SEC_REMAINING_QTY NUMBER;--OPM Convergence
1563 /* cursor rtv_cursor(
1564 k_po_header_id number
1565 , k_organization_id number
1566 , k_inventory_item_id number
1567 , k_revision varchar2)
1568 is
1569 select
1570 rcv_transaction_id
1571 , receipt_source_code
1572 , unit_of_measure
1573 from rcv_transactions_v
1574 where po_header_id = k_po_header_id
1575 and to_organization_id = k_organization_id
1576 and item_id = k_inventory_item_id
1577 and (item_revision = k_revision OR
1578 item_revision is null and p_revision is null)
1579 and inspection_status_code = 'NOT INSPECTED'
1580 and routing_id = g_inspection_routing;
1581 */
1582 /* Bug 1542687: For performance reasons, the cursor is based on base tables below.*/
1583
1584 cursor rtv_cursor(
1585 k_po_header_id number
1586 , k_organization_id number
1587 , k_inventory_item_id number
1588 , k_revision varchar2
1589 , k_revision_control number -- Added for bug 3134272
1590 ) is
1591 select
1592 rs.rcv_transaction_id
1593 , rsh.receipt_source_code
1594 , rs.unit_of_measure
1595 , rs.secondary_unit_of_measure --OPM Convergence
1596 from rcv_supply rs, rcv_transactions rt, rcv_shipment_headers rsh
1597 where rs.po_header_id = k_po_header_id
1598 and rs.to_organization_id = k_organization_id
1599 and rs.item_id = k_inventory_item_id
1600 and (k_revision_control = 2
1601 and Nvl(rs.item_revision,-1) = Nvl(k_revision,-1)
1602 OR k_revision_control = 1)
1603 -- Changed the above for bug 3134272
1604 and rs.rcv_transaction_id = rt.transaction_id
1605 and rsh.shipment_header_id = rs.shipment_header_id
1606 and rt.inspection_status_code = 'NOT INSPECTED'
1607 and rs.supply_type_code = 'RECEIVING'
1608 and rt.transaction_type <> 'UNORDERED'
1609 and rt.routing_header_id = g_inspection_routing;
1610
1611 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1612 begin
1613 x_return_status := fnd_api.g_ret_sts_success;
1614
1615 -- dbms_output.put_line('main_process_po: Just entering main_process_po');
1616
1617 --First check if the transaction date satisfies the validation.
1618 --If the transaction date is invalid then error out the transaction
1619 IF inv_rcv_common_apis.g_po_startup_value.sob_id IS NULL THEN
1620 --BUG 3444196: Used the HR view instead for performance reasons
1621 SELECT TO_NUMBER(hoi.org_information1)
1622 INTO inv_rcv_common_apis.g_po_startup_value.sob_id
1623 FROM hr_organization_information hoi
1624 WHERE hoi.organization_id = p_organization_id
1625 AND (hoi.org_information_context || '') = 'Accounting Information' ;
1626 END IF;
1627
1628 inv_rcv_common_apis.validate_trx_date(
1629 p_trx_date => SYSDATE
1630 , p_organization_id => p_organization_id
1631 , p_sob_id => inv_rcv_common_apis.g_po_startup_value.sob_id
1632 , x_return_status => x_return_status
1633 , x_error_code => x_msg_data
1634 );
1635
1636 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1637 RETURN;
1638 END IF;
1639
1640 savepoint inspect_main_po_sp;
1641
1642 -- Quantity entered on form
1643 l_remaining_qty := l_quantity;
1644 l_remaining_sec_qty := l_secondary_qty; --OPM Convergence
1645
1646 -- Quantity successfully transacted
1647 l_transacted_qty := 0;
1648
1649 -- One time fetch of item's primary uom code
1650 -- Fetching revision control for bug 3134272
1651 select primary_uom_code,revision_qty_control_code, secondary_uom_code --OPM Convergence
1652 into l_primary_uom_code,l_revision_control, l_sec_uom_code --OPM Convergence
1653 from mtl_system_items
1654 where organization_id = l_organization_id
1655 and inventory_item_id = l_inventory_item_id;
1656
1657 -- dbms_output.put_line('main_process_po: Fetched item primary uom code');
1658
1659 -- Purchasing/receiving uses unit of measure (Each)
1660 -- rather than uom code(Ea) and hence the following..
1661 -- This will be used later while inserting into interface table
1662
1663 SELECT unit_of_measure
1664 INTO l_uom
1665 FROM mtl_units_of_measure
1666 WHERE uom_code = l_uom_code;
1667 /* OPM Convergence */
1668 IF l_sec_uom_code IS NOT NULL THEN
1669
1670 SELECT unit_of_measure
1671 INTO l_sec_uom
1672 FROM mtl_units_of_measure
1673 WHERE uom_code = l_sec_uom_code;
1674
1675 END IF;
1676 -- dbms_output.put_line('main_process_po: Convert inspection uom code into uom');
1677
1678 -- Open RCV Transactions V cursor
1679 open rtv_cursor(
1680 l_po_header_id
1681 , l_organization_id
1682 , l_inventory_item_id
1683 , l_revision
1684 , l_revision_control -- Added for bug 3134272
1685 );
1686
1687 -- dbms_output.put_line('main_process_po: Opened RTV Cursor');
1688
1689 while(l_remaining_qty > 0)
1690 loop
1691 fetch rtv_cursor into
1692 l_rcv_transaction_id
1693 , l_receipt_source_code
1694 , l_rtv_uom
1695 , l_rtv_sec_uom; --OPM Convergence
1696
1697 if rtv_cursor%notfound then
1698 exit;
1699 end if;
1700
1701 -- Get quantity that can be still inspected
1702
1703 RCV_QUANTITIES_S.GET_AVAILABLE_QUANTITY (
1704 'INSPECT'
1705 , l_rcv_transaction_id
1706 , l_receipt_source_code
1707 , null
1708 , l_rcv_transaction_id
1709 , null
1710 , l_rtv_qty
1711 , l_tolerable_qty
1712 , l_rtv_uom );
1713
1714 if (l_rtv_qty > 0) then
1715
1716 -- dbms_output.put_line('main_process_po: convert rtv uom into uom code');
1717
1718 SELECT uom_code
1719 INTO l_rtv_uom_code
1720 FROM mtl_units_of_measure
1721 WHERE unit_of_measure = l_rtv_uom;
1722
1723 -- If inspection uom is not same as receipt uom, convert
1724
1725 if (l_uom_code <> l_rtv_uom_code) then
1726 l_rtv_qty := inv_convert.inv_um_convert(
1727 l_inventory_item_id
1728 , NULL
1729 , l_rtv_qty
1730 , l_rtv_uom_code
1731 , l_uom_code
1732 , NULL
1733 , NULL);
1734 end if;
1735
1736 if l_rtv_qty >= l_remaining_qty then
1737 l_rtv_qty := l_remaining_qty;
1738 l_rtv_sec_qty := l_sec_remaining_qty; --OPM Convergence
1739 l_remaining_qty := 0;
1740 l_sec_remaining_qty :=0; --OPM Convergence
1741 else
1742 l_remaining_qty := l_remaining_qty - l_rtv_qty;
1743 l_sec_remaining_qty := l_sec_remaining_qty - l_rtv_sec_qty; --OPM Convergence
1744 end if;
1745
1746 -- If required convert into primary unit of measure
1747 if (l_uom_code <> l_primary_uom_code) then
1748
1749 -- dbms_output.put_line('main_process_po: convert inspect uom into primary uom');
1750
1751 l_primary_qty := inv_convert.inv_um_convert(
1752 l_inventory_item_id
1753 , NULL
1754 , l_rtv_qty
1755 , l_uom_code
1756 , l_primary_uom_code
1757 , NULL
1758 , NULL);
1759 else
1760 l_primary_qty := l_rtv_qty;
1761 end if;
1762
1763 -- dbms_output.put_line('main_process_po: Calling insert_inspect_rec_rti');
1764
1765 -- Insert into rti, passing l_rtv_qty, inspection information
1766 insert_inspect_rec_rti (
1767 x_return_status => l_return_status
1768 , x_msg_count => l_msg_count
1769 , x_msg_data => l_msg_data
1770 , p_rcv_transaction_id => l_rcv_transaction_id
1771 , p_quantity => l_rtv_qty
1772 , p_uom => l_uom
1773 , p_inspection_code => l_inspection_code
1774 , p_quality_code => l_quality_code
1775 , p_transaction_date => l_transaction_date
1776 , p_transaction_type => l_transaction_type
1777 , p_vendor_lot => l_vendor_lot
1778 , p_reason_id => l_reason_id
1779 , p_primary_qty => l_primary_qty
1780 , p_organization_id => l_organization_id
1781 , p_comments => l_comments
1782 , p_attribute_category => l_attribute_category
1783 , p_attribute1 => l_attribute1
1784 , p_attribute2 => l_attribute2
1785 , p_attribute3 => l_attribute3
1786 , p_attribute4 => l_attribute4
1787 , p_attribute5 => l_attribute5
1788 , p_attribute6 => l_attribute6
1789 , p_attribute7 => l_attribute7
1790 , p_attribute8 => l_attribute8
1791 , p_attribute9 => l_attribute9
1792 , p_attribute10 => l_attribute10
1793 , p_attribute11 => l_attribute11
1794 , p_attribute12 => l_attribute12
1795 , p_attribute13 => l_attribute13
1796 , p_attribute14 => l_attribute14
1797 , p_attribute15 => l_attribute15
1798 , p_qa_collection_id => l_qa_collection_id
1799 , p_sec_uom => l_sec_uom --OPM Convergence
1800 , p_secondary_qty => l_rtv_sec_qty); --OPM Convergence
1801
1802 IF l_return_status = fnd_api.g_ret_sts_error THEN
1803 RAISE fnd_api.g_exc_error;
1804 END IF ;
1805
1806 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1807 RAISE fnd_api.g_exc_unexpected_error;
1808 END IF;
1809
1810 -- dbms_output.put_line('main_process_po: Successful insert_inspect_rec_rti');
1811
1812 -- Count successfully transacted qty
1813 l_transacted_qty := l_transacted_qty + l_rtv_qty;
1814 end if;
1815 end loop;
1816
1817 IF l_remaining_qty > 0 THEN
1818 FND_MESSAGE.set_name('INV','INV_QTY_LESS_OR_EQUAL');
1819 FND_MSG_PUB.Add;
1820 RAISE FND_API.G_EXC_ERROR;
1821 END IF;
1822
1823 close rtv_cursor;
1824
1825 exception
1826 when fnd_api.g_exc_error THEN
1827 rollback to inspect_main_po_sp;
1828
1829 x_return_status := fnd_api.g_ret_sts_error;
1830
1831 -- Get message count and data
1832 fnd_msg_pub.count_and_get
1833 ( p_count => x_msg_count
1834 , p_data => x_msg_data
1835 );
1836
1837 IF (rtv_cursor%isopen) THEN
1838 CLOSE rtv_cursor;
1839 END IF;
1840
1841 when fnd_api.g_exc_unexpected_error THEN
1842 rollback to inspect_main_po_sp;
1843
1844 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1845
1846 -- Get message count and data
1847 fnd_msg_pub.count_and_get
1848 ( p_count => x_msg_count
1849 , p_data => x_msg_data
1850 );
1851
1852 IF (rtv_cursor%isopen) THEN
1853 CLOSE rtv_cursor;
1854 END IF;
1855
1856 when others THEN
1857 rollback to inspect_main_po_sp;
1858
1859 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1860 --
1861 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
1862 THEN
1863 fnd_msg_pub.add_exc_msg
1864 ( g_pkg_name
1865 , 'main_process_po'
1866 );
1867 END IF;
1868
1869 -- Get message count and data
1870 fnd_msg_pub.count_and_get
1871 ( p_count => x_msg_count
1872 , p_data => x_msg_data
1873 );
1874
1875 IF (rtv_cursor%isopen) THEN
1876 CLOSE rtv_cursor;
1877 END IF;
1878
1879 end main_process_po;
1880
1881 procedure main_process_intransit(
1882 x_return_status OUT NOCOPY VARCHAR2
1883 , x_msg_count OUT NOCOPY NUMBER
1884 , x_msg_data OUT NOCOPY VARCHAR2
1885 , p_inventory_item_id IN NUMBER
1886 , p_organization_id IN NUMBER
1887 , p_shipment_header_id IN NUMBER
1888 , p_revision IN VARCHAR2
1889 , p_uom_code IN VARCHAR2
1890 , p_quantity IN NUMBER
1891 , p_inspection_code IN VARCHAR2
1892 , p_quality_code IN VARCHAR2
1893 , p_transaction_type IN VARCHAR2
1894 , p_reason_id IN NUMBER
1895 , p_transaction_date IN DATE DEFAULT SYSDATE
1896 , p_qa_collection_id IN NUMBER DEFAULT NULL
1897 , p_vendor_lot IN VARCHAR2 DEFAULT NULL
1898 , p_comments IN VARCHAR2 DEFAULT NULL
1899 , p_attribute_category IN VARCHAR2 DEFAULT NULL
1900 , p_attribute1 IN VARCHAR2 DEFAULT NULL
1901 , p_attribute2 IN VARCHAR2 DEFAULT NULL
1902 , p_attribute3 IN VARCHAR2 DEFAULT NULL
1903 , p_attribute4 IN VARCHAR2 DEFAULT NULL
1904 , p_attribute5 IN VARCHAR2 DEFAULT NULL
1905 , p_attribute6 IN VARCHAR2 DEFAULT NULL
1906 , p_attribute7 IN VARCHAR2 DEFAULT NULL
1907 , p_attribute8 IN VARCHAR2 DEFAULT NULL
1908 , p_attribute9 IN VARCHAR2 DEFAULT NULL
1909 , p_attribute10 IN VARCHAR2 DEFAULT NULL
1910 , p_attribute11 IN VARCHAR2 DEFAULT NULL
1911 , p_attribute12 IN VARCHAR2 DEFAULT NULL
1912 , p_attribute13 IN VARCHAR2 DEFAULT NULL
1913 , p_attribute14 IN VARCHAR2 DEFAULT NULL
1914 , p_attribute15 IN VARCHAR2 DEFAULT NULL
1915 , p_secondary_qty IN NUMBER DEFAULT NULL) --OPM Convergence
1916 is
1917 l_inventory_item_id NUMBER := p_inventory_item_id;
1918 l_organization_id NUMBER := p_organization_id;
1919 l_revision VARCHAR2(10) := p_revision;
1920 l_revision_control NUMBER; -- Added for bug 3134272
1921 l_uom_code VARCHAR2(5) := p_uom_code;
1922 l_uom VARCHAR2(30);
1923 l_quantity NUMBER := p_quantity;
1924 l_shipment_header_id NUMBER := p_shipment_header_id;
1925
1926 l_inspection_code VARCHAR2(25) := p_inspection_code;
1927 l_quality_code VARCHAR2(25) := p_quality_code;
1928 l_transaction_date DATE := p_transaction_date;
1929 l_comments VARCHAR2(240) := p_comments;
1930 l_attribute_category VARCHAR2(30) := p_attribute_category;
1931 l_attribute1 VARCHAR2(150) := p_attribute1;
1932 l_attribute2 VARCHAR2(150) := p_attribute2;
1933 l_attribute3 VARCHAR2(150) := p_attribute3;
1934 l_attribute4 VARCHAR2(150) := p_attribute4;
1935 l_attribute5 VARCHAR2(150) := p_attribute5;
1936 l_attribute6 VARCHAR2(150) := p_attribute6;
1937 l_attribute7 VARCHAR2(150) := p_attribute7;
1938 l_attribute8 VARCHAR2(150) := p_attribute8;
1939 l_attribute9 VARCHAR2(150) := p_attribute9;
1940 l_attribute10 VARCHAR2(150) := p_attribute10;
1941 l_attribute11 VARCHAR2(150) := p_attribute11;
1942 l_attribute12 VARCHAR2(150) := p_attribute12;
1943 l_attribute13 VARCHAR2(150) := p_attribute13;
1944 l_attribute14 VARCHAR2(150) := p_attribute14;
1945 l_attribute15 VARCHAR2(150) := p_attribute15;
1946 l_transaction_type VARCHAR2(30) := p_transaction_type;
1947 l_vendor_lot VARCHAR2(30) := p_vendor_lot;
1948 l_reason_id NUMBER := p_reason_id;
1949
1950 l_qa_collection_id NUMBER := p_qa_collection_id;
1951
1952 l_primary_qty NUMBER;
1953 l_primary_uom_code varchar2(5);
1954
1955 l_rcv_transaction_id number;
1956 l_rtv_qty number;
1957 l_rtv_uom varchar2(25); /* Each */
1958 l_rtv_uom_code varchar2(5); /* Ea */
1959 l_receipt_source_code varchar2(25);
1960 l_tolerable_qty number;
1961
1962 l_remaining_qty number;
1963 l_transacted_qty number;
1964
1965 l_return_status varchar2(5);
1966 l_msg_count number;
1967 l_msg_data varchar2(1000);
1968
1969 l_secondary_qty NUMBER := p_secondary_qty; --OPM COnvergence
1970 l_remaining_sec_qty NUMBER; --OPM Convergence
1971 l_rtv_sec_uom VARCHAR2(25);--OPM Convergence
1972 l_sec_uom_code VARCHAR2(3);--OPM Convergence
1973 l_sec_uom VARCHAR2(25);--OPM Convergence
1974 l_rtv_sec_qty NUMBER;--OPM COnvergence
1975 l_sec_remaining_qty NUMBER; --OPM Convergence
1976
1977 /* cursor rtv_cursor(
1978 k_shipment_header_id number
1979 , k_organization_id number
1980 , k_inventory_item_id number
1981 , k_revision varchar2)
1982 is
1983 select
1984 rcv_transaction_id
1985 , receipt_source_code
1986 , unit_of_measure
1987 from rcv_transactions_v
1988 where receipt_source_code <> 'VENDOR'
1989 and shipment_header_id = k_shipment_header_id
1990 and to_organization_id = k_organization_id
1991 and item_id = k_inventory_item_id
1992 and (item_revision = k_revision OR
1993 item_revision is null and p_revision is null)
1994 and inspection_status_code = 'NOT INSPECTED'
1995 and routing_id = g_inspection_routing;
1996 */
1997 /* Bug 1542687: For performance reasons, the cursor is based on base tables below.*/
1998
1999 cursor rtv_cursor(
2000 k_shipment_header_id number
2001 , k_organization_id number
2002 , k_inventory_item_id number
2003 , k_revision varchar2
2004 , k_revision_control number -- Added for bug 3134272
2005 ) is
2006 select
2007 rs.rcv_transaction_id
2008 , rsh.receipt_source_code
2009 , rs.unit_of_measure
2010 , rs.secondary_unit_of_measure --OPM Convergence
2011 from rcv_supply rs, rcv_transactions rt, rcv_shipment_headers rsh
2012 where rsh.receipt_source_code <> 'VENDOR'
2013 and rs.shipment_header_id = k_shipment_header_id
2014 and rs.to_organization_id = k_organization_id
2015 and rs.item_id = k_inventory_item_id
2016 and (k_revision_control = 2
2017 and Nvl(rs.item_revision,-1) = Nvl(k_revision,-1)
2018 OR k_revision_control = 1)
2019 -- Changed the above for bug 3134272
2020 and rs.rcv_transaction_id = rt.transaction_id
2021 and rsh.shipment_header_id = rs.shipment_header_id
2022 and rt.inspection_status_code = 'NOT INSPECTED'
2023 and rs.supply_type_code = 'RECEIVING'
2024 and rt.transaction_type <> 'UNORDERED'
2025 and rt.routing_header_id = g_inspection_routing;
2026 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2027 begin
2028 x_return_status := fnd_api.g_ret_sts_success;
2029
2030 -- dbms_output.put_line('main_process_intransit: Just entering main_process_intransit');
2031
2032 --First check if the transaction date satisfies the validation.
2033 --If the transaction date is invalid then error out the transaction
2034 IF inv_rcv_common_apis.g_po_startup_value.sob_id IS NULL THEN
2035 --BUG 3444196: Used the HR view instead for performance reasons
2036 SELECT TO_NUMBER(hoi.org_information1)
2037 INTO inv_rcv_common_apis.g_po_startup_value.sob_id
2038 FROM hr_organization_information hoi
2039 WHERE hoi.organization_id = p_organization_id
2040 AND (hoi.org_information_context || '') = 'Accounting Information' ;
2041 END IF;
2042
2043 inv_rcv_common_apis.validate_trx_date(
2044 p_trx_date => SYSDATE
2045 , p_organization_id => p_organization_id
2046 , p_sob_id => inv_rcv_common_apis.g_po_startup_value.sob_id
2047 , x_return_status => x_return_status
2048 , x_error_code => x_msg_data
2049 );
2050
2051 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2052 RETURN;
2053 END IF;
2054
2055 savepoint inspect_main_intransit_sp;
2056
2057 -- Quantity entered on form
2058 l_remaining_qty := l_quantity;
2059
2060 -- Quantity successfully transacted
2061 l_transacted_qty := 0;
2062
2063 -- One time fetch of item's primary uom code
2064 -- Fetching revision control for bug 3134272
2065 select primary_uom_code,revision_qty_control_code, secondary_uom_code --OPM Convergence
2066 into l_primary_uom_code,l_revision_control, l_rtv_sec_uom --OPM Convergence
2067 from mtl_system_items
2068 where organization_id = l_organization_id
2069 and inventory_item_id = l_inventory_item_id;
2070
2071 -- dbms_output.put_line('main_process_intransit: Fetched item primary uom code');
2072
2073 -- Purchasing/receiving uses unit of measure (Each)
2074 -- rather than uom code(Ea) and hence the following..
2075 -- This will be used later while inserting into interface table
2076
2077 SELECT unit_of_measure
2078 INTO l_uom
2079 FROM mtl_units_of_measure
2080 WHERE uom_code = l_uom_code;
2081
2082 /* OPM Convergence */
2083 IF l_sec_uom_code IS NOT NULL THEN
2084
2085 SELECT unit_of_measure
2086 INTO l_sec_uom
2087 FROM mtl_units_of_measure
2088 WHERE uom_code = l_sec_uom_code;
2089
2090 END IF;
2091
2092 -- dbms_output.put_line('main_process_intransit: Convert inspection uom code into uom');
2093
2094 -- Open RCV Transactions V cursor
2095 open rtv_cursor(
2096 l_shipment_header_id
2097 , l_organization_id
2098 , l_inventory_item_id
2099 , l_revision
2100 , l_revision_control -- Added for bug 3134272
2101 );
2102
2103 -- dbms_output.put_line('main_process_intransit: Opened RTV Cursor');
2104
2105 while(l_remaining_qty > 0)
2106 loop
2107 fetch rtv_cursor into
2108 l_rcv_transaction_id
2109 , l_receipt_source_code
2110 , l_rtv_uom
2111 , l_rtv_sec_qty; --OPM Convergence
2112
2113 if rtv_cursor%notfound then
2114 exit;
2115 end if;
2116
2117 -- Get quantity that can be still inspected
2118
2119 RCV_QUANTITIES_S.GET_AVAILABLE_QUANTITY (
2120 'INSPECT'
2121 , l_rcv_transaction_id
2122 , l_receipt_source_code
2123 , null
2124 , l_rcv_transaction_id
2125 , null
2126 , l_rtv_qty
2127 , l_tolerable_qty
2128 , l_rtv_uom );
2129
2130 if (l_rtv_qty > 0) then
2131
2132 -- dbms_output.put_line('main_process_intransit: convert rtv uom into uom code');
2133
2134 SELECT uom_code
2135 INTO l_rtv_uom_code
2136 FROM mtl_units_of_measure
2137 WHERE unit_of_measure = l_rtv_uom;
2138
2139 -- If inspection uom is not same as receipt uom, convert
2140
2141 if (l_uom_code <> l_rtv_uom_code) then
2142 l_rtv_qty := inv_convert.inv_um_convert(
2143 l_inventory_item_id
2144 , NULL
2145 , l_rtv_qty
2146 , l_rtv_uom_code
2147 , l_uom_code
2148 , NULL
2149 , NULL);
2150 end if;
2151
2152 if l_rtv_qty >= l_remaining_qty then
2153 l_rtv_qty := l_remaining_qty;
2154 l_rtv_sec_qty := l_sec_remaining_qty;
2155 l_remaining_qty := 0;
2156 l_sec_remaining_qty := 0;
2157 else
2158 l_remaining_qty := l_remaining_qty - l_rtv_qty;
2159 l_sec_remaining_qty := l_sec_remaining_qty - l_rtv_sec_qty;
2160 end if;
2161
2162 -- If required convert into primary unit of measure
2163 if (l_uom_code <> l_primary_uom_code) then
2164
2165 -- dbms_output.put_line('main_process_intransit: convet inspect uom into primary uom');
2166
2167 l_primary_qty := inv_convert.inv_um_convert(
2168 l_inventory_item_id
2169 , NULL
2170 , l_rtv_qty
2171 , l_uom_code
2172 , l_primary_uom_code
2173 , NULL
2174 , NULL);
2175 else
2176 l_primary_qty := l_rtv_qty;
2177 end if;
2178
2179 -- dbms_output.put_line('main_process_intransit: Calling insert_inspect_rec_rti');
2180
2181 -- Insert into rti, passing l_rtv_qty, inspection information
2182 insert_inspect_rec_rti (
2183 x_return_status => l_return_status
2184 , x_msg_count => l_msg_count
2185 , x_msg_data => l_msg_data
2186 , p_rcv_transaction_id => l_rcv_transaction_id
2187 , p_quantity => l_rtv_qty
2188 , p_uom => l_uom
2189 , p_inspection_code => l_inspection_code
2190 , p_quality_code => l_quality_code
2191 , p_transaction_date => l_transaction_date
2192 , p_transaction_type => l_transaction_type
2193 , p_vendor_lot => l_vendor_lot
2194 , p_reason_id => l_reason_id
2195 , p_primary_qty => l_primary_qty
2196 , p_organization_id => l_organization_id
2197 , p_comments => l_comments
2198 , p_attribute_category => l_attribute_category
2199 , p_attribute1 => l_attribute1
2200 , p_attribute2 => l_attribute2
2201 , p_attribute3 => l_attribute3
2202 , p_attribute4 => l_attribute4
2203 , p_attribute5 => l_attribute5
2204 , p_attribute6 => l_attribute6
2205 , p_attribute7 => l_attribute7
2206 , p_attribute8 => l_attribute8
2207 , p_attribute9 => l_attribute9
2208 , p_attribute10 => l_attribute10
2209 , p_attribute11 => l_attribute11
2210 , p_attribute12 => l_attribute12
2211 , p_attribute13 => l_attribute13
2212 , p_attribute14 => l_attribute14
2213 , p_attribute15 => l_attribute15
2214 , p_qa_collection_id => l_qa_collection_id
2215 , p_sec_uom => l_sec_uom --OPM Convergence
2216 , p_secondary_qty => l_rtv_sec_qty); --OPM Convergence
2217
2218 IF l_return_status = fnd_api.g_ret_sts_error THEN
2219 RAISE fnd_api.g_exc_error;
2220 END IF ;
2221
2222 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
2223 RAISE fnd_api.g_exc_unexpected_error;
2224 END IF;
2225
2226 -- dbms_output.put_line('main_process_intransit: Successful insert_inspect_rec_rti');
2227
2228 -- Count successfully transacted qty
2229 l_transacted_qty := l_transacted_qty + l_rtv_qty;
2230 end if;
2231 end loop;
2232
2233 IF l_remaining_qty > 0 THEN
2234 FND_MESSAGE.set_name('INV','INV_QTY_LESS_OR_EQUAL');
2235 FND_MSG_PUB.Add;
2236 RAISE FND_API.G_EXC_ERROR;
2237 END IF;
2238
2239 close rtv_cursor;
2240
2241 exception
2242 when fnd_api.g_exc_error THEN
2243 rollback to inspect_main_intransit_sp;
2244
2245 x_return_status := fnd_api.g_ret_sts_error;
2246
2247 -- Get message count and data
2248 fnd_msg_pub.count_and_get
2249 ( p_count => x_msg_count
2250 , p_data => x_msg_data
2251 );
2252
2253 IF (rtv_cursor%isopen) THEN
2254 CLOSE rtv_cursor;
2255 END IF;
2256
2257 when fnd_api.g_exc_unexpected_error THEN
2258 rollback to inspect_main_intransit_sp;
2259
2260 x_return_status := fnd_api.g_ret_sts_unexp_error ;
2261
2262 -- Get message count and data
2263 fnd_msg_pub.count_and_get
2264 ( p_count => x_msg_count
2265 , p_data => x_msg_data
2266 );
2267
2268 IF (rtv_cursor%isopen) THEN
2269 CLOSE rtv_cursor;
2270 END IF;
2271
2272 when others THEN
2273 rollback to inspect_main_intransit_sp;
2274
2275 x_return_status := fnd_api.g_ret_sts_unexp_error ;
2276 --
2277 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
2278 THEN
2279 fnd_msg_pub.add_exc_msg
2280 ( g_pkg_name
2281 , 'main_process_intransit'
2282 );
2283 END IF;
2284
2285 -- Get message count and data
2286 fnd_msg_pub.count_and_get
2287 ( p_count => x_msg_count
2288 , p_data => x_msg_data
2289 );
2290
2291 IF (rtv_cursor%isopen) THEN
2292 CLOSE rtv_cursor;
2293 END IF;
2294
2295 end main_process_intransit;
2296
2297 procedure main_process_rma(
2298 x_return_status OUT NOCOPY VARCHAR2
2299 , x_msg_count OUT NOCOPY NUMBER
2300 , x_msg_data OUT NOCOPY VARCHAR2
2301 , p_inventory_item_id IN NUMBER
2302 , p_organization_id IN NUMBER
2303 , p_oe_order_header_id IN NUMBER
2304 , p_revision IN VARCHAR2
2305 , p_uom_code IN VARCHAR2
2306 , p_quantity IN NUMBER
2307 , p_inspection_code IN VARCHAR2
2308 , p_quality_code IN VARCHAR2
2309 , p_transaction_type IN VARCHAR2
2310 , p_reason_id IN NUMBER
2311 , p_transaction_date IN DATE DEFAULT SYSDATE
2312 , p_qa_collection_id IN NUMBER DEFAULT NULL
2313 , p_vendor_lot IN VARCHAR2 DEFAULT NULL
2314 , p_comments IN VARCHAR2 DEFAULT NULL
2315 , p_attribute_category IN VARCHAR2 DEFAULT NULL
2316 , p_attribute1 IN VARCHAR2 DEFAULT NULL
2317 , p_attribute2 IN VARCHAR2 DEFAULT NULL
2318 , p_attribute3 IN VARCHAR2 DEFAULT NULL
2319 , p_attribute4 IN VARCHAR2 DEFAULT NULL
2320 , p_attribute5 IN VARCHAR2 DEFAULT NULL
2321 , p_attribute6 IN VARCHAR2 DEFAULT NULL
2322 , p_attribute7 IN VARCHAR2 DEFAULT NULL
2323 , p_attribute8 IN VARCHAR2 DEFAULT NULL
2324 , p_attribute9 IN VARCHAR2 DEFAULT NULL
2325 , p_attribute10 IN VARCHAR2 DEFAULT NULL
2326 , p_attribute11 IN VARCHAR2 DEFAULT NULL
2327 , p_attribute12 IN VARCHAR2 DEFAULT NULL
2328 , p_attribute13 IN VARCHAR2 DEFAULT NULL
2329 , p_attribute14 IN VARCHAR2 DEFAULT NULL
2330 , p_attribute15 IN VARCHAR2 DEFAULT NULL
2331 , p_secondary_qty IN NUMBER DEFAULT NULL) --OPM Convergence)
2332 is
2333 l_inventory_item_id NUMBER := p_inventory_item_id;
2334 l_organization_id NUMBER := p_organization_id;
2335 l_revision VARCHAR2(10) := p_revision;
2336 l_revision_control NUMBER; -- Added for bug 3134272
2337 l_uom_code VARCHAR2(5) := p_uom_code;
2338 l_uom VARCHAR2(30);
2339 l_quantity NUMBER := p_quantity;
2340 l_oe_order_header_id NUMBER := p_oe_order_header_id;
2341
2342 l_inspection_code VARCHAR2(25) := p_inspection_code;
2343 l_quality_code VARCHAR2(25) := p_quality_code;
2344 l_transaction_date DATE := p_transaction_date;
2345 l_comments VARCHAR2(240) := p_comments;
2346 l_attribute_category VARCHAR2(30) := p_attribute_category;
2347 l_attribute1 VARCHAR2(150) := p_attribute1;
2348 l_attribute2 VARCHAR2(150) := p_attribute2;
2349 l_attribute3 VARCHAR2(150) := p_attribute3;
2350 l_attribute4 VARCHAR2(150) := p_attribute4;
2351 l_attribute5 VARCHAR2(150) := p_attribute5;
2352 l_attribute6 VARCHAR2(150) := p_attribute6;
2353 l_attribute7 VARCHAR2(150) := p_attribute7;
2354 l_attribute8 VARCHAR2(150) := p_attribute8;
2355 l_attribute9 VARCHAR2(150) := p_attribute9;
2356 l_attribute10 VARCHAR2(150) := p_attribute10;
2357 l_attribute11 VARCHAR2(150) := p_attribute11;
2358 l_attribute12 VARCHAR2(150) := p_attribute12;
2359 l_attribute13 VARCHAR2(150) := p_attribute13;
2360 l_attribute14 VARCHAR2(150) := p_attribute14;
2361 l_attribute15 VARCHAR2(150) := p_attribute15;
2362 l_transaction_type VARCHAR2(30) := p_transaction_type;
2363 l_vendor_lot VARCHAR2(30) := p_vendor_lot;
2364 l_reason_id NUMBER := p_reason_id;
2365
2366 l_qa_collection_id NUMBER := p_qa_collection_id;
2367
2368 l_primary_qty NUMBER;
2369 l_primary_uom_code varchar2(5);
2370
2371 l_rcv_transaction_id number;
2372 l_rtv_qty number;
2373 l_rtv_uom varchar2(25); /* Each */
2374 l_rtv_uom_code varchar2(5); /* Ea */
2375 l_receipt_source_code varchar2(25);
2376 l_tolerable_qty number;
2377
2378 l_remaining_qty number;
2379 l_transacted_qty number;
2380
2381 l_return_status varchar2(5);
2382 l_msg_count number;
2383 l_msg_data varchar2(1000);
2384
2385 l_secondary_qty NUMBER := p_secondary_qty; --OPM COnvergence
2386 l_remaining_sec_qty NUMBER; --OPM Convergence
2387 l_rtv_sec_uom VARCHAR2(25);--OPM Convergence
2388 l_sec_uom_code VARCHAR2(3);--OPM Convergence
2389 l_sec_uom VARCHAR2(25);--OPM Convergence
2390 l_rtv_sec_qty NUMBER;--OPM COnvergence
2391 l_sec_remaining_qty NUMBER; --OPM Convergence
2392 /* cursor rtv_cursor(
2393 k_oe_order_header_id number
2394 , k_organization_id number
2395 , k_inventory_item_id number
2396 , k_revision varchar2)
2397 is
2398 select
2399 rcv_transaction_id
2400 , receipt_source_code
2401 , unit_of_measure
2402 from rcv_transactions_v
2403 where receipt_source_code = 'CUSTOMER'
2404 and oe_order_header_id = k_oe_order_header_id
2405 and to_organization_id = k_organization_id
2406 and item_id = k_inventory_item_id
2407 and (item_revision = k_revision OR
2408 item_revision is null and p_revision is null)
2409 and inspection_status_code = 'NOT INSPECTED'
2410 and routing_id = g_inspection_routing;
2411 */
2412 /* Bug 1542687: For performance reasons, the cursor is based on base tables below.*/
2413
2414 cursor rtv_cursor(
2415 k_oe_order_header_id number
2416 , k_organization_id number
2417 , k_inventory_item_id number
2418 , k_revision varchar2
2419 , k_revision_control number -- Added for bug 3134272
2420 ) is
2421 select
2422 rs.rcv_transaction_id
2423 , rsh.receipt_source_code
2424 , rs.unit_of_measure
2425 , rs.secondary_unit_of_measure --OPM Convergence
2426 from rcv_supply rs, rcv_transactions rt, rcv_shipment_headers rsh
2427 where rsh.receipt_source_code = 'CUSTOMER'
2428 and rs.oe_order_header_id = k_oe_order_header_id
2429 and rs.to_organization_id = k_organization_id
2430 and rs.item_id = k_inventory_item_id
2431 and (k_revision_control = 2
2432 and Nvl(rs.item_revision,-1) = Nvl(k_revision,-1)
2433 OR k_revision_control = 1)
2434 -- Changed the above for bug 3134272
2435 and rs.rcv_transaction_id = rt.transaction_id
2436 and rsh.shipment_header_id = rs.shipment_header_id
2437 and rt.inspection_status_code = 'NOT INSPECTED'
2438 and rs.supply_type_code = 'RECEIVING'
2439 and rt.transaction_type <> 'UNORDERED'
2440 and rt.routing_header_id = g_inspection_routing;
2441 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2442 begin
2443 x_return_status := fnd_api.g_ret_sts_success;
2444
2445 -- dbms_output.put_line('main_process_rma: Just entering main_process_rma');
2446 --First check if the transaction date satisfies the validation.
2447 --If the transaction date is invalid then error out the transaction
2448 IF inv_rcv_common_apis.g_po_startup_value.sob_id IS NULL THEN
2449 --BUG 3444196: Used the HR view instead for performance reasons
2450 SELECT TO_NUMBER(hoi.org_information1)
2451 INTO inv_rcv_common_apis.g_po_startup_value.sob_id
2452 FROM hr_organization_information hoi
2453 WHERE hoi.organization_id = p_organization_id
2454 AND (hoi.org_information_context || '') = 'Accounting Information' ;
2455 END IF;
2456
2457 inv_rcv_common_apis.validate_trx_date(
2458 p_trx_date => SYSDATE
2459 , p_organization_id => p_organization_id
2460 , p_sob_id => inv_rcv_common_apis.g_po_startup_value.sob_id
2461 , x_return_status => x_return_status
2462 , x_error_code => x_msg_data
2463 );
2464
2465 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2466 RETURN;
2467 END IF;
2468
2469 savepoint inspect_main_rma_sp;
2470
2471 -- Quantity entered on form
2472 l_remaining_qty := l_quantity;
2473
2474 -- Quantity successfully transacted
2475 l_transacted_qty := 0;
2476
2477 -- One time fetch of item's primary uom code
2478 -- Fetching revision control for bug 3134272
2479 select primary_uom_code,revision_qty_control_code, secondary_uom_code --OPM Convergence
2480 into l_primary_uom_code,l_revision_control, l_sec_uom_code --OPM Convergence
2481 from mtl_system_items
2482 where organization_id = l_organization_id
2483 and inventory_item_id = l_inventory_item_id;
2484
2485 -- dbms_output.put_line('main_process_rma: Fetched item primary uom code');
2486
2487 -- Purchasing/receiving uses unit of measure (Each)
2488 -- rather than uom code(Ea) and hence the following..
2489 -- This will be used later while inserting into interface table
2490
2491 SELECT unit_of_measure
2492 INTO l_uom
2493 FROM mtl_units_of_measure
2494 WHERE uom_code = l_uom_code;
2495
2496 /* OPM Convergence */
2497 IF l_sec_uom_code IS NOT NULL THEN
2498
2499 SELECT unit_of_measure
2500 INTO l_sec_uom
2501 FROM mtl_units_of_measure
2502 WHERE uom_code = l_sec_uom_code;
2503
2504 END IF;
2505 -- dbms_output.put_line('main_process_rma: Convert inspection uom code into uom');
2506
2507 -- Open RCV Transactions V cursor
2508 open rtv_cursor(
2509 l_oe_order_header_id
2510 , l_organization_id
2511 , l_inventory_item_id
2512 , l_revision
2513 , l_revision_control -- added for bug 3134272
2514 );
2515
2516 -- dbms_output.put_line('main_process_rma: Opened RTV Cursor');
2517
2518 while(l_remaining_qty > 0)
2519 loop
2520 fetch rtv_cursor into
2521 l_rcv_transaction_id
2522 , l_receipt_source_code
2523 , l_rtv_uom
2524 , l_rtv_sec_uom;
2525
2526 if rtv_cursor%notfound then
2527 exit;
2528 end if;
2529
2530 -- Get quantity that can be still inspected
2531
2532 RCV_QUANTITIES_S.GET_AVAILABLE_QUANTITY (
2533 'INSPECT'
2534 , l_rcv_transaction_id
2535 , l_receipt_source_code
2536 , null
2537 , l_rcv_transaction_id
2538 , null
2539 , l_rtv_qty
2540 , l_tolerable_qty
2541 , l_rtv_uom );
2542
2543 if (l_rtv_qty > 0) then
2544
2545 -- dbms_output.put_line('main_process_rma: convert rtv uom into uom code');
2546
2547 SELECT uom_code
2548 INTO l_rtv_uom_code
2549 FROM mtl_units_of_measure
2550 WHERE unit_of_measure = l_rtv_uom;
2551
2552 -- If inspection uom is not same as receipt uom, convert
2553
2554 if (l_uom_code <> l_rtv_uom_code) then
2555 l_rtv_qty := inv_convert.inv_um_convert(
2556 l_inventory_item_id
2557 , NULL
2558 , l_rtv_qty
2559 , l_rtv_uom_code
2560 , l_uom_code
2561 , NULL
2562 , NULL);
2563 end if;
2564
2565 if l_rtv_qty >= l_remaining_qty then
2566 l_rtv_qty := l_remaining_qty;
2567 l_remaining_qty := 0;
2568 else
2569 l_remaining_qty := l_remaining_qty - l_rtv_qty;
2570 end if;
2571
2572 -- If required convert into primary unit of measure
2573 if (l_uom_code <> l_primary_uom_code) then
2574
2575 -- dbms_output.put_line('main_process_rma: convet inspect uom into primary uom');
2576
2577 l_primary_qty := inv_convert.inv_um_convert(
2578 l_inventory_item_id
2579 , NULL
2580 , l_rtv_qty
2581 , l_uom_code
2582 , l_primary_uom_code
2583 , NULL
2584 , NULL);
2585 else
2586 l_primary_qty := l_rtv_qty;
2587 end if;
2588
2589 -- dbms_output.put_line('main_process_rma: Calling insert_inspect_rec_rti');
2590
2591 -- Insert into rti, passing l_rtv_qty, inspection information
2592 insert_inspect_rec_rti (
2593 x_return_status => l_return_status
2594 , x_msg_count => l_msg_count
2595 , x_msg_data => l_msg_data
2596 , p_rcv_transaction_id => l_rcv_transaction_id
2597 , p_quantity => l_rtv_qty
2598 , p_uom => l_uom
2599 , p_inspection_code => l_inspection_code
2600 , p_quality_code => l_quality_code
2601 , p_transaction_date => l_transaction_date
2602 , p_transaction_type => l_transaction_type
2603 , p_vendor_lot => l_vendor_lot
2604 , p_reason_id => l_reason_id
2605 , p_primary_qty => l_primary_qty
2606 , p_organization_id => l_organization_id
2607 , p_comments => l_comments
2608 , p_attribute_category => l_attribute_category
2609 , p_attribute1 => l_attribute1
2610 , p_attribute2 => l_attribute2
2611 , p_attribute3 => l_attribute3
2612 , p_attribute4 => l_attribute4
2613 , p_attribute5 => l_attribute5
2614 , p_attribute6 => l_attribute6
2615 , p_attribute7 => l_attribute7
2616 , p_attribute8 => l_attribute8
2617 , p_attribute9 => l_attribute9
2618 , p_attribute10 => l_attribute10
2619 , p_attribute11 => l_attribute11
2620 , p_attribute12 => l_attribute12
2621 , p_attribute13 => l_attribute13
2622 , p_attribute14 => l_attribute14
2623 , p_attribute15 => l_attribute15
2624 , p_qa_collection_id => l_qa_collection_id
2625 , p_sec_uom => l_sec_uom --OPM Convergence
2626 , p_secondary_qty => l_rtv_sec_qty); --OPM Convergence);
2627
2628 IF l_return_status = fnd_api.g_ret_sts_error THEN
2629 RAISE fnd_api.g_exc_error;
2630 END IF ;
2631
2632 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
2633 RAISE fnd_api.g_exc_unexpected_error;
2634 END IF;
2635
2636 -- dbms_output.put_line('main_process_rma: Successful insert_inspect_rec_rti');
2637
2638 -- Count successfully transacted qty
2639 l_transacted_qty := l_transacted_qty + l_rtv_qty;
2640 end if;
2641 end loop;
2642
2643 IF l_remaining_qty > 0 THEN
2644 FND_MESSAGE.set_name('INV','INV_QTY_LESS_OR_EQUAL');
2645 FND_MSG_PUB.Add;
2646 RAISE FND_API.G_EXC_ERROR;
2647 END IF;
2648
2649 close rtv_cursor;
2650
2651 exception
2652 when fnd_api.g_exc_error THEN
2653 rollback to inspect_main_rma_sp;
2654
2655 x_return_status := fnd_api.g_ret_sts_error;
2656
2657 -- Get message count and data
2658 fnd_msg_pub.count_and_get
2659 ( p_count => x_msg_count
2660 , p_data => x_msg_data
2661 );
2662
2663 IF (rtv_cursor%isopen) THEN
2664 CLOSE rtv_cursor;
2665 END IF;
2666
2667 when fnd_api.g_exc_unexpected_error THEN
2668 rollback to inspect_main_rma_sp;
2669
2670 x_return_status := fnd_api.g_ret_sts_unexp_error ;
2671
2672 -- Get message count and data
2673 fnd_msg_pub.count_and_get
2674 ( p_count => x_msg_count
2675 , p_data => x_msg_data
2676 );
2677
2678 IF (rtv_cursor%isopen) THEN
2679 CLOSE rtv_cursor;
2680 END IF;
2681
2682 when others THEN
2683 rollback to inspect_main_rma_sp;
2684
2685 x_return_status := fnd_api.g_ret_sts_unexp_error ;
2686 --
2687 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
2688 THEN
2689 fnd_msg_pub.add_exc_msg
2690 ( g_pkg_name
2691 , 'main_process_rma'
2692 );
2693 END IF;
2694
2695 -- Get message count and data
2696 fnd_msg_pub.count_and_get
2697 ( p_count => x_msg_count
2698 , p_data => x_msg_data
2699 );
2700
2701 IF (rtv_cursor%isopen) THEN
2702 CLOSE rtv_cursor;
2703 END IF;
2704 end main_process_rma;
2705
2706 procedure main_process_receipt(
2707 x_return_status OUT NOCOPY VARCHAR2
2708 , x_msg_count OUT NOCOPY NUMBER
2709 , x_msg_data OUT NOCOPY VARCHAR2
2710 , p_inventory_item_id IN NUMBER
2711 , p_organization_id IN NUMBER
2712 , p_receipt_num IN VARCHAR2
2713 , p_revision IN VARCHAR2
2714 , p_uom_code IN VARCHAR2
2715 , p_quantity IN NUMBER
2716 , p_inspection_code IN VARCHAR2
2717 , p_quality_code IN VARCHAR2
2718 , p_transaction_type IN VARCHAR2
2719 , p_reason_id IN NUMBER
2720 , p_transaction_date IN DATE DEFAULT SYSDATE
2721 , p_qa_collection_id IN NUMBER DEFAULT NULL
2722 , p_vendor_lot IN VARCHAR2 DEFAULT NULL
2723 , p_comments IN VARCHAR2 DEFAULT NULL
2724 , p_attribute_category IN VARCHAR2 DEFAULT NULL
2725 , p_attribute1 IN VARCHAR2 DEFAULT NULL
2726 , p_attribute2 IN VARCHAR2 DEFAULT NULL
2727 , p_attribute3 IN VARCHAR2 DEFAULT NULL
2728 , p_attribute4 IN VARCHAR2 DEFAULT NULL
2729 , p_attribute5 IN VARCHAR2 DEFAULT NULL
2730 , p_attribute6 IN VARCHAR2 DEFAULT NULL
2731 , p_attribute7 IN VARCHAR2 DEFAULT NULL
2732 , p_attribute8 IN VARCHAR2 DEFAULT NULL
2733 , p_attribute9 IN VARCHAR2 DEFAULT NULL
2734 , p_attribute10 IN VARCHAR2 DEFAULT NULL
2735 , p_attribute11 IN VARCHAR2 DEFAULT NULL
2736 , p_attribute12 IN VARCHAR2 DEFAULT NULL
2737 , p_attribute13 IN VARCHAR2 DEFAULT NULL
2738 , p_attribute14 IN VARCHAR2 DEFAULT NULL
2739 , p_attribute15 IN VARCHAR2 DEFAULT NULL
2740 , p_secondary_qty IN NUMBER DEFAULT NULL) --OPM Convergence)
2741 is
2742 l_inventory_item_id NUMBER := p_inventory_item_id;
2743 l_organization_id NUMBER := p_organization_id;
2744 l_revision VARCHAR2(10) := p_revision;
2745 l_revision_control NUMBER; -- Added for bug 3134272
2746 l_uom_code VARCHAR2(5) := p_uom_code;
2747 l_uom VARCHAR2(30);
2748 l_quantity NUMBER := p_quantity;
2749 l_receipt_num NUMBER := p_receipt_num;
2750
2751 l_inspection_code VARCHAR2(25) := p_inspection_code;
2752 l_quality_code VARCHAR2(25) := p_quality_code;
2753 l_transaction_date DATE := p_transaction_date;
2754 l_comments VARCHAR2(240) := p_comments;
2755 l_attribute_category VARCHAR2(30) := p_attribute_category;
2756 l_attribute1 VARCHAR2(150) := p_attribute1;
2757 l_attribute2 VARCHAR2(150) := p_attribute2;
2758 l_attribute3 VARCHAR2(150) := p_attribute3;
2759 l_attribute4 VARCHAR2(150) := p_attribute4;
2760 l_attribute5 VARCHAR2(150) := p_attribute5;
2761 l_attribute6 VARCHAR2(150) := p_attribute6;
2762 l_attribute7 VARCHAR2(150) := p_attribute7;
2763 l_attribute8 VARCHAR2(150) := p_attribute8;
2764 l_attribute9 VARCHAR2(150) := p_attribute9;
2765 l_attribute10 VARCHAR2(150) := p_attribute10;
2766 l_attribute11 VARCHAR2(150) := p_attribute11;
2767 l_attribute12 VARCHAR2(150) := p_attribute12;
2768 l_attribute13 VARCHAR2(150) := p_attribute13;
2769 l_attribute14 VARCHAR2(150) := p_attribute14;
2770 l_attribute15 VARCHAR2(150) := p_attribute15;
2771 l_transaction_type VARCHAR2(30) := p_transaction_type;
2772 l_vendor_lot VARCHAR2(30) := p_vendor_lot;
2773 l_reason_id NUMBER := p_reason_id;
2774
2775 l_qa_collection_id NUMBER := p_qa_collection_id;
2776
2777 l_primary_qty NUMBER;
2778 l_primary_uom_code varchar2(5);
2779
2780 l_rcv_transaction_id number;
2781 l_rtv_qty number;
2782 l_rtv_uom varchar2(25); /* Each */
2783 l_rtv_uom_code varchar2(5); /* Ea */
2784 l_receipt_source_code varchar2(25);
2785 l_tolerable_qty number;
2786
2787 l_remaining_qty number;
2788 l_transacted_qty number;
2789
2790 l_return_status varchar2(5);
2791 l_msg_count number;
2792 l_msg_data varchar2(1000);
2793
2794 l_secondary_qty NUMBER := p_secondary_qty; --OPM COnvergence
2795 l_remaining_sec_qty NUMBER; --OPM Convergence
2796 l_rtv_sec_uom VARCHAR2(25);--OPM Convergence
2797 l_sec_uom_code VARCHAR2(3);--OPM Convergence
2798 l_sec_uom VARCHAR2(25);--OPM Convergence
2799 l_rtv_sec_qty NUMBER;--OPM COnvergence
2800 l_sec_remaining_qty NUMBER; --OPM Convergence
2801
2802 /* cursor rtv_cursor(
2803 k_receipt_num varchar2
2804 , k_organization_id number
2805 , k_inventory_item_id number
2806 , k_revision varchar2)
2807 is
2808 select
2809 rcv_transaction_id
2810 , receipt_source_code
2811 , unit_of_measure
2812 from rcv_transactions_v
2813 where receipt_num = k_receipt_num
2814 and to_organization_id = k_organization_id
2815 and item_id = k_inventory_item_id
2816 and (item_revision = k_revision OR
2817 item_revision is null and p_revision is null)
2818 and inspection_status_code = 'NOT INSPECTED'
2819 and routing_id = g_inspection_routing;
2820 */
2821 /* Bug 1542687: For performance reasons, the cursor is based on base tables below.*/
2822
2823 cursor rtv_cursor(
2824 k_receipt_num varchar2
2825 , k_organization_id number
2826 , k_inventory_item_id number
2827 , k_revision varchar2
2828 , k_revision_control number -- Added for bug 3134272
2829 ) is
2830 select
2831 rs.rcv_transaction_id
2832 , rsh.receipt_source_code
2833 , rs.unit_of_measure
2834 , rs.secondary_unit_of_measure --OPM Convergence
2835 from rcv_supply rs, rcv_transactions rt, rcv_shipment_headers rsh
2836 where rsh.receipt_num = k_receipt_num
2837 and rs.to_organization_id = k_organization_id
2838 and rs.item_id = k_inventory_item_id
2839 and (k_revision_control = 2
2840 and Nvl(rs.item_revision,-1) = Nvl(k_revision,-1)
2841 OR k_revision_control = 1)
2842 -- Changed the above for bug 3134272
2843 and rs.rcv_transaction_id = rt.transaction_id
2844 and rsh.shipment_header_id = rs.shipment_header_id
2845 and rt.inspection_status_code = 'NOT INSPECTED'
2846 and rs.supply_type_code = 'RECEIVING'
2847 and rt.transaction_type <> 'UNORDERED'
2848 and rt.routing_header_id = g_inspection_routing;
2849 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2850 begin
2851 x_return_status := fnd_api.g_ret_sts_success;
2852
2853 -- dbms_output.put_line('main_process_receipt: Just entering main_process_receipt');
2854 --First check if the transaction date satisfies the validation.
2855 --If the transaction date is invalid then error out the transaction
2856 IF inv_rcv_common_apis.g_po_startup_value.sob_id IS NULL THEN
2857 --BUG 3444196: Used the HR view instead for performance reasons
2858 SELECT TO_NUMBER(hoi.org_information1)
2859 INTO inv_rcv_common_apis.g_po_startup_value.sob_id
2860 FROM hr_organization_information hoi
2861 WHERE hoi.organization_id = p_organization_id
2862 AND (hoi.org_information_context || '') = 'Accounting Information' ;
2863 END IF;
2864
2865 inv_rcv_common_apis.validate_trx_date(
2866 p_trx_date => SYSDATE
2867 , p_organization_id => p_organization_id
2868 , p_sob_id => inv_rcv_common_apis.g_po_startup_value.sob_id
2869 , x_return_status => x_return_status
2870 , x_error_code => x_msg_data
2871 );
2872
2873 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2874 RETURN;
2875 END IF;
2876
2877 savepoint inspect_main_receipt_sp;
2878
2879 -- Quantity entered on form
2880 l_remaining_qty := l_quantity;
2881
2882 -- Quantity successfully transacted
2883 l_transacted_qty := 0;
2884
2885 -- One time fetch of item's primary uom code
2886 -- Fetching revision control for bug 3134272
2887 select primary_uom_code,revision_qty_control_code, secondary_uom_code --OPM Convergence
2888 into l_primary_uom_code,l_revision_control, l_sec_uom_code --OPM Convergence
2889 from mtl_system_items
2890 where organization_id = l_organization_id
2891 and inventory_item_id = l_inventory_item_id;
2892
2893 -- dbms_output.put_line('main_process_receipt: Fetched item primary uom code');
2894
2895 -- Purchasing/receiving uses unit of measure (Each)
2896 -- rather than uom code(Ea) and hence the following..
2897 -- This will be used later while inserting into interface table
2898
2899 SELECT unit_of_measure
2900 INTO l_uom
2901 FROM mtl_units_of_measure
2902 WHERE uom_code = l_uom_code;
2903
2904 /* OPM Convergence */
2905 IF l_sec_uom_code IS NOT NULL THEN
2906
2907 SELECT unit_of_measure
2908 INTO l_sec_uom
2909 FROM mtl_units_of_measure
2910 WHERE uom_code = l_sec_uom_code;
2911
2912 END IF;
2913 -- dbms_output.put_line('main_process_receipt: Convert inspection uom code into uom');
2914
2915 -- Open RCV Transactions V cursor
2916 open rtv_cursor(
2917 l_receipt_num
2918 , l_organization_id
2919 , l_inventory_item_id
2920 , l_revision
2921 , l_revision_control -- Added for bug 3134272
2922 );
2923
2924 -- dbms_output.put_line('main_process_receipt: Opened RTV Cursor');
2925
2926 IF (l_debug = 1) THEN
2927 print_debug('l_receipt_num is ' || to_char(l_receipt_num), 4);
2928 END IF;
2929
2930 while(l_remaining_qty > 0)
2931 loop
2932 fetch rtv_cursor into
2933 l_rcv_transaction_id
2934 , l_receipt_source_code
2935 , l_rtv_uom
2936 , l_rtv_sec_uom; --OPM Convergence
2937
2938 if rtv_cursor%notfound then
2939 IF (l_debug = 1) THEN
2940 print_debug('exited from cursor', 4);
2941 END IF;
2942 exit;
2943 end if;
2944
2945 -- Get quantity that can be still inspected
2946
2947 IF (l_debug = 1) THEN
2948 print_debug('l_rcv_transaction_id is ' || to_char(l_rcv_transaction_id), 4);
2949 print_debug('l_receipt_source_code is ' || l_receipt_source_code, 4);
2950 END IF;
2951 RCV_QUANTITIES_S.GET_AVAILABLE_QUANTITY (
2952 'INSPECT'
2953 , l_rcv_transaction_id
2954 , l_receipt_source_code
2955 , null
2956 , l_rcv_transaction_id
2957 , null
2958 , l_rtv_qty
2959 , l_tolerable_qty
2960 , l_rtv_uom );
2961
2962 /* print_debug('l_rtv_qty is ' || to_char(l_rtv_qty), 4); */
2963
2964 if (l_rtv_qty > 0) then
2965
2966 -- dbms_output.put_line('main_process_receipt: convert rtv uom into uom code');
2967
2968 SELECT uom_code
2969 INTO l_rtv_uom_code
2970 FROM mtl_units_of_measure
2971 WHERE unit_of_measure = l_rtv_uom;
2972
2973 -- If inspection uom is not same as receipt uom, convert
2974
2975 if (l_uom_code <> l_rtv_uom_code) then
2976 l_rtv_qty := inv_convert.inv_um_convert(
2977 l_inventory_item_id
2978 , NULL
2979 , l_rtv_qty
2980 , l_rtv_uom_code
2981 , l_uom_code
2982 , NULL
2983 , NULL);
2984 end if;
2985
2986 if l_rtv_qty >= l_remaining_qty then
2987 l_rtv_qty := l_remaining_qty;
2988 l_rtv_sec_qty := l_sec_remaining_qty; --OPM Convergence
2989 l_remaining_qty := 0;
2990 l_sec_remaining_qty := 0; --OPM Convergence
2991 else
2992 l_remaining_qty := l_remaining_qty - l_rtv_qty;
2993 l_sec_remaining_qty := l_sec_remaining_qty - l_rtv_sec_qty; --OPM Convergence
2994 end if;
2995
2996 -- If required convert into primary unit of measure
2997 if (l_uom_code <> l_primary_uom_code) then
2998
2999 -- dbms_output.put_line('main_process_receipt: convet inspect uom into primary uom');
3000
3001 l_primary_qty := inv_convert.inv_um_convert(
3002 l_inventory_item_id
3003 , NULL
3004 , l_rtv_qty
3005 , l_uom_code
3006 , l_primary_uom_code
3007 , NULL
3008 , NULL);
3009 else
3010 l_primary_qty := l_rtv_qty;
3011 end if;
3012
3013 -- dbms_output.put_line('main_process_receipt: Calling insert_inspect_rec_rti');
3014
3015 -- Insert into rti, passing l_rtv_qty, inspection information
3016 insert_inspect_rec_rti (
3017 x_return_status => l_return_status
3018 , x_msg_count => l_msg_count
3019 , x_msg_data => l_msg_data
3020 , p_rcv_transaction_id => l_rcv_transaction_id
3021 , p_quantity => l_rtv_qty
3022 , p_uom => l_uom
3023 , p_inspection_code => l_inspection_code
3024 , p_quality_code => l_quality_code
3025 , p_transaction_date => l_transaction_date
3026 , p_transaction_type => l_transaction_type
3027 , p_vendor_lot => l_vendor_lot
3028 , p_reason_id => l_reason_id
3029 , p_primary_qty => l_primary_qty
3030 , p_organization_id => l_organization_id
3031 , p_comments => l_comments
3032 , p_attribute_category => l_attribute_category
3033 , p_attribute1 => l_attribute1
3034 , p_attribute2 => l_attribute2
3035 , p_attribute3 => l_attribute3
3036 , p_attribute4 => l_attribute4
3037 , p_attribute5 => l_attribute5
3038 , p_attribute6 => l_attribute6
3039 , p_attribute7 => l_attribute7
3040 , p_attribute8 => l_attribute8
3041 , p_attribute9 => l_attribute9
3042 , p_attribute10 => l_attribute10
3043 , p_attribute11 => l_attribute11
3044 , p_attribute12 => l_attribute12
3045 , p_attribute13 => l_attribute13
3046 , p_attribute14 => l_attribute14
3047 , p_attribute15 => l_attribute15
3048 , p_qa_collection_id => l_qa_collection_id ,
3049 p_sec_uom => l_sec_uom --OPM Convergence
3050 , p_secondary_qty => l_rtv_sec_qty); --OPM Convergence
3051
3052 IF l_return_status = fnd_api.g_ret_sts_error THEN
3053 IF (l_debug = 1) THEN
3054 print_debug('exc_error ' || l_return_status, 4);
3055 END IF;
3056 RAISE fnd_api.g_exc_error;
3057 END IF ;
3058
3059 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
3060 IF (l_debug = 1) THEN
3061 print_debug('exc_unexpected_error ' || l_return_status, 4);
3062 END IF;
3063 RAISE fnd_api.g_exc_unexpected_error;
3064 END IF;
3065
3066 -- dbms_output.put_line('main_process_receipt: Successful insert_inspect_rec_rti');
3067
3068 -- Count successfully transacted qty
3069 l_transacted_qty := l_transacted_qty + l_rtv_qty;
3070 IF (l_debug = 1) THEN
3071 print_debug('transacted qty ' || l_transacted_qty, 4);
3072 END IF;
3073 end if;
3074 IF (l_debug = 1) THEN
3075 print_debug('remaining qty ' || l_remaining_qty, 4);
3076 END IF;
3077 end loop;
3078
3079 IF l_remaining_qty > 0 THEN
3080 FND_MESSAGE.set_name('INV','INV_QTY_LESS_OR_EQUAL');
3081 FND_MSG_PUB.Add;
3082 RAISE FND_API.G_EXC_ERROR;
3083 END IF;
3084
3085 close rtv_cursor;
3086
3087 exception
3088 when fnd_api.g_exc_error THEN
3089 IF (l_debug = 1) THEN
3090 print_debug('Jumped to Exception exc_error ', 4);
3091 END IF;
3092 rollback to inspect_main_receipt_sp;
3093
3094 x_return_status := fnd_api.g_ret_sts_error;
3095 IF (l_debug = 1) THEN
3096 print_debug('Jumped to Exception exc_error ' || x_return_status, 4);
3097 END IF;
3098
3099 -- Get message count and data
3100 fnd_msg_pub.count_and_get
3101 ( p_count => x_msg_count
3102 , p_data => x_msg_data
3103 );
3104
3105 IF (rtv_cursor%isopen) THEN
3106 CLOSE rtv_cursor;
3107 END IF;
3108
3109 when fnd_api.g_exc_unexpected_error THEN
3110 IF (l_debug = 1) THEN
3111 print_debug('Jumped to Exception unexpected_exc_error ', 4);
3112 END IF;
3113 rollback to inspect_main_receipt_sp;
3114
3115 x_return_status := fnd_api.g_ret_sts_unexp_error ;
3116 IF (l_debug = 1) THEN
3117 print_debug('Jumped to Exception unexpected_exc_error ' || x_return_status, 4);
3118 END IF;
3119
3120 -- Get message count and data
3121 fnd_msg_pub.count_and_get
3122 ( p_count => x_msg_count
3123 , p_data => x_msg_data
3124 );
3125
3126 IF (rtv_cursor%isopen) THEN
3127 CLOSE rtv_cursor;
3128 END IF;
3129
3130 when others THEN
3131 IF (l_debug = 1) THEN
3132 print_debug('Jumped to Exception others', 4);
3133 END IF;
3134 rollback to inspect_main_receipt_sp;
3135
3136 x_return_status := fnd_api.g_ret_sts_unexp_error ;
3137 IF (l_debug = 1) THEN
3138 print_debug('Jumped to Exception others' || x_return_status, 4);
3139 END IF;
3140 --
3141 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
3142 THEN
3143 fnd_msg_pub.add_exc_msg
3144 ( g_pkg_name
3145 , 'main_process_receipt'
3146 );
3147 END IF;
3148
3149 -- Get message count and data
3150 fnd_msg_pub.count_and_get
3151 ( p_count => x_msg_count
3152 , p_data => x_msg_data
3153 );
3154
3155 IF (rtv_cursor%isopen) THEN
3156 CLOSE rtv_cursor;
3157 END IF;
3158 end main_process_receipt;
3159
3160 procedure insert_inspect_rec_rti (
3161 x_return_status OUT NOCOPY VARCHAR2
3162 , x_msg_count OUT NOCOPY NUMBER
3163 , x_msg_data OUT NOCOPY VARCHAR2
3164 , p_rcv_transaction_id IN NUMBER
3165 , p_quantity IN NUMBER
3166 , p_uom IN VARCHAR2
3167 , p_inspection_code IN VARCHAR2
3168 , p_quality_code IN VARCHAR2
3169 , p_transaction_date IN DATE
3170 , p_transaction_type IN VARCHAR2
3171 , p_vendor_lot IN VARCHAR2
3172 , p_reason_id IN NUMBER
3173 , p_primary_qty IN NUMBER
3174 , p_organization_id IN NUMBER
3175 , p_comments IN VARCHAR2 DEFAULT NULL
3176 , p_attribute_category IN VARCHAR2 DEFAULT NULL
3177 , p_attribute1 IN VARCHAR2 DEFAULT NULL
3178 , p_attribute2 IN VARCHAR2 DEFAULT NULL
3179 , p_attribute3 IN VARCHAR2 DEFAULT NULL
3180 , p_attribute4 IN VARCHAR2 DEFAULT NULL
3181 , p_attribute5 IN VARCHAR2 DEFAULT NULL
3182 , p_attribute6 IN VARCHAR2 DEFAULT NULL
3183 , p_attribute7 IN VARCHAR2 DEFAULT NULL
3184 , p_attribute8 IN VARCHAR2 DEFAULT NULL
3185 , p_attribute9 IN VARCHAR2 DEFAULT NULL
3186 , p_attribute10 IN VARCHAR2 DEFAULT NULL
3187 , p_attribute11 IN VARCHAR2 DEFAULT NULL
3188 , p_attribute12 IN VARCHAR2 DEFAULT NULL
3189 , p_attribute13 IN VARCHAR2 DEFAULT NULL
3190 , p_attribute14 IN VARCHAR2 DEFAULT NULL
3191 , p_attribute15 IN VARCHAR2 DEFAULT NULL
3192 , p_qa_collection_id IN NUMBER DEFAULT NULL
3193 , p_lpn_id IN NUMBER DEFAULT NULL
3194 , p_transfer_lpn_id IN NUMBER DEFAULT NULL
3195 , p_mmtt_temp_id IN NUMBER DEFAULT NULL
3196 , p_sec_uom IN VARCHAR2 DEFAULT NULL --OPM Convergenc
3197 , p_secondary_qty IN NUMBER DEFAULT NULL
3198 ) --OPM Convergence)
3199 is
3200 l_interface_transaction_id NUMBER;
3201 l_group_id NUMBER;
3202
3203 l_user_id NUMBER;
3204 l_logon_id NUMBER;
3205 l_employee_id NUMBER;
3206 l_processor_value VARCHAR2(10);
3207
3208 l_dest_type_code VARCHAR2(25) := 'RECEIVING';
3209 l_po_dist_id NUMBER := NULL;
3210 l_deliver_to_location_id NUMBER := NULL;
3211 l_dest_context VARCHAR2(30) := 'RECEIVING';
3212 l_movement_id NUMBER := NULL;
3213
3214 l_inspection_type VARCHAR2(30);
3215
3216 l_rcv_transaction_id NUMBER := p_rcv_transaction_id;
3217 l_quantity NUMBER := p_quantity;
3218 l_uom VARCHAR2(25) := p_uom;
3219 l_inspection_code VARCHAR2(25) := p_inspection_code;
3220 l_quality_code VARCHAR2(25) := p_quality_code;
3221 l_transaction_date DATE := p_transaction_date;
3222 l_organization_id NUMBER := p_organization_id;
3223 l_comments VARCHAR2(240) := p_comments;
3224 l_attribute_category VARCHAR2(30) := p_attribute_category;
3225 l_attribute1 VARCHAR2(150) := p_attribute1;
3226 l_attribute2 VARCHAR2(150) := p_attribute2;
3227 l_attribute3 VARCHAR2(150) := p_attribute3;
3228 l_attribute4 VARCHAR2(150) := p_attribute4;
3229 l_attribute5 VARCHAR2(150) := p_attribute5;
3230 l_attribute6 VARCHAR2(150) := p_attribute6;
3231 l_attribute7 VARCHAR2(150) := p_attribute7;
3232 l_attribute8 VARCHAR2(150) := p_attribute8;
3233 l_attribute9 VARCHAR2(150) := p_attribute9;
3234 l_attribute10 VARCHAR2(150) := p_attribute10;
3235 l_attribute11 VARCHAR2(150) := p_attribute11;
3236 l_attribute12 VARCHAR2(150) := p_attribute12;
3237 l_attribute13 VARCHAR2(150) := p_attribute13;
3238 l_attribute14 VARCHAR2(150) := p_attribute14;
3239 l_attribute15 VARCHAR2(150) := p_attribute15;
3240 l_transaction_type VARCHAR2(30) := p_transaction_type;
3241 l_vendor_lot VARCHAR2(30) := p_vendor_lot;
3242 l_reason_id NUMBER := p_reason_id;
3243 l_primary_qty NUMBER := p_primary_qty;
3244
3245 l_sec_uom VARCHAR2(25) := p_sec_uom;--OPM Convergence
3246 l_secondary_qty number := p_secondary_qty; --OPM Convergence
3247
3248 l_receipt_source_code VARCHAR2(25);
3249 l_source_document_code VARCHAR2(25);
3250 l_shipment_hdr_id NUMBER;
3251 l_shipment_line_id NUMBER;
3252 l_substitute_code VARCHAR2(25);
3253 l_transaction_id NUMBER;
3254 l_po_hdr_id NUMBER;
3255 l_po_release_id NUMBER;
3256 l_po_line_id NUMBER;
3257 l_po_line_location_id NUMBER;
3258 l_po_rev_num NUMBER;
3259 l_po_unit_price NUMBER;
3260 l_currency_code VARCHAR2(15);
3261 l_currency_conv_rate NUMBER;
3262 l_currency_conv_date DATE;
3263 l_currency_conv_type VARCHAR2(30);
3264 l_req_line_id NUMBER;
3265 l_req_dist_id NUMBER;
3266 l_routing_id NUMBER;
3267 l_routing_step_id NUMBER;
3268 l_location_id NUMBER;
3269 l_category_id NUMBER;
3270 l_primary_uom VARCHAR2(25);
3271 l_item_id NUMBER;
3272 l_item_revision VARCHAR2(3);
3273 l_vendor_id NUMBER;
3274 l_mtl_lot NUMBER;
3275 l_mtl_serial NUMBER;
3276 l_routing_header_id NUMBER;
3277 l_qa_collection_id NUMBER;
3278 l_ussgl_transaction_code VARCHAR2(30);
3279 l_government_context VARCHAR2(30);
3280 l_vendor_site_id NUMBER;
3281 l_oe_order_header_id NUMBER;
3282 l_oe_order_line_id NUMBER;
3283 l_customer_id NUMBER;
3284 l_customer_site_id NUMBER;
3285 l_customer_item_number VARCHAR2(30);
3286 l_lpn_id NUMBER := p_lpn_id;
3287 l_transfer_lpn_id NUMBER := p_transfer_lpn_id;
3288 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3289 l_receipt_num VARCHAR2(30);
3290 l_validation_flag VARCHAR2(1);
3291 l_lpn_group_id NUMBER;
3292 l_mmtt_temp_id NUMBER := p_mmtt_temp_id;
3293 l_lpn_sub mtl_secondary_inventories.secondary_inventory_name%TYPE;
3294 l_lpn_loc_id NUMBER;
3295 l_xfer_lpn_sub mtl_secondary_inventories.secondary_inventory_name%TYPE;
3296 l_xfer_lpn_loc_id NUMBER;
3297 l_xfer_lpn_ctxt NUMBER;
3298 l_rti_sub_code mtl_secondary_inventories.secondary_inventory_name%TYPE;
3299 l_rti_loc_id NUMBER;
3300
3301 l_rti_project_id NUMBER := NULL;
3302 l_rti_task_id NUMBER := NULL;
3303
3304
3305 -- For Bug 7440217
3306 v_lcm_enabled_org varchar2(1);
3307 v_pre_receive varchar2(1);
3308 v_lcm_ship_line_id NUMBER;
3309 v_unit_landed_cost NUMBER;
3310 -- End for Bug 7440217
3311
3312
3313 l_operating_unit_id MO_GLOB_ORG_ACCESS_TMP.ORGANIZATION_ID%TYPE; --<R12 MOAC>
3314
3315 begin
3316 x_return_status := fnd_api.g_ret_sts_success;
3317
3318 savepoint insert_rti_sp;
3319
3320 SELECT
3321 rsh.RECEIPT_SOURCE_CODE
3322 , rt.SOURCE_DOCUMENT_CODE
3323 , rsup.SHIPMENT_HEADER_ID
3324 , rsup.SHIPMENT_LINE_ID
3325 , rt.SUBSTITUTE_UNORDERED_CODE
3326 , rsup.RCV_TRANSACTION_ID
3327 , rsup.PO_HEADER_ID
3328 , rsup.PO_RELEASE_ID
3329 , rsup.PO_LINE_ID
3330 , rsup.PO_LINE_LOCATION_ID
3331 , rt.PO_REVISION_NUM
3332 , NVL(PLL.PRICE_OVERRIDE, POL.UNIT_PRICE)
3333 , rt.CURRENCY_CODE
3334 , rt.CURRENCY_CONVERSION_RATE
3335 , rt.CURRENCY_CONVERSION_DATE
3336 , rt.CURRENCY_CONVERSION_TYPE
3337 , rsup.REQ_LINE_ID
3338 , rsl.REQ_DISTRIBUTION_ID
3339 , rt.ROUTING_header_ID
3340 , rt.ROUTING_STEP_ID
3341 , rt.LOCATION_ID
3342 , rsl.CATEGORY_ID
3343 , rt.PRIMARY_Unit_of_measure
3344 , rsup.ITEM_ID
3345 , rsup.ITEM_REVISION
3346 , rsh.VENDOR_ID
3347 , msi.LOT_CONTROL_CODE
3348 , msi.SERIAL_NUMBER_CONTROL_CODE
3349 , rt.ROUTING_HEADER_ID
3350 , rt.QA_COLLECTION_ID
3351 , rsl.USSGL_TRANSACTION_CODE
3352 , rsl.GOVERNMENT_CONTEXT
3353 , rt.VENDOR_SITE_ID
3354 , rsup.OE_ORDER_HEADER_ID
3355 , rsup.OE_ORDER_LINE_ID
3356 , rsh.CUSTOMER_ID
3357 , rsh.CUSTOMER_SITE_ID
3358 , decode(oel.item_identifier_type, 'CUST', MCI.CUSTOMER_ITEM_NUMBER, '')
3359 INTO
3360 l_receipt_source_code
3361 , l_source_document_code
3362 , l_shipment_hdr_id
3363 , l_shipment_line_id
3364 , l_substitute_code
3365 , l_transaction_id
3366 , l_po_hdr_id
3367 , l_po_release_id
3368 , l_po_line_id
3369 , l_po_line_location_id
3370 , l_po_rev_num
3371 , l_po_unit_price
3372 , l_currency_code
3373 , l_currency_conv_rate
3374 , l_currency_conv_date
3375 , l_currency_conv_type
3376 , l_req_line_id
3377 , l_req_dist_id
3378 , l_routing_id
3379 , l_routing_step_id
3380 , l_location_id
3381 , l_category_id
3382 , l_primary_uom
3383 , l_item_id
3384 , l_item_revision
3385 , l_vendor_id
3386 , l_mtl_lot
3387 , l_mtl_serial
3388 , l_routing_header_id
3389 , l_qa_collection_id
3390 , l_USSGL_TRANSACTION_CODE
3391 , l_GOVERNMENT_CONTEXT
3392 , l_vendor_site_id
3393 , l_oe_order_header_id
3394 , l_oe_order_line_id
3395 , l_customer_id
3396 , l_customer_site_id
3397 , l_customer_item_number
3398 FROM rcv_supply rsup
3399 ,rcv_shipment_headers rsh
3400 ,rcv_shipment_lines rsl
3401 ,rcv_transactions rt
3402 ,po_line_locations pll
3403 ,po_lines pol
3404 ,mtl_system_items msi
3405 ,mtl_customer_items mci
3406 ,oe_order_lines_all oel
3407 WHERE rt.transaction_id = l_rcv_transaction_id
3408 AND rt.transaction_type <> 'UNORDERED'
3409 AND rsup.supply_type_code = 'RECEIVING'
3410 AND rsup.rcv_transaction_id = rt.transaction_id
3411 AND rsh.shipment_header_id = rsup.shipment_header_id
3412 AND rsl.shipment_line_id = rsup.shipment_line_id
3413 AND pll.line_location_id(+) = rsup.po_line_location_id
3414 AND pol.po_line_id(+) = rsup.po_line_id
3415 AND msi.organization_id (+) = rsup.to_organization_id
3416 AND msi.inventory_item_id (+) = rsup.item_id
3417 AND oel.line_id(+) = rsup.oe_order_line_id
3418 AND oel.ordered_item_id = mci.customer_item_id(+);
3419
3420 Begin
3421 IF (l_debug = 1) THEN
3422 print_debug('IN INSERT_INSPECT_REC_RTF ',9);
3423 END IF;
3424 SELECT receipt_num
3425 INTO l_receipt_num
3426 FROM rcv_shipment_headers
3427 WHERE shipment_header_id = l_shipment_hdr_id
3428 AND ship_to_org_id = p_organization_id;
3429
3430 inv_rcv_common_apis.g_rcv_global_var.receipt_num := l_receipt_num;
3431 IF (l_debug = 1) THEN
3432 print_debug('create_intship_rcpt_intf_rec: 10.1 '|| inv_rcv_common_apis.g_rcv_global_var.receipt_num, 9);
3433 END IF;
3434 EXCEPTION
3435 WHEN NO_DATA_FOUND THEN
3436 l_receipt_num := NULL;
3437 END; --end of changes for bug 2894137
3438
3439 /*
3440 dbms_output.put_line('insinsprecrti: rcvtcnid ' || l_rcv_transaction_id);
3441 dbms_output.put_line('insinsprecrti: poid ' || l_po_hdr_id);
3442 dbms_output.put_line('insinsprecrti: polineid ' || l_po_line_id);
3443 dbms_output.put_line('insinsprecrti: polinelocid ' || l_po_line_location_id);
3444 */
3445 INV_RCV_COMMON_APIS.init_startup_values(l_organization_id);
3446
3447 l_user_id := INV_RCV_COMMON_APIS.g_po_startup_value.user_id;
3448 l_logon_id := INV_RCV_COMMON_APIS.g_po_startup_value.logon_id;
3449 l_employee_id := INV_RCV_COMMON_APIS.g_po_startup_value.employee_id;
3450 l_processor_value := INV_RCV_COMMON_APIS.g_po_startup_value.transaction_mode;
3451
3452 IF inv_rcv_common_apis.g_rcv_global_var.interface_group_id is NULL THEN
3453 SELECT rcv_interface_groups_s.nextval
3454 INTO l_group_id FROM dual;
3455
3456 inv_rcv_common_apis.g_rcv_global_var.interface_group_id := l_group_id;
3457 ELSE
3458 l_group_id := inv_rcv_common_apis.g_rcv_global_var.interface_group_id;
3459 END IF;
3460
3461 select rcv_transactions_interface_s.nextval
3462 into l_interface_transaction_id from dual;
3463
3464
3465 if l_inspection_code = 'ACCEPT' then
3466 l_inspection_type := 'ACCEPTED';
3467 else
3468 l_inspection_type := 'REJECTED';
3469 end if;
3470
3471 SELECT RT.MOVEMENT_ID
3472 INTO l_movement_id
3473 FROM RCV_TRANSACTIONS RT
3474 WHERE RT.TRANSACTION_ID = l_rcv_transaction_id;
3475
3476 --<R12 MOAC>
3477 l_operating_unit_id := inv_rcv_common_apis.get_operating_unit_id( l_receipt_source_code,
3478 l_po_hdr_id,
3479 l_req_line_id,
3480 l_oe_order_header_id );
3481
3482 /*
3483 ** If collection id is passed (by QA) use it i.e.overwrite
3484 ** l_qa_collection_id
3485 */
3486
3487 if (p_qa_collection_id is not null) then
3488 l_qa_collection_id := p_qa_collection_id;
3489 end if;
3490
3491 /* FP-J Enhancement
3492 * Populate the LPN_GROUP_ID, validation_flag columns, subinventory
3493 * and locator_id columns in RTI if WMS and PO patch levels are J or higher
3494 */
3495 IF ((inv_rcv_common_apis.g_wms_patch_level >= inv_rcv_common_apis.g_patchset_j) AND
3496 (inv_rcv_common_apis.g_po_patch_level >= inv_rcv_common_apis.g_patchset_j_po)) THEN
3497 l_validation_flag := 'Y';
3498 l_lpn_group_id := l_group_id;
3499
3500 /* If the current transaction is LPN-based (for a WMS org), then we need to
3501 * populate the subinventory,locator_id columns in the RTI record
3502 * If the transfer LPN has context "Resides in Receiving" Then
3503 * Populate the RTI record from WLPN for the transfer LPN
3504 * Else
3505 * Populate the RTI record from WLPN for the inspected LPN
3506 * End If
3507 */
3508 IF (p_lpn_id IS NOT NULL OR p_transfer_lpn_id IS NOT NULL) THEN
3509 BEGIN
3510 SELECT lpn_context
3511 , subinventory_code
3512 , locator_id
3513 INTO l_xfer_lpn_ctxt
3514 , l_xfer_lpn_sub
3515 , l_xfer_lpn_loc_id
3516 FROM wms_license_plate_numbers
3517 WHERE lpn_id = p_transfer_lpn_id;
3518
3519 IF (NVL(l_xfer_lpn_ctxt, 5) = 3) THEN
3520 l_rti_sub_code := l_xfer_lpn_sub;
3521 l_rti_loc_id := l_xfer_lpn_loc_id;
3522 ELSE
3523 --Transfer LPN has been generated afresh, so we need to default the RTI
3524 --with the sub/locator of the inspected LPN
3525 BEGIN
3526 SELECT subinventory_code
3527 , locator_id
3528 INTO l_lpn_sub
3529 , l_lpn_loc_id
3530 FROM wms_license_plate_numbers
3531 WHERE lpn_id = p_lpn_id;
3532
3533 l_rti_sub_code := l_lpn_sub;
3534 l_rti_loc_id := l_lpn_loc_id;
3535
3536 EXCEPTION
3537 WHEN OTHERS THEN
3538 l_rti_sub_code := NULL;
3539 l_rti_loc_id := NULL;
3540 END;
3541 END IF; --END IF check xfer lpn context
3542 EXCEPTION
3543 WHEN OTHERS THEN
3544 l_rti_sub_code := NULL;
3545 l_rti_loc_id := NULL;
3546 END;
3547
3548 -- For lpn transactions we also need to populate project and task
3549 -- FROM mol
3550 IF (p_lpn_id IS NOT NULL) THEN
3551 IF (l_debug = 1) THEN
3552 print_debug('insert_inspect_rec_rti: Before calculating project ' , 4);
3553 END IF;
3554 BEGIN
3555 SELECT project_id
3556 , task_id
3557 INTO l_rti_project_id
3558 , l_rti_task_id
3559 FROM mtl_txn_request_lines
3560 WHERE lpn_id = p_lpn_id
3561 AND inventory_item_id = l_item_id
3562 -- Bug 3366617
3563 -- The following check was not needed as the process_flag is not yet updated.
3564 -- AND wms_process_flag = 2
3565 AND ROWNUM < 2;
3566 EXCEPTION
3567 WHEN OTHERS THEN
3568 IF (l_debug = 1) THEN
3569 print_debug('insert_inspect_rec_rti: In the exception of calculating project ' , 4);
3570 END IF;
3571 l_rti_project_id := NULL;
3572 l_rti_task_id := NULL;
3573 END;
3574 END IF; --IF (p_lpn_id IN NOT NULL) THEN
3575 ELSE
3576 --For a non-LPN based transaction, subinventory/locator would be NULL
3577 l_rti_sub_code := NULL;
3578 l_rti_loc_id := NULL;
3579 --For non-lpn based transactions, project/task will also be null
3580 l_rti_project_id := NULL;
3581 l_rti_task_id := NULL;
3582 END IF;
3583 --WMS or PO patch levels are < J, default the values for these new columns to NULL
3584 ELSE
3585 l_validation_flag := NULL;
3586 l_lpn_group_id := NULL;
3587 l_rti_sub_code := NULL;
3588 l_rti_loc_id := NULL;
3589 l_rti_project_id := NULL;
3590 l_rti_task_id := NULL;
3591 END IF;
3592
3593 IF (l_debug = 1) THEN
3594 print_debug('insert_inspect_rec_rti: validation_flag : ' || l_validation_flag || ', lpn_group_id: ' || l_lpn_group_id, 4);
3595 print_debug('insert_inspect_rec_rti: subinventory : ' || l_rti_sub_code || ', locator_id: ' || l_rti_loc_id, 4);
3596 END IF;
3597
3598 -- bug 3452845
3599 IF ((inv_rcv_common_apis.g_inv_patch_level >= inv_rcv_common_apis.g_patchset_j) AND
3600 (inv_rcv_common_apis.g_po_patch_level >= inv_rcv_common_apis.g_patchset_j_po)) THEN
3601 l_transaction_date := Sysdate;
3602 ELSE
3603 l_transaction_date := Trunc(Sysdate);
3604 END IF;
3605
3606 insert into RCV_TRANSACTIONS_INTERFACE
3607 (
3608 receipt_source_code,
3609 interface_transaction_id,
3610 group_id,
3611 last_update_date,
3612 last_updated_by,
3613 created_by,
3614 creation_date,
3615 last_update_login,
3616 interface_source_code,
3617 source_document_code,
3618 destination_type_code,
3619 transaction_date,
3620 quantity,
3621 unit_of_measure,
3622 shipment_header_id,
3623 shipment_line_id,
3624 substitute_unordered_code,
3625 employee_id,
3626 parent_transaction_id,
3627 inspection_status_code,
3628 inspection_quality_code,
3629 po_header_id,
3630 po_release_id,
3631 po_line_id,
3632 po_line_location_id,
3633 po_distribution_id,
3634 po_revision_num,
3635 po_unit_price,
3636 currency_code,
3637 currency_conversion_rate,
3638 requisition_line_id,
3639 req_distribution_id,
3640 routing_header_id,
3641 routing_step_id,
3642 comments,
3643 attribute_category,
3644 attribute1,
3645 attribute2,
3646 attribute3,
3647 attribute4,
3648 attribute5,
3649 attribute6,
3650 attribute7,
3651 attribute8,
3652 attribute9,
3653 attribute10,
3654 attribute11,
3655 attribute12,
3656 attribute13,
3657 attribute14,
3658 attribute15,
3659 transaction_type,
3660 location_id,
3661 processing_status_code,
3662 processing_mode_code,
3663 transaction_status_code,
3664 category_id,
3665 vendor_lot_num,
3666 reason_id,
3667 primary_quantity,
3668 primary_unit_of_measure,
3669 item_id,
3670 item_revision,
3671 to_organization_id,
3672 deliver_to_location_id,
3673 destination_context,
3674 vendor_id,
3675 use_mtl_lot,
3676 use_mtl_serial,
3677 movement_id,
3678 currency_conversion_date,
3679 currency_conversion_type,
3680 qa_collection_id,
3681 ussgl_transaction_code,
3682 government_context,
3683 vendor_site_id,
3684 oe_order_header_id,
3685 oe_order_line_id,
3686 customer_id,
3687 customer_site_id,
3688 lpn_id,
3689 transfer_lpn_id,
3690 mobile_txn,
3691 validation_flag,
3692 lpn_group_id,
3693 mmtt_temp_id,
3694 subinventory,
3695 locator_id,
3696 project_id,
3697 task_id,
3698 secondary_quantity, --OPM Convergence
3699 secondary_unit_of_measure, --OPM Convergence
3700 org_id --<R12 MOAC>
3701 )
3702 values
3703 (
3704 l_receipt_source_code,
3705 l_interface_transaction_id,
3706 l_group_id,
3707 SYSDATE,
3708 l_user_id,
3709 l_user_id,
3710 SYSDATE,
3711 l_logon_id,
3712 'RCV',
3713 l_source_document_code,
3714 l_dest_type_code,
3715 l_transaction_date,
3716 l_quantity,
3717 l_uom,
3718 l_shipment_hdr_id,
3719 l_shipment_line_id,
3720 l_substitute_code,
3721 l_employee_id,
3722 l_transaction_id,
3723 l_inspection_type,
3724 l_quality_code,
3725 l_po_hdr_id,
3726 l_po_release_id,
3727 l_po_line_id,
3728 l_po_line_location_id,
3729 l_po_dist_id,
3730 l_po_rev_num,
3731 l_po_unit_price,
3732 l_currency_code,
3733 l_currency_conv_rate,
3734 l_req_line_id,
3735 l_req_dist_id,
3736 l_routing_id,
3737 l_routing_step_id,
3738 l_comments,
3739 l_attribute_category,
3740 l_attribute1,
3741 l_attribute2,
3742 l_attribute3,
3743 l_attribute4,
3744 l_attribute5,
3745 l_attribute6,
3746 l_attribute7,
3747 l_attribute8,
3748 l_attribute9,
3749 l_attribute10,
3750 l_attribute11,
3751 l_attribute12,
3752 l_attribute13,
3753 l_attribute14,
3754 l_attribute15,
3755 l_transaction_type,
3756 l_location_id,
3757 'PENDING', -- Formerly INSPECTION
3758 l_processor_value,
3759 'PENDING', -- Formerly INSPECTION
3760 l_category_id,
3761 l_vendor_lot,
3762 l_reason_id,
3763 l_primary_qty,
3764 l_primary_uom,
3765 l_item_id,
3766 l_item_revision,
3767 l_organization_id,
3768 l_deliver_to_location_id,
3769 l_dest_context,
3770 l_vendor_id,
3771 l_mtl_lot,
3772 l_mtl_serial,
3773 l_movement_id,
3774 Trunc(l_currency_conv_date),
3775 l_currency_conv_type,
3776 l_qa_collection_id,
3777 l_ussgl_transaction_code,
3778 l_government_context,
3779 l_vendor_site_id,
3780 l_oe_order_header_id,
3781 l_oe_order_line_id,
3782 l_customer_id,
3783 l_customer_site_id,
3784 l_lpn_id,
3785 l_transfer_lpn_id,
3786 'Y',
3787 l_validation_flag,
3788 l_lpn_group_id,
3789 l_mmtt_temp_id,
3790 l_rti_sub_code,
3791 l_rti_loc_id,
3792 l_rti_project_id,
3793 l_rti_task_id,
3794 l_secondary_qty, --OPM Convergence
3795 l_sec_uom, --OPM Convergence
3796 l_operating_unit_id --<R12 MOAC>
3797 );
3798
3799
3800
3801 -- For Bug 7440217 added the following code to update RTI with the status as PENDING so that it gets picked up for processing
3802 SELECT mp.lcm_enabled_flag
3803 INTO v_lcm_enabled_org
3804 FROM mtl_parameters mp
3805 WHERE mp.organization_id = l_organization_id;
3806
3807 SELECT rp.pre_receive
3808 INTO v_pre_receive
3809 FROM rcv_parameters rp
3810 WHERE rp.organization_id = l_organization_id;
3811
3812 IF nvl(v_lcm_enabled_org, 'N') = 'Y' THEN
3813
3814 SELECT LCM_SHIPMENT_LINE_ID, UNIT_LANDED_COST
3815 INTO v_lcm_ship_line_id, v_unit_landed_cost
3816 FROM rcv_shipment_lines
3817 WHERE shipment_line_id = l_shipment_line_id;
3818
3819 UPDATE rcv_transactions_interface
3820 SET lcm_shipment_line_id = v_lcm_ship_line_id,
3821 unit_landed_cost = v_unit_landed_cost
3822 WHERE interface_transaction_id = l_interface_transaction_id
3823 AND to_organization_id = l_organization_id;
3824 END IF;
3825 -- End for Bug 7440217
3826
3827
3828
3829 --Set the global variable for interface_transaction_id to be used in
3830 --setting product_transaction_id for the MTLI/MSNI records
3831 g_interface_transaction_id := l_interface_transaction_id;
3832
3833 exception
3834 when fnd_api.g_exc_error THEN
3835 rollback to insert_rti_sp;
3836
3837 x_return_status := fnd_api.g_ret_sts_error;
3838
3839 -- Get message count and data
3840 fnd_msg_pub.count_and_get
3841 ( p_count => x_msg_count
3842 , p_data => x_msg_data
3843 );
3844
3845 when fnd_api.g_exc_unexpected_error THEN
3846 rollback to insert_rti_sp;
3847
3848 x_return_status := fnd_api.g_ret_sts_unexp_error ;
3849
3850 -- Get message count and data
3851 fnd_msg_pub.count_and_get
3852 ( p_count => x_msg_count
3853 , p_data => x_msg_data
3854 );
3855
3856 when others THEN
3857 rollback to insert_rti_sp;
3858
3859 x_return_status := fnd_api.g_ret_sts_unexp_error ;
3860 --
3861 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
3862 THEN
3863 fnd_msg_pub.add_exc_msg
3864 ( g_pkg_name
3865 , 'insert_inspect_rec_rti'
3866 );
3867 END IF;
3868
3869 -- Get message count and data
3870 fnd_msg_pub.count_and_get
3871 ( p_count => x_msg_count
3872 , p_data => x_msg_data
3873 );
3874 end insert_inspect_rec_rti;
3875
3876 procedure rcv_manager_rpc_call(
3877 x_return_status out NOCOPY varchar2
3878 , x_return_code out NOCOPY number)
3879 is
3880 rc NUMBER;
3881 --l_timeout NUMBER := 300;
3882 l_timeout NUMBER; ----bug 5169107
3883 l_outcome VARCHAR2(200) := NULL;
3884 l_message VARCHAR2(200) := NULL;
3885 x_str varchar2(4000) := NULL;
3886
3887 r_val1 varchar2(200) := NULL;
3888 r_val2 varchar2(200) := NULL;
3889 r_val3 varchar2(200) := NULL;
3890 r_val4 varchar2(200) := NULL;
3891 r_val5 varchar2(200) := NULL;
3892 r_val6 varchar2(200) := NULL;
3893 r_val7 varchar2(200) := NULL;
3894 r_val8 varchar2(200) := NULL;
3895 r_val9 varchar2(200) := NULL;
3896 r_val10 varchar2(200) := NULL;
3897 r_val11 varchar2(200) := NULL;
3898 r_val12 varchar2(200) := NULL;
3899 r_val13 varchar2(200) := NULL;
3900 r_val14 varchar2(200) := NULL;
3901 r_val15 varchar2(200) := NULL;
3902 r_val16 varchar2(200) := NULL;
3903 r_val17 varchar2(200) := NULL;
3904 r_val18 varchar2(200) := NULL;
3905 r_val19 varchar2(200) := NULL;
3906 r_val20 varchar2(200) := NULL;
3907
3908 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3909 begin
3910 x_return_status := fnd_api.g_ret_sts_success;
3911
3912 /*
3913 dbms_output.put_line('rcv_mgr_rpc_call: group_id '
3914 || inv_RCV_COMMON_APIS.g_rcv_global_var.interface_group_id);
3915 */
3916 --bug 5169107
3917 l_timeout := fnd_profile.value('INV_RPC_TIMEOUT');
3918 if l_timeout is null then
3919 l_timeout := 300;
3920 end if;
3921 --bug 5169107
3922
3923 rc := fnd_transaction.synchronous (
3924 l_timeout, l_outcome, l_message, 'PO', 'RCVTPO',
3925 'ONLINE', inv_RCV_COMMON_APIS.g_rcv_global_var.interface_group_id,
3926 NULL, NULL, NULL, NULL, NULL, NULL,
3927 NULL, NULL, NULL, NULL, NULL, NULL,
3928 NULL, NULL, NULL, NULL, NULL, NULL);
3929
3930 -- dbms_output.put_line('rc of RPC:' || rc);
3931
3932 x_return_code := rc;
3933
3934 IF (rc = 0 and (l_outcome NOT IN ('WARNING', 'ERROR'))) THEN
3935 NULL;
3936 ELSIF (rc = 1) THEN
3937 x_return_status := fnd_api.g_ret_sts_error;
3938 ELSIF (rc = 2) THEN
3939 x_return_status := fnd_api.g_ret_sts_error;
3940 ELSIF (rc = 3 or (l_outcome IN ('WARNING', 'ERROR'))) THEN
3941 x_return_status := fnd_api.g_ret_sts_error;
3942
3943 rc := fnd_transaction.get_values (
3944 r_val1, r_val2, r_val3, r_val4, r_val5,
3945 r_val6, r_val7, r_val8, r_val9, r_val10,
3946 r_val11, r_val12, r_val13, r_val14, r_val15,
3947 r_val16, r_val17, r_val18, r_val19, r_val20);
3948
3949 /*
3950 dbms_output.put_line('r_val1 :' || r_val1);
3951 dbms_output.put_line('r_val2 :' || r_val2);
3952 dbms_output.put_line('r_val3 :' || r_val3);
3953 dbms_output.put_line('r_val4 :' || r_val4);
3954 dbms_output.put_line('r_val5 :' || r_val5);
3955 dbms_output.put_line('r_val6 :' || r_val6);
3956 dbms_output.put_line('r_val7 :' || r_val7);
3957 dbms_output.put_line('r_val8 :' || r_val8);
3958 dbms_output.put_line('r_val9 :' || r_val9);
3959 dbms_output.put_line('r_val10:' || r_val10);
3960 dbms_output.put_line('r_val11:' || r_val11);
3961 dbms_output.put_line('r_val12:' || r_val12);
3962 dbms_output.put_line('r_val13:' || r_val13);
3963 dbms_output.put_line('r_val14:' || r_val14);
3964 dbms_output.put_line('r_val15:' || r_val15);
3965 dbms_output.put_line('r_val16:' || r_val16);
3966 dbms_output.put_line('r_val17:' || r_val17);
3967 dbms_output.put_line('r_val18:' || r_val18);
3968 dbms_output.put_line('r_val19:' || r_val19);
3969 dbms_output.put_line('r_val20:' || r_val20);
3970 */
3971
3972 END IF;
3973
3974 -- reset group id
3975 inv_RCV_COMMON_APIS.g_rcv_global_var.interface_group_id := '';
3976 end rcv_manager_rpc_call;
3977
3978
3979
3980
3981
3982
3983
3984
3985 PROCEDURE launch_rcv_manager_rpc(
3986 x_return_status OUT NOCOPY VARCHAR2
3987 , x_return_code OUT NOCOPY NUMBER) IS
3988 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
3989 l_msg_count NUMBER;
3990 l_msg_data VARCHAR2(400);
3991 l_label_status VARCHAR2(500);
3992 l_txn_id_tbl inv_label.transaction_id_rec_type;
3993 l_counter NUMBER := 0;
3994 CURSOR c_rti_txn_id IS
3995 -- Bug 2377796
3996 -- LPN lables are not getting printed for rejected LPNS
3997 --SELECT MIN(rti.interface_transaction_id)
3998 SELECT rti.interface_transaction_id
3999 FROM rcv_transactions_interface rti
4000 WHERE rti.GROUP_ID = inv_rcv_common_apis.g_rcv_global_var.interface_group_id;
4001 -- GROUP BY rti.lpn_id;
4002 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
4003 BEGIN
4004
4005 --Commenting out the commit below since it would be done by the wrapper
4006 --to the receiving manager call (INV_RCV_MOBILE_PROCESS_TXN)
4007 --COMMIT;
4008
4009 -- calling label printing API
4010 /* FP-J Lot/Serial Support Enhancement
4011 * If WMS and PO patch levels are J or higher then the label printing calls
4012 * would be done from the receiving TM and should NOT be done here.
4013 * If either of these are lower than J, then retain the original processing
4014 */
4015 IF ((inv_rcv_common_apis.g_wms_patch_level < inv_rcv_common_apis.g_patchset_j) OR
4016 (inv_rcv_common_apis.g_po_patch_level < inv_rcv_common_apis.g_patchset_j_po)) THEN
4017 IF (l_debug = 1) THEN
4018 print_debug('create_std_rcpt_intf_rec: 8.1 before inv_label.print_label ', 4);
4019 END IF;
4020
4021 l_counter := 1;
4022 OPEN c_rti_txn_id;
4023
4024 LOOP
4025 FETCH c_rti_txn_id INTO l_txn_id_tbl(l_counter);
4026 EXIT WHEN c_rti_txn_id%NOTFOUND;
4027
4028 IF (l_debug = 1) THEN
4029 print_debug('create_std_rcpt_intf_rec calling printing for:' || l_txn_id_tbl(l_counter), 4);
4030 END IF;
4031
4032 l_counter := l_counter + 1;
4033 END LOOP;
4034
4035 CLOSE c_rti_txn_id;
4036 inv_label.print_label(
4037 x_return_status => l_return_status
4038 , x_msg_count => l_msg_count
4039 , x_msg_data => l_msg_data
4040 , x_label_status => l_label_status
4041 , p_api_version => 1.0
4042 , p_print_mode => 1
4043 , p_business_flow_code => 2
4044 , p_transaction_id => l_txn_id_tbl
4045 );
4046
4047 IF l_return_status <> fnd_api.g_ret_sts_success THEN
4048 fnd_message.set_name('INV', 'INV_RCV_CRT_PRINT_LAB_FAIL'); -- MSGTBD
4049 fnd_msg_pub.ADD;
4050 x_return_status := 'W';
4051
4052 IF (l_debug = 1) THEN
4053 print_debug('create_std_rcpt_intf_rec 8.2: inv_label.print_label FAILED;' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 4);
4054 END IF;
4055 END IF;
4056 --If both WMS and PO are at Patchset J or higher
4057 ELSE
4058 IF (l_debug = 1) THEN
4059 print_debug('launch_rcv_manager_rpc 6.3: WMS and PO patch levels are J or higher. So NO label printing from UI', 4);
4060 END IF;
4061 END IF; --END IF check WMS and PO patch levels
4062
4063 --Calling the receiving manager using the wrapper to honor the processing mode profile
4064 --instead of the direct RPC call
4065 --rcv_manager_rpc_call(x_return_status, x_return_code);
4066
4067
4068 IF (l_debug =1 ) THEN
4069 print_debug('********* PROCESSING_MODE IS :' ||
4070 INV_RCV_COMMON_APIS.g_po_startup_value.transaction_mode
4071 || ' ************',4);
4072 END IF;
4073
4074 INV_RCV_MOBILE_PROCESS_TXN.rcv_process_receive_txn(
4075 x_return_status => x_return_status
4076 , x_msg_data => l_msg_data);
4077
4078 IF (l_debug = 1) THEN
4079 print_debug('return status is launch procedure ' || x_return_status, 4);
4080 print_debug('return msg data l_msg_data: ' || l_msg_data, 4);
4081 END IF;
4082
4083 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
4084 IF (l_debug = 1) THEN
4085 print_debug('launch_rcv_manager_rpc 6.5: Encountered g_exc_error while calling receiving manager;'|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS'), 4);
4086 END IF;
4087 RAISE FND_API.G_EXC_ERROR;
4088 END IF;
4089
4090 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4091 IF (l_debug = 1) THEN
4092 print_debug('launch_rcv_manager_rpc 6.6: Encountered g_exc_unexp_error while calling receiving manager;'|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS'), 4);
4093 END IF;
4094 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4095 END IF;
4096
4097 EXCEPTION
4098 WHEN fnd_api.g_exc_error THEN
4099 x_return_status := fnd_api.g_ret_sts_error;
4100 IF (c_rti_txn_id%ISOPEN) THEN
4101 CLOSE c_rti_txn_id;
4102 END IF;
4103 WHEN fnd_api.g_exc_unexpected_error THEN
4104 x_return_status := fnd_api.g_ret_sts_unexp_error;
4105 IF (c_rti_txn_id%ISOPEN) THEN
4106 CLOSE c_rti_txn_id;
4107 END IF;
4108 WHEN OTHERS THEN
4109 x_return_status := fnd_api.g_ret_sts_unexp_error;
4110 IF (c_rti_txn_id%ISOPEN) THEN
4111 CLOSE c_rti_txn_id;
4112 END IF;
4113 END launch_rcv_manager_rpc;
4114
4115
4116
4117
4118
4119
4120 procedure rcv_manager_conc_call
4121 is
4122 v_req_id number;
4123 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
4124 begin
4125 v_req_id := fnd_request.submit_request('PO',
4126 'RVCTP',
4127 null,
4128 null,
4129 false,
4130 'IMMEDIATE',
4131 inv_RCV_COMMON_APIS.g_rcv_global_var.interface_group_id,
4132 '0', --fnd_char.local_chr(0), ?
4133 NULL,
4134 NULL,
4135 NULL,
4136 NULL,
4137 NULL, NULL,
4138 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
4139 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
4140 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
4141 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
4142 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
4143
4144 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
4145 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
4146 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
4147 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
4148
4149 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
4150 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
4151 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
4152 NULL, NULL, NULL, NULL, NULL, NULL, NULL);
4153
4154 /*
4155 dbms_output.put_line('request id:' || v_req_id);
4156 */
4157
4158 if (v_req_id <= 0 or v_req_id is null) then
4159 -- concurrent manager error, Handle error and rollback
4160 -- need error message etc. here ?
4161 NULL;
4162 ELSE
4163 NULL;
4164 end if;
4165
4166 -- reset group id
4167 inv_RCV_COMMON_APIS.g_rcv_global_var.interface_group_id := '';
4168
4169 end rcv_manager_conc_call;
4170
4171 procedure launch_rcv_manager_conc
4172 is
4173 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
4174 begin
4175 commit;
4176 rcv_manager_conc_call;
4177 end launch_rcv_manager_conc;
4178
4179
4180
4181 --------------------------------------------------------
4182 ----------ADDED BY MANU GUPTA 10-18-2000 ---------------
4183 -- returns S if ok, E if not
4184 -- type=LPN,RMA,INTSHIP,PO,RECEIPT
4185
4186 FUNCTION get_inspection_qty(
4187 p_type IN VARCHAR2
4188 , p_lpn_id IN NUMBER := NULL
4189 , p_po_header_id IN NUMBER := NULL
4190 , p_po_release_id IN NUMBER := NULL
4191 , p_po_line_id IN NUMBER := NULL
4192 , p_shipment_header_id IN NUMBER := NULL
4193 , p_oe_order_header_id IN NUMBER := NULL
4194 , p_organization_id IN NUMBER
4195 , p_item_id IN NUMBER
4196 , p_uom_code IN VARCHAR2
4197 , x_inspection_qty OUT NOCOPY NUMBER
4198 , x_return_status OUT NOCOPY VARCHAR2
4199 , x_msg_data OUT NOCOPY VARCHAR2) RETURN NUMBER
4200 IS
4201 l_total_qty NUMBER;
4202 l_cur_qty NUMBER;
4203 --The variable will hold the value of UOM
4204 l_cur_uom_code VARCHAR2(26); --Bug #3908752
4205 l_msg_count NUMBER;
4206 l_rcv_transaction_id NUMBER;
4207 l_tolerable_qty NUMBER;
4208
4209 CURSOR c_txn_lines IS
4210 SELECT uom_code, quantity
4211 FROM mtl_txn_request_lines
4212 WHERE inspection_status = 1
4213 AND organization_id = p_organization_id
4214 AND inventory_item_id = p_item_id
4215 AND lpn_id = p_lpn_id;
4216
4217 CURSOR c_po_source_lines IS
4218 SELECT rs.rcv_transaction_id
4219 FROM rcv_supply rs
4220 , rcv_transactions rt
4221 WHERE rs.item_id = p_item_id
4222 AND rs.po_header_id = p_po_header_id
4223 AND nvl(rs.po_release_id,-1) = nvl(p_po_release_id,nvl(rs.po_release_id,-1))
4224 AND nvl(rs.po_line_id,-1) = nvl(p_po_line_id, nvl(rs.po_line_id,-1))
4225 AND rs.rcv_transaction_id = rt.transaction_id
4226 AND rt.inspection_status_code = 'NOT INSPECTED'
4227 AND rs.supply_type_code = 'RECEIVING'
4228 AND rt.transaction_type <> 'UNORDERED'
4229 AND rt.routing_header_id = 2
4230 --BUG 4103743: Need to query on org id also
4231 AND rs.to_organization_id = p_organization_id; /* Inspection routing */
4232
4233
4234 -- use this for receipts also --
4235 CURSOR c_intship_source_lines IS
4236 SELECT rs.rcv_transaction_id
4237 FROM rcv_supply rs
4238 , rcv_transactions rt
4239 WHERE rs.item_id = p_item_id
4240 AND rs.shipment_header_id = p_shipment_header_id
4241 AND rs.rcv_transaction_id = rt.transaction_id
4242 AND rt.inspection_status_code = 'NOT INSPECTED'
4243 AND rs.supply_type_code = 'RECEIVING'
4244 AND rt.transaction_type <> 'UNORDERED'
4245 AND rt.routing_header_id = 2; /* Inspection routing */
4246
4247 CURSOR c_rma_source_lines IS
4248 SELECT rs.rcv_transaction_id
4249 FROM rcv_supply rs
4250 , rcv_transactions rt
4251 WHERE rs.item_id = p_item_id
4252 AND rs.oe_order_header_id = p_oe_order_header_id
4253 AND rs.rcv_transaction_id = rt.transaction_id
4254 AND rt.inspection_status_code = 'NOT INSPECTED'
4255 AND rs.supply_type_code = 'RECEIVING'
4256 AND rt.transaction_type <> 'UNORDERED'
4257 AND rt.routing_header_id = 2; /* Inspection routing */
4258
4259 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
4260 BEGIN
4261
4262 l_total_qty := 0;
4263
4264 -- lpn section --
4265 IF (p_type = 'LPN') THEN
4266
4267 --print_debug('Inside get_inspection_qty LPN...', 4);
4268
4269 OPEN c_txn_lines;
4270 LOOP
4271 FETCH c_txn_lines INTO l_cur_uom_code, l_cur_qty;
4272 EXIT WHEN c_txn_lines%NOTFOUND;
4273
4274 --print_debug('LPN l_cur_qty is ' || to_char(l_cur_qty), 4);
4275
4276 IF (l_cur_uom_code <> p_uom_code) THEN
4277 l_cur_qty := inv_convert.inv_um_convert(
4278 p_item_id
4279 , null
4280 , l_cur_qty
4281 , l_cur_uom_code
4282 , p_uom_code
4283 , ''
4284 , '');
4285 END IF;
4286
4287 l_total_qty := l_total_qty + l_cur_qty;
4288 END LOOP;
4289 CLOSE c_txn_lines;
4290
4291 -- po section --
4292 ELSE IF (p_type = 'PO') THEN
4293 --print_debug('Inside get_inspection_qty PO...', 4);
4294
4295 OPEN c_po_source_lines;
4296 LOOP
4297 FETCH c_po_source_lines INTO l_rcv_transaction_id;
4298 EXIT WHEN c_po_source_lines%NOTFOUND;
4299 --print_debug('PO l_supply_source_id is ' || to_char(l_rcv_transaction_id), 4);
4300
4301 rcv_quantities_s.get_available_quantity(
4302 'INSPECT'
4303 ,l_rcv_transaction_id
4304 ,''
4305 ,''
4306 ,null
4307 ,''
4308 ,l_cur_qty
4309 ,l_tolerable_qty
4310 ,l_cur_uom_code);
4311
4312 -- they pass me unit of measure, now i get uom code
4313
4314 select uom_code
4315 into l_cur_uom_code
4316 from mtl_units_of_measure
4317 where unit_of_measure = l_cur_uom_code;
4318
4319 IF (l_cur_uom_code <> p_uom_code) THEN
4320 l_cur_qty := inv_convert.inv_um_convert(
4321 p_item_id
4322 , null
4323 , l_cur_qty
4324 , l_cur_uom_code
4325 , p_uom_code
4326 , ''
4327 ,'');
4328 END IF;
4329 l_total_qty := l_total_qty + l_cur_qty;
4330 END LOOP;
4331 CLOSE c_po_source_lines;
4332
4333 -- intransit or receipt section --
4334 ELSE IF (p_type in ('INTSHIP','RECEIPT')) THEN
4335 --print_debug('Inside get_inspection_qty RECEIPT...', 4);
4336
4337 OPEN c_intship_source_lines;
4338 LOOP
4339 FETCH c_intship_source_lines INTO l_rcv_transaction_id;
4340
4341 --print_debug('before exit RECEIPT l_rcv_transaction_id is ' || l_rcv_transaction_id, 4);
4342
4343 EXIT WHEN c_intship_source_lines%NOTFOUND;
4344
4345 --print_debug('after exit RECEIPT l_rcv_transaction_id is ' || l_rcv_transaction_id, 4);
4346
4347 rcv_quantities_s.get_available_quantity(
4348 'INSPECT'
4349 ,l_rcv_transaction_id
4350 ,''
4351 ,''
4352 ,null
4353 ,''
4354 ,l_cur_qty
4355 ,l_tolerable_qty
4356 ,l_cur_uom_code);
4357
4358 -- they pass me unit of measure, now i get uom code
4359 select uom_code
4360 into l_cur_uom_code
4361 from mtl_units_of_measure
4362 where unit_of_measure = l_cur_uom_code;
4363
4364 IF (l_cur_uom_code <> p_uom_code) THEN
4365 l_cur_qty := inv_convert.inv_um_convert(
4366 p_item_id
4367 , null
4368 , l_cur_qty
4369 , l_cur_uom_code
4370 , p_uom_code
4371 , ''
4372 , '');
4373 END IF;
4374 l_total_qty := l_total_qty + l_cur_qty;
4375 END LOOP;
4376 CLOSE c_intship_source_lines;
4377
4378 -- rma section --
4379 ELSE IF (p_type = 'RMA') THEN
4380 OPEN c_rma_source_lines;
4381 LOOP
4382 FETCH c_rma_source_lines INTO l_rcv_transaction_id;
4383 EXIT WHEN c_rma_source_lines%NOTFOUND;
4384
4385 rcv_quantities_s.get_available_quantity(
4386 'INSPECT'
4387 ,l_rcv_transaction_id
4388 ,''
4389 ,''
4390 ,null
4391 ,''
4392 ,l_cur_qty
4393 ,l_tolerable_qty
4394 ,l_cur_uom_code);
4395
4396 -- they pass me unit of measure, now i get uom code
4397 select uom_code
4398 into l_cur_uom_code
4399 from mtl_units_of_measure
4400 where unit_of_measure = l_cur_uom_code;
4401
4402 IF (l_cur_uom_code <> p_uom_code) THEN
4403 l_cur_qty := inv_convert.inv_um_convert(
4404 p_item_id
4405 , null
4406 , l_cur_qty
4407 , l_cur_uom_code
4408 , p_uom_code
4409 , ''
4410 , '');
4411 END IF;
4412 l_total_qty := l_total_qty + l_cur_qty;
4413 END LOOP;
4414 CLOSE c_rma_source_lines;
4415
4416 END IF;
4417 END IF;
4418 END IF;
4419 END IF;
4420
4421 x_inspection_qty := l_total_qty;
4422 x_return_status := fnd_api.g_ret_sts_success;
4423
4424 return x_inspection_qty;
4425
4426 EXCEPTION
4427 when fnd_api.g_exc_error THEN
4428 x_return_status := fnd_api.g_ret_sts_error;
4429 -- Get message count and data
4430 fnd_msg_pub.count_and_get ( p_count => l_msg_count , p_data => x_msg_data);
4431 print_debug('***Execution error occured***', 4);
4432 return 0; --Bug #3908752
4433 when fnd_api.g_exc_unexpected_error THEN
4434 x_return_status := fnd_api.g_ret_sts_unexp_error;
4435 -- Get message count and data
4436 fnd_msg_pub.count_and_get ( p_count => l_msg_count , p_data => x_msg_data);
4437 print_debug('***Unexpected error occured***', 4);
4438 return 0;
4439 when others THEN
4440 x_return_status := fnd_api.g_ret_sts_unexp_error ;
4441 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
4442 THEN
4443 fnd_msg_pub.add_exc_msg ( g_pkg_name, 'get_inspection_qty');
4444 END IF;
4445 -- Get message count and data
4446 fnd_msg_pub.count_and_get ( p_count => l_msg_count, p_data => x_msg_data);
4447 IF (c_txn_lines%isopen) THEN
4448 CLOSE c_txn_lines;
4449 END IF;
4450 print_debug('***Error occured while getting Inspection Qty : ' || sqlerrm || ' ***' , 4);
4451 return 0;
4452 END get_inspection_qty;
4453
4454 -------------------------------------------------------------
4455 --------- wrapper function
4456 -------------------------------------------------------------
4457 FUNCTION get_inspection_qty_wrapper(
4458 p_type IN VARCHAR2
4459 , p_id1 IN NUMBER := NULL
4460 , p_id2 IN NUMBER := NULL
4461 , p_id3 IN NUMBER := NULL
4462 , p_organization_id IN NUMBER
4463 , p_item_id IN NUMBER
4464 , p_uom_code IN VARCHAR2) RETURN NUMBER
4465 IS
4466 l_inspection_qty NUMBER;
4467 l_return_status VARCHAR2(10);
4468 l_msg_data VARCHAR2(5000);
4469
4470 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
4471 BEGIN
4472 --print_debug('Inside wrapper, p_type is ' || p_type, 4);
4473 --print_debug('Inside wrapper, p_id1 is ' || p_id1, 4);
4474 --print_debug('Inside wrapper, p_org is ' || to_char(p_organization_id), 4);
4475 --print_debug('Inside wrapper, p_item is ' || to_char(p_item_id), 4);
4476 --print_debug('Inside wrapper,p_uom is ' || p_uom_code, 4);
4477
4478 IF (p_type = 'LPN') THEN
4479 --print_debug('Inside LPN...', 4);
4480
4481 return inv_rcv_std_inspect_apis.get_inspection_qty(
4482 p_type => p_type
4483 , p_lpn_id => p_id1
4484 , p_po_header_id => NULL
4485 , p_po_release_id => NULL
4486 , p_po_line_id => NULL
4487 , p_shipment_header_id => NULL
4488 , p_oe_order_header_id => NULL
4489 , p_organization_id => p_organization_id
4490 , p_item_id => p_item_id
4491 , p_uom_code => p_uom_code
4492 , x_inspection_qty => l_inspection_qty
4493 , x_return_status => l_return_status
4494 , x_msg_data => l_msg_data);
4495
4496 ELSE IF (p_type = 'RMA') THEN
4497 --print_debug('Inside RMA...', 4);
4498
4499 return inv_rcv_std_inspect_apis.get_inspection_qty(
4500 p_type => p_type
4501 , p_lpn_id => NULL
4502 , p_po_header_id => NULL
4503 , p_po_release_id => NULL
4504 , p_po_line_id => NULL
4505 , p_shipment_header_id => NULL
4506 , p_oe_order_header_id => p_id1
4507 , p_organization_id => p_organization_id
4508 , p_item_id => p_item_id
4509 , p_uom_code => p_uom_code
4510 , x_inspection_qty => l_inspection_qty
4511 , x_return_status => l_return_status
4512 , x_msg_data => l_msg_data);
4513
4514 ELSE IF (p_type in ('INTSHIP', 'RECEIPT')) THEN
4515 --print_debug('Inside Intship/Receipt...', 4);
4516
4517 --print_debug('p_shipment_header_id=p_id1 is ' || to_char(p_id1), 4);
4518
4519 return inv_rcv_std_inspect_apis.get_inspection_qty(
4520 p_type => p_type
4521 , p_lpn_id => NULL
4522 , p_po_header_id => NULL
4523 , p_po_release_id => NULL
4524 , p_po_line_id => NULL
4525 , p_shipment_header_id => p_id1
4526 , p_oe_order_header_id => NULL
4527 , p_organization_id => p_organization_id
4528 , p_item_id => p_item_id
4529 , p_uom_code => p_uom_code
4530 , x_inspection_qty => l_inspection_qty
4531 , x_return_status => l_return_status
4532 , x_msg_data => l_msg_data);
4533
4534 ELSE IF (p_type = 'PO') THEN
4535 --print_debug('Inside PO...', 4);
4536
4537 return inv_rcv_std_inspect_apis.get_inspection_qty(
4538 p_type => p_type
4539 , p_lpn_id => NULL
4540 , p_po_header_id => p_id1
4541 , p_po_release_id => p_id2
4542 , p_po_line_id => p_id3
4543 , p_shipment_header_id => NULL
4544 , p_oe_order_header_id => NULL
4545 , p_organization_id => p_organization_id
4546 , p_item_id => p_item_id
4547 , p_uom_code => p_uom_code
4548 , x_inspection_qty => l_inspection_qty
4549 , x_return_status => l_return_status
4550 , x_msg_data => l_msg_data);
4551 END IF;
4552 END IF;
4553 END IF;
4554 END IF;
4555
4556 END get_inspection_qty_wrapper;
4557
4558
4559
4560
4561
4562
4563
4564
4565 -- given a particular lpn id, organization, and item, this method will return
4566 -- the po associated with that item. if there are multiple po's associated with
4567 -- that restriction criteria, then it will return a status of 1.
4568 -- if successful, then a status of 0.
4569 PROCEDURE obtain_receiving_information(
4570 p_lpn_id IN NUMBER
4571 , p_organization_id IN NUMBER
4572 , p_inventory_item_id IN NUMBER
4573 , x_po_id OUT NOCOPY VARCHAR2
4574 , x_po_number OUT NOCOPY VARCHAR2
4575 , x_po_return_status OUT NOCOPY VARCHAR2
4576 , x_vendor_id OUT NOCOPY VARCHAR2
4577 , x_vendor_name OUT NOCOPY VARCHAR2
4578 , x_asl_status_id OUT NOCOPY VARCHAR2
4579 , x_asl_status_dsp OUT NOCOPY VARCHAR2
4580 , x_rma_id OUT NOCOPY VARCHAR2
4581 , x_rma_number OUT NOCOPY VARCHAR2
4582 , x_rma_return_status OUT NOCOPY VARCHAR2
4583 , x_customer_id OUT NOCOPY VARCHAR2
4584 , x_customer_number OUT NOCOPY VARCHAR2
4585 , x_customer_name OUT NOCOPY VARCHAR2
4586 , x_intshp_id OUT NOCOPY VARCHAR2
4587 , x_intshp_number OUT NOCOPY VARCHAR2
4588 , x_intshp_return_status OUT NOCOPY VARCHAR2
4589 , x_receipt_number OUT NOCOPY VARCHAR2
4590 , x_receipt_return_status OUT NOCOPY VARCHAR2
4591 , x_msg_count OUT NOCOPY VARCHAR2
4592 , x_msg_data OUT NOCOPY VARCHAR2)
4593 IS
4594 v_count_po NUMBER;
4595 v_count_rma NUMBER;
4596 v_count_intshp NUMBER;
4597 v_po_line_id NUMBER;
4598
4599 l_progress VARCHAR2(30);
4600 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
4601 BEGIN
4602
4603 IF (l_debug = 1) THEN
4604 print_debug('begin obtain_receiving_info in db', 4);
4605 print_debug('passed in lpn, org, item' || p_lpn_id || ':' || p_organization_id || ':' || p_inventory_item_id, 4);
4606 END IF;
4607 l_progress := '0';
4608
4609 --BUG 3444196: Modify the following query to avoid the
4610 --'Non-mergable view exists for the following SQL' complaints
4611 SELECT COUNT(DISTINCT pha.po_header_id)
4612 INTO v_count_po
4613 FROM mtl_txn_request_lines mtrl, po_line_locations_all plla, po_headers_all pha
4614 WHERE reference = 'PO_LINE_LOCATION_ID'
4615 AND mtrl.reference_id = plla.line_location_id
4616 AND plla.po_header_id = pha.po_header_id
4617 AND mtrl.quantity > nvl(mtrl.quantity_delivered, 0)
4618 AND mtrl.lpn_id = p_lpn_id
4619 AND mtrl.organization_id = p_organization_id
4620 AND mtrl.inventory_item_id = p_inventory_item_id ;
4621
4622 --dbms_output.put_line('vcountpo=' || v_count_po);
4623 l_progress := '10';
4624 IF (l_debug = 1) THEN
4625 print_debug('obtain_receiving_info:progress=' || l_progress, 4);
4626 END IF;
4627
4628 --if only 1 line is returned by the above query, then we need to retrieve multiple values
4629 -- for that line and set the status to 0, representing success
4630 if (v_count_po = 1) then
4631 x_po_return_status := 0;
4632 x_msg_count := ' ';
4633 x_msg_data := ' ';
4634
4635 select distinct pha.po_header_id, pha.segment1, pv.vendor_id, pv.vendor_name, plla.po_line_id
4636 into x_po_id, x_po_number, x_vendor_id, x_vendor_name, v_po_line_id
4637 from mtl_txn_request_lines mtrl, po_line_locations_all plla, po_headers_all pha, po_vendors pv
4638 where reference = 'PO_LINE_LOCATION_ID'
4639 and mtrl.reference_id = plla.line_location_id
4640 and plla.po_header_id = pha.po_header_id
4641 and mtrl.quantity > nvl(mtrl.quantity_delivered, 0)
4642 and pha.vendor_id = pv.vendor_id
4643 and mtrl.lpn_id = p_lpn_id
4644 and mtrl.organization_id = p_organization_id
4645 and mtrl.inventory_item_id = p_inventory_item_id;
4646 l_progress := '20';
4647 IF (l_debug = 1) THEN
4648 print_debug('obtain_receiving_info:progress=' || l_progress, 4);
4649 END IF;
4650
4651
4652 ----dbms_output.put_line('x_po_id=' || x_po_id);
4653 --dbms_output.put_line('x_po_number=' || x_po_number);
4654 --dbms_output.put_line('x_vendor_id=' || x_vendor_id);
4655
4656
4657 begin
4658 -- get ASL -- query provided by jenny zheng from QA team
4659 SELECT pasv.asl_status_id, pasv.asl_status_dsp
4660 into x_asl_status_id, x_asl_status_dsp
4661 FROM po_asl_suppliers_v pasv, po_lines pl, po_headers ph
4662 WHERE pl.item_id = pasv.item_id
4663 AND pl.po_line_id = v_po_line_id -- here use the variable from above
4664 AND pl.po_header_id = ph.po_header_id
4665 AND ph.vendor_id(+) = pasv.vendor_id
4666 AND ph.vendor_site_id(+) = pasv.vendor_site_id
4667 AND (p_organization_id = pasv.using_organization_id
4668 OR pasv.using_organization_id = -1);
4669 l_progress := '30';
4670 IF (l_debug = 1) THEN
4671 print_debug('obtain_receiving_info:progress=' || l_progress, 4);
4672 END IF;
4673 exception
4674 when others then
4675 x_asl_status_id := ' ';
4676 x_asl_status_dsp := ' ';
4677 end;
4678
4679
4680 begin
4681 -- obtain receipt number
4682 select distinct rsh.receipt_num, '0'
4683 into x_receipt_number, x_receipt_return_status
4684 from mtl_txn_request_lines mtrl, rcv_transactions rt, rcv_shipment_headers rsh
4685 where reference = 'PO_LINE_LOCATION_ID'
4686 and mtrl.reference_id = rt.po_line_location_id
4687 and mtrl.quantity > nvl(mtrl.quantity_delivered, 0)
4688 and rt.shipment_header_id = rsh.shipment_header_id
4689 and mtrl.lpn_id = p_lpn_id
4690 and mtrl.lpn_id = rt.transfer_lpn_id --Bug#7390895
4691 and mtrl.organization_id = p_organization_id
4692 and mtrl.inventory_item_id = p_inventory_item_id;
4693 l_progress := '40';
4694 IF (l_debug = 1) THEN
4695 print_debug('obtain_receiving_info:progress=' || l_progress, 4);
4696 END IF;
4697 exception
4698 when others then
4699 x_receipt_number := ' ';
4700 x_receipt_return_status := ' ';
4701 end;
4702
4703
4704 else if (v_count_po = 0) then
4705 x_po_return_status := -1;
4706 x_msg_count := ' ';
4707 x_msg_data := 'NO PO LINES FOUND';
4708 --dbms_output.put_line('no po lines found');
4709
4710 else if (v_count_po > 1) then
4711 x_po_return_status := 1;
4712 x_msg_count := ' ';
4713 x_msg_data := 'MULTIPLE PO LINES FOUND';
4714 --dbms_output.put_line('many po lines found');
4715
4716 end if;
4717 end if;
4718 end if;
4719
4720
4721
4722
4723 --obtain RMA, CUSTOMER INFO
4724 --BUG 3444196: Modify the following query to avoid the
4725 --'Non-mergable view exists for the following SQL' complaints
4726 SELECT COUNT(DISTINCT oeh.header_id)
4727 INTO v_count_rma
4728 FROM mtl_txn_request_lines mtrl, oe_order_lines_all oel, oe_order_headers_all oeh
4729 WHERE reference = 'ORDER_LINE_ID'
4730 AND mtrl.reference_id = oel.line_id
4731 AND mtrl.quantity > nvl(mtrl.quantity_delivered, 0)
4732 AND oel.header_id = oeh.header_id
4733 AND mtrl.lpn_id = p_lpn_id
4734 AND mtrl.organization_id = p_organization_id
4735 AND mtrl.inventory_item_id = p_inventory_item_id;
4736
4737 l_progress := '50';
4738 IF (l_debug = 1) THEN
4739 print_debug('obtain_receiving_info:progress=' || l_progress, 4);
4740 END IF;
4741 if (v_count_rma = 1) then
4742 x_rma_return_status := 0;
4743 x_msg_count := ' ';
4744 x_msg_data := ' ';
4745
4746 select distinct oeh.header_id, oeh.order_number, oest.customer_id, oest.customer_number, oest.name
4747 into x_rma_id, x_rma_number, x_customer_id, x_customer_number, x_customer_name
4748 from mtl_txn_request_lines mtrl, oe_order_lines_all oel, oe_order_headers_all oeh, oe_sold_to_orgs_v oest
4749 where reference = 'ORDER_LINE_ID'
4750 and mtrl.reference_id = oel.line_id
4751 and mtrl.quantity > nvl(mtrl.quantity_delivered, 0)
4752 and oel.header_id = oeh.header_id
4753 and oeh.sold_to_org_id = oest.customer_id
4754 and mtrl.lpn_id = p_lpn_id
4755 and mtrl.organization_id = p_organization_id
4756 and mtrl.inventory_item_id = p_inventory_item_id;
4757 l_progress := '60';
4758 IF (l_debug = 1) THEN
4759 print_debug('obtain_receiving_info:progress=' || l_progress, 4);
4760 END IF;
4761
4762 begin
4763 --obtain the receipt number
4764 select distinct rsh.receipt_num, '0'
4765 into x_receipt_number, x_receipt_return_status
4766 from mtl_txn_request_lines mtrl, rcv_transactions rt, rcv_shipment_headers rsh
4767 where reference = 'ORDER_LINE_ID'
4768 and mtrl.reference_id = rt.oe_order_line_id
4769 and mtrl.quantity > nvl(mtrl.quantity_delivered, 0)
4770 and rt.shipment_header_id = rsh.shipment_header_id
4771 and mtrl.lpn_id = p_lpn_id
4772 and mtrl.organization_id = p_organization_id
4773 and mtrl.inventory_item_id = p_inventory_item_id;
4774 l_progress := '70';
4775 IF (l_debug = 1) THEN
4776 print_debug('obtain_receiving_info:progress=' || l_progress, 4);
4777 END IF;
4778 exception
4779 when others then
4780 x_receipt_number := ' ';
4781 x_receipt_return_status := ' ';
4782 end;
4783
4784
4785 else if (v_count_rma = 0) then
4786 x_rma_return_status := -1;
4787 x_msg_count := ' ';
4788 x_msg_data := x_msg_data || 'NO RMA LINES FOUND';
4789
4790 else if (v_count_rma > 1) then
4791 x_rma_return_status := 1;
4792 x_msg_count := ' ';
4793 x_msg_data := x_msg_data || 'MULTIPLE RMA LINES FOUND';
4794
4795 end if;
4796 end if;
4797 end if;
4798
4799
4800
4801 -- obtain SHIPMENT RECEIPT INFORMATION
4802 SELECT COUNT(DISTINCT rsl.shipment_header_id)
4803 INTO v_count_intshp
4804 FROM mtl_txn_request_lines mtrl, rcv_shipment_lines rsl
4805 WHERE reference = 'SHIPMENT_LINE_ID'
4806 AND mtrl.reference_id = rsl.shipment_line_id
4807 AND mtrl.quantity > nvl(mtrl.quantity_delivered, 0)
4808 AND mtrl.lpn_id = p_lpn_id
4809 AND mtrl.organization_id = p_organization_id
4810 AND mtrl.inventory_item_id = p_inventory_item_id;
4811
4812 l_progress := '80';
4813 IF (l_debug = 1) THEN
4814 print_debug('obtain_receiving_info:progress=' || l_progress, 4);
4815 END IF;
4816
4817 if (v_count_intshp = 1) then
4818 x_intshp_return_status := 0;
4819 x_msg_count := ' ';
4820 x_msg_data := ' ';
4821
4822 select distinct rsl.shipment_header_id, rsh.shipment_num, rsh.receipt_num
4823 into x_intshp_id, x_intshp_number, x_receipt_number
4824 from mtl_txn_request_lines mtrl, rcv_shipment_lines rsl, rcv_shipment_headers rsh
4825 where reference = 'SHIPMENT_LINE_ID'
4826 and mtrl.quantity > nvl(mtrl.quantity_delivered, 0)
4827 and mtrl.reference_id = rsl.shipment_line_id
4828 and rsl.shipment_header_id = rsh.shipment_header_id
4829 and mtrl.lpn_id = p_lpn_id
4830 and mtrl.organization_id = p_organization_id
4831 and mtrl.inventory_item_id = p_inventory_item_id;
4832 l_progress := '90';
4833 IF (l_debug = 1) THEN
4834 print_debug('obtain_receiving_info:progress=' || l_progress, 4);
4835 END IF;
4836
4837 else if (v_count_intshp = 0) then
4838 x_intshp_return_status := -1;
4839 x_msg_count := ' ';
4840 x_msg_data := x_msg_data || 'NO SHIPMENT RECEIPT LINES FOUND';
4841
4842 else if (v_count_intshp > 1) then
4843 x_intshp_return_status := 1;
4844 x_msg_count := ' ';
4845 x_msg_data := x_msg_data || 'MULTIPLE SHIPMENT RECEIPT LINES FOUND';
4846
4847 end if;
4848 end if;
4849 end if;
4850
4851 exception
4852 when others then
4853 IF SQLCODE IS NOT NULL THEN
4854 inv_mobile_helper_functions.sql_error('inv_rcv_std_inspect_apis.obtain_receiving_information', l_progress, SQLCODE);
4855 END IF;
4856
4857
4858
4859
4860 end obtain_receiving_information;
4861
4862
4863 ---------------- END OF SECTION ADDED BY MANU GUPTA --------------------
4864 ------------------------------------------------------------------------
4865
4866
4867 FUNCTION is_revision_required (
4868 p_source_type IN VARCHAR2
4869 , p_source_id IN NUMBER
4870 , p_item_id IN NUMBER
4871 ) RETURN NUMBER
4872 IS
4873 l_count NUMBER;
4874 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
4875 BEGIN
4876
4877 l_count := 1;
4878
4879 IF (p_source_type = 'PO') THEN
4880 BEGIN
4881 SELECT 1
4882 INTO l_count
4883 FROM rcv_supply rs
4884 , rcv_transactions rt
4885 WHERE rs.item_id = p_item_id
4886 AND rs.item_revision IS NULL
4887 AND rs.po_header_id = p_source_id
4888 AND rs.rcv_transaction_id = rt.transaction_id
4889 AND rt.inspection_status_code = 'NOT INSPECTED'
4890 AND rs.supply_type_code = 'RECEIVING'
4891 AND rt.transaction_type <> 'UNORDERED'
4892 AND ROWNUM < 2;
4893 EXCEPTION
4894 WHEN no_data_found THEN
4895 l_count := 0;
4896 END;
4897 ELSE IF (p_source_type IN ('INTSHIP', 'RECEIPT')) THEN
4898 BEGIN
4899 SELECT 1
4900 INTO l_count
4901 FROM rcv_supply rs
4902 , rcv_transactions rt
4903 WHERE rs.item_id = p_item_id
4904 AND rs.item_revision IS NULL
4905 AND rs.shipment_header_id = p_source_id
4906 AND rs.rcv_transaction_id = rt.transaction_id
4907 AND rt.inspection_status_code = 'NOT INSPECTED'
4908 AND rs.supply_type_code = 'RECEIVING'
4909 AND rt.transaction_type <> 'UNORDERED'
4910 AND ROWNUM < 2;
4911 EXCEPTION
4912 WHEN no_data_found THEN
4913 l_count := 0;
4914 END;
4915 ELSE IF (p_source_type = 'RMA') THEN
4916 BEGIN
4917 SELECT 1
4918 INTO l_count
4919 FROM rcv_supply rs
4920 , rcv_transactions rt
4921 WHERE rs.item_id = p_item_id
4922 AND rs.item_revision IS NULL
4923 AND rs.oe_order_header_id = p_source_id
4924 AND rs.rcv_transaction_id = rt.transaction_id
4925 AND rt.inspection_status_code = 'NOT INSPECTED'
4926 AND rs.supply_type_code = 'RECEIVING'
4927 AND rt.transaction_type <> 'UNORDERED'
4928 AND ROWNUM < 2;
4929 EXCEPTION
4930 WHEN no_data_found THEN
4931 l_count := 0;
4932 END;
4933 END IF;
4934 END IF;
4935 END IF;
4936
4937 RETURN l_count;
4938 END is_revision_required;
4939
4940
4941 end inv_rcv_std_inspect_apis;