1 PACKAGE BODY PO_DOCUMENT_LOCK_GRP AS
2 /* $Header: POXGLOKB.pls 120.2 2005/09/08 18:15:31 spangulu noship $*/
3
4 -- Read the profile option that enables/disables the debug log
5 g_fnd_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
6
7 g_pkg_name CONSTANT VARCHAR2(30) := 'PO_DOCUMENT_LOCK_GRP';
8 g_module_prefix CONSTANT VARCHAR2(40) := 'po.plsql.' || g_pkg_name || '.';
9
10 -------------------------------------------------------------------------------
11 --Start of Comments
12 --Name: lock_document
13 --Pre-reqs:
14 -- The operating unit context must be set before calling this API - i.e.:
15 --Function:
16 -- Locks the document, including the header and all the lines, shipments,
17 -- and distributions, as appropriate.
18 --Modifies:
19 -- Acquires database locks on the document.
20 --Parameters:
21 --IN:
22 --p_api_version
23 -- API version number expected by the caller
24 --p_init_msg_list
25 -- If FND_API.G_TRUE, the API will initialize the standard API message list.
26 --p_document_type
27 -- type of document to lock: 'PO', 'PA', or 'RELEASE'
28 --p_document_id
29 -- ID of the document to lock - po_header_id for POs and PAs;
30 -- po_release_id for releases
31 --OUT:
32 --x_return_status
33 -- FND_API.G_RET_STS_SUCCESS if the API successfully locked the document.
34 -- FND_API.G_RET_STS_ERROR if the API could not lock the document, because
35 -- the lock is being held by another session.
36 -- FND_API.G_RET_STS_UNEXP_ERROR if an unexpected error occurred, such as
37 -- invalid document type or document ID.
38 --End of Comments
39 -------------------------------------------------------------------------------
40 PROCEDURE lock_document (
41 p_api_version IN NUMBER,
42 p_init_msg_list IN VARCHAR2,
43 x_return_status OUT NOCOPY VARCHAR2,
44 p_document_type IN VARCHAR2,
45 p_document_id IN NUMBER
46 ) IS
47 l_api_version CONSTANT NUMBER := 1.0;
48 l_api_name CONSTANT VARCHAR2(30) := 'LOCK_DOCUMENT';
49
50 -- ORA-00054 is the resource busy exception, which is raised when we try
51 -- to lock a row that is already locked by another session.
52 resource_busy_exc EXCEPTION;
53 PRAGMA EXCEPTION_INIT(resource_busy_exc, -00054);
54
55 l_dummy NUMBER;
56
57 -- SQL What: Locks the header and all the lines, shipments, and
58 -- distributions of the PO.
59 -- SQL Why: To prevent others from modifying the document.
60 CURSOR lock_po_csr IS
61 SELECT 1
62 FROM po_headers POH, po_lines POL, po_line_locations PLL,
63 po_distributions POD
64 WHERE POH.po_header_id = p_document_id
65 AND POH.po_header_id = POL.po_header_id (+) -- JOIN
66 AND POL.po_line_id = PLL.po_line_id (+) -- JOIN
67 -- Need NVL(..) because we cannot use (+) with the IN operator:
68 -- <Complex Work R12>: Include PREPAYMENT shipment_types in locking.
69 AND NVL(PLL.shipment_type, 'STANDARD') IN ('STANDARD', 'PLANNED', 'PREPAYMENT')
70 AND PLL.line_location_id = POD.line_location_id (+) -- JOIN
71 FOR UPDATE NOWAIT;
72
73 -- SQL What: Locks the header and all the lines and price breaks of the PA.
74 -- SQL Why: To prevent others from modifying the document.
75 CURSOR lock_pa_csr IS
76 SELECT 1
77 FROM po_headers POH, po_lines POL, po_line_locations PLL
78 WHERE POH.po_header_id = p_document_id
79 AND POH.po_header_id = POL.po_header_id (+) -- JOIN
80 AND POL.po_line_id = PLL.po_line_id (+) -- JOIN
81 AND PLL.shipment_type (+) = 'PRICE BREAK'
82 FOR UPDATE NOWAIT;
83
84 -- SQL What: Locks the release and all its shipments and distributions.
85 -- SQL Why: To prevent others from modifying the document.
86 CURSOR lock_release_csr IS
87 SELECT 1
88 FROM po_releases POR, po_line_locations PLL, po_distributions POD
89 WHERE POR.po_release_id = p_document_id
90 AND POR.po_release_id = PLL.po_release_id (+) -- JOIN
91 AND PLL.line_location_id = POD.line_location_id (+) -- JOIN
92 FOR UPDATE NOWAIT;
93
94 -- <Document Manager Rewrite 11.5.11 Start>
95 CURSOR lock_req_csr IS
96 SELECT 1
97 FROM po_requisition_headers porh, po_requisition_lines porl,
98 po_req_distributions pord
99 WHERE porh.requisition_header_id = p_document_id
100 AND porh.requisition_header_id = porl.requisition_header_id (+) -- JOIN
101 AND porl.requisition_line_id = pord.requisition_line_id (+) -- JOIN
102 FOR UPDATE NOWAIT;
103 -- <Document Manager Rewrite 11.5.11 End>
104
105 BEGIN
106 IF (g_fnd_debug = 'Y') THEN
107 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
108 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE, g_module_prefix || l_api_name,
109 'Entering ' || l_api_name );
110 END IF;
111 END IF;
112
113 -- Standard API initialization:
114 IF NOT FND_API.compatible_api_call ( l_api_version, p_api_version,
115 l_api_name, G_PKG_NAME ) THEN
116 RAISE FND_API.g_exc_unexpected_error;
117 END IF;
118
119 IF (FND_API.to_boolean(p_init_msg_list)) THEN
120 FND_MSG_PUB.initialize;
121 END IF;
122
123 x_return_status := FND_API.G_RET_STS_SUCCESS;
124
125 -- For each document type, verify that the requested document exists,
126 -- then try to lock it.
127 IF (p_document_type = 'PO') THEN
128
129 -- Verify that the PO exists.
130 BEGIN
131 SELECT 1
132 INTO l_dummy
133 FROM po_headers
134 WHERE po_header_id = p_document_id
135 AND type_lookup_code IN ('STANDARD','PLANNED');
136 EXCEPTION
137 WHEN no_data_found THEN
138 FND_MESSAGE.set_name('PO', 'PO_INVALID_DOC_IDS');
139 FND_MSG_PUB.add;
140 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
141 END;
142
143 -- Try to lock the PO.
144 OPEN lock_po_csr;
145 FETCH lock_po_csr INTO l_dummy;
146 IF (lock_po_csr%NOTFOUND) THEN -- Cannot acquire the lock
147 RAISE FND_API.G_EXC_ERROR;
148 END IF;
149 CLOSE lock_po_csr;
150
151 ELSIF (p_document_type = 'PA') THEN
152
153 -- Verify that the PA exists.
154 BEGIN
155 SELECT 1
156 INTO l_dummy
157 FROM po_headers
158 WHERE po_header_id = p_document_id
159 AND type_lookup_code IN ('BLANKET','CONTRACT');
160 EXCEPTION
161 WHEN no_data_found THEN
162 FND_MESSAGE.set_name('PO', 'PO_INVALID_DOC_IDS');
163 FND_MSG_PUB.add;
164 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
165 END;
166
167 -- Try to lock the PA.
168 OPEN lock_pa_csr;
169 FETCH lock_pa_csr INTO l_dummy;
170 IF (lock_pa_csr%NOTFOUND) THEN -- Cannot acquire the lock
171 RAISE FND_API.G_EXC_ERROR;
172 END IF;
173 CLOSE lock_pa_csr;
174
175 ELSIF (p_document_type = 'RELEASE') THEN
176
177 -- Verify that the release exists.
178 BEGIN
179 SELECT 1
180 INTO l_dummy
181 FROM po_releases
182 WHERE po_release_id = p_document_id;
183 EXCEPTION
184 WHEN no_data_found THEN
185 FND_MESSAGE.set_name('PO', 'PO_INVALID_DOC_IDS');
186 FND_MSG_PUB.add;
187 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
188 END;
189
190 -- Try to lock the release.
191 OPEN lock_release_csr;
192 FETCH lock_release_csr INTO l_dummy;
193 IF (lock_release_csr%NOTFOUND) THEN -- Cannot acquire the lock
194 RAISE FND_API.G_EXC_ERROR;
195 END IF;
196 CLOSE lock_release_csr;
197
198 -- <Document Manager Rewrite 11.5.11 Start>
199 ELSIF (p_document_type = 'REQUISITION') THEN
200
201 -- Verify that the requisition exists.
202 BEGIN
203 SELECT 1
204 INTO l_dummy
205 FROM po_requisition_headers porh
206 WHERE porh.requisition_header_id = p_document_id;
207 EXCEPTION
208 WHEN no_data_found THEN
209 FND_MESSAGE.set_name('PO', 'PO_INVALID_DOC_IDS');
210 FND_MSG_PUB.add;
211 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
212 END;
213
214 -- Try to lock the requisition.
215 OPEN lock_req_csr;
216 FETCH lock_req_csr INTO l_dummy;
217 IF (lock_req_csr%NOTFOUND) THEN -- Cannot acquire the lock
218 RAISE FND_API.G_EXC_ERROR;
219 END IF;
220 CLOSE lock_req_csr;
221
222 -- <Document Manager Rewrite 11.5.11 End>
223 ELSE -- invalid document type
224
225 FND_MESSAGE.set_name('PO', 'PO_INVALID_DOC_TYPE');
226 FND_MESSAGE.set_token('TYPE', p_document_type);
227 FND_MSG_PUB.add;
228 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
229
230 END IF; -- p_document_type
231
232 IF (g_fnd_debug = 'Y') THEN
233 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
234 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE, g_module_prefix || l_api_name,
235 'Exiting ' || l_api_name );
236 END IF;
237 END IF;
238 EXCEPTION
239 WHEN FND_API.G_EXC_ERROR OR resource_busy_exc THEN -- Cannot acquire the lock
240 FND_MESSAGE.set_name('PO', 'PO_DOC_CANNOT_LOCK');
241 FND_MSG_PUB.add;
242 x_return_status := FND_API.G_RET_STS_ERROR;
243 IF (g_fnd_debug = 'Y') THEN
244 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR) THEN
245 FND_LOG.string( FND_LOG.LEVEL_ERROR, g_module_prefix || l_api_name,
246 FND_MSG_PUB.get(p_msg_index => FND_MSG_PUB.G_LAST,
247 p_encoded => FND_API.G_FALSE) );
248 END IF;
249 END IF;
250 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
251 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
252 IF (g_fnd_debug = 'Y') THEN
253 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
254 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED, g_module_prefix || l_api_name,
255 FND_MSG_PUB.get(p_msg_index => FND_MSG_PUB.G_LAST,
256 p_encoded => FND_API.G_FALSE) );
257 END IF;
258 END IF;
259 WHEN OTHERS THEN
260 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
261 FND_MSG_PUB.add_exc_msg ( G_PKG_NAME, l_api_name );
262 IF (g_fnd_debug = 'Y') THEN
263 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
264 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED, g_module_prefix || l_api_name,
265 FND_MSG_PUB.get(p_msg_index => FND_MSG_PUB.G_LAST,
266 p_encoded => FND_API.G_FALSE) );
267 END IF;
268 END IF;
269 END lock_document;
270
271 END PO_DOCUMENT_LOCK_GRP;
[Home] [Help]