[Home] [Help]
PACKAGE BODY: APPS.PO_LOCKS
Source
1 PACKAGE BODY PO_LOCKS AS
2 -- $Header: POXLOCKB.pls 115.2 2003/08/25 19:24:16 sbull noship $
3
4
5
6 -----------------------------------------------------------------------------
7 -- Declare private package variables.
8 -----------------------------------------------------------------------------
9
10 -- Debugging
11
12 g_pkg_name CONSTANT
13 VARCHAR2(30)
14 := 'PO_LOCKS'
15 ;
16 g_log_head CONSTANT
17 VARCHAR2(50)
18 := 'po.plsql.' || g_pkg_name || '.'
19 ;
20
21 g_debug_stmt
22 BOOLEAN
23 ;
24 g_debug_unexp
25 BOOLEAN
26 ;
27
28
29
30
31 -----------------------------------------------------------------------------
32 -- Define procedures.
33 -----------------------------------------------------------------------------
34
35
36
37
38 -------------------------------------------------------------------------------
39 --Start of Comments
40 --Name: lock_headers
41 --Pre-reqs:
42 -- None.
43 --Modifies:
44 -- None.
45 --Locks:
46 -- PO_HEADERS_ALL
47 -- PO_RELEASES_ALL
48 -- PO_REQUISITION_HEADERS_ALL
49 --Function:
50 -- Locks the document headers of the given ids.
51 --Parameters:
52 --IN:
53 --p_doc_type
54 -- Document type. Use the g_doc_type_<> variables, where <> is:
55 -- REQUISITION
56 -- PA
57 -- PO
58 -- RELEASE
59 --p_doc_level
60 -- The type of ids that are being passed. Use g_doc_level_<>
61 -- HEADER
62 -- LINE
63 -- SHIPMENT
64 -- DISTRIBUTION
65 --p_doc_level_id_tbl
66 -- Ids of the doc level type of which to lock the header of the document.
67 --Testing:
68 --
69 --End of Comments
70 -------------------------------------------------------------------------------
71 PROCEDURE lock_headers(
72 p_doc_type IN VARCHAR2
73 , p_doc_level IN VARCHAR2
74 , p_doc_level_id_tbl IN po_tbl_number
75 )
76 IS
77
78 l_log_head CONSTANT VARCHAR2(100) := g_log_head||'LOCK_HEADERS';
79 l_progress VARCHAR2(3) := '000';
80
81 l_doc_id_tbl po_tbl_number;
82 l_doc_id_key NUMBER;
83
84 ----------------------------------------------------------------
85 -- PO_SESSION_GT column mapping
86 --
87 -- num1 doc id
88 ----------------------------------------------------------------
89
90 CURSOR l_lock_req_csr(p_doc_id_key NUMBER) IS
91 SELECT NULL
92 FROM
93 PO_REQUISITION_HEADERS_ALL PRH
94 , PO_SESSION_GT IDS
95 WHERE PRH.requisition_header_id = IDS.num1
96 AND IDS.key = p_doc_id_key
97 FOR UPDATE OF PRH.requisition_header_id
98 NOWAIT
99 ;
100
101 CURSOR l_lock_release_csr(p_doc_id_key NUMBER) IS
102 SELECT NULL
103 FROM
104 PO_RELEASES_ALL POR
105 , PO_SESSION_GT IDS
106 WHERE POR.po_release_id = IDS.num1
107 AND IDS.key = p_doc_id_key
108 FOR UPDATE OF POR.po_release_id
109 NOWAIT
110 ;
111
112 CURSOR l_lock_po_csr(p_doc_id_key NUMBER) IS
113 SELECT NULL
114 FROM
115 PO_HEADERS_ALL POH
116 , PO_SESSION_GT IDS
117 WHERE POH.po_header_id = IDS.num1
118 AND IDS.key = p_doc_id_key
119 FOR UPDATE OF POH.po_header_id
120 NOWAIT
121 ;
122
123 BEGIN
124
125 IF g_debug_stmt THEN
126 PO_DEBUG.debug_begin(l_log_head);
127 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_type', p_doc_type);
128 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_level', p_doc_level);
129 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_level_id_tbl', p_doc_level_id_tbl);
130 END IF;
131
132 l_progress := '010';
133
134 -- Get the header ids for the given doc level ids.
135
136 PO_CORE_S.get_document_ids(
137 p_doc_type => p_doc_type
138 , p_doc_level => p_doc_level
139 , p_doc_level_id_tbl => p_doc_level_id_tbl
140 , x_doc_id_tbl => l_doc_id_tbl
141 );
142
143 l_progress := '020';
144
145 -- Put the header ids into the scratchpad so that the cursors work.
146
147 SELECT PO_SESSION_GT_S.nextval
148 INTO l_doc_id_key
149 FROM DUAL
150 ;
151
152 l_progress := '030';
153
154 FORALL i IN 1 .. l_doc_id_tbl.COUNT
155 INSERT INTO PO_SESSION_GT ( key, num1 )
156 VALUES ( l_doc_id_key, l_doc_id_tbl(i) )
157 ;
158
159 l_progress := '040';
160
161 -- Cursors lock the rows on OPEN, and we don't need to SELECT anything.
162
163 IF (p_doc_type = PO_CORE_S.g_doc_type_REQUISITION) THEN
164
165 l_progress := '050';
166 IF g_debug_stmt THEN
167 PO_DEBUG.debug_stmt(l_log_head,l_progress,'requisition');
168 END IF;
169
170 OPEN l_lock_req_csr(p_doc_id_key => l_doc_id_key);
171 CLOSE l_lock_req_csr;
172
173 l_progress := '060';
174
175 ELSIF (p_doc_type = PO_CORE_S.g_doc_type_RELEASE) THEN
176
177 l_progress := '070';
178 IF g_debug_stmt THEN
179 PO_DEBUG.debug_stmt(l_log_head,l_progress,'release');
180 END IF;
181
182 OPEN l_lock_release_csr(p_doc_id_key => l_doc_id_key);
183 CLOSE l_lock_release_csr;
184
185 l_progress := '080';
186
187 ELSIF (p_doc_type IN (PO_CORE_S.g_doc_type_PO, PO_CORE_S.g_doc_type_PA)) THEN
188
189 l_progress := '090';
190 IF g_debug_stmt THEN
191 PO_DEBUG.debug_stmt(l_log_head,l_progress,'po, pa');
192 END IF;
193
194 OPEN l_lock_po_csr(p_doc_id_key => l_doc_id_key);
195 CLOSE l_lock_po_csr;
196
197 l_progress := '100';
198
199 ELSE
200
201 l_progress := '190';
202
203 RAISE PO_CORE_S.g_INVALID_CALL_EXC;
204
205 END IF;
206
207 l_progress := '900';
208
209 IF g_debug_stmt THEN
210 PO_DEBUG.debug_end(l_log_head);
211 END IF;
212
213 EXCEPTION
214 WHEN OTHERS THEN
215 IF g_debug_unexp THEN
216 PO_DEBUG.debug_exc(l_log_head,l_progress);
217 END IF;
218 RAISE;
219
220 END lock_headers;
221
222
223
224
225 -------------------------------------------------------------------------------
226 --Start of Comments
227 --Name: lock_distributions
228 --Pre-reqs:
229 -- None.
230 --Modifies:
231 -- None.
232 --Locks:
233 -- PO_DISTRIBUTIONS_ALL
234 -- PO_REQ_DISTRIBUTIONS_ALL
235 --Function:
236 -- Locks the distributions below the given ids.
237 --Parameters:
238 --IN:
239 --p_doc_type
240 -- Document type. Use the g_doc_type_<> variables, where <> is:
241 -- REQUISITION
242 -- PA
243 -- PO
244 -- RELEASE
245 --p_doc_level
246 -- The type of ids that are being passed. Use g_doc_level_<>
247 -- HEADER
248 -- LINE
249 -- SHIPMENT
250 -- DISTRIBUTION
251 --p_doc_level_id_tbl
252 -- Ids of the doc level type of which to lock the header of the document.
253 --Testing:
254 --
255 --End of Comments
256 -------------------------------------------------------------------------------
257 PROCEDURE lock_distributions(
258 p_doc_type IN VARCHAR2
259 , p_doc_level IN VARCHAR2
260 , p_doc_level_id_tbl IN po_tbl_number
261 )
262 IS
263
264 l_log_head CONSTANT VARCHAR2(100) := g_log_head||'LOCK_DISTRIBUTIONS';
265 l_progress VARCHAR2(3) := '000';
266
267 l_dist_id_tbl po_tbl_number;
268 l_dist_id_key NUMBER;
269
270 ----------------------------------------------------------------
271 -- PO_SESSION_GT column mapping
272 --
273 -- num1 distribution id
274 ----------------------------------------------------------------
275
276 CURSOR l_lock_req_csr(p_dist_id_key NUMBER) IS
277 SELECT NULL
278 FROM
279 PO_REQ_DISTRIBUTIONS_ALL PRD
280 , PO_SESSION_GT DIST_IDS
281 WHERE PRD.distribution_id = DIST_IDS.num1
282 AND DIST_IDS.key = p_dist_id_key
283 FOR UPDATE OF PRD.distribution_id
284 NOWAIT
285 ;
286
287 CURSOR l_lock_nonreq_csr(p_dist_id_key NUMBER) IS
288 SELECT NULL
289 FROM
290 PO_DISTRIBUTIONS_ALL POD
291 , PO_SESSION_GT DIST_IDS
292 WHERE POD.po_distribution_id = DIST_IDS.num1
293 AND DIST_IDS.key = p_dist_id_key
294 FOR UPDATE OF POD.po_distribution_id
295 NOWAIT
296 ;
297
298 BEGIN
299
300 IF g_debug_stmt THEN
301 PO_DEBUG.debug_begin(l_log_head);
302 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_type', p_doc_type);
303 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_level', p_doc_level);
304 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_level_id_tbl', p_doc_level_id_tbl);
305 END IF;
306
307 l_progress := '010';
308
309 -- Get the distribution ids for the given doc level ids.
310
311 PO_CORE_S.get_distribution_ids(
312 p_doc_type => p_doc_type
313 , p_doc_level => p_doc_level
314 , p_doc_level_id_tbl => p_doc_level_id_tbl
315 , x_distribution_id_tbl => l_dist_id_tbl
316 );
317
318 l_progress := '015';
319
320 -- Put the distribution ids in the scratchpad, so that the
321 -- cursors will work (PL/SQL locking limitations).
322
323 SELECT PO_SESSION_GT_S.nextval
324 INTO l_dist_id_key
325 FROM DUAL
326 ;
327
328 l_progress := '020';
329
330 FORALL i IN 1 .. l_dist_id_tbl.COUNT
331 INSERT INTO PO_SESSION_GT ( key, num1 )
332 VALUES ( l_dist_id_key, l_dist_id_tbl(i) )
333 ;
334
335 l_progress := '030';
336
337 -- Cursors lock the rows on OPEN, and we don't need to SELECT into anything.
338
339 IF (p_doc_type = PO_CORE_S.g_doc_type_REQUISITION) THEN
340
341 l_progress := '040';
342 IF g_debug_stmt THEN
343 PO_DEBUG.debug_stmt(l_log_head,l_progress,'requisition');
344 END IF;
345
346 OPEN l_lock_req_csr(p_dist_id_key => l_dist_id_key);
347 CLOSE l_lock_req_csr;
348
349 l_progress := '050';
350
351 ELSIF (p_doc_type IN ( PO_CORE_S.g_doc_type_PO
352 , PO_CORE_S.g_doc_type_PA
353 , PO_CORE_S.g_doc_type_RELEASE ))
354 THEN
355
356 l_progress := '060';
357 IF g_debug_stmt THEN
358 PO_DEBUG.debug_stmt(l_log_head,l_progress,'not requisition');
359 END IF;
360
361 OPEN l_lock_nonreq_csr(p_dist_id_key => l_dist_id_key);
362 CLOSE l_lock_nonreq_csr;
363
364 l_progress := '070';
365
366 ELSE
367
368 l_progress := '090';
369
370 RAISE PO_CORE_S.g_INVALID_CALL_EXC;
371
372 END IF;
373
374 l_progress := '900';
375
376 IF g_debug_stmt THEN
377 PO_DEBUG.debug_end(l_log_head);
378 END IF;
379
380 EXCEPTION
381 WHEN OTHERS THEN
382 IF g_debug_unexp THEN
383 PO_DEBUG.debug_exc(l_log_head,l_progress);
384 END IF;
385 RAISE;
386
387 END lock_distributions;
388
389
390
391
392 -----------------------------------------------------------------------------
393 -- Initialize package variables.
394 -----------------------------------------------------------------------------
395
396 BEGIN
397
398 g_debug_stmt := PO_DEBUG.is_debug_stmt_on;
399 g_debug_unexp := PO_DEBUG.is_debug_unexp_on;
400
401
402 END PO_LOCKS;