[Home] [Help]
PACKAGE BODY: APPS.INV_RCV_STD_TRANSFER_APIS
Source
1 PACKAGE BODY INV_RCV_STD_TRANSFER_APIS AS
2 /* $Header: INVSTDTB.pls 120.11.12020000.5 2012/08/10 10:08:08 rdudani ship $ */
3
4 --Transaction UOM Code in RTI. Need this because the rcv_transactions
5 --record type does not store the uom_code
6 g_transfer_uom_code MTL_ITEM_UOMS_VIEW.UOM_CODE%TYPE;
7
8 g_pkg_name CONSTANT VARCHAR2(30) := 'INV_RCV_STD_TRANSFER_APIS';
9
10 /* Debugging utility*/
11 PROCEDURE print_debug (
12 p_err_msg VARCHAR2
13 , p_level NUMBER) IS
14 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'), 0);
15 BEGIN
16 IF (l_debug = 1) THEN
17 inv_mobile_helper_functions.tracelog (
18 p_err_msg => p_err_msg
19 , p_module => g_pkg_name||' ($Revision: 120.11.12020000.5 $)'
20 , p_level => p_level);
21 END IF;
22 --dbms_output.put_line(p_err_msg);
23 END print_debug;
24
25 /*----------------------------------------------------------------------------*
26 * FUNCTION: insert_interface_code
27 * Description:
28 * Helper routing to create a new record in RCV_TRANSACTIONS_INTERFACE
29 *
30 * Input Parameters:
31 * p_rcv_transactions_rec - Record containing the values for RTI
32 *
33 * Returns: NUMBER - transaction_interface_id
34 *---------------------------------------------------------------------------*/
35
36 FUNCTION insert_interface_code (
37 p_rcv_transaction_rec INV_RCV_STD_DELIVER_APIS.RCVTXN_TRANSACTION_REC_TP
38 )
39 RETURN NUMBER IS
40 l_interface_transaction_id NUMBER;
41 l_destination_context RCV_TRANSACTIONS_INTERFACE.DESTINATION_CONTEXT%TYPE := 'RECEIVING';
42 l_parent_transaction_id NUMBER := p_rcv_transaction_rec.rcv_transaction_id;
43 l_movement_id NUMBER;
44 l_bom_resource_id NUMBER := NULL;
45 l_group_id NUMBER := inv_rcv_common_apis.g_rcv_global_var.interface_group_id;
46 l_user_id NUMBER := inv_rcv_common_apis.g_po_startup_value.user_id;
47 l_employee_id NUMBER := inv_rcv_common_apis.g_po_startup_value.employee_id;
48 l_logon_id NUMBER := inv_rcv_common_apis.g_po_startup_value.logon_id;
49 l_transaction_mode VARCHAR2(10) := inv_rcv_common_apis.g_po_startup_value.transaction_mode;
50 l_trx_date DATE;
51 l_currency_conv_date DATE;
52 l_validation_flag VARCHAR2(1);
53 l_lpn_group_id NUMBER;
54 l_progress NUMBER; --Index to track progress and log error
55 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'), 0);
56 l_project_id NUMBER := NULL;
57 l_task_id NUMBER := NULL;
58
59 l_operating_unit_id MO_GLOB_ORG_ACCESS_TMP.ORGANIZATION_ID%TYPE; --<R12 MOAC>
60
61 BEGIN
62
63 print_debug('Entered insert_interface_code 10 : ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 1);
64 print_debug(' parent_transaction_id => '||p_rcv_transaction_rec.rcv_transaction_id,1);
65
66 --Generate a new value for RTID from the sequence
67 SELECT rcv_transactions_interface_s.NEXTVAL
68 INTO l_interface_transaction_id
69 FROM sys.DUAL;
70
71 l_parent_transaction_id := p_rcv_transaction_rec.rcv_transaction_id;
72
73 BEGIN
74 --R12: We can no longer join to MTRL.TXN_SOURCE_ID.
75 --Using RT.PROJECT_ID and RT.TASK_ID should be sufficient
76 SELECT rt.movement_id
77 , rt.project_id
78 , rt.task_id
79 INTO l_movement_id
80 , l_project_id
81 , l_task_id
82 FROM rcv_transactions rt
83 WHERE rt.transaction_id = l_parent_transaction_id
84 AND ROWNUM < 2;
85 EXCEPTION
86 WHEN OTHERS THEN
87 print_debug('Error while retrieving project and task. SQLCODE:'||SQLCODE||' SQLERRM:'||Sqlerrm,1);
88 RAISE FND_API.G_EXC_ERROR;
89 END ;
90
91
92 --Truncate the transaction_date to store only the date part
93 l_trx_date := TRUNC(p_rcv_transaction_rec.transaction_date_nb);
94 -- bug 3452845
95 IF ((inv_rcv_common_apis.g_inv_patch_level >= inv_rcv_common_apis.g_patchset_j) AND
96 (inv_rcv_common_apis.g_po_patch_level >= inv_rcv_common_apis.g_patchset_j_po)) THEN
97 l_trx_date := Sysdate;
98 ELSE
99 l_trx_date := Trunc(Sysdate);
100 END IF;
101
102 l_currency_conv_date := TRUNC(p_rcv_transaction_rec.currency_conversion_date);
103
104 --Populate the LPN_GROUP_ID and validation_flag columns in RTI
105 l_validation_flag := 'Y';
106 l_lpn_group_id := l_group_id;
107
108 --<R12 MOAC>
109 l_operating_unit_id := inv_rcv_common_apis.get_operating_unit_id( p_rcv_transaction_rec.receipt_source_code,
110 p_rcv_transaction_rec.po_header_id,
111 p_rcv_transaction_rec.req_line_id,
112 p_rcv_transaction_rec.oe_order_header_id );
113
114 IF (l_debug = 1) THEN
115 print_debug(' parent txn: ' || l_parent_transaction_id,4);
116 print_debug(' item: ' || p_rcv_transaction_rec.item_id,4);
117 print_debug(' org: ' || p_rcv_transaction_rec.to_organization_id,4);
118 print_debug(' txn tp: ' || p_rcv_transaction_rec.transaction_type,4);
119 print_debug(' src doc: ' || p_rcv_transaction_rec.source_document_code,4);
120 print_debug(' dest: ' || p_rcv_transaction_rec.destination_type_code,4);
121 print_debug(' po hdr: ' || p_rcv_transaction_rec.po_header_id,4);
122 print_debug(' vnd id: ' || p_rcv_transaction_rec.vendor_id,4);
123 print_debug(' po line: ' || p_rcv_transaction_rec.po_line_id,4);
124 print_debug(' po loc: ' || p_rcv_transaction_rec.po_line_location_id,4);
125 print_debug(' shp hdr: ' || p_rcv_transaction_rec.shipment_header_id,4);
126 print_debug(' shp line: ' || p_rcv_transaction_rec.shipment_line_id,4);
127 print_debug(' req hdr: ' || p_rcv_transaction_rec.req_header_id,4);
128 print_debug(' req line: ' || p_rcv_transaction_rec.req_line_id,4);
129 print_debug(' oe hdr: ' || p_rcv_transaction_rec.oe_order_header_id,4);
130 print_debug(' oe line: ' || p_rcv_transaction_rec.oe_order_line_id,4);
131 print_debug(' txn qty: ' || p_rcv_transaction_rec.transaction_quantity,4);
132 print_debug(' txn uom: ' || p_rcv_transaction_rec.transaction_uom,4);
133 print_debug(' prm qty: ' || p_rcv_transaction_rec.primary_quantity,4);
134 print_debug(' prm uom: ' || p_rcv_transaction_rec.primary_uom,4);
135 print_debug(' sec qty: ' || p_rcv_transaction_rec.sec_transaction_quantity,4);
136 print_debug(' sec uom: ' || p_rcv_transaction_rec.secondary_uom ,4);
137 print_debug(' sec uom code: ' || p_rcv_transaction_rec.secondary_uom_code,4);
138 print_debug(' gov ctx: ' || p_rcv_transaction_rec.government_context,4);
139 print_debug(' cur code: ' || p_rcv_transaction_rec.currency_code,4);
140 print_debug(' cur type: ' || p_rcv_transaction_rec.currency_conversion_type,4);
141 print_debug(' cur rate: ' || p_rcv_transaction_rec.currency_conversion_rate,4);
142 print_debug(' lpn: ' || p_rcv_transaction_rec.lpn_id,4);
143 print_debug(' xfr lpn: ' || p_rcv_transaction_rec.transfer_lpn_id,4);
144 print_debug(' locn: ' || p_rcv_transaction_rec.location_id,4);
145 print_debug(' sub: ' || p_rcv_transaction_rec.subinventory_dsp,4);
146 print_debug(' loc: ' || p_rcv_transaction_rec.locator_id,4);
147 print_debug(' grp id: ' || l_group_id, 4);
148 print_debug(' usr id: ' || l_user_id, 4);
149 print_debug(' emp id: ' || l_employee_id, 4);
150 print_debug(' trx dt: ' || l_trx_date, 4);
151 print_debug(' vldn_flg: ' || l_validation_flag, 4);
152 print_debug(' lpn_group_id: ' || l_lpn_group_id, 4);
153 print_debug(' project_id: ' || l_project_id, 4);
154 print_debug(' task_id: ' || l_task_id, 4);
155 print_debug(' l_operating_unit_id: ' || l_operating_unit_id, 4); --<R12 MOAC>
156 END IF;
157
158 BEGIN
159 --At last, now insert the record into RTI
160 INSERT INTO rcv_transactions_interface
161 (
162 receipt_source_code
163 , interface_transaction_id
164 , group_id
165 , created_by
166 , creation_date
167 , last_updated_by
168 , last_update_date
169 , last_update_login
170 , interface_source_code
171 , source_document_code
172 , destination_type_code
173 , transaction_date
174 , quantity
175 , unit_of_measure
176 , shipment_header_id
177 , shipment_line_id
178 , substitute_unordered_code
179 , employee_id
180 , parent_transaction_id
181 , inspection_status_code
182 , inspection_quality_code
183 , po_header_id
184 , po_release_id
185 , po_line_id
186 , po_line_location_id
187 , po_distribution_id
188 , po_revision_num
189 , po_unit_price
190 , currency_code
191 , currency_conversion_rate
192 , requisition_line_id
193 , req_distribution_id
194 , routing_header_id
195 , routing_step_id
196 , packing_slip
197 , vendor_item_num
198 , comments
199 , attribute_category
200 , attribute1
201 , attribute2
202 , attribute3
203 , attribute4
204 , attribute5
205 , attribute6
206 , attribute7
207 , attribute8
208 , attribute9
209 , attribute10
210 , attribute11
211 , attribute12
212 , attribute13
213 , attribute14
214 , attribute15
215 , transaction_type
216 , location_id
217 , processing_status_code
218 , processing_mode_code
219 , transaction_status_code
220 , category_id
221 , vendor_lot_num
222 , reason_id
223 , primary_quantity
224 , primary_unit_of_measure
225 , uom_code
226 -- OPM COnvergence
227 , SECONDARY_QUANTITY
228 , SECONDARY_UNIT_OF_MEASURE
229 , SECONDARY_UOM_CODE
230 , item_id
231 , item_revision
232 , to_organization_id
233 , deliver_to_location_id
234 , destination_context
235 , vendor_id
236 , deliver_to_person_id
237 , subinventory
238 , locator_id
239 , wip_entity_id
240 , wip_line_id
241 , wip_repetitive_schedule_id
242 , wip_operation_seq_num
243 , wip_resource_seq_num
244 , bom_resource_id
245 , use_mtl_lot
246 , use_mtl_serial
247 , movement_id
248 , currency_conversion_date
249 , currency_conversion_type
250 , qa_collection_id
251 , ussgl_transaction_code
252 , government_context
253 , vendor_site_id
254 , oe_order_header_id
255 , oe_order_line_id
256 , customer_id
257 , customer_site_id
258 , put_away_rule_id
259 , put_away_strategy_id
260 , lpn_id
261 , transfer_lpn_id
262 , cost_group_id
263 , mmtt_temp_id
264 , mobile_txn
265 , transfer_cost_group_id
266 , validation_flag
267 , lpn_group_id
268 , project_id
269 , task_id
270 , org_id --<R12 MOAC>
271 , from_subinventory
272 , from_locator_id
273 )
274 VALUES (
275 p_rcv_transaction_rec.receipt_source_code --receipt source code
276 , l_interface_transaction_id --interface txn id
277 , l_group_id --group id
278 , l_user_id --created_by
279 , SYSDATE --creation_date
280 , l_user_id --last_updated_by
281 , SYSDATE --last_update_date
282 , l_logon_id --last_update_login
283 , 'RCV' --interface_source_code
284 , p_rcv_transaction_rec.source_document_code --source_document_code
285 , p_rcv_transaction_rec.destination_type_code --destination_type_code
286 , l_trx_date --transaction_date
287 , p_rcv_transaction_rec.transaction_quantity --quantity
288 , p_rcv_transaction_rec.transaction_uom --unit_of_measure
289 , p_rcv_transaction_rec.shipment_header_id --shipment_header_id
290 , p_rcv_transaction_rec.shipment_line_id --shipment_line_id
291 , p_rcv_transaction_rec.substitute_unordered_code --substitute_unordered_code
292 , l_employee_id --employee_id
293 , l_parent_transaction_id --parent_transaction_id
294 , p_rcv_transaction_rec.inspection_status_code --inspection_status_code
295 , p_rcv_transaction_rec.inspection_quality_code --inspection_quality_code
296 , p_rcv_transaction_rec.po_header_id --po_header_id
297 , p_rcv_transaction_rec.po_release_id --po_release_id
298 , p_rcv_transaction_rec.po_line_id --po_line_id
299 , p_rcv_transaction_rec.po_line_location_id --po_line_location_id
300 , p_rcv_transaction_rec.po_distribution_id --po_distribution_id
301 , p_rcv_transaction_rec.po_revision_num --po_revision_num
302 , p_rcv_transaction_rec.po_unit_price --po_unit_price
303 , p_rcv_transaction_rec.currency_code --currency_code
304 , p_rcv_transaction_rec.currency_conversion_rate --currency_conversion_rate
305 , p_rcv_transaction_rec.req_line_id --requsition_line_id
306 , p_rcv_transaction_rec.req_distribution_id --req_distribution_id
307 , p_rcv_transaction_rec.routing_id --routing_header_id
308 , p_rcv_transaction_rec.routing_step_id --routing_step_id
309 , p_rcv_transaction_rec.packing_slip --packing_slip
310 , p_rcv_transaction_rec.vendor_item_number --vendor_item_num
311 , p_rcv_transaction_rec.comments --comments
312 , p_rcv_transaction_rec.attribute_category --attribute_category
313 , p_rcv_transaction_rec.attribute1 --attribute1
314 , p_rcv_transaction_rec.attribute2 --attribute2
315 , p_rcv_transaction_rec.attribute3 --attribute3
316 , p_rcv_transaction_rec.attribute4 --attribute4
317 , p_rcv_transaction_rec.attribute5 --attribute5
318 , p_rcv_transaction_rec.attribute6 --attribute6
319 , p_rcv_transaction_rec.attribute7 --attribute7
320 , p_rcv_transaction_rec.attribute8 --attribute8
321 , p_rcv_transaction_rec.attribute9 --attribute9
322 , p_rcv_transaction_rec.attribute10 --attribute10
323 , p_rcv_transaction_rec.attribute11 --attribute11
324 , p_rcv_transaction_rec.attribute12 --attribute12
325 , p_rcv_transaction_rec.attribute13 --attribute13
326 , p_rcv_transaction_rec.attribute14 --attribute14
327 , p_rcv_transaction_rec.attribute15 --attribute15
328 , p_rcv_transaction_rec.transaction_type --transaction_type
329 , p_rcv_transaction_rec.location_id --location_id
330 , 'PENDING' --processing_status_code
331 , l_transaction_mode --processing_mode_code
332 , 'PENDING' --transaction_status_code
333 , p_rcv_transaction_rec.category_id --category_id
334 , p_rcv_transaction_rec.vendor_lot_num --vendor_lot_num
335 , p_rcv_transaction_rec.reason_id --reason_id
336 , p_rcv_transaction_rec.primary_quantity --primary_quantity
337 , p_rcv_transaction_rec.primary_uom --primary_unit_of_measure
338 , g_transfer_uom_code --uom_code
339 -- OPM COnvergence
340 , DECODE(p_rcv_transaction_rec.sec_transaction_quantity, FND_API.G_MISS_NUM, NULL)
341 , p_rcv_transaction_rec.secondary_uom
342 , p_rcv_transaction_rec.secondary_uom_code
343 , p_rcv_transaction_rec.item_id --item_id
344 , p_rcv_transaction_rec.item_revision --item_revision
345 , p_rcv_transaction_rec.to_organization_id --to_organization_id
346 , p_rcv_transaction_rec.deliver_to_location_id --deliver_to_location_id
347 , l_destination_context --destination_context
348 , p_rcv_transaction_rec.vendor_id --vendor_id
349 , p_rcv_transaction_rec.deliver_to_person_id --deliver_to_persion_id
350 , p_rcv_transaction_rec.subinventory_dsp --subinventory
351 , p_rcv_transaction_rec.locator_id --locator_id
352 , p_rcv_transaction_rec.wip_entity_id --wip_entity_id
353 , p_rcv_transaction_rec.wip_line_id --wip_line_id
354 , p_rcv_transaction_rec.wip_repetitive_schedule_id --wip_repetitive_schedule_id
355 , p_rcv_transaction_rec.wip_operation_seq_num --wip_operation_seq_num
356 , p_rcv_transaction_rec.wip_resource_seq_num --wip_resource_seq_num
357 , l_bom_resource_id --bom_resource_id
358 , p_rcv_transaction_rec.lot_control_code --use_mtl_lot
359 , p_rcv_transaction_rec.serial_number_control_code --use_mtl_serial
360 , l_movement_id --movement_id
361 , l_currency_conv_date --currency_conversion_date
362 , p_rcv_transaction_rec.currency_conversion_type --currency_conversion_type
363 , p_rcv_transaction_rec.qa_collection_id --qa_collection_id
364 , p_rcv_transaction_rec.ussgl_transaction_code --ussgl_transaction_date
365 , p_rcv_transaction_rec.government_context --government_context
366 , p_rcv_transaction_rec.vendor_site_id --vendor_site_id
367 , p_rcv_transaction_rec.oe_order_header_id --oe_order_header_id
368 , p_rcv_transaction_rec.oe_order_line_id --oe_order_line_id
369 , p_rcv_transaction_rec.customer_id --customer_id
370 , p_rcv_transaction_rec.customer_site_id --customer_site_id
371 , p_rcv_transaction_rec.put_away_rule_id --put_away_rule_id
372 , p_rcv_transaction_rec.put_away_strategy_id --put_away_strategy_id
373 , p_rcv_transaction_rec.lpn_id --lpn_id
374 , p_rcv_transaction_rec.transfer_lpn_id --transfer_lpn_id
375 , p_rcv_transaction_rec.cost_group_id --cost_group_id
376 , p_rcv_transaction_rec.mmtt_temp_id --mmtt_temp_id
377 , 'Y' --mobile_txn
378 , p_rcv_transaction_rec.transfer_cost_group_id --transfer_cost_group_id
379 , l_validation_flag --validation_flag
380 , l_lpn_group_id --lpn_group_id
381 , l_project_id --project_id
382 , l_task_id --task_id
383 , l_operating_unit_id --<R12 MOAC>
384 , p_rcv_transaction_rec.from_subinventory_code --for matching non-lpn materials
385 , p_rcv_transaction_rec.from_locator_id --for matching non-lpn materials
386 );
387 EXCEPTION
388 WHEN OTHERS THEN
389 print_debug('Error inserting into RTI. SQLCODE:'||SQLCODE||' SQLERRM:'||Sqlerrm,1);
390 RAISE FND_API.G_EXC_ERROR;
391 END;
392
393 IF (l_debug = 1) THEN
394 print_debug('insert_interface_code completed successfully. Generated RTID: ' || l_interface_transaction_id, 4);
395 END IF;
396
397 RETURN l_interface_transaction_id;
398
399 EXCEPTION
400 WHEN OTHERS THEN
401 IF SQLCODE IS NOT NULL THEN
402 inv_mobile_helper_functions.sql_error (
403 'INV_RCV_STD_TRANSFER_APIS.INSERT_INTERFACE_CODE', 10, SQLCODE);
404 END IF;
405 RETURN -9999;
406 END insert_interface_code;
407
408 /*----------------------------------------------------------------------------*
409 * PROCEDURE: populate_transfer_rti_values
410 * Description:
411 * Accept the parent transaction record and calculate other values for RTI
412 * Populate the RTI record corresponding to TRANSFER
413 * Call the helper routine to insert the RTI record
414 * Update the Lots and serial interface records with the RTI record created
415 *
416 * Output Parameters:
417 * x_return_status
418 * Return status indicating Success (S), Error (E), Unexpected Error (U)
419 * x_msg_count
420 * Number of messages in message list
421 * x_msg_data
422 * Stacked messages text
423 *
424 * Input Parameters:
425 * p_rcv_transaction_rec - Record type for RTI with quantities initialized
426 * p_rcvtxn_rec - Record type for the parent RCV Transaction
427 * p_parent_txn_id - Transaction ID of the parent transaction
428 * p_organization_id - Organization ID
429 * p_item_id - Item ID
430 * p_revision - Item Revision
431 * p_subinventory_code - Destination receiving subinventory code
432 * p_locator_id - Destination receiving locator ID
433 * p_lot_control_code - Lot Control Code of the item
434 * p_serial_control_code - Serial Control Code of the item
435 * p_original_rti_id - Interface Transaction Id for lot/serial split
436 * p_original_temp_id - Transaction Temp ID of the MMTT being putaway
437 * p_lpn_id - LPN ID of the move order line
438 * p_transfer_lpn_id - LPN ID of the LPN being dropped into
439 * p_doc_type - Document Type (PO/RMA/INTSHIP)
440 *
441 * Returns: NONE
442 *---------------------------------------------------------------------------*/
443 PROCEDURE populate_transfer_rti_values (
444 x_return_status OUT NOCOPY VARCHAR2
445 , x_msg_count OUT NOCOPY NUMBER
446 , x_msg_data OUT NOCOPY VARCHAR2
447 , p_rcv_transaction_rec IN OUT NOCOPY INV_RCV_STD_DELIVER_APIS.rcvtxn_transaction_rec_tp
448 , p_rcvtxn_rec IN OUT NOCOPY INV_RCV_STD_DELIVER_APIS.rcvtxn_enter_rec_cursor_rec
449 , p_parent_txn_id IN NUMBER
450 , p_organization_id IN NUMBER
451 , p_item_id IN NUMBER
452 , p_revision IN VARCHAR2
453 , p_subinventory_code IN VARCHAR2
454 , p_locator_id IN NUMBER
455 , p_lot_control_code IN NUMBER
456 , p_serial_control_code IN NUMBER
457 , p_original_rti_id IN NUMBER DEFAULT NULL
458 , p_original_temp_id IN NUMBER DEFAULT NULL
459 , p_lpn_id IN NUMBER DEFAULT NULL
460 , p_transfer_lpn_id IN NUMBER DEFAULT NULL
461 , p_doc_type IN VARCHAR2 DEFAULT NULL ) IS
462
463 --Local variables
464 l_content_lpn_id NUMBER; --Content LPN ID
465 l_lpn_controlled_flag NUMBER;
466 l_interface_txn_id NUMBER;
467 l_location_id NUMBER;
468 l_msni_count NUMBER := 0;
469 l_progress NUMBER; --Index to track progress and log error
470 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'), 0);
471 BEGIN
472
473 --Initialize the return status to success
474 x_return_status := FND_API.G_RET_STS_SUCCESS;
475
476 l_progress := 10;
477
478 IF (l_debug = 1) THEN
479 print_debug('***Entered populate_transfer_rti_values***: ' || l_progress || ' '
480 || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 4);
481 END IF;
482
483 --First fetch the values for RTI from the original MMTT record
484 p_rcv_transaction_rec.mmtt_temp_id := p_original_temp_id;
485
486 BEGIN
487 SELECT cost_group_id
488 , put_away_rule_id
489 , put_away_strategy_id
490 INTO p_rcv_transaction_rec.cost_group_id
491 , p_rcv_transaction_rec.put_away_rule_id
492 , p_rcv_transaction_rec.put_away_strategy_id
493 FROM mtl_material_transactions_temp
494 WHERE transaction_temp_id = p_original_temp_id;
495 EXCEPTION
496 WHEN OTHERS THEN
497 IF (l_debug = 1) THEN
498 print_debug('Could not fetch original MMTT info. Maybe not passed', 4);
499 END IF;
500 END;
501
502 l_progress := 20;
503
504 BEGIN
505 SELECT cost_group_id
506 INTO p_rcv_transaction_rec.transfer_cost_group_id
507 FROM rcv_shipment_lines
508 WHERE shipment_line_id = p_rcvtxn_rec.shipment_line_id;
509 EXCEPTION
510 WHEN OTHERS THEN
511 p_rcv_transaction_rec.transfer_cost_group_id := NULL;
512 END;
513
514 IF (l_debug = 1) THEN
515 print_debug('***Columns fetched from the original MMTT record***', 4);
516 print_debug(' Orignal Temp ID: ' || p_rcv_transaction_rec.mmtt_temp_id, 4);
517 print_debug(' cost_group_id: ' || p_rcv_transaction_rec.cost_group_id, 4);
518 print_debug(' transfer_cost_group_id: ' || p_rcv_transaction_rec.transfer_cost_group_id, 4);
519 print_debug(' put_away_rule_id: ' || p_rcv_transaction_rec.put_away_rule_id, 4);
520 print_debug(' put_away_strategy_id: ' || p_rcv_transaction_rec.put_away_strategy_id, 4);
521 print_debug(' from_subinventory_code : ' || p_rcvtxn_rec.from_subinventory_code, 4);
522 print_debug(' from_locator_id : ' || p_rcvtxn_rec.from_locator_id, 4);
523 END IF;
524
525 --Now populate the columns from the parent transaction record
526 l_progress := 30;
527
528 IF (l_debug = 1) THEN
529 print_debug('Defaulting columns from the parent (p_rcvtxn_rec) record', 4);
530 END IF;
531
532 p_rcv_transaction_rec.source_document_code := p_rcvtxn_rec.source_document_code;
533 p_rcv_transaction_rec.receipt_source_code := p_rcvtxn_rec.receipt_source_code;
534 p_rcv_transaction_rec.rcv_transaction_id := p_rcvtxn_rec.rcv_transaction_id;
535 p_rcv_transaction_rec.transaction_date := p_rcvtxn_rec.transaction_date;
536 p_rcv_transaction_rec.po_header_id := p_rcvtxn_rec.po_header_id;
537 p_rcv_transaction_rec.po_revision_num := p_rcvtxn_rec.po_revision_num;
538 p_rcv_transaction_rec.po_release_id := p_rcvtxn_rec.po_release_id;
539 p_rcv_transaction_rec.vendor_id := p_rcvtxn_rec.vendor_id;
540 p_rcv_transaction_rec.vendor_site_id := p_rcvtxn_rec.vendor_site_id;
541 p_rcv_transaction_rec.po_line_id := p_rcvtxn_rec.po_line_id;
542 p_rcv_transaction_rec.po_line_location_id := p_rcvtxn_rec.po_line_location_id;
543 p_rcv_transaction_rec.po_unit_price := p_rcvtxn_rec.po_unit_price;
544 p_rcv_transaction_rec.category_id := p_rcvtxn_rec.category_id;
545 p_rcv_transaction_rec.employee_id := p_rcvtxn_rec.employee_id;
546 p_rcv_transaction_rec.comments := p_rcvtxn_rec.comments;
547 p_rcv_transaction_rec.req_header_id := p_rcvtxn_rec.req_header_id;
548 p_rcv_transaction_rec.req_line_id := p_rcvtxn_rec.req_line_id;
549 p_rcv_transaction_rec.shipment_header_id := p_rcvtxn_rec.shipment_header_id;
550 p_rcv_transaction_rec.shipment_line_id := p_rcvtxn_rec.shipment_line_id;
551 p_rcv_transaction_rec.packing_slip := p_rcvtxn_rec.packing_slip;
552 p_rcv_transaction_rec.government_context := p_rcvtxn_rec.government_context;
553 p_rcv_transaction_rec.ussgl_transaction_code := p_rcvtxn_rec.ussgl_transaction_code;
554 p_rcv_transaction_rec.inspection_status_code := p_rcvtxn_rec.inspection_status_code;
555 p_rcv_transaction_rec.inspection_quality_code := p_rcvtxn_rec.inspection_quality_code;
556 p_rcv_transaction_rec.vendor_lot_num := p_rcvtxn_rec.vendor_lot_num;
557 p_rcv_transaction_rec.vendor_item_number := p_rcvtxn_rec.vendor_item_number;
558 p_rcv_transaction_rec.substitute_unordered_code := p_rcvtxn_rec.substitute_unordered_code;
559 p_rcv_transaction_rec.routing_id := p_rcvtxn_rec.routing_id;
560 p_rcv_transaction_rec.routing_step_id := p_rcvtxn_rec.routing_step_id;
561 p_rcv_transaction_rec.reason_id := p_rcvtxn_rec.reason_id;
562 p_rcv_transaction_rec.currency_code := p_rcvtxn_rec.currency_code;
563 p_rcv_transaction_rec.currency_conversion_rate := p_rcvtxn_rec.currency_conversion_rate;
564 p_rcv_transaction_rec.currency_conversion_date := p_rcvtxn_rec.currency_conversion_date;
565 p_rcv_transaction_rec.currency_conversion_type := p_rcvtxn_rec.currency_conversion_type;
566 p_rcv_transaction_rec.req_distribution_id := p_rcvtxn_rec.req_distribution_id;
567 p_rcv_transaction_rec.destination_type_code_hold := p_rcvtxn_rec.destination_type_code_hold;
568 p_rcv_transaction_rec.un_number_id := p_rcvtxn_rec.un_number_id;
569 p_rcv_transaction_rec.hazard_class_id := p_rcvtxn_rec.hazard_class_id;
570 p_rcv_transaction_rec.creation_date := p_rcvtxn_rec.creation_date;
571 p_rcv_transaction_rec.attribute_category := p_rcvtxn_rec.attribute_category;
572 p_rcv_transaction_rec.attribute1 := p_rcvtxn_rec.attribute1;
573 p_rcv_transaction_rec.attribute2 := p_rcvtxn_rec.attribute2;
574 p_rcv_transaction_rec.attribute3 := p_rcvtxn_rec.attribute3;
575 p_rcv_transaction_rec.attribute4 := p_rcvtxn_rec.attribute4;
576 p_rcv_transaction_rec.attribute5 := p_rcvtxn_rec.attribute5;
577 p_rcv_transaction_rec.attribute6 := p_rcvtxn_rec.attribute6;
578 p_rcv_transaction_rec.attribute7 := p_rcvtxn_rec.attribute7;
579 p_rcv_transaction_rec.attribute8 := p_rcvtxn_rec.attribute8;
580 p_rcv_transaction_rec.attribute9 := p_rcvtxn_rec.attribute9;
581 p_rcv_transaction_rec.attribute10 := p_rcvtxn_rec.attribute10;
582 p_rcv_transaction_rec.attribute11 := p_rcvtxn_rec.attribute11;
583 p_rcv_transaction_rec.attribute12 := p_rcvtxn_rec.attribute12;
584 p_rcv_transaction_rec.attribute13 := p_rcvtxn_rec.attribute13;
585 p_rcv_transaction_rec.attribute14 := p_rcvtxn_rec.attribute14;
586 p_rcv_transaction_rec.attribute15 := p_rcvtxn_rec.attribute15;
587 p_rcv_transaction_rec.qa_collection_id := p_rcvtxn_rec.qa_collection_id;
588 p_rcv_transaction_rec.oe_order_header_id := p_rcvtxn_rec.oe_order_header_id;
589 p_rcv_transaction_rec.oe_order_line_id := p_rcvtxn_rec.oe_order_line_id;
590 p_rcv_transaction_rec.customer_id := p_rcvtxn_rec.customer_id;
591 p_rcv_transaction_rec.customer_site_id := p_rcvtxn_rec.customer_site_id;
592 p_rcv_transaction_rec.from_subinventory_code := p_rcvtxn_rec.from_subinventory_code;
593 p_rcv_transaction_rec.from_locator_id := p_rcvtxn_rec.from_locator_id;
594
595 p_rcv_transaction_rec.po_distribution_id := NULL;
596
597 --Next, populate the columns from the input parameters
598 l_progress := 40;
599
600 IF (l_debug = 1) THEN
601 print_debug('Setting the location_id from the subinventory', 4);
602 END IF;
603 IF (p_subinventory_code IS NOT NULL) THEN
604 BEGIN
605 SELECT location_id
606 INTO l_location_id
607 FROM mtl_secondary_inventories
608 WHERE secondary_inventory_name = p_subinventory_code
609 AND organization_id = p_organization_id;
610 EXCEPTION
611 WHEN OTHERS THEN
612 l_location_id := p_rcvtxn_rec.location_id;
613 END;
614 ELSE
615 l_location_id := p_rcvtxn_rec.location_id;
616 END IF;
617
618 p_rcv_transaction_rec.location_id := l_location_id;
619
620 IF (l_debug = 1) THEN
621 print_debug('Defaulting columns from the input parameters', 4);
622 END IF;
623
624 p_rcv_transaction_rec.lpn_id := p_lpn_id;
625 p_rcv_transaction_rec.transfer_lpn_id := p_transfer_lpn_id;
626 p_rcv_transaction_rec.item_id := p_item_id;
627 p_rcv_transaction_rec.item_revision := p_revision;
628 p_rcv_transaction_rec.subinventory_dsp := p_subinventory_code;
629 p_rcv_transaction_rec.locator_id := p_locator_id;
630 p_rcv_transaction_rec.to_organization_id := p_organization_id;
631 p_rcv_transaction_rec.transaction_date_nb := SYSDATE;
632 p_rcv_transaction_rec.serial_number_control_code := p_serial_control_code;
633 p_rcv_transaction_rec.lot_control_code := p_lot_control_code;
634
635 --Default the rest of the values for an RTI corresponding to Transfer
636 --Destination Type and context are 'RECEIVING', transaction type is 'TRANSFER'
637 --Delivery To and WIP related columns are NULL
638 p_rcv_transaction_rec.transaction_type := 'TRANSFER';
639 p_rcv_transaction_rec.destination_type_code := 'RECEIVING';
640 p_rcv_transaction_rec.deliver_to_person_id := NULL;
641 p_rcv_transaction_rec.deliver_to_location_id := NULL;
642 p_rcv_transaction_rec.wip_entity_id := NULL;
643 p_rcv_transaction_rec.wip_line_id := NULL;
644 p_rcv_transaction_rec.wip_operation_seq_num := NULL;
645 p_rcv_transaction_rec.wip_resource_seq_num := NULL;
646 p_rcv_transaction_rec.wip_repetitive_schedule_id := NULL;
647
648 --Now, insert the RCV_TRANSACTIONS_INTERFACE record
649 l_progress := 50;
650
651 IF (l_debug = 1) THEN
652 print_debug('Inserting the RTI for TRANSFER transaction', 4);
653 END IF;
654
655 IF (NVL(inv_rcv_common_apis.g_rcv_global_var.express_mode, 'NO') <> 'YES') THEN
656 --Call the helper routine to create the RTI record
657 l_interface_txn_id := insert_interface_code (
658 p_rcv_transaction_rec => p_rcv_transaction_rec);
659
660 IF (l_debug = 1) THEN
661 print_debug('INTERFACE_TXN_ID returned for the RTI: ' || l_interface_txn_id, 4);
662
663 --If the insert fails, return value would be -9999 and so error out
664 IF (l_interface_txn_id = -9999) THEN
665 IF (l_debug = 1) THEN
666 print_debug('Progress: ' || l_progress || '. Failure in RTI creation for Transfer', 4);
667 END IF;
668 RAISE FND_API.G_EXC_ERROR;
669 END IF;
670 END IF;
671
672 --Update the lots and serials interface records with the RTID that was
673 --just generated. For transfer, there will only be one parent transaction.
674 --So, we just need to update the product_transaction_id of MTLI and MSNI
675 --records (which have a dummy value) with the RTID just created
676 l_progress := 60;
677 l_msni_count := 0;
678 IF (p_lot_control_code = 2) THEN
679 IF (p_serial_control_code = 6) THEN
680 --First update the lot interface records
681 --Bug #3405320
682 --We support putaway of serials if the item serial control code is
683 --dynamic at SO Issue for int ship/int req documents.
684 --Check the number of serials in interface for this transaction
685 -- (For other docs, the value of l_msni_count would be 0).
686 IF (p_doc_type = 'INTSHIP') THEN
687 SELECT count(1)
688 INTO l_msni_count
689 FROM mtl_serial_numbers_interface
690 WHERE product_transaction_id = p_original_rti_id
691 AND product_code = 'RCV';
692 END IF;
693
694 IF (l_debug = 1) THEN
695 print_debug('Serial control code is 6. doc_type : ' || p_doc_type ||
696 ' . Update MTLI.serial_txn_temp_id TO NULL', 4);
697 END IF;
698 --Bug #3405320
699 --If the document is RMA, do not change serial_transaction_temp_od
700 --For other documents, check if serials are populated in MSNI
701 --If there are none, NULL out serial_temp_id in MTLI else retain it
702 UPDATE mtl_transaction_lots_interface
703 SET product_transaction_id = l_interface_txn_id
704 , serial_transaction_temp_id = DECODE(p_doc_type, 'RMA', serial_transaction_temp_id,
705 decode(l_msni_count, 0, NULL, serial_transaction_temp_id))
706 WHERE product_code = 'RCV'
707 AND product_transaction_id = p_original_rti_id;
708 ELSE
709 --First update the lot interface records
710 UPDATE mtl_transaction_lots_interface
711 SET product_transaction_id = l_interface_txn_id
712 WHERE product_code = 'RCV'
713 AND product_transaction_id = p_original_rti_id;
714 END IF;
715
716 IF (l_debug = 1) THEN
717 print_debug('Updated ' || SQL%ROWCOUNT || ' lot records', 4);
718 END IF;
719
720 --For a lot and serial controlled item, update the MSNI records too
721 UPDATE mtl_serial_numbers_interface
722 SET product_transaction_id = l_interface_txn_id
723 WHERE product_code = 'RCV'
724 AND product_transaction_id = p_original_rti_id;
725
726 IF (l_debug = 1) THEN
727 print_debug('Updated ' || SQL%ROWCOUNT || ' serial records', 4);
728 END IF;
729
730 ELSIF (p_serial_control_code > 1) THEN
731 --Update the serial interface records
732 UPDATE mtl_serial_numbers_interface
733 SET product_transaction_id = l_interface_txn_id
734 WHERE product_code = 'RCV'
735 AND product_transaction_id = p_original_rti_id;
736
737 IF (l_debug = 1) THEN
738 print_debug('Updated ' || SQL%ROWCOUNT || ' serial records', 4);
739 END IF;
740 END IF; --END IF p_lot_control_code = 2
741 END IF; --END IF express_mode <> 'YES'
742
743 l_progress := 70;
744
745 IF (l_debug = 1) THEN
746 print_debug('***populate_transfer_rti_values completed successfully***', 4);
747 END IF;
748
749 EXCEPTION
750 WHEN FND_API.G_EXC_ERROR THEN
751 x_return_status := FND_API.G_RET_STS_ERROR;
752 fnd_msg_pub.count_and_get (
753 p_encoded => FND_API.G_FALSE
754 , p_count => x_msg_count
755 , p_data => x_msg_data );
756 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
757 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
758 fnd_msg_pub.count_and_get (
759 p_encoded => FND_API.G_FALSE
760 , p_count => x_msg_count
761 , p_data => x_msg_data );
762 WHEN OTHERS THEN
763 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
764 fnd_msg_pub.count_and_get (
765 p_encoded => FND_API.G_FALSE
766 , p_count => x_msg_count
767 , p_data => x_msg_data );
768 IF SQLCODE IS NOT NULL THEN
769 inv_mobile_helper_functions.sql_error (
770 'INV_RCV_STD_TRANSFER_APIS.POPULATE_TRANSFER_RTI_VALUES',
771 l_progress,
772 SQLCODE);
773 END IF;
774 END populate_transfer_rti_values;
775
776 /*----------------------------------------------------------------------------*
777 * PROCEDURE: get_avail_quantity_to_transfer
778 * Description: This procedure does the following
779 * a) Validate LPN from rcv_supply for the parent txn
780 * b) Validate Lot from rcv_lots_supply for the parent txn
781 * c) Check the available quantity for the parent transaction
782 * d) Convert the quantity into UOM of MO Line and primary UOM
783 *
784 * Output Parameters:
785 * x_return_status
786 * Return status indicating Success (S), Error (E), Unexpected Error (U)
787 * x_msg_count
788 * Number of messages in message list
789 * x_msg_data
790 * Stacked messages text
791 *
792 * Input Parameters:
793 * p_parent_txn_id - Parent Txn Id
794 * p_organization_id - Organization ID
795 * p_item_id - Item ID
796 * p_lpn_id - LPN ID of the parent txn
797 * p_lot_number - Lot Number
798 * p_transfer_quantity - Quantity selected for transfer
799 * p_transfer_uom_code - Transaction (MMTT) UOM Code
800 * p_primary_uom_code - Item's Primary UOM Code
801 * x_avail_transfer_qty - Quantity available to transfer in Txn UOM
802 * x_avail_primary_qty - Available transfer quantity in primary UOM
803 *
804 * Returns: NONE
805 *---------------------------------------------------------------------------*/
806 PROCEDURE get_avail_quantity_to_transfer(
807 x_return_status OUT NOCOPY VARCHAR2
808 , x_msg_count OUT NOCOPY NUMBER
809 , x_msg_data OUT NOCOPY VARCHAR2
810 , p_parent_txn_id IN NUMBER
811 , p_organization_id IN NUMBER
812 , p_item_id IN NUMBER
813 , p_lpn_id IN NUMBER
814 , p_lot_number IN VARCHAR2
815 , p_transfer_quantity IN NUMBER
816 , p_receipt_source_code IN VARCHAR2
817 , p_transfer_uom_code IN VARCHAR2
818 , p_primary_uom_code IN VARCHAR2
819 , x_avail_transfer_qty OUT NOCOPY NUMBER
820 , x_avail_primary_qty OUT NOCOPY NUMBER ) IS
821
822 --Local Variables
823 l_parent_txn_id NUMBER; --Parent Transaction Id
824 l_avail_transfer_qty NUMBER; --Quantity available to transfer
825 l_avail_primary_qty NUMBER; --Quantity in primary UOM
826 l_rcvtxn_qty NUMBER; --Parent transaction qty
827 l_rcvqty_txn_uom NUMBER; --Available Qty in txn uom
828 l_rcvtxn_uom_code MTL_UNITS_OF_MEASURE.UOM_CODE%TYPE;
829 l_rcvtxn_uom MTL_UNITS_OF_MEASURE.UNIT_OF_MEASURE%TYPE;
830 l_tolerable_qty NUMBER;
831 l_lpn_count NUMBER; --Count of RT records for the LPN ID
832 l_lot_count NUMBER; --Count of RLS records for the given lot
833
834 l_progress NUMBER; --Index to track progress and log error
835 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'), 0);
836
837 BEGIN
838 --Initizlize the return status to success
839 x_return_status := FND_API.G_RET_STS_SUCCESS;
840
841 l_progress := 10;
842
843 IF (l_debug = 1) THEN
844 print_debug('***Entered get_avail_quantity_to_transfer***' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 4);
845 print_debug('Validating LPN in parent txn', 4);
846 END IF;
847
848 --Validate the LPN from rcv_supply
849 IF (p_lpn_id IS NOT NULL AND p_lpn_id > 0) THEN
850 BEGIN
851 SELECT count(1)
852 INTO l_lpn_count
853 FROM rcv_supply rs
854 WHERE rs.rcv_transaction_id = p_parent_txn_id
855 AND rs.lpn_id = p_lpn_id;
856
857 IF l_lpn_count = 0 THEN
858 IF (l_debug = 1) THEN
859 print_debug('MOL LPN not matching!!!', 4);
860 END IF;
861 fnd_message.set_name('INV', 'INV_RCV_NO_ROWS');
862 fnd_msg_pub.add;
863 RAISE FND_API.G_EXC_ERROR;
864 END IF;
865
866 EXCEPTION
867 WHEN OTHERS THEN
868 fnd_message.set_name('INV', 'INV_RCV_NO_ROWS');
869 fnd_msg_pub.add;
870 RAISE FND_API.G_EXC_ERROR;
871 END;
872 END IF; --END IF p_lpn_id is NOT NULL
873
874 l_progress := 20;
875 IF (l_debug = 1) THEN
876 print_debug('Validating Lot in parent txn', 4);
877 END IF;
878
879 --Validate the lot number against RCV_LOTS_SUPPLY
880 IF (p_lot_number IS NOT NULL) THEN
881 BEGIN
882 SELECT count(1)
883 INTO l_lot_count
884 FROM rcv_lots_supply rls
885 WHERE rls.lot_num = p_lot_number
886 AND rls.transaction_id = p_parent_txn_id;
887
888 IF l_lot_count = 0 THEN
889 IF (l_debug = 1) THEN
890 print_debug('Lot Number not matching!!!', 4);
891 END IF;
892 fnd_message.set_name('INV', 'INV_RCV_NO_ROWS');
893 fnd_msg_pub.add;
894 RAISE FND_API.G_EXC_ERROR;
895 END IF;
896
897 EXCEPTION
898 WHEN OTHERS THEN
899 fnd_message.set_name('INV', 'INV_RCV_NO_ROWS');
900 fnd_msg_pub.add;
901 RAISE FND_API.G_EXC_ERROR;
902 END;
903 END IF; --END IF p_lot_number is NOT NULL
904
905 l_parent_txn_id := p_parent_txn_id;
906
907 l_progress := 30;
908 IF (l_debug = 1) THEN
909 print_debug('Calling rcv_quantities_s.get_available_quantity to get transfer qty', 4);
910 END IF;
911
912 --Check the quantity available to transfer and the transaction UOM
913 rcv_quantities_s.get_available_quantity (
914 p_transaction_type => 'TRANSFER'
915 , p_parent_id => l_parent_txn_id
916 , p_receipt_source_code => p_receipt_source_code
917 , p_parent_transaction_type => NULL
918 , p_grand_parent_id => l_parent_txn_id
919 , p_correction_type => NULL
920 , p_available_quantity => l_rcvtxn_qty
921 , p_tolerable_quantity => l_tolerable_qty
922 , p_unit_of_measure => l_rcvtxn_uom);
923
924 IF (l_debug = 1) THEN
925 print_debug('*** Return Values from get_available_quantity***', 4);
926 print_debug(' l_rcvtxn_qty: ' || l_rcvtxn_qty || ' :: l_rcvtxn_uom '
927 || l_rcvtxn_uom || ' :: l_tolerable_qty: ' || l_tolerable_qty, 4);
928 END IF;
929
930 SELECT uom_code
931 INTO l_rcvtxn_uom_code
932 FROM mtl_item_uoms_view
933 WHERE organization_id = p_organization_id
934 AND inventory_item_id = p_item_id
935 AND unit_of_measure = l_rcvtxn_uom;
936
937 l_progress := 40;
938 IF (l_debug = 1) THEN
939 print_debug('Converting l_rcvtxn_qty to transfer UOM', 4);
940 END IF;
941
942 --Convert the available quantity from paremt txn UOM to transfer (MOL) UOM
943 IF l_rcvtxn_uom_code <> p_transfer_uom_code THEN
944 l_rcvqty_txn_uom := inv_convert.inv_um_convert(
945 item_id => p_item_id
946 , precision => NULL
947 , from_quantity => l_rcvtxn_qty
948 , from_unit => l_rcvtxn_uom_code
949 , to_unit => p_transfer_uom_code
950 , from_name => NULL
951 , to_name => NULL);
952 ELSE
953 l_rcvqty_txn_uom := l_rcvtxn_qty;
954 END IF;
955
956 -- a) If available quantity is 0 then raise the error to indicate match failed
957 -- b) If input quantity > available quantity, then raise over tolerance error
958 -- c) If input quantity < available quantity, then set transfer quantity to input quantity
959 -- d) If input quantity = available quantity, then set transfer quantity to avalable quantity
960
961 IF (l_rcvqty_txn_uom = 0) THEN
962 IF (l_debug = 1) THEN
963 print_debug('get_avail_quantity_to_transfer: There is no quantity available to transfer: ', 4);
964 END IF;
965 fnd_message.set_name('INV', 'INV_RCV_NO_ROWS');
966 fnd_msg_pub.add;
967 RAISE FND_API.G_EXC_ERROR;
968 ELSIF (p_transfer_quantity > l_rcvqty_txn_uom) THEN
969 IF (l_debug = 1) THEN
970 print_debug('Transfer qty ' || p_transfer_quantity || ' exceeds ' ||
971 ' available quantity ' || l_rcvqty_txn_uom, 4);
972 END IF;
973 fnd_message.set_name('INV', 'INV_RCV_QTY_OVER_TOLERANCE');
974 fnd_msg_pub.add;
975 RAISE FND_API.G_EXC_ERROR;
976 ELSIF (p_transfer_quantity < l_rcvqty_txn_uom) THEN
977 l_avail_transfer_qty := p_transfer_quantity;
978 ELSE
979 l_avail_transfer_qty := l_rcvqty_txn_uom;
980 END IF;
981
982 l_progress := 50;
983 IF (l_debug = 1) THEN
984 print_debug('Getting transfer quantity in primary UOM', 4);
985 END IF;
986
987 --Get the quantity in terms of primary UOM
988 IF p_transfer_uom_code <> p_primary_uom_code THEN
989 l_avail_primary_qty := inv_convert.inv_um_convert(
990 item_id => p_item_id
991 , precision => NULL
992 , from_quantity => l_avail_transfer_qty
993 , from_unit => p_transfer_uom_code
994 , to_unit => p_primary_uom_code
995 , from_name => NULL
996 , to_name => NULL);
997 ELSE
998 l_avail_primary_qty := l_avail_transfer_qty;
999 END IF;
1000
1001 --Set the available quantity values, for both txn uom and primary uom
1002 x_avail_transfer_qty := l_avail_transfer_qty;
1003 x_avail_primary_qty := l_avail_primary_qty;
1004
1005 l_progress := 60;
1006 IF (l_debug = 1) THEN
1007 print_debug('get_avail_quantity_to_transfer completed successfully', 4);
1008 END IF;
1009
1010 EXCEPTION
1011 WHEN FND_API.G_EXC_ERROR THEN
1012 x_return_status := FND_API.G_RET_STS_ERROR;
1013 fnd_msg_pub.count_and_get (
1014 p_encoded => FND_API.G_FALSE
1015 , p_count => x_msg_count
1016 , p_data => x_msg_data );
1017 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1018 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1019 fnd_msg_pub.count_and_get (
1020 p_encoded => FND_API.G_FALSE
1021 , p_count => x_msg_count
1022 , p_data => x_msg_data );
1023 WHEN OTHERS THEN
1024 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1025 fnd_msg_pub.count_and_get (
1026 p_encoded => FND_API.G_FALSE
1027 , p_count => x_msg_count
1028 , p_data => x_msg_data );
1029 IF SQLCODE IS NOT NULL THEN
1030 inv_mobile_helper_functions.sql_error (
1031 'INV_RCV_STD_TRANSFER_APIS.GET_AVAIL_QTY_TO_TRANSFER',
1032 l_progress,
1033 SQLCODE);
1034 END IF;
1035 END get_avail_quantity_to_transfer;
1036
1037 /*-----------------------------------------------------------------------------
1038 * PROCEDURE: create_po_transfer_rec
1039 * Description/Processing Logic:
1040 * Called when the document source type is a Purchase Order
1041 * Fetch the parent transaction values using po line location id
1042 * Validate the transfer quantity with rcv_supply for the PO line location
1043 * Initialize the RTI record with the quantity and primary quantity
1044 * Call the populate_transfer_rti_values rotuine to create the Transfer RTI
1045 *
1046 * Output Parameters:
1047 * x_return_status
1048 * Return status indicating Success (S), Error (E), Unexpected Error (U)
1049 * x_msg_count
1050 * Number of messages in message list
1051 * x_msg_data
1052 * Stacked messages text
1053 *
1054 * Input Parameters:
1055 * p_organization_id - Organization ID
1056 * p_po_header_id - PO Header ID
1057 * p_po_release_id - PO Release ID
1058 * p_po_line_id - PO Line ID for the PO line location
1059 * p_po_line_location_id - PO Line ID for the PO line location
1060 * p_parent_txn_id - Transaction ID of the parent transaction
1061 * p_item_id - Item Being transferred
1062 * p_revision - Item Revision
1063 * p_subinventory_code - Destination receiving subinventory code
1064 * p_locator_id - Destination receiving locator ID
1065 * p_transfer_quantity - Quantity to be transferred
1066 * p_transfer_uom_code - UOM code of the quantity being tranferred
1067 * p_lot_control_code - Lot Control Code of the item
1068 * p_serial_control_code - Serial Control Code of the item
1069 * p_original_rti_id - Interface Transaction Id for lot/serial split
1070 * p_original_temp_id - Transaction Temp ID of the MMTT being putaway
1071 * p_lpn_id - LPN ID of the move order line
1072 * p_transfer_lpn_id - LPN ID of the LPN being dropped into
1073 *
1074 * Returns: NONE
1075 *---------------------------------------------------------------------------*/
1076 PROCEDURE create_po_transfer_rec (
1077 x_return_status OUT NOCOPY VARCHAR2
1078 , x_msg_count OUT NOCOPY VARCHAR2
1079 , x_msg_data OUT NOCOPY VARCHAR2
1080 , p_organization_id IN NUMBER
1081 , p_po_header_id IN NUMBER
1082 , p_po_release_id IN NUMBER
1083 , p_po_line_id IN NUMBER
1084 , p_po_line_location_id IN NUMBER
1085 , p_parent_txn_id IN NUMBER
1086 , p_item_id IN NUMBER
1087 , p_revision IN VARCHAR2
1088 , p_subinventory_code IN VARCHAR2
1089 , p_locator_id IN NUMBER
1090 , p_transfer_quantity IN NUMBER
1091 , p_transfer_uom_code IN VARCHAR2
1092 , p_lot_control_code IN NUMBER
1093 , p_serial_control_code IN NUMBER
1094 , p_original_rti_id IN NUMBER DEFAULT NULL
1095 , p_original_temp_id IN NUMBER DEFAULT NULL
1096 , p_lot_number IN VARCHAR2 DEFAULT NULL
1097 , p_lpn_id IN NUMBER DEFAULT NULL
1098 , p_transfer_lpn_id IN NUMBER DEFAULT NULL
1099 , p_sec_transfer_quantity IN NUMBER DEFAULT NULL --OPM Convergence
1100 , p_sec_transfer_uom_code IN VARCHAR2 DEFAULT NULL)--OPM Convergence
1101 IS
1102 CURSOR c_rcvtxn_detail( v_rcv_txn_id NUMBER,
1103 v_po_line_location_id NUMBER) IS
1104 SELECT rs.from_organization_id from_organization_id
1105 , rs.to_organization_id to_organization_id
1106 , rt.source_document_code source_document_code
1107 , rsh.receipt_source_code receipt_source_code
1108 , rs.rcv_transaction_id rcv_transaction_id
1109 , rt.transaction_date transaction_date
1110 , rt.transaction_type transaction_type
1111 , rt.primary_unit_of_measure primary_unit_of_measure
1112 , rt.primary_quantity primary_quantity
1113 , rs.po_header_id po_header_id
1114 , rt.po_revision_num po_revision_num
1115 , rs.po_release_id po_release_id
1116 , rsh.vendor_id vendor_id
1117 , rt.vendor_site_id vendor_site_id
1118 , rs.po_line_id po_line_id
1119 , rt.po_unit_price po_unit_price
1120 , rsl.category_id category_id
1121 , rs.item_id item_id
1122 , msi.serial_number_control_code serial_number_control_code
1123 , msi.lot_control_code lot_control_code
1124 , rs.item_revision item_revision
1125 , rs.po_line_location_id po_line_location_id
1126 , to_number(NULL) po_distribution_id
1127 , rt.employee_id employee_id
1128 , rsl.comments comments
1129 , to_number(NULL) req_header_id
1130 , to_number(NULL) req_line_id
1131 , rs.shipment_header_id shipment_header_id
1132 , rs.shipment_line_id shipment_line_id
1133 , rsh.packing_slip packing_slip
1134 , rsl.government_context government_context
1135 , rsl.ussgl_transaction_code ussgl_transaction_code
1136 , rt.inspection_status_code inspection_status_code
1137 , rt.inspection_quality_code inspectin_quality_code
1138 , rt.vendor_lot_num vendor_lot_num
1139 , pol.vendor_product_num vendor_item_number
1140 , rt.substitute_unordered_code substitute_unordered_code
1141 , rt.routing_header_id routing_id
1142 , rt.routing_step_id routing_step_id
1143 , rt.reason_id reason_id
1144 , rt.currency_code currency_code
1145 , rt.currency_conversion_rate currency_conversion_rate
1146 , rt.currency_conversion_date currency_conversion_date
1147 , rt.currency_conversion_type currency_conversion_type
1148 , to_number(NULL) req_distribution_id
1149 , rs.destination_type_code destination_type_code_hold
1150 , rs.destination_type_code final_destination_type_code
1151 , rt.location_id location_id
1152 , to_number(NULL) final_deliver_to_person_id
1153 , to_number(NULL) final_deliver_to_location_id
1154 , rsl.to_subinventory subinventory
1155 , NVL(pol.un_number_id,
1156 msi.un_number_id) un_number_id
1157 , NVL(pol.hazard_class_id,
1158 msi.hazard_class_id) hazard_class_id
1159 , rs.creation_date creation_date
1160 , rt.attribute_category attribute_category
1161 , rt.attribute1 attribute1
1162 , rt.attribute2 attribute2
1163 , rt.attribute3 attribute3
1164 , rt.attribute4 attribute4
1165 , rt.attribute5 attribute5
1166 , rt.attribute6 attribute6
1167 , rt.attribute7 attribute7
1168 , rt.attribute8 attribute8
1169 , rt.attribute9 attribute9
1170 , rt.attribute10 attribute10
1171 , rt.attribute11 attribute11
1172 , rt.attribute12 attribute12
1173 , rt.attribute13 attribute13
1174 , rt.attribute14 attribute14
1175 , rt.attribute15 attribute15
1176 , rt.qa_collection_id qa_collection_id
1177 , to_number(NULL) oe_order_header_id
1178 , to_number(NULL) oe_order_line_id
1179 , rsh.customer_id customer_id
1180 , rsh.customer_site_id customer_site_id
1181 , to_number(NULL) wip_entity_id
1182 , to_number(NULL) po_operation_seq_num
1183 , to_number(NULL) po_resource_seq_num
1184 , to_number(NULL) wip_repetitive_schedule_id
1185 , to_number(NULL) wip_line_id
1186 , to_number(NULL) bom_resource_id
1187 , to_char(NULL) final_subinventory
1188 , rt.secondary_quantity secondary_quantity --OPM Convergence
1189 , rt.secondary_unit_of_measure secondary_uom --OPM Convergence
1190 --The following columns are needed for matching in cases where no LPN is involved
1191 , rs.to_subinventory from_subinventory_code
1192 , rs.to_locator_id from_locator_id
1193 FROM rcv_transactions rt
1194 , rcv_supply rs
1195 , rcv_shipment_headers rsh
1196 , rcv_shipment_lines rsl
1197 , po_lines pol
1198 , mtl_system_items msi
1199 WHERE rs.rcv_transaction_id = v_rcv_txn_id
1200 AND rs.to_organization_id = p_organization_id
1201 AND rs.rcv_transaction_id = rt.transaction_id
1202 AND rs.po_line_location_id = v_po_line_location_id
1203 AND rs.shipment_line_id = rsl.shipment_line_id
1204 AND rs.shipment_header_id = rsh.shipment_header_id
1205 AND rs.po_line_id = pol.po_line_id
1206 AND msi.organization_id = p_organization_id
1207 AND msi.inventory_item_id = rs.item_id;
1208
1209 --Local variables
1210 l_parent_transaction_id NUMBER; --Transaction ID of the parent RT record
1211 l_con_transfer_qty NUMBER; --Qty converted in MOL UOM code
1212 l_primary_qty NUMBER; --Quantity in primay uom code
1213 l_primary_uom mtl_units_of_measure.unit_of_measure%TYPE;
1214 l_transfer_uom mtl_units_of_measure.unit_of_measure%TYPE;
1215 l_primary_uom_code mtl_units_of_measure.uom_code%TYPE;
1216 l_transfer_uom_code mtl_units_of_measure.uom_code%TYPE;
1217 l_rcv_transaction_rec INV_RCV_STD_DELIVER_APIS.rcvtxn_transaction_rec_tp;
1218 l_rcvtxn_rec INV_RCV_STD_DELIVER_APIS.rcvtxn_enter_rec_cursor_rec;
1219 l_receipt_source_code RCV_SHIPMENT_HEADERS.RECEIPT_SOURCE_CODE%TYPE;
1220 l_parent_txn_id NUMBER;
1221 l_um_transfer_qty NUMBER; --Transfer quantity converted to MOL UOM
1222
1223 l_progress NUMBER; --Index to track progress and log error
1224 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'), 0);
1225
1226 --OPM Convergence
1227 l_sec_transfer_uom_code mtl_units_of_measure.uom_code%TYPE; --OPM Convergence
1228 l_secondary_uom mtl_units_of_measure.unit_of_measure%TYPE;
1229 l_sec_transfer_uom mtl_units_of_measure.unit_of_measure%TYPE;
1230 l_secondary_uom_code mtl_units_of_measure.uom_code%TYPE;
1231 l_sec_um_xfer_qty NUMBER;
1232
1233 l_secondary_qty NUMBER;
1234
1235
1236 BEGIN
1237
1238 --Initialize the return status to success
1239 x_return_status := FND_API.G_RET_STS_SUCCESS;
1240
1241 l_progress := 10;
1242
1243 IF (l_debug = 1) THEN
1244 print_debug('Entered create_po_transfer_rec: ' || l_progress || ' ' ||
1245 TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 3);
1246 print_debug('************Input parameters are************', 3);
1247 print_debug(' p_po_header_id ===> ' || p_po_header_id, 3);
1248 print_debug(' p_po_release_id ===> ' || p_po_release_id, 3);
1249 print_debug(' p_po_line_id ===> ' || p_po_line_id, 3);
1250 print_debug(' p_po_line_location_id ===> ' || p_po_line_location_id, 3);
1251 print_debug('********************************************', 3);
1252 END IF;
1253
1254 l_progress := 20;
1255
1256 IF (l_debug = 1) THEN
1257 print_debug('Fetching the Units of measure for the item', 4);
1258 END IF;
1259
1260 /* Fetch the primary uom, primary uom code and the transfer uom
1261 * This is needed because receiving works in unit of measure rather
1262 * than the uom code
1263 */
1264 BEGIN
1265
1266 l_transfer_uom_code := p_transfer_uom_code;
1267 l_sec_transfer_uom_code := p_sec_transfer_uom_code; --OPM Convergence
1268
1269 SELECT primary_unit_of_measure
1270 , primary_uom_code
1271 -- , secondary_unit_of_measure
1272 , secondary_uom_code --OPM Convergence
1273 INTO l_primary_uom
1274 , l_primary_uom_code
1275 -- , l_secondary_uom ----OPM Convergence
1276 , l_secondary_uom_code --OPM Convergence
1277 FROM mtl_system_items
1278 WHERE inventory_item_id = p_item_id
1279 AND organization_id = p_organization_id;
1280
1281 SELECT unit_of_measure
1282 INTO l_transfer_uom
1283 FROM mtl_item_uoms_view
1284 WHERE organization_id = p_organization_id
1285 AND inventory_item_id = p_item_id
1286 AND uom_code = l_transfer_uom_code;
1287
1288
1289 --OPM Convergence
1290 IF l_sec_transfer_uom_code IS NOT NULL THEN
1291
1292 SELECT unit_of_measure
1293 INTO l_sec_transfer_uom
1294 FROM mtl_item_uoms_view
1295 WHERE organization_id = p_organization_id
1296 AND inventory_item_id = p_item_id
1297 AND uom_code = l_sec_transfer_uom_code;
1298
1299 END IF;
1300
1301 EXCEPTION
1302 WHEN OTHERS THEN
1303 fnd_message.set_name('INV', 'INV-NO ITEM UOM');
1304 fnd_msg_pub.add;
1305 RAISE FND_API.G_EXC_ERROR;
1306 END;
1307
1308 l_progress := 30;
1309
1310 IF (l_debug = 1) THEN
1311 print_debug('Progress: '|| l_progress || '. Fetching the parent transaction details', 4);
1312 END IF;
1313
1314 --Fetch the parent transaction details
1315 OPEN c_rcvtxn_detail(p_parent_txn_id, p_po_line_location_id);
1316 FETCH c_rcvtxn_detail INTO l_rcvtxn_rec;
1317 CLOSE c_rcvtxn_detail;
1318
1319 l_progress := 40;
1320
1321 l_parent_txn_id := p_parent_txn_id;
1322 l_receipt_source_code := l_rcvtxn_rec.receipt_source_code;
1323
1324 --Call the common routine to validate the LPN, lot and also fetch the
1325 --quantity available to transfer
1326 IF (l_debug = 1) THEN
1327 print_debug('***Current Progress: ' || l_progress || '. Calling get_avail_quantity_to_transfer with***', 4);
1328 print_debug(' Primary UOM Code: ' || l_primary_uom_code, 4);
1329 print_debug(' Transfer UOM Code:' || l_transfer_uom_code, 4);
1330 print_debug(' Secondary UOM Code:' || l_sec_transfer_uom_code || ' (' || l_sec_transfer_uom || ')', 4);
1331 END IF;
1332
1333 get_avail_quantity_to_transfer(
1334 x_return_status => x_return_status
1335 , x_msg_count => x_msg_count
1336 , x_msg_data => x_msg_data
1337 , p_parent_txn_id => l_parent_txn_id
1338 , p_organization_id => p_organization_id
1339 , p_item_id => p_item_id
1340 , p_lpn_id => p_lpn_id
1341 , p_lot_number => p_lot_number
1342 , p_transfer_quantity => p_transfer_quantity
1343 , p_receipt_source_code => l_receipt_source_code
1344 , p_transfer_uom_code => l_transfer_uom_code
1345 , p_primary_uom_code => l_primary_uom_code
1346 , x_avail_transfer_qty => l_um_transfer_qty
1347 , x_avail_primary_qty => l_primary_qty);
1348
1349 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1350 IF (l_debug = 1) THEN
1351 print_debug('Failure in create_po_transfer_rec at progress level '
1352 || l_progress || ' . get_avail_quantity_to_transfer raised FND_API.G_EXC_ERROR', 4);
1353 END IF;
1354 RAISE FND_API.G_EXC_ERROR;
1355 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1356 IF (l_debug = 1) THEN
1357 print_debug('Failure in create_po_transfer_rec at progress level ' || l_progress
1358 || ' . get_avail_quantity_to_transfer raised FND_API.G_EXC_UNEXP_ERROR', 4);
1359 END IF;
1360 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1361 END IF;
1362
1363 /* OPM Convergence. Obtain the seondary available to transfer quantity
1364 using the qty available to transfer in primary uom
1365 */
1366 l_sec_um_xfer_qty := inv_convert.inv_um_convert(p_item_id,
1367 5,
1368 l_um_transfer_qty,
1369 l_transfer_uom_code,
1370 p_sec_transfer_uom_code,
1371 NULL,
1372 NULL);
1373 IF (l_debug = 1) THEN
1374 print_debug('create_po_transfer_rec: Quantity to transfer (txn uom): ' || l_um_transfer_qty, 4);
1375 print_debug('Primary Quantity: ' || l_primary_qty ||' Secondary Quantity: ' || l_sec_um_xfer_qty || ' ' || l_secondary_uom_code, 4);
1376 END IF;
1377
1378 l_progress := 50;
1379
1380 --Populate the rcv_transactions_rec record with the quantities and UOMs
1381 --This is later populated with other values from the parent transaction and MMTT
1382 l_rcv_transaction_rec.transaction_quantity := l_um_transfer_qty;
1383 l_rcv_transaction_rec.transaction_uom := l_transfer_uom;
1384 l_rcv_transaction_rec.primary_quantity := l_primary_qty;
1385 l_rcv_transaction_rec.primary_uom := l_primary_uom;
1386
1387 --OPM Convergence
1388 l_rcv_transaction_rec.sec_transaction_quantity := l_sec_um_xfer_qty;
1389 l_rcv_transaction_rec.secondary_uom := l_sec_transfer_uom;
1390 l_rcv_transaction_rec.secondary_uom_code := l_secondary_uom_code;
1391
1392 --Now call the procedure to create the RTI record for transfer
1393 l_progress := 60;
1394
1395 IF (l_debug = 1) THEN
1396 print_debug('Calling populate_transfer_rti_values', 4);
1397 END IF;
1398
1399 populate_transfer_rti_values (
1400 x_return_status => x_return_status
1401 , x_msg_count => x_msg_count
1402 , x_msg_data => x_msg_data
1403 , p_rcv_transaction_rec => l_rcv_transaction_rec
1404 , p_rcvtxn_rec => l_rcvtxn_rec
1405 , p_parent_txn_id => p_parent_txn_id
1406 , p_organization_id => p_organization_id
1407 , p_item_id => p_item_id
1408 , p_revision => p_revision
1409 , p_subinventory_code => p_subinventory_code
1410 , p_locator_id => p_locator_id
1411 , p_lot_control_code => p_lot_control_code
1412 , p_serial_control_code => p_serial_control_code
1413 , p_original_rti_id => p_original_rti_id
1414 , p_original_temp_id => p_original_temp_id
1415 , p_lpn_id => p_lpn_id
1416 , p_transfer_lpn_id => p_transfer_lpn_id
1417 , p_doc_type => 'PO');
1418
1419 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1420 IF (l_debug = 1) THEN
1421 print_debug('Failure in create_po_transfer_rec at progress level '
1422 || l_progress || ' . populate_transfer_rti_values raised FND_API.G_EXC_ERROR', 4);
1423 END IF;
1424 RAISE FND_API.G_EXC_ERROR;
1425 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1426 IF (l_debug = 1) THEN
1427 print_debug('Failure in create_po_transfer_rec at progress level ' || l_progress
1428 || ' . populate_transfer_rti_values raised FND_API.G_EXC_UNEXP_ERROR', 4);
1429 END IF;
1430 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1431 END IF;
1432
1433 l_progress := 70;
1434 IF (l_debug = 1) THEN
1435 print_debug('***create_po_transfer_rec completed successfully***', 4);
1436 END IF;
1437
1438 EXCEPTION
1439 WHEN FND_API.G_EXC_ERROR THEN
1440 print_debug('Exception in create_po_transfer_rec ', 4);
1441 IF c_rcvtxn_detail%ISOPEN THEN
1442 CLOSE c_rcvtxn_detail;
1443 END IF;
1444 x_return_status := FND_API.G_RET_STS_ERROR;
1445 fnd_msg_pub.count_and_get (
1446 p_encoded => FND_API.G_FALSE
1447 , p_count => x_msg_count
1448 , p_data => x_msg_data );
1449 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1450 print_debug('Unexpected Exception in create_po_transfer_rec ', 4);
1451 IF c_rcvtxn_detail%ISOPEN THEN
1452 CLOSE c_rcvtxn_detail;
1453 END IF;
1454 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1455 fnd_msg_pub.count_and_get (
1456 p_encoded => FND_API.G_FALSE
1457 , p_count => x_msg_count
1458 , p_data => x_msg_data );
1459 WHEN OTHERS THEN
1460 print_debug('Other Exception in create_po_transfer_rec '||SQLERRM, 4);
1461 IF c_rcvtxn_detail%ISOPEN THEN
1462 CLOSE c_rcvtxn_detail;
1463 END IF;
1464 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1465 fnd_msg_pub.count_and_get (
1466 p_encoded => FND_API.G_FALSE
1467 , p_count => x_msg_count
1468 , p_data => x_msg_data );
1469 IF SQLCODE IS NOT NULL THEN
1470 inv_mobile_helper_functions.sql_error (
1471 'INV_RCV_STD_TRANSFER_APIS.CREATE_PO_TRANSFER_REC',
1472 l_progress,
1473 SQLCODE);
1474 END IF;
1475 END create_po_transfer_rec;
1476
1477
1478 /*-----------------------------------------------------------------------------
1479 * PROCEDURE: create_int_ship_transfer_rec
1480 * Description/Processing Logic:
1481 * Called when the document source type is Internal Req/ Intransit Ship
1482 * Fetch the parent transaction values using shipment line
1483 * Validate the transfer quantity with rcv_supply for the shipment line
1484 * Initialize the RTI record with the quantity and primary quantity
1485 * Call the populate_transfer_rti_values rotuine to create the Transfer RTI
1486 *
1487 * Output Parameters:
1488 * x_return_status
1489 * Return status indicating Success (S), Error (E), Unexpected Error (U)
1490 * x_msg_count
1491 * Number of messages in message list
1492 * x_msg_data
1493 * Stacked messages text
1494 *
1495 * Input Parameters:
1496 * p_organization_id - Organization ID
1497 * p_shipment_header_id - PO Header ID
1498 * p_shipment_line_id - PO Line ID for the PO line location
1499 * p_parent_txn_id - Transaction ID of the parent transaction
1500 * p_item_id - Item Being transferred
1501 * p_revision - Item Revision
1502 * p_subinventory_code - Destination receiving subinventory code
1503 * p_locator_id - Destination receiving locator ID
1504 * p_transfer_quantity - Quantity to be transferred
1505 * p_transfer_uom_code - UOM code of the quantity being tranferred
1506 * p_lot_control_code - Lot Control Code of the item
1507 * p_serial_control_code - Serial Control Code of the item
1508 * p_original_rti_id - Interface Transaction Id for lot/serial split
1509 * p_original_temp_id - Transaction Temp ID of the MMTT being putaway
1510 * p_lpn_id - LPN ID of the move order line
1511 * p_transfer_lpn_id - LPN ID of the LPN being dropped into
1512 *
1513 * Returns: NONE
1514 *---------------------------------------------------------------------------*/
1515 PROCEDURE create_int_ship_transfer_rec (
1516 x_return_status OUT NOCOPY VARCHAR2
1517 , x_msg_count OUT NOCOPY VARCHAR2
1518 , x_msg_data OUT NOCOPY VARCHAR2
1519 , p_organization_id IN NUMBER
1520 , p_shipment_header_id IN NUMBER
1521 , p_shipment_line_id IN NUMBER
1522 , p_parent_txn_id IN NUMBER
1523 , p_item_id IN NUMBER
1524 , p_revision IN VARCHAR2
1525 , p_subinventory_code IN VARCHAR2
1526 , p_locator_id IN NUMBER
1527 , p_transfer_quantity IN NUMBER
1528 , p_transfer_uom_code IN VARCHAR2
1529 , p_lot_control_code IN NUMBER
1530 , p_serial_control_code IN NUMBER
1531 , p_original_rti_id IN NUMBER DEFAULT NULL
1532 , p_original_temp_id IN NUMBER DEFAULT NULL
1533 , p_lot_number IN VARCHAR2 DEFAULT NULL
1534 , p_lpn_id IN NUMBER DEFAULT NULL
1535 , p_transfer_lpn_id IN NUMBER DEFAULT NULL
1536 , p_sec_transfer_quantity IN NUMBER DEFAULT NULL --OPM Convergence
1537 , p_sec_transfer_uom_code IN VARCHAR2 DEFAULT NULL)--OPM Convergence
1538 IS
1539 CURSOR c_rcvtxn_detail( v_rcv_txn_id NUMBER,
1540 v_shipment_line_id NUMBER) IS
1541 SELECT rs.from_organization_id from_organization_id
1542 , rs.to_organization_id to_organization_id
1543 , rt.source_document_code source_document_code
1544 , rsh.receipt_source_code receipt_source_code
1545 , rs.rcv_transaction_id rcv_transaction_id
1546 , rt.transaction_date transaction_date
1547 , rt.transaction_type transaction_type
1548 , rt.primary_unit_of_measure primary_unit_of_measure
1549 , rt.primary_quantity primary_quantity
1550 , to_number(NULL) po_header_id
1551 , to_number(NULL) po_revision_num
1552 , to_number(NULL) po_release_id
1553 , rsh.vendor_id vendor_id
1554 , rt.vendor_site_id vendor_site_id
1555 , to_number(NULL) po_line_id
1556 , to_number(NULL) po_unit_price
1557 , rsl.category_id category_id
1558 , rs.item_id item_id
1559 , msi.serial_number_control_code serial_number_control_code
1560 , msi.lot_control_code lot_control_code
1561 , rs.item_revision item_revision
1562 , to_number(NULL) po_line_location_id
1563 , to_number(NULL) po_distribution_id
1564 , rt.employee_id employee_id
1565 , rsl.comments comments
1566 , to_number(NULL) req_header_id
1567 , to_number(NULL) req_line_id
1568 , rs.shipment_header_id shipment_header_id
1569 , rs.shipment_line_id shipment_line_id
1570 , rsh.packing_slip packing_slip
1571 , rsl.government_context government_context
1572 , rsl.ussgl_transaction_code ussgl_transaction_code
1573 , rt.inspection_status_code inspection_status_code
1574 , rt.inspection_quality_code inspectin_quality_code
1575 , rt.vendor_lot_num vendor_lot_num
1576 , '' vendor_item_number
1577 , rt.substitute_unordered_code substitute_unordered_code
1578 , rt.routing_header_id routing_id
1579 , rt.routing_step_id routing_step_id
1580 , rt.reason_id reason_id
1581 , rt.currency_code currency_code
1582 , rt.currency_conversion_rate currency_conversion_rate
1583 , rt.currency_conversion_date currency_conversion_date
1584 , rt.currency_conversion_type currency_conversion_type
1585 , rsl.req_distribution_id req_distribution_id
1586 , rs.destination_type_code destination_type_code_hold
1587 , rs.destination_type_code final_destination_type_code
1588 , rt.location_id location_id
1589 , to_number(NULL) final_deliver_to_person_id
1590 , to_number(NULL) final_deliver_to_location_id
1591 , rsl.to_subinventory subinventory
1592 , msi.un_number_id un_number_id
1593 , msi.hazard_class_id hazard_class_id
1594 , rs.creation_date creation_date
1595 , rt.attribute_category attribute_category
1596 , rt.attribute1 attribute1
1597 , rt.attribute2 attribute2
1598 , rt.attribute3 attribute3
1599 , rt.attribute4 attribute4
1600 , rt.attribute5 attribute5
1601 , rt.attribute6 attribute6
1602 , rt.attribute7 attribute7
1603 , rt.attribute8 attribute8
1604 , rt.attribute9 attribute9
1605 , rt.attribute10 attribute10
1606 , rt.attribute11 attribute11
1607 , rt.attribute12 attribute12
1608 , rt.attribute13 attribute13
1609 , rt.attribute14 attribute14
1610 , rt.attribute15 attribute15
1611 , rt.qa_collection_id qa_collection_id
1612 , to_number(NULL) oe_order_header_id
1613 , to_number(NULL) oe_order_line_id
1614 , rsh.customer_id customer_id
1615 , rsh.customer_site_id customer_site_id
1616 , to_number(NULL) wip_entity_id
1617 , to_number(NULL) po_operation_seq_num
1618 , to_number(NULL) po_resource_seq_num
1619 , to_number(NULL) wip_repetitive_schedule_id
1620 , to_number(NULL) wip_line_id
1621 , to_number(NULL) bom_resource_id
1622 , to_char(NULL) final_subinventory
1623 , rt.secondary_quantity secondary_quantity --OPM Convergence
1624 , rt.secondary_unit_of_measure secondary_uom --OPM Convergence
1625 --The following columns are needed for matching in cases where no LPN is involved
1626 , rs.to_subinventory from_subinventory_code
1627 , rs.to_locator_id from_locator_id
1628 FROM rcv_transactions rt
1629 , rcv_supply rs
1630 , rcv_shipment_headers rsh
1631 , rcv_shipment_lines rsl
1632 , mtl_system_items msi
1633 WHERE rs.rcv_transaction_id = v_rcv_txn_id
1634 AND rs.to_organization_id = p_organization_id
1635 AND rs.rcv_transaction_id = rt.transaction_id
1636 AND rs.shipment_line_id = v_shipment_line_id
1637 AND rs.shipment_line_id = rsl.shipment_line_id
1638 AND rs.shipment_header_id = rsh.shipment_header_id
1639 AND msi.organization_id = p_organization_id
1640 AND msi.inventory_item_id = rs.item_id;
1641
1642 --Local variables
1643 l_parent_transaction_id NUMBER; --Transaction ID of the parent RT record
1644 l_con_transfer_qty NUMBER; --Qty converted in MOL UOM code
1645 l_primary_qty NUMBER; --Quantity in primay uom code
1646 l_primary_uom mtl_units_of_measure.unit_of_measure%TYPE;
1647 l_transfer_uom mtl_units_of_measure.unit_of_measure%TYPE;
1648 l_primary_uom_code mtl_units_of_measure.uom_code%TYPE;
1649 l_transfer_uom_code mtl_units_of_measure.uom_code%TYPE;
1650 l_rcv_transaction_rec INV_RCV_STD_DELIVER_APIS.rcvtxn_transaction_rec_tp;
1651 l_rcvtxn_rec INV_RCV_STD_DELIVER_APIS.rcvtxn_enter_rec_cursor_rec;
1652 l_receipt_source_code RCV_SHIPMENT_HEADERS.RECEIPT_SOURCE_CODE%TYPE;
1653 l_parent_txn_id NUMBER;
1654 l_um_transfer_qty NUMBER; --Transfer quantity converted to MOL UOM
1655 l_progress NUMBER; --Index to track progress and log error
1656 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'), 0);
1657 --OPM Convergence
1658 l_sec_transfer_uom_code mtl_units_of_measure.uom_code%TYPE; --OPM Convergence
1659 l_secondary_uom mtl_units_of_measure.unit_of_measure%TYPE;
1660 l_sec_transfer_uom mtl_units_of_measure.unit_of_measure%TYPE;
1661 l_secondary_uom_code mtl_units_of_measure.uom_code%TYPE;
1662 l_sec_um_xfer_qty NUMBER;
1663
1664 l_secondary_qty NUMBER;
1665 BEGIN
1666
1667 l_progress := 10;
1668
1669 --Initialize the return status to success
1670 x_return_status := FND_API.G_RET_STS_SUCCESS;
1671
1672 IF (l_debug = 1) THEN
1673 print_debug('Entered create_int_ship_transfer_rec: ' || l_progress || ' ' ||
1674 TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 3);
1675 print_debug('************Input parameters are************', 3);
1676 print_debug(' p_shipment_header_id ===> ' || p_shipment_header_id, 3);
1677 print_debug(' p_shipment_line_id ===> ' || p_shipment_line_id, 3);
1678 print_debug('********************************************', 3);
1679 END IF;
1680
1681 l_progress := 20;
1682
1683 IF (l_debug = 1) THEN
1684 print_debug('Fetching the units of measure for the item', 4);
1685 END IF;
1686
1687 --Fetch the primary uom, primary uom code and transaction uom for the item
1688 --This is because receiving works unit of measure than uom code
1689 BEGIN
1690 l_transfer_uom_code := p_transfer_uom_code;
1691 l_sec_transfer_uom_code := p_sec_transfer_uom_code;
1692
1693 SELECT primary_unit_of_measure
1694 , primary_uom_code
1695 -- , secondary_unit_of_measure
1696 , secondary_uom_code
1697 INTO l_primary_uom
1698 , l_primary_uom_code
1699 --- , l_secondary_uom
1700 , l_secondary_uom_code
1701 FROM mtl_system_items
1702 WHERE inventory_item_id = p_item_id
1703 AND organization_id = p_organization_id;
1704
1705 SELECT unit_of_measure
1706 INTO l_transfer_uom
1707 FROM mtl_item_uoms_view
1708 WHERE inventory_item_id = p_item_id
1709 AND organization_id = p_organization_id
1710 AND uom_code = l_transfer_uom_code;
1711
1712 --OPM Convergence
1713 IF l_sec_transfer_uom_code IS NOT NULL THEN
1714
1715 SELECT unit_of_measure
1716 INTO l_sec_transfer_uom
1717 FROM mtl_item_uoms_view
1718 WHERE inventory_item_id = p_item_id
1719 AND organization_id = p_organization_id
1720 AND uom_code = l_sec_transfer_uom_code;
1721
1722 END IF;
1723
1724 EXCEPTION
1725 WHEN OTHERS THEN
1726 fnd_message.set_name('INV', 'INV-NO ITEM UOM');
1727 fnd_msg_pub.add;
1728 RAISE FND_API.G_EXC_ERROR;
1729 END;
1730
1731 l_progress := 30;
1732
1733 IF (l_debug = 1) THEN
1734 print_debug('Progress: ' || l_progress || '. Fetching parent txn details', 4);
1735 END IF;
1736
1737 OPEN c_rcvtxn_detail(p_parent_txn_id, p_shipment_line_id);
1738 FETCH c_rcvtxn_detail INTO l_rcvtxn_rec;
1739 CLOSE c_rcvtxn_detail;
1740
1741 l_progress := 40;
1742
1743 l_parent_txn_id := p_parent_txn_id;
1744 l_receipt_source_code := l_rcvtxn_rec.receipt_source_code;
1745
1746 --Call the routine to validate LPN, lot, fetch the quantity available
1747 --to transfer in transaction and primary uom
1748 IF (l_debug = 1) THEN
1749 print_debug('***Current Progress: ' || l_progress || '. Calling get_avail_quantity_to_transfer with***', 4);
1750 print_debug(' Primary UOM Code: ' || l_primary_uom_code, 4);
1751 print_debug(' Transfer UOM Code: ' || l_transfer_uom_code, 4);
1752 print_debug(' Secondary UOM Code:' || l_sec_transfer_uom_code || ' (' || l_sec_transfer_uom || ')', 4);
1753 END IF;
1754
1755 get_avail_quantity_to_transfer (
1756 x_return_status => x_return_status
1757 , x_msg_count => x_msg_count
1758 , x_msg_data => x_msg_data
1759 , p_parent_txn_id => p_parent_txn_id
1760 , p_organization_id => p_organization_id
1761 , p_item_id => p_item_id
1762 , p_lpn_id => p_lpn_id
1763 , p_lot_number => p_lot_number
1764 , p_transfer_quantity => p_transfer_quantity
1765 , p_receipt_source_code => l_receipt_source_code
1766 , p_transfer_uom_code => l_transfer_uom_code
1767 , p_primary_uom_code => l_primary_uom_code
1768 , x_avail_transfer_qty => l_um_transfer_qty
1769 , x_avail_primary_qty => l_primary_qty);
1770
1771 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1772 IF (l_debug = 1) THEN
1773 print_debug('Failure in create_int_ship_transfer_rec at progress: ' ||
1774 l_progress || '. get_avail_quantity_to_transfer returned FND_API.G_EXC_ERROR', 9);
1775 END IF;
1776 RAISE FND_API.G_EXC_ERROR;
1777 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1778 IF (l_debug = 1) THEN
1779 print_debug('Failure in create_int_ship_transfer_rec at progress: ' ||
1780 l_progress || '. get_avail_quantity_to_transfer returned FND_API.G_EXC_UNEXPECTED_ERROR', 4);
1781 END IF;
1782 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1783 END IF;
1784
1785 /* OPM Convergence. Obtain the seondary available to transfer quantity
1786 using the qty available to transfer in primary uom
1787 */
1788 l_sec_um_xfer_qty := inv_convert.inv_um_convert(p_item_id,
1789 5,
1790 l_um_transfer_qty,
1791 l_transfer_uom_code,
1792 p_sec_transfer_uom_code,
1793 NULL,
1794 NULL);
1795
1796
1797 IF (l_debug = 1) THEN
1798 print_debug('create_int_ship_transfer_rec: Quantity to transfer (txn uom) ' || l_um_transfer_qty, 4);
1799 print_debug('Primary Quantity: ' || l_primary_qty ||' Secondary Quantity: ' || l_sec_um_xfer_qty || ' ' || l_secondary_uom_code, 4);
1800 END IF;
1801
1802 l_progress := 50;
1803
1804 --Populate the rcv_transactions_rec record with the quantities and UOMs
1805 --This is later populated with other values from the parent transaction and MMTT
1806 l_rcv_transaction_rec.transaction_quantity := l_um_transfer_qty;
1807 l_rcv_transaction_rec.transaction_uom := l_transfer_uom;
1808 l_rcv_transaction_rec.primary_quantity := l_primary_qty;
1809 l_rcv_transaction_rec.primary_uom := l_primary_uom;
1810 --OPM Convergence
1811 l_rcv_transaction_rec.sec_transaction_quantity := l_sec_um_xfer_qty;
1812 l_rcv_transaction_rec.secondary_uom := l_sec_transfer_uom;
1813 l_rcv_transaction_rec.secondary_uom_code := l_secondary_uom_code;
1814
1815
1816
1817 --Now create the RTI record for Transfer for Int Ship
1818 populate_transfer_rti_values (
1819 x_return_status => x_return_status
1820 , x_msg_count => x_msg_count
1821 , x_msg_data => x_msg_data
1822 , p_rcv_transaction_rec => l_rcv_transaction_rec
1823 , p_rcvtxn_rec => l_rcvtxn_rec
1824 , p_parent_txn_id => p_parent_txn_id
1825 , p_organization_id => p_organization_id
1826 , p_item_id => p_item_id
1827 , p_revision => p_revision
1828 , p_subinventory_code => p_subinventory_code
1829 , p_locator_id => p_locator_id
1830 , p_lot_control_code => p_lot_control_code
1831 , p_serial_control_code => p_serial_control_code
1832 , p_original_rti_id => p_original_rti_id
1833 , p_original_temp_id => p_original_temp_id
1834 , p_lpn_id => p_lpn_id
1835 , p_transfer_lpn_id => p_transfer_lpn_id
1836 , p_doc_type => 'INTSHIP');
1837
1838 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1839 IF (l_debug = 1) THEN
1840 print_debug('Failure in create_int_ship_transfer_rec at progress level '
1841 || l_progress || ' . populate_transfer_rti_values raised FND_API.G_EXC_ERROR', 4);
1842 END IF;
1843 RAISE FND_API.G_EXC_ERROR;
1844 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1845 IF (l_debug = 1) THEN
1846 print_debug('Failure in create_int_ship_transfer_rec at progress level '
1847 || l_progress || ' . populate_transfer_rti_values raised FND_API.G_EXC_UNEXP_ERROR', 4);
1848 END IF;
1849 END IF;
1850
1851 l_progress := 60;
1852
1853 IF (l_debug = 1) THEN
1854 print_debug('***create_int_ship_transfer_rec completed succesfully ***', 4);
1855 END IF;
1856
1857 EXCEPTION
1858 WHEN FND_API.G_EXC_ERROR THEN
1859 IF c_rcvtxn_detail%ISOPEN THEN
1860 CLOSE c_rcvtxn_detail;
1861 END IF;
1862 x_return_status := FND_API.G_RET_STS_ERROR;
1863 fnd_msg_pub.count_and_get (
1864 p_encoded => FND_API.G_FALSE
1865 , p_count => x_msg_count
1866 , p_data => x_msg_data );
1867 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1868 IF c_rcvtxn_detail%ISOPEN THEN
1869 CLOSE c_rcvtxn_detail;
1870 END IF;
1871 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1872 fnd_msg_pub.count_and_get (
1873 p_encoded => FND_API.G_FALSE
1874 , p_count => x_msg_count
1875 , p_data => x_msg_data );
1876 WHEN OTHERS THEN
1877 IF c_rcvtxn_detail%ISOPEN THEN
1878 CLOSE c_rcvtxn_detail;
1879 END IF;
1880 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1881 fnd_msg_pub.count_and_get (
1882 p_encoded => FND_API.G_FALSE
1883 , p_count => x_msg_count
1884 , p_data => x_msg_data );
1885 IF SQLCODE IS NOT NULL THEN
1886 inv_mobile_helper_functions.sql_error (
1887 'INV_RCV_STD_TRANSFER_APIS.CREATE_INT_SHIP_TRANSFER_REC',
1888 l_progress,
1889 SQLCODE);
1890 END IF;
1891 END create_int_ship_transfer_rec;
1892
1893
1894 /*-----------------------------------------------------------------------------
1895 * PROCEDURE: create_rma_transfer_rec
1896 * Description/Processing Logic:
1897 * Called when the document source type is a RMA
1898 * Fetch the parent transaction values using order line ID
1899 * Validate the transfer quantity with rcv_supply for the parent transaction
1900 * Initialize the RTI record with the quantity and primary quantity
1901 * Call the populate_transfer_rti_values rotuine to create the Transfer RTI
1902 *
1903 * Output Parameters:
1904 * x_return_status
1905 * Return status indicating Success (S), Error (E), Unexpected Error (U)
1906 * x_msg_count
1907 * Number of messages in message list
1908 * x_msg_data
1909 * Stacked messages text
1910 *
1911 * Input Parameters:
1912 * p_organization_id - Organization ID
1913 * p_oe_order_header_id - Sales Order Header ID
1914 * p_oe_order_line_id - Sales Order Line ID
1915 * p_parent_txn_id - Transaction ID of the parent transaction
1916 * p_item_id - Item Being transferred
1917 * p_revision - Item Revision
1918 * p_subinventory_code - Destination receiving subinventory code
1919 * p_locator_id - Destination receiving locator ID
1920 * p_transfer_quantity - Quantity to be transferred
1921 * p_transfer_uom_code - UOM code of the quantity being tranferred
1922 * p_lot_control_code - Lot Control Code of the item
1923 * p_serial_control_code - Serial Control Code of the item
1924 * p_original_rti_id - Interface Transaction Id for lot/serial split
1925 * p_original_temp_id - Transaction Temp ID of the MMTT being putaway
1926 * p_lpn_id - LPN ID of the move order line
1927 * p_transfer_lpn_id - LPN ID of the LPN being dropped into
1928 *
1929 * Returns: NONE
1930 *---------------------------------------------------------------------------*/
1931 PROCEDURE create_rma_transfer_rec (
1932 x_return_status OUT NOCOPY VARCHAR2
1933 , x_msg_count OUT NOCOPY VARCHAR2
1934 , x_msg_data OUT NOCOPY VARCHAR2
1935 , p_organization_id IN NUMBER
1936 , p_oe_order_header_id IN NUMBER
1937 , p_oe_order_line_id IN NUMBER
1938 , p_parent_txn_id IN NUMBER
1939 , p_item_id IN NUMBER
1940 , p_revision IN VARCHAR2
1941 , p_subinventory_code IN VARCHAR2
1942 , p_locator_id IN NUMBER
1943 , p_transfer_quantity IN NUMBER
1944 , p_transfer_uom_code IN VARCHAR2
1945 , p_lot_control_code IN NUMBER
1946 , p_serial_control_code IN NUMBER
1947 , p_original_rti_id IN NUMBER DEFAULT NULL
1948 , p_original_temp_id IN NUMBER DEFAULT NULL
1949 , p_lot_number IN VARCHAR2 DEFAULT NULL
1950 , p_lpn_id IN NUMBER DEFAULT NULL
1951 , p_transfer_lpn_id IN NUMBER DEFAULT NULL
1952 , p_sec_transfer_quantity IN NUMBER DEFAULT NULL --OPM Convergence
1953 , p_sec_transfer_uom_code IN VARCHAR2 DEFAULT NULL--OPM Convergence
1954 ) IS
1955 CURSOR c_rcvtxn_detail( v_rcv_txn_id NUMBER,
1956 v_oe_order_line_id NUMBER) IS
1957 SELECT rs.from_organization_id from_organization_id
1958 , rs.to_organization_id to_organization_id
1959 , rt.source_document_code source_document_code
1960 , rsh.receipt_source_code receipt_source_code
1961 , rs.rcv_transaction_id rcv_transaction_id
1962 , rt.transaction_date transaction_date
1963 , rt.transaction_type transaction_type
1964 , rt.primary_unit_of_measure primary_unit_of_measure
1965 , rt.primary_quantity primary_quantity
1966 , to_number(NULL) po_header_id
1967 , to_number(NULL) po_revision_num
1968 , to_number(NULL) po_release_id
1969 , rsh.vendor_id vendor_id
1970 , rt.vendor_site_id vendor_site_id
1971 , to_number(NULL) po_line_id
1972 , to_number(NULL) po_unit_price
1973 , rsl.category_id category_id
1974 , rs.item_id item_id
1975 , msi.serial_number_control_code serial_number_control_code
1976 , msi.lot_control_code lot_control_code
1977 , rs.item_revision item_revision
1978 , to_number(NULL) po_line_location_id
1979 , to_number(NULL) po_distribution_id
1980 , rt.employee_id employee_id
1981 , rsl.comments comments
1982 , to_number(NULL) req_header_id
1983 , to_number(NULL) req_line_id
1984 , rs.shipment_header_id shipment_header_id
1985 , rs.shipment_line_id shipment_line_id
1986 , rsh.packing_slip packing_slip
1987 , rsl.government_context government_context
1988 , rsl.ussgl_transaction_code ussgl_transaction_code
1989 , rt.inspection_status_code inspection_status_code
1990 , rt.inspection_quality_code inspectin_quality_code
1991 , rt.vendor_lot_num vendor_lot_num
1992 , '' vendor_item_number
1993 , rt.substitute_unordered_code substitute_unordered_code
1994 , rt.routing_header_id routing_id
1995 , rt.routing_step_id routing_step_id
1996 , rt.reason_id reason_id
1997 , rt.currency_code currency_code
1998 , rt.currency_conversion_rate currency_conversion_rate
1999 , rt.currency_conversion_date currency_conversion_date
2000 , rt.currency_conversion_type currency_conversion_type
2001 , rsl.req_distribution_id req_distribution_id
2002 , rs.destination_type_code destination_type_code_hold
2003 , to_number(NULL) final_destination_type_code
2004 , rt.location_id location_id
2005 , to_number(NULL) final_deliver_to_person_id
2006 , to_number(NULL) final_deliver_to_location_id
2007 , rsl.to_subinventory subinventory
2008 , msi.un_number_id un_number_id
2009 , msi.hazard_class_id hazard_class_id
2010 , rs.creation_date creation_date
2011 , rt.attribute_category attribute_category
2012 , rt.attribute1 attribute1
2013 , rt.attribute2 attribute2
2014 , rt.attribute3 attribute3
2015 , rt.attribute4 attribute4
2016 , rt.attribute5 attribute5
2017 , rt.attribute6 attribute6
2018 , rt.attribute7 attribute7
2019 , rt.attribute8 attribute8
2020 , rt.attribute9 attribute9
2021 , rt.attribute10 attribute10
2022 , rt.attribute11 attribute11
2023 , rt.attribute12 attribute12
2024 , rt.attribute13 attribute13
2025 , rt.attribute14 attribute14
2026 , rt.attribute15 attribute15
2027 , rt.qa_collection_id qa_collection_id
2028 , rs.oe_order_header_id oe_order_header_id
2029 , rs.oe_order_line_id oe_order_line_id
2030 , rsh.customer_id customer_id
2031 , rsh.customer_site_id customer_site_id
2032 , to_number(NULL) wip_entity_id
2033 , to_number(NULL) po_operation_seq_num
2034 , to_number(NULL) po_resource_seq_num
2035 , to_number(NULL) wip_repetitive_schedule_id
2036 , to_number(NULL) wip_line_id
2037 , to_number(NULL) bom_resource_id
2038 , to_char(NULL) final_subinventory
2039 , rt.secondary_quantity secondary_quantity --OPM Convergence
2040 , rt.secondary_unit_of_measure secondary_uom --OPM Convergence
2041 --The following columns are needed for matching in cases where no LPN is involved
2042 , rs.to_subinventory from_subinventory_code
2043 , rs.to_locator_id from_locator_id
2044 FROM rcv_transactions rt
2045 , rcv_supply rs
2046 , rcv_shipment_headers rsh
2047 , rcv_shipment_lines rsl
2048 , mtl_system_items msi
2049 WHERE rs.rcv_transaction_id = v_rcv_txn_id
2050 AND rs.to_organization_id = p_organization_id
2051 AND rs.rcv_transaction_id = rt.transaction_id
2052 AND rs.oe_order_line_id = v_oe_order_line_id
2053 AND rs.shipment_line_id = rsl.shipment_line_id
2054 AND rs.shipment_header_id = rsh.shipment_header_id
2055 AND msi.organization_id = p_organization_id
2056 AND msi.inventory_item_id = rs.item_id;
2057
2058 --Local variables
2059 l_parent_transaction_id NUMBER; --Transaction ID of the parent RT record
2060 l_con_transfer_qty NUMBER; --Qty converted in MOL UOM code
2061 l_primary_qty NUMBER; --Quantity in primay uom code
2062 l_primary_uom mtl_units_of_measure.unit_of_measure%TYPE;
2063 l_transfer_uom mtl_units_of_measure.unit_of_measure%TYPE;
2064 l_primary_uom_code mtl_units_of_measure.uom_code%TYPE;
2065 l_transfer_uom_code mtl_units_of_measure.uom_code%TYPE;
2066 l_rcv_transaction_rec INV_RCV_STD_DELIVER_APIS.rcvtxn_transaction_rec_tp;
2067 l_rcvtxn_rec INV_RCV_STD_DELIVER_APIS.rcvtxn_enter_rec_cursor_rec;
2068 l_receipt_source_code RCV_SHIPMENT_HEADERS.RECEIPT_SOURCE_CODE%TYPE;
2069 l_parent_txn_id NUMBER;
2070 l_um_transfer_qty NUMBER; --Transfer quantity converted to MOL UOM
2071
2072 l_progress NUMBER; --Index to track progress and log error
2073 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'), 0);
2074
2075 --OPM Convergence
2076 l_sec_transfer_uom_code mtl_units_of_measure.uom_code%TYPE; --OPM Convergence
2077 l_secondary_uom mtl_units_of_measure.unit_of_measure%TYPE;
2078 l_sec_transfer_uom mtl_units_of_measure.unit_of_measure%TYPE;
2079 l_secondary_uom_code mtl_units_of_measure.uom_code%TYPE;
2080 l_sec_um_xfer_qty NUMBER;
2081 l_secondary_qty NUMBER;
2082
2083 BEGIN
2084
2085 --Initialize the return status to succes
2086 x_return_status := FND_API.G_RET_STS_SUCCESS;
2087
2088 l_progress := 10;
2089
2090 IF (l_debug = 1) THEN
2091 print_debug('Entered create_rma_transfer_rec: ' || l_progress || ' ' ||
2092 TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 3);
2093 print_debug('************Input parameters are************', 3);
2094 print_debug(' p_oe_order_header_id ===> ' || p_oe_order_header_id, 3);
2095 print_debug(' p_oe_order_line_id ===> ' || p_oe_order_line_id, 3);
2096 print_debug('********************************************', 3);
2097 END IF;
2098
2099 l_progress := 20;
2100
2101 IF (l_debug = 1) THEN
2102 print_debug('Fetching the units of measure for the item', 4);
2103 END IF;
2104
2105 --Fetch the primary uom, primary uom code and transaction uom for the item
2106 --This is because receiving works unit of measure than uom code
2107 BEGIN
2108 l_transfer_uom_code := p_transfer_uom_code;
2109 l_sec_transfer_uom_code := p_sec_transfer_uom_code;
2110 SELECT primary_unit_of_measure
2111 , primary_uom_code
2112 -- , secondary_unit_of_measure --OPM Convergence
2113 , secondary_uom_code
2114 INTO l_primary_uom
2115 , l_primary_uom_code
2116 -- , l_secondary_uom
2117 , l_secondary_uom_code
2118 FROM mtl_system_items
2119 WHERE inventory_item_id = p_item_id
2120 AND organization_id = p_organization_id;
2121
2122 SELECT unit_of_measure
2123 INTO l_transfer_uom
2124 FROM mtl_item_uoms_view
2125 WHERE inventory_item_id = p_item_id
2126 AND organization_id = p_organization_id
2127 AND uom_code = l_transfer_uom_code;
2128
2129 --OPM Convergence
2130 IF l_sec_transfer_uom_code IS NOT NULL THEN
2131
2132 SELECT unit_of_measure
2133 INTO l_sec_transfer_uom
2134 FROM mtl_item_uoms_view
2135 WHERE inventory_item_id = p_item_id
2136 AND organization_id = p_organization_id
2137 AND uom_code = l_sec_transfer_uom_code;
2138
2139 END IF;
2140
2141
2142 EXCEPTION
2143 WHEN OTHERS THEN
2144 fnd_message.set_name('INV', 'INV-NO ITEM UOM');
2145 fnd_msg_pub.add;
2146 RAISE FND_API.G_EXC_ERROR;
2147 END;
2148
2149 l_progress := 30;
2150
2151 IF (l_debug = 1) THEN
2152 print_debug('Fetching the parent transaction details', 4);
2153 END IF;
2154
2155 --Fetch the parent transaction details
2156 OPEN c_rcvtxn_detail(p_parent_txn_id, p_oe_order_line_id);
2157 FETCH c_rcvtxn_detail INTO l_rcvtxn_rec;
2158 CLOSE c_rcvtxn_detail;
2159
2160 l_progress := 40;
2161
2162 l_parent_txn_id := p_parent_txn_id;
2163 l_receipt_source_code := l_rcvtxn_rec.receipt_source_code;
2164
2165 --Call the routine to validate LPN, Lot and return the available quantity
2166 --to transfer in transaction UOM and primary UOM
2167 IF (l_debug = 1) THEN
2168 print_debug('Current Progress: ' || l_progress || '. Calling get_avail_quantity_to_transfer with', 4);
2169 print_debug(' Primary UOM Code: ' || l_primary_uom_code, 4);
2170 print_debug(' Transfer UOM Code:' || l_transfer_uom_code, 4);
2171 print_debug(' Secondary UOM Code:' || l_sec_transfer_uom_code || ' (' || l_sec_transfer_uom || ')', 4);
2172 END IF;
2173
2174 get_avail_quantity_to_transfer (
2175 x_return_status => x_return_status
2176 , x_msg_count => x_msg_count
2177 , x_msg_data => x_msg_data
2178 , p_parent_txn_id => p_parent_txn_id
2179 , p_organization_id => p_organization_id
2180 , p_item_id => p_item_id
2181 , p_lpn_id => p_lpn_id
2182 , p_lot_number => p_lot_number
2183 , p_transfer_quantity => p_transfer_quantity
2184 , p_receipt_source_code => l_receipt_source_code
2185 , p_transfer_uom_code => l_transfer_uom_code
2186 , p_primary_uom_code => l_primary_uom_code
2187 , x_avail_transfer_qty => l_um_transfer_qty
2188 , x_avail_primary_qty => l_primary_qty);
2189
2190 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2191 IF (l_debug = 1) THEN
2192 print_debug('Failure in create_rma_transfer_rec at progress level '
2193 || l_progress || ' . get_avail_quantity_to_transfer raised FND_API.G_EXC_ERROR', 4);
2194 END IF;
2195 RAISE FND_API.G_EXC_ERROR;
2196 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2197 IF (l_debug = 1) THEN
2198 print_debug('Failure in create_rma_transfer_rec at progress level '
2199 || l_progress || ' . get_avail_quantity_to_transfer raised FND_API.G_EXC_UNEXP_ERROR', 4);
2200 END IF;
2201 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2202 END IF;
2203 /* OPM Convergence. Obtain the seondary available to transfer quantity
2204 using the qty available to transfer in primary uom
2205 */
2206 l_sec_um_xfer_qty := inv_convert.inv_um_convert(p_item_id,
2207 5,
2208 l_um_transfer_qty,
2209 l_transfer_uom_code,
2210 p_sec_transfer_uom_code,
2211 NULL,
2212 NULL);
2213
2214 l_progress := 60;
2215 IF (l_debug = 1) THEN
2216 print_debug('create_rma_transfer_rec: Quantity to transfer (txn uom): ' || l_um_transfer_qty, 4);
2217 print_debug('Primary Quantity: ' || l_primary_qty ||' Secondary Quantity: ' || l_sec_um_xfer_qty || ' ' || l_secondary_uom_code, 4);
2218 END IF;
2219
2220 --Populate the rcv_transactions_rec record with the quantities and UOMs
2221 l_rcv_transaction_rec.transaction_quantity := l_um_transfer_qty;
2222 l_rcv_transaction_rec.transaction_uom := l_transfer_uom;
2223 l_rcv_transaction_rec.primary_quantity := l_primary_qty;
2224 l_rcv_transaction_rec.primary_uom := l_primary_uom;
2225
2226 --OPM Convergence
2227 l_rcv_transaction_rec.sec_transaction_quantity := l_sec_um_xfer_qty;
2228 l_rcv_transaction_rec.secondary_uom := l_sec_transfer_uom;
2229 l_rcv_transaction_rec.secondary_uom_code := l_secondary_uom_code;
2230
2231 --Now call the procedure to create the RTI record for transfer
2232 l_progress := 60;
2233 IF (l_debug = 1) THEN
2234 print_debug('Calling populate_transfer_rti_values', 4);
2235 END IF;
2236
2237 populate_transfer_rti_values (
2238 x_return_status => x_return_status
2239 , x_msg_count => x_msg_count
2240 , x_msg_data => x_msg_data
2241 , p_rcv_transaction_rec => l_rcv_transaction_rec
2242 , p_rcvtxn_rec => l_rcvtxn_rec
2243 , p_parent_txn_id => p_parent_txn_id
2244 , p_organization_id => p_organization_id
2245 , p_item_id => p_item_id
2246 , p_revision => p_revision
2247 , p_subinventory_code => p_subinventory_code
2248 , p_locator_id => p_locator_id
2249 , p_lot_control_code => p_lot_control_code
2250 , p_serial_control_code => p_serial_control_code
2251 , p_original_rti_id => p_original_rti_id
2252 , p_original_temp_id => p_original_temp_id
2253 , p_lpn_id => p_lpn_id
2254 , p_transfer_lpn_id => p_transfer_lpn_id
2255 , p_doc_type => 'RMA');
2256
2257 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2258 IF (l_debug = 1) THEN
2259 print_debug('Failure in create_rma_transfer_rec at progress level '
2260 || l_progress || ' . populate_transfer_rti_values raised FND_API.G_EXC_ERROR', 4);
2261 END IF;
2262 RAISE FND_API.G_EXC_ERROR;
2263 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2264 IF (l_debug = 1) THEN
2265 print_debug('Failure in create_rma_transfer_rec at progress level '
2266 || l_progress || ' . populate_transfer_rti_values raised FND_API.G_EXC_UNEXP_ERROR', 4);
2267 END IF;
2268 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2269 END IF;
2270
2271 l_progress := 70;
2272 IF (l_debug = 1) THEN
2273 print_debug('***create_rma_transfer_rec completed successfully***', 4);
2274 END IF;
2275
2276 EXCEPTION
2277 WHEN FND_API.G_EXC_ERROR THEN
2278 IF c_rcvtxn_detail%ISOPEN THEN
2279 CLOSE c_rcvtxn_detail;
2280 END IF;
2281 x_return_status := FND_API.G_RET_STS_ERROR;
2282 fnd_msg_pub.count_and_get (
2283 p_encoded => FND_API.G_FALSE
2284 , p_count => x_msg_count
2285 , p_data => x_msg_data );
2286 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2287 IF c_rcvtxn_detail%ISOPEN THEN
2288 CLOSE c_rcvtxn_detail;
2289 END IF;
2290 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2291 fnd_msg_pub.count_and_get (
2292 p_encoded => FND_API.G_FALSE
2293 , p_count => x_msg_count
2294 , p_data => x_msg_data );
2295 WHEN OTHERS THEN
2296 IF c_rcvtxn_detail%ISOPEN THEN
2297 CLOSE c_rcvtxn_detail;
2298 END IF;
2299 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2300 fnd_msg_pub.count_and_get (
2301 p_encoded => FND_API.G_FALSE
2302 , p_count => x_msg_count
2303 , p_data => x_msg_data );
2304 IF SQLCODE IS NOT NULL THEN
2305 inv_mobile_helper_functions.sql_error (
2306 'INV_RCV_STD_TRANSFER_APIS.CREATE_RMA_TRANSFER_REC',
2307 l_progress,
2308 SQLCODE);
2309 END IF;
2310 END create_rma_transfer_rec;
2311
2312
2313 /*------------------------------------------------------------------------------
2314 * PROCEDURE: create_transfer_rcvtxn_rec
2315 * Description:
2316 * This procedure creates a RCV_TRANSACTIONS_INTERFACE record for a Receiving
2317 * Transfer Transaction<br>
2318 * Called from the Mobile putaway UI and packing workbench
2319 *
2320 * Output Parameters
2321 * x_return_status
2322 * Return status indicating Success (S), Error (E), Unexpected Error (U)
2323 * x_msg_count
2324 * Number of messages in message list
2325 * x_msg_data
2326 * Stacked messages text
2327 *
2328 * Input Parameters
2329 * p_organization_id - Organization ID
2330 * p_parent_txn_id - Transaction ID of the parent transaction
2331 * p_reference_id - Reference ID of the move order line
2332 * p_reference - Reference Indicator for the source doc
2333 * p_reference_type_code - Reference Type Code
2334 * p_item_id - Item Being transferred
2335 * p_revision - Item Revision
2336 * p_subinventory_code - Destination receiving subinventory code
2337 * p_locator_id - Destination receiving locator ID
2338 * p_transfer_quantity - Quantity to be transferred
2339 * p_transfer_uom_code - UOM code of the quantity being tranferred
2340 * p_lot_control_code - Lot Control Code of the item
2341 * p_serial_control_code - Serial Control Code of the item
2342 * p_original_rti_id - Original RTI ID for lot/serial split
2343 * p_original_temp_id - Transaction Temp ID of the putaway MMTT
2344 * p_lot_number - Lot Number on the move order line
2345 * p_lpn_id - LPN ID of the move order line
2346 * p_transfer_lpn_id - LPN ID of the LPN being dropped into
2347 *
2348 * Returns: NONE
2349 *---------------------------------------------------------------------------*/
2350 PROCEDURE create_transfer_rcvtxn_rec(
2351 x_return_status OUT NOCOPY VARCHAR2
2352 , x_msg_count OUT NOCOPY NUMBER
2353 , x_msg_data OUT NOCOPY VARCHAR2
2354 , p_organization_id IN NUMBER
2355 , p_parent_txn_id IN NUMBER
2356 , p_reference_id IN NUMBER
2357 , p_reference IN VARCHAR2
2358 , p_reference_type_code IN NUMBER
2359 , p_item_id IN NUMBER
2360 , p_revision IN VARCHAR2
2361 , p_subinventory_code IN VARCHAR2
2362 , p_locator_id IN NUMBER
2363 , p_transfer_quantity IN NUMBER
2364 , p_transfer_uom_code IN VARCHAR2
2365 , p_lot_control_code IN NUMBER
2366 , p_serial_control_code IN NUMBER
2367 , p_original_rti_id IN NUMBER DEFAULT NULL
2368 , p_original_temp_id IN NUMBER DEFAULT NULL
2369 , p_lot_number IN VARCHAR2 DEFAULT NULL
2370 , p_lpn_id IN NUMBER DEFAULT NULL
2371 , p_transfer_lpn_id IN NUMBER DEFAULT NULL
2372 , p_sec_transfer_quantity IN NUMBER DEFAULT NULL --OPM Convergence
2373 , p_sec_transfer_uom_code IN VARCHAR2 DEFAULT NULL ) --OPM Convergence
2374 IS
2375
2376 --Local Variables
2377 l_po_header_id NUMBER;
2378 l_po_release_id NUMBER;
2379 l_po_line_id NUMBER;
2380 l_shipment_header_id NUMBER;
2381 l_oe_order_header_id NUMBER;
2382 l_group_id NUMBER; --Interface Group ID
2383 l_progress NUMBER; --Index to track progress and log error
2384 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'), 0);
2385 l_message VARCHAR2(1000);
2386
2387 l_sec_uom_code VARCHAR2(3) := p_sec_transfer_uom_code;
2388
2389 BEGIN
2390
2391 --Initialize the return status
2392 x_return_status := FND_API.G_RET_STS_SUCCESS;
2393
2394 IF (l_debug = 1) THEN
2395 print_debug('Entered create_transfer_rcvtxn_rec 10: ' ||
2396 to_char(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 1);
2397 END IF;
2398
2399 l_progress := 20;
2400
2401
2402 --Print the input parameters
2403 IF (l_debug = 1) THEN
2404 print_debug('******Input parameters passed to the procedure:****** ', 3);
2405 print_debug(' p_lpn_id ===> ' || p_lpn_id, 3);
2406 print_debug(' p_organization_id ===> ' || p_organization_id, 3);
2407 print_debug(' p_parent_txn_id ===> ' || p_parent_txn_id, 3);
2408 print_debug(' p_reference_id ===> ' || p_reference_id, 3);
2409 print_debug(' p_reference ===> ' || p_reference, 3);
2410 print_debug(' p_reference_type_code ===> ' || p_reference_type_code, 3);
2411 print_debug(' p_item_id ===> ' || p_item_id, 3);
2412 print_debug(' p_revision ===> ' || p_revision, 3);
2413 print_debug(' p_subinventory_code ===> ' || p_subinventory_code, 3);
2414 print_debug(' p_locator_id ===> ' || p_locator_id, 3);
2415 print_debug(' p_transfer_quantity ===> ' || p_transfer_quantity, 3);
2416 print_debug(' p_transfer_uom_code ===> ' || p_transfer_uom_code, 3);
2417 print_debug(' p_original_rti_id ===> ' || p_original_rti_id, 3);
2418 print_debug(' p_lot_control_code ===> ' || p_lot_control_code, 3);
2419 print_debug(' p_serial_control_code ===> ' || p_serial_control_code, 3);
2420 print_debug(' p_original_temp_id ===> ' || p_original_temp_id, 3);
2421 print_debug(' p_lot_number ===> ' || p_lot_number, 3);
2422 print_debug(' p_secondary_quantity ===> ' || p_sec_transfer_quantity,3);
2423 print_debug(' p_secondary_uom_code ===> ' || l_sec_uom_code,3);
2424 print_debug('***************************************************** ', 3);
2425 END IF;
2426
2427 IF(l_sec_uom_code IS NULL) THEN
2428 IF (l_debug = 1) THEN
2429 print_debug('Fetching the secondary units of measure for the item', 4);
2430 END IF;
2431
2432 SELECT secondary_uom_code
2433 INTO l_sec_uom_code
2434 FROM mtl_system_items
2435 WHERE inventory_item_id = p_item_id
2436 AND organization_id = p_organization_id;
2437
2438 IF (l_debug = 1) THEN
2439 print_debug(' p_secondary_uom_code ===> ' || l_sec_uom_code,3);
2440 END IF;
2441
2442 END IF;
2443
2444 --Initialize the receiving parameters for the organization id
2445 BEGIN
2446 inv_rcv_common_apis.init_startup_values(p_organization_id);
2447 EXCEPTION
2448 WHEN OTHERS THEN
2449 IF (l_debug = 1) THEN
2450 print_debug('Failed at progress : ' || l_progress ||
2451 ' . Error in initializing receiving parameters', 4);
2452 print_debug('err: ' || substr(sqlerrm,1,140),4);
2453 END IF;
2454 fnd_message.set_name('INV', 'INV_RCV_PARAM');
2455 fnd_msg_pub.add;
2456
2457 RAISE FND_API.G_EXC_ERROR;
2458 END;
2459
2460 --First check if the transaction date satisfies the validation.
2461 --If the transaction date is invalid then error out the transaction
2462 IF inv_rcv_common_apis.g_po_startup_value.sob_id IS NULL THEN
2463 --Bug # 3444214
2464 --For better performance, using hr_organization_information to fetch set_of_books_id
2465 SELECT TO_NUMBER(hoi.org_information1)
2466 INTO inv_rcv_common_apis.g_po_startup_value.sob_id
2467 FROM hr_organization_information hoi
2468 WHERE hoi.organization_id = p_organization_id
2469 AND (hoi.org_information_context || '') = 'Accounting Information';
2470 END IF;
2471
2472 l_progress := 15;
2473
2474 inv_rcv_common_apis.validate_trx_date(
2475 p_trx_date => SYSDATE
2476 , p_organization_id => p_organization_id
2477 , p_sob_id => inv_rcv_common_apis.g_po_startup_value.sob_id
2478 , x_return_status => x_return_status
2479 , x_error_code => l_message
2480 );
2481
2482 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2483 RETURN;
2484 END IF;
2485
2486 l_progress := 20;
2487
2488 --Fetch the group_id from global variable. Set the value for the current set
2489 IF (l_debug = 1) THEN
2490 print_debug('Setting group_id for the current txn', 4);
2491 END IF;
2492
2493 IF inv_rcv_common_apis.g_rcv_global_var.interface_group_id IS NULL THEN
2494 SELECT rcv_interface_groups_s.NEXTVAL
2495 INTO l_group_id
2496 FROM sys.dual;
2497 inv_rcv_common_apis.g_rcv_global_var.interface_group_id := l_group_id;
2498 ELSE
2499 l_group_id := inv_rcv_common_apis.g_rcv_global_var.interface_group_id;
2500 END IF;
2501
2502 --Set the package variable for uom_code
2503 g_transfer_uom_code := p_transfer_uom_code;
2504
2505 --Check the document reference from the Move Order line and call the
2506 --appropriate procedures
2507
2508 --If the source document is a Purchase Order
2509 IF p_reference_type_code = 4 AND p_reference = 'PO_LINE_LOCATION_ID' THEN
2510
2511 l_progress := 30;
2512
2513 BEGIN
2514 SELECT po_header_id
2515 , po_release_id
2516 , po_line_id
2517 INTO l_po_header_id
2518 , l_po_release_id
2519 , l_po_line_id
2520 FROM po_line_locations
2521 WHERE line_location_id = p_reference_id;
2522 EXCEPTION
2523 WHEN OTHERS THEN
2524 IF (l_debug = 1) THEN
2525 print_debug('create_transfer_rcvtxn_rec 20: Error retrieving po info.',1);
2526 print_debug('SQLCODE:'||SQLCODE||' SQLERRM:'||Sqlerrm,1);
2527 END IF;
2528 RAISE fnd_api.g_exc_error;
2529 END;
2530
2531 IF (l_debug = 1) THEN
2532 print_debug('Current Progress: ' || l_progress || '. Calling create_po_transfer_rec', 4);
2533 END IF;
2534
2535 create_po_transfer_rec (
2536 x_return_status => x_return_status
2537 , x_msg_count => x_msg_count
2538 , x_msg_data => x_msg_data
2539 , p_organization_id => p_organization_id
2540 , p_po_header_id => l_po_header_id
2541 , p_po_release_id => l_po_release_id
2542 , p_po_line_id => l_po_line_id
2543 , p_po_line_location_id => p_reference_id
2544 , p_parent_txn_id => p_parent_txn_id
2545 , p_item_id => p_item_id
2546 , p_revision => p_revision
2547 , p_subinventory_code => p_subinventory_code
2548 , p_locator_id => p_locator_id
2549 , p_transfer_quantity => p_transfer_quantity
2550 , p_transfer_uom_code => p_transfer_uom_code
2551 , p_lot_control_code => p_lot_control_code
2552 , p_serial_control_code => p_serial_control_code
2553 , p_original_rti_id => p_original_rti_id
2554 , p_original_temp_id => p_original_temp_id
2555 , p_lot_number => p_lot_number
2556 , p_lpn_id => p_lpn_id
2557 , p_transfer_lpn_id => p_transfer_lpn_id
2558 , p_sec_transfer_quantity => p_sec_transfer_quantity --OPM Convergence
2559 , p_sec_transfer_uom_code => l_sec_uom_code ); --OPM Convergence
2560
2561 --Check the return status
2562 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2563 fnd_message.set_name('INV', 'INV_RCV_CREATE_PO_RTI_FAIL');
2564 fnd_msg_pub.add;
2565
2566 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2567 IF (l_debug = 1) THEN
2568 print_debug('Failure in create_transfer_rcvtxn_rec at progress level '
2569 || l_progress || ' . create_po_transfer_rec raised FND_API.G_EXC_ERROR', 4);
2570 END IF;
2571 RAISE FND_API.G_EXC_ERROR;
2572 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2573 IF (l_debug = 1) THEN
2574 print_debug('Failure in create_transfer_rcvtxn_rec at progress level '
2575 || l_progress || ' . create_po_transfer_rec raised FND_API.G_EXC_UNEXP_ERROR', 4);
2576 END IF;
2577 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2578 END IF;
2579 END IF; --END IF check return status
2580
2581 IF l_debug = 1 THEN
2582 print_debug('RTI record created successfully for PO', 4);
2583 END IF;
2584
2585 --If the source document is an Internal Requisition/Intransit Shipment
2586
2587 --Bug 5662935 :Added reference_type_code = 6 in the following condition.
2588 --After the bug fix done as a part of bug 4996680,reference_type_code=6 is
2589 --for source document 'Intransit Shipment' and reference_type_code = 8
2590 --is for source document 'Internal Requisition'.
2591 ELSIF p_reference_type_code in (6,8) AND p_reference = 'SHIPMENT_LINE_ID' THEN
2592
2593 l_progress := 40;
2594
2595 SELECT shipment_header_id
2596 INTO l_shipment_header_id
2597 FROM rcv_shipment_lines
2598 WHERE shipment_line_id = p_reference_id;
2599
2600 IF (l_debug = 1) THEN
2601 print_debug('Current Progress: ' || l_progress ||
2602 ' . Calling create_int_ship_transfer_rec', 4);
2603 END IF;
2604
2605 create_int_ship_transfer_rec (
2606 x_return_status => x_return_status
2607 , x_msg_count => x_msg_count
2608 , x_msg_data => x_msg_data
2609 , p_organization_id => p_organization_id
2610 , p_shipment_header_id => l_shipment_header_id
2611 , p_shipment_line_id => p_reference_id
2612 , p_parent_txn_id => p_parent_txn_id
2613 , p_item_id => p_item_id
2614 , p_revision => p_revision
2615 , p_subinventory_code => p_subinventory_code
2616 , p_locator_id => p_locator_id
2617 , p_transfer_quantity => p_transfer_quantity
2618 , p_transfer_uom_code => p_transfer_uom_code
2619 , p_lot_control_code => p_lot_control_code
2620 , p_serial_control_code => p_serial_control_code
2621 , p_original_temp_id => p_original_temp_id
2622 , p_original_rti_id => p_original_rti_id
2623 , p_lot_number => p_lot_number
2624 , p_lpn_id => p_lpn_id
2625 , p_transfer_lpn_id => p_transfer_lpn_id
2626 , p_sec_transfer_quantity => p_sec_transfer_quantity --OPM Convergence
2627 , p_sec_transfer_uom_code => l_sec_uom_code ); --OPM Convergence
2628
2629 --Check the return status
2630 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2631 fnd_message.set_name('INV', 'INV_RCV_CRT_INSHP_RTI_FAIL');
2632 fnd_msg_pub.add;
2633
2634 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2635 IF (l_debug = 1) THEN
2636 print_debug('Failure in create_transfer_rcvtxn_rec at progress level '
2637 || l_progress || ' . create_int_ship_transfer_rec raised FND_API.G_EXC_ERROR', 4);
2638 END IF;
2639 RAISE FND_API.G_EXC_ERROR;
2640 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2641 IF (l_debug = 1) THEN
2642 print_debug('Failure in create_transfer_rcvtxn_rec at progress level '
2643 || l_progress || ' . create_int_ship_transfer_rec raised FND_API.G_EXC_UNEXP_ERROR', 4);
2644 END IF;
2645 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2646 END IF;
2647 END IF; --END IF check return status
2648
2649 IF l_debug = 1 THEN
2650 print_debug('RTI record created successfully for INT SHIP.', 4);
2651 END IF;
2652
2653 --If the source document is an RMA
2654 ELSIF p_reference_type_code = 7 AND p_reference = 'ORDER_LINE_ID' THEN
2655
2656 l_progress := 50;
2657
2658 SELECT header_id
2659 INTO l_oe_order_header_id
2660 FROM oe_order_lines_all
2661 WHERE line_id = p_reference_id;
2662
2663 IF (l_debug = 1) THEN
2664 print_debug('Current Progress: ' || l_progress ||
2665 ' . Calling create_rma_transfer_rec', 4);
2666 END IF;
2667
2668 create_rma_transfer_rec (
2669 x_return_status => x_return_status
2670 , x_msg_count => x_msg_count
2671 , x_msg_data => x_msg_data
2672 , p_organization_id => p_organization_id
2673 , p_oe_order_header_id => l_oe_order_header_id
2674 , p_oe_order_line_id => p_reference_id
2675 , p_parent_txn_id => p_parent_txn_id
2676 , p_item_id => p_item_id
2677 , p_revision => p_revision
2678 , p_subinventory_code => p_subinventory_code
2679 , p_locator_id => p_locator_id
2680 , p_transfer_quantity => p_transfer_quantity
2681 , p_transfer_uom_code => p_transfer_uom_code
2682 , p_lot_control_code => p_lot_control_code
2683 , p_serial_control_code => p_serial_control_code
2684 , p_original_rti_id => p_original_rti_id
2685 , p_original_temp_id => p_original_temp_id
2686 , p_lot_number => p_lot_number
2687 , p_lpn_id => p_lpn_id
2688 , p_transfer_lpn_id => p_transfer_lpn_id
2689 , p_sec_transfer_quantity => p_sec_transfer_quantity --OPM Convergence
2690 , p_sec_transfer_uom_code => l_sec_uom_code ); --OPM Convergence
2691
2692 --Check the return status
2693 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2694 fnd_message.set_name('INV', 'INV_RCV_CREATE_RMA_RTI_FAIL');
2695 fnd_msg_pub.add;
2696
2697 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2698 IF (l_debug = 1) THEN
2699 print_debug('Failure in create_transfer_rcvtxn_rec at progress level '
2700 || l_progress || ' . create_rma_transfer_rec raised FND_API.G_EXC_ERROR', 4);
2701 END IF;
2702 RAISE FND_API.G_EXC_ERROR;
2703 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2704 IF (l_debug = 1) THEN
2705 print_debug('Failure in create_transfer_rcvtxn_rec at progress level '
2706 || l_progress || ' . create_rma_transfer_rec raised FND_API.G_EXC_UNEXP_ERROR', 4);
2707 END IF;
2708 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2709 END IF;
2710 END IF; --END IF check return status
2711
2712 IF l_debug = 1 THEN
2713 print_debug('RTI record created successfully for RMA.', 4);
2714 END IF;
2715
2716 --If the move order line reference is invalid then scream
2717 ELSE
2718 print_debug('Failed at : ' || l_progress || ' . Invalid Reference passed : '
2719 || p_reference_type_code, 4);
2720 fnd_message.set_name('INV', 'INV-BAD SOURCE TYPE');
2721 fnd_msg_pub.add;
2722 RAISE FND_API.G_EXC_ERROR;
2723 END IF;
2724
2725 l_progress := 60;
2726
2727 IF (l_debug = 1) THEN
2728 print_debug('Current Progress : ' || l_progress ||
2729 ' . :-) create_transfer_rcvtxn_rec completed successfully! :-)', 4);
2730 END IF;
2731
2732 EXCEPTION
2733 WHEN FND_API.G_EXC_ERROR THEN
2734 x_return_status := FND_API.G_RET_STS_ERROR;
2735 fnd_msg_pub.count_and_get (
2736 p_encoded => FND_API.G_FALSE
2737 , p_count => x_msg_count
2738 , p_data => x_msg_data );
2739 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2740 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2741 fnd_msg_pub.count_and_get (
2742 p_encoded => FND_API.G_FALSE
2743 , p_count => x_msg_count
2744 , p_data => x_msg_data );
2745 WHEN OTHERS THEN
2746 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2747 fnd_msg_pub.count_and_get (
2748 p_encoded => FND_API.G_FALSE
2749 , p_count => x_msg_count
2750 , p_data => x_msg_data );
2751 IF SQLCODE IS NOT NULL THEN
2752 inv_mobile_helper_functions.sql_error (
2753 'INV_RCV_STD_TRANSFER_APIS.CREATE_TRANSFER_RCVTXN_REC',
2754 l_progress,
2755 SQLCODE);
2756 END IF;
2757 END create_transfer_rcvtxn_rec;
2758
2759 FUNCTION insert_mtli_helper(
2760 p_txn_if_id IN OUT NOCOPY NUMBER
2761 , p_lot_number IN VARCHAR2
2762 , p_txn_qty IN NUMBER
2763 , p_prm_qty IN NUMBER
2764 , p_item_id IN NUMBER
2765 , p_org_id IN NUMBER
2766 , p_serial_temp_id IN NUMBER
2767 , p_product_txn_id IN NUMBER
2768 , p_secondary_quantity IN NUMBER --OPM Convergence
2769 , p_secondary_uom IN NUMBER --OPM Convergence
2770 ) RETURN BOOLEAN IS
2771 --Local variables
2772 l_lot_status_id NUMBER;
2773 l_txn_if_id NUMBER := p_txn_if_id;
2774 l_product_txn_id NUMBER := p_product_txn_id;
2775 l_expiration_date DATE;
2776 l_prod_code VARCHAR2(5) := inv_rcv_integration_apis.G_PROD_CODE;
2777 l_return_status VARCHAR2(1);
2778 l_msg_count NUMBER;
2779 l_msg_data VARCHAR2(10000);
2780 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
2781 BEGIN
2782 --Get the required columns from MLN first
2783
2784 -- Added for bug 14269755
2785 BEGIN
2786
2787 SELECT expiration_date
2788 , status_id
2789 INTO l_expiration_date
2790 , l_lot_status_id
2791 FROM mtl_lot_numbers
2792 WHERE lot_number = p_lot_number
2793 AND inventory_item_id = p_item_id
2794 AND organization_id = p_org_id;
2795
2796 EXCEPTION
2797 WHEN No_Data_Found THEN
2798
2799 IF (l_debug = 1) THEN
2800 print_debug('Exception occurred No Data Found : ' || l_lot_status_id,1);
2801 END IF;
2802 END;
2803
2804 IF (l_txn_if_id IS NULL) THEN
2805 BEGIN
2806 SELECT mtl_material_transactions_s.NEXTVAL
2807 INTO l_txn_if_id
2808 FROM sys.dual;
2809 EXCEPTION
2810 WHEN OTHERS THEN
2811 IF (l_debug = 1) THEN
2812 print_debug('insert_mtli_helper: Error retrieving from seq.',1);
2813 print_debug('insert_mtli_helper: SQLCODE: '||SQLCODE||' SQLERRM:'||Sqlerrm,1);
2814 END IF;
2815 END;
2816 END IF;
2817
2818 IF (l_debug = 1) THEN
2819 print_debug('insert_mtli_helper: l_txn_if_id: '||l_txn_if_id,1);
2820 END IF;
2821
2822 --Call the insert_mtli API
2823 inv_rcv_integration_pvt.insert_mtli
2824 (p_product_transaction_id => l_product_txn_id
2825 ,p_product_code => l_prod_code
2826 ,p_interface_id => l_txn_if_id
2827 ,p_org_id => p_org_id
2828 ,p_item_id => p_item_id
2829 ,p_lot_number => p_lot_number
2830 ,p_transaction_quantity => p_txn_qty
2831 ,p_primary_quantity => p_prm_qty
2832 ,p_serial_interface_id => p_serial_temp_id
2833 ,x_return_status => l_return_status
2834 ,x_msg_count => l_msg_count
2835 ,x_msg_data => l_msg_data
2836 ,p_sec_qty => p_secondary_quantity
2837 );
2838
2839 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2840 fnd_msg_pub.count_and_get(p_count => l_msg_count, p_data => l_msg_data);
2841 IF (l_debug = 1) THEN
2842 print_debug('insert_mtli_helper: Error occurred while creating interface lots: ' || l_msg_data,1);
2843 END IF;
2844 RETURN FALSE;
2845 END IF;
2846
2847 p_txn_if_id := l_txn_if_id;
2848
2849 RETURN TRUE;
2850
2851 EXCEPTION
2852 WHEN OTHERS THEN
2853 IF (l_debug = 1) THEN
2854 print_debug('Exception occurred in insert_mtli_helper: ',1);
2855 END IF;
2856 RETURN FALSE;
2857 END insert_mtli_helper;
2858
2859 FUNCTION insert_msni_helper(
2860 p_txn_if_id IN OUT NOCOPY NUMBER
2861 , p_serial_number IN VARCHAR2
2862 , p_item_id IN NUMBER
2863 , p_org_id IN NUMBER
2864 , p_product_txn_id IN OUT NOCOPY NUMBER
2865 ) RETURN BOOLEAN IS
2866 --Local variables
2867 l_serial_status_id NUMBER;
2868 l_txn_if_id NUMBER := p_txn_if_id;
2869 l_product_txn_id NUMBER := p_product_txn_id;
2870 l_prod_code VARCHAR2(5) := inv_rcv_integration_apis.G_PROD_CODE;
2871 l_yes VARCHAR2(1) := inv_rcv_integration_apis.G_YES;
2872 l_no VARCHAR2(1) := inv_rcv_integration_apis.G_NO;
2873 l_false VARCHAR2(1) := inv_rcv_integration_apis.G_FALSE;
2874 l_return_status VARCHAR2(1);
2875 l_msg_count NUMBER;
2876 l_msg_data VARCHAR2(10000);
2877 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
2878 BEGIN
2879
2880 --Get the serial status
2881 -- Added for bug 14269755
2882 BEGIN
2883
2884 SELECT status_id
2885 INTO l_serial_status_id
2886 FROM mtl_serial_numbers
2887 WHERE serial_number = p_serial_number
2888 AND inventory_item_id = p_item_id;
2889
2890 EXCEPTION
2891 WHEN No_Data_Found THEN
2892
2893 IF (l_debug = 1) THEN
2894 print_debug('Exception occurred No Data Found : ' || l_serial_status_id,1);
2895 END IF;
2896 END;
2897
2898 --Call the insert_msni API
2899 inv_rcv_integration_apis.insert_msni(
2900 p_api_version => 1.0
2901 , p_init_msg_lst => l_false
2902 , x_return_status => l_return_status
2903 , x_msg_count => l_msg_count
2904 , x_msg_data => l_msg_data
2905 , p_transaction_interface_id => l_txn_if_id
2906 , p_fm_serial_number => p_serial_number
2907 , p_to_serial_number => p_serial_number
2908 , p_organization_id => p_org_id
2909 , p_inventory_item_id => p_item_id
2910 , p_status_id => l_serial_status_id
2911 , p_product_transaction_id => l_product_txn_id
2912 , p_product_code => l_prod_code
2913 , p_att_exist => l_yes
2914 , p_update_msn => l_no);
2915
2916 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2917 fnd_msg_pub.count_and_get(p_count => l_msg_count, p_data => l_msg_data);
2918 IF (l_debug = 1) THEN
2919 print_debug('insert_msni_helper: Error occurred while creating interface serials: ' || l_msg_data,1);
2920 END IF;
2921 RETURN FALSE;
2922 END IF;
2923
2924 IF (l_debug = 1) THEN
2925 print_debug('insert_msni_helper: msni '||p_txn_if_id||' inserted for serial '||p_serial_number,1);
2926 END IF;
2927
2928 RETURN TRUE;
2929
2930 EXCEPTION
2931 WHEN OTHERS THEN
2932 IF (l_debug = 1) THEN
2933 print_debug('Exception occurred in insert_msni_helper: ',1);
2934 END IF;
2935 RETURN FALSE;
2936 END insert_msni_helper;
2937
2938
2939 PROCEDURE Match_transfer_rcvtxn_rec(
2940 x_return_status OUT NOCOPY VARCHAR2
2941 , x_msg_count OUT NOCOPY NUMBER
2942 , x_msg_data OUT NOCOPY VARCHAR2
2943 , p_organization_id IN NUMBER
2944 , p_parent_txn_id IN NUMBER
2945 , p_reference_id IN NUMBER
2946 , p_reference IN VARCHAR2
2947 , p_reference_type_code IN NUMBER
2948 , p_item_id IN NUMBER
2949 , p_revision IN VARCHAR2
2950 , p_subinventory_code IN VARCHAR2
2951 , p_locator_id IN NUMBER
2952 , p_transfer_quantity IN NUMBER
2953 , p_transfer_uom_code IN VARCHAR2
2954 , p_lot_control_code IN NUMBER
2955 , p_serial_control_code IN NUMBER
2956 , p_original_rti_id IN NUMBER DEFAULT NULL
2957 , p_original_temp_id IN NUMBER DEFAULT NULL
2958 , p_lot_number IN VARCHAR2 DEFAULT NULL
2959 , p_lpn_id IN NUMBER DEFAULT NULL
2960 , p_transfer_lpn_id IN NUMBER DEFAULT NULL
2961 , p_sec_transfer_quantity IN NUMBER DEFAULT NULL --OPM Convergence
2962 , p_sec_transfer_uom_code IN VARCHAR2 DEFAULT NULL --OPM Convergence
2963 , p_inspection_status IN NUMBER DEFAULT NULL
2964 , p_primary_uom_code IN VARCHAR2
2965 , p_from_sub IN VARCHAR2 DEFAULT NULL --Needed for matching non-lpn materials
2966 , p_from_loc IN NUMBER DEFAULT NULL --Needed for matching non-lpn materials
2967 , p_shipment_header_id IN NUMBER DEFAULT NULL) -- added for bug 14269755
2968 IS
2969
2970 CURSOR c_serial_cur(v_from_sub VARCHAR2, v_from_locator_id NUMBER) IS
2971 SELECT distinct
2972 rsl.source_document_code source_document_code
2973 ,rsl.po_line_location_id po_line_location_id
2974 ,rsl.po_distribution_id po_distribution_id
2975 ,rsl.shipment_line_id shipment_line_id
2976 ,rsl.oe_order_line_id oe_order_line_id
2977 ,rsh.receipt_source_code receipt_source_code
2978 ,rss.serial_num serial_num
2979 ,rt.uom_code uom_code
2980 ,rss.transaction_id rcv_transaction_id
2981 ,rss.lot_num lot_num
2982 ,rs.secondary_quantity secondary_quantity
2983 ,msni.transaction_interface_id transaction_interface_id
2984 ,rsl.asn_line_flag asn_line_flag
2985 FROM rcv_supply rs,
2986 rcv_transactions rt,
2987 rcv_serials_supply rss,
2988 rcv_shipment_lines rsl,
2989 rcv_shipment_headers rsh,
2990 mtl_serial_numbers_interface msni
2991 WHERE rs.item_id = p_item_id
2992 -- AND Nvl(rs.item_revision,nvl(p_revision,'@@@')) = nvl(p_revision,'@@@') BUG13562127
2993 AND nvl(p_revision, '@@@@') = decode(p_revision,null,'@@@@',rs.item_revision)
2994 AND rs.to_organization_id = p_organization_id
2995 AND nvl(rs.lpn_id,-1) = nvl(p_lpn_id,-1)
2996 AND rs.rcv_transaction_id = rt.transaction_id
2997 AND (rt.subinventory = v_from_sub OR v_from_sub IS NULL ) -- modified for bug 14269755
2998 AND (rt.locator_id = v_from_locator_id OR v_from_locator_id IS NULL) -- modified for bug 14269755
2999 AND msni.product_code = 'RCV'
3000 AND msni.product_transaction_id = p_original_rti_id
3001 AND rss.serial_num between msni.fm_serial_number and msni.to_serial_number
3002 AND nvl(rss.lot_num,'@$#_') = nvl(p_lot_number, '@$#_')
3003 AND rss.supply_type_code = 'RECEIVING'
3004 AND rs.shipment_line_id = rsl.shipment_line_id
3005 AND rs.rcv_transaction_id = rss.transaction_id
3006 AND rsh.shipment_header_id = rsl.shipment_header_id
3007 AND decode(rt.routing_header_id, 2,
3008 decode(rt.inspection_status_code,'NOT INSPECTED',1, 'ACCEPTED',2,'REJECTED', 3)
3009 ,-1) = nvl(p_inspection_status, -1)
3010 -- Added this condition for bug 14269755
3011 AND (p_shipment_header_id IS NULL OR rsl.shipment_header_id=p_shipment_header_id)
3012 AND ((rsl.shipment_line_id = p_reference_id AND p_reference='SHIPMENT_LINE_ID')
3013 OR
3014 (rsl.po_line_location_id = p_reference_id AND p_reference='PO_LINE_LOCATION_ID')
3015 OR
3016 (rsl.oe_order_line_id = p_reference_id AND p_reference='ORDER_LINE_ID')
3017 )
3018 ORDER BY rcv_transaction_id
3019 ;
3020
3021 -- Added this cursor for bug 14269755
3022 CURSOR c_serial_cur_no_rss(v_from_sub VARCHAR2, v_from_locator_id NUMBER) IS
3023 SELECT distinct
3024 rsl.source_document_code source_document_code
3025 ,rsl.po_line_location_id po_line_location_id
3026 ,rsl.po_distribution_id po_distribution_id
3027 ,rsl.shipment_line_id shipment_line_id
3028 ,rsl.oe_order_line_id oe_order_line_id
3029 ,rsh.receipt_source_code receipt_source_code
3030 ,msni.fm_serial_number serial_num
3031 ,rt.uom_code uom_code
3032 ,rs.rcv_transaction_id rcv_transaction_id
3033 ,mtli.lot_number lot_num
3034 ,rs.secondary_quantity secondary_quantity
3035 ,msni.transaction_interface_id transaction_interface_id
3036 ,rsl.asn_line_flag asn_line_flag
3037 FROM rcv_supply rs,
3038 rcv_transactions rt,
3039 rcv_shipment_lines rsl,
3040 rcv_shipment_headers rsh,
3041 mtl_serial_numbers_interface msni,
3042 mtl_transaction_lots_interface mtli
3043 WHERE rs.item_id = p_item_id
3044 AND nvl(p_revision, '@@@@') = decode(p_revision,null,'@@@@',rs.item_revision)
3045 AND rs.to_organization_id = p_organization_id
3046 AND (rt.subinventory = v_from_sub OR v_from_sub IS NULL ) -- modified for bug 14269755
3047 AND (rt.locator_id = v_from_locator_id OR v_from_locator_id IS NULL) -- modified for bug 14269755
3048 AND nvl(rs.lpn_id,-1) = nvl(p_lpn_id,-1)
3049 AND rs.rcv_transaction_id = rt.transaction_id
3050 AND msni.product_code = 'RCV'
3051 AND msni.product_transaction_id = p_original_rti_id
3052 AND rs.shipment_line_id = rsl.shipment_line_id
3053 AND rsh.shipment_header_id = rsl.shipment_header_id
3054 AND mtli.product_transaction_id (+) = msni.product_transaction_id
3055 AND (p_shipment_header_id IS NULL OR rsl.shipment_header_id=p_shipment_header_id)
3056 AND ((rsl.shipment_line_id = p_reference_id AND p_reference='SHIPMENT_LINE_ID')
3057 OR
3058 (rsl.po_line_location_id = p_reference_id AND p_reference='PO_LINE_LOCATION_ID')
3059 OR
3060 (rsl.oe_order_line_id = p_reference_id AND p_reference='ORDER_LINE_ID')
3061 )
3062 AND decode(rt.routing_header_id, 2,
3063 decode(rt.inspection_status_code,'NOT INSPECTED',1, 'ACCEPTED',2,'REJECTED', 3)
3064 ,-1) = nvl(p_inspection_status, -1)
3065 ORDER BY rcv_transaction_id
3066 ;
3067
3068
3069 CURSOR c_rtv_cur(v_from_sub VARCHAR2, v_from_locator_id NUMBER) IS
3070 SELECT
3071 rsl.source_document_code source_document_code
3072 ,rsl.po_line_location_id po_line_location_id
3073 ,rsl.po_distribution_id po_distribution_id
3074 ,rsl.shipment_line_id shipment_line_id
3075 ,rsl.oe_order_line_id oe_order_line_id
3076 ,rs.supply_source_id supply_source_id
3077 ,rs.rcv_transaction_id rcv_transaction_id
3078 ,rsh.receipt_source_code receipt_source_code
3079 ,rt.uom_code uom_code
3080 ,rs.secondary_quantity secondary_quantity
3081 /*Bug 5511398:In case the receipt transcation for WMS enabled org is done via
3082 desktop, then there will not be data in RLS table,in which case fetch the qty
3083 from rcv_supply .*/
3084 /*,Nvl(rls.primary_quantity,0) lot_prim_qty
3085 ,Nvl(rls.quantity,0) lot_qty*/
3086 ,nvl(rls.primary_quantity,rs.to_org_primary_quantity) lot_prim_qty
3087 ,nvl(rls.quantity,rs.quantity) lot_qty
3088 ,decode(rt.uom_code, p_transfer_uom_code, 1, 2) ORDERING1
3089 ,decode(rt.uom_code, p_transfer_uom_code, (p_transfer_quantity - rs.quantity), 0) ORDERING2
3090 ,rsl.asn_line_flag asn_line_flag
3091 FROM rcv_supply rs,
3092 rcv_transactions rt,
3093 rcv_shipment_lines rsl,
3094 rcv_lots_supply rls,
3095 rcv_shipment_headers rsh
3096 WHERE rs.item_id = p_item_id
3097 -- AND Nvl(rs.item_revision,nvl(p_revision,'@@@@')) = nvl(p_revision,'@@@@') --BUG13562127
3098 AND nvl(p_revision, '@@@@') = decode(p_revision,null,'@@@@',rs.item_revision)
3099 AND rs.to_organization_id = p_organization_id
3100 AND nvl(rs.lpn_id,-1) = nvl(p_lpn_id,-1)
3101 AND (rt.subinventory = v_from_sub OR v_from_sub IS NULL ) -- modified for bug 14269755
3102 AND (rt.locator_id = v_from_locator_id OR v_from_locator_id IS NULL) -- modified for bug 14269755
3103 AND nvl(rt.transfer_lpn_id,-1) = nvl(p_lpn_id,-1)
3104 AND rs.rcv_transaction_id = rt.transaction_id
3105 AND rt.shipment_line_id = rsl.shipment_line_id
3106 AND rs.supply_type_code = 'RECEIVING'
3107 AND rls.transaction_id (+) = rs.supply_source_id
3108 AND nvl(rls.lot_num, '@$#_') = nvl(p_lot_number, '@$#_')
3109 -- Added this for bug 14269755
3110 AND (p_shipment_header_id IS NULL OR rsl.shipment_header_id=p_shipment_header_id)
3111 AND ((rsl.shipment_line_id = p_reference_id AND p_reference='SHIPMENT_LINE_ID')
3112 OR
3113 (rsl.po_line_location_id = p_reference_id AND p_reference='PO_LINE_LOCATION_ID')
3114 OR
3115 (rsl.oe_order_line_id = p_reference_id AND p_reference='ORDER_LINE_ID')
3116 )
3117 AND rsh.shipment_header_id = rsl.shipment_header_id
3118 AND decode(rt.routing_header_id, 2,
3119 decode(rt.inspection_status_code,'NOT INSPECTED',1, 'ACCEPTED',2,'REJECTED', 3)
3120 ,-1) = nvl(p_inspection_status, -1)
3121 --Bug 5331779 - Begin change
3122 --Adding the following to make sure that we do not pickup RS with serial numbers
3123 AND NOT exists
3124 (SELECT '1' FROM rcv_serials_supply rss
3125 WHERE rss.transaction_id = rs.supply_source_id
3126 AND rss.supply_type_code = 'RECEIVING')
3127 --Bug 5331779-End change
3128 ORDER BY ORDERING1, ORDERING2
3129 ;
3130
3131
3132 --Local Variables
3133 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'), 0);
3134
3135
3136 l_new_rti_info inv_rcv_integration_apis.child_rec_tb_tp;
3137 l_from_sub VARCHAR2(30);
3138 l_from_locator_id NUMBER;
3139 l_split_lot_serial_ok BOOLEAN;
3140 l_receipt_uom VARCHAR2(25);
3141 l_receipt_qty NUMBER;
3142
3143 l_parent_txn_id NUMBER;
3144 l_remaining_prim_qty NUMBER;
3145 l_qty_to_insert NUMBER;
3146 l_avail_qty NUMBER;
3147 l_tolerable_qty NUMBER;
3148 l_avail_prim_qty NUMBER;
3149 l_secondary_quantity NUMBER;
3150 l_original_lot_sec_qty NUMBER;
3151 l_lot_sec_qty_to_insert NUMBER;
3152 l_new_intf_id NUMBER;
3153 l_primary_uom_code VARCHAR2(3);
3154 l_uom_code VARCHAR2(3);
3155 L_RTI_REC_FOUND Boolean ;
3156 l_result Boolean;
3157 l_reference VARCHAR2(240);
3158 l_reference_type_code NUMBER;
3159 l_reference_id NUMBER;
3160 l_return_status VARCHAR2(1):= fnd_api.g_ret_sts_success;
3161 l_msg_count NUMBER;
3162 l_msg_data VARCHAR2(4000);
3163 L_matched_index NUMBER;
3164 l_ser_txn_temp_id NUMBER;
3165 l_progress VARCHAR2(10);
3166 l_original_sec_qty NUMBER;
3167 l_lot_temp_id NUMBER;
3168 l_processed_lot_prim_qty NUMBER;
3169 l_processed_lot_qty NUMBER;
3170
3171 TYPE rti_info IS RECORD
3172 (rti_id NUMBER,
3173 rcv_transaction_id NUMBER,
3174 lot_number VARCHAR2(80),
3175 source_document_code VARCHAR2(25),
3176 po_line_location_id NUMBER,
3177 po_distribution_id NUMBER,
3178 shipment_line_id NUMBER,
3179 oe_order_line_id NUMBER,
3180 receipt_source_code VARCHAR2(25),
3181 serial_intf_id NUMBER,
3182 uom_code VARCHAR2(3),
3183 reference VARCHAR2(240),
3184 REFERENCE_TYPE_CODE NUMBER,
3185 REFERENCE_ID NUMBER,
3186 quantity NUMBER);
3187
3188 l_txn_id NUMBER;
3189 l_lot_num VARCHAR2(80);
3190
3191 TYPE lot_tb_tp IS TABLE OF rti_info INDEX BY VARCHAR2(80);
3192 TYPE rti_tb_tp IS TABLE OF lot_tb_tp INDEX BY BINARY_INTEGER;
3193
3194 l_rti_tb rti_tb_tp;
3195 l_mmtt_id_to_insert NUMBER;
3196 k NUMBER;
3197 l VARCHAR2(80);
3198 l_rss_exist VARCHAR2(1) := 'N' ; -- Added for bug 14269755
3199 l_lot_number VARCHAR2(80) := p_lot_number;
3200
3201 BEGIN
3202
3203 --Initialize the return status
3204 x_return_status := FND_API.G_RET_STS_SUCCESS;
3205
3206 SAVEPOINT match_rti_ss;
3207
3208 IF (l_debug = 1) THEN
3209 print_debug('Entered Match_transfer_rcvtxn_rec 10: '|| to_char(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 1);
3210 END IF;
3211
3212 l_progress := 20;
3213
3214
3215 --Print the input parameters
3216 IF (l_debug = 1) THEN
3217 print_debug('******Input parameters passed to the procedure:****** ', 3);
3218 print_debug(' p_lpn_id ===> ' || p_lpn_id, 3);
3219 print_debug(' p_organization_id ===> ' || p_organization_id, 3);
3220 print_debug(' p_parent_txn_id ===> ' || p_parent_txn_id, 3);
3221 print_debug(' p_reference_id ===> ' || p_reference_id, 3);
3222 print_debug(' p_reference ===> ' || p_reference, 3);
3223 print_debug(' p_reference_type_code ===> ' || p_reference_type_code, 3);
3224 print_debug(' p_item_id ===> ' || p_item_id, 3);
3225 print_debug(' p_revision ===> ' || p_revision, 3);
3226 print_debug(' p_subinventory_code ===> ' || p_subinventory_code, 3);
3227 print_debug(' p_locator_id ===> ' || p_locator_id, 3);
3228 print_debug(' p_transfer_quantity ===> ' || p_transfer_quantity, 3);
3229 print_debug(' p_transfer_uom_code ===> ' || p_transfer_uom_code, 3);
3230 print_debug(' p_original_rti_id ===> ' || p_original_rti_id, 3);
3231 print_debug(' p_lot_control_code ===> ' || p_lot_control_code, 3);
3232 print_debug(' p_serial_control_code ===> ' || p_serial_control_code, 3);
3233 print_debug(' p_original_temp_id ===> ' || p_original_temp_id, 3);
3234 print_debug(' p_lot_number ===> ' || p_lot_number, 3);
3235 print_debug(' p_secondary_quantity ===> ' || p_sec_transfer_quantity,3);
3236 print_debug(' p_secondary_uom_code ===> ' || p_sec_transfer_uom_code,3);
3237 print_debug(' p_primary_uom_code ===> ' ||p_primary_uom_code,3);
3238 print_debug(' p_from_sub ===> ' ||p_from_sub,3);
3239 print_debug(' p_from_loc ===> ' ||p_from_loc,3);
3240 print_debug('***************************************************** ', 3);
3241 END IF;
3242
3243 l_primary_uom_code := p_primary_uom_code;
3244
3245 IF (p_lpn_id IS NULL) THEN
3246 l_from_sub := p_from_sub;
3247 l_from_locator_id := p_from_loc;
3248 ELSE
3249 BEGIN
3250 select subinventory_code
3251 , locator_id
3252 into l_from_sub
3253 ,l_from_locator_id
3254 from wms_license_plate_numbers wlpn
3255 where wlpn.lpn_id = p_lpn_id;
3256 EXCEPTION
3257 WHEN NO_DATA_FOUND THEN
3258 l_from_sub := NULL;
3259 l_from_locator_id := NULL;
3260 END;
3261 END IF;
3262
3263 l_remaining_prim_qty := inv_convert.inv_um_convert(
3264 item_id => p_item_id,
3265 precision => 6,
3266 from_quantity => p_transfer_quantity,
3267 from_unit => p_transfer_uom_code,
3268 to_unit => l_primary_uom_code,
3269 from_name => null,
3270 to_name => null);
3271
3272 IF (p_serial_control_code = 1 OR p_serial_control_code = 6) then
3273 -- CASE WHERE ITEM IS NOT SERIAL CONTROLLED
3274 -- OR CASE WHEN SERIAL CONTROLLED CODE IS AT SO ISSUE Bug: 5331779
3275 For l_rtv_rec in c_rtv_cur(l_from_sub, l_from_locator_id)
3276 Loop
3277
3278 IF l_remaining_prim_qty <= 0 THEN
3279 EXIT;
3280 END If;
3281
3282 IF (l_debug = 1) THEN
3283 print_debug('INSIDE MATCHING LOOP TO FIND THE PARENT TXN ' , 1);
3284 print_debug('SOURCE_DOCUMNET_CODE = '|| l_rtv_rec.source_document_code , 1);
3285 print_debug('po_line_location_id = ' || l_rtv_rec.po_line_location_id, 1);
3286 print_debug('shipment_line_id = ' || l_rtv_rec.shipment_line_id , 1);
3287 print_debug('oe_order_line_id ' || l_rtv_rec.oe_order_line_id , 1);
3288 print_debug('supply_source_id ' || l_rtv_rec.supply_source_id, 1);
3289 print_debug('receipt_source_code ' || l_rtv_rec.receipt_source_code, 1);
3290 print_debug('transaction id ' || l_rtv_rec.rcv_transaction_id, 1);
3291 print_debug('lot primary quantity ' || l_rtv_rec.lot_prim_qty, 1);
3292 END IF;
3293
3294 IF (p_lot_control_code = 2) THEN
3295 IF (l_debug = 1) THEN
3296 print_debug('Querying MTLI to determine available QTY ' , 1);
3297 END IF;
3298
3299 BEGIN
3300 SELECT SUM(primary_quantity)
3301 , SUM(transaction_quantity)
3302 INTO l_processed_lot_prim_qty
3303 , l_processed_lot_qty
3304 FROM mtl_transaction_lots_interface
3305 WHERE product_code = 'RCV'
3306 AND product_transaction_id
3307 IN (SELECT interface_transaction_id
3308 FROM rcv_transactions_interface
3309 WHERE parent_transaction_id = l_rtv_rec.rcv_transaction_id
3310 )
3311 AND lot_number = p_lot_number;
3312 EXCEPTION
3313 WHEN OTHERS THEN
3314 l_processed_lot_prim_qty := 0;
3315 l_processed_lot_qty := 0;
3316 END;
3317
3318 IF (l_debug = 1) THEN
3319 print_debug('processed lot prim qty: '||l_processed_lot_prim_qty , 1);
3320 print_debug('processed lot qty: '|| l_processed_lot_qty, 1);
3321 END IF;
3322
3323 l_avail_prim_qty := l_rtv_rec.lot_prim_qty - Nvl(l_processed_lot_prim_qty,0);
3324 l_avail_qty := l_rtv_rec.lot_qty - Nvl(l_processed_lot_qty,0);
3325
3326 IF (l_debug = 1) THEN
3327 print_debug('available lot qty: '||l_avail_prim_qty , 1);
3328 END IF;
3329
3330 ELSE
3331 IF (l_debug = 1) THEN
3332 print_debug('BEFORE GETTING AVAIL QTY ' , 1);
3333 END IF;
3334
3335 rcv_quantities_s.get_available_quantity(
3336 'TRANSFER'
3337 , l_rtv_rec.rcv_transaction_id
3338 , l_rtv_rec.receipt_source_code
3339 , NULL
3340 , l_rtv_rec.rcv_transaction_id
3341 , NULL
3342 , l_avail_qty
3343 , l_tolerable_qty
3344 , l_receipt_uom);
3345 IF (l_debug = 1) THEN
3346 print_debug('AFTER GETTING AVAIL QTY. AVAIL QTY: '||l_avail_qty , 1);
3347 END IF;
3348
3349
3350 l_avail_prim_qty := inv_convert.inv_um_convert( item_id => p_item_id,
3351 precision => 6,
3352 from_quantity => l_avail_qty,
3353 from_unit => l_rtv_rec.uom_code,
3354 to_unit => l_primary_uom_code,
3355 from_name => null,
3356 to_name => null);
3357
3358 IF (l_debug = 1) THEN
3359 print_debug('AVAIL QTY = ' || l_avail_qty , 1);
3360 print_debug('AVAIL_PRIM QTY = ' || l_avail_prim_qty , 1);
3361 print_debug('REMAINING_PRIM QTY = '|| l_remaining_prim_qty , 1);
3362 END IF;
3363 END IF;
3364
3365 IF l_avail_prim_qty <= 0 THEN
3366 IF (l_debug = 1) THEN
3367 print_debug('SKIPPING THIS LINE',1);
3368 END IF;
3369 GOTO nextrtrecord;
3370 END IF;
3371
3372
3373 SELECT rcv_transactions_interface_s.NEXTVAL
3374 INTO l_new_intf_id
3375 FROM dual;
3376
3377 IF (l_avail_prim_qty < l_remaining_prim_qty ) THEN
3378 IF (p_transfer_uom_code <> l_rtv_rec.uom_code) THEN
3379 l_qty_to_insert := inv_convert.inv_um_convert( item_id => p_item_id,
3380 precision => 6,
3381 from_quantity => l_avail_qty,
3382 from_unit => l_rtv_rec.uom_code,
3383 to_unit => p_transfer_uom_code,
3384 from_name => null,
3385 to_name => null );
3386
3387
3388 -- Qty to Insert IS
3389 IF (l_debug = 1) THEN
3390 print_debug('QTY TO INSERT1: = '||L_QTY_TO_INSERT , 1);
3391 END IF;
3392 ELSE
3393 --
3394 -- WHICH MEANS THE DELIVER TRANSACTION UOM CODE
3395 -- IS SAME AS THAN THE RECEIPT TRANSACTION UOM CODE
3396 --
3397 l_qty_to_insert := l_avail_qty;
3398
3399 IF (l_debug = 1) THEN
3400 print_debug('QTY TO INSERT2: = '||L_QTY_TO_INSERT , 1);
3401 END IF;
3402
3403 END IF; -- IF (p_rcvtxn_uom_code <> l_rtv_rec.uom_code
3404
3405 l_remaining_prim_qty := l_remaining_prim_qty - l_avail_prim_qty;
3406
3407 IF (l_debug = 1) THEN
3408 print_debug('INTF ID = '||l_new_intf_id , 1);
3409 END IF;
3410
3411 IF (p_original_temp_id IS NOT NULL) THEN
3412 IF (l_debug = 1) THEN
3413 print_debug('Calling split_mmtt', 1);
3414 END IF;
3415
3416 inv_rcv_integration_apis.split_mmtt
3417 (p_orig_mmtt_id => p_original_temp_id
3418 ,p_prim_qty_to_splt => l_avail_prim_qty
3419 ,p_prim_uom_code => l_primary_uom_code
3420 ,x_new_mmtt_id => l_mmtt_id_to_insert
3421 ,x_return_status => l_return_status
3422 ,x_msg_count => l_msg_count
3423 ,x_msg_data => l_msg_data
3424 );
3425
3426 IF (l_debug = 1) THEN
3427 print_debug('Returned from split_mmtt',1);
3428 print_debug('x_return_status: '||l_return_status,1);
3429 END IF;
3430
3431 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
3432 IF (l_debug = 1) THEN
3433 print_debug('x_msg_data: '||l_msg_data,1);
3434 print_debug('x_msg_count: '||l_msg_count,1);
3435 print_debug('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM,1);
3436 print_debug('Raising Exception!!!',1);
3437 END IF;
3438 l_progress := '@@@';
3439 RAISE fnd_api.g_exc_unexpected_error;
3440 END IF;
3441 END IF;--END IF (p_original_temp_id IS NOT NULL) THEN
3442
3443 IF (p_lot_control_code = 2) THEN
3444 l_new_rti_info(1).orig_interface_trx_id := p_original_rti_id;
3445 l_new_rti_info(1).new_interface_trx_id := l_new_intf_id;
3446 l_new_rti_info(1).quantity := L_qty_to_insert;
3447
3448 l_new_rti_info(1).to_organization_id := p_organization_id;
3449 l_new_rti_info(1).item_id := p_item_id;
3450 l_new_rti_info(1).uom_code := p_transfer_uom_code;
3451
3452 IF (l_remaining_prim_qty > 0) THEN
3453 IF (p_transfer_uom_code <> l_primary_uom_code) THEN
3454 l_new_rti_info(2).quantity := inv_convert.inv_um_convert
3455 ( item_id => p_item_id,
3456 precision => 6,
3457 from_quantity => l_remaining_prim_qty,
3458 from_unit => l_primary_uom_code,
3459 to_unit => p_transfer_uom_code,
3460 from_name => null,
3461 to_name => null );
3462 ELSE
3463 l_new_rti_info(2).quantity := l_remaining_prim_qty;
3464 END IF;
3465
3466 l_new_rti_info(2).orig_interface_trx_id := p_original_rti_id;
3467 l_new_rti_info(2).new_interface_trx_id := p_original_rti_id;
3468
3469 l_new_rti_info(2).to_organization_id := p_organization_id;
3470 l_new_rti_info(2).item_id := p_item_id;
3471 l_new_rti_info(2).uom_code := p_transfer_uom_code;
3472 END IF;
3473
3474 IF (l_debug = 1) THEN
3475 print_debug('Before calling the split lot serial', 1);
3476 END IF;
3477
3478 l_split_lot_serial_ok := inv_rcv_integration_apis.split_lot_serial
3479 (p_api_version => 1.0
3480 , p_init_msg_lst => FND_API.G_FALSE
3481 , x_return_status => l_return_status
3482 , x_msg_count => l_msg_count
3483 , x_msg_data => l_msg_data
3484 , p_new_rti_info => l_new_rti_info);
3485
3486
3487 IF (l_debug = 1) THEN
3488 print_debug('After calling the split lot serial', 1);
3489 END IF;
3490
3491 IF ( NOT l_split_lot_serial_ok) THEN
3492 IF (l_debug = 1) THEN
3493 print_debug(' MATCH_TRANSFER_INTF_REC: Failure in split_lot_serial ', 4);
3494 END IF;
3495 RAISE FND_API.G_EXC_ERROR;
3496 END IF;
3497 END IF;
3498
3499 ELSE
3500 l_qty_to_insert := inv_convert.inv_um_convert(
3501 item_id => p_item_id,
3502 precision => 6,
3503 from_quantity => l_remaining_prim_qty,
3504 from_unit => l_primary_uom_code,
3505 to_unit => p_transfer_uom_code,
3506 from_name => null,
3507 to_name => null );
3508
3509 IF (l_debug = 1) THEN
3510 print_debug('QTY TO INSERT3: = ' || L_QTY_TO_INSERT , 1);
3511 END IF;
3512
3513 IF (p_lot_control_code = 2) THEN
3514 UPDATE mtl_transaction_lots_interface
3515 SET product_transaction_id = l_new_intf_id
3516 WHERE product_transaction_id = p_original_rti_id
3517 AND product_code = 'RCV';
3518 END IF;
3519
3520 l_remaining_prim_qty := 0;
3521 l_mmtt_id_to_insert := p_original_temp_id;
3522 END IF;
3523
3524 IF l_rtv_rec.source_document_code = 'PO' THEN
3525 L_reference := 'PO_LINE_LOCATION_ID';
3526 L_reference_type_code := 4;
3527 L_reference_id := l_rtv_rec.po_line_location_id ;
3528 --Bug5662935:For source_document_code 'INVENTORY', reference_type_code is'6'.
3529 ELSIF l_rtv_rec.source_document_code = 'INVENTORY' THEN
3530 L_reference := 'SHIPMENT_LINE_ID';
3531 L_reference_type_code := 6;
3532 L_reference_id := l_rtv_rec.shipment_line_id;
3533 ELSIF l_rtv_rec.source_document_code = 'REQ' THEN
3534 L_reference := 'SHIPMENT_LINE_ID';
3535 L_reference_type_code := 8;
3536 L_reference_id := l_rtv_rec.shipment_line_id;
3537 ELSIF l_rtv_rec.source_document_code = 'RMA' THEN
3538 L_reference := 'ORDER_LINE_ID';
3539 L_reference_type_code := 7;
3540 L_reference_id := l_rtv_rec.oe_order_line_id;
3541 ELSE
3542 -- FAIL HERE AS THERE MAY NOT BE ANY OTHER SOURCE DOCUMENT CODE
3543 IF (l_debug = 1) THEN
3544 print_debug('REFERENCE INFO CAN NOT BE RETRIEVVED FROM RT' , 1);
3545 END IF;
3546 fnd_message.set_name('INV', 'INV_FAILED');
3547 fnd_msg_pub.ADD;
3548 RAISE fnd_api.g_exc_error;
3549 END IF;
3550
3551 IF (l_debug = 1) THEN
3552 print_debug('REFERENCE = '||L_REFERENCE , 1);
3553 print_debug('REFERENCE_TYPE_CODE = '||L_REFERENCE_TYPE_CODE , 1);
3554 print_debug('REFERENCE_ID = '||L_REFERENCE_ID , 1);
3555 END IF;
3556
3557
3558
3559 -- If the secondary_uom_code is not null then calculate the secondary
3560 -- Qty based on percentage of transaction quantity
3561
3562 if p_sec_transfer_uom_code is not null Then
3563 IF (l_debug = 1) THEN
3564 print_debug('CALCULATING SEC QTY' , 1);
3565 END IF;
3566 l_secondary_quantity := p_sec_transfer_quantity * (l_qty_to_insert/p_transfer_quantity);
3567 Else
3568 l_secondary_quantity := null;
3569 End if;
3570
3571 IF (l_debug = 1) THEN
3572 print_debug('SECONDARY UOM CODE = '|| p_sec_transfer_uom_code , 1);
3573 print_debug('SECONDARY QTY = '|| l_secondary_quantity, 1);
3574 END IF;
3575
3576 inv_rcv_std_transfer_apis.create_transfer_rcvtxn_rec(
3577 x_return_status => l_return_status
3578 , x_msg_count => l_msg_count
3579 , x_msg_data => l_msg_data
3580 , p_organization_id => p_organization_id
3581 , p_parent_txn_id => l_rtv_rec.rcv_transaction_id
3582 , p_reference_id => l_reference_id
3583 , p_reference => l_reference
3584 , p_reference_type_code => l_reference_type_code
3585 , p_item_id => p_item_id
3586 , p_revision => p_revision
3587 , p_subinventory_code => p_subinventory_code
3588 , p_locator_id => p_locator_id
3589 , p_transfer_quantity => l_qty_to_insert
3590 , p_transfer_uom_code => p_transfer_uom_code
3591 , p_lot_control_code => p_lot_control_code
3592 , p_serial_control_code => p_serial_control_code
3593 , p_original_rti_id => l_new_intf_id
3594 , p_original_temp_id => l_mmtt_id_to_insert
3595 , p_lot_number => p_lot_number
3596 , p_lpn_id => p_lpn_id
3597 , p_transfer_lpn_id => p_transfer_lpn_id
3598 , p_sec_transfer_quantity => l_secondary_quantity
3599 , p_sec_transfer_uom_code => p_sec_transfer_uom_code
3600 );
3601
3602 IF (l_debug = 1) THEN
3603 print_debug('AFTER CALLING THE TRANSFER API: STATUS = '|| l_return_status, 1);
3604 END IF;
3605
3606 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
3607 fnd_message.set_name('WMS', 'WMS_TASK_ERROR');
3608 fnd_msg_pub.ADD;
3609 RAISE fnd_api.g_exc_unexpected_error;
3610 ELSIF l_return_status = fnd_api.g_ret_sts_error THEN
3611 fnd_message.set_name('WMS', 'WMS_TASK_ERROR');
3612 fnd_msg_pub.ADD;
3613 RAISE fnd_api.g_exc_error;
3614 END IF;
3615
3616 <<nextrtrecord>>
3617 NULL;
3618 End Loop; -- End of Recipt' txns Loop
3619 --Bug 5331779-Begin change
3620 --If l_remaining_prim_qty > 0 then only
3621 --fail if the serial control code is 1 because for serial control
3622 --code of 6, there may be lines with serial number which are yet to
3623 --be processed.
3624 IF p_serial_control_code = 1 AND l_remaining_prim_qty > 0 THEN
3625 -- Bug 5331779 -End change
3626 -- IF l_remaining_prim_qty > 0 THEN
3627
3628 IF (l_debug = 1) THEN
3629 print_debug('COUND NOT MATCH RECEIPT TRANSACTION FOR THE QTY TO BE TRANSFERRED: FAILURE ', 1);
3630 END IF;
3631
3632 -- Unable to match RS with quantity.
3633 fnd_message.set_name('WMS', 'WMS_TASK_ERROR');
3634 fnd_msg_pub.ADD;
3635 RAISE FND_API.g_exc_error;
3636
3637 END IF;
3638 --Bug 5331779-Begin Change
3639 --Instead of an else block we need to have
3640 --another if block because for items with serial control code 6, we
3641 --want both blocks to be executed.
3642 END IF;
3643 --ELSE -- ITEM IS SERIAL CONTROLLED.
3644
3645 IF ((p_serial_control_code NOT IN (1,6))
3646 OR (p_serial_control_code = 6 AND l_remaining_prim_qty > 0)) THEN
3647 --Bug 5331779- End change
3648
3649 L_RTI_REC_FOUND := FALSE;
3650
3651 BEGIN
3652 --Added for bug 14269755
3653 SELECT 'Y' INTO l_rss_exist FROM DUAL
3654 WHERE EXISTS
3655 (SELECT 1
3656 FROM rcv_serials_supply rss,
3657 mtl_serial_numbers_interface msni
3658 WHERE msni.product_code = 'RCV'
3659 AND msni.product_transaction_id = p_original_rti_id
3660 AND rss.serial_num between msni.fm_serial_number and msni.to_serial_number
3661 AND rss.supply_type_code = 'RECEIVING');
3662 EXCEPTION
3663 WHEN No_Data_Found THEN
3664 l_rss_exist := 'N';
3665 END;
3666
3667 IF(l_rss_exist = 'Y') THEN -- Added for bug 14269755
3668
3669 FOR l_serial_rec in C_SERIAL_CUR(l_from_sub, l_from_locator_id)
3670 LOOP
3671
3672
3673 IF (l_debug = 1) THEN
3674 print_debug('INSIDE SERIAL CURSOR LOOP ', 1);
3675 END IF;
3676
3677 IF (l_debug = 1) THEN
3678 print_debug('COUNT OF RTI ROWS '|| l_rti_tb.COUNT, 1);
3679 END IF;
3680
3681 l_txn_id := l_serial_rec.rcv_transaction_id;
3682 l_lot_num := nvl(l_serial_rec.lot_num, '@$#_');
3683
3684 IF (l_rti_tb.exists(l_txn_id) AND
3685 l_rti_tb(l_txn_id).exists(l_lot_num)) THEN
3686
3687 l_rti_tb(l_txn_id)(l_lot_num).quantity
3688 := l_rti_tb(l_txn_id)(l_lot_num).quantity + 1;
3689
3690 ELSE
3691 SELECT rcv_transactions_interface_s.NEXTVAL
3692 INTO l_new_intf_id
3693 FROM dual;
3694
3695 l_rti_tb(l_txn_id)(l_lot_num).rti_id := l_new_intf_id;
3696
3697 IF p_lot_control_code = 2 AND l_lot_num <> '@$#_' THEN -- bug 14269755 IF (p_lot_number IS NOT NULL) THEN
3698
3699 -- Generate the Serial Txn Temp Id
3700
3701 select mtl_material_transactions_s.nextval
3702 into l_ser_txn_temp_id from dual;
3703
3704 l_rti_tb(l_txn_id)(l_lot_num).lot_number := l_serial_rec.lot_num;
3705 l_rti_tb(l_txn_id)(l_lot_num).serial_intf_id := l_ser_txn_temp_id;
3706 ELSE
3707 l_rti_tb(l_txn_id)(l_lot_num).serial_intf_id := null;
3708 END IF;
3709
3710 l_rti_tb(l_txn_id)(l_lot_num).rcv_transaction_id := l_serial_rec.rcv_transaction_id;
3711 l_rti_tb(l_txn_id)(l_lot_num).po_line_location_id := l_serial_rec.po_line_location_id;
3712 l_rti_tb(l_txn_id)(l_lot_num).po_distribution_id := l_serial_rec.po_distribution_id;
3713 l_rti_tb(l_txn_id)(l_lot_num).uom_code := l_serial_rec.uom_code;
3714 l_rti_tb(l_txn_id)(l_lot_num).source_document_code := l_serial_rec.source_document_code;
3715 l_rti_tb(l_txn_id)(l_lot_num).quantity := 1;
3716 l_rti_tb(l_txn_id)(l_lot_num).receipt_source_code := l_serial_rec.receipt_source_code;
3717 l_rti_tb(l_txn_id)(l_lot_num).uom_code := l_serial_rec.uom_code;
3718
3719 --l_rti_tb(l_txn_id)(l_lot_num).secondary_quantity := l_serial_rec.secondary_quantity;
3720 --l_rti_tb(l_txn_id)(l_lot_num).secondary_uom_code := l_serial_rec.secondary_uom_code;
3721
3722 IF l_rti_tb(l_txn_id)(l_lot_num).source_document_code = 'PO' THEN
3723 l_rti_tb(l_txn_id)(l_lot_num).reference := 'PO_LINE_LOCATION_ID';
3724 l_rti_tb(l_txn_id)(l_lot_num).reference_type_code := 4;
3725 l_rti_tb(l_txn_id)(l_lot_num).reference_id := l_serial_rec.po_line_location_id;
3726 --Bug5662935:For source_document_code 'INVENTORY', reference_type_code is'6'.
3727 ELSIF l_rti_tb(l_txn_id)(l_lot_num).source_document_code = 'INVENTORY' THEN
3728 l_rti_tb(l_txn_id)(l_lot_num).reference := 'SHIPMENT_LINE_ID';
3729 l_rti_tb(l_txn_id)(l_lot_num).reference_type_code := 6;
3730 l_rti_tb(l_txn_id)(l_lot_num).reference_id := l_serial_rec.shipment_line_id;
3731 ELSIF l_rti_tb(l_txn_id)(l_lot_num).source_document_code = 'REQ' THEN
3732 l_rti_tb(l_txn_id)(l_lot_num).reference := 'SHIPMENT_LINE_ID';
3733 l_rti_tb(l_txn_id)(l_lot_num).reference_type_code := 8;
3734 l_rti_tb(l_txn_id)(l_lot_num).reference_id := l_serial_rec.shipment_line_id;
3735 ELSIF l_rti_tb(l_txn_id)(l_lot_num).source_document_code = 'RMA' THEN
3736 l_rti_tb(l_txn_id)(l_lot_num).reference := 'ORDER_LINE_ID';
3737 l_rti_tb(l_txn_id)(l_lot_num).reference_type_code := 7;
3738 l_rti_tb(l_txn_id)(l_lot_num).reference_id := l_serial_rec.oe_order_line_id;
3739 ELSE
3740 -- FAIL HERE AS THERE MAY NOT BE ANY OTHER SOURCE DOCUMENT CODE
3741 IF (l_debug = 1) THEN
3742 print_debug('REF INFO CAN NOT BE RETRIEVVED FROM RT' , 1);
3743 END IF;
3744 fnd_message.set_name('INV', 'INV_FAILED');
3745 fnd_msg_pub.ADD;
3746 RAISE fnd_api.g_exc_error;
3747 END IF;
3748
3749 IF (l_debug = 1) THEN
3750 print_debug('REFERENCE = ' ||l_rti_tb(l_txn_id)(l_lot_num).reference , 1);
3751 print_debug('REFERENCE_TYPE_CODE = '||l_rti_tb(l_txn_id)(l_lot_num).reference_type_code , 1);
3752 print_debug('REFERENCE_ID = ' ||l_rti_tb(l_txn_id)(l_lot_num).reference_id , 1);
3753 END IF;
3754 END IF;
3755
3756 -- INSERT MSNI HERE
3757 l_result := insert_msni_helper(
3758 p_txn_if_id => l_rti_tb(l_txn_id)(l_lot_num).serial_intf_id
3759 , p_serial_number => l_serial_rec.serial_num
3760 , p_org_id => p_organization_id
3761 , p_item_id => p_item_id
3762 , p_product_txn_id => l_rti_tb(l_txn_id)(l_lot_num).rti_id
3763 );
3764
3765 IF NOT l_result THEN
3766 IF (l_debug = 1) THEN
3767 print_debug('Failure while Inserting MSNI records - lot and serial controlled item',1);
3768 END IF;
3769 RAISE fnd_api.g_exc_unexpected_error;
3770 END IF; -- END IF check l_result
3771
3772 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3773 fnd_msg_pub.count_and_get(p_count => l_msg_count, p_data => l_msg_data);
3774 IF (l_debug = 1) THEN
3775 print_debug('insert_msni_helper: Error occurred while creating interface serials: ' || l_msg_data,1);
3776 END IF;
3777 RAISE fnd_api.g_exc_error;
3778 END IF;
3779 END LOOP; -- End of serial cursor Loop here
3780
3781 -- Start changes for bug 14269755
3782 ELSE
3783
3784 FOR l_serial_rec in C_SERIAL_CUR_NO_RSS(l_from_sub, l_from_locator_id)
3785 LOOP
3786
3787 IF (l_debug = 1) THEN
3788 print_debug('INSIDE NO RSS SERIAL CURSOR LOOP ', 1);
3789 END IF;
3790
3791 IF (l_debug = 1) THEN
3792 print_debug('COUNT OF RTI ROWS '|| l_rti_tb.COUNT, 1);
3793 END IF;
3794 l_txn_id := l_serial_rec.rcv_transaction_id;
3795 l_lot_num := nvl(l_serial_rec.lot_num, '@$#_');
3796
3797 IF (l_rti_tb.exists(l_txn_id) AND
3798 l_rti_tb(l_txn_id).exists(l_lot_num)) THEN
3799
3800 l_rti_tb(l_txn_id)(l_lot_num).quantity
3801 := l_rti_tb(l_txn_id)(l_lot_num).quantity + 1;
3802
3803 ELSE
3804
3805 SELECT rcv_transactions_interface_s.NEXTVAL
3806 INTO l_new_intf_id
3807 FROM dual;
3808
3809 l_rti_tb(l_txn_id)(l_lot_num).rti_id := l_new_intf_id;
3810
3811 IF p_lot_control_code = 2 AND l_lot_num <> '@$#_' THEN -- bug 14269755 IF (p_lot_number IS NOT NULL) THEN
3812
3813 -- Generate the Serial Txn Temp Id
3814
3815 select mtl_material_transactions_s.nextval
3816 into l_ser_txn_temp_id from dual;
3817
3818 l_rti_tb(l_txn_id)(l_lot_num).lot_number := l_serial_rec.lot_num;
3819 l_rti_tb(l_txn_id)(l_lot_num).serial_intf_id := l_ser_txn_temp_id;
3820 ELSE
3821 l_rti_tb(l_txn_id)(l_lot_num).serial_intf_id := null;
3822 END IF;
3823
3824 l_rti_tb(l_txn_id)(l_lot_num).rcv_transaction_id := l_serial_rec.rcv_transaction_id;
3825 l_rti_tb(l_txn_id)(l_lot_num).po_line_location_id := l_serial_rec.po_line_location_id;
3826 l_rti_tb(l_txn_id)(l_lot_num).po_distribution_id := l_serial_rec.po_distribution_id;
3827 l_rti_tb(l_txn_id)(l_lot_num).uom_code := l_serial_rec.uom_code;
3828 l_rti_tb(l_txn_id)(l_lot_num).source_document_code := l_serial_rec.source_document_code;
3829 l_rti_tb(l_txn_id)(l_lot_num).quantity := 1;
3830 l_rti_tb(l_txn_id)(l_lot_num).receipt_source_code := l_serial_rec.receipt_source_code;
3831 l_rti_tb(l_txn_id)(l_lot_num).uom_code := l_serial_rec.uom_code;
3832
3833 --l_rti_tb(l_txn_id)(l_lot_num).secondary_quantity := l_serial_rec.secondary_quantity;
3834 --l_rti_tb(l_txn_id)(l_lot_num).secondary_uom_code := l_serial_rec.secondary_uom_code;
3835
3836 IF l_rti_tb(l_txn_id)(l_lot_num).source_document_code = 'PO' THEN
3837 l_rti_tb(l_txn_id)(l_lot_num).reference := 'PO_LINE_LOCATION_ID';
3838 l_rti_tb(l_txn_id)(l_lot_num).reference_type_code := 4;
3839 l_rti_tb(l_txn_id)(l_lot_num).reference_id := l_serial_rec.po_line_location_id;
3840 --Bug5662935:For source_document_code 'INVENTORY', reference_type_code is'6'.
3841 ELSIF l_rti_tb(l_txn_id)(l_lot_num).source_document_code = 'INVENTORY' THEN
3842 l_rti_tb(l_txn_id)(l_lot_num).reference := 'SHIPMENT_LINE_ID';
3843 l_rti_tb(l_txn_id)(l_lot_num).reference_type_code := 6;
3844 l_rti_tb(l_txn_id)(l_lot_num).reference_id := l_serial_rec.shipment_line_id;
3845 ELSIF l_rti_tb(l_txn_id)(l_lot_num).source_document_code = 'REQ' THEN
3846 l_rti_tb(l_txn_id)(l_lot_num).reference := 'SHIPMENT_LINE_ID';
3847 l_rti_tb(l_txn_id)(l_lot_num).reference_type_code := 8;
3848 l_rti_tb(l_txn_id)(l_lot_num).reference_id := l_serial_rec.shipment_line_id;
3849 ELSIF l_rti_tb(l_txn_id)(l_lot_num).source_document_code = 'RMA' THEN
3850 l_rti_tb(l_txn_id)(l_lot_num).reference := 'ORDER_LINE_ID';
3851 l_rti_tb(l_txn_id)(l_lot_num).reference_type_code := 7;
3852 l_rti_tb(l_txn_id)(l_lot_num).reference_id := l_serial_rec.oe_order_line_id;
3853 ELSE
3854 -- FAIL HERE AS THERE MAY NOT BE ANY OTHER SOURCE DOCUMENT CODE
3855 IF (l_debug = 1) THEN
3856 print_debug('REF INFO CAN NOT BE RETRIEVVED FROM RT' , 1);
3857 END IF;
3858 fnd_message.set_name('INV', 'INV_FAILED');
3859 fnd_msg_pub.ADD;
3860 RAISE fnd_api.g_exc_error;
3861 END IF;
3862
3863 IF (l_debug = 1) THEN
3864 print_debug('REFERENCE = ' ||l_rti_tb(l_txn_id)(l_lot_num).reference , 1);
3865 print_debug('REFERENCE_TYPE_CODE = '||l_rti_tb(l_txn_id)(l_lot_num).reference_type_code , 1);
3866 print_debug('REFERENCE_ID = ' ||l_rti_tb(l_txn_id)(l_lot_num).reference_id , 1);
3867 END IF;
3868 END IF;
3869 print_debug('before insert msni helper' , 1);
3870 -- INSERT MSNI HERE
3871 l_result := insert_msni_helper(
3872 p_txn_if_id => l_rti_tb(l_txn_id)(l_lot_num).serial_intf_id
3873 , p_serial_number => l_serial_rec.serial_num
3874 , p_org_id => p_organization_id
3875 , p_item_id => p_item_id
3876 , p_product_txn_id => l_rti_tb(l_txn_id)(l_lot_num).rti_id
3877 );
3878 print_debug('after insert msni helper' , 1);
3879 IF NOT l_result THEN
3880 IF (l_debug = 1) THEN
3881 print_debug('Failure while Inserting MSNI records - lot and serial controlled item',1);
3882 END IF;
3883 RAISE fnd_api.g_exc_unexpected_error;
3884 END IF; -- END IF check l_result
3885
3886 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3887 fnd_msg_pub.count_and_get(p_count => l_msg_count, p_data => l_msg_data);
3888 IF (l_debug = 1) THEN
3889 print_debug('insert_msni_helper: Error occurred while creating interface serials: ' || l_msg_data,1);
3890 END IF;
3891 RAISE fnd_api.g_exc_error;
3892 END IF;
3893
3894 END LOOP;
3895
3896 END IF;
3897 -- End changes for bug 14269755
3898
3899 --
3900 -- INSERT LOT ROWS HERE IF IT IS BOTH LOT AND SERIAL CONTROLLED
3901
3902 k := l_rti_tb.first;
3903
3904 LOOP
3905 EXIT WHEN k IS NULL;
3906
3907 l := l_rti_tb(k).first;
3908
3909 LOOP
3910 EXIT WHEN l IS NULL;
3911
3912 --
3913 -- Get the seconadry qty from the Original MTLI
3914 --
3915 IF p_transfer_uom_code = l_primary_uom_code Then
3916 l_qty_to_insert := l_rti_tb(k)(l).quantity;
3917 ELSE
3918 l_qty_to_insert := inv_convert.inv_um_convert
3919 ( item_id => p_item_id,
3920 precision => 6,
3921 from_quantity => l_rti_tb(k)(l).quantity,
3922 from_unit => l_primary_uom_code,
3923 to_unit => p_transfer_uom_code,
3924 from_name => null,
3925 to_name => null );
3926 END IF;
3927
3928 l_uom_code := p_transfer_uom_code;
3929
3930 IF (l_debug = 1) THEN
3931 print_debug(' qty to insert = ' || l_qty_to_insert, 1);
3932 END IF;
3933
3934 rcv_quantities_s.get_available_quantity(
3935 'TRANSFER'
3936 ,l_rti_tb(k)(l).rcv_transaction_id
3937 ,l_rti_tb(k)(l).receipt_source_code
3938 , NULL
3939 , l_rti_tb(k)(l).rcv_transaction_id
3940 , NULL
3941 , l_avail_qty
3942 , l_tolerable_qty
3943 , l_receipt_uom);
3944 if l_rti_tb(k)(l).uom_code <> l_uom_code then
3945
3946 l_receipt_qty := inv_convert.inv_um_convert( item_id => p_item_id,
3947 precision => 6,
3948 from_quantity => l_avail_qty,
3949 from_unit => l_rti_tb(k)(l).uom_code,
3950 to_unit => l_uom_code,
3951 from_name => null,
3952 to_name => null );
3953
3954 l_avail_qty := l_receipt_qty;
3955 End if;
3956
3957 IF l_avail_qty < l_qty_to_insert THEN
3958 -- FAIL THE TXN NOT ENOUGH QTY AVAIABLE TO TRANSACT
3959 IF (l_debug = 1) THEN
3960 print_debug('Avaiable Qty is less than Txn Qty ' , 1);
3961 END IF;
3962 fnd_message.set_name('WMS', 'WMS_TASK_ERROR');
3963 fnd_msg_pub.ADD;
3964 RAISE fnd_api.g_exc_error;
3965 END IF;
3966
3967 IF (l_rti_tb(k)(l).quantity < l_remaining_prim_qty) THEN
3968
3969 IF (p_original_temp_id IS NOT NULL) THEN
3970 IF (l_debug = 1) THEN
3971 print_debug('Calling split_mmtt', 1);
3972 END IF;
3973
3974 inv_rcv_integration_apis.split_mmtt
3975 (p_orig_mmtt_id => p_original_temp_id
3976 ,p_prim_qty_to_splt => l_rti_tb(k)(l).quantity
3977 ,p_prim_uom_code => l_primary_uom_code
3978 ,x_new_mmtt_id => l_mmtt_id_to_insert
3979 ,x_return_status => l_return_status
3980 ,x_msg_count => l_msg_count
3981 ,x_msg_data => l_msg_data
3982 );
3983
3984 IF (l_debug = 1) THEN
3985 print_debug('Returned from split_mmtt',1);
3986 print_debug('x_return_status: '||l_return_status,1);
3987 END IF;
3988
3989 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
3990 IF (l_debug = 1) THEN
3991 print_debug('x_msg_data: '||l_msg_data,1);
3992 print_debug('x_msg_count: '||l_msg_count,1);
3993 print_debug('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM,1);
3994 print_debug('Raising Exception!!!',1);
3995 END IF;
3996 l_progress := '@@@';
3997 RAISE fnd_api.g_exc_unexpected_error;
3998 END IF;
3999 END IF;--END IF (p_original_temp_id IS NOT NULL) THEN
4000
4001 l_remaining_prim_qty := l_remaining_prim_qty-l_rti_tb(k)(l).quantity;
4002 ELSE
4003 l_mmtt_id_to_insert := p_original_temp_id;
4004 l_remaining_prim_qty := 0;
4005 END IF;
4006
4007 IF l <> '@$#_' THEN -- bug 14269755
4008 l_lot_number := Nvl(p_lot_number, l);
4009 END IF;
4010
4011 IF p_lot_control_code = 2 THEN -- bug 14269755
4012 -- CASE FOR BOTH LOT AND SERIAL CONTROLLED ITEM
4013 BEGIN
4014 select secondary_transaction_quantity
4015 into l_original_lot_sec_qty
4016 from mtl_transaction_lots_interface mtli
4017 where mtli.lot_number = p_lot_number
4018 and mtli.product_code = 'RCV'
4019 and mtli.product_transaction_id = p_original_rti_id ;
4020 EXCEPTION
4021 WHEN NO_DATA_FOUND THEN NULL;
4022 END;
4023
4024 IF l_original_sec_qty is not null THEN
4025 l_lot_sec_qty_to_insert := l_original_lot_sec_qty * (l_qty_to_insert / p_transfer_quantity);
4026 END IF;
4027
4028 IF (l_debug = 1) THEN
4029 print_debug('Lot Secondary qty to insert = ' || l_lot_sec_qty_to_insert, 1);
4030 END IF;
4031
4032 IF (l_debug = 1) THEN
4033 print_debug('BEFORE CALLING THE insert_mtli_helper API ', 1);
4034 print_debug(' p_txn_if_id => '|| l_lot_temp_id,1);
4035 print_debug(' p_lot_number => '|| l_rti_tb(k)(l).lot_number,1);
4036 print_debug(' p_txn_qty => '|| l_qty_to_insert,1);
4037 print_debug(' p_prm_qty => '|| l_rti_tb(k)(l).quantity,1);
4038 print_debug(' p_item_id => '|| p_item_id,1);
4039 print_debug(' p_org_id => '|| p_organization_id,1);
4040 print_debug(' p_serial_temp_id=> '|| l_rti_tb(k)(l).serial_intf_id,1);
4041 print_debug(' p_product_txn_id=> '|| l_rti_tb(k)(l).rti_id,1);
4042 print_debug(' p_secondary_quantit=> '|| l_lot_sec_qty_to_insert,1);
4043 print_debug(' p_secondary_uom =>'|| p_sec_transfer_uom_code,1);
4044 END IF;
4045
4046 l_result := insert_mtli_helper
4047 (p_txn_if_id => l_lot_temp_id
4048 , p_lot_number => l_rti_tb(k)(l).lot_number
4049 , p_txn_qty => l_qty_to_insert
4050 , p_prm_qty => l_rti_tb(k)(l).quantity
4051 , p_item_id => p_item_id
4052 , p_org_id => p_organization_id
4053 , p_serial_temp_id => l_rti_tb(k)(l).serial_intf_id
4054 , p_product_txn_id => l_rti_tb(k)(l).rti_id
4055 , p_secondary_quantity => l_lot_sec_qty_to_insert --OPM Convergence
4056 , p_secondary_uom => p_sec_transfer_uom_code); --OPM Convergence
4057
4058 IF NOT l_result THEN
4059 IF (l_debug = 1) THEN
4060 print_debug('Failure while Inserting MTLI records - lot and serial controlled item',1);
4061 END IF;
4062 RAISE fnd_api.g_exc_unexpected_error;
4063 END IF; --END IF check l_result
4064 END IF; --IF P_LOT_NUMBER IS NOT NULL THEN
4065
4066 -- CALL TO RTI HERE
4067
4068 l_reference := l_rti_tb(k)(l).reference;
4069 l_reference_id := l_rti_tb(k)(l).reference_id;
4070 l_reference_type_code := l_rti_tb(k)(l).reference_type_code;
4071 l_new_intf_id := l_rti_tb(k)(l).rti_id;
4072 l_parent_txn_id := l_rti_tb(k)(l).rcv_transaction_id;
4073
4074
4075 IF P_SEC_TRANSFER_UOM_CODE is not null THEN
4076 l_secondary_quantity := p_sec_transfer_quantity * (l_qty_to_insert/p_transfer_quantity);
4077 END IF;
4078
4079 IF (l_debug = 1) THEN
4080 print_debug('BEFORE CALLING THE TRANSFER API ', 1);
4081 END IF;
4082
4083 inv_rcv_std_transfer_apis.create_transfer_rcvtxn_rec
4084 (x_return_status => l_return_status
4085 , x_msg_count => l_msg_count
4086 , x_msg_data => l_msg_data
4087 , p_organization_id => p_organization_id
4088 , p_parent_txn_id => l_parent_txn_id
4089 , p_reference_id => l_reference_id
4090 , p_reference => l_reference
4091 , p_reference_type_code => l_reference_type_code
4092 , p_item_id => p_item_id
4093 , p_revision => p_revision
4094 , p_subinventory_code => p_subinventory_code
4095 , p_locator_id => p_locator_id
4096 , p_transfer_quantity => l_qty_to_insert
4097 , p_transfer_uom_code => p_transfer_uom_code
4098 , p_lot_control_code => p_lot_control_code
4099 , p_serial_control_code => p_serial_control_code
4100 , p_original_rti_id => l_new_intf_id
4101 , p_original_temp_id => l_mmtt_id_to_insert
4102 , p_lot_number => p_lot_number
4103 , p_lpn_id => p_lpn_id
4104 , p_transfer_lpn_id => p_transfer_lpn_id
4105 , p_sec_transfer_quantity => l_secondary_quantity
4106 , p_sec_transfer_uom_code => p_sec_transfer_uom_code
4107 );
4108
4109 IF (l_debug = 1) THEN
4110 print_debug('AFTER CALLING THE TRANSFER API: STATUS = '||l_return_status, 1);
4111 END IF;
4112
4113 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
4114 fnd_message.set_name('WMS', 'WMS_TASK_ERROR');
4115 fnd_msg_pub.ADD;
4116 RAISE fnd_api.g_exc_unexpected_error;
4117 ELSIF l_return_status = fnd_api.g_ret_sts_error THEN
4118 fnd_message.set_name('WMS', 'WMS_TASK_ERROR');
4119 fnd_msg_pub.ADD;
4120 RAISE fnd_api.g_exc_error;
4121 END IF;
4122
4123 l := l_rti_tb(k).next(l);
4124 END LOOP;
4125
4126 k := l_rti_tb.next(k);
4127 END LOOP;
4128 END IF; -- End of serial Controlled
4129
4130 IF (l_debug = 1) THEN
4131 print_debug('MATCH TRANSFER RETURNING WITH SUCCESS RETUN STATUS = ' ||x_return_status, 1);
4132 END IF;
4133
4134 EXCEPTION
4135 WHEN FND_API.G_EXC_ERROR THEN
4136 rollback to match_rti_ss;
4137 x_return_status := FND_API.G_RET_STS_ERROR;
4138 fnd_msg_pub.count_and_get (
4139 p_encoded => FND_API.G_FALSE
4140 , p_count => x_msg_count
4141 , p_data => x_msg_data );
4142 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4143 rollback to match_rti_ss;
4144 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4145 fnd_msg_pub.count_and_get (
4146 p_encoded => FND_API.G_FALSE
4147 , p_count => x_msg_count
4148 , p_data => x_msg_data );
4149 WHEN OTHERS THEN
4150 rollback to match_rti_ss;
4151 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4152 fnd_msg_pub.count_and_get (
4153 p_encoded => FND_API.G_FALSE
4154 , p_count => x_msg_count
4155 , p_data => x_msg_data );
4156 IF SQLCODE IS NOT NULL THEN
4157 inv_mobile_helper_functions.sql_error (
4158 'INV_RCV_STD_TRANSFER_APIS.MATCH_TRANSFER_RCVTXN_REC',
4159 l_progress,
4160 SQLCODE);
4161 END IF;
4162 END Match_transfer_rcvtxn_rec;
4163
4164 END INV_RCV_STD_TRANSFER_APIS;
4165