[Home] [Help]
PACKAGE BODY: APPS.INV_RESERVATION_LOCK_PVT
Source
1 PACKAGE BODY INV_RESERVATION_LOCK_PVT AS
2 /* $Header: INVLRSVB.pls 120.1.12020000.2 2012/08/15 04:24:28 tachen ship $ */
3
4 PROCEDURE lock_supply_demand_record
5 (p_organization_id IN NUMBER
6 ,p_inventory_item_id IN NUMBER
7 ,p_source_type_id IN NUMBER
8 ,p_source_header_id IN NUMBER
9 ,p_source_line_id IN NUMBER
10 ,p_source_line_detail IN NUMBER
11 ,x_lock_handle OUT NOCOPY VARCHAR2
12 ,x_lock_status OUT NOCOPY NUMBER) IS
13
14 l_lock_handle VARCHAR2(128);
15 l_lock_name VARCHAR2(2000);
16 l_status NUMBER;
17 l_sessionid NUMBER;
18 l_lock_status NUMBER;
19
20 l_source_header_id NUMBER := p_source_header_id;
21 l_source_line_id NUMBER := p_source_line_id;
22 l_source_line_detail NUMBER := p_source_line_detail;
23 l_source_type_id NUMBER := p_source_type_id; /* Bug# 13641346 */
24
25 BEGIN
26 l_status := 9;
27
28 IF (l_source_header_id = fnd_api.g_miss_num) THEN
29 l_source_header_id := NULL;
30 END IF;
31
32 IF (l_source_line_id = fnd_api.g_miss_num) THEN
33 l_source_line_id := NULL;
34 END IF;
35
36 IF (l_source_line_detail = fnd_api.g_miss_num) THEN
37 l_source_line_detail := NULL;
38 END IF;
39
40 /* Bug# 13641346 if source type id passed g_miss_num,
41 the lock name exceed 128 which is the max length
42 defined in DB */
43 IF (l_source_type_id = fnd_api.g_miss_num) THEN
44 l_source_type_id := NULL;
45 END IF;
46
47 l_lock_name := 'INV_RSV_' ||
48 p_organization_id || '_' ||
49 p_inventory_item_id || '_' ||
50 Nvl(l_source_type_id,-99) || '_' || /* Bug# 13641346 */
51 Nvl(l_source_header_id,-99) || '_' ||
52 Nvl(l_source_line_id, -99) || '_' ||
53 Nvl(l_source_line_detail, -99);
54
55 get_lock_handle(l_lock_name,l_lock_handle);
56
57 /* select USERENV('SESSIONID') into l_sessionid from dual;
58 inv_log_util.trace('Session is.. ' ||l_sessionid , 'INV_RESERVATION_LOCK_PVT', 9); */
59
60 inv_log_util.trace('Lock Name is...:' ||l_lock_name , 'INV_RESERVATION_LOCK_PVT', 9);
61 inv_log_util.trace('Lock Handle is...:' ||l_lock_handle , 'INV_RESERVATION_LOCK_PVT', 9);
62
63 l_status := dbms_lock.request
64 (
65 lockhandle => l_lock_handle
66 ,lockmode => dbms_lock.x_mode
67 ,timeout => dbms_lock.maxwait
68 ,release_on_commit => TRUE);
69 -- l_status = 4.No need to insert into temp table when lock is already
70 -- owned by this session.it will become duplicate entry if we insert
71 -- and release lock may fail in this case for the second time.
72
73 inv_log_util.trace('l_status returns from dbms_lock.request = ' || l_status, 'INV_RESERVATION_LOCK_PVT', 9);
74
75 if (l_status <> 0 AND l_status <> 4) then
76 if (l_status = 1 OR l_status = 2) then
77 l_lock_status := 0;
78 end if;
79 ELSE
80 l_lock_status := 1;
81 END IF;
82
83 inv_log_util.trace('l_lock_status = ' || l_lock_status, 'INV_RESERVATION_LOCK_PVT', 9);
84 x_lock_status := l_lock_status;
85 x_lock_handle := l_lock_handle;
86 EXCEPTION
87 WHEN OTHERS THEN
88 inv_log_util.trace('Exception: ' || SQLERRM, 'INV_RESERVATION_LOCK_PVT', 9);
89 x_lock_status := 0;
90 END;
91
92 PROCEDURE get_lock_handle
93 (p_lock_name IN VARCHAR2,
94 x_lock_handle OUT NOCOPY VARCHAR2) IS
95
96 PRAGMA AUTONOMOUS_TRANSACTION;
97 begin
98 inv_log_util.trace('p_lock_name = ' || p_lock_name, 'INV_RESERVATION_LOCK_PVT', 9);
99 dbms_lock.allocate_unique
100 (lockname => p_lock_name
101 ,lockhandle => x_lock_handle);
102 commit;
103 end;
104
105 PROCEDURE release_lock(p_lock_handle IN VARCHAR2)
106 IS
107 l_ret_status NUMBER;
108 BEGIN
109 inv_log_util.trace('Begin release locks.. ' , 'INV_RESERVATION_LOCK_PVT', 9);
110
111 l_ret_status := dbms_lock.release(p_lock_handle);
112 if l_ret_status = 0 then
113 inv_log_util.trace('Lock released successfully' , 'INV_RESERVATION_LOCK_PVT', 9);
114 else
115 inv_log_util.trace('Error in releasing the lock'||l_ret_status, 'INV_RESERVATION_LOCK_PVT', 9);
116 end if;
117
118 END;
119
120 END INV_RESERVATION_LOCK_PVT;