[Home] [Help]
PACKAGE BODY: APPS.GML_RCV_DIR_RCPT_APIS
Source
1 PACKAGE BODY gml_rcv_dir_rcpt_apis AS
2 /* $Header: GMLDIRDB.pls 120.0 2005/05/25 16:19:24 appldev noship $*/
3
4 g_interface_transaction_id NUMBER;
5
6 PROCEDURE populate_default_values
7 (p_rcv_transaction_rec IN OUT NOCOPY gml_rcv_std_rcpt_apis.rcv_transaction_rec_tp,
8 p_rcv_rcpt_rec IN OUT NOCOPY gml_rcv_std_rcpt_apis.rcv_enter_receipts_rec_tp,
9 p_group_id IN NUMBER,
10 p_organization_id IN NUMBER,
11 p_item_id IN NUMBER,
12 p_revision IN VARCHAR2,
13 p_source_type IN VARCHAR2,
14 p_subinventory_code IN VARCHAR2,
15 p_locator_id IN NUMBER,
16 p_transaction_temp_id IN NUMBER,
17 p_lot_control_code IN NUMBER,
18 p_serial_control_code IN NUMBER)
19 IS
20
21 l_interface_transaction_id NUMBER;
22 -- this is used to keep track of the id used to insert the row in rti
23
24 l_lot_serial_break_tbl gml_rcv_common_apis.trans_rec_tb_tp;
25 -- table that will store the record into which the lot/serial entered
26 -- have to be broken.
27
28 -- l_transaction_type VARCHAR2(20) := 'DELIVER';
29 -- I thought till 07/16/2000 that this should be deliver, but seems
30 -- that it should actually be receive.
31 l_transaction_type VARCHAR2(20) := 'RECEIVE';
32 l_valid_ship_to_location BOOLEAN;
33 l_valid_deliver_to_location BOOLEAN;
34 l_valid_deliver_to_person BOOLEAN;
35 l_valid_subinventory BOOLEAN;
36
37 CURSOR Get_Item_No IS
38 select segment1
39 from mtl_system_items
40 where inventory_item_id = p_item_id and
41 organization_id=p_organization_id;
42
43 BEGIN
44
45
46 --validate deliver to info
47 rcv_transactions_sv.val_destination_info
48 (p_organization_id,
49 p_item_id,
50 NULL,
51 p_rcv_rcpt_rec.deliver_to_location_id,
52 p_rcv_rcpt_rec.deliver_to_person_id,
53 p_rcv_rcpt_rec.destination_subinventory,
54 l_valid_ship_to_location,
55 l_valid_deliver_to_location,
56 l_valid_deliver_to_person,
57 l_valid_subinventory);
58
59
60 -- since user fill in deliver to subinventory and locator, and they are validated through LOV
61 -- we dont need to validate or default them here as receiving does.
62
63 IF l_valid_deliver_to_person THEN
64 p_rcv_transaction_rec.deliver_to_person_id := p_rcv_rcpt_rec.deliver_to_person_id;
65 END IF;
66
67 IF l_valid_deliver_to_location THEN
68 p_rcv_transaction_rec.deliver_to_location_id := p_rcv_rcpt_rec.deliver_to_location_id;
69 END IF;
70
71 p_rcv_transaction_rec.destination_subinventory := p_subinventory_code;
72 p_rcv_transaction_rec.locator_id := p_locator_id;
73
74 -- revision should be passed into matching logic
75
76 p_rcv_transaction_rec.item_revision := p_revision;
77 p_rcv_rcpt_rec.item_revision := p_revision;
78
79 l_interface_transaction_id := gml_rcv_std_rcpt_apis.insert_txn_interface
80 (p_rcv_transaction_rec,
81 p_rcv_rcpt_rec,
82 p_group_id,
83 l_transaction_type,
84 p_organization_id,
85 p_rcv_transaction_rec.deliver_to_location_id,
86 p_source_type);
87
88 --Store the interface_transaction_id in a global variable
89 g_interface_transaction_id := l_interface_transaction_id;
90
91 /*
92
93 l_lot_serial_break_tbl(1).transaction_id := l_interface_transaction_id;
94 l_lot_serial_break_tbl(1).primary_quantity := p_rcv_transaction_rec.primary_quantity;
95 l_lot_serial_break_tbl(1).unit_of_measure := p_rcv_transaction_rec.transaction_uom;
96
97 OPEN Get_Item_No;
98 FETCH Get_Item_No INTO l_lot_serial_break_tbl(1).item_no;
99 CLOSE Get_Item_No;
100 */
101
102 END populate_default_values;
103
104
105 PROCEDURE create_osp_drct_dlvr_rti_rec (p_move_order_header_id IN OUT NOCOPY NUMBER,
106 p_organization_id IN NUMBER,
107 p_po_header_id IN NUMBER,
108 p_po_release_id IN NUMBER,
109 p_po_line_id IN NUMBER,
110 p_po_line_location_id IN NUMBER,
111 p_po_distribution_id IN NUMBER,
112 p_item_id IN NUMBER,
113 p_rcv_qty IN NUMBER,
114 p_rcv_uom IN VARCHAR2,
115 p_rcv_uom_code IN VARCHAR2,
116 p_source_type IN VARCHAR2,
117 p_transaction_temp_id IN NUMBER,
118 p_revision IN VARCHAR2,
119 x_status OUT NOCOPY VARCHAR2,
120 x_message OUT NOCOPY VARCHAR2)
121 IS
122 l_rcpt_match_table_detail GML_RCV_TXN_INTERFACE.cascaded_trans_tab_type; -- output for matching algorithm
123 l_rcv_transaction_rec gml_rcv_std_rcpt_apis.rcv_transaction_rec_tp; -- rcv_transaction block
124
125 l_transaction_type VARCHAR2(20) := 'DELIVER';
126 l_total_primary_qty NUMBER := 0;
127
128 l_msg_count NUMBER;
129 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
130
131 l_group_id NUMBER;
132 l_rcv_rcpt_rec gml_rcv_std_rcpt_apis.rcv_enter_receipts_rec_tp;
133
134 l_err_message VARCHAR2(100);
135 l_temp_message VARCHAR2(100);
136 l_msg_prod VARCHAR2(5);
137
138 l_progress VARCHAR2(10);
139
140
141 CURSOR l_curs_rcpt_detail
142 (v_po_distribution_id NUMBER)
143 IS
144 SELECT
145 'N' LINE_CHKBOX,
146 'VENDOR' SOURCE_TYPE_CODE,
147 'VENDOR' RECEIPT_SOURCE_CODE,
148 'PO' ORDER_TYPE_CODE,
149 '' ORDER_TYPE,
150 POLL.PO_HEADER_ID PO_HEADER_ID,
151 POH.SEGMENT1 PO_NUMBER,
152 POLL.PO_LINE_ID PO_LINE_ID,
153 POL.LINE_NUM PO_LINE_NUMBER,
154 POLL.LINE_LOCATION_ID PO_LINE_LOCATION_ID,
155 POLL.SHIPMENT_NUM PO_SHIPMENT_NUMBER,
156 POLL.PO_RELEASE_ID PO_RELEASE_ID,
157 POR.RELEASE_NUM PO_RELEASE_NUMBER,
158 TO_NUMBER(NULL) REQ_HEADER_ID,
159 NULL REQ_NUMBER,
160 TO_NUMBER(NULL) REQ_LINE_ID,
161 TO_NUMBER(NULL) REQ_LINE,
162 TO_NUMBER(NULL) REQ_DISTRIBUTION_ID,
163 POH.PO_HEADER_ID RCV_SHIPMENT_HEADER_ID,
164 POH.SEGMENT1 RCV_SHIPMENT_NUMBER,
165 POL.PO_LINE_ID RCV_SHIPMENT_LINE_ID,
166 POL.LINE_NUM RCV_LINE_NUMBER,
167 POH.PO_HEADER_ID FROM_ORGANIZATION_ID,
168 POLL.SHIP_TO_ORGANIZATION_ID TO_ORGANIZATION_ID,
169 POH.VENDOR_ID VENDOR_ID,
170 '' SOURCE,
171 POH.VENDOR_SITE_ID VENDOR_SITE_ID,
172 '' OUTSIDE_OPERATION_FLAG,
173 POL.ITEM_ID ITEM_ID,
174 NULL uom_code,
175 POL.UNIT_MEAS_LOOKUP_CODE PRIMARY_UOM,
176 MUM.UOM_CLASS PRIMARY_UOM_CLASS,
177 NULL ITEM_ALLOWED_UNITS_LOOKUP_CODE,
178 NULL ITEM_LOCATOR_CONTROL,
179 '' RESTRICT_LOCATORS_CODE,
180 '' RESTRICT_SUBINVENTORIES_CODE,
181 NULL SHELF_LIFE_CODE,
182 NULL SHELF_LIFE_DAYS,
183 MSI.SERIAL_NUMBER_CONTROL_CODE SERIAL_NUMBER_CONTROL_CODE,
184 MSI.LOT_CONTROL_CODE LOT_CONTROL_CODE,
185 DECODE(MSI.REVISION_QTY_CONTROL_CODE,1,'N',2,'Y','N') ITEM_REV_CONTROL_FLAG_TO,
186 NULL ITEM_REV_CONTROL_FLAG_FROM,
187 NULL ITEM_NUMBER,
188 POL.ITEM_REVISION ITEM_REVISION,
189 POL.ITEM_DESCRIPTION ITEM_DESCRIPTION,
190 POL.CATEGORY_ID ITEM_CATEGORY_ID,
191 '' HAZARD_CLASS,
192 '' UN_NUMBER,
193 POL.VENDOR_PRODUCT_NUM VENDOR_ITEM_NUMBER,
194 POLL.SHIP_TO_LOCATION_ID SHIP_TO_LOCATION_ID,
195 '' SHIP_TO_LOCATION,
196 NULL PACKING_SLIP,
197 POLL.RECEIVING_ROUTING_ID ROUTING_ID,
198 '' ROUTING_NAME,
199 POLL.NEED_BY_DATE NEED_BY_DATE,
200 NVL(POLL.PROMISED_DATE,POLL.NEED_BY_DATE) EXPECTED_RECEIPT_DATE,
201 POLL.QUANTITY ORDERED_QTY,
202 POL.UNIT_MEAS_LOOKUP_CODE ORDERED_UOM,
203 NULL USSGL_TRANSACTION_CODE,
204 POLL.GOVERNMENT_CONTEXT GOVERNMENT_CONTEXT,
205 POLL.INSPECTION_REQUIRED_FLAG INSPECTION_REQUIRED_FLAG,
206 POLL.RECEIPT_REQUIRED_FLAG RECEIPT_REQUIRED_FLAG,
207 POLL.ENFORCE_SHIP_TO_LOCATION_CODE ENFORCE_SHIP_TO_LOCATION_CODE,
208 NVL(POLL.PRICE_OVERRIDE,POL.UNIT_PRICE) UNIT_PRICE,
209 POH.CURRENCY_CODE CURRENCY_CODE,
210 POH.RATE_TYPE CURRENCY_CONVERSION_TYPE,
211 POH.RATE_DATE CURRENCY_CONVERSION_DATE,
212 POH.RATE CURRENCY_CONVERSION_RATE,
213 POH.NOTE_TO_RECEIVER NOTE_TO_RECEIVER,
214 pod.destination_type_code DESTINATION_TYPE_CODE,
215 pod.deliver_to_person_id DELIVER_TO_PERSON_ID,
216 pod.deliver_to_location_id DELIVER_TO_LOCATION_ID,
217 pod.destination_subinventory DESTINATION_SUBINVENTORY,
218 POLL.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY,
219 POLL.ATTRIBUTE1 ATTRIBUTE1,
220 POLL.ATTRIBUTE2 ATTRIBUTE2,
221 POLL.ATTRIBUTE3 ATTRIBUTE3,
222 POLL.ATTRIBUTE4 ATTRIBUTE4,
223 POLL.ATTRIBUTE5 ATTRIBUTE5,
224 POLL.ATTRIBUTE6 ATTRIBUTE6,
225 POLL.ATTRIBUTE7 ATTRIBUTE7,
226 POLL.ATTRIBUTE8 ATTRIBUTE8,
227 POLL.ATTRIBUTE9 ATTRIBUTE9,
228 POLL.ATTRIBUTE10 ATTRIBUTE10,
229 POLL.ATTRIBUTE11 ATTRIBUTE11,
230 POLL.ATTRIBUTE12 ATTRIBUTE12,
231 POLL.ATTRIBUTE13 ATTRIBUTE13,
232 POLL.ATTRIBUTE14 ATTRIBUTE14,
233 POLL.ATTRIBUTE15 ATTRIBUTE15,
234 POLL.CLOSED_CODE CLOSED_CODE,
235 NULL ASN_TYPE,
236 NULL BILL_OF_LADING,
237 TO_DATE(NULL) SHIPPED_DATE,
238 NULL FREIGHT_CARRIER_CODE,
239 NULL WAYBILL_AIRBILL_NUM,
240 NULL FREIGHT_BILL_NUM,
241 NULL VENDOR_LOT_NUM,
242 NULL CONTAINER_NUM,
243 NULL TRUCK_NUM,
244 NULL BAR_CODE_LABEL,
245 '' RATE_TYPE_DISPLAY,
246 POLL.MATCH_OPTION MATCH_OPTION,
247 POLL.COUNTRY_OF_ORIGIN_CODE COUNTRY_OF_ORIGIN_CODE,
248 TO_NUMBER(NULL) OE_ORDER_HEADER_ID,
249 TO_NUMBER(NULL) OE_ORDER_NUM,
250 TO_NUMBER(NULL) OE_ORDER_LINE_ID,
251 TO_NUMBER(NULL) OE_ORDER_LINE_NUM,
252 TO_NUMBER(NULL) CUSTOMER_ID,
253 TO_NUMBER(NULL) CUSTOMER_SITE_ID,
254 NULL CUSTOMER_ITEM_NUM,
255 NULL pll_note_to_receiver,
256 pod.po_distribution_id,
257 pod.quantity_ordered - pod.quantity_delivered qty_ordered,
258 pod.wip_entity_id,
259 pod.wip_operation_seq_num,
260 pod.wip_resource_seq_num,
261 pod.wip_repetitive_schedule_id,
262 pod.wip_line_id,
263 pod.bom_resource_id,
264 '' DESTINATION_TYPE,
265 '' LOCATION,
266 pod.rate currency_conversion_rate_pod,
267 pod.rate_date currency_conversion_date_pod,
268 pod.project_id project_id,
269 pod.task_id task_id
270 FROM
271 PO_HEADERS POH,
272 PO_LINE_LOCATIONS POLL,
273 PO_LINES POL,
274 PO_RELEASES POR,
275 MTL_SYSTEM_ITEMS MSI,
276 MTL_UNITS_OF_MEASURE mum,
277 PO_DISTRIBUTIONS POD
278 WHERE
279 POD.PO_DISTRIBUTION_ID = v_po_distribution_id
280 AND POH.PO_HEADER_ID = POLL.PO_HEADER_ID
281 AND POL.PO_LINE_ID = POLL.PO_LINE_ID
282 AND POLL.PO_RELEASE_ID = POR.PO_RELEASE_ID(+)
283 AND POD.LINE_LOCATION_ID = POLL.LINE_LOCATION_ID
284 AND MUM.UNIT_OF_MEASURE (+) = POL.UNIT_MEAS_LOOKUP_CODE
285 AND NVL(MSI.ORGANIZATION_ID,POLL.SHIP_TO_ORGANIZATION_ID) = POLL.SHIP_TO_ORGANIZATION_ID
286 AND MSI.INVENTORY_ITEM_ID (+) = POL.ITEM_ID;
287 BEGIN
288
289 x_status := fnd_api.g_ret_sts_success;
290 SAVEPOINT crt_po_rti_sp;
291 l_progress := '10';
292
293 -- query po_startup_value
294 Begin
295 inv_rcv_common_apis.init_startup_values(p_organization_id);
296 Exception
297 when NO_DATA_FOUND then
298 FND_MESSAGE.SET_NAME('INV', 'INV_RCV_PARAM');
299 FND_MSG_PUB.ADD;
300 RAISE ;
301 End;
302
303 l_progress := '20';
304 -- default l_group_id ? clear group id after done
305 IF inv_rcv_common_apis.g_rcv_global_var.interface_group_id is NULL THEN
306 SELECT rcv_interface_groups_s.nextval
307 INTO l_group_id
308 FROM dual;
309 inv_rcv_common_apis.g_rcv_global_var.interface_group_id := l_group_id;
310 ELSE
311 l_group_id := inv_rcv_common_apis.g_rcv_global_var.interface_group_id;
312 END IF;
313
314 l_progress := '30';
315 OPEN l_curs_rcpt_detail(p_po_distribution_id);
316 l_progress := '31';
317 FETCH l_curs_rcpt_detail INTO l_rcv_rcpt_rec;
318
319 l_rcv_rcpt_rec.item_id := p_item_id;
320
321 l_progress := '32';
322 CLOSE l_curs_rcpt_detail;
323 l_progress := '33';
324
325 -- bug 2743146
326 -- Make sure that the po_distribution passed does satisfy the tolerance
327 -- limits by calling the matching algorithm for that.
328 -- initialize input record for matching algorithm
329 inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross(inv_rcv_std_rcpt_apis.g_receipt_detail_index).transaction_type := 'DELIVER';
330 inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross(inv_rcv_std_rcpt_apis.g_receipt_detail_index).quantity := p_rcv_qty;
331 inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross(inv_rcv_std_rcpt_apis.g_receipt_detail_index).unit_of_measure := p_rcv_uom;
332 inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross(inv_rcv_std_rcpt_apis.g_receipt_detail_index).item_id := p_item_id;
333
334
335 inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross(inv_rcv_std_rcpt_apis.g_receipt_detail_index).to_organization_id := p_organization_id;
336 inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross(inv_rcv_std_rcpt_apis.g_receipt_detail_index).group_id := l_group_id;
337 inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross(inv_rcv_std_rcpt_apis.g_receipt_detail_index).po_header_id := p_po_header_id;
338 inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross(inv_rcv_std_rcpt_apis.g_receipt_detail_index).po_release_id := p_po_release_id;
339
340 -- line id, line location id and distribution id will be passed only from the putaway api.
341 -- line id however, can also be passed through the UI if the line number
342 -- field is enabled on the UI.
343 inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross(inv_rcv_std_rcpt_apis.g_receipt_detail_index).po_line_id := l_rcv_rcpt_rec.po_line_id;
344 inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross(inv_rcv_std_rcpt_apis.g_receipt_detail_index).po_line_location_id := l_rcv_rcpt_rec.po_line_location_id;
345 inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross(inv_rcv_std_rcpt_apis.g_receipt_detail_index).po_distribution_id := p_po_distribution_id;
346
347 IF p_item_id IS NOT NULL THEN
348 BEGIN
349 select primary_unit_of_measure
350 into inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross(inv_rcv_std_rcpt_apis.g_receipt_detail_index).primary_unit_of_measure
351 from mtl_system_items
352 where mtl_system_items.inventory_item_id = p_item_id
353 and mtl_system_items.organization_id = p_organization_id;
354 EXCEPTION
355 when no_data_found then
356 NULL;
357
358 END;
359 ELSE
360 inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross(inv_rcv_std_rcpt_apis.g_receipt_detail_index).primary_unit_of_measure := NULL;
361 END IF;
362
363 inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross(inv_rcv_std_rcpt_apis.g_receipt_detail_index).revision := p_revision;
364 --inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross(inv_rcv_std_rcpt_apis.g_receipt_detail_index).project_id := p_project_id;
365 --inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross(inv_rcv_std_rcpt_apis.g_receipt_detail_index).task_id := p_task_id;
366 inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross(inv_rcv_std_rcpt_apis.g_receipt_detail_index).expected_receipt_date := Sysdate; --?
367 inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross(inv_rcv_std_rcpt_apis.g_receipt_detail_index).tax_amount := 0; -- ?
368 inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross(inv_rcv_std_rcpt_apis.g_receipt_detail_index).error_status := 'S'; -- ?
369
370
371 l_progress := '40';
372 gml_rcv_txn_interface.matching_logic
373 (x_return_status => x_status,
374 x_msg_count => l_msg_count,
375 x_msg_data => x_message,
376 x_cascaded_table => gml_rcv_std_rcpt_apis.g_rcpt_match_table_gross,
377 n => gml_rcv_std_rcpt_apis.g_receipt_detail_index,
378 temp_cascaded_table => l_rcpt_match_table_detail,
379 p_receipt_num => NULL,
380 p_shipment_header_id => NULL,
381 p_lpn_id => NULL
382 );
383 -- x_status is not successful if there is any execution error in matching.
384 IF x_status = fnd_api.g_ret_sts_error THEN
385 FND_MESSAGE.SET_NAME('INV', 'INV_RCV_MATCH_ERROR');
386 FND_MSG_PUB.ADD;
387 RAISE fnd_api.g_exc_error;
388 END IF;
389
390 IF x_status = fnd_api.g_ret_sts_unexp_error THEN
391 FND_MESSAGE.SET_NAME('INV', 'INV_RCV_MATCH_ERROR');
392 FND_MSG_PUB.ADD;
393 RAISE fnd_api.g_exc_unexpected_error;
394 END IF;
395
396 IF inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross(inv_rcv_std_rcpt_apis.g_receipt_detail_index).error_status = 'E' THEN
397 l_err_message := inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross(inv_rcv_std_rcpt_apis.g_receipt_detail_index).error_message;
398 FND_MESSAGE.SET_NAME('INV', l_err_message);
399 FND_MSG_PUB.ADD;
400 RAISE fnd_api.g_exc_error;
401 END IF;
402
403 l_err_message := '@@@';
404 FOR i IN inv_rcv_std_rcpt_apis.g_receipt_detail_index..(inv_rcv_std_rcpt_apis.g_receipt_detail_index + l_rcpt_match_table_detail.COUNT - 1) LOOP
405 IF l_rcpt_match_table_detail(i-inv_rcv_std_rcpt_apis.g_receipt_detail_index+1).error_status = 'W' THEN
406 x_status := 'W';
407
408 l_temp_message := l_rcpt_match_table_detail(i-inv_rcv_std_rcpt_apis.g_receipt_detail_index+1).error_message;
409 IF l_temp_message IS NULL THEN
410 l_err_message := 'INV_RCV_GEN_TOLERANCE_EXCEED';
411 l_msg_prod := 'INV';
412 EXIT;
413 END IF;
414 IF l_err_message = '@@@' THEN
415 l_err_message := l_temp_message;
416 l_msg_prod := 'INV';
417 ELSIF l_temp_message <> l_err_message THEN
418 l_msg_prod := 'INV';
419 l_err_message := 'INV_RCV_GEN_TOLERANCE_EXCEED';
420 EXIT;
421 END IF;
422 END IF;
423 END LOOP;
424
425 IF l_err_message <> '@@@' THEN
426 FND_MESSAGE.SET_NAME(l_msg_prod, l_err_message);
427 FND_MSG_PUB.ADD;
428 END IF;
429 -- End bug fix 2743146
430
431
432 l_progress := '60';
433
434
435 l_rcv_transaction_rec.po_distribution_id := p_po_distribution_id;
436
437 l_rcv_transaction_rec.transaction_qty := p_rcv_qty;
438 l_rcv_transaction_rec.transaction_uom := p_rcv_uom;
439 l_rcv_transaction_rec.primary_quantity := rcv_transactions_interface_sv.convert_into_correct_qty(p_rcv_qty,
440 p_rcv_uom,
441 p_item_id,
442 l_rcv_rcpt_rec.primary_uom);
443 l_rcv_transaction_rec.primary_uom := l_rcv_rcpt_rec.primary_uom;
444 l_total_primary_qty := l_total_primary_qty + l_rcv_transaction_rec.primary_quantity;
445
446 l_progress := '64';
447
448 -- update following fields for po_distribution related values
449 l_rcv_transaction_rec.currency_conversion_date := l_rcv_rcpt_rec.currency_conversion_date_pod;
450 l_rcv_transaction_rec.currency_conversion_rate := l_rcv_rcpt_rec.currency_conversion_rate_pod;
451 l_rcv_transaction_rec.ordered_qty := l_rcv_rcpt_rec.qty_ordered;
452 l_rcv_rcpt_rec.uom_code := p_rcv_uom_code;
453
454 -- wip related fields
455 l_rcv_transaction_rec.wip_entity_id := l_rcv_rcpt_rec.wip_entity_id;
456 l_rcv_transaction_rec.wip_operation_seq_num := l_rcv_rcpt_rec.wip_operation_seq_num;
457 l_rcv_transaction_rec.wip_resource_seq_num := l_rcv_rcpt_rec.wip_resource_seq_num;
458 l_rcv_transaction_rec.wip_repetitive_schedule_id := l_rcv_rcpt_rec.wip_repetitive_schedule_id;
459 l_rcv_transaction_rec.wip_line_id := l_rcv_rcpt_rec.wip_line_id;
460 l_rcv_transaction_rec.bom_resource_id := l_rcv_rcpt_rec.bom_resource_id;
461
462 populate_default_values(p_rcv_transaction_rec => l_rcv_transaction_rec,
463 p_rcv_rcpt_rec => l_rcv_rcpt_rec,
464 p_group_id => l_group_id,
465 p_organization_id => p_organization_id,
466 p_item_id => p_item_id,
467 p_revision => p_revision,
468 p_source_type => p_source_type,
469 p_subinventory_code => NULL,
470 p_locator_id => NULL,
471 p_transaction_temp_id => p_transaction_temp_id,
472 p_lot_control_code => NULL,
473 p_serial_control_code => NULL);
474
475 l_progress := '65';
476 inv_rcv_common_apis.do_check
477 (p_organization_id => p_organization_id,
478 p_inventory_item_id => p_item_id,
479 p_transaction_type_id => 18,
480 p_primary_quantity => l_total_primary_qty,
481 x_return_status => l_return_status,
482 x_msg_count => l_msg_count,
483 x_msg_data => x_message);
484
485 IF l_return_status <> fnd_api.g_ret_sts_success THEN
486 x_status := l_return_status;
487 END IF;
488
489 l_progress := '70';
490
491 -- Clear the Lot Rec
492 inv_rcv_std_rcpt_apis.g_rcpt_lot_qty_rec_tb.DELETE;
493
494
495 EXCEPTION
496 WHEN fnd_api.g_exc_error THEN
497 ROLLBACK TO crt_po_rti_sp;
498 x_status := fnd_api.g_ret_sts_error;
499
500 IF l_curs_rcpt_detail%isopen THEN
501 CLOSE l_curs_rcpt_detail;
502 END IF;
503
504 fnd_msg_pub.count_and_get
505 (p_encoded => FND_API.g_false,
506 p_count => l_msg_count,
507 p_data => x_message
508 );
509
510 WHEN fnd_api.g_exc_unexpected_error THEN
511 ROLLBACK TO crt_po_rti_sp;
512 x_status := fnd_api.g_ret_sts_unexp_error ;
513 IF l_curs_rcpt_detail%isopen THEN
514 CLOSE l_curs_rcpt_detail;
515 END IF;
516
517 fnd_msg_pub.count_and_get
518 (p_encoded => FND_API.g_false,
519 p_count => l_msg_count,
520 p_data => x_message
521 );
522
523
524 WHEN OTHERS THEN
525 ROLLBACK TO crt_po_rti_sp;
526 x_status := fnd_api.g_ret_sts_unexp_error ;
527 IF l_curs_rcpt_detail%isopen THEN
528 CLOSE l_curs_rcpt_detail;
529 END IF;
530 fnd_msg_pub.count_and_get
531 (p_encoded => FND_API.g_false,
532 p_count => l_msg_count,
533 p_data => x_message
534 );
535
536
537 END create_osp_drct_dlvr_rti_rec;
538
539
540 PROCEDURE create_po_drct_dlvr_rti_rec(p_move_order_header_id IN OUT NOCOPY NUMBER,
541 p_organization_id IN NUMBER,
542 p_po_header_id IN NUMBER,
543 p_po_release_id IN NUMBER,
544 p_po_line_id IN NUMBER,
545 p_po_line_location_id IN NUMBER,
546 p_po_distribution_id IN NUMBER,
547 p_item_id IN NUMBER,
548 p_rcv_qty IN NUMBER,
549 p_rcv_sec_qty IN NUMBER,
550 p_rcv_uom IN VARCHAR2,
551 p_rcv_uom_code IN VARCHAR2,
552 p_rcv_sec_uom IN VARCHAR2,
553 p_rcv_sec_uom_code IN VARCHAR2,
554 p_source_type IN VARCHAR2,
555 p_subinventory VARCHAR2,
556 p_locator_id NUMBER,
557 p_transaction_temp_id IN NUMBER,
558 p_lot_control_code IN NUMBER,
559 p_serial_control_code IN NUMBER,
560 p_lpn_id IN NUMBER,
561 p_revision IN VARCHAR2,
562 x_status OUT NOCOPY VARCHAR2,
563 x_message OUT NOCOPY VARCHAR2,
564 p_inv_item_id IN NUMBER,
565 p_item_desc IN VARCHAR2,
566 p_location_id IN NUMBER,
567 p_is_expense IN VARCHAR2,
568 p_project_id IN NUMBER,
569 p_task_id IN NUMBER,
570 p_country_code IN VARCHAR2 DEFAULT NULL)
571 IS
572
573 l_rcpt_match_table_detail GML_RCV_TXN_INTERFACE.cascaded_trans_tab_type; -- output for matching algorithm
574
575 l_rcv_transaction_rec gml_rcv_std_rcpt_apis.rcv_transaction_rec_tp; -- rcv_transaction block
576
577 l_transaction_type VARCHAR2(20) := 'DELIVER';
578 l_total_primary_qty NUMBER := 0;
579
580 l_msg_count NUMBER;
581 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
582
583 l_group_id NUMBER;
584 l_rcv_rcpt_rec gml_rcv_std_rcpt_apis.rcv_enter_receipts_rec_tp;
585 --l_mmtt_rec mtl_material_transactions_temp%ROWTYPE;
586
587 l_err_message VARCHAR2(100);
588 l_temp_message VARCHAR2(100);
589 l_msg_prod VARCHAR2(5);
590
591 l_progress VARCHAR2(10);
592
593 l_new_rti_info inv_rcv_integration_apis.child_rec_tb_tp;
594 l_split_lot_serial_ok BOOLEAN; --Return status of lot_serial_split API
595
596
597 CURSOR l_curs_rcpt_detail
598 (v_po_distribution_id NUMBER)
599 IS
600 SELECT
601 'N' LINE_CHKBOX,
602 'VENDOR' SOURCE_TYPE_CODE,
603 'VENDOR' RECEIPT_SOURCE_CODE,
604 'PO' ORDER_TYPE_CODE,
605 '' ORDER_TYPE,
606 POLL.PO_HEADER_ID PO_HEADER_ID,
607 POH.SEGMENT1 PO_NUMBER,
608 POLL.PO_LINE_ID PO_LINE_ID,
609 POL.LINE_NUM PO_LINE_NUMBER,
610 POLL.LINE_LOCATION_ID PO_LINE_LOCATION_ID,
611 POLL.SHIPMENT_NUM PO_SHIPMENT_NUMBER,
612 POLL.PO_RELEASE_ID PO_RELEASE_ID,
613 POR.RELEASE_NUM PO_RELEASE_NUMBER,
614 TO_NUMBER(NULL) REQ_HEADER_ID,
615 NULL REQ_NUMBER,
616 TO_NUMBER(NULL) REQ_LINE_ID,
617 TO_NUMBER(NULL) REQ_LINE,
618 TO_NUMBER(NULL) REQ_DISTRIBUTION_ID,
619 POH.PO_HEADER_ID RCV_SHIPMENT_HEADER_ID,
620 POH.SEGMENT1 RCV_SHIPMENT_NUMBER,
621 POL.PO_LINE_ID RCV_SHIPMENT_LINE_ID,
622 POL.LINE_NUM RCV_LINE_NUMBER,
623 POH.PO_HEADER_ID FROM_ORGANIZATION_ID,
624 POLL.SHIP_TO_ORGANIZATION_ID TO_ORGANIZATION_ID,
625 POH.VENDOR_ID VENDOR_ID,
626 '' SOURCE,
627 POH.VENDOR_SITE_ID VENDOR_SITE_ID,
628 '' OUTSIDE_OPERATION_FLAG,
629 POL.ITEM_ID ITEM_ID,
630 -- Bug 2073164
631 NULL uom_code,
632 POL.UNIT_MEAS_LOOKUP_CODE PRIMARY_UOM,
633 MUM.UOM_CLASS PRIMARY_UOM_CLASS,
634 NULL ITEM_ALLOWED_UNITS_LOOKUP_CODE,
635 NULL ITEM_LOCATOR_CONTROL,
636 '' RESTRICT_LOCATORS_CODE,
637 '' RESTRICT_SUBINVENTORIES_CODE,
638 NULL SHELF_LIFE_CODE,
639 NULL SHELF_LIFE_DAYS,
640 MSI.SERIAL_NUMBER_CONTROL_CODE SERIAL_NUMBER_CONTROL_CODE,
641 MSI.LOT_CONTROL_CODE LOT_CONTROL_CODE,
642 DECODE(MSI.REVISION_QTY_CONTROL_CODE,1,'N',2,'Y','N') ITEM_REV_CONTROL_FLAG_TO,
643 NULL ITEM_REV_CONTROL_FLAG_FROM,
644 NULL ITEM_NUMBER,
645 POL.ITEM_REVISION ITEM_REVISION,
646 POL.ITEM_DESCRIPTION ITEM_DESCRIPTION,
647 POL.CATEGORY_ID ITEM_CATEGORY_ID,
648 '' HAZARD_CLASS,
649 '' UN_NUMBER,
650 POL.VENDOR_PRODUCT_NUM VENDOR_ITEM_NUMBER,
651 POLL.SHIP_TO_LOCATION_ID SHIP_TO_LOCATION_ID,
652 '' SHIP_TO_LOCATION,
653 NULL PACKING_SLIP,
654 POLL.RECEIVING_ROUTING_ID ROUTING_ID,
655 '' ROUTING_NAME,
656 POLL.NEED_BY_DATE NEED_BY_DATE,
657 NVL(POLL.PROMISED_DATE,POLL.NEED_BY_DATE) EXPECTED_RECEIPT_DATE,
658 POLL.QUANTITY ORDERED_QTY,
659 POL.UNIT_MEAS_LOOKUP_CODE ORDERED_UOM,
660 NULL USSGL_TRANSACTION_CODE,
661 POLL.GOVERNMENT_CONTEXT GOVERNMENT_CONTEXT,
662 POLL.INSPECTION_REQUIRED_FLAG INSPECTION_REQUIRED_FLAG,
663 POLL.RECEIPT_REQUIRED_FLAG RECEIPT_REQUIRED_FLAG,
664 POLL.ENFORCE_SHIP_TO_LOCATION_CODE ENFORCE_SHIP_TO_LOCATION_CODE,
665 NVL(POLL.PRICE_OVERRIDE,POL.UNIT_PRICE) UNIT_PRICE,
666 POH.CURRENCY_CODE CURRENCY_CODE,
667 POH.RATE_TYPE CURRENCY_CONVERSION_TYPE,
668 POH.RATE_DATE CURRENCY_CONVERSION_DATE,
669 POH.RATE CURRENCY_CONVERSION_RATE,
670 POH.NOTE_TO_RECEIVER NOTE_TO_RECEIVER,
671 pod.destination_type_code DESTINATION_TYPE_CODE,
672 pod.deliver_to_person_id DELIVER_TO_PERSON_ID,
673 pod.deliver_to_location_id DELIVER_TO_LOCATION_ID,
674 pod.destination_subinventory DESTINATION_SUBINVENTORY,
675 POLL.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY,
676 POLL.ATTRIBUTE1 ATTRIBUTE1,
677 POLL.ATTRIBUTE2 ATTRIBUTE2,
678 POLL.ATTRIBUTE3 ATTRIBUTE3,
679 POLL.ATTRIBUTE4 ATTRIBUTE4,
680 POLL.ATTRIBUTE5 ATTRIBUTE5,
681 POLL.ATTRIBUTE6 ATTRIBUTE6,
682 POLL.ATTRIBUTE7 ATTRIBUTE7,
683 POLL.ATTRIBUTE8 ATTRIBUTE8,
684 POLL.ATTRIBUTE9 ATTRIBUTE9,
685 POLL.ATTRIBUTE10 ATTRIBUTE10,
686 POLL.ATTRIBUTE11 ATTRIBUTE11,
687 POLL.ATTRIBUTE12 ATTRIBUTE12,
688 POLL.ATTRIBUTE13 ATTRIBUTE13,
689 POLL.ATTRIBUTE14 ATTRIBUTE14,
690 POLL.ATTRIBUTE15 ATTRIBUTE15,
691 POLL.CLOSED_CODE CLOSED_CODE,
692 NULL ASN_TYPE,
693 NULL BILL_OF_LADING,
694 TO_DATE(NULL) SHIPPED_DATE,
695 NULL FREIGHT_CARRIER_CODE,
696 NULL WAYBILL_AIRBILL_NUM,
697 NULL FREIGHT_BILL_NUM,
698 NULL VENDOR_LOT_NUM,
699 NULL CONTAINER_NUM,
700 NULL TRUCK_NUM,
701 NULL BAR_CODE_LABEL,
702 '' RATE_TYPE_DISPLAY,
703 POLL.MATCH_OPTION MATCH_OPTION,
704 POLL.COUNTRY_OF_ORIGIN_CODE COUNTRY_OF_ORIGIN_CODE,
705 TO_NUMBER(NULL) OE_ORDER_HEADER_ID,
706 TO_NUMBER(NULL) OE_ORDER_NUM,
707 TO_NUMBER(NULL) OE_ORDER_LINE_ID,
708 TO_NUMBER(NULL) OE_ORDER_LINE_NUM,
709 TO_NUMBER(NULL) CUSTOMER_ID,
710 TO_NUMBER(NULL) CUSTOMER_SITE_ID,
711 NULL CUSTOMER_ITEM_NUM,
712 NULL pll_note_to_receiver,
713 --POLL.NOTE_TO_RECEIVER PLL_NOTE_TO_RECEIVER,
714 pod.po_distribution_id,
715 pod.quantity_ordered - pod.quantity_delivered qty_ordered,
716 pod.wip_entity_id,
717 pod.wip_operation_seq_num,
718 pod.wip_resource_seq_num,
719 pod.wip_repetitive_schedule_id,
720 pod.wip_line_id,
721 pod.bom_resource_id,
722 '' DESTINATION_TYPE,
723 '' LOCATION,
724 pod.rate currency_conversion_rate_pod,
725 pod.rate_date currency_conversion_date_pod,
726 pod.project_id project_id,
727 pod.task_id task_id
728 FROM
729 PO_HEADERS POH,
730 PO_LINE_LOCATIONS POLL,
731 PO_LINES POL,
732 PO_RELEASES POR,
733 MTL_SYSTEM_ITEMS MSI,
734 MTL_UNITS_OF_MEASURE mum,
735 PO_DISTRIBUTIONS POD
736 WHERE
737 POD.PO_DISTRIBUTION_ID = v_po_distribution_id
738 AND POH.PO_HEADER_ID = POLL.PO_HEADER_ID
739 AND POL.PO_LINE_ID = POLL.PO_LINE_ID
740 AND POLL.PO_RELEASE_ID = POR.PO_RELEASE_ID(+)
741 AND POD.LINE_LOCATION_ID = POLL.LINE_LOCATION_ID
742 AND MUM.UNIT_OF_MEASURE (+) = POL.UNIT_MEAS_LOOKUP_CODE
743 AND NVL(MSI.ORGANIZATION_ID,POLL.SHIP_TO_ORGANIZATION_ID) = POLL.SHIP_TO_ORGANIZATION_ID
744 AND MSI.INVENTORY_ITEM_ID (+) = POL.ITEM_ID
745 AND (p_project_id is null or
746 (p_project_id = -9999 and pod.project_id is null) -- bug 2669021
747 or POD.project_id = p_project_id
748 )
749 and ( p_task_id is null or pod.task_id = p_task_id );
750 BEGIN
751
752 x_status := fnd_api.g_ret_sts_success;
753 SAVEPOINT crt_po_rti_sp;
754 l_progress := '10';
755
756 -- query po_startup_value
757 Begin
758 inv_rcv_common_apis.init_startup_values(p_organization_id);
759 Exception
760 when NO_DATA_FOUND then
761 FND_MESSAGE.SET_NAME('INV', 'INV_RCV_PARAM');
762 FND_MSG_PUB.ADD;
763 RAISE ;
764 End;
765
766 l_progress := '20';
767 -- default l_group_id ? clear group id after done
768 IF inv_rcv_common_apis.g_rcv_global_var.interface_group_id is NULL THEN
769 SELECT rcv_interface_groups_s.nextval
770 INTO l_group_id
771 FROM dual;
772 inv_rcv_common_apis.g_rcv_global_var.interface_group_id := l_group_id;
773 ELSE
774 l_group_id := inv_rcv_common_apis.g_rcv_global_var.interface_group_id;
775 END IF;
776
777 l_progress := '30';
778 -- initialize input record for matching algorithm
779 gml_rcv_std_rcpt_apis.g_rcpt_match_table_gross(gml_rcv_std_rcpt_apis.g_receipt_detail_index).transaction_type := 'DELIVER';
780 gml_rcv_std_rcpt_apis.g_rcpt_match_table_gross(gml_rcv_std_rcpt_apis.g_receipt_detail_index).quantity := p_rcv_qty;
781 gml_rcv_std_rcpt_apis.g_rcpt_match_table_gross(gml_rcv_std_rcpt_apis.g_receipt_detail_index).unit_of_measure := p_rcv_uom;
782
783 -- OPM changes
784 gml_rcv_std_rcpt_apis.g_rcpt_match_table_gross(inv_rcv_std_rcpt_apis.g_receipt_detail_index).secondary_quantity := p_rcv_sec_qty;
785 gml_rcv_std_rcpt_apis.g_rcpt_match_table_gross(inv_rcv_std_rcpt_apis.g_receipt_detail_index).secondary_unit_of_measure := p_rcv_sec_uom;
786
787 if p_inv_item_id is not null then -- p_item_id has substitute item id
788 gml_rcv_std_rcpt_apis.g_rcpt_match_table_gross(gml_rcv_std_rcpt_apis.g_receipt_detail_index).item_id := p_inv_item_id;
789 else
790 IF p_item_id IS NOT NULL THEN
791 gml_rcv_std_rcpt_apis.g_rcpt_match_table_gross(gml_rcv_std_rcpt_apis.g_receipt_detail_index).item_id := p_item_id;
792 ELSE
793 gml_rcv_std_rcpt_apis.g_rcpt_match_table_gross(gml_rcv_std_rcpt_apis.g_receipt_detail_index).item_id := NULL;
794 gml_rcv_std_rcpt_apis.g_rcpt_match_table_gross(gml_rcv_std_rcpt_apis.g_receipt_detail_index).item_desc := p_item_desc;
795 end if;
796 end if;
797
798 gml_rcv_std_rcpt_apis.g_rcpt_match_table_gross(gml_rcv_std_rcpt_apis.g_receipt_detail_index).to_organization_id := p_organization_id;
799 gml_rcv_std_rcpt_apis.g_rcpt_match_table_gross(gml_rcv_std_rcpt_apis.g_receipt_detail_index).group_id := l_group_id;
800 gml_rcv_std_rcpt_apis.g_rcpt_match_table_gross(gml_rcv_std_rcpt_apis.g_receipt_detail_index).po_header_id := p_po_header_id;
801 gml_rcv_std_rcpt_apis.g_rcpt_match_table_gross(gml_rcv_std_rcpt_apis.g_receipt_detail_index).po_release_id := p_po_release_id;
802
803 -- line id, line location id and distribution id will be passed only from the putaway api.
804 -- line id however, can also be passed through the UI if the line number
805 -- field is enabled on the UI.
806 gml_rcv_std_rcpt_apis.g_rcpt_match_table_gross(gml_rcv_std_rcpt_apis.g_receipt_detail_index).po_line_id := p_po_line_id;
807 gml_rcv_std_rcpt_apis.g_rcpt_match_table_gross(gml_rcv_std_rcpt_apis.g_receipt_detail_index).po_line_location_id := p_po_line_location_id;
808 gml_rcv_std_rcpt_apis.g_rcpt_match_table_gross(gml_rcv_std_rcpt_apis.g_receipt_detail_index).po_distribution_id := p_po_distribution_id;
809
810 IF p_item_id IS NOT NULL THEN
811 BEGIN
812 select primary_unit_of_measure
813 into gml_rcv_std_rcpt_apis.g_rcpt_match_table_gross(gml_rcv_std_rcpt_apis.g_receipt_detail_index).primary_unit_of_measure
814 from mtl_system_items
815 where mtl_system_items.inventory_item_id = p_item_id
816 and mtl_system_items.organization_id = p_organization_id;
817 EXCEPTION
818 when no_data_found then
819 NULL;
820
821 END;
822 ELSE
823 gml_rcv_std_rcpt_apis.g_rcpt_match_table_gross(gml_rcv_std_rcpt_apis.g_receipt_detail_index).primary_unit_of_measure := NULL;
824 END IF;
825
826 gml_rcv_std_rcpt_apis.g_rcpt_match_table_gross(gml_rcv_std_rcpt_apis.g_receipt_detail_index).revision := p_revision;
827 gml_rcv_std_rcpt_apis.g_rcpt_match_table_gross(gml_rcv_std_rcpt_apis.g_receipt_detail_index).project_id := p_project_id;
828 gml_rcv_std_rcpt_apis.g_rcpt_match_table_gross(gml_rcv_std_rcpt_apis.g_receipt_detail_index).task_id := p_task_id;
829 gml_rcv_std_rcpt_apis.g_rcpt_match_table_gross(gml_rcv_std_rcpt_apis.g_receipt_detail_index).expected_receipt_date := Sysdate; --?
830 gml_rcv_std_rcpt_apis.g_rcpt_match_table_gross(gml_rcv_std_rcpt_apis.g_receipt_detail_index).tax_amount := 0; -- ?
831 gml_rcv_std_rcpt_apis.g_rcpt_match_table_gross(gml_rcv_std_rcpt_apis.g_receipt_detail_index).error_status := 'S'; -- ?
832
833
834 l_progress := '40';
835 --- OPM Specific version
836
837 gml_rcv_txn_interface.matching_logic
838 (x_return_status => x_status,
839 x_msg_count => l_msg_count,
840 x_msg_data => x_message,
841 x_cascaded_table => gml_rcv_std_rcpt_apis.g_rcpt_match_table_gross,
842 n => gml_rcv_std_rcpt_apis.g_receipt_detail_index,
843 temp_cascaded_table => l_rcpt_match_table_detail,
844 p_receipt_num => NULL,
845 p_shipment_header_id => NULL,
846 p_lpn_id => NULL
847 );
848
849 -- x_status is not successful if there is any execution error in matching.
850 IF x_status = fnd_api.g_ret_sts_error THEN
851 FND_MESSAGE.SET_NAME('INV', 'INV_RCV_MATCH_ERROR');
852 FND_MSG_PUB.ADD;
853 RAISE fnd_api.g_exc_error;
854 END IF;
855
856 IF x_status = fnd_api.g_ret_sts_unexp_error THEN
857 FND_MESSAGE.SET_NAME('INV', 'INV_RCV_MATCH_ERROR');
858 FND_MSG_PUB.ADD;
859 RAISE fnd_api.g_exc_unexpected_error;
860 END IF;
861
862 IF gml_rcv_std_rcpt_apis.g_rcpt_match_table_gross(gml_rcv_std_rcpt_apis.g_receipt_detail_index).error_status = 'E' THEN
863 l_err_message := gml_rcv_std_rcpt_apis.g_rcpt_match_table_gross(gml_rcv_std_rcpt_apis.g_receipt_detail_index).error_message;
864 FND_MESSAGE.SET_NAME('INV', l_err_message);
865 FND_MSG_PUB.ADD;
866 RAISE fnd_api.g_exc_error;
867 END IF;
868
869
870 l_err_message := '@@@';
871 FOR i IN gml_rcv_std_rcpt_apis.g_receipt_detail_index..(gml_rcv_std_rcpt_apis.g_receipt_detail_index + l_rcpt_match_table_detail.COUNT - 1) LOOP
872 IF l_rcpt_match_table_detail(i-gml_rcv_std_rcpt_apis.g_receipt_detail_index+1).error_status = 'W' THEN
873 x_status := 'W';
874
875 l_temp_message := l_rcpt_match_table_detail(i-gml_rcv_std_rcpt_apis.g_receipt_detail_index+1).error_message;
876 IF l_temp_message IS NULL THEN
877 l_err_message := 'INV_RCV_GEN_TOLERANCE_EXCEED';
878 l_msg_prod := 'INV';
879 EXIT;
880 END IF;
881 IF l_err_message = '@@@' THEN
882 l_err_message := l_temp_message;
883 l_msg_prod := 'INV';
884 ELSIF l_temp_message <> l_err_message THEN
885 l_msg_prod := 'INV';
886 l_err_message := 'INV_RCV_GEN_TOLERANCE_EXCEED';
887 EXIT;
888 END IF;
889 END IF;
890 END LOOP;
891
892 IF l_err_message <> '@@@' THEN
893 FND_MESSAGE.SET_NAME(l_msg_prod, l_err_message);
894 FND_MSG_PUB.ADD;
895 END IF;
896
897 -- based on return from matching algorithm,
898 -- determine which line in rcv_transaction block to be inserted into RTI
899
900
901 l_progress := '60';
902
903 -- loop through results returned by matching algorithm
904 FOR match_result_count IN 1..l_rcpt_match_table_detail.COUNT LOOP
905 l_progress := '62';
906 OPEN l_curs_rcpt_detail(l_rcpt_match_table_detail(match_result_count).po_distribution_id);
907 l_progress := '64';
908 FETCH l_curs_rcpt_detail INTO l_rcv_rcpt_rec;
909
910 -- Earlier item_id was filled with PO Line Item ID if the parameter p_inv_item_id
911 -- is not null, so that matching logic finds shipments. Now, in order to actually
912 -- insert RTI, replace item_id with a new value which is nothing but the substitute
913 -- item.
914 l_rcv_rcpt_rec.item_id := p_item_id;
915
916 l_progress := '66';
917 CLOSE l_curs_rcpt_detail;
918 l_progress := '68';
919
920
921 l_rcv_transaction_rec.po_distribution_id := l_rcpt_match_table_detail(match_result_count).po_distribution_id;
922
923 -- update following fields from matching algorithm return value
924 l_rcv_transaction_rec.transaction_qty := l_rcpt_match_table_detail(match_result_count).quantity;
925 l_rcv_transaction_rec.transaction_uom := l_rcpt_match_table_detail(match_result_count).unit_of_measure;
926 l_rcv_transaction_rec.primary_quantity := l_rcpt_match_table_detail(match_result_count).primary_quantity;
927 l_rcv_transaction_rec.primary_uom := l_rcpt_match_table_detail(match_result_count).primary_unit_of_measure;
928 l_total_primary_qty := l_total_primary_qty + l_rcv_transaction_rec.primary_quantity;
929 l_rcv_transaction_rec.secondary_quantity := l_rcpt_match_table_detail(match_result_count).secondary_quantity;
930 l_rcv_transaction_rec.secondary_uom_code := p_rcv_sec_uom_code;
931 l_rcv_transaction_rec.secondary_unit_of_measure := p_rcv_sec_uom;
932
933 l_progress := '70';
934
935 l_rcv_transaction_rec.lpn_id := p_lpn_id;
936 l_rcv_transaction_rec.transfer_lpn_id := p_lpn_id;
937 -- update following fields for po_distribution related values
938 l_rcv_transaction_rec.currency_conversion_date := l_rcv_rcpt_rec.currency_conversion_date_pod;
939 l_rcv_transaction_rec.currency_conversion_rate := l_rcv_rcpt_rec.currency_conversion_rate_pod;
940 l_rcv_transaction_rec.ordered_qty := l_rcv_rcpt_rec.qty_ordered;
941 --Bug 2073164
942 l_rcv_rcpt_rec.uom_code := p_rcv_uom_code;
943 l_rcv_transaction_rec.lpn_id := p_lpn_id;
944
945 -- wip related fields
946 IF l_rcv_rcpt_rec.wip_entity_id > 0 THEN
947 l_rcv_transaction_rec.wip_entity_id := l_rcv_rcpt_rec.wip_entity_id;
948 l_rcv_transaction_rec.wip_operation_seq_num := l_rcv_rcpt_rec.wip_operation_seq_num;
949 l_rcv_transaction_rec.wip_resource_seq_num := l_rcv_rcpt_rec.wip_resource_seq_num;
950
951 l_rcv_transaction_rec.wip_repetitive_schedule_id := l_rcv_rcpt_rec.wip_repetitive_schedule_id;
952 l_rcv_transaction_rec.wip_line_id := l_rcv_rcpt_rec.wip_line_id;
953 l_rcv_transaction_rec.bom_resource_id := l_rcv_transaction_rec.bom_resource_id;
954 -- there is getting actual values call for wip
955 -- since they are not inserted in RTI, I am not calling it here
956 -- the code is in
957 -- rcv_transactions_sv.get_wip_info ()
958 END IF;
959
960 IF p_country_code IS NOT NULL THEN
961 l_rcv_rcpt_rec.COUNTRY_OF_ORIGIN_CODE := p_country_code;
962 END IF;
963 l_progress := '71';
964
965 if l_rcv_rcpt_rec.destination_type_code = 'EXPENSE' then
966 if l_rcv_transaction_rec.deliver_to_location_id is null and
967 p_location_id is not null then
968 l_rcv_transaction_rec.deliver_to_location_id := p_location_id;
969 End if;
970 End if;
971
972
973 populate_default_values(p_rcv_transaction_rec => l_rcv_transaction_rec,
974 p_rcv_rcpt_rec => l_rcv_rcpt_rec,
975 p_group_id => l_group_id,
976 p_organization_id => p_organization_id,
977 p_item_id => p_item_id,
978 p_revision => p_revision,
979 p_source_type => p_source_type,
980 p_subinventory_code => p_subinventory,
981 p_locator_id => p_locator_id,
982 p_transaction_temp_id => p_transaction_temp_id,
983 p_lot_control_code => p_lot_control_code,
984 p_serial_control_code => p_serial_control_code);
985 l_progress := '80';
986
987 /* FP-J Lot/Serial Support Enhancement
988 * Populate the table to store the information of the RTIs created used for
989 * splitting the lots and serials based on RTI quantity
990 */
991
992 l_new_rti_info(match_result_count).orig_interface_trx_id := p_transaction_temp_id;
993 l_new_rti_info(match_result_count).new_interface_trx_id := g_interface_transaction_id;
994 l_new_rti_info(match_result_count).quantity := l_rcv_transaction_rec.transaction_qty;
995
996
997 END LOOP;
998 -- append index in input table where the line to be detailed needs to be inserted
999 --inv_rcv_std_rcpt_apis.g_receipt_detail_index := l_rcpt_match_table_detail.COUNT + inv_rcv_std_rcpt_apis.g_receipt_detail_index;
1000
1001 l_split_lot_serial_ok := inv_rcv_integration_apis.split_lot_serial(
1002 p_api_version => 1.0
1003 , p_init_msg_lst => FND_API.G_FALSE
1004 , x_return_status => l_return_status
1005 , x_msg_count => l_msg_count
1006 , x_msg_data => x_message
1007 , p_new_rti_info => l_new_rti_info);
1008
1009 IF (NOT l_split_lot_serial_ok) THEN
1010 RAISE FND_API.G_EXC_ERROR;
1011 END IF;
1012
1013
1014 l_progress := '90';
1015
1016 /** Shortage checking not supported by OPM
1017
1018 inv_rcv_common_apis.do_check
1019 (p_organization_id => p_organization_id,
1020 p_inventory_item_id => p_item_id,
1021 p_transaction_type_id => 18,
1022 p_primary_quantity => l_total_primary_qty,
1023 x_return_status => l_return_status,
1024 x_msg_count => l_msg_count,
1025 x_msg_data => x_message);
1026
1027 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1028 x_status := l_return_status;
1029 END IF;
1030 */
1031 l_progress := '100';
1032
1033 -- Clear the Lot Rec
1034 ---inv_rcv_std_rcpt_apis.g_rcpt_lot_qty_rec_tb.DELETE;
1035 gml_rcv_std_rcpt_apis.g_rcpt_lot_qty_rec_tb.DELETE;
1036
1037
1038 EXCEPTION
1039 WHEN fnd_api.g_exc_error THEN
1040 ROLLBACK TO crt_po_rti_sp;
1041 x_status := fnd_api.g_ret_sts_error;
1042
1043 IF l_curs_rcpt_detail%isopen THEN
1044 CLOSE l_curs_rcpt_detail;
1045 END IF;
1046
1047 fnd_msg_pub.count_and_get
1048 (p_encoded => FND_API.g_false,
1049 p_count => l_msg_count,
1050 p_data => x_message
1051 );
1052
1053 WHEN fnd_api.g_exc_unexpected_error THEN
1054 ROLLBACK TO crt_po_rti_sp;
1055 x_status := fnd_api.g_ret_sts_unexp_error ;
1056 IF l_curs_rcpt_detail%isopen THEN
1057 CLOSE l_curs_rcpt_detail;
1058 END IF;
1059
1060 fnd_msg_pub.count_and_get
1061 (p_encoded => FND_API.g_false,
1062 p_count => l_msg_count,
1063 p_data => x_message
1064 );
1065
1066
1067 WHEN OTHERS THEN
1068 x_message := SQLERRM;
1069 ROLLBACK TO crt_po_rti_sp;
1070 x_status := fnd_api.g_ret_sts_unexp_error ;
1071 IF l_curs_rcpt_detail%isopen THEN
1072 CLOSE l_curs_rcpt_detail;
1073 END IF;
1074 fnd_msg_pub.count_and_get
1075 (p_encoded => FND_API.g_false,
1076 p_count => l_msg_count,
1077 p_data => x_message
1078 );
1079
1080
1081 END create_po_drct_dlvr_rti_rec;
1082
1083
1084
1085 PROCEDURE create_int_shp_dr_del_rti_rec(p_move_order_header_id IN OUT NOCOPY NUMBER,
1086 p_organization_id IN NUMBER,
1087 p_shipment_header_id IN NUMBER,
1088 p_shipment_line_id IN NUMBER,
1089 p_item_id IN NUMBER,
1090 p_rcv_qty IN NUMBER,
1091 p_rcv_uom IN VARCHAR2,
1092 p_rcv_uom_code IN VARCHAR2,
1093 p_source_type IN VARCHAR2,
1094 p_subinventory VARCHAR2,
1095 p_locator_id NUMBER,
1096 p_transaction_temp_id IN NUMBER,
1097 p_lot_control_code IN NUMBER,
1098 p_serial_control_code IN NUMBER,
1099 p_lpn_id IN NUMBER,
1100 p_revision IN VARCHAR2,
1101 p_project_id IN NUMBER DEFAULT NULL,
1102 p_task_id IN NUMBER DEFAULT NULL,
1103 x_status OUT NOCOPY VARCHAR2,
1104 x_message OUT NOCOPY VARCHAR2,
1105 p_country_code IN VARCHAR2 DEFAULT NULL
1106 )
1107
1108 IS
1109
1110 l_rcpt_match_table_detail INV_RCV_COMMON_APIS.cascaded_trans_tab_type; -- output for matching algorithm
1111
1112 l_rcv_transaction_rec gml_rcv_std_rcpt_apis.rcv_transaction_rec_tp; -- rcv_transaction block
1113
1114 l_transaction_type VARCHAR2(20) := 'DELIVER';
1115 l_total_primary_qty NUMBER := 0;
1116
1117 l_msg_count NUMBER;
1118 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
1119
1120 l_group_id NUMBER;
1121
1122 l_rcv_rcpt_rec gml_rcv_std_rcpt_apis.rcv_enter_receipts_rec_tp;
1123 l_mmtt_rec mtl_material_transactions_temp%ROWTYPE;
1124
1125 l_err_message VARCHAR2(100);
1126 l_temp_message VARCHAR2(100);
1127 l_msg_prod VARCHAR2(5);
1128
1129 l_progress VARCHAR2(10);
1130 l_receipt_num VARCHAR2(30);
1131
1132 CURSOR l_curs_rcpt_detail
1133 (v_shipment_line_id NUMBER)
1134 IS
1135 SELECT
1136 'N' LINE_CHKBOX,
1137 'INTERNAL' SOURCE_TYPE_CODE,
1138 DECODE(RSL.SOURCE_DOCUMENT_CODE,'INVENTORY','INVENTORY','REQ','INTERNAL ORDER') RECEIPT_SOURCE_CODE,
1139 RSL.SOURCE_DOCUMENT_CODE ORDER_TYPE_CODE,
1140 '' ORDER_TYPE,
1141 RSH.SHIPMENT_HEADER_ID PO_HEADER_ID,
1142 RSH.SHIPMENT_NUM PO_NUMBER,
1143 RSL.SHIPMENT_LINE_ID PO_LINE_ID,
1144 RSL.LINE_NUM PO_LINE_NUMBER,
1145 RSL.SHIPMENT_LINE_ID PO_LINE_LOCATION_ID,
1146 RSL.LINE_NUM PO_SHIPMENT_NUMBER,
1147 RSH.SHIPMENT_HEADER_ID PO_RELEASE_ID,
1148 RSH.SHIPMENT_HEADER_ID PO_RELEASE_NUMBER,
1149 PORH.REQUISITION_HEADER_ID REQ_HEADER_ID,
1150 PORH.SEGMENT1 REQ_NUMBER,
1151 PORL.REQUISITION_LINE_ID REQ_LINE_ID,
1152 PORL.LINE_NUM REQ_LINE,
1153 RSL.REQ_DISTRIBUTION_ID REQ_DISTRIBUTION_ID,
1154 RSL.SHIPMENT_HEADER_ID RCV_SHIPMENT_HEADER_ID,
1155 RSH.SHIPMENT_NUM RCV_SHIPMENT_NUMBER,
1156 RSL.SHIPMENT_LINE_ID RCV_SHIPMENT_LINE_ID,
1157 RSL.LINE_NUM RCV_LINE_NUMBER,
1158 RSL.FROM_ORGANIZATION_ID FROM_ORGANIZATION_ID,
1159 RSL.TO_ORGANIZATION_ID TO_ORGANIZATION_ID,
1160 RSL.SHIPMENT_LINE_ID VENDOR_ID,
1161 '' SOURCE,
1162 TO_NUMBER(NULL) VENDOR_SITE_ID,
1163 'N' OUTSIDE_OPERATION_FLAG,
1164 RSL.ITEM_ID ITEM_ID,
1165 -- Bug 2073164
1166 NULL uom_code,
1167 RSL.UNIT_OF_MEASURE PRIMARY_UOM,
1168 MUM.UOM_CLASS PRIMARY_UOM_CLASS,
1169 NVL(MSI.ALLOWED_UNITS_LOOKUP_CODE,2) ITEM_ALLOWED_UNITS_LOOKUP_CODE,
1170 NVL(MSI.LOCATION_CONTROL_CODE,1) ITEM_LOCATOR_CONTROL,
1171 DECODE(MSI.RESTRICT_LOCATORS_CODE,1,'Y','N') RESTRICT_LOCATORS_CODE,
1172 DECODE(MSI.RESTRICT_SUBINVENTORIES_CODE,1,'Y','N') RESTRICT_SUBINVENTORIES_CODE,
1173 NVL(MSI.SHELF_LIFE_CODE,1) SHELF_LIFE_CODE,
1174 NVL(MSI.SHELF_LIFE_DAYS,0) SHELF_LIFE_DAYS,
1175 MSI.SERIAL_NUMBER_CONTROL_CODE SERIAL_NUMBER_CONTROL_CODE,
1176 MSI.LOT_CONTROL_CODE LOT_CONTROL_CODE,
1177 DECODE(MSI.REVISION_QTY_CONTROL_CODE,1,'N',2,'Y','N') ITEM_REV_CONTROL_FLAG_TO,
1178 DECODE(MSI1.REVISION_QTY_CONTROL_CODE, 1,'N',2,'Y','N') ITEM_REV_CONTROL_FLAG_FROM,
1179 NULL ITEM_NUMBER,
1180 RSL.ITEM_REVISION ITEM_REVISION,
1181 RSL.ITEM_DESCRIPTION ITEM_DESCRIPTION,
1182 RSL.CATEGORY_ID ITEM_CATEGORY_ID,
1183 '' HAZARD_CLASS,
1184 '' UN_NUMBER,
1185 RSL.VENDOR_ITEM_NUM VENDOR_ITEM_NUMBER,
1186 RSH.SHIP_TO_LOCATION_ID SHIP_TO_LOCATION_ID,
1187 '' SHIP_TO_LOCATION,
1188 RSH.PACKING_SLIP PACKING_SLIP,
1189 RSL.ROUTING_HEADER_ID ROUTING_ID,
1190 '' ROUTING_NAME,
1191 PORL.NEED_BY_DATE NEED_BY_DATE,
1192 RSH.EXPECTED_RECEIPT_DATE EXPECTED_RECEIPT_DATE,
1193 RSL.QUANTITY_SHIPPED ORDERED_QTY,
1194 RSL.PRIMARY_UNIT_OF_MEASURE ORDERED_UOM,
1195 RSH.USSGL_TRANSACTION_CODE USSGL_TRANSACTION_CODE,
1196 RSH.GOVERNMENT_CONTEXT GOVERNMENT_CONTEXT,
1197 NULL INSPECTION_REQUIRED_FLAG,
1198 NULL RECEIPT_REQUIRED_FLAG,
1199 NULL ENFORCE_SHIP_TO_LOCATION_CODE,
1200 TO_NUMBER(NULL) UNIT_PRICE,
1201 NULL CURRENCY_CODE,
1202 NULL CURRENCY_CONVERSION_TYPE,
1203 TO_DATE(NULL) CURRENCY_CONVERSION_DATE,
1204 TO_NUMBER(NULL) CURRENCY_CONVERSION_RATE,
1205 NULL note_to_receiver,
1206 --PORL.NOTE_TO_RECEIVER NOTE_TO_RECEIVER,
1207 RSL.DESTINATION_TYPE_CODE DESTINATION_TYPE_CODE,
1208 RSL.DELIVER_TO_PERSON_ID DELIVER_TO_PERSON_ID,
1209 RSL.DELIVER_TO_LOCATION_ID DELIVER_TO_LOCATION_ID,
1210 RSL.TO_SUBINVENTORY DESTINATION_SUBINVENTORY,
1211 RSL.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY,
1212 RSL.ATTRIBUTE1 ATTRIBUTE1,
1213 RSL.ATTRIBUTE2 ATTRIBUTE2,
1214 RSL.ATTRIBUTE3 ATTRIBUTE3,
1215 RSL.ATTRIBUTE4 ATTRIBUTE4,
1216 RSL.ATTRIBUTE5 ATTRIBUTE5,
1217 RSL.ATTRIBUTE6 ATTRIBUTE6,
1218 RSL.ATTRIBUTE7 ATTRIBUTE7,
1219 RSL.ATTRIBUTE8 ATTRIBUTE8,
1220 RSL.ATTRIBUTE9 ATTRIBUTE9,
1221 RSL.ATTRIBUTE10 ATTRIBUTE10,
1222 RSL.ATTRIBUTE11 ATTRIBUTE11,
1223 RSL.ATTRIBUTE12 ATTRIBUTE12,
1224 RSL.ATTRIBUTE13 ATTRIBUTE13,
1225 RSL.ATTRIBUTE14 ATTRIBUTE14,
1226 RSL.ATTRIBUTE15 ATTRIBUTE15,
1227 'OPEN' CLOSED_CODE,
1228 NULL ASN_TYPE,
1229 RSH.BILL_OF_LADING BILL_OF_LADING,
1230 RSH.SHIPPED_DATE SHIPPED_DATE,
1231 RSH.FREIGHT_CARRIER_CODE FREIGHT_CARRIER_CODE,
1232 RSH.WAYBILL_AIRBILL_NUM WAYBILL_AIRBILL_NUM,
1233 RSH.FREIGHT_BILL_NUMBER FREIGHT_BILL_NUM,
1234 RSL.VENDOR_LOT_NUM VENDOR_LOT_NUM,
1235 RSL.CONTAINER_NUM CONTAINER_NUM,
1236 RSL.TRUCK_NUM TRUCK_NUM,
1237 RSL.BAR_CODE_LABEL BAR_CODE_LABEL,
1238 NULL RATE_TYPE_DISPLAY,
1239 'P' MATCH_OPTION,
1240 NULL COUNTRY_OF_ORIGIN_CODE,
1241 TO_NUMBER(NULL) OE_ORDER_HEADER_ID,
1242 TO_NUMBER(NULL) OE_ORDER_NUM,
1243 TO_NUMBER(NULL) OE_ORDER_LINE_ID,
1244 TO_NUMBER(NULL) OE_ORDER_LINE_NUM,
1245 TO_NUMBER(NULL) CUSTOMER_ID,
1246 TO_NUMBER(NULL) CUSTOMER_SITE_ID,
1247 NULL CUSTOMER_ITEM_NUM,
1248 NULL pll_note_to_receiver,
1249 --PORL.NOTE_TO_RECEIVER PLL_NOTE_TO_RECEIVER,
1250 NULL PO_DISTRIBUTION_ID,
1251 NULL QTY_ORDERED,
1252 NULL WIP_ENTITY_ID,
1253 NULL WIP_OPERATION_SEQ_NUM,
1254 NULL WIP_RESOURCE_SEQ_NUM,
1255 NULL WIP_REPETITIVE_SCHEDULE_ID,
1256 NULL WIP_LINE_ID,
1257 NULL BOM_RESOURCE_ID,
1258 '' DESTINATION_TYPE,
1259 '' LOCATION,
1260 NULL CURRENCY_CONVERSION_RATE_POD,
1261 NULL CURRENCY_CONVERSION_DATE_POD,
1262 NULL PROJECT_ID,
1263 NULL TASK_ID
1264 FROM
1265 RCV_SHIPMENT_HEADERS RSH,
1266 RCV_SHIPMENT_LINES RSL,
1267 PO_REQUISITION_HEADERS PORH,
1268 PO_REQUISITION_LINES PORL,
1269 MTL_SYSTEM_ITEMS MSI,
1270 MTL_SYSTEM_ITEMS MSI1,
1271 MTL_UNITS_OF_MEASURE MUM
1272 WHERE
1273 RSH.RECEIPT_SOURCE_CODE <> 'VENDOR'
1274 AND RSL.REQUISITION_LINE_ID = PORL.REQUISITION_LINE_ID(+)
1275 AND PORL.REQUISITION_HEADER_ID = PORH.REQUISITION_HEADER_ID(+)
1276 AND RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID
1277 AND MUM.UNIT_OF_MEASURE (+) = RSL.UNIT_OF_MEASURE
1278 AND MSI.ORGANIZATION_ID (+) = RSL.TO_ORGANIZATION_ID
1279 AND MSI.INVENTORY_ITEM_ID (+) = RSL.ITEM_ID
1280 AND MSI1.ORGANIZATION_ID (+) = RSL.FROM_ORGANIZATION_ID
1281 AND MSI1.INVENTORY_ITEM_ID (+) = RSL.ITEM_ID
1282 AND RSL.SHIPMENT_LINE_ID = v_shipment_line_id
1283 AND (( rsl.source_document_code = 'REQ' and
1284 exists
1285 (select '1'
1286 from po_req_distributions_all prd
1287 where (p_project_id is null or
1288 (p_project_id = -9999 and prd.project_id is null) or -- bug 2669021
1289 prd.project_id = p_project_id
1290 )
1291 and (p_task_id is null or prd.task_id = p_task_id)
1292 )
1293 )or rsl.source_document_code <> 'REQ'
1294 );
1295 BEGIN
1296 x_status := fnd_api.g_ret_sts_success;
1297 l_progress := '10';
1298 SAVEPOINT crt_intship_rti_sp;
1299
1300 -- query po_startup_value
1301 Begin
1302 /* Bug #2516729
1303 * Fetch rcv_shipment_headers.receipt_number for the given shipment_header_id.
1304 * If it exists , assign it to the global variable for receipt # (g_rcv_global_var.receipt_num)
1305 * in order that a new receipt # is not created everytime and the existing receipt # is used
1306 */
1307 BEGIN
1308 SELECT receipt_num
1309 INTO l_receipt_num
1310 FROM rcv_shipment_headers
1311 WHERE shipment_header_id = p_shipment_header_id
1312 AND ship_to_org_id = p_organization_id;
1313
1314 inv_rcv_common_apis.g_rcv_global_var.receipt_num := l_receipt_num;
1315 EXCEPTION
1316 WHEN NO_DATA_FOUND THEN
1317 l_receipt_num := NULL;
1318 END;
1319 inv_rcv_common_apis.init_startup_values(p_organization_id);
1320 Exception
1321 when NO_DATA_FOUND then
1322 FND_MESSAGE.SET_NAME('INV', 'INV_RCV_PARAM');
1323 FND_MSG_PUB.ADD;
1324 RAISE ;
1325 End;
1326
1327 l_progress := '20';
1328 -- default l_group_id ? clear group id after done
1329 IF inv_rcv_common_apis.g_rcv_global_var.interface_group_id is NULL THEN
1330 SELECT rcv_interface_groups_s.nextval
1331 INTO l_group_id
1332 FROM dual;
1333 inv_rcv_common_apis.g_rcv_global_var.interface_group_id := l_group_id;
1334 ELSE
1335 l_group_id := inv_rcv_common_apis.g_rcv_global_var.interface_group_id;
1336 END IF;
1337
1338 l_progress := '30';
1339 -- initialize input record for matching algorithm
1340 inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross(inv_rcv_std_rcpt_apis.g_receipt_detail_index).transaction_type := 'DELIVER';
1341 inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross(inv_rcv_std_rcpt_apis.g_receipt_detail_index).quantity := p_rcv_qty;
1342 inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross(inv_rcv_std_rcpt_apis.g_receipt_detail_index).unit_of_measure := p_rcv_uom;
1343 inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross(inv_rcv_std_rcpt_apis.g_receipt_detail_index).group_id := l_group_id;
1344 inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross(inv_rcv_std_rcpt_apis.g_receipt_detail_index).item_id := p_item_id;
1345 inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross(inv_rcv_std_rcpt_apis.g_receipt_detail_index).to_organization_id := p_organization_id;
1346 inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross(inv_rcv_std_rcpt_apis.g_receipt_detail_index).shipment_header_id := p_shipment_header_id;
1347 -- line id will be passed only from the putaway api.
1348 inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross(inv_rcv_std_rcpt_apis.g_receipt_detail_index).shipment_line_id := p_shipment_line_id;
1349 inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross(inv_rcv_std_rcpt_apis.g_receipt_detail_index).project_id := p_project_id;
1350 inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross(inv_rcv_std_rcpt_apis.g_receipt_detail_index).task_id := p_task_id;
1351
1352 BEGIN
1353 select primary_unit_of_measure
1354 into inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross(inv_rcv_std_rcpt_apis.g_receipt_detail_index).primary_unit_of_measure
1355 from mtl_system_items
1356 where mtl_system_items.inventory_item_id = p_item_id
1357 and mtl_system_items.organization_id = p_organization_id;
1358 EXCEPTION
1359 when no_data_found then
1360 NULL;
1361 END;
1362
1363 inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross(inv_rcv_std_rcpt_apis.g_receipt_detail_index).expected_receipt_date := Sysdate; --?
1364 inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross(inv_rcv_std_rcpt_apis.g_receipt_detail_index).tax_amount := 0; -- ?
1365 inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross(inv_rcv_std_rcpt_apis.g_receipt_detail_index).error_status := 'S'; -- ?
1366
1367 /** EOU Commented out as this procedure is not needed by OPM
1368 l_progress := '40';
1369 inv_rcv_txn_match.matching_logic
1370 (x_return_status => x_status, --?
1371 x_msg_count => l_msg_count,
1372 x_msg_data => x_message,
1373 x_cascaded_table => inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross,
1374 n => inv_rcv_std_rcpt_apis.g_receipt_detail_index,
1375 temp_cascaded_table => l_rcpt_match_table_detail,
1376 p_receipt_num => NULL,
1377 p_match_type => 'INTRANSIT SHIPMENT',
1378 p_lpn_id => NULL
1379 );
1380 */
1381
1382 -- x_status is not successful if there is any execution error in matching.
1383 IF x_status = fnd_api.g_ret_sts_error THEN
1384 FND_MESSAGE.SET_NAME('INV', 'INV_RCV_MATCH_ERROR');
1385 FND_MSG_PUB.ADD;
1386 RAISE fnd_api.g_exc_error;
1387 END IF;
1388
1389 IF x_status = fnd_api.g_ret_sts_unexp_error THEN
1390 FND_MESSAGE.SET_NAME('INV', 'INV_RCV_MATCH_ERROR');
1391 FND_MSG_PUB.ADD;
1392 RAISE fnd_api.g_exc_unexpected_error;
1393 END IF;
1394
1395 IF inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross(inv_rcv_std_rcpt_apis.g_receipt_detail_index).error_status = 'E' THEN
1396 l_err_message := inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross(inv_rcv_std_rcpt_apis.g_receipt_detail_index).error_message;
1397 FND_MESSAGE.SET_NAME('INV', l_err_message);
1398 FND_MSG_PUB.ADD;
1399 RAISE fnd_api.g_exc_error;
1400 END IF;
1401
1402 l_err_message := '@@@';
1403 FOR i IN inv_rcv_std_rcpt_apis.g_receipt_detail_index..(inv_rcv_std_rcpt_apis.g_receipt_detail_index + l_rcpt_match_table_detail.COUNT - 1) LOOP
1404 IF l_rcpt_match_table_detail(i-inv_rcv_std_rcpt_apis.g_receipt_detail_index+1).error_status = 'W' THEN
1405 x_status := 'W';
1406
1407 l_temp_message := l_rcpt_match_table_detail(i-inv_rcv_std_rcpt_apis.g_receipt_detail_index+1).error_message;
1408 IF l_temp_message IS NULL THEN
1409 l_err_message := 'INV_RCV_GEN_TOLERANCE_EXCEED';
1410 l_msg_prod := 'INV';
1411 EXIT;
1412 END IF;
1413 IF l_err_message = '@@@' THEN
1414 l_err_message := l_temp_message;
1415 l_msg_prod := 'INV';
1416 ELSIF l_temp_message <> l_err_message THEN
1417 l_msg_prod := 'INV';
1418 l_err_message := 'INV_RCV_GEN_TOLERANCE_EXCEED';
1419 EXIT;
1420 END IF;
1421 END IF;
1422 END LOOP;
1423
1424 IF l_err_message <> '@@@' THEN
1425 FND_MESSAGE.SET_NAME(l_msg_prod, l_err_message);
1426 FND_MSG_PUB.ADD;
1427 END IF;
1428
1429 l_progress := '50';
1430
1431
1432 l_rcv_transaction_rec.rcv_shipment_line_id := l_rcv_rcpt_rec.rcv_shipment_line_id;
1433
1434 -- loop through results returned by matching algorithm
1435 l_progress := '60';
1436 FOR match_result_count IN 1..l_rcpt_match_table_detail.COUNT LOOP
1437
1438 l_progress := '62';
1439 OPEN l_curs_rcpt_detail(l_rcpt_match_table_detail(match_result_count).shipment_line_id);
1440 l_progress := '64';
1441 FETCH l_curs_rcpt_detail INTO l_rcv_rcpt_rec;
1442 l_progress := '66';
1443 CLOSE l_curs_rcpt_detail;
1444 l_progress := '68';
1445
1446
1447 l_rcv_transaction_rec.rcv_shipment_line_id := l_rcpt_match_table_detail(match_result_count).shipment_line_id;
1448 -- Get the transfer_cost_group_id from rcv_shipment_lines
1449 BEGIN
1450 SELECT cost_group_id
1451 INTO l_rcv_transaction_rec.transfer_cost_group_id
1452 FROM rcv_shipment_lines
1453 WHERE shipment_line_id = l_rcv_transaction_rec.rcv_shipment_line_id;
1454 EXCEPTION
1455 WHEN OTHERS THEN
1456 l_rcv_transaction_rec.transfer_cost_group_id := NULL;
1457 END;
1458
1459 -- update following fields from matching algorithm return value
1460 l_rcv_transaction_rec.transaction_qty := l_rcpt_match_table_detail(match_result_count).quantity;
1461 l_rcv_transaction_rec.transaction_uom := l_rcpt_match_table_detail(match_result_count).unit_of_measure;
1462 --Bug 2073164
1463 l_rcv_rcpt_rec.uom_code := p_rcv_uom_code;
1464 l_rcv_transaction_rec.primary_quantity := l_rcpt_match_table_detail(match_result_count).primary_quantity;
1465 l_rcv_transaction_rec.primary_uom := l_rcpt_match_table_detail(match_result_count).primary_unit_of_measure;
1466 l_total_primary_qty := l_total_primary_qty + l_rcv_transaction_rec.primary_quantity;
1467
1468 l_progress := '70';
1469
1470
1471 l_rcv_transaction_rec.lpn_id := p_lpn_id;
1472 l_rcv_transaction_rec.transfer_lpn_id := p_lpn_id;
1473
1474 IF p_country_code IS NOT NULL THEN
1475 l_rcv_rcpt_rec.COUNTRY_OF_ORIGIN_CODE := p_country_code;
1476 END IF;
1477
1478
1479 populate_default_values(p_rcv_transaction_rec => l_rcv_transaction_rec,
1480 p_rcv_rcpt_rec => l_rcv_rcpt_rec,
1481 p_group_id => l_group_id,
1482 p_organization_id => p_organization_id,
1483 p_item_id => p_item_id,
1484 p_revision => p_revision,
1485 p_source_type => p_source_type,
1486 p_subinventory_code => p_subinventory,
1487 p_locator_id => p_locator_id,
1488 p_transaction_temp_id => p_transaction_temp_id,
1489 p_lot_control_code => p_lot_control_code,
1490 p_serial_control_code =>
1491 p_serial_control_code);
1492
1493 IF l_rcv_rcpt_rec.req_line_id IS NOT NULL AND
1494 p_serial_control_code NOT IN (1, 6) THEN
1495 -- update rss for req
1496 inv_rcv_std_deliver_apis.update_rcv_serials_supply
1497 (
1498 x_return_status => l_return_status,
1499 x_msg_count => l_msg_count,
1500 x_msg_data => x_message,
1501 p_shipment_line_id => l_rcv_transaction_rec.rcv_shipment_line_id
1502 );
1503
1504 END IF;
1505
1506
1507 END LOOP;
1508 -- append index in input table where the line to be detailed needs to be inserted
1509 --inv_rcv_std_rcpt_apis.g_receipt_detail_index := l_rcpt_match_table_detail.COUNT + inv_rcv_std_rcpt_apis.g_receipt_detail_index;
1510
1511 l_progress := '90';
1512 inv_rcv_common_apis.do_check
1513 (p_organization_id => p_organization_id,
1514 p_inventory_item_id => p_item_id,
1515 p_transaction_type_id => 61,
1516 p_primary_quantity => l_total_primary_qty,
1517 x_return_status => l_return_status,
1518 x_msg_count => l_msg_count,
1519 x_msg_data => x_message);
1520
1521 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1522 x_status := l_return_status;
1523 END IF;
1524
1525 l_progress := '100';
1526
1527 -- Clear the Lot Rec
1528 inv_rcv_std_rcpt_apis.g_rcpt_lot_qty_rec_tb.DELETE;
1529
1530
1531 EXCEPTION
1532 WHEN fnd_api.g_exc_error THEN
1533 ROLLBACK TO crt_intship_rti_sp;
1534 x_status := fnd_api.g_ret_sts_error;
1535
1536 IF l_curs_rcpt_detail%isopen THEN
1537 CLOSE l_curs_rcpt_detail;
1538 END IF;
1539
1540 fnd_msg_pub.count_and_get
1541 (p_encoded => FND_API.g_false,
1542 p_count => l_msg_count,
1543 p_data => x_message
1544 );
1545
1546 WHEN fnd_api.g_exc_unexpected_error THEN
1547 ROLLBACK TO crt_intship_rti_sp;
1548 x_status := fnd_api.g_ret_sts_unexp_error ;
1549 IF l_curs_rcpt_detail%isopen THEN
1550 CLOSE l_curs_rcpt_detail;
1551 END IF;
1552
1553 fnd_msg_pub.count_and_get
1554 (p_encoded => FND_API.g_false,
1555 p_count => l_msg_count,
1556 p_data => x_message
1557 );
1558
1559
1560 WHEN OTHERS THEN
1561 ROLLBACK TO crt_intship_rti_sp;
1562 x_status := fnd_api.g_ret_sts_unexp_error ;
1563 IF l_curs_rcpt_detail%isopen THEN
1564 CLOSE l_curs_rcpt_detail;
1565 END IF;
1566 fnd_msg_pub.count_and_get
1567 (p_encoded => FND_API.g_false,
1568 p_count => l_msg_count,
1569 p_data => x_message
1570 );
1571
1572 END create_int_shp_dr_del_rti_rec;
1573
1574
1575
1576 PROCEDURE create_rma_drct_dlvr_rti_rec(p_move_order_header_id IN OUT NOCOPY NUMBER,
1577 p_organization_id IN NUMBER,
1578 p_oe_order_header_id IN NUMBER,
1579 p_oe_order_line_id IN NUMBER,
1580 p_item_id IN NUMBER,
1581 p_rcv_qty IN NUMBER,
1582 p_rcv_uom IN VARCHAR2,
1583 p_rcv_uom_code IN VARCHAR2,
1584 p_source_type IN VARCHAR2,
1585 p_subinventory VARCHAR2,
1586 p_locator_id NUMBER,
1587 p_transaction_temp_id IN NUMBER,
1588 p_lot_control_code IN NUMBER,
1589 p_serial_control_code IN NUMBER,
1590 p_lpn_id IN NUMBER,
1591 p_revision IN VARCHAR2,
1592 x_status OUT NOCOPY VARCHAR2,
1593 x_message OUT NOCOPY VARCHAR2,
1594 p_project_id IN NUMBER,
1595 p_task_id IN NUMBER,
1596 p_country_code IN VARCHAR2 DEFAULT NULL
1597 )
1598 IS
1599
1600 l_rcpt_match_table_detail INV_RCV_COMMON_APIS.cascaded_trans_tab_type; -- output for matching algorithm
1601
1602 l_rcv_transaction_rec gml_rcv_std_rcpt_apis.rcv_transaction_rec_tp; -- rcv_transaction block
1603
1604 l_transaction_type VARCHAR2(20) := 'DELIVER';
1605 l_total_primary_qty NUMBER := 0;
1606
1607 l_msg_count NUMBER;
1608 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
1609
1610 l_group_id NUMBER;
1611
1612 l_rcv_rcpt_rec gml_rcv_std_rcpt_apis.rcv_enter_receipts_rec_tp;
1613 l_mmtt_rec mtl_material_transactions_temp%ROWTYPE;
1614
1615 l_err_message VARCHAR2(100);
1616 l_temp_message VARCHAR2(100);
1617 l_msg_prod VARCHAR2(5);
1618
1619 l_progress VARCHAR2(10);
1620
1621 CURSOR l_curs_rcpt_detail
1622 (V_OE_ORDER_LINE_ID NUMBER)
1623 IS
1624 SELECT
1625 'N' LINE_CHKBOX,
1626 'CUSTOMER' SOURCE_TYPE_CODE,
1627 'CUSTOMER' RECEIPT_SOURCE_CODE,
1628 '' ORDER_TYPE_CODE,
1629 '' ORDER_TYPE,
1630 TO_NUMBER(NULL) PO_HEADER_ID,
1631 NULL PO_NUMBER,
1632 TO_NUMBER(NULL) PO_LINE_ID,
1633 TO_NUMBER(NULL) PO_LINE_NUMBER,
1634 TO_NUMBER(NULL) PO_LINE_LOCATION_ID,
1635 TO_NUMBER(NULL) PO_SHIPMENT_NUMBER,
1636 TO_NUMBER(NULL) PO_RELEASE_ID,
1637 TO_NUMBER(NULL) PO_RELEASE_NUMBER,
1638 TO_NUMBER(NULL) REQ_HEADER_ID,
1639 NULL REQ_NUMBER,
1640 TO_NUMBER(NULL) REQ_LINE_ID,
1641 TO_NUMBER(NULL) REQ_LINE,
1642 TO_NUMBER(NULL) REQ_DISTRIBUTION_ID,
1643 TO_NUMBER(NULL) RCV_SHIPMENT_HEADER_ID,
1644 NULL RCV_SHIPMENT_NUMBER,
1645 TO_NUMBER(NULL) RCV_SHIPMENT_LINE_ID,
1646 TO_NUMBER(NULL) RCV_LINE_NUMBER,
1647 NVL(OEL.SHIP_TO_ORG_ID,OEH.SHIP_TO_ORG_ID) FROM_ORGANIZATION_ID,
1648 NVL(OEL.SHIP_FROM_ORG_ID, OEH.SHIP_FROM_ORG_ID) TO_ORGANIZATION_ID,
1649 TO_NUMBER(NULL) VENDOR_ID,
1650 '' SOURCE,
1651 TO_NUMBER(NULL) VENDOR_SITE_ID,
1652 NULL OUTSIDE_OPERATION_FLAG,
1653 OEL.INVENTORY_ITEM_ID ITEM_ID,
1654 -- Bug 2073164
1655 NULL uom_code,
1656 MUM.UNIT_OF_MEASURE PRIMARY_UOM,
1657 MUM.UOM_CLASS PRIMARY_UOM_CLASS,
1658 NVL(MSI.ALLOWED_UNITS_LOOKUP_CODE ,2) ITEM_ALLOWED_UNITS_LOOKUP_CODE,
1659 NVL(MSI.LOCATION_CONTROL_CODE ,1) ITEM_LOCATOR_CONTROL,
1660 DECODE(MSI.RESTRICT_LOCATORS_CODE ,1 ,'Y' ,'N') RESTRICT_LOCATORS_CODE,
1661 DECODE(MSI.RESTRICT_SUBINVENTORIES_CODE ,1 ,'Y' ,'N') RESTRICT_SUBINVENTORIES_CODE,
1662 NVL(MSI.SHELF_LIFE_CODE ,1) SHELF_LIFE_CODE,
1663 NVL(MSI.SHELF_LIFE_DAYS ,0) SHELF_LIFE_DAYS,
1664 MSI.SERIAL_NUMBER_CONTROL_CODE SERIAL_NUMBER_CONTROL_CODE,
1665 MSI.LOT_CONTROL_CODE LOT_CONTROL_CODE,
1666 DECODE(MSI.REVISION_QTY_CONTROL_CODE ,1 ,'N' ,2 ,'Y' ,'N') ITEM_REV_CONTROL_FLAG_TO,
1667 NULL ITEM_REV_CONTROL_FLAG_FROM,
1668 MSI.SEGMENT1 ITEM_NUMBER,
1669 OEL.ITEM_REVISION ITEM_REVISION,
1670 MSI.DESCRIPTION ITEM_DESCRIPTION,
1671 TO_NUMBER(NULL) ITEM_CATEGORY_ID,
1672 NULL HAZARD_CLASS,
1673 NULL UN_NUMBER,
1674 NULL VENDOR_ITEM_NUMBER,
1675 OEL.SHIP_FROM_ORG_ID SHIP_TO_LOCATION_ID,
1676 '' SHIP_TO_LOCATION,
1677 NULL PACKING_SLIP,
1678 TO_NUMBER(NULL) ROUTING_ID,
1679 NULL ROUTING_NAME,
1680 OEL.REQUEST_DATE NEED_BY_DATE,
1681 NVL(OEL.PROMISE_DATE, OEL.REQUEST_DATE) EXPECTED_RECEIPT_DATE,
1682 OEL.ORDERED_QUANTITY ORDERED_QTY,
1683 '' ORDERED_UOM,
1684 NULL USSGL_TRANSACTION_CODE,
1685 NULL GOVERNMENT_CONTEXT,
1686 MSI.INSPECTION_REQUIRED_FLAG INSPECTION_REQUIRED_FLAG,
1687 'Y' RECEIPT_REQUIRED_FLAG,
1688 'N' ENFORCE_SHIP_TO_LOCATION_CODE,
1689 OEL.UNIT_SELLING_PRICE UNIT_PRICE,
1690 OEH.TRANSACTIONAL_CURR_CODE CURRENCY_CODE,
1691 OEH.CONVERSION_TYPE_CODE CURRENCY_CONVERSION_TYPE,
1692 OEH.CONVERSION_RATE_DATE CURRENCY_CONVERSION_DATE,
1693 OEH.CONVERSION_RATE CURRENCY_CONVERSION_RATE,
1694 NULL NOTE_TO_RECEIVER,
1695 NULL DESTINATION_TYPE_CODE,
1696 OEL.DELIVER_TO_CONTACT_ID DELIVER_TO_PERSON_ID,
1697 OEL.DELIVER_TO_ORG_ID DELIVER_TO_LOCATION_ID,
1698 NULL DESTINATION_SUBINVENTORY,
1699 OEL.CONTEXT ATTRIBUTE_CATEGORY,
1700 OEL.ATTRIBUTE1 ATTRIBUTE1,
1701 OEL.ATTRIBUTE2 ATTRIBUTE2,
1702 OEL.ATTRIBUTE3 ATTRIBUTE3,
1703 OEL.ATTRIBUTE4 ATTRIBUTE4,
1704 OEL.ATTRIBUTE5 ATTRIBUTE5,
1705 OEL.ATTRIBUTE6 ATTRIBUTE6,
1706 OEL.ATTRIBUTE7 ATTRIBUTE7,
1707 OEL.ATTRIBUTE8 ATTRIBUTE8,
1708 OEL.ATTRIBUTE9 ATTRIBUTE9,
1709 OEL.ATTRIBUTE10 ATTRIBUTE10,
1710 OEL.ATTRIBUTE11 ATTRIBUTE11,
1711 OEL.ATTRIBUTE12 ATTRIBUTE12,
1712 OEL.ATTRIBUTE13 ATTRIBUTE13,
1713 OEL.ATTRIBUTE14 ATTRIBUTE14,
1714 OEL.ATTRIBUTE15 ATTRIBUTE15,
1715 NULL CLOSED_CODE,
1716 NULL ASN_TYPE,
1717 NULL BILL_OF_LADING,
1718 TO_DATE(NULL) SHIPPED_DATE,
1719 NULL FREIGHT_CARRIER_CODE,
1720 NULL WAYBILL_AIRBILL_NUM,
1721 NULL FREIGHT_BILL_NUM,
1722 NULL VENDOR_LOT_NUM,
1723 NULL CONTAINER_NUM,
1724 NULL TRUCK_NUM,
1725 NULL BAR_CODE_LABEL,
1726 NULL RATE_TYPE_DISPLAY,
1727 NULL MATCH_OPTION,
1728 NULL COUNTRY_OF_ORIGIN_CODE,
1729 OEL.HEADER_ID OE_ORDER_HEADER_ID,
1730 OEH.ORDER_NUMBER OE_ORDER_NUM,
1731 OEL.LINE_ID OE_ORDER_LINE_ID,
1732 OEL.LINE_NUMBER OE_ORDER_LINE_NUM,
1733 OEL.SOLD_TO_ORG_ID CUSTOMER_ID,
1734 NVL(OEL.SHIP_TO_ORG_ID, OEH.SHIP_TO_ORG_ID) CUSTOMER_SITE_ID,
1735 '' CUSTOMER_ITEM_NUM,
1736 '' PLL_NOTE_TO_RECEIVER,
1737 NULL PO_DISTRIBUTION_ID,
1738 NULL QTY_ORDERED,
1739 NULL WIP_ENTITY_ID,
1740 NULL WIP_OPERATION_SEQ_NUM,
1741 NULL WIP_RESOURCE_SEQ_NUM,
1742 NULL WIP_REPETITIVE_SCHEDULE_ID,
1743 NULL WIP_LINE_ID,
1744 NULL BOM_RESOURCE_ID,
1745 '' DESTINATION_TYPE,
1746 '' LOCATION,
1747 NULL CURRENCY_CONVERSION_RATE_POD,
1748 NULL CURRENCY_CONVERSION_DATE_POD,
1749 NULL PROJECT_ID,
1750 NULL TASK_ID
1751 FROM
1752 OE_ORDER_LINES_all OEL,
1753 OE_ORDER_HEADERS_all OEH,
1754 MTL_SYSTEM_ITEMS MSI,
1755 MTL_UNITS_OF_MEASURE MUM
1756 WHERE OEL.LINE_CATEGORY_CODE='RETURN'
1757 AND OEL.HEADER_ID = OEH.HEADER_ID
1758 AND OEL.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
1759 AND OEL.SHIP_FROM_ORG_ID = MSI.ORGANIZATION_ID
1760 AND MSI.PRIMARY_UOM_CODE = MUM.UOM_CODE
1761 AND OEL.BOOKED_FLAG='Y'
1762 AND OEL.ORDERED_QUANTITY > NVL(OEL.SHIPPED_QUANTITY,0)
1763 AND MSI.MTL_TRANSACTIONS_ENABLED_FLAG = 'Y'
1764 AND OEL.LINE_ID = v_oe_order_line_id
1765 AND (p_project_id is null or
1766 (p_project_id = -9999 and oel.project_id is null ) or -- bug 2669021
1767 OEL.project_id = p_project_id
1768 )
1769 and ( p_task_id is null or OEL.task_id = p_task_id );
1770
1771 BEGIN
1772 x_status := fnd_api.g_ret_sts_success;
1773 l_progress := '10';
1774 SAVEPOINT crt_rma_rti_sp;
1775
1776 -- query po_startup_value
1777 Begin
1778 inv_rcv_common_apis.init_startup_values(p_organization_id);
1779 Exception
1780 when NO_DATA_FOUND then
1781 FND_MESSAGE.SET_NAME('INV', 'INV_RCV_PARAM');
1782 FND_MSG_PUB.ADD;
1783 RAISE ;
1784 End;
1785
1786 l_progress := '20';
1787 -- default l_group_id ? clear group id after done
1788 IF inv_rcv_common_apis.g_rcv_global_var.interface_group_id is NULL THEN
1789 SELECT rcv_interface_groups_s.nextval
1790 INTO l_group_id
1791 FROM dual;
1792 inv_rcv_common_apis.g_rcv_global_var.interface_group_id := l_group_id;
1793 ELSE
1794 l_group_id := inv_rcv_common_apis.g_rcv_global_var.interface_group_id;
1795 END IF;
1796
1797 -- initialize input record for matching algorithm
1798 inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross(inv_rcv_std_rcpt_apis.g_receipt_detail_index).transaction_type := 'DELIVER';
1799 inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross(inv_rcv_std_rcpt_apis.g_receipt_detail_index).quantity := p_rcv_qty;
1800 inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross(inv_rcv_std_rcpt_apis.g_receipt_detail_index).unit_of_measure := p_rcv_uom;
1801 inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross(inv_rcv_std_rcpt_apis.g_receipt_detail_index).group_id := l_group_id;
1802 inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross(inv_rcv_std_rcpt_apis.g_receipt_detail_index).item_id := p_item_id;
1803 inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross(inv_rcv_std_rcpt_apis.g_receipt_detail_index).to_organization_id := p_organization_id;
1804 inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross(inv_rcv_std_rcpt_apis.g_receipt_detail_index).oe_order_header_id := p_oe_order_header_id;
1805 -- line id will be passed only from the putaway api.
1806 inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross(inv_rcv_std_rcpt_apis.g_receipt_detail_index).oe_order_line_id := p_oe_order_line_id;
1807 inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross(inv_rcv_std_rcpt_apis.g_receipt_detail_index).project_id := p_project_id; --bug# 2794612
1808 inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross(inv_rcv_std_rcpt_apis.g_receipt_detail_index).task_id := p_task_id; --bug# 2794612
1809
1810 BEGIN
1811 select primary_unit_of_measure
1812 into inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross(inv_rcv_std_rcpt_apis.g_receipt_detail_index).primary_unit_of_measure
1813 from mtl_system_items
1814 where mtl_system_items.inventory_item_id = p_item_id
1815 and mtl_system_items.organization_id = p_organization_id;
1816 EXCEPTION
1817 when no_data_found then
1818 NULL;
1819 END;
1820
1821 inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross(inv_rcv_std_rcpt_apis.g_receipt_detail_index).expected_receipt_date := Sysdate; --?
1822 inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross(inv_rcv_std_rcpt_apis.g_receipt_detail_index).tax_amount := 0; -- ?
1823 inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross(inv_rcv_std_rcpt_apis.g_receipt_detail_index).error_status := 'S'; -- ?
1824
1825 l_progress := '40';
1826 inv_rcv_txn_match.matching_logic
1827 (x_return_status => x_status, --?
1828 x_msg_count => l_msg_count,
1829 x_msg_data => x_message,
1830 x_cascaded_table => inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross,
1831 n => inv_rcv_std_rcpt_apis.g_receipt_detail_index,
1832 temp_cascaded_table => l_rcpt_match_table_detail,
1833 p_receipt_num => NULL,
1834 p_match_type => 'RMA',
1835 p_lpn_id => NULL
1836 );
1837
1838 -- x_status is not successful if there is any execution error in matching.
1839 IF x_status = fnd_api.g_ret_sts_error THEN
1840 FND_MESSAGE.SET_NAME('INV', 'INV_RCV_MATCH_ERROR');
1841 FND_MSG_PUB.ADD;
1842 RAISE fnd_api.g_exc_error;
1843 END IF;
1844
1845 IF x_status = fnd_api.g_ret_sts_unexp_error THEN
1846 FND_MESSAGE.SET_NAME('INV', 'INV_RCV_MATCH_ERROR');
1847 FND_MSG_PUB.ADD;
1848 RAISE fnd_api.g_exc_unexpected_error;
1849 END IF;
1850
1851 IF inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross(inv_rcv_std_rcpt_apis.g_receipt_detail_index).error_status = 'E' THEN
1852 l_err_message := inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross(inv_rcv_std_rcpt_apis.g_receipt_detail_index).error_message;
1853 FND_MESSAGE.SET_NAME('INV', l_err_message);
1854 FND_MSG_PUB.ADD;
1855 RAISE fnd_api.g_exc_error;
1856 END IF;
1857
1858 l_err_message := '@@@';
1859 FOR i IN inv_rcv_std_rcpt_apis.g_receipt_detail_index..(inv_rcv_std_rcpt_apis.g_receipt_detail_index + l_rcpt_match_table_detail.COUNT - 1) LOOP
1860 IF l_rcpt_match_table_detail(i-inv_rcv_std_rcpt_apis.g_receipt_detail_index+1).error_status = 'W' THEN
1861 x_status := 'W';
1862
1863 l_temp_message := l_rcpt_match_table_detail(i-inv_rcv_std_rcpt_apis.g_receipt_detail_index+1).error_message;
1864 IF l_temp_message IS NULL THEN
1865 l_err_message := 'INV_RCV_GEN_TOLERANCE_EXCEED';
1866 l_msg_prod := 'INV';
1867 EXIT;
1868 END IF;
1869 IF l_err_message = '@@@' THEN
1870 l_err_message := l_temp_message;
1871 l_msg_prod := 'INV';
1872 ELSIF l_temp_message <> l_err_message THEN
1873 l_msg_prod := 'INV';
1874 l_err_message := 'INV_RCV_GEN_TOLERANCE_EXCEED';
1875 EXIT;
1876 END IF;
1877 END IF;
1878 END LOOP;
1879
1880 IF l_err_message <> '@@@' THEN
1881 FND_MESSAGE.SET_NAME(l_msg_prod, l_err_message);
1882 FND_MSG_PUB.ADD;
1883 END IF;
1884
1885 -- based on return from matching algorithm,
1886 -- determine which line in rcv_transaction block to be inserted into RTI
1887
1888
1889 -- loop through results returned by matching algorithm
1890 l_progress := '60';
1891 FOR match_result_count IN 1..l_rcpt_match_table_detail.COUNT LOOP
1892 l_progress := '62';
1893 OPEN l_curs_rcpt_detail(l_rcpt_match_table_detail(match_result_count).oe_order_line_id);
1894 l_progress := '64';
1895 FETCH l_curs_rcpt_detail INTO l_rcv_rcpt_rec;
1896 l_progress := '66';
1897 CLOSE l_curs_rcpt_detail;
1898 l_progress := '68';
1899
1900 l_rcv_transaction_rec.oe_order_line_id := l_rcpt_match_table_detail(match_result_count).oe_order_line_id;
1901
1902
1903 -- update following fields from matching algorithm return value
1904 l_rcv_transaction_rec.transaction_qty := l_rcpt_match_table_detail(match_result_count).quantity;
1905 l_rcv_transaction_rec.transaction_uom := l_rcpt_match_table_detail(match_result_count).unit_of_measure;
1906 --Bug 2073164
1907 l_rcv_rcpt_rec.uom_code := p_rcv_uom_code;
1908 l_rcv_transaction_rec.primary_quantity := l_rcpt_match_table_detail(match_result_count).primary_quantity;
1909 l_rcv_transaction_rec.primary_uom := l_rcpt_match_table_detail(match_result_count).primary_unit_of_measure;
1910 l_total_primary_qty := l_total_primary_qty + l_rcv_transaction_rec.primary_quantity;
1911
1912
1913 l_progress := '70';
1914
1915 l_rcv_transaction_rec.lpn_id := p_lpn_id;
1916 l_rcv_transaction_rec.transfer_lpn_id := p_lpn_id;
1917
1918 IF p_country_code IS NOT NULL THEN
1919 l_rcv_rcpt_rec.COUNTRY_OF_ORIGIN_CODE := p_country_code;
1920 END IF;
1921
1922 populate_default_values(p_rcv_transaction_rec => l_rcv_transaction_rec,
1923 p_rcv_rcpt_rec => l_rcv_rcpt_rec,
1924 p_group_id => l_group_id,
1925 p_organization_id => p_organization_id,
1926 p_item_id => p_item_id,
1927 p_revision => p_revision,
1928 p_source_type => p_source_type,
1929 p_subinventory_code => p_subinventory,
1930 p_locator_id => p_locator_id,
1931 p_transaction_temp_id => p_transaction_temp_id,
1932 p_lot_control_code => p_lot_control_code,
1933 p_serial_control_code => p_serial_control_code);
1934 l_progress := '80';
1935
1936 END LOOP;
1937 -- append index in input table where the line to be detailed needs to be inserted
1938 --inv_rcv_std_rcpt_apis.g_receipt_detail_index := l_rcpt_match_table_detail.COUNT + inv_rcv_std_rcpt_apis.g_receipt_detail_index;
1939
1940 l_progress := '90';
1941 inv_rcv_common_apis.do_check
1942 (p_organization_id => p_organization_id,
1943 p_inventory_item_id => p_item_id,
1944 p_transaction_type_id => 15,
1945 p_primary_quantity => l_total_primary_qty,
1946 x_return_status => l_return_status,
1947 x_msg_count => l_msg_count,
1948 x_msg_data => x_message);
1949
1950 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1951 x_status := l_return_status;
1952 END IF;
1953
1954 l_progress := '100';
1955
1956 -- Clear the Lot Rec
1957 inv_rcv_std_rcpt_apis.g_rcpt_lot_qty_rec_tb.DELETE;
1958
1959
1960 EXCEPTION
1961 WHEN fnd_api.g_exc_error THEN
1962 ROLLBACK TO crt_rma_rti_sp;
1963 x_status := fnd_api.g_ret_sts_error;
1964
1965 IF l_curs_rcpt_detail%isopen THEN
1966 CLOSE l_curs_rcpt_detail;
1967 END IF;
1968
1969 fnd_msg_pub.count_and_get
1970 (p_encoded => FND_API.g_false,
1971 p_count => l_msg_count,
1972 p_data => x_message
1973 );
1974
1975 WHEN fnd_api.g_exc_unexpected_error THEN
1976 ROLLBACK TO crt_rma_rti_sp;
1977 x_status := fnd_api.g_ret_sts_unexp_error ;
1978 IF l_curs_rcpt_detail%isopen THEN
1979 CLOSE l_curs_rcpt_detail;
1980 END IF;
1981
1982 fnd_msg_pub.count_and_get
1983 (p_encoded => FND_API.g_false,
1984 p_count => l_msg_count,
1985 p_data => x_message
1986 );
1987
1988 WHEN OTHERS THEN
1989 ROLLBACK TO crt_rma_rti_sp;
1990 x_status := fnd_api.g_ret_sts_unexp_error ;
1991 IF l_curs_rcpt_detail%isopen THEN
1992 CLOSE l_curs_rcpt_detail;
1993 END IF;
1994 fnd_msg_pub.count_and_get
1995 (p_encoded => FND_API.g_false,
1996 p_count => l_msg_count,
1997 p_data => x_message
1998 );
1999
2000 END create_rma_drct_dlvr_rti_rec;
2001
2002
2003 PROCEDURE create_asn_con_dd_intf_rec
2004 (p_move_order_header_id IN OUT NOCOPY NUMBER,
2005 p_organization_id IN NUMBER,
2006 p_shipment_header_id IN NUMBER,
2007 p_po_header_id IN NUMBER,
2008 p_item_id IN NUMBER,
2009 p_rcv_qty IN NUMBER,
2010 p_rcv_uom IN VARCHAR2,
2011 p_rcv_uom_code IN VARCHAR2,
2012 p_source_type IN VARCHAR2,
2013 p_subinventory VARCHAR2,
2014 p_locator_id NUMBER,
2015 p_lpn_id IN NUMBER,
2016 p_lot_control_code IN NUMBER,
2017 p_serial_control_code IN NUMBER,
2018 p_revision IN VARCHAR2,
2019 p_transaction_temp_id IN NUMBER,
2020 x_status OUT NOCOPY VARCHAR2,
2021 x_message OUT NOCOPY VARCHAR2,
2022 p_project_id IN NUMBER,
2023 p_task_id IN NUMBER,
2024 p_country_code IN VARCHAR2 DEFAULT NULL,
2025 p_item_desc IN VARCHAR2 DEFAULT NULL
2026 )
2027 IS
2028 l_rcpt_match_table_detail INV_RCV_COMMON_APIS.cascaded_trans_tab_type; -- output for matching algorithm
2029
2030 l_rcv_transaction_rec gml_rcv_std_rcpt_apis.rcv_transaction_rec_tp; -- rcv_transaction block
2031
2032 l_transaction_type VARCHAR2(20) := 'DELIVER';
2033 l_total_primary_qty NUMBER := 0;
2034 l_match_type VARCHAR2(20);
2035
2036 l_msg_count NUMBER;
2037 l_msg_data VARCHAR2(400);
2038 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
2039
2040 l_group_id NUMBER;
2041
2042 l_rcv_rcpt_rec gml_rcv_std_rcpt_apis.rcv_enter_receipts_rec_tp;
2043 l_mmtt_rec mtl_material_transactions_temp%ROWTYPE;
2044
2045 l_err_message VARCHAR2(100);
2046 l_temp_message VARCHAR2(100);
2047 l_msg_prod VARCHAR2(5);
2048
2049 l_progress VARCHAR2(10);
2050 l_receipt_num VARCHAR2(30);
2051
2052 CURSOR l_curs_rcpt_detail
2053 (v_shipment_line_id NUMBER,
2054 v_po_distribution_id NUMBER)
2055 IS
2056 SELECT
2057 'N' LINE_CHKBOX,
2058 'ASN' SOURCE_TYPE_CODE,
2059 'VENDOR' RECEIPT_SOURCE_CODE,
2060 'PO' ORDER_TYPE_CODE,
2061 '' ORDER_TYPE,
2062 POLL.PO_HEADER_ID PO_HEADER_ID,
2063 POH.SEGMENT1 PO_NUMBER,
2064 POLL.PO_LINE_ID PO_LINE_ID,
2065 POL.LINE_NUM PO_LINE_NUMBER,
2066 POLL.LINE_LOCATION_ID PO_LINE_LOCATION_ID,
2067 POLL.SHIPMENT_NUM PO_SHIPMENT_NUMBER,
2068 POLL.PO_RELEASE_ID PO_RELEASE_ID,
2069 POR.RELEASE_NUM PO_RELEASE_NUMBER,
2070 TO_NUMBER(NULL) REQ_HEADER_ID,
2071 NULL REQ_NUMBER,
2072 TO_NUMBER(NULL) REQ_LINE_ID,
2073 TO_NUMBER(NULL) REQ_LINE,
2074 TO_NUMBER(NULL) REQ_DISTRIBUTION_ID,
2075 RSH.SHIPMENT_HEADER_ID RCV_SHIPMENT_HEADER_ID,
2076 RSH.SHIPMENT_NUM RCV_SHIPMENT_NUMBER,
2077 RSL.SHIPMENT_LINE_ID RCV_SHIPMENT_LINE_ID,
2078 RSL.LINE_NUM RCV_LINE_NUMBER,
2079 NVL(RSL.FROM_ORGANIZATION_ID,POH.PO_HEADER_ID) FROM_ORGANIZATION_ID,
2080 RSL.TO_ORGANIZATION_ID TO_ORGANIZATION_ID,
2081 RSH.VENDOR_ID VENDOR_ID,
2082 '' SOURCE,
2083 RSH.VENDOR_SITE_ID VENDOR_SITE_ID,
2084 '' OUTSIDE_OPERATION_FLAG,
2085 RSL.ITEM_ID ITEM_ID,
2086 -- Bug 2073164
2087 NULL uom_code,
2088 RSL.UNIT_OF_MEASURE PRIMARY_UOM,
2089 MUM.UOM_CLASS PRIMARY_UOM_CLASS,
2090 NVL(MSI.ALLOWED_UNITS_LOOKUP_CODE,2) ITEM_ALLOWED_UNITS_LOOKUP_CODE,
2091 NVL(MSI.LOCATION_CONTROL_CODE,1) ITEM_LOCATOR_CONTROL,
2092 DECODE(MSI.RESTRICT_LOCATORS_CODE,1,'Y', 'N') RESTRICT_LOCATORS_CODE,
2093 DECODE(MSI.RESTRICT_SUBINVENTORIES_CODE,1,'Y','N') RESTRICT_SUBINVENTORIES_CODE,
2094 NVL(MSI.SHELF_LIFE_CODE,1) SHELF_LIFE_CODE,
2095 NVL(MSI.SHELF_LIFE_DAYS,0) SHELF_LIFE_DAYS,
2096 MSI.SERIAL_NUMBER_CONTROL_CODE SERIAL_NUMBER_CONTROL_CODE,
2097 MSI.LOT_CONTROL_CODE LOT_CONTROL_CODE,
2098 DECODE(MSI.REVISION_QTY_CONTROL_CODE,1,'N',2,'Y','N') ITEM_REV_CONTROL_FLAG_TO,
2099 NULL ITEM_REV_CONTROL_FLAG_FROM,
2100 NULL ITEM_NUMBER,
2101 RSL.ITEM_REVISION ITEM_REVISION,
2102 RSL.ITEM_DESCRIPTION ITEM_DESCRIPTION,
2103 RSL.CATEGORY_ID ITEM_CATEGORY_ID,
2104 '' HAZARD_CLASS,
2105 '' UN_NUMBER,
2106 RSL.VENDOR_ITEM_NUM VENDOR_ITEM_NUMBER,
2107 RSL.SHIP_TO_LOCATION_ID SHIP_TO_LOCATION_ID,
2108 '' SHIP_TO_LOCATION,
2109 RSL.PACKING_SLIP PACKING_SLIP,
2110 RSL.ROUTING_HEADER_ID ROUTING_ID,
2111 '' ROUTING_NAME,
2112 POLL.NEED_BY_DATE NEED_BY_DATE,
2113 RSH.EXPECTED_RECEIPT_DATE EXPECTED_RECEIPT_DATE,
2114 POLL.QUANTITY ORDERED_QTY,
2115 POL.UNIT_MEAS_LOOKUP_CODE ORDERED_UOM,
2116 RSL.USSGL_TRANSACTION_CODE USSGL_TRANSACTION_CODE,
2117 RSL.GOVERNMENT_CONTEXT GOVERNMENT_CONTEXT,
2118 POLL.INSPECTION_REQUIRED_FLAG INSPECTION_REQUIRED_FLAG,
2119 POLL.RECEIPT_REQUIRED_FLAG RECEIPT_REQUIRED_FLAG,
2120 POLL.ENFORCE_SHIP_TO_LOCATION_CODE ENFORCE_SHIP_TO_LOCATION_CODE,
2121 NVL(POLL.PRICE_OVERRIDE,POL.UNIT_PRICE) UNIT_PRICE,
2122 POH.CURRENCY_CODE CURRENCY_CODE,
2123 POH.RATE_TYPE CURRENCY_CONVERSION_TYPE,
2124 POH.RATE_DATE CURRENCY_CONVERSION_DATE,
2125 POH.RATE CURRENCY_CONVERSION_RATE,
2126 POH.NOTE_TO_RECEIVER NOTE_TO_RECEIVER,
2127 POD.DESTINATION_TYPE_CODE DESTINATION_TYPE_CODE,
2128 POD.DELIVER_TO_PERSON_ID DELIVER_TO_PERSON_ID,
2129 POD.DELIVER_TO_LOCATION_ID DELIVER_TO_LOCATION_ID,
2130 POD.DESTINATION_SUBINVENTORY DESTINATION_SUBINVENTORY,
2131 RSL.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY,
2132 RSL.ATTRIBUTE1 ATTRIBUTE1,
2133 RSL.ATTRIBUTE2 ATTRIBUTE2,
2134 RSL.ATTRIBUTE3 ATTRIBUTE3,
2135 RSL.ATTRIBUTE4 ATTRIBUTE4,
2136 RSL.ATTRIBUTE5 ATTRIBUTE5,
2137 RSL.ATTRIBUTE6 ATTRIBUTE6,
2138 RSL.ATTRIBUTE7 ATTRIBUTE7,
2139 RSL.ATTRIBUTE8 ATTRIBUTE8,
2140 RSL.ATTRIBUTE9 ATTRIBUTE9,
2141 RSL.ATTRIBUTE10 ATTRIBUTE10,
2142 RSL.ATTRIBUTE11 ATTRIBUTE11,
2143 RSL.ATTRIBUTE12 ATTRIBUTE12,
2144 RSL.ATTRIBUTE13 ATTRIBUTE13,
2145 RSL.ATTRIBUTE14 ATTRIBUTE14,
2146 RSL.ATTRIBUTE15 ATTRIBUTE15,
2147 POLL.CLOSED_CODE CLOSED_CODE,
2148 RSH.ASN_TYPE ASN_TYPE,
2149 RSH.BILL_OF_LADING BILL_OF_LADING,
2150 RSH.SHIPPED_DATE SHIPPED_DATE,
2151 RSH.FREIGHT_CARRIER_CODE FREIGHT_CARRIER_CODE,
2152 RSH.WAYBILL_AIRBILL_NUM WAYBILL_AIRBILL_NUM,
2153 RSH.FREIGHT_BILL_NUMBER FREIGHT_BILL_NUM,
2154 RSL.VENDOR_LOT_NUM VENDOR_LOT_NUM,
2155 RSL.CONTAINER_NUM CONTAINER_NUM,
2156 RSL.TRUCK_NUM TRUCK_NUM,
2157 RSL.BAR_CODE_LABEL BAR_CODE_LABEL,
2158 '' RATE_TYPE_DISPLAY,
2159 POLL.MATCH_OPTION MATCH_OPTION,
2160 RSL.COUNTRY_OF_ORIGIN_CODE COUNTRY_OF_ORIGIN_CODE,
2161 TO_NUMBER(NULL) OE_ORDER_HEADER_ID,
2162 TO_NUMBER(NULL) OE_ORDER_NUM,
2163 TO_NUMBER(NULL) OE_ORDER_LINE_ID,
2164 TO_NUMBER(NULL) OE_ORDER_LINE_NUM,
2165 TO_NUMBER(NULL) CUSTOMER_ID,
2166 TO_NUMBER(NULL) CUSTOMER_SITE_ID,
2167 null CUSTOMER_ITEM_NUM,
2168 NULL pll_note_to_receiver,
2169 --POLL.NOTE_TO_RECEIVER PLL_NOTE_TO_RECEIVER,
2170 pod.po_distribution_id PO_DISTRIBUTION_ID,
2171 pod.quantity_ordered - pod.quantity_delivered QTY_ORDERED,
2172 pod.wip_entity_id WIP_ENTITY_ID,
2173 pod.wip_operation_seq_num WIP_OPERATION_SEQ_NUM,
2174 pod.wip_resource_seq_num WIP_RESOURCE_SEQ_NUM,
2175 pod.wip_repetitive_schedule_id WIP_REPETITIVE_SCHEDULE_ID,
2176 pod.wip_line_id WIP_LINE_ID,
2177 pod.bom_resource_id BOM_RESOURCE_ID,
2178 '' DESTINATION_TYPE,
2179 '' LOCATION,
2180 pod.rate CURRENCY_CONVERSION_RATE_POD,
2181 pod.rate_date CURRENCY_CONVERSION_DATE_POD,
2182 pod.project_id PROJECT_ID,
2183 pod.task_id TASK_ID
2184 FROM
2185 RCV_SHIPMENT_LINES RSL,
2186 RCV_SHIPMENT_HEADERS RSH,
2187 PO_HEADERS POH,
2188 PO_LINE_LOCATIONS POLL,
2189 PO_LINES POL,
2190 PO_RELEASES POR,
2191 MTL_SYSTEM_ITEMS MSI,
2192 MTL_UNITS_OF_MEASURE MUM,
2193 PO_DISTRIBUTIONS POD
2194 WHERE
2195 POD.PO_DISTRIBUTION_ID = v_po_distribution_id
2196 AND POD.LINE_LOCATION_ID = POLL.LINE_LOCATION_ID
2197 AND NVL(POLL.APPROVED_FLAG,'N') = 'Y'
2198 AND NVL(POLL.CANCEL_FLAG,'N') = 'N'
2199 AND NVL(POLL.CLOSED_CODE,'OPEN') <> 'FINALLY CLOSED'
2200 AND POLL.SHIPMENT_TYPE IN ('STANDARD','BLANKET','SCHEDULED')
2201 AND POH.PO_HEADER_ID = POLL.PO_HEADER_ID
2202 AND POL.PO_LINE_ID = POLL.PO_LINE_ID
2203 AND POLL.PO_RELEASE_ID = POR.PO_RELEASE_ID (+)
2204 AND MUM.UNIT_OF_MEASURE (+) = RSL.UNIT_OF_MEASURE
2205 AND NVL(MSI.ORGANIZATION_ID,RSL.TO_ORGANIZATION_ID) = RSL.TO_ORGANIZATION_ID
2206 AND MSI.INVENTORY_ITEM_ID (+) = RSL.ITEM_ID
2207 AND POLL.LINE_LOCATION_ID = RSL.PO_LINE_LOCATION_ID
2208 AND RSL.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
2209 AND RSH.ASN_TYPE IN ('ASN','ASBN')
2210 AND RSL.SHIPMENT_LINE_STATUS_CODE <> 'CANCELLED'
2211 AND rsl.shipment_line_id = v_shipment_line_id
2212 and (p_project_id is null or
2213 ( p_project_id = -9999 and pod.project_id is null) or -- bug 2669021
2214 pod.project_id = p_project_id
2215 )
2216 and (p_task_id is null or pod.task_id = p_task_id)
2217 UNION
2218 SELECT
2219 'N' LINE_CHKBOX,
2220 'INTERNAL' SOURCE_TYPE_CODE,
2221 DECODE(RSL.SOURCE_DOCUMENT_CODE,'INVENTORY','INVENTORY','REQ','INTERNAL ORDER') RECEIPT_SOURCE_CODE,
2222 RSL.SOURCE_DOCUMENT_CODE ORDER_TYPE_CODE,
2223 '' ORDER_TYPE,
2224 RSH.SHIPMENT_HEADER_ID PO_HEADER_ID,
2225 RSH.SHIPMENT_NUM PO_NUMBER,
2226 RSL.SHIPMENT_LINE_ID PO_LINE_ID,
2227 RSL.LINE_NUM PO_LINE_NUMBER,
2228 RSL.SHIPMENT_LINE_ID PO_LINE_LOCATION_ID,
2229 RSL.LINE_NUM PO_SHIPMENT_NUMBER,
2230 RSH.SHIPMENT_HEADER_ID PO_RELEASE_ID,
2231 RSH.SHIPMENT_HEADER_ID PO_RELEASE_NUMBER,
2232 PORH.REQUISITION_HEADER_ID REQ_HEADER_ID,
2233 PORH.SEGMENT1 REQ_NUMBER,
2234 PORL.REQUISITION_LINE_ID REQ_LINE_ID,
2235 PORL.LINE_NUM REQ_LINE,
2236 RSL.REQ_DISTRIBUTION_ID REQ_DISTRIBUTION_ID,
2237 RSL.SHIPMENT_HEADER_ID RCV_SHIPMENT_HEADER_ID,
2238 RSH.SHIPMENT_NUM RCV_SHIPMENT_NUMBER,
2239 RSL.SHIPMENT_LINE_ID RCV_SHIPMENT_LINE_ID,
2240 RSL.LINE_NUM RCV_LINE_NUMBER,
2241 RSL.FROM_ORGANIZATION_ID FROM_ORGANIZATION_ID,
2242 RSL.TO_ORGANIZATION_ID TO_ORGANIZATION_ID,
2243 RSL.SHIPMENT_LINE_ID VENDOR_ID,
2244 '' SOURCE,
2245 TO_NUMBER(NULL) VENDOR_SITE_ID,
2246 'N' OUTSIDE_OPERATION_FLAG,
2247 RSL.ITEM_ID ITEM_ID,
2248 -- Bug 2073164
2249 NULL uom_code,
2250 RSL.UNIT_OF_MEASURE PRIMARY_UOM,
2251 MUM.UOM_CLASS PRIMARY_UOM_CLASS,
2252 NVL(MSI.ALLOWED_UNITS_LOOKUP_CODE,2) ITEM_ALLOWED_UNITS_LOOKUP_CODE,
2253 NVL(MSI.LOCATION_CONTROL_CODE,1) ITEM_LOCATOR_CONTROL,
2254 DECODE(MSI.RESTRICT_LOCATORS_CODE,1,'Y','N') RESTRICT_LOCATORS_CODE,
2255 DECODE(MSI.RESTRICT_SUBINVENTORIES_CODE,1,'Y','N') RESTRICT_SUBINVENTORIES_CODE,
2256 NVL(MSI.SHELF_LIFE_CODE,1) SHELF_LIFE_CODE,
2257 NVL(MSI.SHELF_LIFE_DAYS,0) SHELF_LIFE_DAYS,
2258 MSI.SERIAL_NUMBER_CONTROL_CODE SERIAL_NUMBER_CONTROL_CODE,
2259 MSI.LOT_CONTROL_CODE LOT_CONTROL_CODE,
2260 DECODE(MSI.REVISION_QTY_CONTROL_CODE,1,'N',2,'Y','N') ITEM_REV_CONTROL_FLAG_TO,
2261 DECODE(MSI1.REVISION_QTY_CONTROL_CODE, 1,'N',2,'Y','N') ITEM_REV_CONTROL_FLAG_FROM,
2262 NULL ITEM_NUMBER,
2263 RSL.ITEM_REVISION ITEM_REVISION,
2264 RSL.ITEM_DESCRIPTION ITEM_DESCRIPTION,
2265 RSL.CATEGORY_ID ITEM_CATEGORY_ID,
2266 '' HAZARD_CLASS,
2267 '' UN_NUMBER,
2268 RSL.VENDOR_ITEM_NUM VENDOR_ITEM_NUMBER,
2269 RSH.SHIP_TO_LOCATION_ID SHIP_TO_LOCATION_ID,
2270 '' SHIP_TO_LOCATION,
2271 RSH.PACKING_SLIP PACKING_SLIP,
2272 RSL.ROUTING_HEADER_ID ROUTING_ID,
2273 '' ROUTING_NAME,
2274 PORL.NEED_BY_DATE NEED_BY_DATE,
2275 RSH.EXPECTED_RECEIPT_DATE EXPECTED_RECEIPT_DATE,
2276 RSL.QUANTITY_SHIPPED ORDERED_QTY,
2277 RSL.PRIMARY_UNIT_OF_MEASURE ORDERED_UOM,
2278 RSH.USSGL_TRANSACTION_CODE USSGL_TRANSACTION_CODE,
2279 RSH.GOVERNMENT_CONTEXT GOVERNMENT_CONTEXT,
2280 NULL INSPECTION_REQUIRED_FLAG,
2281 NULL RECEIPT_REQUIRED_FLAG,
2282 NULL ENFORCE_SHIP_TO_LOCATION_CODE,
2283 TO_NUMBER(NULL) UNIT_PRICE,
2284 NULL CURRENCY_CODE,
2285 NULL CURRENCY_CONVERSION_TYPE,
2286 TO_DATE(NULL) CURRENCY_CONVERSION_DATE,
2287 TO_NUMBER(NULL) CURRENCY_CONVERSION_RATE,
2288 NULL note_to_receiver,
2289 --PORL.NOTE_TO_RECEIVER NOTE_TO_RECEIVER,
2290 RSL.DESTINATION_TYPE_CODE DESTINATION_TYPE_CODE,
2291 RSL.DELIVER_TO_PERSON_ID DELIVER_TO_PERSON_ID,
2292 RSL.DELIVER_TO_LOCATION_ID DELIVER_TO_LOCATION_ID,
2293 RSL.TO_SUBINVENTORY DESTINATION_SUBINVENTORY,
2294 RSL.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY,
2295 RSL.ATTRIBUTE1 ATTRIBUTE1,
2296 RSL.ATTRIBUTE2 ATTRIBUTE2,
2297 RSL.ATTRIBUTE3 ATTRIBUTE3,
2298 RSL.ATTRIBUTE4 ATTRIBUTE4,
2299 RSL.ATTRIBUTE5 ATTRIBUTE5,
2300 RSL.ATTRIBUTE6 ATTRIBUTE6,
2301 RSL.ATTRIBUTE7 ATTRIBUTE7,
2302 RSL.ATTRIBUTE8 ATTRIBUTE8,
2303 RSL.ATTRIBUTE9 ATTRIBUTE9,
2304 RSL.ATTRIBUTE10 ATTRIBUTE10,
2305 RSL.ATTRIBUTE11 ATTRIBUTE11,
2306 RSL.ATTRIBUTE12 ATTRIBUTE12,
2307 RSL.ATTRIBUTE13 ATTRIBUTE13,
2308 RSL.ATTRIBUTE14 ATTRIBUTE14,
2309 RSL.ATTRIBUTE15 ATTRIBUTE15,
2310 'OPEN' CLOSED_CODE,
2311 NULL ASN_TYPE,
2312 RSH.BILL_OF_LADING BILL_OF_LADING,
2313 RSH.SHIPPED_DATE SHIPPED_DATE,
2314 RSH.FREIGHT_CARRIER_CODE FREIGHT_CARRIER_CODE,
2315 RSH.WAYBILL_AIRBILL_NUM WAYBILL_AIRBILL_NUM,
2316 RSH.FREIGHT_BILL_NUMBER FREIGHT_BILL_NUM,
2317 RSL.VENDOR_LOT_NUM VENDOR_LOT_NUM,
2318 RSL.CONTAINER_NUM CONTAINER_NUM,
2319 RSL.TRUCK_NUM TRUCK_NUM,
2320 RSL.BAR_CODE_LABEL BAR_CODE_LABEL,
2321 NULL RATE_TYPE_DISPLAY,
2322 'P' MATCH_OPTION,
2323 NULL COUNTRY_OF_ORIGIN_CODE,
2324 TO_NUMBER(NULL) OE_ORDER_HEADER_ID,
2325 TO_NUMBER(NULL) OE_ORDER_NUM,
2326 TO_NUMBER(NULL) OE_ORDER_LINE_ID,
2327 TO_NUMBER(NULL) OE_ORDER_LINE_NUM,
2328 TO_NUMBER(NULL) CUSTOMER_ID,
2329 TO_NUMBER(NULL) CUSTOMER_SITE_ID,
2330 NULL CUSTOMER_ITEM_NUM,
2331 NULL pll_note_to_receiver,
2332 --PORL.NOTE_TO_RECEIVER PLL_NOTE_TO_RECEIVER,
2333 TO_NUMBER(NULL) PO_DISTRIBUTION_ID,
2334 TO_NUMBER(NULL) QTY_ORDERED,
2335 TO_NUMBER(NULL) WIP_ENTITY_ID,
2336 TO_NUMBER(NULL) WIP_OPERATION_SEQ_NUM,
2337 TO_NUMBER(NULL) WIP_RESOURCE_SEQ_NUM,
2338 TO_NUMBER(NULL) WIP_REPETITIVE_SCHEDULE_ID,
2339 TO_NUMBER(NULL) WIP_LINE_ID,
2340 TO_NUMBER(NULL) BOM_RESOURCE_ID,
2341 '' DESTINATION_TYPE,
2342 '' LOCATION,
2343 TO_NUMBER(NULL) CURRENCY_CONVERSION_RATE_POD,
2344 TO_DATE(NULL) CURRENCY_CONVERSION_DATE_POD,
2345 TO_NUMBER(NULL) PROJECT_ID,
2346 TO_NUMBER(NULL) TASK_ID
2347 FROM
2348 RCV_SHIPMENT_HEADERS RSH,
2349 RCV_SHIPMENT_LINES RSL,
2350 PO_REQUISITION_HEADERS PORH,
2351 PO_REQUISITION_LINES PORL,
2352 MTL_SYSTEM_ITEMS MSI,
2353 MTL_SYSTEM_ITEMS MSI1,
2354 MTL_UNITS_OF_MEASURE MUM
2355 WHERE
2356 RSH.RECEIPT_SOURCE_CODE <> 'VENDOR'
2357 AND RSL.REQUISITION_LINE_ID = PORL.REQUISITION_LINE_ID(+)
2358 AND PORL.REQUISITION_HEADER_ID = PORH.REQUISITION_HEADER_ID(+)
2359 AND RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID
2360 AND MUM.UNIT_OF_MEASURE (+) = RSL.UNIT_OF_MEASURE
2361 AND MSI.ORGANIZATION_ID (+) = RSL.TO_ORGANIZATION_ID
2362 AND MSI.INVENTORY_ITEM_ID (+) = RSL.ITEM_ID
2363 AND MSI1.ORGANIZATION_ID (+) = RSL.FROM_ORGANIZATION_ID
2364 AND MSI1.INVENTORY_ITEM_ID (+) = RSL.ITEM_ID
2365 AND RSH.ASN_TYPE IS NULL
2366 AND RSL.SHIPMENT_LINE_ID = v_shipment_line_id
2367 AND (( rsl.source_document_code = 'REQ' and
2368 exists
2369 (select '1'
2370 from po_req_distributions_all prd
2371 where (p_project_id is null or
2372 (p_project_id = -9999 and prd.project_id is null) or -- bug 2669021
2373 prd.project_id = p_project_id
2374 )
2375 and (p_task_id is null or prd.task_id = p_task_id)
2376 )
2377 )or rsl.source_document_code <> 'REQ'
2378 );
2379
2380 BEGIN
2381
2382 SAVEPOINT crt_asn_con_rti_sp;
2383 x_status := FND_API.G_RET_STS_SUCCESS;
2384 l_progress := '10';
2385
2386 -- query po_startup_value
2387 Begin
2388 /* Bug 2516729
2389 * Fetch rcv_shipment_headers.receipt_number for the given shipment_header_id.
2390 * If it exists , assign it to the global variable for receipt # (g_rcv_global_var.receipt_num)
2391 * in order that a new receipt # is not created everytime and the existing receipt # is used
2392 */
2393 BEGIN
2394 SELECT receipt_num
2395 INTO l_receipt_num
2396 FROM rcv_shipment_headers
2397 WHERE shipment_header_id = p_shipment_header_id
2398 AND ship_to_org_id = p_organization_id;
2399
2400 inv_rcv_common_apis.g_rcv_global_var.receipt_num := l_receipt_num;
2401 EXCEPTION
2402 WHEN NO_DATA_FOUND THEN
2403 l_receipt_num := NULL;
2404 END;
2405 INV_rcv_common_apis.init_startup_values(p_organization_id);
2406 Exception
2407 when NO_DATA_FOUND then
2408 FND_MESSAGE.SET_NAME('INV', 'INV_RCV_PARAM');
2409 FND_MSG_PUB.ADD;
2410 RAISE fnd_api.g_exc_error ;
2411 End;
2412
2413 -- default header level non-DB items in rcv_transaction block
2414 -- and default other values need to be insert into RTI
2415
2416
2417 l_progress := '20';
2418
2419 -- default l_group_id ? clear group id after done
2420 IF INV_rcv_common_apis.g_rcv_global_var.interface_group_id is NULL THEN
2421 SELECT rcv_interface_groups_s.nextval
2422 INTO l_group_id
2423 FROM dual;
2424 INV_rcv_common_apis.g_rcv_global_var.interface_group_id := l_group_id;
2425 ELSE
2426 l_group_id := INV_rcv_common_apis.g_rcv_global_var.interface_group_id;
2427 END IF;
2428
2429 l_progress := '30';
2430
2431
2432 -- call matching algorithm ?
2433
2434 -- initialize input record for matching algorithm
2435 inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross(inv_rcv_std_rcpt_apis.g_receipt_detail_index).group_id := l_group_id;
2436 inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross(inv_rcv_std_rcpt_apis.g_receipt_detail_index).transaction_type := 'DELIVER';
2437 inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross(inv_rcv_std_rcpt_apis.g_receipt_detail_index).quantity := p_rcv_qty;
2438 inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross(inv_rcv_std_rcpt_apis.g_receipt_detail_index).unit_of_measure := p_rcv_uom;
2439 IF p_item_id IS NOT NULL THEN
2440 inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross(inv_rcv_std_rcpt_apis.g_receipt_detail_index).item_id := p_item_id;
2441 ELSE
2442 inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross(inv_rcv_std_rcpt_apis.g_receipt_detail_index).item_id := NULL;
2443 inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross(inv_rcv_std_rcpt_apis.g_receipt_detail_index).item_desc := p_item_desc;
2444 end if;
2445 --inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross(inv_rcv_std_rcpt_apis.g_receipt_detail_index).item_id := p_item_id;
2446 inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross(inv_rcv_std_rcpt_apis.g_receipt_detail_index).to_organization_id := p_organization_id;
2447 inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross(inv_rcv_std_rcpt_apis.g_receipt_detail_index).shipment_header_id := p_shipment_header_id;
2448 inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross(inv_rcv_std_rcpt_apis.g_receipt_detail_index).po_header_id := p_po_header_id;
2449 inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross(inv_rcv_std_rcpt_apis.g_receipt_detail_index).expected_receipt_date := Sysdate; --?
2450 inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross(inv_rcv_std_rcpt_apis.g_receipt_detail_index).tax_amount := 0; -- ?
2451 inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross(inv_rcv_std_rcpt_apis.g_receipt_detail_index).error_status := 'S'; -- ?
2452 inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross(inv_rcv_std_rcpt_apis.g_receipt_detail_index).project_id := p_project_id;--BUG# 2794612
2453 inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross(inv_rcv_std_rcpt_apis.g_receipt_detail_index).task_id := p_task_id;--BUG# 2794612
2454
2455
2456 l_progress := '60';
2457
2458 IF p_item_id IS NOT NULL THEN
2459 SELECT primary_unit_of_measure
2460 INTO inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross(inv_rcv_std_rcpt_apis.g_receipt_detail_index).primary_unit_of_measure
2461 FROM mtl_system_items
2462 WHERE mtl_system_items.inventory_item_id = p_item_id
2463 AND mtl_system_items.organization_id = p_organization_id;
2464 l_progress := '70';
2465 ELSE
2466 inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross(inv_rcv_std_rcpt_apis.g_receipt_detail_index).primary_unit_of_measure := NULL;
2467 l_progress := '71';
2468 END IF;
2469
2470
2471 IF p_source_type = 'ASN' THEN
2472 l_match_type := 'ASN';
2473 ELSE
2474 l_match_type := 'INTRANSIT SHIPMENT';
2475 BEGIN
2476 SELECT cost_group_id
2477 INTO l_rcv_transaction_rec.cost_group_id
2478 FROM wms_lpn_contents wlpnc
2479 WHERE organization_id = p_organization_id
2480 AND parent_lpn_id = p_lpn_id
2481 AND wlpnc.inventory_item_id = p_item_id
2482 AND exists (SELECT 1
2483 FROM cst_cost_group_accounts
2484 WHERE organization_id = p_organization_id
2485 AND cost_group_id = wlpnc.cost_group_id);
2486 EXCEPTION
2487 WHEN OTHERS THEN
2488 l_rcv_transaction_rec.cost_group_id := NULL;
2489 END;
2490
2491 IF l_rcv_transaction_rec.cost_group_id IS NULL THEN
2492 UPDATE wms_lpn_contents wlpnc
2493 SET cost_group_id = NULL
2494 WHERE organization_id = p_organization_id
2495 AND parent_lpn_id = p_lpn_id
2496 AND wlpnc.inventory_item_id = p_item_id
2497 AND NOT exists (SELECT 1
2498 FROM cst_cost_group_accounts
2499 WHERE organization_id = p_organization_id
2500 AND cost_group_id = wlpnc.cost_group_id);
2501
2502 END IF;
2503 END IF;
2504
2505 /* EOU Comented out as this procedure is not needed by OPM
2506 INV_rcv_txn_match.matching_logic
2507 (x_return_status => l_return_status, --?
2508 x_msg_count => l_msg_count,
2509 x_msg_data => l_msg_data,
2510 x_cascaded_table => inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross,
2511 n => inv_rcv_std_rcpt_apis.g_receipt_detail_index,
2512 temp_cascaded_table => l_rcpt_match_table_detail,
2513 p_receipt_num => NULL,
2514 p_match_type => l_match_type,
2515 p_lpn_id => p_lpn_id
2516 );
2517
2518 */
2519
2520 IF l_return_status = fnd_api.g_ret_sts_error THEN
2521 FND_MESSAGE.SET_NAME('INV', 'INV_RCV_MATCH_ERROR');
2522 FND_MSG_PUB.ADD;
2523 RAISE fnd_api.g_exc_error;
2524 END IF;
2525
2526 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
2527 FND_MESSAGE.SET_NAME('INV', 'INV_RCV_MATCH_ERROR');
2528 FND_MSG_PUB.ADD;
2529 RAISE fnd_api.g_exc_unexpected_error;
2530 END IF;
2531
2532 IF inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross(inv_rcv_std_rcpt_apis.g_receipt_detail_index).error_status = 'E' THEN
2533 l_err_message := inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross(inv_rcv_std_rcpt_apis.g_receipt_detail_index).error_message;
2534 FND_MESSAGE.SET_NAME('INV', l_err_message);
2535 FND_MSG_PUB.ADD;
2536 RAISE fnd_api.g_exc_error;
2537 END IF;
2538
2539 l_err_message := '@@@';
2540 FOR i IN inv_rcv_std_rcpt_apis.g_receipt_detail_index..(inv_rcv_std_rcpt_apis.g_receipt_detail_index + l_rcpt_match_table_detail.COUNT - 1) LOOP
2541 IF l_rcpt_match_table_detail(i-inv_rcv_std_rcpt_apis.g_receipt_detail_index+1).error_status = 'W' THEN
2542 x_status := 'W';
2543
2544 l_temp_message := l_rcpt_match_table_detail(i-inv_rcv_std_rcpt_apis.g_receipt_detail_index+1).error_message;
2545 IF l_temp_message IS NULL THEN
2546 l_err_message := 'INV_RCV_GEN_TOLERANCE_EXCEED';
2547 l_msg_prod := 'INV';
2548 EXIT;
2549 END IF;
2550 IF l_err_message = '@@@' THEN
2551 l_err_message := l_temp_message;
2552 l_msg_prod := 'INV';
2553 ELSIF l_temp_message <> l_err_message THEN
2554 l_msg_prod := 'INV';
2555 l_err_message := 'INV_RCV_GEN_TOLERANCE_EXCEED';
2556 EXIT;
2557 END IF;
2558 END IF;
2559 END LOOP;
2560
2561 IF l_err_message <> '@@@' THEN
2562 FND_MESSAGE.SET_NAME(l_msg_prod, l_err_message);
2563 FND_MSG_PUB.ADD;
2564 END IF;
2565
2566
2567 -- load the matching algorithm result into input data structure
2568
2569
2570 -- based on return from matching algorithm,
2571 -- determine which line in rcv_transaction block to be inserted into RTI
2572
2573
2574 -- loop through results returned by matching algorithm
2575 FOR match_result_count IN 1..l_rcpt_match_table_detail.COUNT LOOP
2576 l_progress := '72';
2577
2578 OPEN l_curs_rcpt_detail(l_rcpt_match_table_detail(match_result_count).shipment_line_id,
2579 l_rcpt_match_table_detail(match_result_count).po_distribution_id);
2580
2581 l_progress := '74';
2582
2583 FETCH l_curs_rcpt_detail INTO l_rcv_rcpt_rec;
2584
2585 l_progress := '76';
2586
2587 CLOSE l_curs_rcpt_detail;
2588
2589 l_progress := '78';
2590
2591 l_rcv_transaction_rec.rcv_shipment_line_id := l_rcpt_match_table_detail(match_result_count).shipment_line_id;
2592 l_rcv_transaction_rec.po_distribution_id := l_rcpt_match_table_detail(match_result_count).po_distribution_id;
2593
2594
2595 -- update following fields from matching algorithm return value
2596 l_rcv_transaction_rec.transaction_qty := l_rcpt_match_table_detail(match_result_count).quantity;
2597 l_rcv_transaction_rec.transaction_uom := l_rcpt_match_table_detail(match_result_count).unit_of_measure;
2598 l_rcv_transaction_rec.primary_quantity := l_rcpt_match_table_detail(match_result_count).primary_quantity;
2599 l_rcv_transaction_rec.primary_uom := l_rcpt_match_table_detail(match_result_count).primary_unit_of_measure;
2600 l_rcv_transaction_rec.lpn_id := p_lpn_id;
2601 l_rcv_transaction_rec.transfer_lpn_id := p_lpn_id;
2602 -- update following fields for po_distribution related values
2603 l_rcv_transaction_rec.currency_conversion_date := l_rcv_rcpt_rec.currency_conversion_date_pod;
2604 l_rcv_transaction_rec.currency_conversion_rate := l_rcv_rcpt_rec.currency_conversion_rate_pod;
2605 -- following fileds can have distribution level values
2606 -- therefore they are set here instead of in the common insert code
2607 l_rcv_transaction_rec.ordered_qty := l_rcv_rcpt_rec.qty_ordered;
2608 --Bug 2073164
2609 l_rcv_rcpt_rec.uom_code := p_rcv_uom_code;
2610 l_total_primary_qty := l_total_primary_qty + l_rcv_transaction_rec.primary_quantity;
2611 l_rcv_transaction_rec.lpn_id := p_lpn_id;
2612
2613
2614 -- wip related fields
2615 IF l_rcv_rcpt_rec.wip_entity_id > 0 THEN
2616 l_rcv_transaction_rec.wip_entity_id := l_rcv_rcpt_rec.wip_entity_id;
2617 l_rcv_transaction_rec.wip_operation_seq_num := l_rcv_rcpt_rec.wip_operation_seq_num;
2618 l_rcv_transaction_rec.wip_resource_seq_num := l_rcv_rcpt_rec.wip_resource_seq_num;
2619
2620 l_rcv_transaction_rec.wip_repetitive_schedule_id := l_rcv_rcpt_rec.wip_repetitive_schedule_id;
2621 l_rcv_transaction_rec.wip_line_id := l_rcv_rcpt_rec.wip_line_id;
2622 l_rcv_transaction_rec.bom_resource_id := l_rcv_transaction_rec.bom_resource_id;
2623 -- there is getting actual values call for wip
2624 -- since they are not inserted in RTI, I am not calling it here
2625 -- the code is in
2626 -- rcv_transactions_sv.get_wip_info ()
2627 END IF;
2628
2629 l_progress := '80';
2630 populate_default_values(p_rcv_transaction_rec => l_rcv_transaction_rec,
2631 p_rcv_rcpt_rec => l_rcv_rcpt_rec,
2632 p_group_id => l_group_id,
2633 p_organization_id => p_organization_id,
2634 p_item_id => p_item_id,
2635 p_revision => p_revision,
2636 p_source_type => p_source_type,
2637 p_subinventory_code => p_subinventory,
2638 p_locator_id => p_locator_id,
2639 p_transaction_temp_id => p_transaction_temp_id,
2640 p_lot_control_code => p_lot_control_code,
2641 p_serial_control_code => p_serial_control_code);
2642
2643 l_progress := '90';
2644
2645
2646 END LOOP;
2647
2648 IF l_curs_rcpt_detail%isopen THEN
2649 CLOSE l_curs_rcpt_detail;
2650 END IF;
2651
2652 -- append index in input table where the line to be detailed needs to be inserted
2653 --g_receipt_detail_index := l_rcpt_match_table_detail.COUNT + g_receipt_detail_index;
2654
2655 -- UPDATE lpn context
2656
2657 l_progress := '100';
2658
2659 UPDATE wms_license_plate_numbers
2660 SET lpn_context = 3
2661 WHERE lpn_id = p_lpn_id;
2662
2663 l_progress := '110';
2664
2665
2666 l_progress := '120';
2667 inv_rcv_common_apis.do_check
2668 (p_organization_id => p_organization_id,
2669 p_inventory_item_id => p_item_id,
2670 p_transaction_type_id => 18,
2671 p_primary_quantity => l_total_primary_qty,
2672 x_return_status => l_return_status,
2673 x_msg_count => l_msg_count,
2674 x_msg_data => x_message);
2675
2676 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2677 x_status := l_return_status;
2678 END IF;
2679
2680 l_progress := '130';
2681
2682 /** Not supported by OPM
2683
2684 -- Calling The ASN Discrepnacy Details
2685 inv_cr_asn_details.CREATE_ASN_DETAILS (
2686 p_organization_id,
2687 l_group_id,
2688 l_rcv_rcpt_rec,
2689 l_rcv_transaction_rec,
2690 inv_rcv_std_rcpt_apis.g_rcpt_lot_qty_rec_tb,
2691 to_number(null),
2692 l_return_status,
2693 l_msg_data );
2694 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2695 x_status := l_return_status;
2696 END IF;
2697
2698 */
2699
2700 l_progress := '140';
2701
2702 -- Clear the Lot Rec
2703 inv_rcv_std_rcpt_apis.g_rcpt_lot_qty_rec_tb.DELETE;
2704
2705
2706 EXCEPTION
2707
2708 WHEN fnd_api.g_exc_error THEN
2709 ROLLBACK TO crt_asn_con_rti_sp;
2710 x_status := fnd_api.g_ret_sts_error;
2711 IF l_curs_rcpt_detail%isopen THEN
2712 CLOSE l_curs_rcpt_detail;
2713 END IF;
2714
2715 fnd_msg_pub.count_and_get
2716 (p_encoded => FND_API.g_false,
2717 p_count => l_msg_count,
2718 p_data => x_message
2719 );
2720
2721 WHEN fnd_api.g_exc_unexpected_error THEN
2722 ROLLBACK TO crt_asn_con_rti_sp;
2723 x_status := fnd_api.g_ret_sts_unexp_error ;
2724 IF l_curs_rcpt_detail%isopen THEN
2725 CLOSE l_curs_rcpt_detail;
2726 END IF;
2727
2728 fnd_msg_pub.count_and_get
2729 (p_encoded => FND_API.g_false,
2730 p_count => l_msg_count,
2731 p_data => x_message
2732 );
2733
2734
2735 WHEN OTHERS THEN
2736 ROLLBACK TO crt_asn_con_rti_sp;
2737 x_status := fnd_api.g_ret_sts_unexp_error ;
2738 IF l_curs_rcpt_detail%isopen THEN
2739 CLOSE l_curs_rcpt_detail;
2740 END IF;
2741
2742
2743 fnd_msg_pub.count_and_get
2744 (p_encoded => FND_API.g_false,
2745 p_count => l_msg_count,
2746 p_data => x_message
2747 );
2748
2749 END;
2750
2751
2752 PROCEDURE create_asn_exp_dd_intf_rec
2753 (p_move_order_header_id IN OUT NOCOPY NUMBER,
2754 p_organization_id IN NUMBER,
2755 p_shipment_header_id IN NUMBER,
2756 p_po_header_id IN NUMBER,
2757 p_source_type IN VARCHAR2,
2758 p_subinventory VARCHAR2,
2759 p_locator_id NUMBER,
2760 p_lpn_id IN NUMBER,
2761 p_transaction_temp_id IN NUMBER,
2762 x_status OUT NOCOPY VARCHAR2,
2763 x_message OUT NOCOPY VARCHAR2,
2764 p_project_id IN NUMBER,
2765 p_task_id IN NUMBER,
2766 p_country_code IN VARCHAR2 DEFAULT NULL
2767 )
2768 IS
2769
2770 -- Bug 2182881
2771 -- changed the cursor as for lot_numbers it was not joining with
2772 -- organization_id.
2773 CURSOR l_curs_asn_lpn_content IS
2774 SELECT
2775 lpnc.lpn_id,
2776 lpnc.inventory_item_id,
2777 lpnc.revision,
2778 lpnc.quantity,
2779 lpnc.uom_code,
2780 lpnc.lot_control_code,
2781 lpnc.serial_number_control_code,
2782 lpnc.primary_uom_code,
2783 p_po_header_id,
2784 lpnc.lot_number,
2785 mln.expiration_date
2786 FROM
2787 mtl_lot_numbers mln,
2788 (SELECT wlpn.lpn_id,
2789 wlpnc.inventory_item_id,
2790 msi.organization_id,
2791 msi.lot_control_code,
2792 msi.serial_number_control_code,
2793 msi.primary_uom_code,
2794 wlpnc.revision,
2795 wlpnc.quantity,
2796 wlpnc.uom_code,
2797 wlpnc.lot_number,
2798 wlpnc.source_line_id
2799 FROM wms_lpn_contents wlpnc
2800 , wms_license_plate_numbers wlpn
2801 , mtl_system_items msi
2802 , rcv_shipment_headers rsh
2803 WHERE rsh.shipment_header_id = p_shipment_header_id
2804 AND (wlpn.source_header_id = rsh.shipment_header_id
2805 OR wlpn.source_name = rsh.shipment_num)
2806 AND wlpn.lpn_context IN (6, 7) -- only those pre-ASN receiving ones
2807 AND wlpnc.parent_lpn_id = Nvl(p_lpn_id, wlpn.lpn_id)
2808 AND wlpnc.inventory_item_id = msi.inventory_item_id
2809 AND msi.organization_id = p_organization_id
2810 AND wlpn.lpn_id = wlpnc.parent_lpn_id
2811 AND (wlpnc.source_line_id IN
2812 (SELECT pola.po_line_id
2813 FROM po_lines_all pola
2814 WHERE pola.po_header_id = Nvl(p_po_header_id, pola.po_header_id))
2815 OR wlpnc.source_line_id IS NULL)
2816 ) lpnc
2817 WHERE lpnc.inventory_item_id = mln.inventory_item_id(+)
2818 AND lpnc.lot_number =mln.lot_number(+)
2819 AND lpnc.organization_id =mln.organization_id(+);
2820
2821 CURSOR l_curs_serial_number
2822 (v_inventory_item_id NUMBER,
2823 v_revision VARCHAR2,
2824 v_lot_number VARCHAR2,
2825 v_lpn_id NUMBER) IS
2826 -- bug 2182881
2827 -- added nvl around the cursor
2828 SELECT serial_number
2829 FROM mtl_serial_numbers
2830 WHERE inventory_item_id = v_inventory_item_id
2831 AND (revision = v_revision OR (revision IS NULL AND
2832 v_revision IS NULL))
2833 AND (lot_number = v_lot_number OR (lot_number IS NULL AND
2834 v_lot_number IS NULL))
2835 AND lpn_id = v_lpn_id;
2836
2837 TYPE number_tab_tp IS TABLE OF NUMBER
2838 INDEX BY BINARY_INTEGER;
2839
2840 TYPE date_tab_tp IS TABLE OF DATE
2841 INDEX BY BINARY_INTEGER;
2842
2843 TYPE varchar_tab_tp IS TABLE OF VARCHAR2(30)
2844 INDEX BY BINARY_INTEGER;
2845
2846 l_msnt_transaction_temp_id number_tab_tp;
2847 l_msnt_last_update_date date_tab_tp;
2848 l_msnt_last_updated_by number_tab_tp;
2849 l_msnt_creation_date date_tab_tp;
2850 l_msnt_created_by number_tab_tp ;
2851 l_msnt_fm_serial_number varchar_tab_tp;
2852 l_msnt_to_serial_number varchar_tab_tp;
2853
2854 l_lpn_id NUMBER;
2855 l_inventory_item_id NUMBER;
2856 l_revision VARCHAR2(30);
2857 l_quantity NUMBER;
2858 l_uom_code VARCHAR2(3);
2859 l_lot_control_code NUMBER;
2860 l_serial_control_code NUMBER;
2861 l_unit_of_measure VARCHAR2(25);
2862 l_po_header_id NUMBER;
2863 l_lot_number VARCHAR2(30);
2864 l_lot_expiration_date DATE;
2865
2866 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
2867 l_msg_count NUMBER;
2868 l_msg_data VARCHAR2(400);
2869 l_progress VARCHAR2(10);
2870
2871 l_transaction_temp_id NUMBER;
2872 l_serial_txn_temp_id NUMBER;
2873 l_primary_uom_code VARCHAR2(3);
2874 l_primary_qty NUMBER;
2875 l_uom_conv_ratio NUMBER;
2876 l_serial_number VARCHAR2(30);
2877 l_msnt_rec mtl_serial_numbers_temp%ROWTYPE;
2878 l_serial_number_count NUMBER;
2879
2880 l_label_status VARCHAR2(500);
2881 BEGIN
2882
2883 x_status := fnd_api.g_ret_sts_success;
2884
2885 l_progress := '10';
2886
2887 OPEN l_curs_asn_lpn_content;
2888
2889 l_progress := '20';
2890
2891 LOOP
2892 FETCH l_curs_asn_lpn_content INTO
2893 l_lpn_id,
2894 l_inventory_item_id,
2895 l_revision,
2896 l_quantity,
2897 l_uom_code,
2898 l_lot_control_code,
2899 l_serial_control_code,
2900 l_primary_uom_code,
2901 l_po_header_id,
2902 l_lot_number,
2903 l_lot_expiration_date;
2904
2905 EXIT WHEN l_curs_asn_lpn_content%notfound;
2906
2907 l_progress := '30';
2908
2909 inv_rcv_std_rcpt_apis.update_lpn_org(p_organization_id => p_organization_id,
2910 p_lpn_id => l_lpn_id,
2911 x_return_status => l_return_status,
2912 x_msg_count => l_msg_count,
2913 x_msg_data => l_msg_data);
2914
2915 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2916 RAISE fnd_api.g_exc_error;
2917 END IF;
2918
2919 l_progress := '35';
2920 SELECT unit_of_measure
2921 INTO l_unit_of_measure
2922 FROM mtl_item_uoms_view
2923 WHERE uom_code = l_uom_code
2924 AND organization_id = p_organization_id
2925 AND inventory_item_id = l_inventory_item_id;
2926
2927 l_progress := '40';
2928
2929
2930 -- insert into mtlt
2931 IF l_lot_number IS NOT NULL THEN
2932
2933 inv_convert.inv_um_conversion(from_unit => l_uom_code,
2934 to_unit => l_primary_uom_code,
2935 item_id => l_inventory_item_id,
2936 uom_rate => l_uom_conv_ratio);
2937
2938 IF l_uom_conv_ratio = -99999 THEN -- uom conversion failure
2939 FND_MESSAGE.SET_NAME('INV', 'INV_INT_UOMCONVCODE');
2940 FND_MSG_PUB.ADD;
2941 RAISE fnd_api.g_exc_error;
2942 END IF;
2943
2944 l_primary_qty := l_quantity * l_uom_conv_ratio;
2945
2946 inv_rcv_common_apis.insert_lot
2947 (p_transaction_temp_id => l_transaction_temp_id,
2948 p_created_by => fnd_global.user_id,
2949 p_transaction_qty => l_quantity,
2950 p_primary_qty => l_primary_qty,
2951 p_lot_number => l_lot_number,
2952 p_expiration_date => l_lot_expiration_date,
2953 p_status_id => NULL,
2954 x_serial_transaction_temp_id => l_serial_txn_temp_id,
2955 x_return_status => l_return_status,
2956 x_msg_data => l_msg_data);
2957
2958 IF l_return_status = FND_API.g_ret_sts_error THEN
2959 FND_MESSAGE.SET_NAME('INV', 'INV_RCV_CREATE_ASNEXP_RTI_FAIL');
2960 FND_MSG_PUB.ADD;
2961 RAISE FND_API.G_EXC_ERROR;
2962 END IF;
2963
2964 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
2965 FND_MESSAGE.SET_NAME('INV', 'INV_RCV_CREATE_ASNEXP_RTI_FAIL');
2966 FND_MSG_PUB.ADD;
2967 RAISE FND_API.g_exc_unexpected_error;
2968 END IF;
2969
2970 END IF;
2971
2972 l_progress := '41';
2973
2974 -- insert into msnt
2975
2976 IF l_serial_control_code = 2
2977 OR l_serial_control_code = 5 THEN
2978
2979 OPEN l_curs_serial_number
2980 (l_inventory_item_id,
2981 l_revision,
2982 l_lot_number,
2983 l_lpn_id);
2984 l_serial_number_count := 0;
2985
2986 IF l_serial_txn_temp_id IS NULL THEN -- Not lot controlled
2987 l_progress := '42';
2988 SELECT mtl_material_transactions_s.NEXTVAL
2989 INTO l_serial_txn_temp_id
2990 FROM dual;
2991 l_progress := '44';
2992
2993 l_transaction_temp_id := l_serial_txn_temp_id;
2994 END IF;
2995
2996 LOOP
2997 l_progress := '45';
2998
2999 FETCH l_curs_serial_number INTO l_serial_number;
3000
3001 l_progress := '46';
3002
3003 EXIT WHEN l_curs_serial_number%notfound;
3004 l_serial_number_count := l_serial_number_count + 1;
3005
3006 l_msnt_transaction_temp_id(l_serial_number_count) := l_serial_txn_temp_id;
3007 l_msnt_last_update_date(l_serial_number_count) := Sysdate;
3008 l_msnt_last_updated_by(l_serial_number_count) := fnd_global.user_id;
3009 l_msnt_creation_date(l_serial_number_count) := Sysdate;
3010 l_msnt_created_by(l_serial_number_count) := fnd_global.user_id;
3011 l_msnt_fm_serial_number(l_serial_number_count) := l_serial_number;
3012 l_msnt_to_serial_number(l_serial_number_count) := l_serial_number;
3013
3014 END LOOP;
3015
3016
3017 CLOSE l_curs_serial_number;
3018
3019 l_progress := '47';
3020
3021 FORALL i IN 1..l_msnt_transaction_temp_id.COUNT
3022 INSERT INTO mtl_serial_numbers_temp
3023 (transaction_temp_id,
3024 last_update_date,
3025 last_updated_by,
3026 creation_date,
3027 created_by,
3028 fm_serial_number,
3029 to_serial_number
3030 )
3031 VALUES
3032 (l_msnt_transaction_temp_id(i),
3033 l_msnt_last_update_date(i),
3034 l_msnt_last_updated_by(i),
3035 l_msnt_creation_date(i),
3036 l_msnt_created_by(i),
3037 l_msnt_fm_serial_number(i),
3038 l_msnt_to_serial_number(i)
3039 );
3040
3041 l_progress := '48';
3042
3043 FORALL i IN 1..l_msnt_transaction_temp_id.COUNT
3044 UPDATE mtl_serial_numbers
3045 SET group_mark_id = l_serial_txn_temp_id
3046 WHERE inventory_item_id = l_inventory_item_id
3047 AND serial_number = l_msnt_fm_serial_number(i);
3048
3049 l_progress := '49';
3050
3051 END IF;
3052
3053
3054 create_asn_con_dd_intf_rec
3055 (p_move_order_header_id => p_move_order_header_id,
3056 p_organization_id => p_organization_id,
3057 p_shipment_header_id => p_shipment_header_id,
3058 p_po_header_id => l_po_header_id,
3059 p_item_id => l_inventory_item_id,
3060 p_rcv_qty => l_quantity,
3061 p_rcv_uom => l_unit_of_measure,
3062 p_rcv_uom_code => l_uom_code,
3063 p_source_type => p_source_type,
3064 p_subinventory => p_subinventory,
3065 p_locator_id => p_locator_id,
3066 p_lpn_id => l_lpn_id,
3067 p_lot_control_code => l_lot_control_code,
3068 p_serial_control_code => l_serial_control_code,
3069 p_revision => l_revision,
3070 p_transaction_temp_id => Nvl(l_transaction_temp_id, p_transaction_temp_id),
3071 x_status => l_return_status,
3072 x_message => l_msg_data ,
3073 p_project_id => p_project_id,
3074 p_task_id => p_task_id
3075 );
3076
3077 IF l_return_status = FND_API.g_ret_sts_error THEN
3078 FND_MESSAGE.SET_NAME('INV', 'INV_RCV_CREATE_ASNEXP_RTI_FAIL');
3079 FND_MSG_PUB.ADD;
3080 RAISE FND_API.G_EXC_ERROR;
3081 END IF;
3082
3083 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
3084 FND_MESSAGE.SET_NAME('INV', 'INV_RCV_CREATE_ASNEXP_RTI_FAIL');
3085 FND_MSG_PUB.ADD;
3086 RAISE FND_API.g_exc_unexpected_error;
3087 END IF;
3088
3089
3090 END LOOP;
3091
3092 l_progress := '60';
3093
3094 IF l_curs_asn_lpn_content%isopen THEN
3095 CLOSE l_curs_asn_lpn_content;
3096 END IF;
3097
3098 l_progress := '70';
3099
3100 -- UPDATE lpn context
3101
3102 UPDATE wms_license_plate_numbers
3103 SET lpn_context = 3
3104 WHERE source_header_id = p_shipment_header_id
3105 AND lpn_id = Nvl(p_lpn_id, lpn_id);
3106
3107 l_progress := '80';
3108
3109 -- UPDATE the lpn history table with source name as ASNEXP since no packing happened.
3110 -- This is needed to help the cleanup later on
3111 -- Nothing else is updated to keep in synch with license_plate_number update
3112
3113
3114 update wms_lpn_histories
3115 set source_name = 'ASNEXP',
3116 source_header_id = INV_rcv_common_apis.g_rcv_global_var.interface_group_id
3117 where lpn_context = 7
3118 and parent_lpn_id in (select lpn_id
3119 from wms_license_plate_numbers
3120 WHERE source_header_id = p_shipment_header_id
3121 AND lpn_id = Nvl(p_lpn_id, lpn_id)
3122 );
3123
3124
3125
3126
3127
3128 EXCEPTION
3129
3130 WHEN fnd_api.g_exc_error THEN
3131 x_status := fnd_api.g_ret_sts_error;
3132 IF l_curs_asn_lpn_content%isopen THEN
3133 CLOSE l_curs_asn_lpn_content;
3134 END IF;
3135
3136 fnd_msg_pub.count_and_get
3137 (p_encoded => FND_API.g_false,
3138 p_count => l_msg_count,
3139 p_data => x_message
3140 );
3141
3142 WHEN fnd_api.g_exc_unexpected_error THEN
3143 x_status := fnd_api.g_ret_sts_unexp_error ;
3144 IF l_curs_asn_lpn_content%isopen THEN
3145 CLOSE l_curs_asn_lpn_content;
3146 END IF;
3147
3148 fnd_msg_pub.count_and_get
3149 (p_encoded => FND_API.g_false,
3150 p_count => l_msg_count,
3151 p_data => x_message
3152 );
3153
3154
3155 WHEN OTHERS THEN
3156 x_status := fnd_api.g_ret_sts_unexp_error ;
3157 IF l_curs_asn_lpn_content%isopen THEN
3158 CLOSE l_curs_asn_lpn_content;
3159 END IF;
3160
3161
3162 fnd_msg_pub.count_and_get
3163 (p_encoded => FND_API.g_false,
3164 p_count => l_msg_count,
3165 p_data => x_message
3166 );
3167
3168 END create_asn_exp_dd_intf_rec;
3169
3170
3171 PROCEDURE create_osp_direct_rti_rec(p_move_order_header_id IN OUT NOCOPY NUMBER,
3172 p_organization_id IN NUMBER,
3173 p_po_header_id IN NUMBER,
3174 p_po_release_id IN NUMBER,
3175 p_po_line_id IN NUMBER,
3176 p_item_id IN NUMBER,
3177 p_rcv_qty IN NUMBER,
3178 p_rcv_uom IN VARCHAR2,
3179 p_rcv_uom_code IN VARCHAR2,
3180 p_source_type IN VARCHAR2,
3181 p_transaction_temp_id IN NUMBER,
3182 p_revision IN VARCHAR2,
3183 p_po_distribution_id IN NUMBER,
3184 x_status OUT NOCOPY VARCHAR2,
3185 x_message OUT NOCOPY VARCHAR2
3186 )
3187 IS
3188 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
3189 l_msg_count NUMBER;
3190 l_msg_data VARCHAR2(400);
3191 l_label_status VARCHAR2(500);
3192 l_progress VARCHAR2(10);
3193 l_txn_id_tbl inv_label.transaction_id_rec_type;
3194 l_counter NUMBER := 0;
3195
3196 CURSOR c_rti_txn_id IS
3197 /* Bug 2443163 */
3198 /* SELECT MIN(rti.interface_transaction_id) */
3199 /* Group BY LPN_ID is changed for Express Receipts */
3200 /* Also duplicate print of LPN labels is avoided */
3201 SELECT MAX(rti.interface_transaction_id)
3202 FROM rcv_transactions_interface rti
3203 WHERE rti.group_id = INV_rcv_common_apis.g_rcv_global_var.interface_group_id
3204 GROUP BY decode(p_source_type, 'ASNEXP',rti.interface_transaction_id,'SHIPMENTEXP',rti.interface_transaction_id,null) ;
3205 -- GROUP BY rti.lpn_id;
3206 BEGIN
3207 x_status := fnd_api.g_ret_sts_success;
3208 l_progress := '10';
3209
3210 IF inv_rcv_common_apis.g_po_startup_value.sob_id IS NULL THEN
3211 select ood.set_of_books_id
3212 into inv_rcv_common_apis.g_po_startup_value.sob_id
3213 FROM org_organization_definitions ood,
3214 gl_sets_of_books sob
3215 WHERE organization_id = p_organization_id
3216 AND sob.set_of_books_id = ood.set_of_books_id;
3217 END IF;
3218
3219 l_progress := '10';
3220 -- first check if the transaction date satisfies the validation.
3221 inv_rcv_common_apis.validate_trx_date(p_trx_date => Sysdate,
3222 p_organization_id => p_organization_id,
3223 p_sob_id => inv_rcv_common_apis.g_po_startup_value.sob_id,
3224 x_return_status => x_status,
3225 x_error_code => x_message);
3226
3227 IF x_status <> fnd_api.g_ret_sts_success THEN
3228 RETURN;
3229 END IF;
3230
3231 IF p_po_header_id IS NULL AND p_item_id IS NULL THEN
3232 x_status := fnd_api.g_ret_sts_error;
3233 RETURN;
3234 END IF;
3235
3236 l_progress := '30';
3237
3238 create_osp_drct_dlvr_rti_rec(p_move_order_header_id => p_move_order_header_id,
3239 p_organization_id => p_organization_id,
3240 p_po_header_id => p_po_header_id,
3241 p_po_release_id => p_po_release_id,
3242 p_po_line_id => p_po_line_id,
3243 p_po_line_location_id => NULL,
3244 p_po_distribution_id => p_po_distribution_id,
3245 p_item_id => p_item_id,
3246 p_rcv_qty => p_rcv_qty,
3247 p_rcv_uom => p_rcv_uom,
3248 p_rcv_uom_code => p_rcv_uom_code,
3249 p_source_type => p_source_type,
3250 p_transaction_temp_id => p_transaction_temp_id,
3251 p_revision => p_revision,
3252 x_status => l_return_status,
3253 x_message => x_message);
3254
3255 IF l_return_status = FND_API.g_ret_sts_error THEN
3256 FND_MESSAGE.SET_NAME('INV', 'INV_RCV_CREATE_PO_RTI_FAIL'); -- MSGTBD
3257 FND_MSG_PUB.ADD;
3258 RAISE FND_API.G_EXC_ERROR;
3259 END IF;
3260 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
3261 FND_MESSAGE.SET_NAME('INV', 'INV_RCV_CREATE_PO_RTI_FAIL'); -- MSGTBD
3262 FND_MSG_PUB.ADD;
3263 RAISE FND_API.g_exc_unexpected_error;
3264 END IF;
3265
3266 l_progress := '40';
3267 x_status := l_return_status; -- l_return_status can be 'W', we want to carry that over
3268
3269 -- calling label printing API
3270 IF l_return_status <> FND_API.g_ret_sts_error THEN
3271 l_progress := '40';
3272
3273 l_counter := 1;
3274 OPEN c_rti_txn_id;
3275 LOOP
3276 FETCH c_rti_txn_id
3277 INTO l_txn_id_tbl(l_counter);
3278 EXIT WHEN c_rti_txn_id%notfound;
3279 l_counter := l_counter + 1;
3280 END LOOP;
3281 CLOSE c_rti_txn_id;
3282
3283 inv_label.print_label
3284 (x_return_status => l_return_status
3285 , x_msg_count => l_msg_count
3286 , x_msg_data => l_msg_data
3287 , x_label_status => l_label_status
3288 , p_api_version => 1.0
3289 , p_print_mode => 1
3290 , p_business_flow_code => 1
3291 , p_transaction_id => l_txn_id_tbl
3292 );
3293
3294 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3295 FND_MESSAGE.SET_NAME('INV', 'INV_RCV_CRT_PRINT_LAB_FAIL'); -- MSGTBD
3296 FND_MSG_PUB.ADD;
3297 x_status := 'W';
3298 END IF;
3299
3300 END IF;
3301
3302 EXCEPTION
3303 WHEN fnd_api.g_exc_error THEN
3304 x_status := fnd_api.g_ret_sts_error;
3305 fnd_msg_pub.count_and_get
3306 (p_encoded => FND_API.g_false,
3307 p_count => l_msg_count,
3308 p_data => x_message
3309 );
3310
3311 WHEN fnd_api.g_exc_unexpected_error THEN
3312 x_status := fnd_api.g_ret_sts_unexp_error ;
3313 fnd_msg_pub.count_and_get
3314 (p_encoded => FND_API.g_false,
3315 p_count => l_msg_count,
3316 p_data => x_message
3317 );
3318
3319 WHEN OTHERS THEN
3320 x_status := fnd_api.g_ret_sts_unexp_error ;
3321 fnd_msg_pub.count_and_get
3322 (p_encoded => FND_API.g_false,
3323 p_count => l_msg_count,
3324 p_data => x_message
3325 );
3326
3327 END create_osp_direct_rti_rec;
3328
3329
3330 PROCEDURE create_direct_rti_rec(p_move_order_header_id IN OUT NOCOPY NUMBER,
3331 p_organization_id IN NUMBER,
3332 p_po_header_id IN NUMBER,
3333 p_po_release_id IN NUMBER,
3334 p_po_line_id IN NUMBER,
3335 p_shipment_header_id IN NUMBER,
3336 p_oe_order_header_id IN NUMBER,
3337 p_item_id IN NUMBER,
3338 p_rcv_qty IN NUMBER,
3339 p_rcv_sec_qty IN NUMBER,
3340 p_rcv_uom IN VARCHAR2,
3341 p_rcv_uom_code IN VARCHAR2,
3342 p_rcv_sec_uom IN VARCHAR2,
3343 p_rcv_sec_uom_code IN VARCHAR2,
3344 p_source_type IN VARCHAR2,
3345 p_subinventory IN VARCHAR2,
3346 p_locator_id IN NUMBER,
3347 p_transaction_temp_id IN NUMBER,
3348 p_lot_control_code IN NUMBER,
3349 p_serial_control_code IN NUMBER,
3350 p_lpn_id IN NUMBER,
3351 p_revision IN VARCHAR2,
3352 x_status OUT NOCOPY VARCHAR2,
3353 x_message OUT NOCOPY VARCHAR2,
3354 p_inv_item_id IN NUMBER DEFAULT NULL,
3355 p_item_desc IN VARCHAR2 DEFAULT NULL,
3356 p_location_id IN NUMBER DEFAULT NULL,
3357 p_is_expense IN VARCHAR2 DEFAULT NULL,
3358 p_project_id IN NUMBER default null,
3359 p_task_id IN NUMBER default null,
3360 p_country_code IN VARCHAR2 DEFAULT NULL
3361 )
3362 IS
3363 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
3364 l_msg_count NUMBER;
3365 l_msg_data VARCHAR2(400);
3366 l_label_status VARCHAR2(500);
3367 l_progress VARCHAR2(10);
3368 l_txn_id_tbl inv_label.transaction_id_rec_type;
3369 l_counter NUMBER := 0;
3370 l_subinventory VARCHAR2(10);
3371 l_locator_id NUMBER;
3372
3373 CURSOR c_rti_txn_id IS
3374 /* Bug 2443163 */
3375 /* SELECT MIN(rti.interface_transaction_id) */
3376 /* Group BY LPN_ID is changed for Express Receipts */
3377 /* Also duplicate print of LPN labels is avoided */
3378
3379 SELECT MAX(rti.interface_transaction_id)
3380 FROM rcv_transactions_interface rti
3381 WHERE rti.group_id = INV_rcv_common_apis.g_rcv_global_var.interface_group_id
3382 GROUP BY decode(p_source_type, 'ASNEXP',rti.interface_transaction_id,'SHIPMENTEXP',rti.interface_transaction_id,null) ;
3383 -- GROUP BY rti.lpn_id;
3384 BEGIN
3385 x_status := fnd_api.g_ret_sts_success;
3386 l_progress := '10';
3387
3388 IF inv_rcv_common_apis.g_po_startup_value.sob_id IS NULL THEN
3389 select ood.set_of_books_id
3390 into inv_rcv_common_apis.g_po_startup_value.sob_id
3391 FROM org_organization_definitions ood,
3392 gl_sets_of_books sob
3393 WHERE organization_id = p_organization_id
3394 AND sob.set_of_books_id = ood.set_of_books_id;
3395 END IF;
3396
3397 l_progress := '10';
3398 -- first check if the transaction date satisfies the validation.
3399 inv_rcv_common_apis.validate_trx_date(p_trx_date => Sysdate,
3400 p_organization_id => p_organization_id,
3401 p_sob_id => inv_rcv_common_apis.g_po_startup_value.sob_id,
3402 x_return_status => x_status,
3403 x_error_code => x_message);
3404
3405 IF x_status <> fnd_api.g_ret_sts_success THEN
3406 RETURN;
3407 END IF;
3408 IF p_shipment_header_id IS NULL THEN -- Added this check to fix bug no. 2159179
3409 IF p_po_header_id IS NULL AND p_item_id IS NULL THEN
3410 x_status := fnd_api.g_ret_sts_error;
3411 RETURN;
3412 END IF;
3413 END IF;
3414
3415 l_progress := '30';
3416 IF p_po_header_id IS NOT NULL
3417 AND p_source_type <> 'ASNEXP'
3418 AND p_source_type <> 'ASNCONFM' -- bug fix 2129249
3419 THEN
3420 l_progress := '40';
3421 IF p_item_id IS NULL AND p_item_desc IS NULL THEN
3422 x_status := fnd_api.g_ret_sts_error;
3423 RETURN;
3424 END IF;
3425 l_subinventory := p_subinventory;
3426 l_locator_id := p_locator_id;
3427 IF p_location_id IS NOT NULL THEN
3428 l_subinventory := '';
3429 l_locator_id := '';
3430 END IF;
3431 create_po_drct_dlvr_rti_rec(p_move_order_header_id => p_move_order_header_id,
3432 p_organization_id => p_organization_id,
3433 p_po_header_id => p_po_header_id,
3434 p_po_release_id => p_po_release_id,
3435 p_po_line_id => p_po_line_id,
3436 p_po_line_location_id => NULL,
3437 p_po_distribution_id => NULL,
3438 p_item_id => p_item_id,
3439 p_rcv_qty => p_rcv_qty,
3440 p_rcv_sec_qty => p_rcv_sec_qty,
3441 p_rcv_uom => p_rcv_uom,
3442 p_rcv_uom_code => p_rcv_uom_code,
3443 p_rcv_sec_uom => p_rcv_sec_uom,
3444 p_rcv_sec_uom_code => p_rcv_sec_uom_code,
3445 p_source_type => p_source_type,
3446 p_subinventory => l_subinventory,
3447 p_locator_id => l_locator_id,
3448 p_transaction_temp_id => p_transaction_temp_id,
3449 p_lot_control_code => p_lot_control_code,
3450 p_serial_control_code => p_serial_control_code,
3451 p_lpn_id => p_lpn_id,
3452 p_revision => p_revision,
3453 x_status => l_return_status,
3454 x_message => x_message,
3455 p_inv_item_id => p_inv_item_id,
3456 p_item_desc => p_item_desc,
3457 p_location_id => p_location_id,
3458 p_is_expense => p_is_expense,
3459 p_project_id => p_project_id,
3460 p_task_id => p_task_id,
3461 p_country_code => p_country_code);
3462
3463 IF l_return_status = FND_API.g_ret_sts_error THEN
3464 FND_MESSAGE.SET_NAME('INV', 'INV_RCV_CREATE_PO_RTI_FAIL'); -- MSGTBD
3465 FND_MSG_PUB.ADD;
3466 RAISE FND_API.G_EXC_ERROR;
3467 END IF;
3468 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
3469 FND_MESSAGE.SET_NAME('INV', 'INV_RCV_CREATE_PO_RTI_FAIL'); -- MSGTBD
3470 FND_MSG_PUB.ADD;
3471 RAISE FND_API.g_exc_unexpected_error;
3472 END IF;
3473
3474 ELSIF p_shipment_header_id IS NOT NULL THEN
3475 l_progress := '50';
3476
3477 IF p_source_type = 'ASNEXP' OR
3478 p_source_type = 'SHIPMENTEXP' OR
3479 p_source_type = 'SHIPMENT' OR
3480 p_source_type = 'REQEXP' THEN
3481
3482 IF p_source_type = 'ASNEXP' THEN
3483
3484 create_asn_exp_dd_intf_rec
3485 (p_move_order_header_id => p_move_order_header_id,
3486 p_organization_id => p_organization_id,
3487 p_shipment_header_id => p_shipment_header_id,
3488 p_po_header_id => p_po_header_id,
3489 p_source_type => 'ASN',
3490 p_subinventory => p_subinventory,
3491 p_locator_id => p_locator_id,
3492 p_lpn_id => p_lpn_id,
3493 p_transaction_temp_id => p_transaction_temp_id,
3494 x_status => l_return_status,
3495 x_message => l_msg_data,
3496 p_project_id => p_project_id,
3497 p_task_id => p_task_id,
3498 p_country_code => p_country_code
3499 );
3500
3501 IF l_return_status = FND_API.g_ret_sts_error THEN
3502 FND_MESSAGE.SET_NAME('INV', 'INV_RCV_CREATE_ASNEXP_RTI_FAIL'); -- MSGTBD
3503 FND_MSG_PUB.ADD;
3504 RAISE FND_API.G_EXC_ERROR;
3505 END IF;
3506
3507
3508 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
3509 FND_MESSAGE.SET_NAME('INV', 'INV_RCV_CREATE_ASNEXP_RTI_FAIL'); -- MSGTBD
3510 FND_MSG_PUB.ADD;
3511 RAISE FND_API.g_exc_unexpected_error;
3512 END IF;
3513 ELSE
3514 l_progress := '50';
3515
3516 create_asn_exp_dd_intf_rec
3517 (p_move_order_header_id => p_move_order_header_id,
3518 p_organization_id => p_organization_id,
3519 p_shipment_header_id => p_shipment_header_id,
3520 p_po_header_id => p_po_header_id,
3521 p_source_type => 'INTERNAL',
3522 p_subinventory => p_subinventory,
3523 p_locator_id => p_locator_id,
3524 p_lpn_id => p_lpn_id,
3525 p_transaction_temp_id => p_transaction_temp_id,
3526 x_status => l_return_status,
3527 x_message => l_msg_data,
3528 p_project_id => p_project_id,
3529 p_task_id => p_task_id,
3530 p_country_code => p_country_code
3531 );
3532
3533 IF l_return_status = FND_API.g_ret_sts_error THEN
3534 FND_MESSAGE.SET_NAME('INV', 'INV_RCV_CREATE_INTSHIPEXP_RTI_FAIL'); -- MSGTBD
3535 FND_MSG_PUB.ADD;
3536 RAISE FND_API.G_EXC_ERROR;
3537 END IF;
3538
3539
3540 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
3541 FND_MESSAGE.SET_NAME('INV', 'INV_RCV_CREATE_INTSHIPEXP_RTI_FAIL'); -- MSGTBD
3542 FND_MSG_PUB.ADD;
3543 RAISE FND_API.g_exc_unexpected_error;
3544 END IF;
3545
3546 END IF;
3547
3548 ELSIF p_source_type = 'ASNCONFM' THEN
3549 l_progress := '60';
3550
3551 create_asn_con_dd_intf_rec
3552 (p_move_order_header_id => p_move_order_header_id,
3553 p_organization_id => p_organization_id,
3554 p_shipment_header_id => p_shipment_header_id,
3555 p_po_header_id => p_po_header_id,
3556 p_item_id => p_item_id,
3557 p_rcv_qty => p_rcv_qty,
3558 p_rcv_uom => p_rcv_uom,
3559 p_rcv_uom_code => p_rcv_uom_code,
3560 p_source_type => 'ASN',
3561 p_subinventory => p_subinventory,
3562 p_locator_id => p_locator_id,
3563 p_lpn_id => p_lpn_id,
3564 p_lot_control_code => p_lot_control_code,
3565 p_serial_control_code => p_serial_control_code,
3566 p_revision => p_revision,
3567 p_transaction_temp_id => p_transaction_temp_id,
3568 x_status => l_return_status,
3569 x_message => l_msg_data,
3570 p_project_id => p_project_id,
3571 p_task_id => p_task_id,
3572 p_country_code => p_country_code,
3573 p_item_desc => p_item_desc
3574 );
3575
3576 IF l_return_status = FND_API.g_ret_sts_error THEN
3577 FND_MESSAGE.SET_NAME('INV', 'INV_RCV_CRT_ASNCON_RTI_FAIL'); -- MSGTBD
3578 FND_MSG_PUB.ADD;
3579 RAISE FND_API.G_EXC_ERROR;
3580 END IF;
3581
3582
3583 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
3584 FND_MESSAGE.SET_NAME('INV', 'INV_RCV_CREATE_INTSHIPEXP_RTI_FAIL'); -- MSGTBD
3585 FND_MSG_PUB.ADD;
3586 RAISE FND_API.g_exc_unexpected_error;
3587 END IF;
3588
3589
3590 ELSE
3591 create_int_shp_dr_del_rti_rec(p_move_order_header_id => p_move_order_header_id,
3592 p_organization_id => p_organization_id,
3593 p_shipment_header_id => p_shipment_header_id,
3594 p_shipment_line_id => NULL,
3595 p_item_id => p_item_id,
3596 p_rcv_qty => p_rcv_qty,
3597 p_rcv_uom => p_rcv_uom,
3598 p_rcv_uom_code => p_rcv_uom_code,
3599 p_source_type => p_source_type,
3600 p_subinventory => p_subinventory,
3601 p_locator_id => p_locator_id,
3602 p_transaction_temp_id => p_transaction_temp_id,
3603 p_lot_control_code => p_lot_control_code,
3604 p_serial_control_code => p_serial_control_code,
3605 p_lpn_id => p_lpn_id,
3606 p_revision => p_revision,
3607 p_project_id => p_project_id,
3608 p_task_id => p_task_id,
3609 x_status => l_return_status,
3610 x_message => x_message,
3611 p_country_code => p_country_code);
3612 IF l_return_status = FND_API.g_ret_sts_error THEN
3613 FND_MESSAGE.SET_NAME('INV', 'INV_RCV_CRT_INSHP_RTI_FAIL'); -- MSGTBD
3614 FND_MSG_PUB.ADD;
3615 RAISE FND_API.G_EXC_ERROR;
3616 END IF;
3617 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
3618 FND_MESSAGE.SET_NAME('INV', 'INV_RCV_CRT_INSHP_RTI_FAIL'); -- MSGTBD
3619 FND_MSG_PUB.ADD;
3620 RAISE FND_API.g_exc_unexpected_error;
3621 END IF;
3622
3623 END IF;
3624
3625
3626 ELSIF p_oe_order_header_id IS NOT NULL THEN
3627 l_progress := '60';
3628 create_rma_drct_dlvr_rti_rec(p_move_order_header_id => p_move_order_header_id,
3629 p_organization_id => p_organization_id,
3630 p_oe_order_header_id => p_oe_order_header_id,
3631 p_oe_order_line_id => NULL,
3632 p_item_id => p_item_id,
3633 p_rcv_qty => p_rcv_qty,
3634 p_rcv_uom => p_rcv_uom,
3635 p_rcv_uom_code => p_rcv_uom_code,
3636 p_source_type => p_source_type,
3637 p_subinventory => p_subinventory,
3638 p_locator_id => p_locator_id,
3639 p_transaction_temp_id => p_transaction_temp_id,
3640 p_lot_control_code => p_lot_control_code,
3641 p_serial_control_code => p_serial_control_code,
3642 p_lpn_id => p_lpn_id,
3643 p_revision => p_revision,
3644 x_status => l_return_status,
3645 x_message => x_message,
3646 p_project_id => p_project_id,
3647 p_task_id => p_task_id,
3648 p_country_code => p_country_code );
3649 IF l_return_status = FND_API.g_ret_sts_error THEN
3650 FND_MESSAGE.SET_NAME('INV', 'INV_RCV_CREATE_RMA_RTI_FAIL'); -- MSGTBD
3651 FND_MSG_PUB.ADD;
3652 RAISE FND_API.G_EXC_ERROR;
3653 END IF;
3654 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
3655 FND_MESSAGE.SET_NAME('INV', 'INV_RCV_CREATE_RMA_RTI_FAIL'); -- MSGTBD
3656 FND_MSG_PUB.ADD;
3657 RAISE FND_API.g_exc_unexpected_error;
3658 END IF;
3659 END IF;
3660
3661 l_progress := '80';
3662 x_status := l_return_status; -- l_return_status can be 'W', we want to carry that over
3663
3664 -- calling label printing API
3665 IF l_return_status <> FND_API.g_ret_sts_error THEN
3666 l_progress := '80';
3667
3668
3669 l_counter := 1;
3670 OPEN c_rti_txn_id;
3671 LOOP
3672 FETCH c_rti_txn_id
3673 INTO l_txn_id_tbl(l_counter);
3674 EXIT WHEN c_rti_txn_id%notfound;
3675 l_counter := l_counter + 1;
3676 END LOOP;
3677 CLOSE c_rti_txn_id;
3678
3679 inv_label.print_label
3680 (x_return_status => l_return_status
3681 , x_msg_count => l_msg_count
3682 , x_msg_data => l_msg_data
3683 , x_label_status => l_label_status
3684 , p_api_version => 1.0
3685 , p_print_mode => 1
3686 , p_business_flow_code => 1
3687 , p_transaction_id => l_txn_id_tbl
3688 );
3689
3690 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3691 FND_MESSAGE.SET_NAME('INV', 'INV_RCV_CRT_PRINT_LAB_FAIL'); -- MSGTBD
3692 FND_MSG_PUB.ADD;
3693 x_status := 'W';
3694 END IF;
3695
3696 END IF;
3697
3698
3699 EXCEPTION
3700 WHEN fnd_api.g_exc_error THEN
3701 x_status := fnd_api.g_ret_sts_error;
3702 fnd_msg_pub.count_and_get
3703 (p_encoded => FND_API.g_false,
3704 p_count => l_msg_count,
3705 p_data => x_message
3706 );
3707
3708 WHEN fnd_api.g_exc_unexpected_error THEN
3709 x_status := fnd_api.g_ret_sts_unexp_error ;
3710 fnd_msg_pub.count_and_get
3711 (p_encoded => FND_API.g_false,
3712 p_count => l_msg_count,
3713 p_data => x_message
3714 );
3715
3716 WHEN OTHERS THEN
3717 x_status := fnd_api.g_ret_sts_unexp_error ;
3718 fnd_msg_pub.count_and_get
3719 (p_encoded => FND_API.g_false,
3720 p_count => l_msg_count,
3721 p_data => x_message
3722 );
3723
3724 END create_direct_rti_rec;
3725
3726 /*
3727 PROCEDURE pack_lpn_txn
3728 IS
3729 l_proc_msg VARCHAR2(400);
3730 l_return_status NUMBER;
3731 BEGIN
3732
3733 l_return_status := inv_lpn_trx_pub.process_lpn_trx
3734 (p_trx_hdr_id => inv_rcv_common_apis.g_rcv_global_var.transaction_header_id,
3735 p_mode => 2, -- putaway mode
3736 p_commit => 'F',
3737 x_proc_msg => l_proc_msg);
3738
3739
3740 END pack_lpn_txn;
3741 */
3742
3743
3744 END gml_rcv_dir_rcpt_apis;