1 PACKAGE BODY PO_DOCUMENT_LOCK_GRP AS
2 /* $Header: POXGLOKB.pls 120.2.12020000.2 2013/02/10 18:17:02 vegajula ship $*/
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
272
273
274 -------------------------------------------------------------------------------
275 --<Bug 14254141 :Cancel Refactoring Project >
276 --Start of Comments
277 --Name: lock_document
278 --Pre-reqs:
279 -- The operating unit context must be set before calling this API - i.e.:
280 --Function:
281 -- Locks the document, including the header and all the lines, shipments,
282 -- and distributions, as appropriate.
283 -- documents to be locked are available in po_session_gt with key=po_sesiongt_key
284 --Modifies:
285 -- Acquires database locks on the document.
286 --Parameters:
287 --IN:
288 -- p_api_version :
289 -- API version number expected by the caller
290 -- p_init_msg_list:
291 -- If FND_API.G_TRUE, the API will initialize the standard API message list.
292 -- po_sesiongt_key:
293 -- Docuements to be locked are manitined in po_session_gt
294 -- po_sesiongt_key is the key in po_session_gt to identify
295 -- the intended records in the table
296 -- p_online_report_id:
297 -- If the locking of any of teh document contained in po_session_gt fails
298 -- Then an appropriate error message will be inserted in po_online_report_text
299 -- table with online_report_id=p_online_report_id
300 -- p_user_id
301 -- Current User Id
302 -- p_login_id
303 -- Current User login ID
304
305 --OUT:
306 --x_return_status
307 -- FND_API.G_RET_STS_SUCCESS if the API successfully locked the document.
308 -- FND_API.G_RET_STS_ERROR if the API could not lock the document, because
309 -- the lock is being held by another session.
310 -- FND_API.G_RET_STS_UNEXP_ERROR if an unexpected error occurred, such as
311 -- invalid document type or document ID.
312 --End of Comments
313 -------------------------------------------------------------------------------
314
315
316 PROCEDURE lock_document (
317 p_online_report_id IN NUMBER,
318 p_api_version IN NUMBER,
319 p_init_msg_list IN VARCHAR2,
320 x_return_status OUT NOCOPY VARCHAR2,
321 p_user_id IN po_lines.last_updated_by%TYPE,
322 p_login_id IN po_lines.last_update_login%TYPE ,
323 po_sesiongt_key IN po_session_gt.key%TYPE)
324
325 IS
326
327 d_api_name CONSTANT VARCHAR2(30) := 'LOCK_DOCUMENT.';
328 d_module CONSTANT VARCHAR2(100) := g_module_prefix || d_api_name;
329
330 l_progress VARCHAR2(3);
331 l_api_version CONSTANT NUMBER := 1.0;
332 l_sequence po_online_report_text.sequence%TYPE;
333
334
335 -- ORA-00054 is the resource busy exception, which is raised when we try
336 -- to lock a row that is already locked by another session.
337 resource_busy_exc EXCEPTION;
338 PRAGMA EXCEPTION_INIT(resource_busy_exc, -00054);
339
340 l_dummy po_tbl_NUMBER;
341
342
343 -- SQL What: Locks the header and all the lines, shipments, and
344 -- distributions of the PO.
345 -- SQL Why: To prevent others from modifying the document.
346 CURSOR lock_po_csr IS
347 SELECT 1
348 FROM po_headers POH,
349 po_lines POL,
350 po_line_locations PLL,
351 po_distributions POD,
352 po_session_gt gt
353 WHERE gt.KEY=po_sesiongt_key
354 AND POH.po_header_id =gt.char4
355 AND gt.char1 = 'PO'
356 AND POH.po_header_id = POL.po_header_id (+) -- JOIN
357 AND POL.po_line_id = PLL.po_line_id (+) -- JOIN
358 AND NVL(PLL.shipment_type, 'STANDARD') IN ('STANDARD', 'PLANNED', 'PREPAYMENT')
359 AND PLL.line_location_id = POD.line_location_id (+) -- JOIN
360 FOR UPDATE NOWAIT;
361
362
363 -- SQL What: Locks the header and all the lines and price breaks of the PA.
364 -- SQL Why: To prevent others from modifying the document.
365 CURSOR lock_pa_csr IS
366 SELECT 1
367 FROM po_headers POH,
368 po_lines POL,
369 po_line_locations PLL,
370 po_session_gt gt
371 WHERE gt.KEY=po_sesiongt_key
372 AND POH.po_header_id =gt.char4
373 AND gt.char1 = 'PA'
374 AND POH.po_header_id = POL.po_header_id (+) -- JOIN
375 AND POL.po_line_id = PLL.po_line_id (+) -- JOIN
376 AND PLL.shipment_type (+) = 'PRICE BREAK'
377 FOR UPDATE NOWAIT;
378
379 -- SQL What: Locks the release and all its shipments and distributions.
380 -- SQL Why: To prevent others from modifying the document.
381 CURSOR lock_release_csr IS
382 SELECT 1
383 FROM po_releases POR, po_line_locations PLL,
384 po_distributions POD,
385 po_session_gt gt
386 WHERE gt.KEY=po_sesiongt_key
387 AND POR.po_release_id =gt.char4
388 AND gt.char1 = 'RELEASE'
389 AND POR.po_release_id = PLL.po_release_id (+) -- JOIN
390 AND PLL.line_location_id = POD.line_location_id (+) -- JOIN
391 FOR UPDATE NOWAIT;
392
393 BEGIN
394
395
396 IF (g_fnd_debug = 'Y') THEN
397 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
398 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE, g_module_prefix || d_api_name,
399 'Entering ' || d_api_name );
400 END IF;
401 END IF;
402
403 l_progress :='000';
404
405 IF (g_fnd_debug = 'Y') THEN
406 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
407 PO_DEBUG.debug_begin(d_module);
408 PO_DEBUG.debug_var(d_module,l_progress,'p_online_report_id',p_online_report_id);
409 PO_DEBUG.debug_var(d_module,l_progress,'p_api_version',p_api_version);
410 PO_DEBUG.debug_var(d_module,l_progress,'p_init_msg_list',p_init_msg_list);
411 PO_DEBUG.debug_var(d_module,l_progress,'p_user_id',p_user_id);
412 PO_DEBUG.debug_var(d_module,l_progress,'p_login_id',p_login_id);
413 PO_DEBUG.debug_var(d_module,l_progress,'po_sesiongt_key',po_sesiongt_key);
414 END IF;
415 END IF;
416
417
418 -- Standard API initialization:
419 IF NOT FND_API.compatible_api_call ( l_api_version, p_api_version,
420 d_api_name, G_PKG_NAME ) THEN
421 RAISE FND_API.g_exc_unexpected_error;
422 END IF;
423
424 IF (FND_API.to_boolean(p_init_msg_list)) THEN
425 FND_MSG_PUB.initialize;
426 END IF;
427
428
429 l_progress :='001';
430
431 SELECT Nvl(Max(sequence) ,0)
432 INTO l_sequence
433 FROM PO_ONLINE_REPORT_TEXT
434 WHERE online_report_id=p_online_report_id;
435
436 IF (g_fnd_debug = 'Y') THEN
437 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
438 PO_DEBUG.debug_var(d_module,l_progress,'l_sequence',l_sequence);
439 END IF;
440 END IF;
441
442
443 l_progress :='002';
444
445 -- Try to lock the PO.
446 OPEN lock_po_csr;
447 FETCH lock_po_csr BULK COLLECT INTO l_dummy;
448 CLOSE lock_po_csr;
449
450 l_progress :='003';
451
452
453 -- Try to lock the PA.
454 OPEN lock_pa_csr;
455 FETCH lock_pa_csr BULK COLLECT INTO l_dummy;
456 CLOSE lock_pa_csr;
457
458 l_progress :='004';
459 -- Try to lock the release.
460 OPEN lock_release_csr;
461 FETCH lock_release_csr BULK COLLECT INTO l_dummy;
462 CLOSE lock_release_csr;
463
464
465 x_return_status := FND_API.G_RET_STS_SUCCESS;
466
467
468 IF (g_fnd_debug = 'Y') THEN
469 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
470 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE, g_module_prefix || d_api_name,
471 'Exiting ' || d_api_name );
472 END IF;
473 END IF;
474
475 EXCEPTION
476 WHEN resource_busy_exc THEN -- Cannot acquire the lock
477 INSERT INTO PO_ONLINE_REPORT_TEXT
478 (ONLINE_REPORT_ID,
479 LAST_UPDATE_LOGIN,
480 LAST_UPDATED_BY,
481 LAST_UPDATE_DATE,
482 CREATED_BY,
483 CREATION_DATE,
484 LINE_NUM,
485 SHIPMENT_NUM,
486 DISTRIBUTION_NUM,
487 SEQUENCE,
488 TEXT_LINE,
489 transaction_id,
490 transaction_level)
491 VALUES
492 ( p_online_report_id,
493 p_login_id,
494 p_user_id,
495 SYSDATE,
496 p_user_id,
497 SYSDATE,
498 0,
499 0,
500 0,
501 l_sequence + 1,
502 PO_CORE_S.get_translated_text
503 ('PO_DOC_CANNOT_LOCK'),
504 0,
505 0);
506
507 x_return_status := FND_API.G_RET_STS_ERROR;
508
509 IF (g_fnd_debug = 'Y') THEN
510 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR) THEN
511 FND_LOG.string( FND_LOG.LEVEL_ERROR, g_module_prefix || d_api_name,
512 FND_MSG_PUB.get(p_msg_index => FND_MSG_PUB.G_LAST,
513 p_encoded => FND_API.G_FALSE) );
514 END IF;
515 END IF;
516
517 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
518
519 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
520 IF (g_fnd_debug = 'Y') THEN
521 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
522 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED, g_module_prefix || d_api_name,
523 FND_MSG_PUB.get(p_msg_index => FND_MSG_PUB.G_LAST,
524 p_encoded => FND_API.G_FALSE) );
525 END IF;
526 END IF;
527 WHEN OTHERS THEN
528 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
529 FND_MSG_PUB.add_exc_msg ( G_PKG_NAME, d_api_name );
530 IF (g_fnd_debug = 'Y') THEN
531 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
532 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED, g_module_prefix || d_api_name,
533 FND_MSG_PUB.get(p_msg_index => FND_MSG_PUB.G_LAST,
534 p_encoded => FND_API.G_FALSE) );
535 END IF;
536 END IF;
537 END lock_document;
538
539
540
541 END PO_DOCUMENT_LOCK_GRP;
[Home] [Help]