1 PACKAGE BODY wsh_interface_message_pkg AS
2 /* $Header: WSHINMSB.pls 120.0.12010000.1 2009/03/26 06:00:08 brana noship $ */
3
4 /*==============================================================================
5 -- PROCEDURE: lock_record
6 -- Purpose: Locking records in wsh_del_details_interface and wsh_new_del_interface
7 -- Description: This procedure is called from Interface Message Correction Form
8 -- for locking the record in table wsh_del_details_interface
9 -- and wsh_new_del_interface
10 * ==============================================================================*/
11
12 --
13 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_INTERFACE_MESSAGE_PKG';
14 --
15 PROCEDURE lock_record(
16 p_delivery_interface_id IN NUMBER,
17 p_delivery_detail_interface_id IN NUMBER,
18 x_return_status OUT NOCOPY VARCHAR2) is
19
20
21 CURSOR l_lock_del_interface is
22 SELECT 1
23 FROM wsh_new_del_interface
24 WHERE delivery_interface_id = p_delivery_interface_id
25 FOR UPDATE NOWAIT;
26
27
28 CURSOR l_lock_del_details_interface is
29 SELECT 1
30 FROM wsh_del_details_interface
31 WHERE delivery_detail_interface_id = p_delivery_detail_interface_id
32 FOR UPDATE NOWAIT;
33
34 l_id NUMBER :=0;
35 l_debug_on BOOLEAN;
36 l_module_name CONSTANT VARCHAR(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'LOCK_RECORD';
37 RECORD_LOCKED EXCEPTION;
38 PRAGMA EXCEPTION_INIT(RECORD_LOCKED, -54);
39
40 BEGIN
41
42 x_return_status := wsh_util_core.G_RET_STS_SUCCESS;
43 l_debug_on := wsh_debug_interface.g_debug;
44 --
45 IF l_debug_on IS NULL THEN
46 l_debug_on := wsh_debug_sv.is_debug_enabled;
47 END IF;
48 --
49 IF l_debug_on THEN
50 wsh_debug_sv.push(l_module_name);
51 END IF;
52 --
53 IF p_delivery_interface_id IS NOT NULL THEN
54
55 OPEN l_lock_del_interface;
56 FETCH l_lock_del_interface INTO l_id ;
57 CLOSE l_lock_del_interface;
58
59 ELSIF p_delivery_detail_interface_id IS NOT NULL THEN
60
61 OPEN l_lock_del_details_interface;
62 FETCH l_lock_del_details_interface INTO l_id ;
63 CLOSE l_lock_del_details_interface;
64
65 END IF;
66 --
67 IF l_id = 0 THEN
68 x_return_status := wsh_util_core.G_RET_STS_ERROR;
69 fnd_message.set_name('FND', 'FND_RECORD_DELETED_ERROR');
70 --
71 IF p_delivery_interface_id IS NOT NULL THEN
72 --
73 IF l_debug_on THEN
74 wsh_debug_sv.log(l_module_name,'Record does not exists for the Delivery Interface ID ',p_delivery_interface_id);
75 END IF;
76 --
77 ELSIF p_delivery_detail_interface_id IS NOT NULL THEN
78 --
79 IF l_debug_on THEN
80 wsh_debug_sv.log(l_module_name,'Record does not exists for the Delivery Detail Interface ID',p_delivery_detail_interface_id);
81 END IF;
82 --
83 END IF;
84 --
85 IF l_debug_on THEN
86 wsh_debug_sv.log(l_module_name,'Value of L_ID = ',l_id);
87 END IF;
88 --
89 END IF;
90 --
91 IF l_debug_on THEN
92 wsh_debug_sv.log(l_module_name,'x_return_status ',x_return_status);
93 wsh_debug_sv.pop(l_module_name);
94 END IF;
95 --
96 EXCEPTION
97
98 WHEN RECORD_LOCKED THEN
99 fnd_message.set_name('FND', 'FND_LOCK_RECORD_ERROR');
100 x_return_status := wsh_util_core.G_RET_STS_UNEXP_ERROR;
101 --
102 IF l_debug_on THEN
103 --
104 IF p_delivery_interface_id IS NOT NULL THEN
105 wsh_debug_sv.log(l_module_name,'Delivery Interface ID is locked by another user',p_delivery_interface_id);
106 ELSIF p_delivery_detail_interface_id IS NOT NULL THEN
107 wsh_debug_sv.log(l_module_name,'Delivery Detail Interface ID is Locked by another user',p_delivery_detail_interface_id);
108 END IF;
109 --
110 wsh_debug_sv.log(l_module_name,'SQLERRM = ', SQLERRM );
111 wsh_debug_sv.log(l_module_name,'x_return_status ',x_return_status);
112 wsh_debug_sv.pop(l_module_name);
113 END IF;
114 --
115 WHEN OTHERS THEN
116 x_return_status := wsh_util_core.g_ret_sts_error;
117 --
118 IF l_debug_on THEN
119 wsh_debug_sv.log(l_module_name,'SQLERRM : ',sqlerrm);
120 wsh_debug_sv.log(l_module_name,'x_return_status : ',x_return_status);
121 wsh_debug_sv.pop(l_module_name);
122 END IF;
123 --
124
125 End lock_record;
126
127 END wsh_interface_message_pkg;
[Home] [Help]