[Home] [Help]
PACKAGE BODY: APPS.CSD_RECEIVE_PVT
Source
1 PACKAGE BODY csd_receive_pvt AS
2 /* $Header: csdvrcvb.pls 120.2.12010000.2 2008/10/30 08:00:37 subhat ship $ */
3
4 -- ---------------------------------------------------------
5 -- Define global variables
6 -- ---------------------------------------------------------
7 g_pkg_name CONSTANT VARCHAR2 (30) := 'CSD_RECEIVE_PVT';
8 g_file_name CONSTANT VARCHAR2 (12) := 'csdvrcvb.pls';
9 g_debug_level CONSTANT NUMBER := csd_gen_utility_pvt.g_debug_level;
10 g_prcess_sts_pending CONSTANT VARCHAR2(10) := 'PENDING';
11 g_rcpt_source_customer CONSTANT VARCHAR2(10) := 'CUSTOMER';
12 g_txn_type_new CONSTANT VARCHAR2(10) := 'NEW';
13
14 /*****
15 PENDING',
16 'CUSTOMER', 'NEW'
17 **/
18
19 FUNCTION check_group_id (
20 p_group_id IN NUMBER
21 )
22 RETURN BOOLEAN;
23
24 PROCEDURE dump_receive_tbl (
25 p_receive_tbl IN csd_receive_util.rcv_tbl_type,
26 p_level NUMBER,
27 p_module VARCHAR2
28 );
29
30 PROCEDURE log_error_stack;
31
32 /*-----------------------------------------------------------------------------------------------------------*/
33 /* procedure name: RECEIVE_ITEM */
34 /* description : Populates the Receive open interface tables and calls the Receive processor. This handles */
35 /* all types of receives a) Direct b) Standard */
36 /* Called from : CSDREPLN.pld. logistics tab.*/
37 /* Input Parm : p_api_version NUMBER Required Api Version number */
38 /* p_init_msg_list VARCHAR2 Optional Initializes message stack if fnd_api.g_true, */
39 /* default value is fnd_api.g_false */
40 /* p_commit VARCHAR2 Optional Commits in API if fnd_api.g_true, default */
41 /* fnd_api.g_false */
42 /* p_validation_level NUMBER Optional API uses this parameter to determine which */
43 /* validation steps must be done and which steps */
44 /* should be skipped. */
45 /* p_receive_rec CSD_RECEIVE_UTIL.RCV_REC_TYPE Required */
46 /* Output Parm : x_return_status VARCHAR2 Return status after the call. The status can be*/
47 /* fnd_api.g_ret_sts_success (success) */
48 /* fnd_api.g_ret_sts_error (error) */
49 /* fnd_api.g_ret_sts_unexp_error (unexpected) */
50 /* x_msg_count NUMBER Number of messages in the message stack */
51 /* x_msg_data VARCHAR2 Message text if x_msg_count >= 1 */
52 /* x_rcv_error_msg_tbl Returns table of error messages */
53 /*-----------------------------------------------------------------------------------------------------------*/
54 PROCEDURE receive_item (
55 p_api_version IN NUMBER,
56 p_init_msg_list IN VARCHAR2,
57 p_commit IN VARCHAR2,
58 p_validation_level IN NUMBER,
59 x_return_status OUT NOCOPY VARCHAR2,
60 x_msg_count OUT NOCOPY NUMBER,
61 x_msg_data OUT NOCOPY VARCHAR2,
62 x_rcv_error_msg_tbl OUT NOCOPY csd_receive_util.rcv_error_msg_tbl,
63 p_receive_tbl IN OUT NOCOPY csd_receive_util.rcv_tbl_type
64 )
65 IS
66 l_api_version_number CONSTANT NUMBER := 1.0;
67 l_api_name CONSTANT VARCHAR2 (30) := 'RECEIVE_ITEM';
68 l_index NUMBER;
69 l_request_group_id NUMBER;
70 l_retcode NUMBER;
71 BEGIN
72
73 -- Standard Start of API savepoint
74 SAVEPOINT sp_receive_item;
75
76
77 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
78 THEN
79 fnd_log.STRING (fnd_log.level_procedure,
80 'CSD.PLSQL.CSD_RECEIVE_PVT.RECEIVE_ITEM.BEGIN',
81 'Entered RECEIVE_ITEM'
82 );
83 END IF;
84
85 dump_receive_tbl ( p_receive_tbl,
86 fnd_log.level_statement,
87 'CSD.PLSQL.CSD_RECEIVE_PVT.RECEIVE_ITEM.BEGIN'
88 );
89 IF fnd_api.to_boolean (p_init_msg_list)
90 THEN
91 -- initialize message list
92 fnd_msg_pub.initialize;
93 END IF;
94
95 -- Standard call to check for call compatibility.
96 IF NOT fnd_api.compatible_api_call (l_api_version_number,
97 p_api_version,
98 l_api_name,
99 g_pkg_name
100 )
101 THEN
102 RAISE fnd_api.g_exc_unexpected_error;
103 END IF;
104
105 -- initialize return status
106 x_return_status := fnd_api.g_ret_sts_success;
107
108 /**********Program logic ******************/
109
110 --Validate all the records in the input table.
111 IF (fnd_log.level_event >= fnd_log.g_current_runtime_level)
112 THEN
113 fnd_log.STRING (fnd_log.level_event,
114 'CSD.PLSQL.CSD_RECEIVE_PVT.RECEIVE_ITEM',
115 'Validating Input'
116 );
117 END IF;
118 -------------Validate Input data.
119 FOR l_index IN p_receive_tbl.FIRST .. p_receive_tbl.LAST
120 LOOP
121 csd_receive_util.validate_rcv_input
122 (p_validation_level => fnd_api.g_valid_level_full,
123 x_return_status => x_return_status,
124 p_receive_rec => p_receive_tbl (l_index)
125 );
126 IF (x_return_status <> fnd_api.g_ret_sts_success)
127 THEN
128 RAISE fnd_api.g_exc_error;
129 END IF;
130
131 p_receive_tbl (l_index).routing_header_id :=
132 csd_receive_util.is_auto_rcv_available
133 (p_receive_tbl (l_index).inventory_item_id,
134 p_receive_tbl (l_index).to_organization_id,
135 p_receive_tbl (l_index).internal_order_flag,
136 p_receive_tbl (l_index).from_organization_id
137 );
138
139 IF (p_receive_tbl (l_index).routing_header_id = -1)
140 THEN
141 fnd_message.set_name ('CSD', 'CSD_AUTO_RECV_NOT_POSSIBLE');
142 fnd_msg_pub.ADD;
143 RAISE fnd_api.g_exc_error;
144 END IF;
145
146 END LOOP;
147
148 --------POpulate the interface tables.
149 IF (fnd_log.level_event >= fnd_log.g_current_runtime_level)
150 THEN
151 fnd_log.STRING (fnd_log.level_event,
152 'CSD.PLSQL.CSD_RECEIVE_PVT.RECEIVE_ITEM',
153 'Populating Interface tables'
154 );
155 END IF;
156
157 populate_rcv_intf_tbls
158 (p_api_version => 1.0,
159 p_init_msg_list => fnd_api.g_false,
160 p_validation_level => fnd_api.g_valid_level_full,
161 x_return_status => x_return_status,
162 x_msg_count => x_msg_count,
163 x_msg_data => x_msg_data,
164 p_receive_tbl => p_receive_tbl,
165 x_request_group_id => l_request_group_id
166 );
167
168 IF (x_return_status <> fnd_api.g_ret_sts_success)
169 THEN
170 RAISE fnd_api.g_exc_error;
171 END IF;
172
173 -- Call request online to invoke receiving processsor in online mode.
174 IF (fnd_log.level_event >= fnd_log.g_current_runtime_level)
175 THEN
176 fnd_log.STRING (fnd_log.level_event,
177 'CSD.PLSQL.CSD_RECEIVE_PVT.RECEIVE_ITEM',
178 'Calling the receive api online'
179 );
180 END IF;
181
182 rcv_req_online (p_api_version => 1.0,
183 p_init_msg_list => fnd_api.g_false,
184 p_commit => fnd_api.g_false,
185 p_validation_level => fnd_api.g_valid_level_full,
186 x_return_status => x_return_status,
187 x_msg_count => x_msg_count,
188 x_msg_data => x_msg_data,
189 p_request_group_id => l_request_group_id
190 );
191
192 IF (x_return_status <> fnd_api.g_ret_sts_success)
193 THEN
194 RAISE fnd_api.g_exc_error;
195 END IF;
196
197 IF (fnd_log.level_event >= fnd_log.g_current_runtime_level)
198 THEN
199 fnd_log.STRING
200 (fnd_log.level_event,
201 'CSD.PLSQL.CSD_RECEIVE_PVT.RECEIVE_ITEM',
202 'Checking the errors in interface tables after the receive process'
203 );
204 END IF;
205
206 --Call Check_Rcv_Errors to check the errors in the PO_INTERFACE_ERRORS table.
207 csd_receive_util.check_rcv_errors
208 (x_return_status => x_return_status,
209 x_rcv_error_msg_tbl => x_rcv_error_msg_tbl,
210 p_request_group_id => l_request_group_id
211 );
212
213 IF (x_return_status <> fnd_api.g_ret_sts_success)
214 THEN
215 RAISE fnd_api.g_exc_error;
216 END IF;
217
218
219 -- Delete the interface table records created.
220 /************8
221
222 DELETE_INTF_TBLS(
223 x_return_status => x_return_status,
224 p_request_group_id => l_request_group_id
225 );
226 l_request_group_id := null;
227 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
228 RAISE FND_API.G_EXC_ERROR;
229 END IF;
230 *****************/
231
232
233 -- Standard call to get message count and IF count is get message info.
234 fnd_msg_pub.count_and_get (p_count => x_msg_count,
235 p_data => x_msg_data);
236
237 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
238 THEN
239 fnd_log.STRING (fnd_log.level_procedure,
240 'CSD.PLSQL.CSD_RECEIVE_PVT.RECEIVE_ITEM.END',
241 'Leaving RECEIVE_ITEM'
242 );
243 END IF;
244
245 --Commit the changes.
246 IF (p_commit = fnd_api.g_true)
247 THEN
248 COMMIT;
249 END IF;
250
251 EXCEPTION
252 WHEN fnd_api.g_exc_error
253 THEN
254 x_return_status := fnd_api.g_ret_sts_error;
255 ROLLBACK TO sp_receive_item;
256 fnd_msg_pub.count_and_get (p_count => x_msg_count,
257 p_data => x_msg_data);
258
259 /*************************88
260 if(l_request_group_id is not null) then
261 DELETE_INTF_TBLS(
262 x_return_status => x_return_status,
263 p_request_group_id => l_request_group_id
264 );
265 end if;
266 *****************************/
267
268 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)
269 THEN
270 fnd_log.STRING (fnd_log.level_error,
271 'CSD.PLSQL.CSD_RECEIVE_PVT.RECEIVE_ITEM',
272 'EXC_ERROR in Receive_Item[' || x_msg_data || ']'
273 );
274 END IF;
275 WHEN fnd_api.g_exc_unexpected_error
276 THEN
277 x_return_status := fnd_api.g_ret_sts_unexp_error;
278 ROLLBACK TO sp_receive_item;
279 fnd_msg_pub.count_and_get (p_count => x_msg_count,
280 p_data => x_msg_data);
281
282 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
283 THEN
284 fnd_log.STRING (fnd_log.level_exception,
285 'CSD.PLSQL.CSD_RECEIVE_PVT.RECEIVE_ITEM',
286 'EXC_UNEXPECTED_ERROR in Receive_Item[' || x_msg_data || ']'
287 );
288 END IF;
289 WHEN OTHERS
290 THEN
291 x_return_status := fnd_api.g_ret_sts_unexp_error;
292 ROLLBACK TO sp_receive_item;
293
294 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
295 THEN
296 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
297 END IF;
298
299 fnd_msg_pub.count_and_get (p_count => x_msg_count,
300 p_data => x_msg_data);
301
302 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)
303 THEN
304 fnd_log.STRING (fnd_log.level_unexpected,
305 'CSD.PLSQL.CSD_RECEIVE_PVT.RECEIVE_ITEM',
306 'SQL Message in Receive_Item[' || SQLERRM || ']'
307 );
308 END IF;
309 END receive_item;
310
311 /*-----------------------------------------------------------------------------------------------------------*/
312 /* procedure name: POPULATE_INTF_TBLS */
313 /* description : Inserts records into open interface tables for receiving. */
314 /* Called from : CSD_RCV_PVT.RECEIVE_ITEM api */
315 /* Input Parm : p_api_version NUMBER Required Api Version number */
316 /* p_init_msg_list VARCHAR2 Optional Initializes message stack if fnd_api.g_true, */
317 /* default value is fnd_api.g_false */
318 /* p_commit VARCHAR2 Optional Commits in API if fnd_api.g_true, default */
319 /* fnd_api.g_false */
320 /* p_validation_level NUMBER Optional API uses this parameter to determine which */
321 /* validation steps must be done and which steps */
322 /* should be skipped. */
323 /* p_receive_rec CSD_RECEIVE_UTIL.RCV_REC_TYPE Required */
324 /* Output Parm : x_return_status VARCHAR2 Return status after the call. The status can be*/
325 /* fnd_api.g_ret_sts_success (success) */
326 /* fnd_api.g_ret_sts_error (error) */
327 /* fnd_api.g_ret_sts_unexp_error (unexpected) */
328 /* x_msg_count NUMBER Number of messages in the message stack */
329 /* x_msg_data VARCHAR2 Message text if x_msg_count >= 1 */
330 /* x_request_group_id NUMBER Required */
331 /*-----------------------------------------------------------------------------------------------------------*/
332 PROCEDURE populate_rcv_intf_tbls (
333 p_api_version IN NUMBER,
334 p_init_msg_list IN VARCHAR2,
335 p_validation_level IN NUMBER,
336 x_return_status OUT NOCOPY VARCHAR2,
337 x_msg_count OUT NOCOPY NUMBER,
338 x_msg_data OUT NOCOPY VARCHAR2,
339 p_receive_tbl IN csd_receive_util.rcv_tbl_type,
340 x_request_group_id OUT NOCOPY NUMBER
341 )
342 IS
343 pragma AUTONOMOUS_TRANSACTION;
344 l_api_version_number CONSTANT NUMBER := 1.0;
345 l_api_name CONSTANT VARCHAR2 (30)
346 := 'Populate_Rcv_Intf_Tbls';
347 l_hdr_intf_id NUMBER;
348 i NUMBER;
349 p_receive_rec csd_receive_util.rcv_rec_type;
350 l_source_code VARCHAR2 (240);
351 l_source_line_id NUMBER := 1;
352 l_txn_tmp_id NUMBER;
353 l_source_header_id NUMBER := 1;
354 l_process_sts_pending CONSTANT VARCHAR2(10) := 'PENDING';
355 l_rcpt_source_customer CONSTANT VARCHAR2(10) := 'CUSTOMER';
356 l_txn_Type_new CONSTANT VARCHAR2(10) := 'NEW';
357 l_validation_flag CONSTANT VARCHAR2(1) := 'Y';
358
359 l_lot_expiration_date DATE;
360 l_process_flag CONSTANT VARCHAR2 (1) := '1'; -- 1 means process
361 l_intf_txn_id NUMBER;
362 sql_str VARCHAR2 (2000);
363 exec_flag BOOLEAN;
364 l_emp_id NUMBER;
365 l_receipt_source_code VARCHAR2(30);
366 l_source_document_code VARCHAR2(30);
367 l_org_id NUMBER;
368
369 cursor c_get_org_id (p_order_line_id in number) is
370 select org_id
371 from oe_order_lines_all
372 where line_id = p_order_line_id;
373
374 BEGIN
375
376
377 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
378 THEN
379 fnd_log.STRING
380 (fnd_log.level_procedure,
381 'CSD.PLSQL.CSD_RECEIVE_PVT.POPULATE_RCV_INTF_TBLS.BEGIN',
382 'Entered Populate_Rcv_Intf_Tbls'
383 );
384 END IF;
385
386 l_source_code := 'CSD';
387
388 IF fnd_api.to_boolean (p_init_msg_list)
389 THEN
390 -- initialize message list
391 fnd_msg_pub.initialize;
392 END IF;
393
394 -- Standard call to check for call compatibility.
395 IF NOT fnd_api.compatible_api_call (l_api_version_number,
396 p_api_version,
397 l_api_name,
398 g_pkg_name
399 )
400 THEN
401 RAISE fnd_api.g_exc_unexpected_error;
402 END IF;
403
404 -- initialize return status
405 x_return_status := fnd_api.g_ret_sts_success;
406
407 ---Program logic.......
408 IF (fnd_log.level_event >= fnd_log.g_current_runtime_level)
409 THEN
410 fnd_log.STRING (fnd_log.level_event,
411 'CSD.PLSQL.CSD_RECEIVE_PVT.POPULATE_RCV_INTF_TBLS',
412 'Inserting header interface table data'
413 );
414 END IF;
415
416 if(p_receive_tbl (1).internal_order_flag = 'Y') then
417 l_source_document_code := 'REQ';
418 l_receipt_source_code := 'INTERNAL ORDER';
419 else
420 l_source_document_code := 'RMA';
421 l_receipt_source_Code := 'CUSTOMER';
422 end if;
423
424 -- Insert header record.
425 INSERT INTO rcv_headers_interface
426 (header_interface_id,
427 GROUP_ID,
428 ship_to_organization_id,
429 expected_receipt_date, last_update_date,
430 last_updated_by, last_update_login, creation_date,
431 created_by, validation_flag, processing_status_code,
432 receipt_source_code, transaction_type,
433 -- added for internal orders.
434 shipped_Date,
435 shipment_num
436 )
437 VALUES (rcv_headers_interface_s.NEXTVAL,
438 rcv_interface_groups_s.NEXTVAL,
439 p_receive_tbl (1).to_organization_id,
440 p_receive_tbl (1).expected_receipt_date, SYSDATE,
441 fnd_global.user_id, fnd_global.login_id, SYSDATE,
442 fnd_global.user_id, l_validation_flag, l_process_sts_pending,
443 l_receipt_source_code, l_txn_Type_new,
444 -- added for internal orders.
445 p_receive_tbl (1).shipped_date,
446 p_receive_tbl (1).shipment_number
447 )
448 RETURNING header_interface_id, GROUP_ID
449 INTO l_hdr_intf_id, x_request_group_id;
450
451 --
452 -- Dynamic sql is being used to ensure that the code is not dependent on
453 -- the 11.5.0 PO code. This will be only run time dependent.(functional dependence)
454 sql_str :=
455 'UPDATE RCV_HEADERS_INTERFACE SET HEADER_INTERFACE_ID=HEADER_INTERFACE_ID';
456 exec_flag := FALSE;
457
458 IF (p_receive_tbl (1).customer_id IS NOT NULL)
459 THEN
460 sql_str :=
461 sql_str
462 || ',CUSTOMER_ID='
463 || TO_CHAR (p_receive_tbl (1).customer_id);
464 exec_flag := TRUE;
465 END IF;
466
467 IF (p_receive_tbl (1).customer_site_id IS NOT NULL)
468 THEN
469 sql_str :=
470 sql_str
471 || ',CUSTOMER_SITE_ID = '
472 || TO_CHAR (p_receive_tbl (1).customer_site_id);
473 exec_flag := TRUE;
474 END IF;
475
476 /**********************Commented for 4277749
477 sql_str := sql_str || ' WHERE HEADER_INTERFACE_ID=' || l_hdr_intf_id;
478 ***************************************/
479
480 -- bug fix for performance bug 4277749 begin
481 sql_str := sql_str || ' WHERE HEADER_INTERFACE_ID= :1';
482 -- bug fix for performance bug 4277749 end
483
484 IF (exec_flag)
485 THEN
486 IF (fnd_log.level_event >= fnd_log.g_current_runtime_level)
487 THEN
488 fnd_log.STRING
489 (fnd_log.level_event,
490 'CSD.PLSQL.CSD_RECEIVE_PVT.POPULATE_RCV_INTF_TBLS',
491 'Calling execute immediate with sql['
492 || sql_str
493 || ']'
494 );
495 END IF;
496
497 -- bug fix for performance bug 4277749 , added using clause
498 EXECUTE IMMEDIATE sql_str using l_hdr_intf_id;
499 END IF;
500
501 IF (fnd_log.level_event >= fnd_log.g_current_runtime_level)
502 THEN
503 fnd_log.STRING (fnd_log.level_event,
504 'CSD.PLSQL.CSD_RECEIVE_PVT.POPULATE_RCV_INTF_TBLS',
505 'Inserting transactions interface table data'
506 );
507 END IF;
508
509 csd_receive_util.get_employee_id (fnd_global.user_id, l_emp_id);
510
514 p_receive_rec := p_receive_tbl (i);
511 --Insert data into the transactions interface table.
512 FOR i IN p_receive_tbl.FIRST .. p_receive_tbl.LAST
513 LOOP
515 p_receive_rec.employee_id := l_emp_id;
516 csd_receive_util.get_rcv_item_params (p_receive_rec);
517
518 -- Derive the Org id
519 -- MOAC change Bug#4245577
520 OPEN c_get_org_id (p_receive_rec.order_line_id);
521 FETCH c_get_org_id INTO l_org_id;
522 CLOSE c_get_org_id;
523
524 if(p_receive_rec.internal_order_flag = 'Y') then
525 l_source_document_code := 'REQ';
526 l_receipt_source_code := 'INTERNAL ORDER';
527 else
528 l_source_document_code := 'RMA';
529 l_receipt_source_Code := 'CUSTOMER';
530 end if;
531 -- bug#7509332,12.1 FP, subhat.
532 -- Locator information is not passed to rcv_transactions_interface.
533 -- currently we insert the locator_id into location_id column. Where as locator_id should be
534 -- inserted into locator_id of rcv_transactions_interface.
535 -- the column descriptions from eTRM.
536 -- LOCATOR_ID NUMBER Destination locator unique identifier
537 -- LOCATION_ID NUMBER Receiving location unique identifier
538
539 INSERT INTO rcv_transactions_interface
540 (interface_transaction_id, header_interface_id,
541 GROUP_ID, transaction_date,
542 quantity, unit_of_measure,
543 oe_order_header_id,
544 document_num,
545 item_id,
546 item_revision,
547 to_organization_id,
548 ship_to_location_id,
549 subinventory, last_update_date,
550 last_updated_by, creation_date, created_by,
551 last_update_login, validation_flag,
552 source_document_code, interface_source_code,
553 auto_transact_code,
554 receipt_source_code,
555 transaction_type,
556 processing_status_code,
557 processing_mode_code,
558 transaction_status_code,
559 -- new columns to be updated,
560 category_id, uom_code,
561 employee_id,
562 primary_quantity,
563 primary_unit_of_measure,
564 routing_header_id, routing_step_id,
565 inspection_status_code,
566 destination_type_code, expected_receipt_date,
567 destination_context,
568 use_mtl_lot,
569 use_mtl_serial,
570 source_doc_quantity,
571 source_doc_unit_of_measure, oe_order_line_id,
572 --po_unit_price,
573 currency_code,
574 customer_id,
575 customer_site_id,
576 -- added for internal orders
577 requisition_line_id,
578 shipped_date,
579 shipment_num,
580 from_organization_id,
581 --location_id,
582 locator_id, --bug#7509332, 12.1 FP, subhat
583 deliver_to_location_id,
584 shipment_header_id,
585 shipment_line_id,
586 org_id -- MOAC change Bug#4245577
587 )
588 VALUES (rcv_transactions_interface_s.NEXTVAL, l_hdr_intf_id,
589 x_request_group_id, p_receive_rec.transaction_date,
590 p_receive_rec.quantity, p_receive_rec.unit_of_measure,
591 p_receive_rec.order_header_id,
592 p_receive_rec.doc_number,
593 p_receive_rec.inventory_item_id,
594 p_receive_rec.item_revision,
595 p_receive_rec.to_organization_id,
596 p_receive_rec.ship_to_location_id,
597 p_receive_rec.subinventory, SYSDATE,
598 fnd_global.user_id, SYSDATE, fnd_global.user_id,
599 fnd_global.login_id, 'Y',
600 l_source_document_code
601 , 'RCV' --Interface_source_Code
602 , 'DELIVER' -- auto _Transact_Code
603 , l_receipt_source_Code --receipt_source_code
604 , 'RECEIVE' --Transaction_type
605 , 'PENDING' -- processing_status_Code
606 , 'ONLINE' --processing_mode _Code
607 , 'PENDING' --transaction_status_Code
608 , p_receive_rec.category_id
609 , p_receive_rec.uom_code
610 , p_receive_rec.employee_id
611 , p_receive_rec.quantity -- Primary quantity
612 , p_receive_rec.primary_unit_of_measure -- primary unit of measure.
613 , 1------------temp--------- p_receive_rec.routing_header_id
614 , 1
618 'INVENTORY' -- destination_context
615 ,'NOT INSPECTED' -- inspection status code
616 ,'INVENTORY' -- destination_type code
617 , SYSDATE,
619 ,
620 p_receive_rec.lot_control_code,
621 p_receive_rec.serial_control_code,
622 p_receive_rec.quantity -- Source doc quantity
623 ,
624 p_receive_rec.unit_of_measure, -- source doc unit_of measure
625 p_receive_rec.order_line_id,
626 p_receive_rec.currency_code,
627 p_receive_rec.customer_id,
628 p_receive_rec.customer_site_id,
629 -- added for internal orders
630 p_receive_rec.requisition_line_id,
631 p_Receive_rec.shipped_date,
632 p_Receive_rec.shipment_number,
633 p_Receive_rec.from_organization_id,
634 p_Receive_rec.locator_id,
635 p_Receive_rec.deliver_to_location_id,
636 p_Receive_rec.shipment_header_id,
637 p_Receive_rec.shipment_line_id,
638 l_org_id -- MOAC change Bug#4245577
639 )
640 RETURNING interface_transaction_id
641 INTO l_intf_txn_id;
642
643 --
644 -- Dynamic sql is being used to ensure that the code is not dependent on
645 -- the 11.5.0 PO code. This will be only run time dependent.(functional dependence)
646 sql_str := NULL;
647
648 IF (p_receive_rec.order_number IS NOT NULL)
649 THEN
650 /**********************Commented for 4277749
651 sql_str :=
652 ' UPDATE RCV_TRANSACTIONS_INTERFACE SET OE_ORDER_NUM ='
653 || p_receive_rec.order_number
654 || ' WHERE INTERFACE_TRANSACTION_ID='
655 || l_intf_txn_id;
656 *******************************/
657 -- bug fix for performance bug 4277749 begin
658 sql_str :=
659 ' UPDATE RCV_TRANSACTIONS_INTERFACE SET OE_ORDER_NUM = :1'
660 || ' WHERE INTERFACE_TRANSACTION_ID=:2' ;
661
662 IF (fnd_log.level_event >= fnd_log.g_current_runtime_level)
663 THEN
664 fnd_log.STRING
665 (fnd_log.level_event,
666 'CSD.PLSQL.CSD_RECEIVE_PVT.POPULATE_RCV_INTF_TBLS',
667 'Calling execute immediate with sql['
668 || sql_str
669 || ']'
670 );
671 END IF;
672 EXECUTE IMMEDIATE sql_str using p_receive_rec.order_number,l_intf_txn_id;
673 -- bug fix for performance bug 4277749 End
674 END IF;
675
676 /**********************Commented for 4277749
677 IF (sql_str IS NOT NULL)
678 THEN
679 IF (fnd_log.level_event >= fnd_log.g_current_runtime_level)
680 THEN
681 fnd_log.STRING
682 (fnd_log.level_event,
683 'CSD.PLSQL.CSD_RECEIVE_PVT.POPULATE_RCV_INTF_TBLS',
684 'Calling execute immediate with sql['
685 || sql_str
686 || ']'
687 );
688 END IF;
689
690 EXECUTE IMMEDIATE sql_str;
691 END IF;
692 ************************************************/
693
694 IF (p_receive_rec.lot_number IS NOT NULL)
695 THEN
696 IF (p_receive_rec.serial_number IS NOT NULL)
697 THEN
698 SELECT mtl_material_transactions_s.NEXTVAL
699 INTO l_txn_tmp_id
700 FROM DUAL;
701 ELSE
702 l_txn_tmp_id := l_intf_txn_id;
703 END IF;
704
705 IF (fnd_log.level_event >= fnd_log.g_current_runtime_level)
706 THEN
707 fnd_log.STRING
708 (fnd_log.level_event,
709 'CSD.PLSQL.CSD_RECEIVE_PVT.POPULATE_RCV_INTF_TBLS',
710 'Inserting lot interface table data for ['
711 || l_intf_txn_id
712 || ']lot number['
713 || p_receive_rec.lot_number
714 || ']'
715 );
716 END IF;
717
718 INSERT INTO mtl_transaction_lots_interface
719 (transaction_interface_id, source_code,
720 source_line_id, last_update_date, last_updated_by,
721 creation_date, created_by, last_update_login,
722 lot_number, lot_expiration_date,
723 transaction_quantity, primary_quantity,
724 serial_transaction_temp_id
725 )
726 VALUES (l_intf_txn_id, l_source_code,
727 l_source_line_id, SYSDATE, fnd_global.user_id,
728 SYSDATE, fnd_global.user_id, fnd_global.login_id,
729 p_receive_rec.lot_number, l_lot_expiration_date,
730 p_receive_rec.quantity, p_receive_rec.quantity,
731 l_txn_tmp_id
732 );
736 sql_str :=
733 --
734 -- Dynamic sql is being used to ensure that the code is not dependent on
735 -- the 11.5.0 PO code. This will be only run time dependent.(functional dependence)
737 'UPDATE mtl_transaction_lots_interface SET product_code=''RCV'' ';
738 sql_str :=
739 sql_str
740 || ',product_transaction_id='
741 || l_intf_txn_id
742 -- bug fix for performance bug 4277749 changed the where clause
743 -- to use using clause
744 || ' Where transaction_interface_id = :1';
745
746 IF (fnd_log.level_event >= fnd_log.g_current_runtime_level)
747 THEN
748 fnd_log.STRING
749 (fnd_log.level_event,
750 'CSD.PLSQL.CSD_RECEIVE_PVT.POPULATE_RCV_INTF_TBLS',
751 'Calling execute immediate with sql['
752 || sql_str
753 || ']'
754 );
755 END IF;
756
757 -- bug fix for performance bug 4277749 changed the where clause
758 -- to use using clause
759 EXECUTE IMMEDIATE sql_str using l_intf_txn_id;
760
761 END IF;
762
763 --If the serial controlled rec is not null then insert records
764 -- into the serial numbers interface table
765 IF (p_receive_rec.serial_number IS NOT NULL)
766 THEN
767 IF (p_receive_rec.lot_number IS NULL)
768 THEN
769 l_txn_tmp_id := l_intf_txn_id;
770 END IF;
771
772 IF (fnd_log.level_event >= fnd_log.g_current_runtime_level)
773 THEN
774 fnd_log.STRING
775 (fnd_log.level_event,
776 'CSD.PLSQL.CSD_RECEIVE_PVT.POPULATE_RCV_INTF_TBLS',
777 'Inserting serial interface table data for ['
778 || l_intf_txn_id
779 || ']serial number['
780 || p_receive_rec.serial_number
781 || ']'
782 );
783 END IF;
784
785 INSERT INTO mtl_serial_numbers_interface
786 (transaction_interface_id, source_code,
787 source_line_id, last_update_date, last_updated_by,
788 creation_date, created_by, last_update_login,
789 fm_serial_number,
790 to_serial_number,
791 process_flag
792 )
793 VALUES (l_txn_tmp_id, l_source_code,
794 l_source_line_id, SYSDATE, fnd_global.user_id,
795 SYSDATE, fnd_global.user_id, fnd_global.login_id,
796 p_receive_rec.serial_number,
797 p_receive_rec.serial_number,
798 l_process_flag
799 );
800 --
801 -- Dynamic sql is being used to ensure that the code is not dependent on
802 -- the 11.5.0 PO code. This will be only run time dependent.(functional dependence)
803 sql_str :=
804 'UPDATE mtl_serial_numbers_interface SET product_code=''RCV'' ';
805 sql_str :=
806 sql_str
807 || ',product_transaction_id='
808 || l_intf_txn_id
809 -- bug fix for performance bug 4277749 changed the where clause
810 -- to use using clause
811 || ' Where transaction_interface_id = :1';
812
813 IF (fnd_log.level_event >= fnd_log.g_current_runtime_level)
814 THEN
815 fnd_log.STRING
816 (fnd_log.level_event,
817 'CSD.PLSQL.CSD_RECEIVE_PVT.POPULATE_RCV_INTF_TBLS',
818 'Calling execute immediate with sql['
819 || sql_str
820 || ']'
821 );
822 END IF;
823
824 -- bug fix for performance bug 4277749 changed the where clause
825 -- to use using clause
826 EXECUTE IMMEDIATE sql_str using l_txn_tmp_id;
827
828 END IF;
829 END LOOP;
830
831
832 -- Standard call to get message count and IF count is get message info.
833 fnd_msg_pub.count_and_get (p_count => x_msg_count,
834 p_data => x_msg_data);
835
836 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
837 THEN
838 fnd_log.STRING (fnd_log.level_procedure,
839 'CSD.PLSQL.CSD_RECEIVE_PVT.POPULATE_RCV_INTF_TBLS',
840 'Leaving POPULATE_RCV_INTF_TBLS'
841 );
842 END IF;
843
844 COMMIT;
845
846
847 EXCEPTION
848 WHEN fnd_api.g_exc_error
849 THEN
850 x_return_status := fnd_api.g_ret_sts_error;
851 ROLLBACK ;
852 fnd_msg_pub.count_and_get (p_count => x_msg_count,
853 p_data => x_msg_data);
854
858 (fnd_log.level_error,
855 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)
856 THEN
857 fnd_log.STRING
859 'CSD.PLSQL.CSD_RECEIVE_PVT.POPULATE_RCV_INTF_TBLS',
860 'EXC_EXC_ERROR in populate rcv intf tbls [' || x_msg_data || ']'
861 );
862 END IF;
863 WHEN fnd_api.g_exc_unexpected_error
864 THEN
865 x_return_status := fnd_api.g_ret_sts_unexp_error;
866 ROLLBACK ;
867 fnd_msg_pub.count_and_get (p_count => x_msg_count,
868 p_data => x_msg_data);
869
870 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
871 THEN
872 fnd_log.STRING
873 (fnd_log.level_exception,
874 'CSD.PLSQL.CSD_RECEIVE_PVT.POPULATE_RCV_INTF_TBLS',
875 'EXC_UNEXPECTED_ERROR in populate rcv intf tbls[' || x_msg_data || ']'
876 );
877 END IF;
878 WHEN OTHERS
879 THEN
880 x_return_status := fnd_api.g_ret_sts_unexp_error;
881 ROLLBACK ;
882
883 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
884 THEN
885 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
886 END IF;
887
888 fnd_msg_pub.count_and_get (p_count => x_msg_count,
889 p_data => x_msg_data);
890
891 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)
892 THEN
893 fnd_log.STRING
894 (fnd_log.level_unexpected,
895 'CSD.PLSQL.CSD_RECEIVE_PVT.POPULATE_RCV_INTF_TBLS',
896 'SQL Message in populate rcv intf tbls[' || SQLERRM || ']'
897 );
898 END IF;
899 END populate_rcv_intf_tbls;
900
901
902 /*-----------------------------------------------------------------------------------------------------------*/
903 /* procedure name: RCV_REQ_ONLINE */
904 /* description : This API will submit the request for receiving in the online mode. */
905 /* Called from : */
906 /* Input Parm : p_api_version NUMBER Required Api Version number */
907 /* p_init_msg_list VARCHAR2 Optional Initializes message stack if fnd_api.g_true, */
908 /* default value is fnd_api.g_false */
909 /* p_commit VARCHAR2 Optional Commits in API if fnd_api.g_true, default */
910 /* fnd_api.g_false */
911 /* p_validation_level NUMBER Optional API uses this parameter to determine which */
912 /* validation steps must be done and which steps */
913 /* should be skipped. */
914 /* p_request_group_id NUMBER Required The request group for which the receiving */
915 /* processor
916 /* Output Parm : x_return_status VARCHAR2 Return status after the call. The status can be*/
917 /* fnd_api.g_ret_sts_success (success) */
918 /* fnd_api.g_ret_sts_error (error) */
919 /* fnd_api.g_ret_sts_unexp_error (unexpected) */
920 /* x_msg_count NUMBER Number of messages in the message stack */
921 /* x_msg_data VARCHAR2 Message text if x_msg_count >= 1 */
922 /*-----------------------------------------------------------------------------------------------------------*/
923 PROCEDURE rcv_req_online (
924 p_api_version IN NUMBER,
925 p_commit IN VARCHAR2,
926 p_init_msg_list IN VARCHAR2,
927 p_validation_level IN NUMBER,
928 x_return_status OUT NOCOPY VARCHAR2,
929 x_msg_count OUT NOCOPY NUMBER,
930 x_msg_data OUT NOCOPY VARCHAR2,
931 p_request_group_id IN NUMBER
932 )
933 IS
934 --pragma AUTONOMOUS_TRANSACTION;
935 l_api_version_number CONSTANT NUMBER := 1.0;
936 l_api_name CONSTANT VARCHAR2 (30) := 'RCV_REQ_ONLINE';
937 l_ret_code NUMBER;
938 l_outcome VARCHAR2 (200);
939 l_message fnd_new_messages.MESSAGE_TEXT%TYPE;
940 r_val1 VARCHAR2 (200);
941 r_val2 VARCHAR2 (200);
942 r_val3 VARCHAR2 (200);
943 r_val4 VARCHAR2 (200);
944 r_val5 VARCHAR2 (200);
945 r_val6 VARCHAR2 (200);
946 r_val7 VARCHAR2 (200);
947 r_val8 VARCHAR2 (200);
951 r_val12 VARCHAR2 (200);
948 r_val9 VARCHAR2 (200);
949 r_val10 VARCHAR2 (200);
950 r_val11 VARCHAR2 (200);
952 r_val13 VARCHAR2 (200);
953 r_val14 VARCHAR2 (200);
954 r_val15 VARCHAR2 (200);
955 r_val16 VARCHAR2 (200);
956 r_val17 VARCHAR2 (200);
957 r_val18 VARCHAR2 (200);
958 r_val19 VARCHAR2 (200);
959 r_val20 VARCHAR2 (200);
960 x_progress VARCHAR2 (4);
961 l_TIMEOUT NUMBER;
962 l_str1 fnd_new_messages.MESSAGE_TEXT%TYPE
963 ;
964 l_str2 fnd_new_messages.MESSAGE_TEXT%TYPE
965 ;
966 l_phase VARCHAR2 (200);
967 l_status VARCHAR2 (200);
968 l_dev_phase VARCHAR2 (200);
969 l_dev_status VARCHAR2 (200);
970 l_success BOOLEAN;
971 l_msg_index_out NUMBER;
972 l_index NUMBER;
973 BEGIN
974
975
976 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
977 THEN
978 fnd_log.STRING (fnd_log.level_procedure,
979 'CSD.PLSQL.CSD_RECEIVE_PVT.RCV_REQ_ONLINE.BEGIN',
980 'Entered RCV_REQ_ONLINE'
981 );
982 END IF;
983 x_progress := '000';
984 l_TIMEOUT := 300;
985 l_ret_code := 0;
986
987 -- Standard Start of API savepoint
988 SAVEPOINT sp_rcv_req_online;
989
990 IF fnd_api.to_boolean (p_init_msg_list)
991 THEN
992 -- initialize message list
993 fnd_msg_pub.initialize;
994 END IF;
995
996 -- Standard call to check for call compatibility.
997 IF NOT fnd_api.compatible_api_call (l_api_version_number,
998 p_api_version,
999 l_api_name,
1000 g_pkg_name
1001 )
1002 THEN
1003 RAISE fnd_api.g_exc_unexpected_error;
1004 END IF;
1005
1006 -- initialize return status
1007 x_return_status := fnd_api.g_ret_sts_success;
1008
1009 /*
1010 ** Set the cursor style to working
1011 */
1012
1013 IF (fnd_log.level_event >= fnd_log.g_current_runtime_level)
1014 THEN
1015 fnd_log.STRING (fnd_log.level_event,
1016 'CSD.PLSQL.CSD_RECEIVE_PVT.RCV_REQ_ONLINE',
1017 'Calling receiving processor with req group id['
1018 || p_request_group_id
1019 || ']'
1020 );
1021 END IF;
1022
1023 l_ret_code :=
1024 fnd_transaction.synchronous (l_TIMEOUT,
1025 l_outcome,
1026 l_MESSAGE,
1027 'PO',
1028 'RCVTPO',
1029 'ONLINE',
1030 p_request_group_id,
1031 NULL, NULL, NULL, NULL, NULL, NULL,
1032 NULL, NULL, NULL, NULL, NULL, NULL,
1033 NULL, NULL, NULL, NULL, NULL, NULL
1034 );
1035
1036 IF (fnd_log.level_event >= fnd_log.g_current_runtime_level)
1037 THEN
1038 fnd_log.STRING (fnd_log.level_event,
1039 'CSD.PLSQL.CSD_RECEIVE_PVT.RCV_REQ_ONLINE',
1040 'receiving processor, rc=['
1041 || l_ret_code
1042 || '],message['
1043 || l_MESSAGE
1044 || ']l_outcome['
1045 || l_outcome
1046 || ']'
1047 );
1048 END IF;
1049
1050 IF l_ret_code <> 0 THEN
1051 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)
1052 THEN
1053 FOR l_index IN 1 .. fnd_msg_pub.count_msg
1054 LOOP
1055 fnd_msg_pub.get (p_msg_index => l_index,
1056 p_encoded => 'F',
1057 p_data => l_MESSAGE,
1058 p_msg_index_out => l_msg_index_out
1059 );
1060 fnd_log.STRING (fnd_log.level_error,
1061 'CSD.PLSQL.CSD_RECEIVE_PVT.RCV_REQ_ONLINE',
1062 'receiving processor, error[' || l_MESSAGE || ']'
1063 );
1064 END LOOP;
1065 END IF;
1066 --FND_MESSAGE.SET_NAME('CSD', 'CSD_RECEIVE_ERROR');
1067 --FND_MSG_PUB.ADD;
1068 --RAISE FND_API.G_EXC_ERROR;
1069 END IF;
1070
1071 -- dbms_output.put_line('outcome=[-'||outcome||'-]');
1072 -- dbms_output.put_line('message=[-'||message||'-]');
1073
1074 /*
1075 ** E_SUCCESS constant number := 0; -- e_code is success
1079 */
1076 ** E_TIMEOUT constant number := 1; -- e_code is timeout
1077 ** E_NOMGR constant number := 2; -- e_code is no manager
1078 ** E_OTHER constant number := 3; -- e_code is other
1080 IF (l_ret_code = 0 AND (l_outcome NOT IN ('WARNING', 'ERROR')))
1081 THEN
1082 NULL;
1083 ELSIF (l_ret_code = 1)
1084 THEN
1085 IF (fnd_log.level_event >= fnd_log.g_current_runtime_level)
1086 THEN
1087 fnd_log.STRING (fnd_log.level_event,
1088 'CSD.PLSQL.CSD_RECEIVE_PVT.RCV_REQ_ONLINE',
1089 'fnd_trnasaction.synchronous TIMED OUT'
1090 );
1091 END IF;
1092
1093 IF (check_group_id (p_request_group_id))
1094 THEN
1095 fnd_message.set_name ('FND', 'TM-TIMEOUT');
1096 l_str1 := fnd_message.get;
1097 fnd_message.CLEAR;
1098 -- use rcv_all_rcvoltm to get translated message
1099 fnd_message.set_name ('PO', 'RCV_ALL_RCVOLTM');
1100 l_str2 := fnd_message.get;
1101 fnd_message.CLEAR;
1102 fnd_message.set_name ('FND', 'CONC-ERROR RUNNING STANDALONE');
1103 fnd_message.set_token ('PROGRAM', l_str2);
1104 fnd_message.set_token ('REQUEST', p_request_group_id);
1105 fnd_message.set_token ('REASON', l_str1);
1106 --fnd_message.show;
1107 fnd_msg_pub.ADD;
1108 x_return_status := fnd_api.g_ret_sts_error;
1109 END IF;
1110 ELSIF (l_Ret_code = 2)
1111 THEN
1112 IF (fnd_log.level_event >= fnd_log.g_current_runtime_level)
1113 THEN
1114 fnd_log.STRING
1115 (fnd_log.level_event,
1116 'CSD.PLSQL.CSD_RECEIVE_PVT.RCV_REQ_ONLINE',
1117 'fnd_trnasaction.synchronous: no concurrent manager available,groupid['
1118 || TO_CHAR (p_request_group_id)
1119 || ']'
1120 );
1121 END IF;
1122
1123 IF (check_group_id (p_request_group_id))
1124 THEN
1125 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1126 THEN
1127 fnd_log.STRING (fnd_log.level_statement,
1128 'CSD.PLSQL.CSD_RECEIVE_PVT.RCV_REQ_ONLINE',
1129 'Adding FND message, groupid['
1130 || TO_CHAR (p_request_group_id)
1131 || ']'
1132 );
1133 END IF;
1134
1135 fnd_message.set_name ('FND', 'TM-SVC LOCK HANDLE FAILED');
1136 l_str1 := fnd_message.get;
1137 fnd_message.CLEAR;
1138 fnd_message.set_name ('PO', 'RCV_ALL_RCVOLTM');
1139 l_str2 := fnd_message.get;
1140 fnd_message.CLEAR;
1141 fnd_message.set_name ('FND', 'CONC-ERROR RUNNING STANDALONE');
1142 fnd_message.set_token ('PROGRAM', l_str2);
1143 fnd_message.set_token ('REQUEST', p_request_group_id);
1144 fnd_message.set_token ('REASON', l_str1);
1145
1146 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)
1147 THEN
1148 fnd_log.MESSAGE (fnd_log.level_error,
1149 'CSD.PLSQL.CSD_RECEIVE_PVT.RCV_REQ_ONLINE');
1150 END IF;
1151
1152 fnd_msg_pub.ADD;
1153 x_return_status := fnd_api.g_ret_sts_error;
1154 END IF;
1155 ELSIF (l_ret_code = 3 OR (l_outcome IN ('WARNING', 'ERROR')))
1156 THEN
1157 IF (fnd_log.level_event >= fnd_log.g_current_runtime_level)
1158 THEN
1159 fnd_log.STRING (fnd_log.level_event,
1160 'CSD.PLSQL.CSD_RECEIVE_PVT.RCV_REQ_ONLINE',
1161 'fnd_synschronous:rc['
1162 || l_Ret_code
1163 || '],outcome['
1164 || l_outcome
1165 || '],for request group['
1166 || p_request_group_id
1167 || ']'
1168 );
1169 END IF;
1170
1171 log_error_stack ();
1172 --IF (check_group_id(p_request_group_id)) THEN
1173 x_progress := '010';
1174 l_Ret_Code :=
1175 fnd_transaction.get_values (r_val1,
1176 r_val2,
1177 r_val3,
1178 r_val4,
1179 r_val5,
1180 r_val6,
1181 r_val7,
1182 r_val8,
1183 r_val9,
1184 r_val10,
1185 r_val11,
1186 r_val12,
1187 r_val13,
1188 r_val14,
1189 r_val15,
1190 r_val16,
1191 r_val17,
1192 r_val18,
1196 l_str1 := r_val1;
1193 r_val19,
1194 r_val20
1195 );
1197
1198 IF (r_val2 IS NOT NULL)
1199 THEN
1200 l_str1 := l_str1 || r_val2;
1201 END IF;
1202
1203 IF (r_val3 IS NOT NULL)
1204 THEN
1205 l_str1 := l_str1 || r_val3;
1206 END IF;
1207
1208 IF (r_val4 IS NOT NULL)
1209 THEN
1210 l_str1 := l_str1 || r_val4;
1211 END IF;
1212
1213 IF (r_val5 IS NOT NULL)
1214 THEN
1215 l_str1 := l_str1 || r_val5;
1216 END IF;
1217
1218 IF (r_val6 IS NOT NULL)
1219 THEN
1220 l_str1 := l_str1 || r_val6;
1221 END IF;
1222
1223 IF (r_val7 IS NOT NULL)
1224 THEN
1225 l_str1 := l_str1 || r_val7;
1226 END IF;
1227
1228 IF (r_val8 IS NOT NULL)
1229 THEN
1230 l_str1 := l_str1 || r_val8;
1231 END IF;
1232
1233 IF (r_val9 IS NOT NULL)
1234 THEN
1235 l_str1 := l_str1 || r_val9;
1236 END IF;
1237
1238 IF (r_val10 IS NOT NULL)
1239 THEN
1240 l_str1 := l_str1 || r_val10;
1241 END IF;
1242
1243 IF (r_val11 IS NOT NULL)
1244 THEN
1245 l_str1 := l_str1 || r_val11;
1246 END IF;
1247
1248 IF (r_val12 IS NOT NULL)
1249 THEN
1250 l_str1 := l_str1 || r_val12;
1251 END IF;
1252
1253 IF (r_val13 IS NOT NULL)
1254 THEN
1255 l_str1 := l_str1 || r_val13;
1256 END IF;
1257
1258 IF (r_val14 IS NOT NULL)
1259 THEN
1260 l_str1 := l_str1 || r_val14;
1261 END IF;
1262
1263 IF (r_val15 IS NOT NULL)
1264 THEN
1265 l_str1 := l_str1 || r_val15;
1266 END IF;
1267
1268 IF (r_val16 IS NOT NULL)
1269 THEN
1270 l_str1 := l_str1 || r_val16;
1271 END IF;
1272
1273 IF (r_val17 IS NOT NULL)
1274 THEN
1275 l_str1 := l_str1 || r_val17;
1276 END IF;
1277
1278 IF (r_val18 IS NOT NULL)
1279 THEN
1280 l_str1 := l_str1 || r_val18;
1281 END IF;
1282
1283 IF (r_val19 IS NOT NULL)
1284 THEN
1285 l_str1 := l_str1 || r_val19;
1286 END IF;
1287
1288 IF (r_val20 IS NOT NULL)
1289 THEN
1290 l_str1 := l_str1 || r_val20;
1291 END IF;
1292
1293 FND_MESSAGE.SET_NAME('CSD','CSD_AUTO_RCV_ERROR');
1294 FND_MESSAGE.SET_TOKEN('RCV_ERROR',l_Str1);
1295 FND_MSG_PUB.add;
1296
1297 IF (fnd_log.level_event >= fnd_log.g_current_runtime_level)
1298 THEN
1299 fnd_log.STRING (fnd_log.level_event,
1300 'CSD.PLSQL.CSD_RECEIVE_PVT.RCV_REQ_ONLINE',
1301 'fnd_trnasaction.synchronous:[' || l_str1 || ']'
1302 );
1303 END IF;
1304
1305 x_return_status := fnd_api.g_ret_sts_error;
1306 --END IF;
1307 END IF;
1308
1309 -- Standard call to get message count and IF count is get message info.
1310 fnd_msg_pub.count_and_get (p_count => x_msg_count,
1311 p_data => x_msg_data);
1312
1313 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1314 THEN
1315 fnd_log.STRING (fnd_log.level_procedure,
1316 'CSD.PLSQL.CSD_RECEIVE_PVT.RCV_REQ_ONLINE.END',
1317 'Leaving RCV_REQ_ONLINE'
1318 );
1319 END IF;
1320 EXCEPTION
1321 WHEN fnd_api.g_exc_error
1322 THEN
1323 x_return_status := fnd_api.g_ret_sts_error;
1324 ROLLBACK TO sp_rcv_req_online;
1325 fnd_msg_pub.count_and_get (p_count => x_msg_count,
1326 p_data => x_msg_data);
1327
1328 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)
1329 THEN
1330 fnd_log.STRING (fnd_log.level_error,
1331 'CSD.PLSQL.CSD_RECEIVE_PVT.RCV_REQ_ONLINE',
1332 'EXC_ERROR[' || x_msg_data || ']'
1333 );
1334 END IF;
1335 WHEN fnd_api.g_exc_unexpected_error
1336 THEN
1337 x_return_status := fnd_api.g_ret_sts_unexp_error;
1338 ROLLBACK TO sp_rcv_req_online;
1339 fnd_msg_pub.count_and_get (p_count => x_msg_count,
1340 p_data => x_msg_data);
1341
1342 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
1343 THEN
1344 fnd_log.STRING (fnd_log.level_exception,
1345 'CSD.PLSQL.CSD_RECEIVE_PVT.RCV_REQ_ONLINE',
1346 'EXC_UNEXPECTED_ERROR in RCV_REQ_ONLINE[' || x_msg_data || ']'
1347 );
1348 END IF;
1349 WHEN OTHERS
1353
1350 THEN
1351 x_return_status := fnd_api.g_ret_sts_unexp_error;
1352 ROLLBACK TO sp_rcv_req_online;
1354 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
1355 THEN
1356 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1357 END IF;
1358
1359 fnd_msg_pub.count_and_get (p_count => x_msg_count,
1360 p_data => x_msg_data);
1361
1362 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)
1363 THEN
1364 fnd_log.STRING (fnd_log.level_unexpected,
1365 'CSD.PLSQL.CSD_RECEIVE_PVT.RCV_REQ_ONLINE',
1366 'SQL Error MEssage in RCV_REQ_ONLINE[' || SQLERRM || ']'
1367 );
1368 END IF;
1369 END rcv_req_online;
1370
1371 /*-----------------------------------------------------------------------------------------------------------*/
1372 /* procedure name: DELETE_INTF_TBLS */
1373 /* description : Deletes records in RCV_HEADERS_INTERFACE, RCV_TRANSACTIONS_INTERFACE., PO_INTERFACE_ERRORS*/
1374 /* MTL_TRANSACTION_LOTS_INTERFACE_TBL, MTL_SERIAL_NUMBERS_INTERFACE_TBL tables. */
1375 /* Called from : receive_item api */
1376 /* Input Parm : */
1377 /* p_request_group_id NUMBER Required */
1378 /* p_interface_transaction_Id NUMBER Required */
1379 /* p_interface_header_Id NUMBER Required */
1380 /* Output Parm : x_return_status VARCHAR2 Return status after the call. The status can be*/
1381 /* fnd_api.g_ret_sts_success (success) */
1382 /* fnd_api.g_ret_sts_error (error) */
1383 /* fnd_api.g_ret_sts_unexp_error (unexpected) */
1384 /*-----------------------------------------------------------------------------------------------------------*/
1385 PROCEDURE delete_intf_tbls (
1386 x_return_status OUT NOCOPY VARCHAR2,
1387 p_request_group_id IN NUMBER
1388 )
1389 IS
1390 pragma AUTONOMOUS_TRANSACTION;
1391 l_api_version_number CONSTANT NUMBER := 1.0;
1392 l_api_name CONSTANT VARCHAR2 (30) := 'Delete_Intf_Tbls';
1393 l_txn_temp_id NUMBER ;
1394
1395 --Cursor to get the headers interface records.
1396 CURSOR cur_headers(p_group_Id NUMBER) is
1397 SELECT HEADER_INTERFACE_ID
1398 FROM RCV_HEADERS_INTERFACE
1399 WHERE GROUP_ID = p_group_id;
1400 --Cursor to get the transactions interface records.
1401 CURSOR cur_transactions(p_group_Id NUMBER) is
1402 SELECT INTERFACE_TRANSACTION_ID
1403 FROM RCV_TRANSACTIONS_INTERFACE
1404 WHERE GROUP_ID = p_group_id;
1405 BEGIN
1406
1407 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1408 THEN
1409 fnd_log.STRING (fnd_log.level_procedure,
1410 'CSD.PLSQL.CSD_RECEIVE_PVT.DELETE_INTF_TBLS.BEGIN',
1411 'Entered Delete_Intf_Tbls'
1412 );
1413 END IF;
1414
1415 -- initialize return status
1416 x_return_status := fnd_api.g_ret_sts_success;
1417
1418
1419 -----------------------------------------------Delete from errors table
1420 FOR l_hdr_rec in cur_headers(p_request_group_id) LOOP
1421 BEGIN
1422 DELETE FROM po_interface_errors err
1423 WHERE err.interface_header_id = l_hdr_Rec.header_interface_id;
1424
1425 EXCEPTION
1426 WHEN NO_DATA_FOUND
1427 THEN
1428 NULL;
1429 WHEN OTHERS
1430 THEN
1431 RAISE fnd_api.g_exc_unexpected_error;
1432 END;
1433 END LOOP;
1434
1435 FOR l_txn_rec in cur_transactions(p_request_group_id) LOOP
1436 BEGIN
1437 DELETE FROM po_interface_errors err
1438 WHERE err.interface_transaction_id = l_txn_rec.interface_transaction_id;
1439
1440 EXCEPTION
1441 WHEN NO_DATA_FOUND
1442 THEN
1443 NULL;
1444 WHEN OTHERS
1445 THEN
1446 RAISE fnd_api.g_exc_unexpected_error;
1447 END;
1448 END LOOP;
1449
1450 ----------------------------------------------Delete from the MTL lots/MTL serial numbers
1451 --------------------------------------------- interface table.
1452 FOR l_txn_rec in cur_transactions(p_request_group_id) LOOP
1453 BEGIN
1454 DELETE FROM mtl_transaction_lots_interface
1455 WHERE TRANSACTION_INTERFACE_ID = l_txn_rec.interface_transaction_Id
1456 RETURNING SERIAL_TRANSACTION_TEMP_ID into l_txn_temp_id;
1457
1458 DELETE FROM mtl_serial_numbers_interface
1462 WHEN NO_DATA_FOUND
1459 WHERE (TRANSACTION_INTERFACE_ID = l_txn_rec.interface_transaction_Id
1460 OR TRANSACTION_INTERFACE_ID = l_txn_temp_id);
1461 EXCEPTION
1463 THEN
1464 NULL;
1465 WHEN OTHERS
1466 THEN
1467 RAISE fnd_api.g_exc_unexpected_error;
1468 END;
1469 END LOOP;
1470
1471 ----------------------------------------------Delete from headers table.
1472 IF (fnd_log.level_event >= fnd_log.g_current_runtime_level)
1473 THEN
1474 fnd_log.STRING (fnd_log.level_event,
1475 'CSD.PLSQL.CSD_RECEIVE_PVT.DELETE_INTF_TBLS',
1476 'Deleting from the headers table'
1477 );
1478 END IF;
1479
1480 BEGIN
1481 DELETE FROM rcv_headers_interface
1482 WHERE GROUP_ID = p_request_group_id;
1483 EXCEPTION
1484 WHEN NO_DATA_FOUND
1485 THEN
1486 NULL;
1487 WHEN OTHERS
1488 THEN
1489 RAISE fnd_api.g_exc_unexpected_error;
1490 END;
1491 ----------------------------------------------Delete from the detail txn records.
1492 IF (fnd_log.level_event >= fnd_log.g_current_runtime_level)
1493 THEN
1494 fnd_log.STRING (fnd_log.level_event,
1495 'CSD.PLSQL.CSD_RECEIVE_PVT.DELETE_INTF_TBLS',
1496 'Deleting from the detail table'
1497 );
1498 END IF;
1499
1500 BEGIN
1501 DELETE FROM rcv_transactions_interface
1502 WHERE GROUP_ID = p_request_group_id;
1503 EXCEPTION
1504 WHEN NO_DATA_FOUND
1505 THEN
1506 NULL;
1507 WHEN OTHERS
1508 THEN
1509 RAISE fnd_api.g_exc_unexpected_error;
1510 END;
1511
1512
1513 COMMIT;
1514
1515 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1516 THEN
1517 fnd_log.STRING (fnd_log.level_procedure,
1518 'CSD.PLSQL.CSD_RECEIVE_PVT.DELETE_INTF_TBLS.END',
1519 'Leaving DELETE_INTF_TBLS'
1520 );
1521 END IF;
1522 EXCEPTION
1523 WHEN fnd_api.g_exc_unexpected_error
1524 THEN
1525 x_return_status := fnd_api.g_ret_sts_unexp_error;
1526 ROLLBACK ;
1527 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
1528 THEN
1529 fnd_log.STRING (fnd_log.level_exception,
1530 'CSD.PLSQL.CSD_RECEIVE_PVT.DELETE_INTF_TBLS',
1531 'EXC_UNEXPECTED_ERROR in delete_intf_tbls'
1532 );
1533 END IF;
1534 WHEN OTHERS
1535 THEN
1536 x_return_status := fnd_api.g_ret_sts_unexp_error;
1537 ROLLBACK;
1538
1539 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
1540 THEN
1541 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1542 END IF;
1543
1544 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)
1545 THEN
1546 fnd_log.STRING (fnd_log.level_unexpected,
1547 'CSD.PLSQL.CSD_RECEIVE_PVT.DELETE_INTF_TBLS',
1548 'SQL Message in delete_intf_tbls[' || SQLERRM || ']'
1549 );
1550 END IF;
1551 END delete_intf_tbls;
1552
1553
1554 /*=============================================================
1555
1556 FUNCTION NAME: check_group_id
1557
1558 =============================================================*/
1559 FUNCTION check_group_id (
1560 p_group_id IN NUMBER
1561 )
1562 RETURN BOOLEAN
1563 IS
1564 l_rec_count NUMBER := 0;
1565 BEGIN
1566 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1567 THEN
1568 fnd_log.STRING (fnd_log.level_procedure,
1569 'CSD.PLSQL.CSD_RECEIVE_PVT.CHECK_GROUP_ID.BEGIN',
1570 'Entered check_group_id, groupid[' || p_group_id
1571 || ']'
1572 );
1573 END IF;
1574
1575 SELECT COUNT (1)
1576 INTO l_rec_count
1577 FROM rcv_transactions_interface
1578 WHERE GROUP_ID = p_group_id;
1579
1580 IF (l_rec_count = 0)
1581 THEN
1582 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1583 THEN
1584 fnd_log.STRING (fnd_log.level_procedure,
1585 'CSD.PLSQL.CSD_RECEIVE_PVT.CHECK_GROUP_ID.END',
1586 'returning false from check_group_id'
1587 );
1588 END IF;
1589
1590 RETURN (FALSE);
1591 ELSE
1592 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1593 THEN
1594 fnd_log.STRING (fnd_log.level_procedure,
1595 'CSD.PLSQL.CSD_RECEIVE_PVT.CHECK_GROUP_ID.END',
1596 'returning true from check_group_id'
1597 );
1598 END IF;
1599
1603 RETURN NULL;
1600 RETURN (TRUE);
1601 END IF;
1602
1604 EXCEPTION
1605 WHEN NO_DATA_FOUND
1606 THEN
1607 RETURN (FALSE);
1608 WHEN OTHERS
1609 THEN
1610 RAISE;
1611 END check_group_id;
1612
1613 /****************************************************************
1614 Dupms the input receive table records in to log
1615 *****************************************************************/
1616 PROCEDURE dump_receive_tbl (
1617 p_receive_tbl IN csd_receive_util.rcv_tbl_type,
1618 p_level NUMBER,
1619 p_module VARCHAR2
1620 )
1621 IS
1622 i INTEGER;
1623 BEGIN
1624 FOR i IN p_receive_tbl.FIRST .. p_receive_tbl.LAST
1625 LOOP
1626 IF (p_level >= fnd_log.g_current_runtime_level)
1627 THEN
1628 fnd_log.STRING (p_level, p_module,
1629 TO_CHAR (i)
1630 || ':'
1631 || TO_CHAR (p_receive_tbl (i).customer_id)
1632 );
1633 fnd_log.STRING (p_level, p_module,
1634 TO_CHAR (i)
1635 || ':'
1636 || TO_CHAR (p_receive_tbl (i).customer_site_id)
1637 );
1638 fnd_log.STRING (p_level, p_module,
1639 TO_CHAR (i)
1640 || ':'
1641 || TO_CHAR (p_receive_tbl (i).employee_id)
1642 );
1643 fnd_log.STRING (p_level,
1644 p_module,
1645 TO_CHAR (i)
1646 || ':'
1647 || TO_CHAR (p_receive_tbl (i).quantity)
1648 );
1649 fnd_log.STRING (p_level,
1650 p_module,
1651 TO_CHAR (i) || ':' || p_receive_tbl (i).uom_code
1652 );
1653 fnd_log.STRING (p_level,
1654 p_module,
1655 TO_CHAR (i)
1656 || ':'
1657 || TO_CHAR (p_receive_tbl (i).inventory_item_id)
1658 );
1659 fnd_log.STRING (p_level,
1660 p_module,
1661 TO_CHAR (i) || ':' || p_receive_tbl (i).item_revision
1662 );
1663 fnd_log.STRING (p_level,
1664 p_module,
1665 TO_CHAR (i)
1666 || ':'
1667 || TO_CHAR (p_receive_tbl (i).to_organization_id)
1668 );
1669 fnd_log.STRING (p_level,
1670 p_module,
1671 TO_CHAR (i)
1672 || ':'
1673 || p_receive_tbl (i).destination_type_code
1674 );
1675 fnd_log.STRING (p_level,
1676 p_module,
1677 TO_CHAR (i) || ':' || p_receive_tbl (i).subinventory
1678 );
1679 fnd_log.STRING (p_level,
1680 p_module,
1681 TO_CHAR (i)
1682 || ':'
1683 || TO_CHAR (p_receive_tbl (i).locator_id)
1684 );
1685 fnd_log.STRING (p_level,
1686 p_module,
1687 TO_CHAR (i)
1688 || ':'
1689 || TO_CHAR (p_receive_tbl (i).deliver_to_location_id)
1690 );
1691 fnd_log.STRING (p_level,
1692 p_module,
1693 TO_CHAR (i)
1694 || ':'
1695 || TO_CHAR (p_receive_tbl (i).requisition_number)
1696 );
1697 fnd_log.STRING (p_level,
1698 p_module,
1699 TO_CHAR (i)
1700 || ':'
1701 || TO_CHAR (p_receive_tbl (i).order_header_id)
1702 );
1703 fnd_log.STRING (p_level,
1704 p_module,
1705 TO_CHAR (i)
1706 || ':'
1707 || TO_CHAR (p_receive_tbl (i).order_line_id)
1708 );
1709 fnd_log.STRING (p_level,
1710 p_module,
1711 TO_CHAR (i) || ':' || p_receive_tbl (i).order_number
1712 );
1713 fnd_log.STRING (p_level,
1714 p_module,
1715 TO_CHAR (i) || ':' || p_receive_tbl (i).doc_number
1716 );
1717 fnd_log.STRING (p_level,
1718 p_module,
1719 TO_CHAR (i)
1720 || ':'
1721 || p_receive_tbl (i).internal_order_flag
1722 );
1723 fnd_log.STRING (p_level,
1724 p_module,
1725 TO_CHAR (i)
1726 || ':'
1727 || TO_CHAR (p_receive_tbl (i).from_organization_id)
1728 );
1729 fnd_log.STRING (p_level,
1730 p_module,
1731 TO_CHAR (i)
1732 || ':'
1733 || TO_CHAR (p_receive_tbl (i).expected_receipt_date)
1734 );
1735 fnd_log.STRING (p_level,
1736 p_module,
1737 TO_CHAR (i)
1738 || ':'
1739 || TO_CHAR (p_receive_tbl (i).transaction_date)
1740 );
1741 fnd_log.STRING (p_level,
1742 p_module,
1743 TO_CHAR (i)
1744 || ':'
1745 || TO_CHAR (p_receive_tbl (i).ship_to_location_id)
1746 );
1747 END IF;
1748 END LOOP;
1749 END dump_receive_tbl;
1750
1751 /*************procedure to log the error stack..........
1752 ****************/
1753 PROCEDURE log_error_stack
1754 IS
1755 l_count NUMBER;
1756 l_msg VARCHAR2 (2000);
1757 l_index_out NUMBER;
1758 BEGIN
1759 l_count := fnd_msg_pub.count_msg ();
1760
1761 IF (l_count > 0)
1762 THEN
1763 FOR i IN 1 .. l_count
1764 LOOP
1765 fnd_msg_pub.get (p_msg_index => i,
1766 p_encoded => 'F',
1767 p_data => l_msg,
1768 p_msg_index_out => l_index_out
1769 );
1770
1771 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)
1772 THEN
1773 fnd_log.STRING (fnd_log.level_error,
1774 'CSD.PLSQL.CSD_RECEIVE_PVT.log_error_stack',
1775 'error[' || l_msg || ']'
1776 );
1777 END IF;
1778 END LOOP;
1779 END IF;
1780 END log_error_stack;
1781 END csd_receive_pvt;