PACKAGE BODY: APPS.INV_RESERVATION_LOCK_PVT

Source

1 PACKAGE BODY INV_RESERVATION_LOCK_PVT AS
2 /* $Header: INVLRSVB.pls 120.1 2005/07/20 16:19:56 lplam noship $ */
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 
24 BEGIN
25    l_status := 9;
26 
27    IF (l_source_header_id = fnd_api.g_miss_num) THEN
28        l_source_header_id := NULL;
29    END IF;
30 
31    IF (l_source_line_id = fnd_api.g_miss_num) THEN
32        l_source_line_id := NULL;
33    END IF;
34 
35    IF (l_source_line_detail = fnd_api.g_miss_num) THEN
36        l_source_line_detail := NULL;
37    END IF;
38 
39    l_lock_name := 'INV_RSV_' ||
40      p_organization_id   || '_' ||
41      p_inventory_item_id || '_' ||
42      p_source_type_id    || '_' ||
43      Nvl(l_source_header_id,-99)  || '_' ||
44      Nvl(l_source_line_id, -99)    || '_' ||
45      Nvl(l_source_line_detail, -99);
46 
47    get_lock_handle(l_lock_name,l_lock_handle);
48 
49    /*	 select USERENV('SESSIONID') into l_sessionid from dual;
50    inv_log_util.trace('Session is.. ' ||l_sessionid , 'INV_RESERVATION_LOCK_PVT', 9);  */
51 
52      inv_log_util.trace('Lock Name is...:' ||l_lock_name , 'INV_RESERVATION_LOCK_PVT', 9);
53    inv_log_util.trace('Lock Handle is...:' ||l_lock_handle , 'INV_RESERVATION_LOCK_PVT', 9);
54 
55    l_status := dbms_lock.request
56      (
57       lockhandle        => l_lock_handle
58       ,lockmode          => dbms_lock.x_mode
59       ,timeout           => dbms_lock.maxwait
60       ,release_on_commit => TRUE);
61    -- l_status = 4.No need to insert into temp table when lock is already
62    -- owned by this session.it will become duplicate entry if we insert
63    -- and release lock may fail in this case for the second time.
64 
65    inv_log_util.trace('l_status returns from dbms_lock.request = ' || l_status, 'INV_RESERVATION_LOCK_PVT', 9);
66 
67    if (l_status <> 0 AND l_status <> 4) then
68       if (l_status = 1 OR l_status = 2) then
69          l_lock_status := 0;
70       end if;
71    ELSE
72       l_lock_status := 1;
73    END IF;
74 
75    inv_log_util.trace('l_lock_status = ' || l_lock_status,  'INV_RESERVATION_LOCK_PVT', 9);
76    x_lock_status := l_lock_status;
77    x_lock_handle := l_lock_handle;
78 EXCEPTION
79    WHEN OTHERS THEN
80       inv_log_util.trace('Exception: ' || SQLERRM,  'INV_RESERVATION_LOCK_PVT', 9);
81       x_lock_status := 0;
82 END;
83 
84 PROCEDURE get_lock_handle
85   (p_lock_name IN VARCHAR2,
86    x_lock_handle OUT NOCOPY VARCHAR2) IS
87 
88       PRAGMA AUTONOMOUS_TRANSACTION;
89 begin
90    inv_log_util.trace('p_lock_name = ' || p_lock_name, 'INV_RESERVATION_LOCK_PVT', 9);
91    dbms_lock.allocate_unique
92      (lockname       => p_lock_name
93       ,lockhandle     => x_lock_handle);
94    commit;
95 end;
96 
97 PROCEDURE release_lock(p_lock_handle IN VARCHAR2)
98   IS
99      l_ret_status NUMBER;
100 BEGIN
101    inv_log_util.trace('Begin release locks.. ' , 'INV_RESERVATION_LOCK_PVT', 9);
102 
103    l_ret_status := dbms_lock.release(p_lock_handle);
104    if l_ret_status = 0 then
105       inv_log_util.trace('Lock released successfully' , 'INV_RESERVATION_LOCK_PVT', 9);
106     else
107       inv_log_util.trace('Error in releasing the lock'||l_ret_status, 'INV_RESERVATION_LOCK_PVT', 9);
108    end if;
109 
110 END;
111 
112 END INV_RESERVATION_LOCK_PVT;