1 PACKAGE BODY PO_REQ_DOCUMENT_LOCK_GRP AS
2 /* $Header: POXGRLKB.pls 120.1 2005/06/29 18:36:50 shsiung 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 g_pkg_name CONSTANT VARCHAR2(30) := 'PO_REQ_DOCUMENT_LOCK_GRP';
7 c_log_head CONSTANT VARCHAR2(50) := 'po.plsql.'|| G_PKG_NAME || '.';
8
9 -------------------------------------------------------------------------------
10 --Start of Comments
11 --Name: lock_requisition
12 --Pre-reqs:
13 -- None.
14 --Modifies:
15 -- None.
16 --Locks:
17 -- Locks the Requisition Header/Line/Distribution records.
18 --Function:
19 -- Locks the Requisition Header/Line/Distribution records.
20 --Parameters:
21 --IN:
22 --p_api_version
23 -- API version number expected by the caller
24 --p_req_header_id
25 -- Requisition Header ID indicating which requisition to lock.
26 --OUT:
27 --x_return_status
28 -- Indicates API return status as 'S', 'E' or 'U'.
29 --End of Comments
30 -------------------------------------------------------------------------------
31 PROCEDURE lock_requisition (
32 p_api_version IN NUMBER,
33 p_req_header_id IN NUMBER,
34 x_return_status OUT NOCOPY VARCHAR2
35 ) IS
36
37 l_api_version CONSTANT NUMBER := 1.0;
38 l_api_name CONSTANT VARCHAR2(30) := 'lock_requisition';
39 l_progress VARCHAR2(3) := '000';
40
41 -- ORA-00054 is the resource busy exception, which is raised when we try
42 -- to lock a row that is already locked by another session.
43 resource_busy_exc EXCEPTION;
44 PRAGMA EXCEPTION_INIT(resource_busy_exc, -00054);
45
46 l_dummy NUMBER;
47
48 -- SQL What: Locks the header and all the lines, and distributions of the Req.
49 CURSOR lock_req_csr IS
50 SELECT 1
51 FROM po_requisition_headers H, po_requisition_lines L, po_req_distributions D
52 WHERE H.requisition_header_id = p_req_header_id
53 AND H.requisition_header_id = L.requisition_header_id (+)
54 AND L.requisition_line_id = D.requisition_line_id (+)
55 FOR UPDATE NOWAIT;
56
57 BEGIN
58
59 IF g_fnd_debug = 'Y' THEN
60 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
61 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, c_log_head || '.'||l_api_name||'.'
62 || l_progress, 'Entering Procedure '||l_api_name);
63 END IF;
64 END IF;
65
66 -- Standard call to check for call compatibility
67 l_progress := '010';
68 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
69 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
70 END IF;
71
72 -- Try to lock the Requisition.
73 OPEN lock_req_csr;
74 FETCH lock_req_csr INTO l_dummy;
75 IF (lock_req_csr%NOTFOUND) THEN -- Cannot acquire the lock
76 RAISE FND_API.G_EXC_ERROR;
77 END IF;
78 CLOSE lock_req_csr;
79
80 x_return_status := FND_API.G_RET_STS_SUCCESS;
81
82 EXCEPTION
83 WHEN FND_API.G_EXC_ERROR OR resource_busy_exc THEN -- Cannot acquire the lock
84 FND_MESSAGE.set_name('PO', 'PO_DOC_CANNOT_LOCK');
85 FND_MSG_PUB.add;
86 x_return_status := FND_API.G_RET_STS_ERROR;
87 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
88 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
89 WHEN OTHERS THEN
90 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
91 FND_MSG_PUB.add_exc_msg ( G_PKG_NAME, l_api_name );
92 END lock_requisition;
93
94 END PO_REQ_DOCUMENT_LOCK_GRP;