1 PACKAGE BODY csd_receive_util AS
2 /* $Header: csdvrutb.pls 120.4.12000000.2 2007/04/24 17:55:51 swai ship $ */
3
4 -- ---------------------------------------------------------
5 -- Define global variables
6 -- ---------------------------------------------------------
7 g_pkg_name CONSTANT VARCHAR2 (30) := 'CSD_RECEVIE_UTIL';
8 g_file_name CONSTANT VARCHAR2 (12) := 'csdvrutb.pls';
9 g_debug_level CONSTANT NUMBER := csd_gen_utility_pvt.g_debug_level;
10 g_inspection_required CONSTANT VARCHAR2 (30) := 'Inspection Required';
11 g_inspection_routing CONSTANT NUMBER := 2;
12 g_standard_routing CONSTANT NUMBER := 1;
13
14 /*--------------------------------------------------------------------------------------*/
15 /* function name: is_auto_rcv_available */
16 /* description : This function will check if the item is eligible for auto receive */
17 /* */
18 /* Called from : This is called from the LOGISTICS UI and also the */
19 /* CSD_RECEIVE_PVT.RECEIVE_ITEM API. */
20 /* Input Parm : p_inventory_item_id NUMBER inventory item id */
21 /* p_inv_org_id NUMBER org id of the receiving */
22 /* sub inventory */
23 /* p_internal_ro_flag VARCHAR2 indicates if the repair */
24 /* order is internal */
25 /* p_from_inv_org_id NUMBER org id from which the */
26 /* transfer is made in the */
27 /* case if internal orders */
28 /* returns Routing header id. NUMBER */
29 /*------------------------------------------------------------------------------------*/
30 FUNCTION is_auto_rcv_available (
31 p_inventory_item_id IN NUMBER,
32 p_inv_org_id IN NUMBER,
33 p_internal_ro_flag IN VARCHAR2,
34 p_from_inv_org_id IN NUMBER
35 )
36 RETURN NUMBER
37 IS
38 l_routing_header_id NUMBER;
39 l_routing_name VARCHAR2 (30);
40 l_location_control_code NUMBER;
41 l_serial_control_code NUMBER;
42
43 --Curosrs for Internal orders ---------------------------------
44 --Cursor to get the routing id from item parameters for internal orders
45 CURSOR cur_item_routing_internal (
46 p_org_id NUMBER,
47 p_item_id NUMBER
48 )
49 IS
50 SELECT itm.receiving_routing_id,
51 itm.location_control_code, itm.serial_number_control_code
52 FROM mtl_system_items_b itm
53 WHERE itm.inventory_item_id = p_item_id
54 AND itm.organization_id = p_org_id;
55
56 --Curosr to get the receive Parameters.
57 CURSOR cur_rcv_routing (
58 p_org_id NUMBER
59 )
60 IS
61 SELECT rcp.receiving_routing_id
62 FROM rcv_parameters rcp
63 WHERE organization_id = p_org_id;
64
65 --Cursor for shipping network
66 CURSOR cur_shipping_network (
67 p_from_org NUMBER,
68 p_to_org NUMBER
69 )
70 IS
71 SELECT mip.routing_header_id
72 FROM mtl_interorg_parameters mip
73 WHERE from_organization_id = p_from_org
74 AND to_organization_id = p_to_org;
75
76 -- Cursors for RMA-----------------------------
77 --Cursor to get the routing id from item parameters for regular RMA
78 CURSOR cur_item_routing_rma (
79 p_org_id NUMBER,
80 p_item_id NUMBER
81 )
82 IS
83 SELECT DECODE (itm.return_inspection_requirement,
84 1, g_inspection_routing,
85 null
86 ),
87 itm.location_control_code, itm.serial_number_control_code
88 FROM mtl_system_items_b itm
89 WHERE itm.inventory_item_id = p_item_id
90 AND itm.organization_id = p_org_id;
91
92 BEGIN
93
94
95 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
96 THEN
97 fnd_log.STRING
98 (fnd_log.level_procedure,
99 'CSD.PLSQL.CSD_RECEIVE_UTIL.IS_AUTO_RCV_AVAILABLE.BEGIN',
100 'Entered IS_AUTO_RCV_AVAILABLE'
101 );
102 END IF;
103
104 --auto receive fucntionality is available only in po patch level j.
105 -- hence return false if the po patch is not set
106 if (PO_CODE_RELEASE_GRP.Current_Release < PO_CODE_RELEASE_GRP.PRC_11i_Family_Pack_J ) then
107 return -1;
108 End If;
109
110
111 l_routing_header_id := -1;
112
113 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
114 THEN
115 fnd_log.STRING (fnd_log.level_statement,
116 'CSD.PLSQL.CSD_RECEIVE_UTIL.IS_AUTO_RCV_AVAILABLE',
117 'Parameter:p_inventory_item_id['
118 || p_inventory_item_id
119 || ']'
120 );
121 fnd_log.STRING (fnd_log.level_statement,
122 'CSD.PLSQL.CSD_RECEIVE_UTIL.IS_AUTO_RCV_AVAILABLE',
123 'Parameter:p_inv_org_id[' || p_inv_org_id || ']'
124 );
125 fnd_log.STRING (fnd_log.level_statement,
126 'CSD.PLSQL.CSD_RECEIVE_UTIL.IS_AUTO_RCV_AVAILABLE',
127 'Parameter:p_internal_RO_flag['
128 || p_internal_ro_flag
129 || ']'
130 );
131 fnd_log.STRING (fnd_log.level_statement,
132 'CSD.PLSQL.CSD_RECEIVE_UTIL.IS_AUTO_RCV_AVAILABLE',
133 'Parameter:p_from_inv_org_id['
134 || p_from_inv_org_id
135 || ']'
136 );
137 END IF;
138
139 IF (fnd_log.level_event >= fnd_log.g_current_runtime_level)
140 THEN
141 fnd_log.STRING (fnd_log.level_event,
142 'CSD.PLSQL.CSD_RECEIVE_UTIL.IS_AUTO_RCV_AVAILABLE',
143 'Checking item level routing'
144 );
145 END IF;
146
147 IF (p_internal_ro_flag = 'Y')
148 THEN
149 --For internal orders
150 -- Step i: Check item attribute
151 --Item level
152 --(Currently limiting to serialized non
153 --- locator controlled by Depot)
154 OPEN cur_item_routing_internal (p_inv_org_id, p_inventory_item_id);
155
156 FETCH cur_item_routing_internal
157 INTO l_routing_header_id, l_location_control_code,
158 l_serial_control_code;
159
160 IF (cur_item_routing_internal%FOUND)
161 THEN
162 IF ( l_routing_header_id = g_inspection_routing
163 /* R12 development : removed the restrictions
164 OR l_location_control_code <> 1
165 OR l_serial_control_code = 1
166 */
167 )
168 THEN
169 l_routing_header_id := -1;
170 END IF;
171 END IF;
172 CLOSE cur_item_routing_internal;
173
174 IF(l_routing_header_id is not null) THEN
175 RETURN l_routing_header_id;
176 END IF;
177
178
179 -- Step 2: Check Shipping network
180 IF (fnd_log.level_event >= fnd_log.g_current_runtime_level)
181 THEN
182 fnd_log.STRING
183 (fnd_log.level_event,
184 'CSD.PLSQL.CSD_RECEIVE_UTIL.IS_AUTO_RCV_AVAILABLE',
185 'Checking shipping network level routing'
186 );
187 END IF;
188
189 OPEN cur_shipping_network (p_from_inv_org_id, p_inv_org_id);
190
191 FETCH cur_shipping_network
192 INTO l_routing_header_id;
193
194 IF (cur_shipping_network%FOUND)
195 THEN
196 IF (l_routing_header_id = g_inspection_routing)
197 THEN
198 l_routing_header_id := -1;
199 END IF;
200 END IF;
201 CLOSE cur_shipping_network;
202
203 IF(l_routing_header_id is not null) THEN
204 RETURN l_routing_header_id;
205 END IF;
206
207 -- Organization level
208 IF (fnd_log.level_event >= fnd_log.g_current_runtime_level)
209 THEN
210 fnd_log.STRING (fnd_log.level_event,
211 'CSD.PLSQL.CSD_RECEIVE_UTIL.IS_AUTO_RCV_AVAILABLE',
212 'Checking org level routing'
213 );
214 END IF;
215
216 OPEN cur_rcv_routing (p_inv_org_id);
217
218 FETCH cur_rcv_routing
219 INTO l_routing_header_id;
220
221 IF (cur_rcv_routing%FOUND)
222 THEN
223 IF (l_routing_header_id = g_inspection_routing)
224 THEN
225 l_routing_header_id := -1;
226 END IF;
227 END IF;
228
229 CLOSE cur_rcv_routing;
230
231 IF(l_routing_header_id is not null) THEN
232 RETURN l_routing_header_id;
233 END IF;
234
235 ELSE
236 --For regular RMA's logic is, fist at item level and then at org level
237 -- return_inspection_Requirement in mtl_system_items_b
238 -- is used for item attribute
239 OPEN cur_item_routing_rma (p_inv_org_id, p_inventory_item_id);
240
241 FETCH cur_item_routing_rma
242 INTO l_routing_header_id, l_location_control_code,
243 l_serial_control_code;
244
245 IF (cur_item_routing_rma%FOUND)
246 THEN
247 IF ( l_routing_header_id = g_inspection_routing
248 /* R12 development : removed the restrictions
249 OR l_location_control_code <> 1
250 OR l_serial_control_code = 1
251 */
252 )
253 THEN
254 l_routing_header_id := -1;
255 END IF;
256 END IF;
257
258 CLOSE cur_item_routing_rma;
259
260 IF(l_routing_header_id is not null) THEN
261 RETURN l_routing_header_id;
262 END IF;
263
264 -- At org level check rcv_parameters. This is kept as dynamic sql
265 -- to remove code dependency for R10.
266
267 BEGIN
268 EXECUTE IMMEDIATE 'SELECT nvl(RMA_RECEIPT_ROUTING_ID, '||g_standard_routing ||')'
269 || 'FROM RCV_PARAMETERS WHERE organization_id =:1'
270 --|| p_inv_org_id --4277749 TBD
271 --4277749 changed the exec to use using clause
272 INTO l_routing_header_id using p_inv_org_id;
273 EXCEPTION
274 WHEN NO_DATA_FOUND THEN
275 l_routing_header_id := g_standard_routing;
276 END;
277
278 IF (l_routing_header_id = g_inspection_routing ) THEN
279 l_routing_header_id := -1;
280 END IF;
281
282 IF(l_routing_header_id is not null) THEN
283 RETURN l_routing_header_id;
284 END IF;
285
286 END IF;
287
288
289
290 -----
291 -- Default the routing to standard if not set anywhere.
292
293 IF(l_routing_header_id is null) THEN
294 l_routing_header_id := g_standard_routing;
295 END IF;
296
297 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
298 THEN
299 fnd_log.STRING
300 (fnd_log.level_procedure,
301 'CSD.PLSQL.CSD_RECEIVE_UTIL.IS_AUTO_RCV_AVAILABLE.END',
302 'Leaving IS_AUTO_RCV_AVAILABLE'
303 );
304 END IF;
305
306 RETURN l_routing_header_id;
307
308 END is_auto_rcv_available;
309
310 /*-----------------------------------------------------------------------------------------------------------*/
311 /* procedure name: VALIDATE_RCV_INPUT */
312 /* description : Validates the RMA data. Checks for mandatory fields for */
313 /* Receiving Open interface API. */
314 /* Called from : CSD_RECEIVE_PVT.RECEIVE_ITEM */
315 /* Input Parm :
316 /* p_validation_level NUMBER Optional API uses this parameter to determine which */
317 /* validation steps must be done and which steps */
318 /* should be skipped. */
319 /* p_receive_rec CSD_RCV_UTIL.RCV_REC_TYPE Required */
320 /* Output Parm : x_return_status VARCHAR2 Return status after the call. The status can be*/
321 /* fnd_api.g_ret_sts_success (success) */
322 /* fnd_api.g_ret_sts_error (error) */
323 /* fnd_api.g_ret_sts_unexp_error (unexpected) */
324 /*-----------------------------------------------------------------------------------------------------------*/
325 PROCEDURE validate_rcv_input (
326 p_validation_level IN NUMBER,
327 x_return_status OUT NOCOPY VARCHAR2,
328 p_receive_rec IN csd_receive_util.rcv_rec_type
329 )
330 IS
331 l_tmp_char VARCHAR2 (1);
332 l_srl_Status VARCHAR2(30);
333 l_c_api_name CONSTANT VARCHAR2 (30) := 'VALIDATE_RCV_INPUT';
334 l_c_srl_out_of_Stores constant number := 4;
335 l_c_srl_in_Stores constant number := 3;
336 l_c_srl_in_transit constant number := 5;
337
338
339 CURSOR cur_subinv (
340 p_org_id NUMBER,
341 p_subinv VARCHAR2
342 )
343 IS
344 SELECT 'x'
345 FROM mtl_secondary_inventories
346 WHERE secondary_inventory_name = p_subinv
347 AND organization_id = p_org_id;
348
349 CURSOR cur_serial_status (
350 p_org_id NUMBER,
351 p_inventory_item_id NUMBER,
352 p_serial_number VARCHAR2
353 )
354 IS
355 SELECT Current_status
356 FROM mtl_serial_numbers
357 WHERE -- current_organization_id = p_org_id
358 --AND
359 inventory_item_id = p_inventory_item_id
360 AND serial_number = p_serial_number;
361 --AND current_status = 4; -- 4=> out of stores
362 BEGIN
363
364 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
365 THEN
366 fnd_log.STRING
367 (fnd_log.level_procedure,
368 'CSD.PLSQL.CSD_RECEIVE_UTIL.VALIDATE_RCV_INPUT.BEGIN',
369 'Entered VALIDATE_RCV_INPUT'
370 );
371 END IF;
372
373 -- initialize return status
374 x_return_status := fnd_api.g_ret_sts_success;
375 -- Check for required parameters
376 csd_process_util.check_reqd_param
377 (p_param_value => p_receive_rec.quantity,
378 p_param_name => 'P_RECEIVE_REC.QUANTITY',
379 p_api_name => l_c_api_name
380 );
381
382 -- Vlaidate the inventory item id.
383
384 IF (NOT csd_process_util.validate_inventory_item_id
385 (p_inventory_item_id => p_receive_rec.inventory_item_id)
386 )
387 THEN
388 RAISE fnd_api.g_exc_error;
389 END IF;
390
391 -- Validate the sub inventory.....
392 OPEN cur_subinv (p_receive_rec.to_organization_id,
393 p_receive_rec.subinventory);
394
395 FETCH cur_subinv
396 INTO l_tmp_char;
397
398 IF (cur_subinv%NOTFOUND)
399 THEN
400 fnd_message.set_name ('CSD', 'CSD_INVALID_SUBINV');
401 fnd_msg_pub.ADD;
402
403 CLOSE cur_subinv;
404
405 RAISE fnd_api.g_exc_error;
406 END IF;
407
408 CLOSE cur_subinv;
409
410 -- validate the serial number status
411 -- if the status is not 'issued out of stores' do not allow it.
412 -- 10/31/05 validate only if the serial number is not null
413
414 if(p_receive_rec.serial_number is not null ) then
415 OPEN cur_serial_status (p_receive_rec.to_organization_id,
416 p_receive_rec.inventory_item_id,
417 p_receive_rec.serial_number);
418
419 FETCH cur_serial_status
420 INTO l_srl_Status;
421
422 --IF (cur_serial_status %NOTFOUND)
423 --THEN
424 -- fnd_message.set_name ('CSD', 'CSD_INVALID_SRL_STATUS');
425 -- fnd_message.set_token ('STATUS', ' ');
426 -- fnd_msg_pub.ADD;
427 -- CLOSE cur_serial_status;
428 -- RAISE fnd_api.g_exc_error;
429 --
430 IF ( cur_serial_status %FOUND )
431 THEN
432 IF ( p_receive_rec.internal_order_flag <> 'Y' AND l_Srl_status <> l_c_srl_out_of_Stores) THEN
433 fnd_message.set_name ('CSD', 'CSD_INVALID_SRL_STATUS');
434 fnd_message.set_token ('STATUS', l_Srl_status);
435 fnd_msg_pub.ADD;
436 CLOSE cur_serial_status;
437 RAISE fnd_api.g_exc_error;
438 ELSIF ( p_receive_rec.internal_order_flag = 'Y' AND l_Srl_status <> l_c_srl_in_Stores
439 AND l_srl_Status <> l_c_srl_in_transit ) THEN
440 fnd_message.set_name ('CSD', 'CSD_INVALID_SRL_STATUS');
441 fnd_message.set_token ('STATUS', l_Srl_status);
442 fnd_msg_pub.ADD;
443 CLOSE cur_serial_status;
444 RAISE fnd_api.g_exc_error;
445
446 END IF;
447 END IF;
448
449 CLOSE cur_serial_status;
450 END IF; -- end of if serail _number is not null
451
452
453 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
454 THEN
455 fnd_log.STRING (fnd_log.level_procedure,
456 'CSD.PLSQL.CSD_RECEIVE_UTIL.VALIDATE_RCV_INPUT.END',
457 'Leaving VALIDATE_RCV_INPUT'
458 );
459 END IF;
460
461 EXCEPTION
462 WHEN fnd_api.g_exc_error
463 THEN
464 x_return_status := fnd_api.g_ret_sts_error;
465 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)
466 THEN
467 fnd_log.STRING (fnd_log.level_error,
468 'CSD.PLSQL.CSD_RECEIVE_UTIL.VALIDATE_RCV_INPUT',
469 'EXC_ERROR in validate_rcv_input '
470 );
471 END IF;
472 WHEN OTHERS
473 THEN
474 x_return_status := fnd_api.g_ret_sts_unexp_error;
475
476 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
477 THEN
478 fnd_msg_pub.add_exc_msg (g_pkg_name, l_c_api_name);
479 END IF;
480
481 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)
482 THEN
483 fnd_log.STRING (fnd_log.level_unexpected,
484 'CSD.PLSQL.CSD_RECEIVE_UTIL.VALIDATE_RCV_INPUT',
485 'SQL Message in validate_rcv_input[' || SQLERRM || ']'
486 );
487 END IF;
488 END validate_rcv_input;
489
490 /*-----------------------------------------------------------------------------------------------------------*/
491 /* procedure name: CHECK_RCV_ERRORS */
492 /* description : Checks the PO_INTERFACE_ERRORS table to see of there are any error records created by the */
493 /* receiving transaction processor.. */
494 /* Called from : CSD_RECEIVE_PVT.RECEIVE_ITEM */
495 /* Input Parm : p_request_group_id NUMBER Required */
496 /* Output Parm : x_return_status VARCHAR2 Return status after the call. The status can be*/
497 /* fnd_api.g_ret_sts_success (success) */
498 /* fnd_api.g_ret_sts_error (error) */
499 /* fnd_api.g_ret_sts_unexp_error (unexpected) */
500 /*-----------------------------------------------------------------------------------------------------------*/
501 PROCEDURE check_rcv_errors (
502 x_return_status OUT NOCOPY VARCHAR2,
503 x_rcv_error_msg_tbl OUT NOCOPY csd_receive_util.rcv_error_msg_tbl,
504 p_request_group_id IN NUMBER
505 )
506 IS
507 l_api_version_number CONSTANT NUMBER := 1.0;
508 l_api_name CONSTANT VARCHAR2 (30) := 'CHECK_RCV_ERRORS';
509 l_header_id NUMBER;
510 l_line_id NUMBER;
511 i NUMBER;
512 l_order_header_id NUMBER;
513 l_order_line_id NUMBER;
514
515 -- Cursor which gets the header interface transaction id from the
516 -- rcv_transactions_interface for the given request group id.
517 CURSOR cur_rcv_headers (
518 p_group_id NUMBER
519 )
520 IS
521 SELECT header_interface_id
522 FROM rcv_headers_interface
523 WHERE GROUP_ID = p_group_id;
524
525 -- Cursor which gets the interface transaction id from the
526 -- rcv_transactions_interface for the given request group id.
527 CURSOR cur_rcv_lines (
528 p_group_id NUMBER
529 )
530 IS
531 SELECT interface_transaction_id
532 FROM rcv_transactions_interface
533 WHERE GROUP_ID = p_group_id;
534
535
536 -- Cursor to select the receiving errors from po_interface_errors table
537 CURSOR cur_rcv_errors (
538 p_hdr_intf_id NUMBER,
539 p_line_intf_id NUMBER
540 )
541 IS
542 SELECT column_name, error_message
543 FROM po_interface_errors
544 WHERE (interface_header_id = p_hdr_intf_id OR
545 interface_line_id = p_line_intf_id) ;
546
547 -- Cursor to derive the transaction_details
548 CURSOR cur_get_txn_details ( p_interface_transaction_id NUMBER) IS
549 SELECT oe_order_header_id,
550 oe_order_line_id
551 FROM rcv_transactions_interface
552 WHERE interface_transaction_id = p_interface_transaction_id;
553
554 BEGIN
555
556
557 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
558 THEN
559 fnd_log.STRING (fnd_log.level_procedure,
560 'CSD.PLSQL.CSD_RECEIVE_UTIL.CHECK_RCV_ERRORS.BEGIN',
561 'Entered CHECK_RCV_ERRORS'
562 );
563 END IF;
564
565 -- initialize return status
566 x_return_status := fnd_api.g_ret_sts_success;
567
568 -- Open cursors to get header_interface id and trnasaction_interface_id
569 open cur_rcv_headers (p_request_group_id);
570 open cur_rcv_lines(p_request_group_id);
571
572
573 --Fetch header_interface id and trnasaction_interface_id and
574 -- loop through untill both are null.
575 l_header_id := -1;
576 l_line_id := -1;
577 i := 0;
578
579 LOOP
580
581 if(l_header_id is not null ) then
582 FETCH cur_rcv_headers into l_header_id;
583 if(cur_rcv_headers%NOTFOUND) THEN
584 l_header_id := null;
585 close cur_rcv_headers;
586 end if;
587 end if;
588
589 if(l_line_id is not null ) then
590 FETCH cur_rcv_lines into l_line_id;
591 if(cur_rcv_lines%NOTFOUND) THEN
592 l_line_id := null;
593 close cur_rcv_lines;
594 end if;
595 end if;
596
597 if(l_header_id is null and l_line_id is null ) then
598 exit;
599 else
600 -- when one of the header_interface id or trnasaction_interface_id
601 -- is not null then fetch the interface errors.
602 FOR rcv_error_rec IN
603 cur_rcv_errors (l_header_id,l_line_id)
604 LOOP
605
606 i := i + 1;
607
608 fnd_message.set_name ('CSD', 'CSD_AUTO_RCV_ERROR');
609 fnd_message.set_token ('RCV_ERROR', rcv_error_rec.error_message);
610 fnd_msg_pub.ADD;
611
612 -- Derive the Transaction details
613 if(l_line_id is not null ) then
614 open cur_get_txn_details(l_line_id);
615 fetch cur_get_txn_details into l_order_header_id,l_order_line_id;
616 close cur_get_txn_details;
617 else
618 l_order_header_id := null;
619 l_order_line_id := null;
620 end if;
621
622 -- Add message to the message table
623 x_rcv_error_msg_tbl(i).group_id := p_request_group_id;
624 x_rcv_error_msg_tbl(i).header_interface_id := l_header_id;
625 x_rcv_error_msg_tbl(i).interface_transaction_id := l_line_id;
626 x_rcv_error_msg_tbl(i).order_header_id := l_order_header_id;
627 x_rcv_error_msg_tbl(i).order_line_id := l_order_line_id;
628 x_rcv_error_msg_tbl(i).column_name := rcv_error_rec.column_name;
629 x_rcv_error_msg_tbl(i).error_message := rcv_error_rec.error_message;
630
631 x_return_status := fnd_api.g_ret_sts_error;
632
633 END LOOP;
634 end if;
635 END LOOP;
636
637 if(cur_rcv_headers%ISOPEN) then
638 close cur_rcv_headers;
639 end if;
640 if(cur_rcv_lines%ISOPEN) then
641 close cur_rcv_lines;
642 end if;
643
644 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
645 THEN
646 fnd_log.STRING (fnd_log.level_procedure,
647 'CSD.PLSQL.CSD_RECEIVE_UTIL.CHECK_RCV_ERRORS.END',
648 'Leaving CHECK_RCV_ERRORS'
649 );
650 END IF;
651 EXCEPTION
652 WHEN OTHERS
653 THEN
654 x_return_status := fnd_api.g_ret_sts_unexp_error;
655
656 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
657 THEN
658 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
659 END IF;
660
661 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)
662 THEN
663 fnd_log.STRING (fnd_log.level_unexpected,
664 'CSD.PLSQL.CSD_RECEIVE_UTIL.CHECK_RCV_ERRORS',
665 'SQL Error Message in check_rcv_errors[' || SQLERRM || ']'
666 );
667 END IF;
668 END check_rcv_errors;
669
670 /*-----------------------------------------------------------------------------------------------------------*/
671 /* procedure name: get_employee_id */
672 /* description : This will return the employee id for the given user id. */
673 /* */
674 /* Called from : CSD_RECEIVE_PVT.RECEIVE_ITEM */
675 /* Input Parm : p_request_group_id NUMBER Required */
676 /* Output Parm : x_return_status VARCHAR2 Return status after the call. The status can be*/
677 /* fnd_api.g_ret_sts_success (success) */
678 /* fnd_api.g_ret_sts_error (error) */
679 /* fnd_api.g_ret_sts_unexp_error (unexpected) */
680 /*-----------------------------------------------------------------------------------------------------------*/
681 PROCEDURE get_employee_id (
682 p_user_id IN NUMBER,
683 x_employee_id OUT NOCOPY NUMBER
684 )
685 IS
686 CURSOR cur_get_employee_id (
687 p_user_id NUMBER
688 )
689 IS
690 SELECT hr.employee_id
691 FROM fnd_user fnd, per_employees_current_x hr
692 WHERE fnd.user_id = p_user_id
693 AND fnd.employee_id = hr.employee_id
694 AND ROWNUM = 1;
695
696 l_emp_id NUMBER;
697 BEGIN
698 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
699 THEN
700 fnd_log.STRING (fnd_log.level_procedure,
701 'CSD.PLSQL.CSD_RECEIVE_UTIL.GET_EMPLOYEE_ID.BEGIN',
702 'Entered get_employee_id'
703 );
704 END IF;
705
706 --get the employee id
707 OPEN cur_get_employee_id (p_user_id);
708
709 FETCH cur_get_employee_id
710 INTO x_employee_id;
711
712 IF (cur_get_employee_id%NOTFOUND)
713 THEN
714 /* swai: Fixed for bug#5505490 / FP#5563349
715 When employee_id not found then return null.
716 User is not defined as employee that's why
717 there is no record in table
718 per_employees_current_x.
719 In this case Depot should not raise any error.
720 */
721 /* CLOSE cur_get_employee_id;
722 RAISE fnd_api.g_exc_unexpected_error; */
723
724 x_employee_id := NULL;
725 END IF;
726
727 CLOSE cur_get_employee_id;
728
729 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
730 THEN
731 fnd_log.STRING (fnd_log.level_procedure,
732 'CSD.PLSQL.CSD_RECEIVE_UTIL.GET_EMPLOYEE_ID.END',
733 'Leaving get_employee_id'
734 );
735 END IF;
736 EXCEPTION
737 WHEN fnd_api.g_exc_unexpected_error
738 THEN
739 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
740 THEN
741 fnd_log.STRING (fnd_log.level_exception,
742 'CSD.PLSQL.CSD_RECEIVE_UTIL.GET_EMPLOYEE_ID',
743 'EXC_UNEXPECTED_ERROR in get_employee_id'
744 );
745 END IF;
746
747 RAISE;
748 WHEN OTHERS
749 THEN
750 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)
751 THEN
752 fnd_log.STRING (fnd_log.level_unexpected,
753 'CSD.PLSQL.CSD_RECEIVE_UTIL.GET_EMPLOYEE_ID',
754 'SQL MEssage in get_employee_id[' || SQLERRM
755 || ']'
756 );
757 END IF;
758
759 RAISE;
760 END get_employee_id;
761
762 /*-----------------------------------------------------------------------------------------------------------*/
763 /* procedure name: get_rcv_item_params */
764 /* description : This will populate some required fields in the receiving data structure */
765 /* */
766 /* Called from : CSD_RECEIVE_PVT.RECEIVE_ITEM */
767 /* Input Parm : p_receive_rec CSD_RCV_UTIL.RCV_REC_TYPE Required */
768 /* Output Parm : x_return_status VARCHAR2 Return status after the call. The status can be*/
769 /* fnd_api.g_ret_sts_success (success) */
770 /* fnd_api.g_ret_sts_error (error) */
771 /* fnd_api.g_ret_sts_unexp_error (unexpected) */
772 /*-----------------------------------------------------------------------------------------------------------*/
773 PROCEDURE get_rcv_item_params (
774 p_receive_rec IN OUT NOCOPY csd_receive_util.rcv_rec_type
775 )
776 IS
777 --Define all the cursors
778 -- Cursor to get the to organization id
779 CURSOR cur_get_to_org (p_order_line_id NUMBER) IS
780 SELECT SHIP_FROM_ORG_ID
781 FROM OE_ORDER_LINES_ALL
782 WHERE LINE_ID = p_order_line_id;
783
784 -- cursor to select the category.
785 CURSOR cur_get_category (
786 p_org_id NUMBER,
787 p_category_set_id NUMBER,
788 p_item_id NUMBER
789 )
790 IS
791 SELECT MAX (category_id)
792 FROM mtl_item_categories
793 WHERE inventory_item_id = p_item_id
794 AND organization_id = p_org_id
795 AND category_set_id = p_category_set_id;
796
797 -- Cursor to select the category set for the
798 -- purchasing functional area.
799 CURSOR cur_get_category_set
800 IS
801 SELECT mdsv.category_set_id
802 FROM mtl_default_sets_view mdsv
803 WHERE mdsv.functional_area_id = 2;
804
805 -- Cursor to select the primary UOM
806 CURSOR cur_get_primary_uom (
807 p_org_id NUMBER,
808 p_item_id NUMBER
809 )
810 IS
811 SELECT primary_unit_of_measure
812 FROM mtl_item_flexfields
813 WHERE inventory_item_id = p_item_id AND organization_id = p_org_id;
814
815 -- Cursor to select the item attributes serial control code and
816 -- lot control code.
817 CURSOR cur_get_item_attribs (
818 p_org_id NUMBER,
819 p_item_id NUMBER
820 )
821 IS
822 SELECT lot_control_code, serial_number_control_code
823 FROM mtl_system_items
824 WHERE organization_id = p_org_id AND inventory_item_id = p_item_id;
825
826
827 -- Cursor to get the unit of measure
828 CURSOR cur_get_unit_of_measure (
829 p_uom_code VARCHAR2
830 )
831 IS
832 SELECT unit_of_measure
833 FROM mtl_units_of_measure_vl
834 WHERE uom_code = p_uom_Code ;
835
836 -- define local vars
837 l_category_set NUMBER;
838
839 BEGIN
840
841 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
842 THEN
843 fnd_log.STRING
844 (fnd_log.level_procedure,
845 'CSD.PLSQL.CSD_RECEIVE_UTIL.GET_RCV_ITEM_PARAMS.BEGIN',
846 'Entered get_rcv_item_params'
847 );
848 END IF;
849
850 --get the to_org id if null
851 if(p_receive_rec.to_organization_id is null) then
852 OPEN cur_get_to_org(p_receive_rec.order_line_id) ;
853
854 FETCH cur_get_to_org
855 INTO p_receive_rec.to_organization_id;
856
857 IF (cur_get_to_org %NOTFOUND)
858 THEN
859 CLOSE cur_get_to_org ;
860
861 RAISE fnd_api.g_exc_unexpected_error;
862 END IF;
863
864 CLOSE cur_get_to_org ;
865 End IF;
866 --get the category set for purchasing functional area
867 OPEN cur_get_category_set;
868
869 FETCH cur_get_category_set
870 INTO l_category_set;
871
872 IF (cur_get_category_set%NOTFOUND)
873 THEN
874 CLOSE cur_get_category;
875
876 RAISE fnd_api.g_exc_unexpected_error;
877 END IF;
878
879 CLOSE cur_get_category_set;
880
881 -- Get the category
882 OPEN cur_get_category (p_receive_rec.to_organization_id,
883 l_category_set,
884 p_receive_rec.inventory_item_id
885 );
886
887 FETCH cur_get_category
888 INTO p_receive_rec.category_id;
889
890 IF (cur_get_category%NOTFOUND)
891 THEN
892 CLOSE cur_get_category;
893 RAISE fnd_api.g_exc_unexpected_error;
894 END IF;
895 CLOSE cur_get_category;
896
897 --Get the primary UOM
898 OPEN cur_get_primary_uom (p_receive_rec.to_organization_id,
899 p_receive_rec.inventory_item_id);
900
901 FETCH cur_get_primary_uom
902 INTO p_receive_rec.primary_unit_of_measure;
903
904 IF (cur_get_primary_uom%NOTFOUND)
905 THEN
906 CLOSE cur_get_primary_uom;
907 RAISE fnd_api.g_exc_unexpected_error;
908 END IF;
909 CLOSE cur_get_primary_uom;
910
911 --Get serial number control code and lot control code
912 OPEN cur_get_item_attribs (p_receive_rec.to_organization_id,
913 p_receive_rec.inventory_item_id);
914
915 FETCH cur_get_item_attribs
916 INTO p_receive_rec.lot_control_code, p_receive_rec.serial_control_code;
917
918 IF (cur_get_item_attribs%NOTFOUND)
919 THEN
920 CLOSE cur_get_item_attribs;
921 RAISE fnd_api.g_exc_unexpected_error;
922 END IF;
923 CLOSE cur_get_item_attribs;
924
925 --Get serial number control code and lot control code
926 OPEN cur_get_unit_of_measure (p_receive_rec.uom_code);
927
928 FETCH cur_get_unit_of_measure
929 INTO p_receive_rec.unit_of_measure;
930
931 IF (cur_get_unit_of_measure%NOTFOUND)
932 THEN
933 CLOSE cur_get_unit_of_measure;
934 RAISE fnd_api.g_exc_unexpected_error;
935 END IF;
936 CLOSE cur_get_unit_of_measure;
937
938
939 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
940 THEN
941 fnd_log.STRING
942 (fnd_log.level_procedure,
943 'CSD.PLSQL.CSD_RECEIVE_UTIL.GET_RCV_ITEM_PARAMS.END',
944 'Leaving get_rcv_item_params'
945 );
946 END IF;
947
948 EXCEPTION
949 WHEN fnd_api.g_exc_unexpected_error
950 THEN
951 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
952 THEN
953 fnd_log.STRING (fnd_log.level_exception,
954 'CSD.PLSQL.CSD_RECEIVE_UTIL.GET_RCV_ITEM_PARAMS',
955 'EXC_UNEXPECTED_ERROR in get_rcv_item_params'
956 );
957 END IF;
958
959 RAISE;
960 WHEN OTHERS
961 THEN
962 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)
963 THEN
964 fnd_log.STRING (fnd_log.level_unexpected,
965 'CSD.PLSQL.CSD_RECEIVE_UTIL.GET_RCV_ITEM_PARAMS',
966 'SQL Error Message in get_rcv_item_params ['
967 || SQLERRM
968 || ']'
969 );
970 END IF;
971
972 RAISE fnd_api.g_exc_unexpected_error;
973 END get_rcv_item_params;
974 END csd_receive_util;
[Home] [Help]