[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 2006/11/22 05:40:13 anviswan 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 $)'
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 SELECT expiration_date
2784 , status_id
2785 INTO l_expiration_date
2786 , l_lot_status_id
2787 FROM mtl_lot_numbers
2788 WHERE lot_number = p_lot_number
2789 AND inventory_item_id = p_item_id
2790 AND organization_id = p_org_id;
2791
2792 IF (l_txn_if_id IS NULL) THEN
2793 BEGIN
2794 SELECT mtl_material_transactions_s.NEXTVAL
2795 INTO l_txn_if_id
2796 FROM sys.dual;
2797 EXCEPTION
2798 WHEN OTHERS THEN
2799 IF (l_debug = 1) THEN
2800 print_debug('insert_mtli_helper: Error retrieving from seq.',1);
2801 print_debug('insert_mtli_helper: SQLCODE: '||SQLCODE||' SQLERRM:'||Sqlerrm,1);
2802 END IF;
2803 END;
2804 END IF;
2805
2806 IF (l_debug = 1) THEN
2807 print_debug('insert_mtli_helper: l_txn_if_id: '||l_txn_if_id,1);
2808 END IF;
2809
2810 --Call the insert_mtli API
2811 inv_rcv_integration_pvt.insert_mtli
2812 (p_product_transaction_id => l_product_txn_id
2813 ,p_product_code => l_prod_code
2814 ,p_interface_id => l_txn_if_id
2815 ,p_org_id => p_org_id
2816 ,p_item_id => p_item_id
2817 ,p_lot_number => p_lot_number
2818 ,p_transaction_quantity => p_txn_qty
2819 ,p_primary_quantity => p_prm_qty
2820 ,p_serial_interface_id => p_serial_temp_id
2821 ,x_return_status => l_return_status
2822 ,x_msg_count => l_msg_count
2823 ,x_msg_data => l_msg_data
2824 ,p_sec_qty => p_secondary_quantity
2825 );
2826
2827 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2828 fnd_msg_pub.count_and_get(p_count => l_msg_count, p_data => l_msg_data);
2829 IF (l_debug = 1) THEN
2830 print_debug('insert_mtli_helper: Error occurred while creating interface lots: ' || l_msg_data,1);
2831 END IF;
2832 RETURN FALSE;
2833 END IF;
2834
2835 p_txn_if_id := l_txn_if_id;
2836
2837 RETURN TRUE;
2838
2839 EXCEPTION
2840 WHEN OTHERS THEN
2841 IF (l_debug = 1) THEN
2842 print_debug('Exception occurred in insert_mtli_helper: ',1);
2843 END IF;
2844 RETURN FALSE;
2845 END insert_mtli_helper;
2846
2847 FUNCTION insert_msni_helper(
2848 p_txn_if_id IN OUT NOCOPY NUMBER
2849 , p_serial_number IN VARCHAR2
2850 , p_item_id IN NUMBER
2851 , p_org_id IN NUMBER
2852 , p_product_txn_id IN OUT NOCOPY NUMBER
2853 ) RETURN BOOLEAN IS
2854 --Local variables
2855 l_serial_status_id NUMBER;
2856 l_txn_if_id NUMBER := p_txn_if_id;
2857 l_product_txn_id NUMBER := p_product_txn_id;
2858 l_prod_code VARCHAR2(5) := inv_rcv_integration_apis.G_PROD_CODE;
2859 l_yes VARCHAR2(1) := inv_rcv_integration_apis.G_YES;
2860 l_no VARCHAR2(1) := inv_rcv_integration_apis.G_NO;
2861 l_false VARCHAR2(1) := inv_rcv_integration_apis.G_FALSE;
2862 l_return_status VARCHAR2(1);
2863 l_msg_count NUMBER;
2864 l_msg_data VARCHAR2(10000);
2865 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
2866 BEGIN
2867
2868 --Get the serial status
2869 SELECT status_id
2870 INTO l_serial_status_id
2871 FROM mtl_serial_numbers
2872 WHERE serial_number = p_serial_number
2873 AND inventory_item_id = p_item_id;
2874
2875 --Call the insert_msni API
2876 inv_rcv_integration_apis.insert_msni(
2877 p_api_version => 1.0
2878 , p_init_msg_lst => l_false
2879 , x_return_status => l_return_status
2880 , x_msg_count => l_msg_count
2881 , x_msg_data => l_msg_data
2882 , p_transaction_interface_id => l_txn_if_id
2883 , p_fm_serial_number => p_serial_number
2884 , p_to_serial_number => p_serial_number
2885 , p_organization_id => p_org_id
2886 , p_inventory_item_id => p_item_id
2887 , p_status_id => l_serial_status_id
2888 , p_product_transaction_id => l_product_txn_id
2889 , p_product_code => l_prod_code
2890 , p_att_exist => l_yes
2891 , p_update_msn => l_no);
2892
2893 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2894 fnd_msg_pub.count_and_get(p_count => l_msg_count, p_data => l_msg_data);
2895 IF (l_debug = 1) THEN
2896 print_debug('insert_msni_helper: Error occurred while creating interface serials: ' || l_msg_data,1);
2897 END IF;
2898 RETURN FALSE;
2899 END IF;
2900
2901 IF (l_debug = 1) THEN
2902 print_debug('insert_msni_helper: msni '||p_txn_if_id||' inserted for serial '||p_serial_number,1);
2903 END IF;
2904
2905 RETURN TRUE;
2906
2907 EXCEPTION
2908 WHEN OTHERS THEN
2909 IF (l_debug = 1) THEN
2910 print_debug('Exception occurred in insert_msni_helper: ',1);
2911 END IF;
2912 RETURN FALSE;
2913 END insert_msni_helper;
2914
2915
2916 PROCEDURE Match_transfer_rcvtxn_rec(
2917 x_return_status OUT NOCOPY VARCHAR2
2918 , x_msg_count OUT NOCOPY NUMBER
2919 , x_msg_data OUT NOCOPY VARCHAR2
2920 , p_organization_id IN NUMBER
2921 , p_parent_txn_id IN NUMBER
2922 , p_reference_id IN NUMBER
2923 , p_reference IN VARCHAR2
2924 , p_reference_type_code IN NUMBER
2925 , p_item_id IN NUMBER
2926 , p_revision IN VARCHAR2
2927 , p_subinventory_code IN VARCHAR2
2928 , p_locator_id IN NUMBER
2929 , p_transfer_quantity IN NUMBER
2930 , p_transfer_uom_code IN VARCHAR2
2931 , p_lot_control_code IN NUMBER
2932 , p_serial_control_code IN NUMBER
2933 , p_original_rti_id IN NUMBER DEFAULT NULL
2934 , p_original_temp_id IN NUMBER DEFAULT NULL
2935 , p_lot_number IN VARCHAR2 DEFAULT NULL
2936 , p_lpn_id IN NUMBER DEFAULT NULL
2937 , p_transfer_lpn_id IN NUMBER DEFAULT NULL
2938 , p_sec_transfer_quantity IN NUMBER DEFAULT NULL --OPM Convergence
2939 , p_sec_transfer_uom_code IN VARCHAR2 DEFAULT NULL --OPM Convergence
2940 , p_inspection_status IN NUMBER DEFAULT NULL
2941 , p_primary_uom_code IN VARCHAR2
2942 , p_from_sub IN VARCHAR2 DEFAULT NULL --Needed for matching non-lpn materials
2943 , p_from_loc IN NUMBER DEFAULT NULL) --Needed for matching non-lpn materials
2944 IS
2945
2946 CURSOR c_serial_cur IS
2947 SELECT distinct
2948 rsl.source_document_code source_document_code
2949 ,rsl.po_line_location_id po_line_location_id
2950 ,rsl.po_distribution_id po_distribution_id
2951 ,rsl.shipment_line_id shipment_line_id
2952 ,rsl.oe_order_line_id oe_order_line_id
2953 ,rsh.receipt_source_code receipt_source_code
2954 ,rss.serial_num serial_num
2955 ,rt.uom_code uom_code
2956 ,rss.transaction_id rcv_transaction_id
2957 ,rss.lot_num lot_num
2958 ,rs.secondary_quantity secondary_quantity
2959 ,msni.transaction_interface_id transaction_interface_id
2960 ,rsl.asn_line_flag asn_line_flag
2961 FROM rcv_supply rs,
2962 rcv_transactions rt,
2963 rcv_serials_supply rss,
2964 rcv_shipment_lines rsl,
2965 rcv_shipment_headers rsh,
2966 mtl_serial_numbers_interface msni
2967 WHERE rs.item_id = p_item_id
2968 AND Nvl(rs.item_revision,nvl(p_revision,'@@@')) = nvl(p_revision,'@@@')
2969 AND rs.to_organization_id = p_organization_id
2970 AND nvl(rs.lpn_id,-1) = nvl(p_lpn_id,-1)
2971 AND rs.rcv_transaction_id = rt.transaction_id
2972 AND msni.product_code = 'RCV'
2973 AND msni.product_transaction_id = p_original_rti_id
2974 AND rss.serial_num between msni.fm_serial_number and msni.to_serial_number
2975 AND nvl(rss.lot_num,'@$#_') = nvl(p_lot_number, '@$#_')
2976 AND rss.supply_type_code = 'RECEIVING'
2977 AND rs.shipment_line_id = rsl.shipment_line_id
2978 AND rs.rcv_transaction_id = rss.transaction_id
2979 AND rsh.shipment_header_id = rsl.shipment_header_id
2980 AND decode(rt.routing_header_id, 2,
2981 decode(rt.inspection_status_code,'NOT INSPECTED',1, 'ACCEPTED',2,'REJECTED', 3)
2982 ,-1) = nvl(p_inspection_status, -1)
2983 ORDER BY rcv_transaction_id
2984 ;
2985
2986 CURSOR c_rtv_cur(v_from_sub VARCHAR2, v_from_locator_id NUMBER) IS
2987 SELECT
2988 rsl.source_document_code source_document_code
2989 ,rsl.po_line_location_id po_line_location_id
2990 ,rsl.po_distribution_id po_distribution_id
2991 ,rsl.shipment_line_id shipment_line_id
2992 ,rsl.oe_order_line_id oe_order_line_id
2993 ,rs.supply_source_id supply_source_id
2994 ,rs.rcv_transaction_id rcv_transaction_id
2995 ,rsh.receipt_source_code receipt_source_code
2996 ,rt.uom_code uom_code
2997 ,rs.secondary_quantity secondary_quantity
2998 /*Bug 5511398:In case the receipt transcation for WMS enabled org is done via
2999 desktop, then there will not be data in RLS table,in which case fetch the qty
3000 from rcv_supply .*/
3001 /*,Nvl(rls.primary_quantity,0) lot_prim_qty
3002 ,Nvl(rls.quantity,0) lot_qty*/
3003 ,nvl(rls.primary_quantity,rs.to_org_primary_quantity) lot_prim_qty
3004 ,nvl(rls.quantity,rs.quantity) lot_qty
3005 ,decode(rt.uom_code, p_transfer_uom_code, 1, 2) ORDERING1
3006 ,decode(rt.uom_code, p_transfer_uom_code, (p_transfer_quantity - rs.quantity), 0) ORDERING2
3007 ,rsl.asn_line_flag asn_line_flag
3008 FROM rcv_supply rs,
3009 rcv_transactions rt,
3010 rcv_shipment_lines rsl,
3011 rcv_lots_supply rls,
3012 rcv_shipment_headers rsh
3013 WHERE rs.item_id = p_item_id
3014 AND Nvl(rs.item_revision,nvl(p_revision,'@@@@')) = nvl(p_revision,'@@@@')
3015 AND rs.to_organization_id = p_organization_id
3016 AND nvl(rs.lpn_id,-1) = nvl(p_lpn_id,-1)
3017 AND nvl(rt.subinventory,'@$#_') = nvl(v_from_sub,'@$#_')
3018 AND nvl(rt.locator_id,-1) = nvl(v_from_locator_id, -1)
3019 AND nvl(rt.transfer_lpn_id,-1) = nvl(p_lpn_id,-1)
3020 AND rs.rcv_transaction_id = rt.transaction_id
3021 AND rt.shipment_line_id = rsl.shipment_line_id
3022 AND rs.supply_type_code = 'RECEIVING'
3023 AND rls.transaction_id (+) = rs.supply_source_id
3024 AND nvl(rls.lot_num, '@$#_') = nvl(p_lot_number, '@$#_')
3025 AND rsh.shipment_header_id = rsl.shipment_header_id
3026 AND decode(rt.routing_header_id, 2,
3027 decode(rt.inspection_status_code,'NOT INSPECTED',1, 'ACCEPTED',2,'REJECTED', 3)
3028 ,-1) = nvl(p_inspection_status, -1)
3029 --Bug 5331779 - Begin change
3030 --Adding the following to make sure that we do not pickup RS with serial numbers
3031 AND NOT exists
3032 (SELECT '1' FROM rcv_serials_supply rss
3033 WHERE rss.transaction_id = rs.supply_source_id
3034 AND rss.supply_type_code = 'RECEIVING')
3035 --Bug 5331779-End change
3036 ORDER BY ORDERING1, ORDERING2
3037 ;
3038
3039
3040 --Local Variables
3041 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'), 0);
3042
3043
3044 l_new_rti_info inv_rcv_integration_apis.child_rec_tb_tp;
3045 l_from_sub VARCHAR2(30);
3046 l_from_locator_id NUMBER;
3047 l_split_lot_serial_ok BOOLEAN;
3048 l_receipt_uom VARCHAR2(25);
3049 l_receipt_qty NUMBER;
3050
3051 l_parent_txn_id NUMBER;
3052 l_remaining_prim_qty NUMBER;
3053 l_qty_to_insert NUMBER;
3054 l_avail_qty NUMBER;
3055 l_tolerable_qty NUMBER;
3056 l_avail_prim_qty NUMBER;
3057 l_secondary_quantity NUMBER;
3058 l_original_lot_sec_qty NUMBER;
3059 l_lot_sec_qty_to_insert NUMBER;
3060 l_new_intf_id NUMBER;
3061 l_primary_uom_code VARCHAR2(3);
3062 l_uom_code VARCHAR2(3);
3063 L_RTI_REC_FOUND Boolean ;
3064 l_result Boolean;
3065 l_reference VARCHAR2(240);
3066 l_reference_type_code NUMBER;
3067 l_reference_id NUMBER;
3068 l_return_status VARCHAR2(1):= fnd_api.g_ret_sts_success;
3069 l_msg_count NUMBER;
3070 l_msg_data VARCHAR2(4000);
3071 L_matched_index NUMBER;
3072 l_ser_txn_temp_id NUMBER;
3073 l_progress VARCHAR2(10);
3074 l_original_sec_qty NUMBER;
3075 l_lot_temp_id NUMBER;
3076 l_processed_lot_prim_qty NUMBER;
3077 l_processed_lot_qty NUMBER;
3078
3079 TYPE rti_info IS RECORD
3080 (rti_id NUMBER,
3081 rcv_transaction_id NUMBER,
3082 lot_number VARCHAR2(80),
3083 source_document_code VARCHAR2(25),
3084 po_line_location_id NUMBER,
3085 po_distribution_id NUMBER,
3086 shipment_line_id NUMBER,
3087 oe_order_line_id NUMBER,
3088 receipt_source_code VARCHAR2(25),
3089 serial_intf_id NUMBER,
3090 uom_code VARCHAR2(3),
3091 reference VARCHAR2(240),
3092 REFERENCE_TYPE_CODE NUMBER,
3093 REFERENCE_ID NUMBER,
3094 quantity NUMBER);
3095
3096 l_txn_id NUMBER;
3097 l_lot_num VARCHAR2(80);
3098
3099 TYPE lot_tb_tp IS TABLE OF rti_info INDEX BY VARCHAR2(80);
3100 TYPE rti_tb_tp IS TABLE OF lot_tb_tp INDEX BY BINARY_INTEGER;
3101
3102 l_rti_tb rti_tb_tp;
3103 l_mmtt_id_to_insert NUMBER;
3104 k NUMBER;
3105 l VARCHAR2(80);
3106
3107 BEGIN
3108
3109 --Initialize the return status
3110 x_return_status := FND_API.G_RET_STS_SUCCESS;
3111
3112 SAVEPOINT match_rti_ss;
3113
3114 IF (l_debug = 1) THEN
3115 print_debug('Entered Match_transfer_rcvtxn_rec 10: '|| to_char(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 1);
3116 END IF;
3117
3118 l_progress := 20;
3119
3120
3121 --Print the input parameters
3122 IF (l_debug = 1) THEN
3123 print_debug('******Input parameters passed to the procedure:****** ', 3);
3124 print_debug(' p_lpn_id ===> ' || p_lpn_id, 3);
3125 print_debug(' p_organization_id ===> ' || p_organization_id, 3);
3126 print_debug(' p_parent_txn_id ===> ' || p_parent_txn_id, 3);
3127 print_debug(' p_reference_id ===> ' || p_reference_id, 3);
3128 print_debug(' p_reference ===> ' || p_reference, 3);
3129 print_debug(' p_reference_type_code ===> ' || p_reference_type_code, 3);
3130 print_debug(' p_item_id ===> ' || p_item_id, 3);
3131 print_debug(' p_revision ===> ' || p_revision, 3);
3132 print_debug(' p_subinventory_code ===> ' || p_subinventory_code, 3);
3133 print_debug(' p_locator_id ===> ' || p_locator_id, 3);
3134 print_debug(' p_transfer_quantity ===> ' || p_transfer_quantity, 3);
3135 print_debug(' p_transfer_uom_code ===> ' || p_transfer_uom_code, 3);
3136 print_debug(' p_original_rti_id ===> ' || p_original_rti_id, 3);
3137 print_debug(' p_lot_control_code ===> ' || p_lot_control_code, 3);
3138 print_debug(' p_serial_control_code ===> ' || p_serial_control_code, 3);
3139 print_debug(' p_original_temp_id ===> ' || p_original_temp_id, 3);
3140 print_debug(' p_lot_number ===> ' || p_lot_number, 3);
3141 print_debug(' p_secondary_quantity ===> ' || p_sec_transfer_quantity,3);
3142 print_debug(' p_secondary_uom_code ===> ' || p_sec_transfer_uom_code,3);
3143 print_debug(' p_primary_uom_code ===> ' ||p_primary_uom_code,3);
3144 print_debug(' p_from_sub ===> ' ||p_from_sub,3);
3145 print_debug(' p_from_loc ===> ' ||p_from_loc,3);
3146 print_debug('***************************************************** ', 3);
3147 END IF;
3148
3149 l_primary_uom_code := p_primary_uom_code;
3150
3151 IF (p_lpn_id IS NULL) THEN
3152 l_from_sub := p_from_sub;
3153 l_from_locator_id := p_from_loc;
3154 ELSE
3155 BEGIN
3156 select subinventory_code
3157 , locator_id
3158 into l_from_sub
3159 ,l_from_locator_id
3160 from wms_license_plate_numbers wlpn
3161 where wlpn.lpn_id = p_lpn_id;
3162 EXCEPTION
3163 WHEN NO_DATA_FOUND THEN
3164 l_from_sub := NULL;
3165 l_from_locator_id := NULL;
3166 END;
3167 END IF;
3168
3169 l_remaining_prim_qty := inv_convert.inv_um_convert(
3170 item_id => p_item_id,
3171 precision => 6,
3172 from_quantity => p_transfer_quantity,
3173 from_unit => p_transfer_uom_code,
3174 to_unit => l_primary_uom_code,
3175 from_name => null,
3176 to_name => null);
3177
3178 IF (p_serial_control_code = 1 OR p_serial_control_code = 6) then
3179 -- CASE WHERE ITEM IS NOT SERIAL CONTROLLED
3180 -- OR CASE WHEN SERIAL CONTROLLED CODE IS AT SO ISSUE Bug: 5331779
3181 For l_rtv_rec in c_rtv_cur(l_from_sub, l_from_locator_id)
3182 Loop
3183
3184 IF l_remaining_prim_qty <= 0 THEN
3185 EXIT;
3186 END If;
3187
3188 IF (l_debug = 1) THEN
3189 print_debug('INSIDE MATCHING LOOP TO FIND THE PARENT TXN ' , 1);
3190 print_debug('SOURCE_DOCUMNET_CODE = '|| l_rtv_rec.source_document_code , 1);
3191 print_debug('po_line_location_id = ' || l_rtv_rec.po_line_location_id, 1);
3192 print_debug('shipment_line_id = ' || l_rtv_rec.shipment_line_id , 1);
3193 print_debug('oe_order_line_id ' || l_rtv_rec.oe_order_line_id , 1);
3194 print_debug('supply_source_id ' || l_rtv_rec.supply_source_id, 1);
3195 print_debug('receipt_source_code ' || l_rtv_rec.receipt_source_code, 1);
3196 print_debug('transaction id ' || l_rtv_rec.rcv_transaction_id, 1);
3197 print_debug('lot primary quantity ' || l_rtv_rec.lot_prim_qty, 1);
3198 END IF;
3199
3200 IF (p_lot_control_code = 2) THEN
3201 IF (l_debug = 1) THEN
3202 print_debug('Querying MTLI to determine available QTY ' , 1);
3203 END IF;
3204
3205 BEGIN
3206 SELECT SUM(primary_quantity)
3207 , SUM(transaction_quantity)
3208 INTO l_processed_lot_prim_qty
3209 , l_processed_lot_qty
3210 FROM mtl_transaction_lots_interface
3211 WHERE product_code = 'RCV'
3212 AND product_transaction_id
3213 IN (SELECT interface_transaction_id
3214 FROM rcv_transactions_interface
3215 WHERE parent_transaction_id = l_rtv_rec.rcv_transaction_id
3216 )
3217 AND lot_number = p_lot_number;
3218 EXCEPTION
3219 WHEN OTHERS THEN
3220 l_processed_lot_prim_qty := 0;
3221 l_processed_lot_qty := 0;
3222 END;
3223
3224 IF (l_debug = 1) THEN
3225 print_debug('processed lot prim qty: '||l_processed_lot_prim_qty , 1);
3226 print_debug('processed lot qty: '|| l_processed_lot_qty, 1);
3227 END IF;
3228
3229 l_avail_prim_qty := l_rtv_rec.lot_prim_qty - Nvl(l_processed_lot_prim_qty,0);
3230 l_avail_qty := l_rtv_rec.lot_qty - Nvl(l_processed_lot_qty,0);
3231
3232 IF (l_debug = 1) THEN
3233 print_debug('available lot qty: '||l_avail_prim_qty , 1);
3234 END IF;
3235
3236 ELSE
3237 IF (l_debug = 1) THEN
3238 print_debug('BEFORE GETTING AVAIL QTY ' , 1);
3239 END IF;
3240
3241 rcv_quantities_s.get_available_quantity(
3242 'TRANSFER'
3243 , l_rtv_rec.rcv_transaction_id
3244 , l_rtv_rec.receipt_source_code
3245 , NULL
3246 , l_rtv_rec.rcv_transaction_id
3247 , NULL
3248 , l_avail_qty
3249 , l_tolerable_qty
3250 , l_receipt_uom);
3251 IF (l_debug = 1) THEN
3252 print_debug('AFTER GETTING AVAIL QTY. AVAIL QTY: '||l_avail_qty , 1);
3253 END IF;
3254
3255
3256 l_avail_prim_qty := inv_convert.inv_um_convert( item_id => p_item_id,
3257 precision => 6,
3258 from_quantity => l_avail_qty,
3259 from_unit => l_rtv_rec.uom_code,
3260 to_unit => l_primary_uom_code,
3261 from_name => null,
3262 to_name => null);
3263
3264 IF (l_debug = 1) THEN
3265 print_debug('AVAIL QTY = ' || l_avail_qty , 1);
3266 print_debug('AVAIL_PRIM QTY = ' || l_avail_prim_qty , 1);
3267 print_debug('REMAINING_PRIM QTY = '|| l_remaining_prim_qty , 1);
3268 END IF;
3269 END IF;
3270
3271 IF l_avail_prim_qty <= 0 THEN
3272 IF (l_debug = 1) THEN
3273 print_debug('SKIPPING THIS LINE',1);
3274 END IF;
3275 GOTO nextrtrecord;
3276 END IF;
3277
3278
3279 SELECT rcv_transactions_interface_s.NEXTVAL
3280 INTO l_new_intf_id
3281 FROM dual;
3282
3283 IF (l_avail_prim_qty < l_remaining_prim_qty ) THEN
3284 IF (p_transfer_uom_code <> l_rtv_rec.uom_code) THEN
3285 l_qty_to_insert := inv_convert.inv_um_convert( item_id => p_item_id,
3286 precision => 6,
3287 from_quantity => l_avail_qty,
3288 from_unit => l_rtv_rec.uom_code,
3289 to_unit => p_transfer_uom_code,
3290 from_name => null,
3291 to_name => null );
3292
3293
3294 -- Qty to Insert IS
3295 IF (l_debug = 1) THEN
3296 print_debug('QTY TO INSERT1: = '||L_QTY_TO_INSERT , 1);
3297 END IF;
3298 ELSE
3299 --
3300 -- WHICH MEANS THE DELIVER TRANSACTION UOM CODE
3301 -- IS SAME AS THAN THE RECEIPT TRANSACTION UOM CODE
3302 --
3303 l_qty_to_insert := l_avail_qty;
3304
3305 IF (l_debug = 1) THEN
3306 print_debug('QTY TO INSERT2: = '||L_QTY_TO_INSERT , 1);
3307 END IF;
3308
3309 END IF; -- IF (p_rcvtxn_uom_code <> l_rtv_rec.uom_code
3310
3311 l_remaining_prim_qty := l_remaining_prim_qty - l_avail_prim_qty;
3312
3313 IF (l_debug = 1) THEN
3314 print_debug('INTF ID = '||l_new_intf_id , 1);
3315 END IF;
3316
3317 IF (p_original_temp_id IS NOT NULL) THEN
3318 IF (l_debug = 1) THEN
3319 print_debug('Calling split_mmtt', 1);
3320 END IF;
3321
3322 inv_rcv_integration_apis.split_mmtt
3323 (p_orig_mmtt_id => p_original_temp_id
3324 ,p_prim_qty_to_splt => l_avail_prim_qty
3325 ,p_prim_uom_code => l_primary_uom_code
3326 ,x_new_mmtt_id => l_mmtt_id_to_insert
3327 ,x_return_status => l_return_status
3328 ,x_msg_count => l_msg_count
3329 ,x_msg_data => l_msg_data
3330 );
3331
3332 IF (l_debug = 1) THEN
3333 print_debug('Returned from split_mmtt',1);
3334 print_debug('x_return_status: '||l_return_status,1);
3335 END IF;
3336
3337 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
3338 IF (l_debug = 1) THEN
3339 print_debug('x_msg_data: '||l_msg_data,1);
3340 print_debug('x_msg_count: '||l_msg_count,1);
3341 print_debug('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM,1);
3342 print_debug('Raising Exception!!!',1);
3343 END IF;
3344 l_progress := '@@@';
3345 RAISE fnd_api.g_exc_unexpected_error;
3346 END IF;
3347 END IF;--END IF (p_original_temp_id IS NOT NULL) THEN
3348
3349 IF (p_lot_control_code = 2) THEN
3350 l_new_rti_info(1).orig_interface_trx_id := p_original_rti_id;
3351 l_new_rti_info(1).new_interface_trx_id := l_new_intf_id;
3352 l_new_rti_info(1).quantity := L_qty_to_insert;
3353
3354 l_new_rti_info(1).to_organization_id := p_organization_id;
3355 l_new_rti_info(1).item_id := p_item_id;
3356 l_new_rti_info(1).uom_code := p_transfer_uom_code;
3357
3358 IF (l_remaining_prim_qty > 0) THEN
3359 IF (p_transfer_uom_code <> l_primary_uom_code) THEN
3360 l_new_rti_info(2).quantity := inv_convert.inv_um_convert
3361 ( item_id => p_item_id,
3362 precision => 6,
3363 from_quantity => l_remaining_prim_qty,
3364 from_unit => l_primary_uom_code,
3365 to_unit => p_transfer_uom_code,
3366 from_name => null,
3367 to_name => null );
3368 ELSE
3369 l_new_rti_info(2).quantity := l_remaining_prim_qty;
3370 END IF;
3371
3372 l_new_rti_info(2).orig_interface_trx_id := p_original_rti_id;
3373 l_new_rti_info(2).new_interface_trx_id := p_original_rti_id;
3374
3375 l_new_rti_info(2).to_organization_id := p_organization_id;
3376 l_new_rti_info(2).item_id := p_item_id;
3377 l_new_rti_info(2).uom_code := p_transfer_uom_code;
3378 END IF;
3379
3380 IF (l_debug = 1) THEN
3381 print_debug('Before calling the split lot serial', 1);
3382 END IF;
3383
3384 l_split_lot_serial_ok := inv_rcv_integration_apis.split_lot_serial
3385 (p_api_version => 1.0
3386 , p_init_msg_lst => FND_API.G_FALSE
3387 , x_return_status => l_return_status
3388 , x_msg_count => l_msg_count
3389 , x_msg_data => l_msg_data
3390 , p_new_rti_info => l_new_rti_info);
3391
3392
3393 IF (l_debug = 1) THEN
3394 print_debug('After calling the split lot serial', 1);
3395 END IF;
3396
3397 IF ( NOT l_split_lot_serial_ok) THEN
3398 IF (l_debug = 1) THEN
3399 print_debug(' MATCH_TRANSFER_INTF_REC: Failure in split_lot_serial ', 4);
3400 END IF;
3401 RAISE FND_API.G_EXC_ERROR;
3402 END IF;
3403 END IF;
3404
3405 ELSE
3406 l_qty_to_insert := inv_convert.inv_um_convert(
3407 item_id => p_item_id,
3408 precision => 6,
3409 from_quantity => l_remaining_prim_qty,
3410 from_unit => l_primary_uom_code,
3411 to_unit => p_transfer_uom_code,
3412 from_name => null,
3413 to_name => null );
3414
3415 IF (l_debug = 1) THEN
3416 print_debug('QTY TO INSERT3: = ' || L_QTY_TO_INSERT , 1);
3417 END IF;
3418
3419 IF (p_lot_control_code = 2) THEN
3420 UPDATE mtl_transaction_lots_interface
3421 SET product_transaction_id = l_new_intf_id
3422 WHERE product_transaction_id = p_original_rti_id
3423 AND product_code = 'RCV';
3424 END IF;
3425
3426 l_remaining_prim_qty := 0;
3427 l_mmtt_id_to_insert := p_original_temp_id;
3428 END IF;
3429
3430 IF l_rtv_rec.source_document_code = 'PO' THEN
3431 L_reference := 'PO_LINE_LOCATION_ID';
3432 L_reference_type_code := 4;
3433 L_reference_id := l_rtv_rec.po_line_location_id ;
3434 --Bug5662935:For source_document_code 'INVENTORY', reference_type_code is'6'.
3435 ELSIF l_rtv_rec.source_document_code = 'INVENTORY' THEN
3436 L_reference := 'SHIPMENT_LINE_ID';
3437 L_reference_type_code := 6;
3438 L_reference_id := l_rtv_rec.shipment_line_id;
3439 ELSIF l_rtv_rec.source_document_code = 'REQ' THEN
3440 L_reference := 'SHIPMENT_LINE_ID';
3441 L_reference_type_code := 8;
3442 L_reference_id := l_rtv_rec.shipment_line_id;
3443 ELSIF l_rtv_rec.source_document_code = 'RMA' THEN
3444 L_reference := 'ORDER_LINE_ID';
3445 L_reference_type_code := 7;
3446 L_reference_id := l_rtv_rec.oe_order_line_id;
3447 ELSE
3448 -- FAIL HERE AS THERE MAY NOT BE ANY OTHER SOURCE DOCUMENT CODE
3449 IF (l_debug = 1) THEN
3450 print_debug('REFERENCE INFO CAN NOT BE RETRIEVVED FROM RT' , 1);
3451 END IF;
3452 fnd_message.set_name('INV', 'INV_FAILED');
3453 fnd_msg_pub.ADD;
3454 RAISE fnd_api.g_exc_error;
3455 END IF;
3456
3457 IF (l_debug = 1) THEN
3458 print_debug('REFERENCE = '||L_REFERENCE , 1);
3459 print_debug('REFERENCE_TYPE_CODE = '||L_REFERENCE_TYPE_CODE , 1);
3460 print_debug('REFERENCE_ID = '||L_REFERENCE_ID , 1);
3461 END IF;
3462
3463
3464
3465 -- If the secondary_uom_code is not null then calculate the secondary
3466 -- Qty based on percentage of transaction quantity
3467
3468 if p_sec_transfer_uom_code is not null Then
3469 IF (l_debug = 1) THEN
3470 print_debug('CALCULATING SEC QTY' , 1);
3471 END IF;
3472 l_secondary_quantity := p_sec_transfer_quantity * (l_qty_to_insert/p_transfer_quantity);
3473 Else
3474 l_secondary_quantity := null;
3475 End if;
3476
3477 IF (l_debug = 1) THEN
3478 print_debug('SECONDARY UOM CODE = '|| p_sec_transfer_uom_code , 1);
3479 print_debug('SECONDARY QTY = '|| l_secondary_quantity, 1);
3480 END IF;
3481
3482 inv_rcv_std_transfer_apis.create_transfer_rcvtxn_rec(
3483 x_return_status => l_return_status
3484 , x_msg_count => l_msg_count
3485 , x_msg_data => l_msg_data
3486 , p_organization_id => p_organization_id
3487 , p_parent_txn_id => l_rtv_rec.rcv_transaction_id
3488 , p_reference_id => l_reference_id
3489 , p_reference => l_reference
3490 , p_reference_type_code => l_reference_type_code
3491 , p_item_id => p_item_id
3492 , p_revision => p_revision
3493 , p_subinventory_code => p_subinventory_code
3494 , p_locator_id => p_locator_id
3495 , p_transfer_quantity => l_qty_to_insert
3496 , p_transfer_uom_code => p_transfer_uom_code
3497 , p_lot_control_code => p_lot_control_code
3498 , p_serial_control_code => p_serial_control_code
3499 , p_original_rti_id => l_new_intf_id
3500 , p_original_temp_id => l_mmtt_id_to_insert
3501 , p_lot_number => p_lot_number
3502 , p_lpn_id => p_lpn_id
3503 , p_transfer_lpn_id => p_transfer_lpn_id
3504 , p_sec_transfer_quantity => l_secondary_quantity
3505 , p_sec_transfer_uom_code => p_sec_transfer_uom_code
3506 );
3507
3508 IF (l_debug = 1) THEN
3509 print_debug('AFTER CALLING THE TRANSFER API: STATUS = '|| l_return_status, 1);
3510 END IF;
3511
3512 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
3513 fnd_message.set_name('WMS', 'WMS_TASK_ERROR');
3514 fnd_msg_pub.ADD;
3515 RAISE fnd_api.g_exc_unexpected_error;
3516 ELSIF l_return_status = fnd_api.g_ret_sts_error THEN
3517 fnd_message.set_name('WMS', 'WMS_TASK_ERROR');
3518 fnd_msg_pub.ADD;
3519 RAISE fnd_api.g_exc_error;
3520 END IF;
3521
3522 <<nextrtrecord>>
3523 NULL;
3524 End Loop; -- End of Recipt' txns Loop
3525 --Bug 5331779-Begin change
3526 --If l_remaining_prim_qty > 0 then only
3527 --fail if the serial control code is 1 because for serial control
3528 --code of 6, there may be lines with serial number which are yet to
3529 --be processed.
3530 IF p_serial_control_code = 1 AND l_remaining_prim_qty > 0 THEN
3531 -- Bug 5331779 -End change
3532 -- IF l_remaining_prim_qty > 0 THEN
3533
3534 IF (l_debug = 1) THEN
3535 print_debug('COUND NOT MATCH RECEIPT TRANSACTION FOR THE QTY TO BE TRANSFERRED: FAILURE ', 1);
3536 END IF;
3537
3538 -- Unable to match RS with quantity.
3539 fnd_message.set_name('WMS', 'WMS_TASK_ERROR');
3540 fnd_msg_pub.ADD;
3541 RAISE FND_API.g_exc_error;
3542
3543 END IF;
3544 --Bug 5331779-Begin Change
3545 --Instead of an else block we need to have
3546 --another if block because for items with serial control code 6, we
3547 --want both blocks to be executed.
3548 END IF;
3549 --ELSE -- ITEM IS SERIAL CONTROLLED.
3550
3551 IF ((p_serial_control_code NOT IN (1,6))
3552 OR (p_serial_control_code = 6 AND l_remaining_prim_qty > 0)) THEN
3553 --Bug 5331779- End change
3554
3555 L_RTI_REC_FOUND := FALSE;
3556
3557 FOR l_serial_rec in C_SERIAL_CUR
3558 LOOP
3559
3560
3561 IF (l_debug = 1) THEN
3562 print_debug('INSIDE SERIAL CURSOR LOOP ', 1);
3563 END IF;
3564
3565 IF (l_debug = 1) THEN
3566 print_debug('COUNT OF RTI ROWS '|| l_rti_tb.COUNT, 1);
3567 END IF;
3568
3569 l_txn_id := l_serial_rec.rcv_transaction_id;
3570 l_lot_num := nvl(l_serial_rec.lot_num, '@$#_');
3571
3572 IF (l_rti_tb.exists(l_txn_id) AND
3573 l_rti_tb(l_txn_id).exists(l_lot_num)) THEN
3574
3575 l_rti_tb(l_txn_id)(l_lot_num).quantity
3576 := l_rti_tb(l_txn_id)(l_lot_num).quantity + 1;
3577
3578 ELSE
3579 SELECT rcv_transactions_interface_s.NEXTVAL
3580 INTO l_new_intf_id
3581 FROM dual;
3582
3583 l_rti_tb(l_txn_id)(l_lot_num).rti_id := l_new_intf_id;
3584
3585 IF (p_lot_number IS NOT NULL) THEN
3586
3587 -- Generate the Serial Txn Temp Id
3588
3589 select mtl_material_transactions_s.nextval
3590 into l_ser_txn_temp_id from dual;
3591
3592 l_rti_tb(l_txn_id)(l_lot_num).lot_number := l_serial_rec.lot_num;
3593 l_rti_tb(l_txn_id)(l_lot_num).serial_intf_id := l_ser_txn_temp_id;
3594 ELSE
3595 l_rti_tb(l_txn_id)(l_lot_num).serial_intf_id := null;
3596 END IF;
3597
3598 l_rti_tb(l_txn_id)(l_lot_num).rcv_transaction_id := l_serial_rec.rcv_transaction_id;
3599 l_rti_tb(l_txn_id)(l_lot_num).po_line_location_id := l_serial_rec.po_line_location_id;
3600 l_rti_tb(l_txn_id)(l_lot_num).po_distribution_id := l_serial_rec.po_distribution_id;
3601 l_rti_tb(l_txn_id)(l_lot_num).uom_code := l_serial_rec.uom_code;
3602 l_rti_tb(l_txn_id)(l_lot_num).source_document_code := l_serial_rec.source_document_code;
3603 l_rti_tb(l_txn_id)(l_lot_num).quantity := 1;
3604 l_rti_tb(l_txn_id)(l_lot_num).receipt_source_code := l_serial_rec.receipt_source_code;
3605 l_rti_tb(l_txn_id)(l_lot_num).uom_code := l_serial_rec.uom_code;
3606
3607 --l_rti_tb(l_txn_id)(l_lot_num).secondary_quantity := l_serial_rec.secondary_quantity;
3608 --l_rti_tb(l_txn_id)(l_lot_num).secondary_uom_code := l_serial_rec.secondary_uom_code;
3609
3610 IF l_rti_tb(l_txn_id)(l_lot_num).source_document_code = 'PO' THEN
3611 l_rti_tb(l_txn_id)(l_lot_num).reference := 'PO_LINE_LOCATION_ID';
3612 l_rti_tb(l_txn_id)(l_lot_num).reference_type_code := 4;
3613 l_rti_tb(l_txn_id)(l_lot_num).reference_id := l_serial_rec.po_line_location_id;
3614 --Bug5662935:For source_document_code 'INVENTORY', reference_type_code is'6'.
3615 ELSIF l_rti_tb(l_txn_id)(l_lot_num).source_document_code = 'INVENTORY' THEN
3616 l_rti_tb(l_txn_id)(l_lot_num).reference := 'SHIPMENT_LINE_ID';
3617 l_rti_tb(l_txn_id)(l_lot_num).reference_type_code := 6;
3618 l_rti_tb(l_txn_id)(l_lot_num).reference_id := l_serial_rec.shipment_line_id;
3619 ELSIF l_rti_tb(l_txn_id)(l_lot_num).source_document_code = 'REQ' THEN
3620 l_rti_tb(l_txn_id)(l_lot_num).reference := 'SHIPMENT_LINE_ID';
3621 l_rti_tb(l_txn_id)(l_lot_num).reference_type_code := 8;
3622 l_rti_tb(l_txn_id)(l_lot_num).reference_id := l_serial_rec.shipment_line_id;
3623 ELSIF l_rti_tb(l_txn_id)(l_lot_num).source_document_code = 'RMA' THEN
3624 l_rti_tb(l_txn_id)(l_lot_num).reference := 'ORDER_LINE_ID';
3625 l_rti_tb(l_txn_id)(l_lot_num).reference_type_code := 7;
3626 l_rti_tb(l_txn_id)(l_lot_num).reference_id := l_serial_rec.oe_order_line_id;
3627 ELSE
3628 -- FAIL HERE AS THERE MAY NOT BE ANY OTHER SOURCE DOCUMENT CODE
3629 IF (l_debug = 1) THEN
3630 print_debug('REF INFO CAN NOT BE RETRIEVVED FROM RT' , 1);
3631 END IF;
3632 fnd_message.set_name('INV', 'INV_FAILED');
3633 fnd_msg_pub.ADD;
3634 RAISE fnd_api.g_exc_error;
3635 END IF;
3636
3637 IF (l_debug = 1) THEN
3638 print_debug('REFERENCE = ' ||l_rti_tb(l_txn_id)(l_lot_num).reference , 1);
3639 print_debug('REFERENCE_TYPE_CODE = '||l_rti_tb(l_txn_id)(l_lot_num).reference_type_code , 1);
3640 print_debug('REFERENCE_ID = ' ||l_rti_tb(l_txn_id)(l_lot_num).reference_id , 1);
3641 END IF;
3642 END IF;
3643
3644 -- INSERT MSNI HERE
3645 l_result := insert_msni_helper(
3646 p_txn_if_id => l_rti_tb(l_txn_id)(l_lot_num).serial_intf_id
3647 , p_serial_number => l_serial_rec.serial_num
3648 , p_org_id => p_organization_id
3649 , p_item_id => p_item_id
3650 , p_product_txn_id => l_rti_tb(l_txn_id)(l_lot_num).rti_id
3651 );
3652
3653 IF NOT l_result THEN
3654 IF (l_debug = 1) THEN
3655 print_debug('Failure while Inserting MSNI records - lot and serial controlled item',1);
3656 END IF;
3657 RAISE fnd_api.g_exc_unexpected_error;
3658 END IF; -- END IF check l_result
3659
3660 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3661 fnd_msg_pub.count_and_get(p_count => l_msg_count, p_data => l_msg_data);
3662 IF (l_debug = 1) THEN
3663 print_debug('insert_msni_helper: Error occurred while creating interface serials: ' || l_msg_data,1);
3664 END IF;
3665 RAISE fnd_api.g_exc_error;
3666 END IF;
3667 END LOOP; -- End of serial cursor Loop here
3668
3669 --
3670 -- INSERT LOT ROWS HERE IF IT IS BOTH LOT AND SERIAL CONTROLLED
3671
3672 k := l_rti_tb.first;
3673
3674 LOOP
3675 EXIT WHEN k IS NULL;
3676
3677 l := l_rti_tb(k).first;
3678
3679 LOOP
3680 EXIT WHEN l IS NULL;
3681
3682 --
3683 -- Get the seconadry qty from the Original MTLI
3684 --
3685 IF p_transfer_uom_code = l_primary_uom_code Then
3686 l_qty_to_insert := l_rti_tb(k)(l).quantity;
3687 ELSE
3688 l_qty_to_insert := inv_convert.inv_um_convert
3689 ( item_id => p_item_id,
3690 precision => 6,
3691 from_quantity => l_rti_tb(k)(l).quantity,
3692 from_unit => l_primary_uom_code,
3693 to_unit => p_transfer_uom_code,
3694 from_name => null,
3695 to_name => null );
3696 END IF;
3697
3698 l_uom_code := p_transfer_uom_code;
3699
3700 IF (l_debug = 1) THEN
3701 print_debug(' qty to insert = ' || l_qty_to_insert, 1);
3702 END IF;
3703
3704 rcv_quantities_s.get_available_quantity(
3705 'TRANSFER'
3706 ,l_rti_tb(k)(l).rcv_transaction_id
3707 ,l_rti_tb(k)(l).receipt_source_code
3708 , NULL
3709 , l_rti_tb(k)(l).rcv_transaction_id
3710 , NULL
3711 , l_avail_qty
3712 , l_tolerable_qty
3713 , l_receipt_uom);
3714 if l_rti_tb(k)(l).uom_code <> l_uom_code then
3715
3716 l_receipt_qty := inv_convert.inv_um_convert( item_id => p_item_id,
3717 precision => 6,
3718 from_quantity => l_avail_qty,
3719 from_unit => l_rti_tb(k)(l).uom_code,
3720 to_unit => l_uom_code,
3721 from_name => null,
3722 to_name => null );
3723
3724 l_avail_qty := l_receipt_qty;
3725 End if;
3726
3727 IF l_avail_qty < l_qty_to_insert THEN
3728 -- FAIL THE TXN NOT ENOUGH QTY AVAIABLE TO TRANSACT
3729 IF (l_debug = 1) THEN
3730 print_debug('Avaiable Qty is less than Txn Qty ' , 1);
3731 END IF;
3732 fnd_message.set_name('WMS', 'WMS_TASK_ERROR');
3733 fnd_msg_pub.ADD;
3734 RAISE fnd_api.g_exc_error;
3735 END IF;
3736
3737 IF (l_rti_tb(k)(l).quantity < l_remaining_prim_qty) THEN
3738
3739 IF (p_original_temp_id IS NOT NULL) THEN
3740 IF (l_debug = 1) THEN
3741 print_debug('Calling split_mmtt', 1);
3742 END IF;
3743
3744 inv_rcv_integration_apis.split_mmtt
3745 (p_orig_mmtt_id => p_original_temp_id
3746 ,p_prim_qty_to_splt => l_rti_tb(k)(l).quantity
3747 ,p_prim_uom_code => l_primary_uom_code
3748 ,x_new_mmtt_id => l_mmtt_id_to_insert
3749 ,x_return_status => l_return_status
3750 ,x_msg_count => l_msg_count
3751 ,x_msg_data => l_msg_data
3752 );
3753
3754 IF (l_debug = 1) THEN
3755 print_debug('Returned from split_mmtt',1);
3756 print_debug('x_return_status: '||l_return_status,1);
3757 END IF;
3758
3759 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
3760 IF (l_debug = 1) THEN
3761 print_debug('x_msg_data: '||l_msg_data,1);
3762 print_debug('x_msg_count: '||l_msg_count,1);
3763 print_debug('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM,1);
3764 print_debug('Raising Exception!!!',1);
3765 END IF;
3766 l_progress := '@@@';
3767 RAISE fnd_api.g_exc_unexpected_error;
3768 END IF;
3769 END IF;--END IF (p_original_temp_id IS NOT NULL) THEN
3770
3771 l_remaining_prim_qty := l_remaining_prim_qty-l_rti_tb(k)(l).quantity;
3772 ELSE
3773 l_mmtt_id_to_insert := p_original_temp_id;
3774 l_remaining_prim_qty := 0;
3775 END IF;
3776
3777 IF p_lot_number IS NOT NULL THEN
3778 -- CASE FOR BOTH LOT AND SERIAL CONTROLLED ITEM
3779 BEGIN
3780 select secondary_transaction_quantity
3781 into l_original_lot_sec_qty
3782 from mtl_transaction_lots_interface mtli
3783 where mtli.lot_number = p_lot_number
3784 and mtli.product_code = 'RCV'
3785 and mtli.product_transaction_id = p_original_rti_id ;
3786 EXCEPTION
3787 WHEN NO_DATA_FOUND THEN NULL;
3788 END;
3789
3790 IF l_original_sec_qty is not null THEN
3791 l_lot_sec_qty_to_insert := l_original_lot_sec_qty * (l_qty_to_insert / p_transfer_quantity);
3792 END IF;
3793
3794 IF (l_debug = 1) THEN
3795 print_debug('Lot Secondary qty to insert = ' || l_lot_sec_qty_to_insert, 1);
3796 END IF;
3797
3798 IF (l_debug = 1) THEN
3799 print_debug('BEFORE CALLING THE insert_mtli_helper API ', 1);
3800 print_debug(' p_txn_if_id => '|| l_lot_temp_id,1);
3801 print_debug(' p_lot_number => '|| l_rti_tb(k)(l).lot_number,1);
3802 print_debug(' p_txn_qty => '|| l_qty_to_insert,1);
3803 print_debug(' p_prm_qty => '|| l_rti_tb(k)(l).quantity,1);
3804 print_debug(' p_item_id => '|| p_item_id,1);
3805 print_debug(' p_org_id => '|| p_organization_id,1);
3806 print_debug(' p_serial_temp_id=> '|| l_rti_tb(k)(l).serial_intf_id,1);
3807 print_debug(' p_product_txn_id=> '|| l_rti_tb(k)(l).rti_id,1);
3808 print_debug(' p_secondary_quantit=> '|| l_lot_sec_qty_to_insert,1);
3809 print_debug(' p_secondary_uom =>'|| p_sec_transfer_uom_code,1);
3810 END IF;
3811
3812 l_result := insert_mtli_helper
3813 (p_txn_if_id => l_lot_temp_id
3814 , p_lot_number => l_rti_tb(k)(l).lot_number
3815 , p_txn_qty => l_qty_to_insert
3816 , p_prm_qty => l_rti_tb(k)(l).quantity
3817 , p_item_id => p_item_id
3818 , p_org_id => p_organization_id
3819 , p_serial_temp_id => l_rti_tb(k)(l).serial_intf_id
3820 , p_product_txn_id => l_rti_tb(k)(l).rti_id
3821 , p_secondary_quantity => l_lot_sec_qty_to_insert --OPM Convergence
3822 , p_secondary_uom => p_sec_transfer_uom_code); --OPM Convergence
3823
3824 IF NOT l_result THEN
3825 IF (l_debug = 1) THEN
3826 print_debug('Failure while Inserting MTLI records - lot and serial controlled item',1);
3827 END IF;
3828 RAISE fnd_api.g_exc_unexpected_error;
3829 END IF; --END IF check l_result
3830 END IF; --IF P_LOT_NUMBER IS NOT NULL THEN
3831
3832 -- CALL TO RTI HERE
3833
3834 l_reference := l_rti_tb(k)(l).reference;
3835 l_reference_id := l_rti_tb(k)(l).reference_id;
3836 l_reference_type_code := l_rti_tb(k)(l).reference_type_code;
3837 l_new_intf_id := l_rti_tb(k)(l).rti_id;
3838 l_parent_txn_id := l_rti_tb(k)(l).rcv_transaction_id;
3839
3840
3841 IF P_SEC_TRANSFER_UOM_CODE is not null THEN
3842 l_secondary_quantity := p_sec_transfer_quantity * (l_qty_to_insert/p_transfer_quantity);
3843 END IF;
3844
3845 IF (l_debug = 1) THEN
3846 print_debug('BEFORE CALLING THE TRANSFER API ', 1);
3847 END IF;
3848
3849 inv_rcv_std_transfer_apis.create_transfer_rcvtxn_rec
3850 (x_return_status => l_return_status
3851 , x_msg_count => l_msg_count
3852 , x_msg_data => l_msg_data
3853 , p_organization_id => p_organization_id
3854 , p_parent_txn_id => l_parent_txn_id
3855 , p_reference_id => l_reference_id
3856 , p_reference => l_reference
3857 , p_reference_type_code => l_reference_type_code
3858 , p_item_id => p_item_id
3859 , p_revision => p_revision
3860 , p_subinventory_code => p_subinventory_code
3861 , p_locator_id => p_locator_id
3862 , p_transfer_quantity => l_qty_to_insert
3863 , p_transfer_uom_code => p_transfer_uom_code
3864 , p_lot_control_code => p_lot_control_code
3865 , p_serial_control_code => p_serial_control_code
3866 , p_original_rti_id => l_new_intf_id
3867 , p_original_temp_id => l_mmtt_id_to_insert
3868 , p_lot_number => p_lot_number
3869 , p_lpn_id => p_lpn_id
3870 , p_transfer_lpn_id => p_transfer_lpn_id
3871 , p_sec_transfer_quantity => l_secondary_quantity
3872 , p_sec_transfer_uom_code => p_sec_transfer_uom_code
3873 );
3874
3875 IF (l_debug = 1) THEN
3876 print_debug('AFTER CALLING THE TRANSFER API: STATUS = '||l_return_status, 1);
3877 END IF;
3878
3879 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
3880 fnd_message.set_name('WMS', 'WMS_TASK_ERROR');
3881 fnd_msg_pub.ADD;
3882 RAISE fnd_api.g_exc_unexpected_error;
3883 ELSIF l_return_status = fnd_api.g_ret_sts_error THEN
3884 fnd_message.set_name('WMS', 'WMS_TASK_ERROR');
3885 fnd_msg_pub.ADD;
3886 RAISE fnd_api.g_exc_error;
3887 END IF;
3888
3889 l := l_rti_tb(k).next(l);
3890 END LOOP;
3891
3892 k := l_rti_tb.next(k);
3893 END LOOP;
3894 END IF; -- End of serial Controlled
3895
3896 IF (l_debug = 1) THEN
3897 print_debug('MATCH TRANSFER RETURNING WITH SUCCESS RETUN STATUS = ' ||x_return_status, 1);
3898 END IF;
3899
3900 EXCEPTION
3901 WHEN FND_API.G_EXC_ERROR THEN
3902 rollback to match_rti_ss;
3903 x_return_status := FND_API.G_RET_STS_ERROR;
3904 fnd_msg_pub.count_and_get (
3905 p_encoded => FND_API.G_FALSE
3906 , p_count => x_msg_count
3907 , p_data => x_msg_data );
3908 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3909 rollback to match_rti_ss;
3910 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3911 fnd_msg_pub.count_and_get (
3912 p_encoded => FND_API.G_FALSE
3913 , p_count => x_msg_count
3914 , p_data => x_msg_data );
3915 WHEN OTHERS THEN
3916 rollback to match_rti_ss;
3917 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3918 fnd_msg_pub.count_and_get (
3919 p_encoded => FND_API.G_FALSE
3920 , p_count => x_msg_count
3921 , p_data => x_msg_data );
3922 IF SQLCODE IS NOT NULL THEN
3923 inv_mobile_helper_functions.sql_error (
3924 'INV_RCV_STD_TRANSFER_APIS.MATCH_TRANSFER_RCVTXN_REC',
3925 l_progress,
3926 SQLCODE);
3927 END IF;
3928 END Match_transfer_rcvtxn_rec;
3929
3930 END INV_RCV_STD_TRANSFER_APIS;
3931